Data Engineering Interview Questions and Answers

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.