Data Engineering Interview Questions and Answers

A data warehouse stores structured, modelled data optimized for analytics and SQL. It uses schema-on-write: data is cleaned and conformed before loading, typically into star or snowflake schemas. Examples include Snowflake, BigQuery and Redshift. Warehouses offer strong performance, governance and consistency for BI.

A data lake stores raw data of any type in cheap object storage and uses schema-on-read: structure is applied at query time. It is flexible and inexpensive but can become a data swamp without cataloguing and governance.

Modern lakehouse architectures combine both: open table formats such as Delta Lake and Apache Iceberg add ACID transactions and schema management on top of lake storage, so the same data serves BI and machine learning. The practical choice depends on data variety, cost and query latency needs.

ETL extracts data from sources, transforms it on a separate processing engine, then loads the clean result into the target. ELT extracts and loads raw data into the target first, then transforms it using the target's own compute.

ETL suits on-premise warehouses with limited storage and compute, and cases where sensitive data must be masked before landing. It requires a dedicated transformation server and often custom code.

ELT suits cloud warehouses that separate storage from compute, such as Snowflake or BigQuery. Loading raw data is cheap, transformations run as SQL inside the warehouse, and the raw layer stays available for reprocessing. Tools like dbt and Fivetran popularized this model.

ELT gives flexibility and lineage because transformations are versioned SQL, but it depends on a powerful warehouse and disciplined access control on raw data.

Both are dimensional models. A star schema has one central fact table surrounded by denormalized dimension tables. Each dimension is a single table, so joins stay simple and queries run fast. A snowflake schema normalizes dimensions into multiple related tables, for example splitting a product dimension into product, category and supplier tables.

Star advantages: fewer joins, simpler SQL, better query performance, easier for BI tools. Snowflake advantages: less redundancy, smaller storage, and dimensions that are easier to maintain when hierarchies change.

Most analytics warehouses use star schemas because storage is cheap and query simplicity matters. Snowflaking helps when a dimension is genuinely shared or very wide. The fact table holds foreign keys and numeric measures at a defined grain, and declaring that grain precisely is the most important modelling decision.

Partitioning splits a large dataset into smaller physical chunks so queries and maintenance target only relevant data. Common strategies are range (by date), list (by region) and hash (for even distribution). In warehouses, partition pruning means a query filtered by date scans only matching partitions, cutting cost and time dramatically.

CREATE TABLE events (
  event_id BIGINT,
  event_date DATE,
  user_id BIGINT
)
PARTITION BY RANGE (event_date);

Considerations:

• Choose a partition key used in most filters, usually a date.
• Avoid too many tiny partitions, which add metadata and planning overhead.
• Watch for skew: one huge partition becomes a bottleneck.
• Clustering or sort keys inside a partition further speed range scans.

Partitioning is separate from bucketing or clustering, which organize data within partitions by another key for joins.

Airflow models a workflow as a DAG: a directed acyclic graph of tasks with dependencies. A scheduler parses DAG files, creates DAG runs per schedule interval, and queues tasks whose upstream dependencies succeeded. Workers execute tasks and the metadata database tracks state.

with DAG("daily_sales", schedule="@daily", start_date=dt(2024,1,1)) as dag:
    extract = PythonOperator(task_id="extract", python_callable=extract_fn)
    transform = SQLOperator(task_id="transform", sql=TRANSFORM_SQL)
    load = PythonOperator(task_id="load", python_callable=load_fn)
    extract >> transform >> load

Key concepts: idempotent tasks, retries with backoff, backfills, sensors that wait for data, and catchup for historical runs. Keep each task atomic and avoid heavy work at parse time. Failures surface per task with logs, and SLA alerts notify on lateness. Alternatives include Dagster, Prefect and cloud schedulers.

Batch processing handles bounded datasets on a schedule. It is simple, high-throughput and easy to reprocess, but results are delayed by the batch interval. Examples include nightly aggregations with Spark or SQL.

Stream processing handles unbounded events continuously, producing low-latency results. Tools include Kafka Streams, Flink and Spark Structured Streaming. It must handle out-of-order events, exactly-once semantics, windowing and state.

spark.readStream.format("kafka").load() \
  .withWatermark("ts", "10 minutes") \
  .groupBy(window("ts", "5 minutes"), "region").count() \
  .writeStream.format("delta").option("checkpointLocation", path).start()

Trade-offs: streaming adds operational complexity and cost, while batch is cheaper and easier to reason about. Common patterns are lambda and kappa architectures, using streams for speed and batch for correctness, or one replayable stream for both.

Data quality checks should run as first-class pipeline steps, failing or quarantining bad data rather than silently loading it.

Dimensions to test:

• Completeness: no unexpected nulls in required columns.
• Uniqueness: primary keys are unique.
• Validity: values fall in allowed ranges or formats, such as ISO dates.
• Consistency: totals reconcile across tables.
• Freshness: the table was updated within the expected window.
• Referential integrity: foreign keys exist in the dimension.

Implement with tools like Great Expectations, dbt tests, Soda or custom assertions. Add a circuit breaker that halts downstream jobs on critical failures, and route failed rows to a quarantine table for inspection. Publish metrics and alerts so issues are caught before they reach dashboards. Record expectations as code and review them like application tests.

An idempotent operation produces the same result whether it runs once or many times. Pipelines retry on failure, run backfills and get replayed, so non-idempotent logic creates duplicates or double counting.

Techniques:

• Overwrite partitions instead of appending: write to a date partition and replace it atomically.
• MERGE or upsert on a natural key rather than a blind INSERT.
• Deduplicate by a deterministic event id with a unique constraint.
• Use deterministic run identifiers and write to temporary tables before swapping.

MERGE INTO sales t
USING staging s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount);

Test idempotency by rerunning the same task and confirming row counts and checksums stay identical. It is the foundation of safe retries and reliable backfills.

Dimensions change over time: a customer moves city, a product changes category. A slowly changing dimension strategy decides how history is preserved.

• Type 1: overwrite the old value. Simple, but history is lost.
• Type 2: add a new row per change with valid_from and valid_to dates plus a current flag. This preserves full history and supports point-in-time reporting.
• Type 3: keep a previous_value column, limited to one prior state.
• Type 4 and 6: hybrids using history tables or current plus historical rows.

Type 2 is the most common for analytics. A fact row stores the dimension surrogate key valid when the event occurred, so joining facts to the correct historical version is automatic.

SELECT * FROM dim_customer
WHERE customer_id = 42 AND is_current = TRUE;

The downside is that the dimension grows and merge logic is more complex.

The medallion architecture organizes a lakehouse into three refinement layers.

• Bronze (raw): data landed as-is from sources, append-only, with ingestion metadata. It is the source of truth and enables replay.
• Silver (validated): cleaned, deduplicated, conformed data, joined into entities and typed correctly. Analysts and engineers build on this layer.
• Gold (curated): business-level aggregates and marts optimized for BI, dashboards and machine learning.

Each layer is a table, often in Delta Lake or Iceberg, and transformations move data bronze to silver to gold. Benefits include clear ownership, incremental processing, and the ability to rebuild downstream layers from raw data. Costs come from storing multiple copies and maintaining transformation jobs. Keep schemas explicit with a catalog or data contract so consumers can rely on silver and gold tables.

Late data breaks the assumption that all events for a time window have arrived. Common strategies:

• Watermarks: define how long to wait for late events before closing a window. A ten-minute watermark holds windows open ten minutes past the latest event time. Events after that are dropped or routed separately.
• Allowed lateness: keep window state longer and update results when late events arrive, emitting revised aggregates downstream.
• Reprocessing: store raw immutable events in the lake, then rerun the affected partition to correct results. Idempotent writes and partitioning by event date make this safe.
• Reconciliation: combine a streaming near-real-time view with a batch correction that overwrites the same partitions.

The right choice balances latency, cost and correctness. Billing pipelines usually favor a batch correction layer, while dashboards may accept approximate streaming results.

CDC captures row-level changes from a source database and streams them downstream with low latency. A typical design:

• Capture: read the database transaction log with a tool like Debezium on the MySQL binlog or Postgres logical replication, rather than polling, so you get every insert, update and delete without load on the source.
• Transport: publish change events to Kafka, keyed by primary key to preserve per-entity ordering.
• Process: consume events, land them in a bronze table, then merge into silver entities. Handle deletes, schema evolution and ordering.
• Serve: expose current state and history for analytics.

{"op":"u","before":{"id":42,"city":"Paris"},"after":{"id":42,"city":"Lyon"}}

Considerations: initial snapshot plus streaming handoff, idempotent merges, tombstones for deletes, and monitoring replication lag.