9 Data Engineering interview questions and answers

A warehouse stores structured, modelled data with the schema enforced on write, and is tuned for SQL analytics.

A data lake stores raw files of any format cheaply in object storage, and the schema is applied when you read.

A lakehouse adds an open table format such as Delta Lake, Iceberg or Hudi on top of lake storage. That gives you ACID transactions, schema enforcement and time travel on cheap storage, so one copy of the data serves both BI and machine learning.

ETL transforms data before loading it into the target. ELT loads raw data first and transforms it inside the warehouse, usually with SQL or dbt.

ELT is common with cloud warehouses because compute scales on demand, and keeping the raw data lets you rebuild models when business logic changes.

It keeps history. When a tracked attribute changes, you close the current row by setting its end date and is_current = false, then insert a new row with a new surrogate key.

Facts join to the surrogate key that was valid when the event happened, so old reports still show the customer's city at the time of the order.

In a narrow transformation each output partition depends on one input partition, for example map, filter or select. No data moves between executors.

A wide transformation such as groupBy, join or distinct needs rows from many partitions, so Spark shuffles data across the network and starts a new stage. Shuffles are usually the most expensive part of a job.

Skew happens when a few keys hold most of the rows. During a shuffle one task gets far more data than the rest, and the stage waits on it.

Fixes: turn on Adaptive Query Execution skew-join handling, broadcast the smaller table, salt the hot key with a random suffix, filter out null keys before the join, or pre-aggregate before joining.

Running it twice for the same input gives the same result, with no duplicates. That makes retries and backfills safe.

Common techniques: overwrite a whole partition instead of appending, use MERGE on business keys, and pass the run date as a parameter instead of calling now() inside the job.

Every change is recorded as a numbered JSON commit in the _delta_log folder, with Parquet checkpoints every few commits.

Writers write new data files first, then try to commit the next log version atomically. If another writer committed first, Delta checks for conflicts and either retries or fails. Readers build a snapshot from the log, so they never see half-written data.

You need three things: a replayable source (Kafka with offsets), checkpointing of those offsets and state (Structured Streaming checkpoints), and an idempotent or transactional sink.

The Delta sink records each batch ID in its commit, so a batch replayed after a crash is not written twice.

Partition on a low-cardinality column you filter on often, usually a date. Too many partitions create small files and slow everything down.

For high-cardinality filter columns such as customer_id, use Z-ordering or liquid clustering. They co-locate similar values inside files so min/max statistics let the engine skip most files.