Trendmicro India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines that move and transform data from source systems into storage and analytics platforms. In a global tech product company, they work on batch and streaming ingestion, data quality, and cost-efficient storage to support product analytics, security telemetry, and internal reporting. Freshers typically start by writing SQL, building ETL jobs, and fixing data issues under senior guidance.

Role and Technical Knowledge

1. 1

   What is the difference between ETL and ELT, and when would you choose one over the other?

   Explain that ETL transforms before loading while ELT loads raw data first and transforms inside the warehouse. Mention that ELT suits cloud warehouses with strong compute, while ETL suits strict schema or limited storage.

2. 2

   Explain star schema and snowflake schema with an example from a product analytics use case.

   Describe fact tables with measures and dimension tables with attributes. Use a fact table of user events linked to dimension tables for users, devices, and time. Note that star schema is simpler and faster for queries, while snowflake reduces redundancy.

3. 3

   How would you handle slowly changing dimensions in a data warehouse?

   Cover Type 1 (overwrite), Type 2 (add new row with effective dates and current flag), and Type 3 (add previous value column). Explain that Type 2 is common for tracking history in customer or product dimensions.

4. 4

   What are the key differences between Parquet and CSV, and why does Parquet matter for big data?

   Highlight that Parquet is columnar, compressed, and schema-aware, which speeds up analytical queries and reduces storage. CSV is row-based, plain text, and slower for large scans. Mention predicate pushdown and column pruning.

Practical Scenarios and Problem Solving

1. 5

   A daily batch job that loads sales data has been failing for three days. How do you debug and recover?

   Start by checking logs for errors, then validate source file availability and schema changes. Suggest rerunning the job for missing partitions, adding idempotency, and setting up alerts to catch failures earlier.

2. 6

   You need to join a large fact table with a small dimension table in Spark. How do you optimize this join?

   Mention broadcast join to avoid shuffling the large table. Explain that Spark can broadcast small tables automatically or you can hint it. Also note filtering early and using column pruning.

3. 7

   How would you design a pipeline to ingest clickstream data from a web app into a data lake?

   Cover a streaming or micro-batch approach using Kafka or Kinesis, landing raw data in object storage, then processing with Spark or Glue into Parquet. Include partitioning by date and schema validation.

4. 8

   A stakeholder reports that a dashboard shows incorrect totals. How do you investigate?

   Trace data lineage from dashboard to source, check for duplicate records, late-arriving data, and join errors. Suggest adding data quality checks like row count comparisons and null checks.

SQL and Coding Depth

1. 9

   Write a SQL query to find the second highest salary from an employee table.

   Use a subquery with MAX and a WHERE clause less than the max, or use DENSE_RANK with a window function. Explain that DENSE_RANK handles ties correctly.

2. 10

   How do you find duplicate records in a table and delete them while keeping one?

   Use GROUP BY and HAVING COUNT(*) > 1 to find duplicates. For deletion, use a CTE with ROW_NUMBER and delete rows where row number is greater than 1.

3. 11

   Given a list of integers in Python, write a function to return the top k frequent elements.

   Use a dictionary to count frequencies, then sort by value or use a heap. Mention that Counter from collections is a clean approach.

4. 12

   Explain the difference between UNION and UNION ALL in SQL.

   UNION removes duplicates and may sort, while UNION ALL keeps all rows and is faster. Recommend UNION ALL when duplicates are not a concern.

Behavioural and HR Questions

1. 13

   Tell me about a time you had to learn a new technology quickly for a project.

   Use the STAR method: describe the situation, the technology, your learning approach, and the outcome. Show curiosity and ability to apply knowledge under deadlines.

2. 14

   Describe a situation where you found a mistake in your own work. How did you handle it?

   Be honest about the error, explain how you detected it, corrected it, and prevented recurrence. Emphasize ownership and attention to detail.

3. 15

   Why do you want to work as a Data Engineer in a product company rather than a services company?

   Connect to building scalable systems that support real products, working with large datasets, and continuous learning. Mention interest in data reliability and impact on product decisions.

4. 16

   How do you prioritize when you have multiple data pipeline issues to fix?

   Assess impact on downstream users and business criticality first. Communicate with stakeholders, fix the highest impact issue, and document the rest for follow-up.