Data Engineer interview prep

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 global tech companies, they work on petabyte-scale data, ensuring reliability, low latency, and cost efficiency for teams across product, analytics, and machine learning. A typical day involves writing code (Python, SQL, Scala), debugging pipeline failures, and optimizing data models.

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 and transforms in the target system. Choose ELT for cloud warehouses with strong compute, ETL for legacy systems or strict compliance.

2. 2

   How would you design a data pipeline to ingest daily logs from millions of devices into a data warehouse?

   Cover batch vs. streaming, partitioning by date, using object storage as landing zone, and orchestrating with Airflow. Mention handling late data and idempotency.

3. 3

   Explain slowly changing dimensions (SCD) and how you would implement Type 2 in a warehouse.

   Describe adding new rows with effective dates and current flag. Mention using surrogate keys and merge statements to track history.

4. 4

   What are the trade-offs between using a star schema and a normalized schema for analytics?

   Star schema simplifies queries and improves read performance; normalized reduces redundancy but requires more joins. Choose based on query patterns and update frequency.

Practical scenarios

1. 5

   A daily pipeline that usually finishes in 2 hours is now taking 6 hours. How do you debug and fix it?

   Check for data volume spikes, skewed joins, resource contention, and failing tasks. Suggest monitoring, logging, and adding parallelism or partitioning.

2. 6

   You need to join a 1 TB fact table with a 10 GB dimension table in Spark. How do you optimize the join?

   Use broadcast join for the smaller table, ensure proper partitioning, and avoid shuffles. Mention salting if skew is present.

3. 7

   How would you handle duplicate records arriving from an upstream source that occasionally retries?

   Use deduplication logic based on unique keys and timestamps, and design idempotent writes. Mention using merge or upsert in the target.

4. 8

   Your pipeline needs to process streaming data with exactly-once semantics. What approach would you take?

   Discuss using Kafka with idempotent producers and transactional writes, or Spark Structured Streaming with checkpointing. Mention deduplication and offset management.

Coding and SQL

1. 9

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

   Use a subquery with MAX or a window function like DENSE_RANK. Explain handling ties and NULLs.

2. 10

   Given a table of user events, write a query to calculate the daily active users for the last 7 days.

   Use DATE_TRUNC and COUNT(DISTINCT user_id) with a WHERE clause on date. Mention timezone considerations.

3. 11

   How would you find and remove duplicate rows from a table using SQL?

   Use ROW_NUMBER() OVER (PARTITION BY columns ORDER BY id) and delete rows with row number > 1. Explain testing on a copy first.

4. 12

   Implement a Python function to flatten a nested JSON object into a single-level dictionary.

   Use recursion with a prefix for keys. Handle lists by either indexing or exploding. Discuss edge cases like empty dicts.

Behavioral and HR

1. 13

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

   Use STAR format: describe the situation, the technology, your learning approach, and the outcome. Highlight resourcefulness and impact.

2. 14

   Describe a situation where you disagreed with a teammate on a technical approach. How did you resolve it?

   Focus on data-driven discussion, listening, and compromise. Show you prioritize project success over being right.

3. 15

   Why do you want to work as a data engineer at a global tech company like Apple?

   Connect personal interest in large-scale data to the company's products and data challenges. Mention specific projects or values that resonate.

4. 16

   How do you prioritize tasks when you have multiple deadlines and a production issue arises?

   Explain assessing impact, communicating with stakeholders, and triaging. Mention documenting and following up to prevent recurrence.