Data Engineering Interview Questions

Summary

ETL transforms data in a separate process before loading it; ELT loads raw data into the warehouse first and transforms it there with SQL. ELT is the usual choice with modern cloud warehouses because it keeps raw data and uses scalable warehouse compute; ETL still fits when data must be cleaned, masked or reduced before it lands.

Longer Answer

Keeping raw data means new questions and bug fixes can be answered by recomputing from tables already in the warehouse, without re-extracting history from production. Transforming before loading remains right for removing personal data that must never reach the warehouse, decoding binary formats, or targets that can't run transformations (operational databases).

Key Points

• ETL: transform, then load.
• ELT: load raw, transform in the warehouse.
• ELT keeps history recomputable.
• Pre-load transforms for privacy and format decoding.

Deep dive: ETL and Data Pipelines: ETL vs ELT

Summary

Start from a freshness requirement agreed with the data's consumers, then choose the simplest style that meets it: daily or hourly batch, micro-batch every few minutes, or true streaming only when decisions need seconds (fraud checks, live pricing, alerting).

Longer Answer

Streaming adds always-on infrastructure, state and checkpoint management, harder reprocessing, late-event handling and more on-call load. Many platforms combine both: a stream for a few low-latency use cases and batch for everything else, often fed from the same change data capture feed.

Key Points

• Turn 'real-time' into a freshness target.
• Prefer batch, then micro-batch, then streaming.
• Streaming costs complexity and on-call load.
• Mixed architectures are common.

Deep dive: ETL and Data Pipelines: Batch, Micro-Batch or Streaming?

Summary

Track a watermark (for example the highest updated_at loaded) and extract rows newer than it. It can miss rows from transactions that commit after the extract with an older timestamp, rows with equal timestamps, clock-skewed writes and hard deletes. Use an overlap window with idempotent upserts, log-based CDC, and regular reconciliation.

Longer Answer

updated_at is set when a row is written but visible only when its transaction commits, so a slow transaction can land behind the watermark. Re-reading a lookback window longer than the longest transaction fixes most gaps as long as the load upserts by primary key. CDC avoids the problem by reading changes in commit order.

Key Points

• Watermark on updated_at or an increasing ID.
• Late commits and deletes are missed.
• Overlap window + upsert.
• Reconcile with the source regularly.

Deep dive: ETL and Data Pipelines: Incremental Extraction and Watermarks

Summary

CDC captures every insert, update and delete from a source database. Log-based CDC reads the database's replication log (PostgreSQL WAL via logical decoding, MySQL binlog) and emits changes in commit order, including deletes, with little load on the source. Polling queries see only current rows, miss deletes and intermediate updates, and repeatedly scan the primary.

Longer Answer

Tools such as Debezium or managed connectors take an initial snapshot, then stream change events (usually to a broker), tracking the log position to resume exactly. Consumers apply changes idempotently and in order per key. Risks: replication slots retaining WAL and filling the source disk if consumers stop, and schema changes flowing through the stream.

Key Points

• Reads the database's own change log.
• Captures deletes and every update.
• Low source load, commit order.
• Monitor slot lag and WAL retention.

Deep dive: ETL and Data Pipelines: Change Data Capture for Pipelines

Summary

Running it more than once produces the same final state as running it once. Achieve it with upserts or MERGE on a declared key, overwriting whole partitions, or loading into staging and swapping atomically, never with blind appends of derived data.

Longer Answer

Retries are guaranteed: jobs fail after writing but before recording success, orchestrators retry, people rerun by hand, and backfills reprocess history. Each target table needs a declared grain (key) and a uniqueness test so duplicates are detected immediately.

Code Example

INSERT INTO daily_revenue (day, revenue)
SELECT order_date, SUM(amount) FROM fct_order_lines WHERE order_date = :day GROUP BY order_date
ON CONFLICT (day) DO UPDATE SET revenue = excluded.revenue;   -- safe to rerun

Key Points

• Same result however many times it runs.
• Upsert/MERGE by key or partition overwrite.
• Staging + atomic swap.
• Uniqueness tests on the grain.

Deep dive: ETL and Data Pipelines: Idempotent Loads

Summary

Require a producer-assigned unique event ID and keep one row per ID, for example with ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) = 1 or a MERGE keyed on the ID. 'Exactly-once' in practice means at-least-once delivery plus idempotent or transactional processing.

Longer Answer

Brokers and webhooks redeliver after lost acknowledgements, so duplicates are normal. For huge tables, deduplicate incrementally within a bounded window. Without producer IDs, a composite key from business fields is a fallback that may merge genuinely distinct events.

Code Example

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
  FROM raw_events
) WHERE rn = 1;

Key Points

• Duplicates come from at-least-once delivery.
• Deduplicate on a producer-assigned ID.
• Window functions or MERGE.
• Exactly-once = at-least-once + idempotent sink.

Deep dive: ETL and Data Pipelines: Deduplication and the Exactly-Once Myth

Summary

A star schema has central fact tables (measurable events at a declared grain, such as order lines) surrounded by dimension tables (customers, products, dates) joined many-to-one. It makes queries simple and fast and prevents join fan-out errors.

Longer Answer

Facts hold numeric measures and foreign keys; dimensions hold descriptive attributes, denormalised for easy filtering and grouping. The approach comes from Ralph Kimball's dimensional modelling. Wide "one big table" models are an alternative in columnar warehouses, provided their grain is explicit.

Key Points

• Facts at a declared grain, dimensions many-to-one.
• Simple, fast, correct queries.
• Kimball dimensional modelling.
• OBT is fine if the grain is explicit.

Deep dive: ETL and Data Pipelines: Star Schemas and Grain

Summary

The grain is what one row represents (one order, one order line, one daily total). Fan-out happens when you join a coarser table to a finer one: the coarse rows repeat for every match, so summing a coarse measure double-counts.

Longer Answer

Example: order totals joined to order lines repeats each order's total per line. Prevent it by declaring and testing every table's grain, joining facts to dimensions many-to-one, and computing measures at their own grain or allocating them down with an agreed rule.

Code Example

-- wrong: order-level total repeated per line
SELECT SUM(t.total) FROM order_totals t JOIN order_items i ON i.order_id = t.order_id;
-- right: sum at the line grain
SELECT SUM(i.quantity * i.unit_price) FROM order_items i;

Key Points

• Grain = what one row means.
• Coarse-to-fine joins repeat rows.
• Summing repeated measures double-counts.
• Test grains and reconcile totals.

Deep dive: ETL and Data Pipelines: Star Schemas and Grain

Summary

Slowly changing dimensions describe how dimension attributes keep history. Type 1 overwrites values (no history); Type 2 inserts a new row per change with a surrogate key and validity range, so facts join to the version valid at the time of the event.

Longer Answer

Use Type 1 for corrections and attributes whose history doesn't matter, Type 2 for attributes that affect historical reports (region, segment). Type 2 tables need tests for one current row per key and non-overlapping ranges. dbt snapshots implement Type 2 automatically.

Key Points

• Type 1: overwrite.
• Type 2: new row with valid_from/valid_to.
• Choose per attribute with the business.
• dbt snapshots build Type 2.

Deep dive: ETL and Data Pipelines: Slowly Changing Dimensions

Summary

dbt manages SQL transformations inside the warehouse as code: each model is a SELECT, dependencies are declared with ref() and source(), and dbt builds a DAG, runs models in order, tests them, supports incremental models and generates documentation with lineage.

Longer Answer

It replaces folders of scripts run in arbitrary order with version-controlled, reviewed, tested models across dev, CI and production environments. A common layering is staging → intermediate → marts. Extraction, loading and scheduling are handled by other tools.

Key Points

• Transformations as SELECT models.
• ref()/source() build the DAG.
• Tests, docs and lineage.
• Incremental models for large tables.

Deep dive: ETL and Data Pipelines: Managing SQL Transformations with dbt

Summary

Orchestrators run tasks when their dependencies succeed (not at fixed times), retry failures, stop downstream tasks and alert on errors, track runs by logical date for easy reruns and backfills, limit concurrency, and provide history and a UI.

Longer Answer

Cron time gaps assume upstream steps finish in time; on slow or failed nights downstream steps silently process partial or stale data. Keep business logic in tested tasks (SQL, dbt, Python) and keep the orchestrator thin. Data-aware scheduling (Airflow datasets/assets, Dagster assets) triggers pipelines when inputs update.

Key Points

• Dependency-driven, not clock-driven.
• Retries, alerts, stop on failure.
• Logical dates make backfills routine.
• Keep orchestration thin.

Deep dive: ETL and Data Pipelines: Orchestration

Summary

Schema checks, uniqueness and not-null on the grain key, not-null and range checks on key measures, accepted values, referential integrity, freshness and volume against history, and reconciliation of totals with an independent source. Critical failures block publication; others warn the owner.

Longer Answer

Tools: dbt tests, Great Expectations, Soda, or data observability platforms. Start with a small set that would have caught past incidents and add checks after each new incident to keep noise low.

Key Points

• Grain uniqueness and key not-nulls.
• Freshness and volume.
• Ranges, accepted values, relationships.
• Reconcile with an independent source.

Deep dive: ETL and Data Pipelines: Data Quality Checks and Data Contracts

Summary

Monitor the data: freshness (age of newest data against an SLO), volume (rows versus history and day-of-week patterns), completeness and distributions (null rates, key metrics), schema changes, and test results. Route alerts to dataset owners with runbooks and block publishing on critical failures.

Longer Answer

Jobs succeed on empty files, stale sources and NULL columns. Data SLOs agreed with consumers turn monitoring into commitments; dashboard banners showing "data delayed" keep users informed.

Key Points

• Freshness, volume, distributions, schema.
• Data SLOs with owners.
• Block publishing on critical failures.
• Communicate delays to users.

Deep dive: ETL and Data Pipelines: Monitoring Freshness, Volume and Data SLOs

Summary

Reconcile day by day to localise the gap, then test the usual causes: join fan-out (order totals joined to lines or payments), duplicate events or rows from retries and reruns, refunds and cancellations counted as revenue, currency handling, and date basis (order date vs settlement date).

Longer Answer

Check uniqueness of every table's grain, count duplicate event IDs, compare gross and net revenue, and inspect days with the largest differences. Fix root causes with idempotent loads, deduplication and explicit grains, then add a daily reconciliation that blocks publication.

Key Points

• Localise with daily reconciliation.
• Check fan-out and duplicates.
• Refunds and date basis.
• Prevent with grains, idempotency and reconciliation.

Deep dive: ETL and Data Pipelines: Capstone: The Revenue Report That Didn't Match

Summary

Partition by event time and recompute a lookback window (for example the last 3 days) on every run with idempotent partition overwrites, sizing the window from measured arrival delays. Label recent numbers provisional, run wider periodic corrections, and use adjustment records where published numbers must not change.

Longer Answer

Event time (when it happened) differs from arrival time, especially for mobile devices, partners and retried webhooks. Measure the delay distribution and choose a window that covers the vast majority; rare stragglers can be handled by weekly corrections or a late-arrivals table.

Key Points

• Event time vs arrival time.
• Recompute a lookback window idempotently.
• Size it from measured delays.
• Provisional vs final numbers.

Deep dive: ETL and Data Pipelines: Late and Out-of-Order Data

Summary

A warehouse (Snowflake, BigQuery, Redshift) manages storage and SQL compute for structured data. A data lake stores raw files of any format cheaply in object storage. A lakehouse adds a table format (Iceberg, Delta Lake, Hudi) on top of lake files, giving tables, ACID transactions, schema evolution and time travel, queryable by several engines.

Longer Answer

Choose a warehouse for simplicity and strong SQL performance; a lakehouse for open formats, very large or varied data, machine-learning workloads and multiple engines on the same data. Many organisations use both.

Key Points

• Warehouse: managed SQL storage and compute.
• Lake: cheap raw files.
• Lakehouse: table formats over lake files.
• Choose by simplicity vs openness.

Deep dive: ETL and Data Pipelines: Warehouses, Lakehouses and Scan Cost

Summary

Minimise what you load, keep personal attributes in one restricted identity table, use pseudonymous keys elsewhere, tag personal columns and track lineage, apply column-level access control and masking, automate deletion requests, and set retention periods for snapshots, exports and backups.

Longer Answer

Under the GDPR, pseudonymised data is still personal data, while properly anonymised data is not. Keyed hashes (HMAC with a secret) are safer pseudonyms than plain hashes, which can be reversed by dictionary attack.

Key Points

• Minimise and separate identity.
• Pseudonymous keys (HMAC).
• Tag, mask and restrict access.
• Automated deletion and retention.

Deep dive: ETL and Data Pipelines: Personal Data, Privacy and Retention

Summary

Likely an empty or partial input (empty export, upstream outage), a renamed or missing column loaded as NULL, or a filter/date bug. Prevent it with volume and freshness monitors, schema and not-null checks on key measures, inputs that fail loudly when empty, and blocking publication on critical test failures.

Longer Answer

Job success only means the code ran. Add data SLOs with owners and show 'data delayed' on dashboards when checks fail.

Key Points

• Empty inputs and schema changes don't error.
• Volume, freshness and null checks.
• Fail loudly on empty inputs.
• Block publication on critical failures.

Deep dive: ETL and Data Pipelines: Monitoring Freshness, Volume and Data SLOs

Summary

Trace both metrics through lineage to their definitions: different filters (cancelled orders, test accounts), grains, time zones or date bases, refresh times, or fan-out bugs. Agree one definition with the business, implement it once in a shared model or semantic layer, and point both dashboards at it.

Longer Answer

Prevent repeats with a single source of truth for key metrics (a metrics/semantic layer or governed marts), documented definitions and reconciliation tests.

Key Points

• Compare definitions via lineage.
• Check filters, grain, time zones and freshness.
• One shared definition.
• Semantic layer or governed marts.

Deep dive: ETL and Data Pipelines: Star Schemas and Grain