Interview preparation

Data Engineering Interview Questions and Answers

Practical questions for data engineers, from pipeline fundamentals to production architecture, reliability and system design.

  • 20 questions
  • Beginner → Senior
  • Production-focused

Data engineering interviews test whether you can build pipelines that keep producing correct data when sources change, jobs fail and volumes grow. The answers are vendor-neutral, with Spark, Delta Lake, SQL Server or Microsoft Fabric as examples, and each is written to be spoken in your own words: a short answer, why it matters in production, what a strong answer usually demonstrates, the traps to avoid, and where to go deeper.

Ce guide est disponible en anglais.

Data engineering interview preparation workspace with pipelines, Lakehouse layers, orchestration, monitoring and production architecture.

At a glance

How the levels work

Q01BeginnerData Engineering Fundamentals

What does a data engineer actually do?

Short answer

A data engineer builds and runs the systems that turn raw data into data people can trust and use. That covers ingestion from applications, files, APIs and databases; transformation into clean, consistent structures; storage in formats and layouts that fit how the data is read; and orchestration, so each step runs in the right order and recovers from failure.

The work does not stop when data lands. A data engineer builds in data quality checks, makes pipelines reliable and rerunnable, and serves the results to the people and systems that consume them: BI reports, analysts, data scientists, machine learning features, other applications. Then they operate it in production: monitoring, troubleshooting, controlling cost and handling schema changes.

Why it matters in production

Every report and model depends on this work. When a pipeline silently drops rows or loads yesterday’s data twice, the people who notice are usually the business users, and trust is lost quickly.

What the interviewer is looking for

A strong answer usually demonstrates that you see the role as owning data as a product: correct, timely, documented and observable, not just transported.

Watch out

Reducing the role to “moving data from A to B.” Copying is the easy part; the job is everything that makes the copy correct, repeatable and useful. Also avoid describing only tools (“I write Spark jobs”): tools change, responsibilities do not.

Back to all questions ↑

Q02BeginnerData Engineering Fundamentals

What is the difference between ETL and ELT?

Short answer

The difference is where the transformation happens. In ETL, data is transformed in a separate processing layer before it’s loaded into the target. In ELT, raw data is loaded first and transformed inside the target platform, usually with SQL. ELT became common because cloud platforms store raw data cheaply and scale compute. Neither is universally better, and most real platforms are hybrid.

Why it matters in production

The choice decides where compute costs land, where sensitive data is stored, how easy it is to replay history, and which team owns transformation logic. Keeping the raw copy, as ELT does, makes it possible to reprocess history when logic changes.

What the interviewer is looking for

A strong answer usually demonstrates that you choose based on constraints (security, cost, volume, tooling, skills), not on fashion. ETL still makes sense when data must be filtered or masked before it reaches the platform (sensitive fields, compliance), when the target is not suited to heavy transformation, or when volume should be reduced early. A typical hybrid does light cleanup on the way in and heavier modeling inside the platform.

Watch out

Presenting ELT as “modern” and ETL as “obsolete.” Loading raw sensitive data into a shared platform without a reason can be a compliance problem, and transforming everything inside an expensive warehouse is not always cheaper.

Back to all questions ↑

Q03BeginnerData Engineering Fundamentals

What is the difference between batch and streaming data processing?

Short answer

Batch processing works on a bounded set of data: yesterday’s orders, this hour’s files. A job starts, processes everything in that set, and finishes. Streaming processes an unbounded, continuous flow of events as they arrive, and the job keeps running.

The main trade-off is latency against complexity and cost. Batch is simpler to build, test, rerun and reason about, and it is efficient for large volumes; results arrive minutes or hours later. Streaming delivers results in seconds or less, but it must handle out-of-order and late events, state, exactly-once or at-least-once guarantees, and an always-on service to operate.

Many “streaming” systems are actually micro-batch: they process small batches every few seconds or minutes, which is often all the business needs.

Why it matters in production

Choosing streaming for a daily report adds cost and operational burden for no benefit. Choosing batch for fraud detection or live inventory makes the result useless.

What the interviewer is looking for

A strong answer usually demonstrates that you start from the latency requirement (“how fresh does this need to be, and what happens if it is late?”) and pick the simplest design that meets it.

Watch out

Saying streaming is always real-time. End-to-end latency also depends on sources, sinks and consumers; a stream that feeds a report refreshed hourly is not real-time to the user. Also avoid treating batch as legacy: most analytical workloads are still batch for good reasons.

Back to all questions ↑

Q04BeginnerData Engineering Fundamentals

What is a data pipeline?

Short answer

A data pipeline is a repeatable process that takes data from one or more sources, moves it, transforms it, validates it and delivers it to a destination where someone uses it. A complete pipeline also has orchestration (when it runs, in what order, what happens on failure, what depends on it) and monitoring (did it run, did it succeed, is the output correct and on time).

For example: every night, read new orders from an operational database, land them as files, clean and deduplicate them, join them to customers and products, check row counts and key rules, load a reporting table, and record the run in an execution log.

Why it matters in production

The copy step is rarely what breaks. Pipelines fail because a source schema changed, a file arrived late, a rerun loaded data twice, or a check was missing and wrong data reached a dashboard. Those are design concerns, not copy concerns.

What the interviewer is looking for

A strong answer usually demonstrates that you think about a pipeline as a product with guarantees: inputs, outputs, expectations, failure behavior and ownership.

Watch out

Describing a pipeline as a chain of copy activities. A pipeline without validation, rerun behavior and monitoring is a script that works until the first bad day.

Back to all questions ↑

Q05WorkingIncremental Processing & CDC

How would you design an incremental data load?

Short answer

I’d process only what changed since the last successful run. First, pick the most reliable change signal the source offers: a watermark column, CDC or Change Tracking, or new files. Each run reads a bounded window, from the stored watermark up to an upper bound captured at the start. It writes with a merge on a business key, so loading the same window twice gives the same result. And it moves the watermark only after the write and checks succeed. If data can arrive late, I re-read a small overlap and let the merge deduplicate.

Why it matters in production

Full reloads become too slow and too expensive as data grows. A careless incremental load is worse, though: it silently skips rows or double-counts them, and nobody notices until the numbers are questioned.

What the interviewer is looking for

A strong answer usually walks through four decisions:

  1. How do I detect change? A watermark column (a modified_at timestamp or an ever-increasing key) for sources that update it reliably; CDC or Change Tracking when the database provides them, especially when deletes matter; file arrival for file-based sources, tracking which files have been processed.
  2. What window do I read? A bounded window with a fixed upper bound (for example, the source’s current maximum, or the run start time minus a safety margin), so rows written during the run are picked up next time instead of being half-read.
  3. How do I write? A MERGE/upsert on a business key, or a whole-partition replace, which makes the load idempotent.
  4. When do I move the watermark? Only after the target write and validation succeed, recorded with the run. If the run fails, the watermark stays put and the next run reprocesses the same window safely.

It also covers late-arriving records (an overlap window plus merge) and deletes.

Watch out

  • Trusting modified_at without checking how the source sets it. Clock differences, bulk updates that skip the column, or long-running transactions committing older timestamps can all cause missed rows.
  • Using >= versus > without thinking: one risks duplicates, the other risks gaps when several rows share the boundary value. Overlap plus merge handles both.
  • Advancing the watermark at the start of the run.
  • Forgetting deletes: a watermark does not see rows that disappeared.

Example

-- 1. Bounded window, captured once at the start of the run
SET @from = (SELECT last_value FROM etl.watermark WHERE source = 'orders');
SET @to   = (SELECT MAX(modified_at) FROM src.orders);

-- 2. Read rows in (@from - overlap, @to] and MERGE them on order_id
-- 3. Only after the merge and checks succeed:
UPDATE etl.watermark SET last_value = @to WHERE source = 'orders';

Back to all questions ↑

Q06WorkingIncremental Processing & CDC

What is CDC, and when would you use it instead of a watermark?

Short answer

CDC, change data capture, records every insert, update and delete on a source, usually from the transaction log, in commit order. A watermark only finds rows whose timestamp or key moved past the last value. That’s simpler, but it can’t see deletes, depends on the source maintaining the column, and only sees the latest state of a row. I’d use CDC when deletes or intermediate changes matter, or when there’s no reliable change column. Otherwise a watermark is often enough.

Why it matters in production

CDC has real costs: it must be enabled on the source, change data has a retention period (if the consumer falls behind or is down too long, changes are lost and a full resync is needed), and schema changes can require reconfiguration. Ordering and transaction boundaries also have to be respected when applying changes.

What the interviewer is looking for

A strong answer usually demonstrates that you weigh completeness against operational complexity. CDC fits when:

  • deletes must be propagated;
  • the source has no reliable change column, or adding one is not possible;
  • intermediate changes matter (audit, history, slowly changing dimensions);
  • the source supports it and the team can operate it, including monitoring lag and retention.

A watermark fits when a reliable column exists, deletes are not needed (or are soft deletes), and simplicity matters more.

Watch out

CDC is not the same thing as replication (keeping a full copy in sync, which may use CDC internally) or event streaming (application-level events published to a message broker). Also avoid assuming CDC is “free”: it adds load and storage on the source.

Back to all questions ↑

Q07SeniorIncremental Processing & CDC

How do you handle late-arriving data in an incremental pipeline?

Short answer

First I separate event time, when something happened, from processing time, when the pipeline saw it, and I find out how late the source can deliver data. Where possible I read incrementally by arrival or commit time, so late events aren’t skipped. Then each run re-reads an overlap window sized from observed lateness, and writes with a merge on a business key so the re-reads are harmless. Late rows that land in older periods must also correct those partitions and the aggregates built on them. Data that arrives weeks late gets a planned backfill.

Why it matters in production

Late data is how “correct” pipelines produce wrong numbers: yesterday’s total is published, then more of yesterday’s sales arrive, and the report never changes. Downstream aggregates and snapshots must be refreshed, not just the base table.

What the interviewer is looking for

A strong answer usually demonstrates that you measure lateness, design for a stated tolerance, keep the watermark safe, and make corrections flow downstream. The main techniques:

  • Overlap windows. Each run re-reads a margin before the last watermark (an hour, a day, sized from observed lateness). Combined with a merge on a business key, re-reading is harmless and late rows are picked up.
  • Watermark on ingestion time, not event time, where possible. If the source provides an arrival or commit timestamp, incremental reads based on it do not miss late events; event time is then used only for partitioning and reporting.
  • Deduplication by business key and a version or sequence column, keeping the latest version, because overlaps and retries will deliver some rows twice.
  • Partition correction. If data is partitioned by event date, late rows must update older partitions. Either merge into them or rebuild affected partitions, and track which partitions changed so downstream aggregates are refreshed too.
  • Reprocessing. For data that arrives very late (days or weeks), a scheduled backfill for a date range is clearer than an ever-growing overlap.

Watch out

  • Treating the maximum event time as the watermark. One early or future-dated record can push it past rows that have not arrived yet.
  • Overlap without merge, which creates duplicates.
  • Assuming the source is ordered because it usually is.

Example

A mobile app uploads offline sales when phones reconnect, sometimes three days late. The pipeline reads by upload time, merges on sale_id, partitions by sale date, records which sale dates changed, and refreshes only those days of the daily sales aggregate.

Back to all questions ↑

Q08WorkingReliability & Idempotency

What does idempotency mean in a data pipeline?

Short answer

A step is idempotent when running it again with the same input leaves the same end state as running it once. If yesterday’s load runs twice, the target should look as if it ran once: no doubled rows or totals. I get there with merges on deterministic business keys, or by replacing a whole partition or window instead of appending. I also deduplicate sources that can resend records, and record which window each run processed.

Why it matters in production

Failures and retries are normal. Orchestrators retry failed activities automatically, and operators rerun jobs after fixing problems. If reruns are not safe, every failure turns into a manual cleanup and a risk of wrong numbers.

What the interviewer is looking for

A strong answer usually demonstrates that you design for reruns from the start and know which operations are naturally idempotent (overwrite, merge) and which are not (append, incrementing counters, sending emails). The common techniques:

  • MERGE/upsert on a deterministic business key instead of plain INSERT.
  • Replace a whole partition or window (delete then insert in one transaction, or overwrite a partition) instead of appending.
  • Deterministic keys derived from source data, not from a sequence or a random value generated at run time.
  • Deduplication on the way in, for sources that can deliver the same record more than once.
  • Execution metadata recording which window or files a run processed, so a rerun knows what it is replacing.

Watch out

  • Idempotency does not mean “the pipeline never writes duplicates” unless the design guarantees it, for example with a unique key the merge enforces. Saying “it’s idempotent” about an append job is a common mistake.
  • Side effects such as sending notifications, calling external APIs or publishing messages are usually not idempotent; they need their own guard, such as recording what has been sent.

Back to all questions ↑

Q09SeniorReliability & Idempotency

How would you make a pipeline safely rerunnable after a partial failure?

Short answer

The pipeline has to know exactly what it was processing, and every step has to be either atomic or safe to repeat. So each run gets an execution ID and a fixed source window recorded at the start, and a rerun processes that same window. Data goes to staging first, gets validated, and is published in one atomic step, such as a merge, a partition overwrite or a table swap. Writes are idempotent, and watermarks only move after publish succeeds. After the rerun, I reconcile counts before consumers refresh.

Why it matters in production

The most damaging failures are the quiet ones: half of a load committed, the watermark moved, and the rerun skips the missing half. Nobody sees an error, but the data is wrong.

What the interviewer is looking for

A strong answer usually demonstrates that you think in terms of atomic boundaries, recorded state and replay, and can explain what happens if the job dies at any point. The design elements:

  • Fixed source windows. Each run is defined by an explicit window (watermark range, list of files, business date) recorded at the start. A rerun reprocesses the same window, not “whatever is new now”.
  • Execution IDs. Every run gets an execution_id, written to the execution log and, where useful, to the rows it writes, so you can find and remove or replace what a failed run wrote.
  • Staging, then publish. Write to a staging table or path, validate it, then publish atomically: a transaction, a MERGE, a partition overwrite or a table swap. A failure before publish leaves the target untouched.
  • Idempotent writes. Merge or replace by key or partition, so repeating a step that partly completed causes no duplicates.
  • Checkpoints per step. Record which steps finished for an execution, so a rerun can skip completed steps or safely repeat them.
  • Advance state last. Move watermarks and mark files as processed only after the publish succeeds.
  • Validate downstream. Reconcile row counts and key totals against the source window before consumers refresh.

Watch out

  • “Just rerun it” without knowing whether the first attempt partly wrote data.
  • Relying on automatic retries for non-idempotent steps, which can double-process.
  • Using “new data since now” as the rerun window, which silently changes what the rerun covers.

Back to all questions ↑

Q10WorkingStorage & File Formats

Why is Parquet commonly used in analytics systems?

Short answer

Parquet is an open, columnar file format: it stores each column’s values together instead of each row. That fits analytics, which usually reads a few columns across many rows. Queries read only the columns they need, similar values compress very well, and per-row-group statistics let engines skip data that can’t match a filter. The schema travels with the file, and almost every engine reads it. It’s a poor fit for single-row lookups and frequent small writes.

Why it matters in production

Choosing Parquet over CSV or JSON for analytical storage often cuts storage and scan time dramatically, which directly lowers cost and speeds up reports.

What the interviewer is looking for

A strong answer usually demonstrates that you connect the format’s design to access patterns:

  • Column pruning: a query reading 3 of 80 columns reads only those 3.
  • Compression and encoding: dictionary and run-length encoding, plus codecs such as Snappy or ZSTD, make files smaller and faster to scan.
  • Predicate pushdown: each row group stores min/max statistics per column, so engines can skip row groups that cannot match a filter.
  • Schema in the file: column names and types travel with the data, unlike CSV.
  • Broad support: Spark, warehouses, lakehouse engines, pandas and many others read it.

Watch out

  • Parquet is a file format, not a database: it has no transactions, no indexes in the database sense, and no in-place updates. Changing one row means rewriting a file, which is why table formats such as Delta Lake add a layer on top.
  • Its benefits depend on layout: many tiny files or row groups lose much of the advantage.

Back to all questions ↑

Q11WorkingStorage & File Formats

What does Delta Lake add on top of Parquet?

Short answer

Delta Lake is a table format. The data is still Parquet, but a transaction log records every change as an ordered commit, and readers use that log to know which files make up the current version. That log is what gives you ACID transactions, conflict detection between writers, schema enforcement, time travel, and row-level MERGE, UPDATE and DELETE. Parquet files themselves never change: updates write new files and mark the old ones as removed.

Why it matters in production

Without a table format, a failed job can leave a folder with half its files written, and concurrent writers can overwrite each other. Delta makes lake storage behave much more like a reliable table, which is what makes lakehouse architectures practical.

What the interviewer is looking for

A strong answer usually demonstrates that you know the transaction log (the _delta_log folder) is the core idea and can explain what it enables:

  • ACID transactions: a write either commits completely or not at all, and readers never see half-written data.
  • Concurrency control: optimistic concurrency detects conflicting writers instead of letting them corrupt the table.
  • Schema enforcement and evolution: writes with mismatched columns are rejected unless schema evolution is explicitly allowed.
  • Time travel and history: you can query or restore earlier versions, within the retention period.
  • MERGE, UPDATE and DELETE: implemented by rewriting affected files and committing the change.
  • Metadata and statistics in the log, which help engines skip files.

Watch out

  • Saying Delta “updates Parquet files in place.” Parquet files are immutable; Delta writes new files and marks old ones as removed.
  • Assuming time travel is unlimited: VACUUM deletes old files after the retention period, which ends the ability to read those versions.
  • Treating Delta as a database server: it is a storage format; performance still depends on file layout and maintenance.

Back to all questions ↑

Q12SeniorStorage & File Formats

What causes the small-file problem, and how would you address it?

Short answer

It happens when a table is made of huge numbers of files much smaller than the engine reads efficiently. Every file has a fixed cost to list, open and track in metadata, so that overhead starts to dominate the actual reading. The usual causes are frequent small appends or streaming writes, partitioning on a high-cardinality column, and too much write parallelism. I’d measure file counts and sizes first, then fix the write pattern and partitioning, and compact the affected partitions on a schedule, with cleanup of old files afterwards.

Why it matters in production

Small files slow reads, slow writes that must scan metadata, increase storage request costs, and can overload drivers or metadata services. It is one of the most common reasons a lakehouse gets slower over time.

What the interviewer is looking for

A strong answer usually demonstrates that you treat compaction as a symptom fix and also correct what caused it.

Common causes:

  • Frequent small writes: streaming or micro-batch jobs, or appends every few minutes, each producing new files.
  • Over-partitioning: partitioning on a high-cardinality column (customer, hour, ID), so each partition holds a little data.
  • High write parallelism: many tasks each writing one small file per partition.
  • Row-level updates and merges, which rewrite files and can fragment them.

How to address it:

  1. Measure first: file count and size distribution per table and partition (target sizes are often in the hundreds of megabytes, depending on the engine), and whether queries are actually slow because of it.
  2. Fix the cause: batch writes more, reduce write parallelism or repartition before writing, and choose partition columns with low cardinality and real filter value.
  3. Compact: run compaction (for Delta, OPTIMIZE) on the partitions that need it, on a schedule that matches how fast files accumulate. Some platforms also offer automatic or optimized writes.
  4. Clean up: old files remain after compaction for time travel, so run VACUUM within a retention policy that matches recovery needs.

Watch out

Compacting blindly: rewriting entire large tables on every run wastes compute and can conflict with concurrent writers. Compact recent or affected partitions, and do not lower VACUUM retention just to save space quickly.

Back to all questions ↑

Q13WorkingData Modeling & Architecture

What is the Medallion architecture, and when would you avoid it?

Short answer

It’s a layered way to organize a lakehouse by quality and purpose. Bronze keeps raw data as received, for replay and audit. Silver is cleaned, deduplicated and conformed, the trusted reusable layer. Gold holds curated outputs for specific consumers, such as dimensional models or aggregates. The value is separation of responsibilities. I’d simplify or skip it for small workloads, for data that’s already clean and modeled at the source, or for low-latency cases where extra hops add delay.

Why it matters in production

Clear layers make reprocessing, debugging and ownership much easier. Unclear layers create the opposite: duplicated logic, nobody sure which table is the trusted one, and storage costs growing three times faster than value.

What the interviewer is looking for

A strong answer usually demonstrates that you treat Medallion as a pattern with contracts, not a requirement, and can describe what each layer guarantees:

  • Bronze: raw data as received, append-only where possible, with load metadata. It is the system of record for replay and audit: if logic changes, you can rebuild from here.
  • Silver: cleaned, typed, deduplicated and conformed data. Business keys are consistent and bad records are quarantined.
  • Gold: curated outputs for specific consumers: dimensional models, aggregates, feature tables, data products.

Ingestion problems stay in Bronze, cleaning rules live in one place in Silver, and consumer logic lives in Gold, so each layer can be validated and rebuilt independently. Applying it mechanically, with layers that have no clear contract, is itself a reason to step back.

Watch out

Saying Medallion requires exactly three layers, or that every table must go through all of them. The layer names are conventions; what matters is that each step has a defined purpose and quality guarantee. Small workloads with three copies of the data pay extra cost and latency for no benefit.

Back to all questions ↑

Q14SeniorData Modeling & Architecture

How would you choose between a normalized model, dimensional model and wide analytical table?

Short answer

I choose by who uses the data and how it changes, and often use more than one. A normalized model suits operational systems and integration layers, where data is written often and consistency matters. A dimensional model, facts at a defined grain plus descriptive dimensions, is my default for shared BI and semantic models: predictable, fast to filter and easy for users. A wide table works as a derived output for one consumer, like an ML feature set or a heavy dashboard. It’s fast to read but harder to keep consistent.

Why it matters in production

The wrong model shows up as slow reports, inconsistent numbers between teams, or expensive rebuilds whenever a dimension changes.

What the interviewer is looking for

A strong answer usually demonstrates trade-off thinking about each model:

  • Normalized (3NF) stores each fact once and avoids update anomalies, but analytics then needs many joins, which are harder for users and slower for large scans.
  • Dimensional (star schema) separates facts (measurable events at a defined grain) from dimensions (customer, product, date). Queries are predictable, filters and aggregations are fast, business users understand it, and history can be kept with slowly changing dimensions.
  • Wide, denormalized tables put everything one use case needs in one table. They are fast to read but repeat data, are harder to keep consistent when dimensions change, and tend to multiply into one table per request.

It also asks the right questions: What is the grain? How often do descriptive attributes change, and must history be kept? Is there a semantic layer that expects a star schema? How many consumers share the data? Who maintains it?

Watch out

Presenting one style as universally best. “Just denormalize everything” ignores update cost and consistency; “always 3NF” ignores how analytical engines and BI tools are used. A common sound pattern is a dimensional model as the shared Gold layer, with wide tables derived from it where needed.

Back to all questions ↑

Q15SeniorSystem Design & Scale

Design a data platform that ingests hundreds of files per day and serves BI reporting.

Short answer

I’d first ask about file types, sources, arrival patterns, freshness needs and growth. Then: files land in an immutable landing zone, and each one is registered in a manifest so nothing is processed twice. Raw data goes into an open table format with load metadata. Validation handles schema drift by quarantining bad files instead of failing everything. Incremental merges build a clean layer, and dimensional models serve BI. Around that sit orchestration with retries and backfills, an execution log, quality checks and access control. The key properties are replayability, idempotency and observability.

Why it matters in production

With hundreds of files a day, the hard problems are not the copy: they are duplicate or missing files, schema drift, partial failures and knowing which report numbers came from which file.

What the interviewer is looking for

A strong answer usually demonstrates that you start with requirements, design for replay, idempotency and observability, and keep the design vendor-neutral, naming tools only as examples (for instance, Microsoft Fabric pipelines and lakehouses, or Spark with Delta Lake). A complete design covers:

  1. Landing zone. Files arrive in an immutable area, organized by source and arrival date. Nothing is edited there.
  2. Ingestion and registration. An orchestrated process detects new files (event or schedule), records each in a file manifest (name, size, checksum, arrival time, status), and skips files already processed.
  3. Raw / Bronze. Each file is loaded as-is into a raw table in an open format such as Parquet or Delta, with metadata columns (source file, load time, execution_id).
  4. Validation and schema handling. Check structure against an expected schema per source. Allowed changes (a new optional column) evolve the schema; breaking changes quarantine the file and alert the owner.
  5. Silver. Clean, type, deduplicate and conform data, loaded incrementally with merges on business keys. Data quality checks decide fail, quarantine or warn.
  6. Gold / serving. Dimensional models or aggregates designed for the reports, served through a warehouse, lakehouse SQL endpoint or BI semantic model.
  7. Orchestration. Dependencies, retries with backoff, concurrency limits, and backfill by date range.
  8. Observability. An execution log per run and per file, row counts at each layer, freshness, failures and quality results, with alerts that reach an owner.
  9. Reruns. Any file or day can be reprocessed from Bronze without duplicates.
  10. Scale and cost. Batch small files together, compact tables, partition by date only if it helps queries, and size compute for the daily peak.
  11. Governance and security. Access by layer and role, sensitive columns masked or restricted, lineage and retention policies.

Watch out

Jumping straight to a product list, or designing only the happy path. Say what happens when a file is late, duplicated, malformed or reloaded.

Back to all questions ↑

Q16WorkingData Quality

How do you build data quality checks into a pipeline?

Short answer

I put checks where bad data enters or changes shape, usually after ingestion and before publishing to consumers. They cover schema, required fields, key uniqueness, referential integrity, accepted values, row counts against normal ranges, reconciliation with the source, freshness and a few business rules. Each check has a severity decided in advance: fail before publishing, quarantine the bad rows and load the rest, or warn. Results go to a log with the execution ID, so failures are traceable and trends are visible.

Why it matters in production

Most data incidents are not crashes; they are pipelines that succeed with wrong data. Quality checks turn silent errors into visible ones, before a business user finds them.

What the interviewer is looking for

A strong answer usually demonstrates a range of checks beyond nulls, a clear fail/quarantine/warn policy, and ownership of what happens after an alert.

Typical checks:

  • Schema: expected columns and types are present.
  • Nullability: required fields are filled.
  • Uniqueness: business keys are unique at the expected grain.
  • Referential integrity: facts point to existing dimension members.
  • Accepted values and ranges: status codes, currencies, no negative quantities where impossible.
  • Volume: row counts within an expected range compared with recent runs.
  • Reconciliation: totals or counts match the source for the same window.
  • Freshness: the newest data is recent enough.
  • Business rules: for example, an order’s line totals add up to the order total.

Severities:

  • Fail: stop before publishing, when wrong data would be worse than late data.
  • Quarantine: move bad rows aside with the reason, load the rest, and alert.
  • Warn: record and alert, but continue, for suspicious but possibly valid patterns.

Watch out

  • Reducing data quality to “check for nulls”.
  • Adding hundreds of checks that only warn, which trains everyone to ignore alerts.
  • Checking only the target, so you cannot tell whether the source or your transformation caused the problem.

Back to all questions ↑

Q17SeniorData Quality

A pipeline succeeded, but the numbers are wrong. How do you investigate it?

Short answer

“Succeeded” only means nothing threw an error. First I pin down exactly which metric, period and slice is wrong, and compared with what. Then I use the execution log to find the runs that produced it and the source window they read. I compare row counts layer by layer, from source to report, to find the first place they diverge. At that step I check the usual suspects: joins that drop or duplicate rows, filters, deduplication, aggregation grain, schema drift and late data. I also check whether the report simply hasn’t refreshed. Then I fix it, backfill, and add a check that would have caught it.

Why it matters in production

Silent wrong data is more damaging than a failed job: decisions are made on it, and once trust is lost, users start keeping their own spreadsheets.

What the interviewer is looking for

A strong answer usually demonstrates a systematic, evidence-based trace:

  1. Define “wrong” precisely. “Revenue for 12 October is 8% low in the EU” is investigable; “the numbers are off” is not.
  2. Correlate the run. Find the execution_id, source window, files processed and row counts per step.
  3. Check the source window. A watermark that moved too far, a timezone shift, or a file that arrived after the run can all leave gaps.
  4. Compare row counts layer by layer: source, raw, cleaned, curated, report.
  5. Inspect the transformation at that step:
    • Joins: an inner join dropping rows with missing dimension keys, or a join to a non-unique key duplicating rows.
    • Filters: a condition excluding valid records, or NULL handling in a WHERE or NOT IN.
    • Deduplication keeping the wrong version.
    • Aggregation grain: summing at the wrong level, or double-counting after a join to a lower grain.
  6. Check schema drift. A renamed or retyped source column can turn into nulls or zeros without an error.
  7. Check late data. Records may have arrived after the period was processed and never been merged.
  8. Check downstream refresh. The tables may be right but the semantic model, cache or extract has not refreshed, or refreshed before the load finished.
  9. Fix, backfill and prevent. Correct the logic, reprocess the affected window idempotently, verify against the source, and add a check (reconciliation, uniqueness, volume) that would have caught it.

Watch out

Rerunning the pipeline and hoping, or patching the report. If you do not find the cause, the same wrong numbers return next week.

Back to all questions ↑

Q18WorkingObservability & Operations

What should you monitor in a production data pipeline?

Short answer

I monitor four questions, because each catches a different failure. Did it run at all? Did it succeed, and how many retries did that take? Did it produce correct data, judged by row counts, quality checks and reconciliation? And did it meet expectations for duration and freshness? Around that, I watch source and destination health and compute resources. Alerts should be few, actionable, routed to an owner, and linked to the run’s execution log entry.

Why it matters in production

Many pipeline problems are invisible to “success/failure” monitoring: a job that loads zero rows, runs four times longer than usual, or delivers data six hours late still shows green.

What the interviewer is looking for

A strong answer usually demonstrates monitoring of data and freshness, not only job status, plus baselines and actionable alerting:

  1. Did it run? Scheduled runs that never started, a disabled trigger, a stuck orchestrator. Absence of a run produces no error, so it needs its own alert.
  2. Did it succeed? Failures, error messages, retries (a job that only succeeds on the third attempt is a warning sign), and partial completions.
  3. Did it produce correct data? Row counts compared with normal ranges, data quality results, reconciliation against the source, null or duplicate rates, and schema changes.
  4. Did it meet expectations? Duration against a baseline, freshness of the output (how old is the newest data consumers see), and whether downstream deadlines were met.

Dependencies matter too: source and destination health (connectivity, throttling, source lag such as CDC latency) and resources where relevant (compute capacity, memory, queueing, cost per run).

Watch out

  • Monitoring only failures.
  • Alerting on everything, which leads to alert fatigue.
  • Measuring duration without a baseline, so nobody knows whether 40 minutes is normal.

Back to all questions ↑

Q19SeniorObservability & Operations

How would you design an execution log for a data platform?

Short answer

It’s one table that records every run of every pipeline and step in the same shape, so I can answer what ran, on which data, with what result. Each row has an execution ID, a parent or correlation ID to trace a whole chain, the job and step name, environment, start and end times, and status. It also has the data context: the source window, row counts read and written, retry or rerun details, and the error. I write a row when the run starts and update it at the end, so stuck runs are visible.

Why it matters in production

When numbers are wrong or a run fails, the execution log is the first place to look. It links a report value back to the run, window and files that produced it, and it is the basis for monitoring, SLAs and reruns.

What the interviewer is looking for

A strong answer usually demonstrates traceability (IDs and correlation), data context (windows and counts, not just status), and operational thinking. Core columns:

  • execution_id: unique per run of a pipeline or step.
  • parent_execution_id and a correlation ID: link steps to the parent run and a whole chain of dependent pipelines, so one business load can be traced end to end.
  • Pipeline/job name and step name, plus code version where possible.
  • Environment (dev, test, prod) and trigger type (schedule, manual, rerun, backfill).
  • Start time, end time, duration, status (running, succeeded, failed, skipped).
  • Source window: watermark from/to, business date, or list of files processed.
  • Row counts: read, inserted, updated, deleted, rejected or quarantined.
  • Retry and rerun information: attempt number, and which execution this rerun replaces.
  • Error message and category (source unavailable, schema change, data quality failure, timeout).
  • Target object written, and a small metadata field (JSON) for step-specific details.

Design principles: keep the logging code shared so every pipeline uses it, write the execution_id onto data rows where useful, and keep the log in a queryable store with a retention policy.

Watch out

Logging only success or failure. A log without source windows and row counts cannot explain wrong data, and a log written only at the end cannot show runs that died.

Back to all questions ↑

Q20SeniorSystem Design & Scale

Your data platform worked well at 10 million rows but is struggling at 500 million. What would you investigate?

Short answer

I wouldn’t start by adding compute. I’d find where the time goes and what grew faster than the data. First a baseline: which steps got slower, and since when. Then the usual suspects at that scale: jobs that quietly reprocess full history instead of running incrementally, small files and partitioning that no longer fit, joins that shuffle or fan out, skewed keys, merges that scan the whole target, and spills or memory pressure. I’d also check source limits and whether the serving layer needs aggregates. I fix the biggest bottleneck, measure again, and only then decide whether more compute is worth paying for.

Why it matters in production

Scaling up hides design problems temporarily and raises cost permanently. The next fiftyfold increase will hit the same wall.

What the interviewer is looking for

A strong answer usually demonstrates evidence-driven diagnosis: baselines, finding the dominant step, and connecting symptoms to causes and specific fixes. What to investigate:

  1. Baseline. Which jobs and queries got slower, by how much, and since when? Per-step durations from the execution log or job metrics show whether one step dominates or everything degraded evenly.
  2. Workload shape. Are jobs still incremental, or is something reprocessing full history every run? A full rebuild that took minutes at 10 million rows takes hours at 500 million. This is often the single biggest win.
  3. Files and layout. File counts and sizes, partitioning that no longer fits (too fine, or not aligned with filters), missing data skipping or clustering, compression, and table maintenance.
  4. Joins and shuffles. Large shuffles, joins that should broadcast a small table, joins that fan out because a key is not unique, and repeated scans of the same large table.
  5. Skew. One key (a large customer, a default value, NULL) holding a large share of rows, leaving one task running long after the others.
  6. Merges and updates. MERGE operations scanning the whole target because the match condition cannot prune files or partitions.
  7. Memory and compute pressure. Spills to disk, out-of-memory retries, executors waiting, capacity throttling, or queueing behind other workloads.
  8. Orchestration concurrency. Too many parallel jobs competing for the same compute or source, or too few, leaving resources idle.
  9. Sources and I/O. Extracts limited by the source database, API rate limits or network throughput, and indexes on source tables where incremental queries filter.
  10. Data model and serving. Reports querying detailed tables that should be aggregated, models that join at a grain too fine, or a serving layer that needs pre-computed results.

More compute may well be justified at the end; the point is to know what you are buying.

Watch out

“Scale up the cluster” as the first answer, or a list of tuning settings without measurement. Also watch for complexity that grows faster than row count, such as a join that duplicates rows or a job that reprocesses everything.

Back to all questions ↑

Keep preparing

New questions are added as they are written. Interview questions for other topics live on the interview questions hub. The best preparation is still hands-on: start from the Data Engineering hub, study the design patterns for reliable data platforms, and keep the data engineering cheat sheets close for quick reference.