Question 1

An Apache Airflow DAG processes files from an S3 bucket by generating dynamic tasks via Dynamic Task Mapping. When a batch contains 10,000 files, the Airflow scheduler experiences severe metadata database locking and task queuing delays. How do you batch dynamic tasks into chunks and tune scheduler parsing configurations to prevent worker starvation?

Accepted Answer

The problem isn't Dynamic Task Mapping conceptually, it's that Airflow treats each mapped task instance as its own row in the metadata database. Map over 10,000 files and the scheduler now has to insert, heartbeat, and re-evaluate 10,000 rows every scheduler loop — that's what causes the DB locking and starves other DAGs of worker slots, since the scheduler spends all its time on this one DAG's bookkeeping instead of dispatching work.

The fix is to stop mapping 1:1 over files. Group the file list into fixed-size chunks first, then expand() over the chunks so each task instance processes a batch internally instead of a single file:

```python
def chunk(lst, size=200):
    return [lst[i:i+size] for i in range(0, len(lst), size)]

file_chunks = chunk(list_s3_files())  # 10,000 files -> ~50 chunks

@task
def process_batch(files: list[str]):
    for f in files:
        process_file(f)

process_batch.expand(files=file_chunks)
```

That turns 10,000 mapped instances into ~50, which is a completely different load profile for the scheduler. On top of that, raise `parsing_processes` and `min_file_process_interval` so DAG file parsing doesn't compete for the same DB connections, cap concurrency with a `pool` (`max_active_tis_per_dag`) so one DAG can't monopolize workers, and use deferrable operators for anything that just waits (like sensors) so it releases the worker slot instead of blocking it.

Question 2

Compare orchestrating complex multi-stage transformations using traditional task-based execution DAGs (Airflow) versus Software-Defined Assets (Dagster), specifically focusing on data lineage tracking, partition-based backfills, and automated upstream freshness policies.

Accepted Answer

Airflow's DAG is fundamentally a graph of *tasks* — imperative steps that happen to produce data as a side effect. Lineage in Airflow is something you bolt on afterward (via OpenLineage integration or manual `inlets`/`outlets`), because the DAG itself doesn't know what dataset a task produced, only that the task ran. Dagster inverts this: the DAG is a graph of *assets* (a table, a file, a model) and the code that materializes them is just an implementation detail. Because the asset is the first-class object, Dagster can track lineage natively — you can ask "what fed into this table" and get a real answer without extra tooling.

For partition-based backfills, Airflow requires you to manually construct backfill runs per `execution_date` and reason about which downstream DAGs need re-triggering — there's no built-in concept of "this asset's March partition is stale." Dagster's partitioned assets let you say "backfill partitions 2026-03-01 through 2026-03-15" and it will re-materialize just those partitions across the whole dependency chain, tracking each partition's own materialization state independently.

Freshness policies are the clearest differentiator: Dagster lets you declare "this asset must be no more than 2 hours stale" directly on the asset, and it will alert or auto-trigger upstream materialization to satisfy that SLA. Airflow has no native equivalent — you'd have to build custom sensors or SLA-miss callbacks and wire them by hand. In practice: Airflow is a great, mature generic task scheduler; Dagster is purpose-built for data-asset-centric pipelines and wins specifically on lineage, partition-aware backfills, and freshness — at the cost of a smaller ecosystem and community than Airflow's.

Question 3

An orchestration workflow uses continuous polling sensors to detect late-arriving files in object storage, consuming 40% of available worker slots with idle waiting. How do you redesign this architecture into an event-driven triggering model using cloud event bridges and webhook endpoints?

Accepted Answer

A classic poke-mode Airflow `S3KeySensor` occupies a full worker slot the entire time it's polling, even though it's doing nothing but checking "does the file exist yet" every N seconds. With enough of these running concurrently, you can starve the worker pool for actual task execution — which is exactly the 40% you're seeing.

The event-driven redesign removes polling entirely: configure S3 (or GCS/Blob) event notifications to publish directly to an SNS topic or EventBridge rule on object-created events, subscribe an SQS queue to that topic, and have Airflow consume from SQS via a deferrable sensor (`SqsSensorAsync` or a custom trigger). A deferrable sensor hands its wait state off to the triggerer process and releases the worker slot completely — it only reoccupies a worker once the event actually arrives.

```
S3 PutObject -> EventBridge rule -> SQS queue -> Airflow deferrable SQS sensor -> DAG trigger
```

If you don't want to touch Airflow's internals at all, the even simpler version is a Lambda subscribed to the S3 event that calls Airflow's REST API (`POST /dags/{dag_id}/dagRuns`) directly — no sensor, no polling, the DAG only exists on-demand when the file actually lands. Either way you go from O(worker-slots) idle waiting to zero, and latency actually improves since you're reacting to the event instead of waiting for the next poll interval.

Question 4

A 12-hour upstream ETL pipeline failure causes a multi-tier dependency chain of 80 downstream reporting tables to miss business SLAs. Design an automated orchestration recovery workflow that isolates critical path dependencies, executes parallel partition backfills, and skips non-critical intermediate staging computations.

Accepted Answer

The first thing this needs is a dependency graph with priority tags, decided ahead of time, not during the incident — you can't triage 80 tables under pressure if you don't already know which ones feed the CEO's dashboard versus which ones are intermediate staging nobody queries directly. Tag each asset/table with a criticality tier (e.g., tier-1 = customer-facing/exec, tier-2 = internal analytics, tier-3 = disposable staging) in your orchestrator's metadata (Dagster asset tags or Airflow DAG/task tags both support this).

When the upstream failure is detected, the recovery workflow does three things automatically: (1) walks the DAG from the failed node forward and marks every tier-1 descendant for immediate backfill, (2) fans those backfills out in parallel by partition (day/hour) rather than serially replaying 12 hours as one giant run — e.g. 12 hourly partitions backfilled as 12 concurrent DAG runs instead of one 12-hour-catchup run, bounded by a pool so you don't overwhelm the warehouse, and (3) explicitly marks tier-3 staging tables as `skipped`/deferred rather than backfilling them at all right now, since re-deriving them later from the now-correct tier-1/2 tables is cheaper than backfilling every layer.

Concretely in Airflow this looks like a `TriggerDagRunOperator` fan-out per partition with a `catchup=False` base DAG plus manual partition-scoped triggers, or in Dagster it's a partitioned asset backfill scoped to a tag selector (`tag:tier=1`). Either way, the key design decision is: don't treat "backfill everything in dependency order" as the recovery plan — that's what turns a 12-hour outage into a 24-hour one. Isolate the critical path, parallelize by partition, and explicitly defer the rest.

Question 5

How do you implement the Write-Audit-Publish (WAP) pattern in an orchestration workflow using Apache Iceberg or LakeFS table branching, ensuring data quality assertions run on isolated staging branches before atomically updating production catalog references?

Accepted Answer

WAP exists to solve one problem: by the time you notice a data quality issue in a production table, downstream consumers have often already read the bad data. The pattern fixes that by never letting new data touch the production reference until it's been validated somewhere isolated.

With Iceberg, this maps directly onto its branching feature. You write the new data to a named branch (`audit-branch`) instead of `main`:

```sql
INSERT INTO catalog.db.orders.branch_audit_branch
SELECT * FROM staging_orders;
```

Then your orchestrator (Airflow/Dagster) runs a data quality task — Great Expectations, dbt tests, or custom assertions — reading specifically from that branch. Only if every check passes does the workflow execute a `fast_forward` or `cherry-pick` operation to move `main` to point at the validated snapshot; if checks fail, the branch is simply abandoned and `main` never moves, so no consumer ever saw the bad write.

LakeFS achieves the same isolation at the object-storage/repo level instead of the table-metadata level: you create an ephemeral branch off `main`, run the ETL job writing into that branch's path, run validation against the branch, and on success call the LakeFS merge API to fast-forward `main`. The orchestration-level guarantee in both cases is the same — publish is a single atomic metadata pointer swap (Iceberg snapshot commit, or LakeFS commit merge), never a partial overwrite, so "audit failed" and "nothing changed in production" are the same state by construction, not something your pipeline has to remember to handle.

Question 6

A distributed Spark job joining a 500GB clickstream fact dataset with a user dimension table consistently hangs at the 99% mark on a single executor due to heavy data skew on a null/default user ID. How do you implement key salting, isolated broadcast joins, and Adaptive Query Execution (AQE) skew join hints to resolve the bottleneck?

Accepted Answer

The 99%-stuck pattern is the textbook signature of data skew: Spark hash-partitions the join key, and if millions of clickstream rows share the same `user_id = null` (or some default sentinel), every one of those rows lands on the same partition, on the same executor, while every other executor finishes in seconds. The job isn't slow, one task is doing 1000x the work of the rest.

The simplest fix, if your engine is on Spark 3.x, is to just turn on AQE skew join handling — it detects skewed partitions at runtime from shuffle statistics and automatically splits them into smaller sub-partitions before the join:

```python
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
```

When AQE alone isn't enough (e.g. you're pre-3.0, or the skew is extreme), manual salting is the reliable fallback: append a random suffix to the skewed key on the large side, and explode the dimension side to match every possible suffix, so the hot key gets spread across N partitions instead of one:

```python
N = 20
fact_salted = fact.withColumn("salt", (rand()*N).cast("int")) \
    .withColumn("join_key", concat(col("user_id"), lit("_"), col("salt")))
dim_salted = dim.crossJoin(spark.range(N).withColumnRenamed("id","salt")) \
    .withColumn("join_key", concat(col("user_id"), lit("_"), col("salt")))
result = fact_salted.join(dim_salted, "join_key")
```

Separately, since the user dimension table is small relative to 500GB of clickstream, force a broadcast join for it explicitly (`broadcast(dim_df)`) so it's never shuffled at all — that alone often removes most of the skew pain since the shuffle-heavy side of the join disappears.

Question 7

A legacy PySpark job executing a standard Python UDF over 100 million rows spends 70% of its execution time in JVM-to-Python serialization and inter-process IPC. How do you refactor the transformation using Apache Arrow-backed Vectorized Pandas UDFs / Polars to accelerate processing by an order of magnitude?

Accepted Answer

A regular PySpark UDF serializes and deserializes data row-by-row across the JVM/Python boundary using pickle — for every single row, Spark ships it out of the JVM, Python runs your function, and the result gets pickled back. At 100 million rows that per-row overhead dominates, which is exactly why 70% of the time is IPC and not actual computation.

A Pandas UDF (`@pandas_udf`) fixes this by batching: Spark hands Python entire columns as Arrow-backed Pandas Series in one shot (typically tens of thousands of rows per batch), your function runs vectorized Pandas/NumPy operations on the whole batch at once, and the result comes back as one Arrow batch instead of millions of individual round trips.

```python
from pyspark.sql.functions import pandas_udf
import pandas as pd

@pandas_udf("double")
def normalize(col: pd.Series) -> pd.Series:
    return (col - col.mean()) / col.std()

df = df.withColumn("norm_amount", normalize(df.amount))
```

This alone typically gives 5-20x speedup because you've eliminated the per-row serialization tax and get vectorized C-level execution inside Pandas/NumPy instead of interpreted Python per row. If the transformation doesn't need distributed execution at all (fits on one large node), it's worth also evaluating whether the whole job should just be Polars instead of Spark — Polars' Rust-native, multi-threaded execution engine skips the JVM entirely and can outperform a small Spark cluster on single-node workloads without any of this serialization overhead to begin with.

Question 8

A nightly Spark batch job frequently encounters ExecutorLostFailure and excessive disk spills during large aggregation phases. How do you analyze the Spark UI storage/execution memory timeline, adjust the memory-fraction versus storage-fraction split, and configure executor off-heap memory to stabilize execution?

Accepted Answer

`ExecutorLostFailure` during aggregation almost always means an executor ran out of memory and either the OS OOM-killer took it out or YARN/Kubernetes killed it for exceeding its container limit — and heavy disk spilling right before the crash is the tell: Spark is spilling shuffle/aggregation buffers to disk because execution memory is exhausted, and disk I/O under that pressure is slow enough that it looks like a hang before it actually fails.

In the Spark UI, the place to look is the Executors tab's memory breakdown and the Stages tab's "Shuffle Spill (Memory)" / "Shuffle Spill (Disk)" columns — a large disk-spill number on the aggregation stage confirms it's a memory-pressure problem, not a data-skew problem (skew shows up as one task taking far longer, not all tasks spilling).

The unified memory model splits `spark.executor.memory` into execution memory (joins/aggregations/shuffles) and storage memory (cached RDDs/DataFrames) via `spark.memory.fraction` (default 0.6 of the JVM heap) and `spark.memory.storageFraction` (default 0.5 of that). If you're not caching much data, storage memory is being wasted while execution memory starves — lowering `storageFraction` (e.g. to 0.2) gives execution more room to work with before spilling. Separately, enabling `spark.memory.offHeap.enabled=true` with a sized `spark.memory.offHeap.size` moves large buffers outside the JVM heap entirely, which reduces GC pressure and gives you a memory pool that isn't subject to JVM heap fragmentation — often the more durable fix than just tuning fractions, since it directly addresses the GC-pause-then-OOM pattern that raw heap tuning doesn't.

Question 9

An Apache Flink streaming pipeline processing 200k events/sec fails with checkpoint expiration timeouts whenever downstream sinks experience network latency. How do you diagnose RocksDB state backend write stalls, unaligned checkpointing, and configure buffer debloating to maintain checkpoint SLA under severe backpressure?

Accepted Answer

When a downstream sink slows down, Flink's natural response is backpressure — upstream operators stop pulling new records because the network buffers to the slow operator are full. With aligned checkpointing (the default pre-1.11 behavior), a checkpoint barrier has to travel through every operator's input buffers in order, so if those buffers are backed up behind a slow sink, the barrier itself gets stuck behind all the queued data and the checkpoint blows past its timeout — this is precisely the failure mode described.

The direct fix is unaligned checkpointing (`execution.checkpointing.unaligned.enabled: true`), which lets barriers overtake buffered records in the channel instead of waiting for them — the in-flight data gets checkpointed as part of channel state instead of being drained first, so checkpoint completion time becomes independent of how backed up the pipeline is.

Separately, check the RocksDB metrics (`state.backend.rocksdb.metrics.*`) for write-stall time — under high throughput, RocksDB's LSM-tree compaction can fall behind writes and trigger internal write stalls that look like general slowness but are actually storage-engine-level backpressure. Tuning `state.backend.rocksdb.writebuffer.count` and enabling `state.backend.rocksdb.compaction.style` tuning (or switching to `PARTITIONED_INDEX_FILTERS`) helps here. Finally, buffer debloating (`taskmanager.network.memory.buffer-debloat.enabled: true`) dynamically shrinks in-flight network buffer sizes when latency is detected, which reduces the amount of in-flight data a barrier has to account for in the first place — directly cutting checkpoint duration under the exact backpressure scenario described, rather than just working around it after the fact.

Question 10

You are building a distributed data pre-processing pipeline for unstructured text documents that must feed directly into model training nodes. Why might Ray Datasets / Dask be preferred over Spark for Ray-based ML cluster workflows, and how do you optimize streaming execution across GPU/CPU node boundaries?

Accepted Answer

Spark's execution model is built around the JVM, and getting data from a Spark executor into a Python-based GPU training loop means crossing the JVM/Python boundary (via serialization, same class of overhead as the pandas UDF problem) and then usually landing the data on disk or in an intermediate store before the training framework picks it up — Spark was never designed to hand data directly to a PyTorch/TensorFlow training loop running on the same cluster.

Ray Datasets is built Python-native and specifically designed to interoperate with Ray Train/Ray Serve, so preprocessing and training can share the exact same Ray cluster and object store (Ray's Plasma store) — a preprocessing actor produces a batch, and a training actor consumes it directly from shared memory without a serialization round-trip or landing it in S3 first. That's the core reason it's preferred for ML pipelines specifically: it's not a better general-purpose engine than Spark, it's the same execution substrate as the training job.

To optimize across the CPU/GPU boundary, the practical pattern is a pipeline of Ray actors with different resource requests: CPU-only actors do tokenization/cleaning (`ds.map_batches(preprocess_fn, num_cpus=1)`), and GPU actors consume the output for embedding/inference or feed the training loop (`ds.map_batches(model_fn, num_gpus=1, compute=ActorPoolStrategy(size=n))`). Ray Datasets' streaming execution overlaps these stages — CPU preprocessing for batch N+1 runs concurrently with GPU consumption of batch N — so you avoid GPUs sitting idle waiting for CPU-bound preprocessing to finish, which is the main throughput killer in naive two-stage (preprocess-then-train) pipelines.

Question 11

In an Apache Iceberg / Delta Lake table, concurrent streaming writes and automated file compaction jobs regularly fail with commit conflicts. How does Optimistic Concurrency Control (OCC) operate at the metadata layer, and how do you structure commit retries and conflict resolution strategies?

Accepted Answer

Both Iceberg and Delta Lake use OCC instead of locking: a writer reads the current table snapshot/version, builds its change set assuming that snapshot is still current, and then atomically tries to commit a new snapshot that points from the old version to the new one. There's no lock held during the actual work — the conflict only gets detected at commit time, when the writer checks "is the version I based my work on still the latest?" If another writer already committed in the meantime, the commit is rejected rather than corrupting the table.

This is exactly why streaming writes and compaction collide: your streaming job appends new files continuously, and a compaction job reads a snapshot, rewrites a bunch of small files into fewer large ones, and tries to commit a snapshot that replaces those files — if a streaming append committed in between, the compaction's commit is based on a stale view and gets rejected.

Both engines resolve this the same way conceptually: on a conflict, the writer doesn't just fail, it re-reads the new current snapshot, re-validates that its change is still compatible (Delta Lake calls this conflict detection at the transaction-log level, checking for actual overlapping file changes rather than just "someone else committed"), and retries the commit against the new base. In practice you configure this via retry counts (`spark.databricks.delta.retryCommit.maxAttempts` gathered via engine defaults, or an application-level retry loop for Iceberg's `commitTransaction`), and reduce collision *frequency* by scheduling compaction to run less often or during low-write windows, and by using Delta Lake's isolation levels / Iceberg's row-level delta writes (merge-on-read) where the conflict surface is narrower than whole-file replacement — since a commit against non-overlapping files won't conflict even if it happens concurrently.

Question 12

A high-throughput distributed ingestion system writing 6,000 Parquet files per second to Amazon S3 starts receiving HTTP 503 Slow Down throttling errors. Explain how S3 partition key prefixes govern request rate scaling limits, and how you design hash-prefixed partition paths to distribute I/O across storage partitions.

Accepted Answer

S3 automatically scales request capacity per *partition* of the key namespace, and it partitions based on lexical key prefixes. If your writes all share a common prefix pattern — like `s3://bucket/events/year=2026/month=08/day=24/` where every write for the day goes under the same date prefix — S3 hasn't had time to split that prefix into enough partitions to absorb 6,000 PUTs/sec, and you hit the per-partition rate limit (roughly 3,500 PUTs/sec/prefix as a soft baseline before auto-scaling catches up), producing 503 Slow Down responses.

The standard fix is to add a high-cardinality, randomly-distributed component at the *front* of the key, not the end, since S3 partitions based on the beginning of the key path. A common pattern is prepending a hash of the object key (or a random hex digit) as its own path segment:

```
s3://bucket/a3f/events/year=2026/month=08/day=24/part-00001.parquet
s3://bucket/7c1/events/year=2026/month=08/day=24/part-00002.parquet
```

Hashing something like the filename or a UUID into a 2-3 character prefix (`hashlib.md5(key).hexdigest()[:3]`) spreads writes across many lexically-distant prefixes, which S3 will partition independently, multiplying your effective throughput ceiling roughly by the number of distinct prefixes in active use. Note this does complicate downstream listing/reads (you can't just list by date anymore, you need a manifest or catalog like Iceberg/Glue tracking file locations), which is why most systems that need this pattern pair it with a table format that tracks file paths in metadata rather than relying on S3 prefix listing to find data.

Question 13

A Trino (Presto) distributed query engine coordinator crashes with OutOfMemory errors when business analysts execute federated joins across a PostgreSQL OLTP database and an S3 Parquet data lake. How do you troubleshoot query execution plans, configure join reordering, and enforce predicate pushdown to prevent coordinator memory exhaustion?

Accepted Answer

The coordinator OOM here is almost always caused by the join being planned in the wrong order or direction: Trino has to bring both sides of a join to the same place to execute it, and if it chooses to broadcast the large S3 Parquet side (or pulls too much of the small-looking-but-actually-large PostgreSQL side) into memory on the coordinator's query planning/distribution path, you get exactly this failure. The coordinator itself doesn't execute the join (workers do), but it does hold the query plan, statistics, and — critically — broadcast-join build-side data can bottleneck coordinator-adjacent memory if statistics are wrong and Trino picks the wrong join strategy.

Start by running `EXPLAIN (TYPE DISTRIBUTED)` on the query to see the actual join type chosen (`BROADCAST` vs `PARTITIONED`) and which side Trino thinks is smaller. If PostgreSQL table statistics aren't being collected/reported accurately through the JDBC connector (this is extremely common — external OLTP sources often report row counts Trino can't trust), Trino's cost-based optimizer can badly misjudge which side to broadcast, broadcasting what's actually the huge S3 dataset.

The fix has three parts: (1) force join reordering via `join_reordering_strategy=AUTOMATIC` combined with providing accurate statistics (run `ANALYZE` on the Iceberg/Hive table for the lake side, and check if the PostgreSQL connector supports statistics at all — if not, consider materializing that side into a Trino-native cache), (2) explicitly hint or restructure the query so the small PostgreSQL dimension table is the broadcast side and the large lake table is scanned in a partitioned/distributed manner, and (3) enforce predicate and projection pushdown by writing filters that the JDBC connector can translate into the PostgreSQL query itself (`WHERE` clauses on indexed columns) rather than filters Trino would otherwise have to apply after pulling all rows across — check `EXPLAIN ANALYZE` for "Predicate" appearing at the TableScan node for the Postgres side to confirm pushdown is actually happening rather than being pulled up to a Filter node above the scan.

Question 14

In a cloud data warehouse (e.g., Snowflake or BigQuery), daytime executive BI dashboard queries are queued behind heavy background data transformation jobs. How do you architect separate compute cluster warehouses, query queue timeouts, and multi-cluster auto-scaling policies to guarantee sub-second BI query latency?

Accepted Answer

The root issue is that BI queries and transformation jobs are sharing one compute pool, so a long-running ETL job holds compute that a sub-second dashboard query is waiting behind — this is a resource-contention problem, not a query-optimization problem, and no amount of SQL tuning on the BI side fixes it if the warehouse itself is saturated by batch work.

The standard architecture is workload isolation via separate virtual warehouses (Snowflake) or reservations/slots (BigQuery): create a dedicated `BI_WAREHOUSE` sized for fast, small, concurrent queries and a separate `ETL_WAREHOUSE` for heavy transforms, so they never compete for the same compute — this alone eliminates the queuing since they're physically isolated clusters, not a shared queue with priorities.

```sql
-- Snowflake
CREATE WAREHOUSE bi_warehouse
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 60
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD';
```

For the BI warehouse specifically, enable multi-cluster auto-scaling (Snowflake's `MAX_CLUSTER_COUNT`) so concurrent dashboard load spins up additional clusters automatically rather than queuing within one cluster — Snowflake's `STANDARD` scaling policy adds clusters proactively based on queued query estimates rather than waiting for the queue to actually build up. On BigQuery, the equivalent is separating dashboard traffic into its own reservation with dedicated slots via BigQuery Editions, so batch ELT jobs running on-demand or in a different reservation can't starve slots the BI reservation needs. Finally, set an aggressive `STATEMENT_TIMEOUT_IN_SECONDS` / query queue timeout on the BI warehouse so a misbehaving dashboard query fails fast and gets retried rather than sitting in a queue building up latency for everyone behind it.

Question 15

Your dbt project has grown to 800 models, making CI test runs take 30 minutes on every pull request. How do you implement dbt Slim CI using state comparison alongside custom generic tests to validate referential integrity and business rules in under 3 minutes?

Accepted Answer

Running the full 800-model DAG on every PR is the mistake — most PRs only touch a handful of models, so rebuilding and testing everything downstream of a change you didn't make is pure waste. dbt's `state:modified` selector exists exactly for this: it compares the current project against a stored manifest from the last successful production run and identifies only the models/tests that actually changed (or depend on something that changed).

```bash
dbt build --select state:modified+ --defer --state ./prod-manifest
```

The `--defer --state` flags are what make this safe: for any upstream model your changed models depend on but didn't themselves change, dbt reads from the already-materialized production table instead of rebuilding it in CI, so you're not paying to rebuild the whole DAG just to test a leaf model's changes. `state:modified+` selects the changed models plus everything downstream of them, which is the actual blast radius of the change — that's normally a small fraction of 800 models on a typical PR.

For the custom generic tests themselves, write them once as reusable macros (e.g., a `not_negative` or `fk_exists_in` generic test in `tests/generic/`) rather than one-off SQL per model, and apply them via YAML `tests:` blocks — that keeps referential integrity and business-rule checks declarative and only running against the modified models in this CI context. Combining scoped selection with deferral is what typically takes a 30-minute full-project run down to under 3 minutes, since you're now testing a diff, not the whole warehouse.

Question 16

Design an automated continuous reconciliation engine that compares change-data-capture (CDC) event streams against source MySQL transaction tables to detect dropped events, duplicate row mutations, and schema desynchronization without locking production tables.

Accepted Answer

The core constraint — no locking production tables — rules out doing row-by-row comparison against live MySQL directly, since even a read query on a hot OLTP table under a reconciliation job's query pattern can contend with production traffic. The design instead treats reconciliation as comparing two independently-derived snapshots: one built from the CDC stream, one built from a low-impact read of MySQL, and diffing those.

For the MySQL side, use a replica (read replica or a binlog-based replica specifically for this job) so reconciliation reads never touch the primary, and periodically compute lightweight checksums per primary-key range rather than pulling full rows — e.g. `CHECKSUM TABLE` per shard, or a rolling hash over `(pk, updated_at, business_columns)` grouped into key ranges. For the CDC side (Debezium/Kafka), maintain a materialized "shadow" state in a compacted Kafka topic or a lightweight store keyed by primary key, built purely from consuming the CDC stream, and compute the same per-range checksum from that.

Reconciliation then becomes comparing checksums per range on a schedule: matching checksums mean the CDC stream faithfully represents that range, a mismatch flags exactly which key range has drift, and you drill into that range specifically (not the whole table) to find the actual dropped/duplicated rows. Dropped events show up as a range where MySQL has rows the CDC shadow state doesn't; duplicates show up as the CDC shadow having more mutations recorded for a PK than the row's actual version/update count in MySQL (Debezium's LSN/GTID position monotonicity check catches out-of-order or replayed events directly). Schema desync is caught separately by comparing Debezium's schema-change topic events against `information_schema` on the replica on a schedule — a column MySQL has that the CDC schema registry doesn't (or vice versa) is flagged immediately rather than waiting for a downstream consumer to fail on a missing field.

Question 17

You are chaos testing a mission-critical financial ledger streaming pipeline. When network latency is injected between Kafka brokers and worker nodes, consumer groups trigger continuous rebalance storms and duplicate writes. How do you configure transactional producer IDs, fencing tokens, and idempotent consumers to ensure zero duplicate records?

Accepted Answer

Rebalance storms under injected latency happen because consumers miss `session.timeout.ms` heartbeat deadlines when the network is slow, get kicked from the group, rejoin, get reassigned partitions, and re-read from the last committed offset — if that offset commit hadn't happened yet for records already processed (e.g. written to the ledger) before the rebalance, you get duplicate writes on rejoin. This is the exact scenario Kafka's exactly-once semantics (EOS) machinery exists for.

First, tune the group to be less trigger-happy under transient latency: raise `session.timeout.ms` and `max.poll.interval.ms` so brief latency spikes don't cause a rebalance at all — a lot of "rebalance storms" are actually just timeouts tuned too aggressively for real-world network jitter, and this alone reduces how often the duplicate-write window even opens.

For the writes themselves, wrap the read-process-write cycle in a Kafka transaction with a stable `transactional.id` per logical writer instance, and enable `enable.idempotence=true` on the producer:

```java
props.put("transactional.id", "ledger-writer-1");
props.put("enable.idempotence", "true");
producer.initTransactions();

producer.beginTransaction();
producer.send(outputRecord);
producer.sendOffsetsToTransaction(offsets, consumerGroupMetadata);
producer.commitTransaction();
```

The `transactional.id` gives you fencing for free: if a rebalance causes two instances to briefly believe they own the same partition, the broker assigns each `initTransactions()` call an epoch, and any producer using a stale epoch gets a `ProducerFencedException` on its next send — Kafka itself rejects the zombie writer's output rather than letting both write. Bundling `sendOffsetsToTransaction` in the same transaction as the actual write means the offset commit and the ledger write succeed or fail atomically, so a mid-write crash-and-rebalance can never leave you in a state where the write happened but the offset wasn't committed (the classic cause of duplicates) — on restart, the consumer resumes from the last atomically-committed offset, guaranteed to be exactly after the last successfully written record.

Question 18

How do you integrate Great Expectations data checkpoints into a production batch pipeline, configuring automated validation suites that evaluate column distribution drift, null thresholds, and unexpected category values, while halting pipeline publication when critical assertions fail?

Accepted Answer

A Great Expectations Checkpoint is the unit that ties together a Batch of data, an Expectation Suite (the actual assertions), and an action list (what to do with results) — it's the piece you actually run inside your orchestrator, not the suite itself. You'd define a suite with expectations like:

```python
validator.expect_column_values_to_not_be_null("user_id", mostly=0.99)
validator.expect_column_values_to_be_in_set("status", ["active","churned","trial"])
validator.expect_column_quantile_values_to_be_between(
    "order_amount", quantile_ranges={"quantiles":[0.5],"value_ranges":[[10,500]]}
)
```

The null-threshold and category checks are straightforward point-in-time assertions. Distribution drift is different — it needs a baseline to compare against, not just a static rule, so this typically uses `expect_column_quantile_values_to_be_between` or a custom expectation comparing today's batch statistics against a rolling window stored from previous runs (e.g. comparing this batch's mean/stddev against last 7 days', flagging if it's beyond N standard deviations).

In the pipeline, the checkpoint runs as a task immediately after the load/transform step and before anything downstream consumes the table: `checkpoint_result = checkpoint.run()`, and the orchestrator branches on `checkpoint_result["success"]`. In Airflow this is a straightforward `BranchPythonOperator` or simply letting the GE task raise/fail, which — combined with `depends_on_past`/downstream tasks depending on this task's success — naturally halts publication: downstream tasks that publish to the production table or trigger consumer notifications simply never run if the checkpoint task is upstream of them and fails. Critical assertions should be marked `severity=critical` or split into a separate suite from advisory checks, so a soft anomaly (e.g. a distribution warning) can log/alert without blocking, while a hard failure (e.g. a null-rate that violates a contract) blocks the publish task from ever executing.

Question 19

What is the difference between ETL and ELT, and when would you choose one over the other?

Accepted Answer

ETL (Extract, Transform, Load) transforms data in a separate processing engine *before* it lands in the destination — you pull data from the source, run it through a transformation stage (Spark, an ETL tool, custom code), and only load the already-clean, already-modeled result into the warehouse. ELT (Extract, Load, Transform) loads raw data into the destination first and does the transformation *inside* the destination system afterward, typically with SQL.

ELT became the dominant pattern once cloud warehouses (Snowflake, BigQuery, Redshift) got cheap and powerful enough to do heavy transformation work themselves — it removes the need for a separate transformation cluster, and tools like dbt exist specifically to manage the "T" as version-controlled SQL models running inside the warehouse. It also means you always have the raw, untransformed data available if you need to reprocess it differently later, since nothing was thrown away before landing.

ETL still makes sense when the transformation needs to happen before data can even be stored cheaply or compliantly — e.g., stripping PII before it's persisted anywhere, or transforming data volumes so large that loading them raw would be wasteful, or when the destination itself can't do the transformation (e.g., loading into a system with no real query engine). In practice, most modern cloud-native data teams default to ELT with dbt, and reach for a pre-load ETL step only when there's a compliance or cost reason data can't land raw.

Question 20

What's the difference between a data warehouse, a data lake, and a data lakehouse?

Accepted Answer

A data warehouse stores structured, schema-on-write data optimized for fast SQL analytics — think Snowflake or Redshift tables with defined columns and types, tuned for BI queries. A data lake stores raw data of any shape (structured, semi-structured, unstructured) cheaply in object storage like S3, with schema-on-read — you decide the structure when you query it, not when you write it, which makes it flexible but historically weaker for reliable, ACID-guaranteed analytics.

The lakehouse (Delta Lake, Apache Iceberg, Apache Hudi) tries to get both: it stores data as files in cheap object storage like a lake, but adds a metadata/transaction layer on top that gives you ACID transactions, schema enforcement, time travel, and the reliability guarantees you'd normally only get from a warehouse — without needing to load the data into a separate proprietary warehouse engine.

A concrete example: a company might dump raw clickstream JSON into an S3 data lake because it's cheap and flexible, then build curated, structured tables in a warehouse for BI dashboards. With a lakehouse architecture (e.g., Iceberg tables on S3 queried by Trino/Spark/Snowflake), you skip the separate warehouse-loading step entirely — the same S3-backed Iceberg table serves both raw exploration and reliable BI queries, which is why lakehouse architectures have become the default choice for new greenfield data platforms.

Question 21

Explain the difference between a star schema and a snowflake schema. Which would you choose for a BI dashboard and why?

Accepted Answer

A star schema has one central fact table (e.g., `fact_sales`) surrounded directly by denormalized dimension tables (`dim_customer`, `dim_product`, `dim_date`) — each dimension is a single flat table, even if that means some redundancy (e.g., `dim_product` might repeat the category name on every row instead of normalizing category into its own table). A snowflake schema normalizes those dimensions further — `dim_product` might link out to a separate `dim_category` table, which links to `dim_department`, and so on, reducing redundancy at the cost of more joins.

For a BI dashboard, star schema is almost always the right choice. BI queries are read-heavy and join-sensitive — every additional join in a snowflake schema is another table scan and join operation the query engine has to do for what's usually a simple filter or group-by. The storage savings from normalizing dimensions are trivial on modern columnar warehouses (dimension tables are typically tiny compared to fact tables), so there's little upside to snowflaking and a real query-performance cost.

Concretely: a dashboard query like "total sales by product category last quarter" against a star schema is one join (`fact_sales` to `dim_product`, category already denormalized onto that row). Against a snowflake schema it's two or three joins (`fact_sales` -> `dim_product` -> `dim_category`). At scale, with BI tools issuing many such queries concurrently, that's a meaningful, avoidable performance tax — which is why star schema remains the standard for dimensional modeling in analytics warehouses.

Question 22

What are Slowly Changing Dimensions (SCDs)? Explain Type 1 vs Type 2 and write SQL implementing a Type 2 update.

Accepted Answer

A Slowly Changing Dimension is a dimension table where attribute values change over time — a customer moves cities, a product gets recategorized — and different SCD "types" define how you handle that change in the dimension table. Type 1 simply overwrites the old value with the new one, losing history entirely: if a customer's address changes, the old address is gone, and any historical fact rows joined to that customer now show the new address as if it had always been true. Type 2 instead preserves history by inserting a new row for the changed record and marking the old row as no-longer-current, so you can still see what the customer's address was at the time each historical sale happened.

Type 1 is appropriate when you genuinely don't care about historical accuracy (e.g., correcting a typo in a name). Type 2 is what you use when historical accuracy matters for reporting — e.g., sales attributed to a customer's sales region at the time of sale, not their current region.

A typical Type 2 implementation uses `effective_date`, `end_date`, and `is_current` columns:

```sql
-- 1. Expire the current row for the changed customer
UPDATE dim_customer
SET end_date = CURRENT_DATE, is_current = FALSE
WHERE customer_id = 123 AND is_current = TRUE;

-- 2. Insert the new version as the current row
INSERT INTO dim_customer (customer_id, address, effective_date, end_date, is_current)
VALUES (123, 'New Address', CURRENT_DATE, NULL, TRUE);
```

Fact tables then join to the dimension on `customer_id` filtered to whatever date range matches the fact's own timestamp, rather than always joining to `is_current = TRUE`, so historical facts correctly resolve to the dimension values that were true at that point in time.

Question 23

What is the difference between OLTP and OLAP systems?

Accepted Answer

OLTP (Online Transaction Processing) systems are optimized for lots of small, fast read/write transactions — think a Postgres or MySQL database backing an e-commerce checkout flow, where each query touches a few rows (insert an order, update inventory) and needs to complete in milliseconds with strong consistency. OLAP (Online Analytical Processing) systems are optimized for the opposite access pattern: fewer, much larger queries that scan and aggregate millions of rows for analytics — "total revenue by region last quarter" — where individual-row latency doesn't matter but scan throughput does.

This difference drives fundamentally different storage layouts: OLTP databases store data row-oriented (all columns of one row together on disk) because transactions typically touch whole rows. OLAP warehouses store data column-oriented (all values of one column together) because analytical queries typically touch a few columns across many rows, and columnar storage lets the engine skip reading columns the query doesn't need entirely.

In practice, a data engineer's job is often literally the bridge between the two: the OLTP database is the system of record for the application, and a pipeline (CDC or batch extract) moves that data into an OLAP warehouse where it can be queried for analytics without putting analytical query load on the production transactional database, which could otherwise slow down or lock the live application.

Question 24

Explain database normalization vs denormalization, and why analytics warehouses often deliberately denormalize data.

Accepted Answer

Normalization organizes data to eliminate redundancy — each fact is stored in exactly one place, and related data is split across tables linked by foreign keys (3NF is the common target: no column depends on anything other than the whole primary key). This is the right model for OLTP systems because it prevents update anomalies — change a customer's address once, in one row, and every reference to it is automatically consistent.

Denormalization deliberately reintroduces redundancy — duplicating data across rows or flattening joined tables into one wider table — to avoid the cost of joins at query time. Analytics warehouses favor denormalization because analytical queries are read-heavy and join-expensive at scale: joining a billion-row fact table against five normalized dimension tables, each themselves normalized further, multiplies query cost for very little benefit, since analytical workloads rarely write and don't suffer from update anomalies the way OLTP does.

A concrete example: an OLTP orders system might normalize `product_id` -> `products` -> `categories` -> `departments` across four tables. A denormalized analytics fact table would flatten category name and department name directly onto each order row (or onto a denormalized `dim_product` in a star schema), so a dashboard query summing sales by department is a single join instead of three. The trade-off is storage (denormalized data takes more space) and update complexity (if a category is renamed, you now update it wherever it's duplicated) — but for read-heavy analytics at scale, that trade is almost always worth it.

Question 25

What is the difference between partitioning and bucketing in Spark/Hive, and when would you use each?

Accepted Answer

Partitioning splits a table's data into separate physical directories based on the value of a column — e.g., `s3://data/events/year=2026/month=08/day=24/` — so a query filtering on that column (`WHERE year=2026 AND month=08`) can skip reading every other partition's files entirely (partition pruning). It works best on low-to-medium cardinality columns that are commonly filtered on, like date or region — too many distinct values (like a raw user ID) creates a huge number of tiny partitions, which hurts performance instead of helping (the "small file problem").

Bucketing instead hashes a column's values into a fixed number of buckets *within* a partition (or table), physically co-locating rows with the same hashed key into the same files. It doesn't help with pruning the way partitioning does, but it helps with join and aggregation performance — if two tables are bucketed on the same key with the same bucket count, a join on that key can be done bucket-to-bucket without a full shuffle, since matching keys are guaranteed to already be co-located.

A practical combination: partition a large events table by `day` (coarse, commonly filtered, low cardinality) and bucket it by `user_id` (high cardinality, frequently joined on) into, say, 256 buckets. Queries filtering by date get partition pruning, and joins against another table bucketed the same way on `user_id` avoid a full shuffle-join — you get both benefits because they solve different problems (pruning vs. join co-location) at different granularities.

Question 26

What does idempotency mean in the context of a data pipeline, and how do you design a pipeline step to be idempotent?

Accepted Answer

Idempotency means running the same operation multiple times produces the same end result as running it once — critically important in data pipelines because retries, backfills, and orchestrator restarts are routine, and if a step isn't idempotent, a retry after a partial failure can silently duplicate or corrupt data instead of safely repeating the same work.

The classic non-idempotent mistake is a plain `INSERT` on retry: if a load job inserts 1 million rows, crashes at row 900,000 after a network blip, and the orchestrator retries the whole task, you now have up to 900,000 duplicate rows unless something prevents it. The fix depends on the operation type: for loads, replace blind inserts with an upsert/`MERGE` keyed on a natural or business key, so re-running the same batch overwrites the same rows instead of adding new ones:

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

For partition-based batch jobs, the common pattern is "delete-then-insert" scoped to the specific partition being processed (`DELETE FROM t WHERE date = '2026-08-24'` then insert that day's data) — rerunning for the same date always produces the same final state regardless of how many times it's retried. For streaming, idempotency usually means designing consumers around a unique event ID plus an upsert (or Kafka transactions, as in exactly-once processing) rather than relying on at-least-once delivery translating directly into an append.

Question 27

Explain the difference between at-least-once, at-most-once, and exactly-once delivery semantics in streaming systems.

Accepted Answer

These describe the guarantee a streaming system makes about how many times a message gets processed relative to how many times it was produced. At-most-once means a message is delivered zero or one times — if something fails after the message is consumed but before it's acknowledged/processed, it's simply lost, never retried. This is the easiest to implement (just don't retry) but unacceptable for anything where losing data matters, like financial events.

At-least-once means a message is delivered one or more times — the system retries on any uncertainty (crash, timeout, unacknowledged processing) rather than risk losing the message, which means duplicates are possible whenever a message was actually processed but the acknowledgment didn't make it back in time. This is Kafka's default consumer behavior and is the most common real-world guarantee, because it's relatively simple and safe as long as downstream processing is idempotent.

Exactly-once means each message affects the final state as if it were processed exactly one time, even though the underlying delivery might still literally retry — it's achieved either through end-to-end idempotent processing on top of at-least-once delivery (dedupe by a unique message ID) or through transactional mechanisms like Kafka's exactly-once semantics (transactional producers plus `sendOffsetsToTransaction`, as in the Kafka chaos-testing question above), which atomically bind the consumed offset to the produced output so a retry can never result in double-processing. In practice, most systems that claim exactly-once are really "at-least-once delivery plus idempotent processing" rather than a delivery mechanism that literally never redelivers — true exactly-once *delivery* is extremely rare because it would require perfect coordination across independent failure domains.

Question 28

What is data lineage, and why does it matter for a data engineering team?

Accepted Answer

Data lineage is the traceable record of where a piece of data came from, what transformations it went through, and what it feeds into downstream — essentially a map of a dataset's full life cycle from source system to final report or model. It answers two questions: "if I change this upstream table, what breaks downstream?" and "if this number in a dashboard looks wrong, where did it actually come from?"

Without lineage, both questions require manually reading pipeline code or asking around — which doesn't scale past a handful of pipelines, and becomes actively dangerous once you have hundreds of interdependent dbt models or Airflow DAGs, because a seemingly-safe schema change can silently break a downstream consumer nobody remembered existed.

Concretely, tools like dbt generate lineage automatically from the `ref()`/`source()` graph between models, OpenLineage standardizes lineage events across different tools (Spark, Airflow, dbt) so they can be aggregated into one graph, and catalog tools (DataHub, Amundsen, Atlan) visualize the resulting graph for both engineers and analysts. A concrete use case: before deprecating a column in a source table, an engineer queries the lineage graph to find every downstream model, dashboard, and ML feature that reads it — turning "I hope nothing breaks" into a checklist of exactly what needs to be updated or migrated first.

Question 29

How do you handle schema evolution in a data pipeline — for example, when an upstream source adds a new column or changes a column's type?

Accepted Answer

The first decision is what format/table layer you're on, because schema evolution support varies enormously. Modern table formats (Iceberg, Delta Lake, BigQuery native tables) handle additive changes — a new column appearing — natively and safely: old data just reads back with `NULL` for the new column, and no rewrite is needed. A plain Hive table backed by raw Parquet without a metadata layer is much more fragile — a new column showing up mid-stream can outright break the read if the reader's schema doesn't reconcile it, depending on the reader.

For additive changes (new columns), the safe default is to make the pipeline schema-tolerant: read with schema merging enabled (`mergeSchema=true` in Spark for Parquet/Delta) so new columns are picked up automatically rather than silently dropped or causing a job failure, and make sure downstream consumers (dbt models, BI semantic layers) treat unknown new columns as "ignore until explicitly mapped" rather than failing.

For breaking changes — a column's type changing (e.g. `int` to `string`), or a column being removed or renamed — you can't safely auto-merge, because that genuinely changes what queries against historical data mean. The standard practice is a schema registry (Confluent Schema Registry for Kafka, or a similar contract layer) that enforces compatibility rules on producers before they can publish a breaking change, catching it at write time instead of at a downstream consumer's read time. When a breaking change is genuinely needed, it should go through an explicit migration: dual-write old and new schema versions for a transition period, backfill/cast historical data if needed, and only retire the old schema once every consumer has migrated — treating it the same way you'd treat any breaking API change, not a silent pipeline update.

Question 30

What's the difference between a primary key, a foreign key, and a surrogate key in dimensional modeling?

Accepted Answer

A primary key uniquely identifies each row in a table — no two rows can share one, and it can't be null. A foreign key is a column in one table that references a primary key in another table, establishing the relationship between them — e.g., `orders.customer_id` referencing `customers.customer_id`. These are standard relational database concepts, enforced (in an OLTP database) by constraints that guarantee referential integrity.

A surrogate key is specifically a system-generated, meaningless identifier (usually an auto-incrementing integer or a UUID) used as a table's primary key instead of a natural key — a real-world attribute like an email address or a government ID number. Dimensional modeling favors surrogate keys for dimension tables specifically because natural keys can change (a customer's email address changes, a product's SKU gets reissued) or aren't stable across source systems, while a surrogate key stays fixed for the life of that dimension row regardless of what happens to the natural key.

This matters directly for SCD Type 2: when a customer's address changes and you insert a new row to preserve history, the natural key (`customer_id` from the source system) stays the same across both the old and new row — but the surrogate key differs, because it's a new row. Fact tables in a star schema join to dimensions using the surrogate key, not the natural key, so that each fact row correctly points to the specific historical version of the dimension that was true at the time the fact occurred.

Question 31

What is the difference between a fact table and a dimension table in dimensional modeling?

Accepted Answer

A fact table stores the measurable, quantitative events of a business process — sales transactions, page views, sensor readings — where each row typically represents one event and includes numeric measures (revenue, quantity, duration) plus foreign keys pointing to the dimensions that describe the context of that event. Fact tables are usually the largest tables in a warehouse by row count, growing continuously as new events happen.

A dimension table stores the descriptive context that gives meaning to those facts — who, what, where, when. `dim_customer` describes customer attributes, `dim_product` describes product attributes, `dim_date` describes calendar attributes — dimension tables are typically much smaller and slower-changing than fact tables (hence "Slowly Changing Dimensions").

Concretely, a `fact_sales` row might be: `(sale_id, date_key, customer_key, product_key, store_key, quantity, revenue)`. None of that row tells you the customer's name or the product's category by itself — you join to `dim_customer` and `dim_product` on their respective keys to get that descriptive detail. This separation (measures in facts, descriptive attributes in dimensions) is the entire basis of the star schema, and it's what makes a question like "total revenue by product category and region last month" a straightforward join-and-aggregate instead of a query against one giant flat table mixing transactional and descriptive data together.

Question 32

Write a SQL query to find the second-highest salary in an employee table, handling the case where there might be ties for the highest salary.

Accepted Answer

The naive approach (`ORDER BY salary DESC LIMIT 1 OFFSET 1`) breaks silently when there are ties for the highest salary — if two employees both earn the top salary, `OFFSET 1` just returns the second row of that tie, which is still the highest salary, not the actual second-highest distinct value. The fix is to work with distinct salary values, not rows.

```sql
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
```

This correctly returns the second-*distinct* highest value regardless of how many people are tied for first, because it explicitly excludes the maximum and then takes the maximum of what remains. It also naturally returns `NULL` if there's no second distinct value (e.g., everyone has the same salary), which is the semantically correct answer rather than throwing an error.

A more general version using `DENSE_RANK()` handles "find the Nth highest distinct salary" for any N cleanly, and is usually the answer interviewers are actually looking for since it generalizes:

```sql
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;
```

`DENSE_RANK` (as opposed to `RANK` or `ROW_NUMBER`) is the correct window function here specifically because it doesn't skip rank numbers after a tie — two people tied for rank 1 both get rank 1, and the next distinct salary correctly gets rank 2, whereas `RANK()` would jump to rank 3.

Question 33

Write a SQL query to remove duplicate rows from a table, keeping only the most recent record per unique key.

Accepted Answer

The reliable pattern for this is `ROW_NUMBER()` partitioned by whatever defines "duplicate" for your table, ordered so the row you want to keep comes first, then deleting everything that isn't rank 1:

```sql
WITH ranked AS (
  SELECT
    ctid,  -- or a surrogate row id
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM orders
)
DELETE FROM orders
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);
```

(`ctid` is Postgres-specific for addressing a physical row without a natural unique column to key on; on other engines you'd use a real primary key or unique row identifier instead.) `PARTITION BY order_id` groups all rows sharing the same logical key together, `ORDER BY updated_at DESC` makes the most recent one rank 1 within its group, and everything ranked 2 or higher within a group is, by definition, an older duplicate to delete.

If you'd rather not delete in place (e.g., you're deduplicating during a load rather than cleaning an existing table), the same `ROW_NUMBER()` logic works as a `SELECT ... WHERE rn = 1` filter feeding into an insert or a view instead — which is actually the more common real-world pattern in a dbt model: dedupe as part of the transformation query itself (`qualify row_number() over (...) = 1` in warehouses that support `QUALIFY`, like Snowflake/BigQuery) rather than mutating the raw source table.

Question 34

Explain SQL window functions with an example — how are they different from a GROUP BY aggregation?

Accepted Answer

`GROUP BY` collapses multiple rows into one output row per group — you lose the individual row detail, you only get the aggregate. A window function computes an aggregate (or ranking, or offset lookup) *across* a defined set of rows (the "window") but returns a value for every individual input row, without collapsing anything — you keep row-level detail and get the aggregate context alongside it in the same row.

A concrete example: showing each employee's salary next to their department's average salary, in the same result set as the individual rows:

```sql
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;
```

With `GROUP BY department`, you'd only get one row per department with the average — you couldn't also see each individual employee's salary in the same query. The window function's `PARTITION BY` defines the group the aggregate is computed over, but `OVER (...)` tells SQL "don't collapse the rows, just attach this computed value to each one."

Window functions also cover ranking (`RANK`, `DENSE_RANK`, `ROW_NUMBER`) and offset access (`LAG`/`LEAD` to compare a row to the previous/next row in an ordered sequence) — e.g., `LAG(revenue) OVER (ORDER BY month)` to compute month-over-month change directly in SQL without a self-join. This class of problem (rank within group, running totals, comparisons to a previous row) is extremely common in data engineering transformation logic and is one of the most frequently tested SQL skills in interviews specifically because a self-join-based version of the same query is much slower and harder to read.

Question 35

What is a materialized view, and how is it different from a regular view? When would you use one in a data pipeline?

Accepted Answer

A regular view is just a saved SQL query — every time you select from it, the underlying query re-executes against the base tables from scratch. It's convenient for reuse and abstraction, but it doesn't save you any compute; querying a view over a billion-row join costs exactly what running that join directly would cost.

A materialized view actually stores the query's *result* as physical data, computed once, and served from storage on subsequent reads until it's refreshed — either on a schedule, incrementally as underlying data changes, or on demand. That trades storage and refresh cost for dramatically faster read performance, since a query against a materialized view is just reading pre-computed data instead of re-running an expensive aggregation or join every time.

A typical data engineering use case: a dashboard needs "daily active users by country," computed from a raw events table with billions of rows. Recomputing that aggregation on every dashboard load would be slow and expensive. A materialized view (or, functionally equivalent, an incrementally-updated dbt model) pre-aggregates that once per day, and the dashboard just reads the small, already-aggregated result. Snowflake, BigQuery, and Postgres all support materialized views natively with automatic or scheduled refresh — the trade-off to manage is staleness (the view reflects data as of its last refresh, not real-time) versus the cost of refreshing more frequently.

Question 36

What is Change Data Capture (CDC), and how does it differ from doing periodic full-table extracts for incremental loads?

Accepted Answer

A periodic full extract re-reads the entire source table on every run and either replaces the destination wholesale or diffs it to find changes — simple to implement, but it scales badly: extracting a 500-million-row table every hour to find the few thousand rows that actually changed is enormously wasteful in both compute and load on the source database, and it can't capture deletes reliably unless you diff against a full previous snapshot.

CDC instead captures changes at the source as they happen, typically by reading the database's transaction log (the write-ahead log in Postgres, the binlog in MySQL) rather than querying tables directly. Tools like Debezium tail this log and emit an event for every insert, update, and delete — including the before/after row values — as a continuous stream, usually into Kafka.

The practical difference shows up in three places: load on the source (CDC reads a log stream instead of running queries against live tables, so it doesn't compete with production traffic the way a periodic `SELECT *` does), latency (CDC gives near-real-time propagation of changes instead of waiting for the next batch window), and correctness (CDC naturally captures deletes and intermediate updates that a snapshot-diff approach can miss entirely if a row is inserted and deleted between two extract runs). The trade-off is operational complexity — CDC requires access to the database's replication log, careful handling of schema changes in that log, and a downstream consumer (usually a stream processor) to turn the change events into an up-to-date table, which is more moving parts than a simple scheduled extract query.

Question 37

What is the medallion architecture (bronze/silver/gold layers), and why is it a common pattern in lakehouse design?

Accepted Answer

The medallion architecture organizes data into three progressively refined layers within a lakehouse. Bronze holds raw, unmodified data exactly as it arrived from the source — no cleaning, no schema enforcement beyond basic ingestion, kept as a permanent, replayable record of what was actually received. Silver applies cleaning, deduplication, type casting, and joins to produce validated, queryable tables — still fairly granular/normalized, but trustworthy. Gold holds business-level, aggregated, often denormalized tables built specifically for consumption — the tables BI dashboards and reports actually query.

The value of this layering is separation of concerns and reprocessability: because bronze is untouched raw data, if you discover a bug in your silver-layer transformation logic months later, you can reprocess from bronze with the fix rather than having lost the original data to an early, lossy transformation. It also gives different consumers appropriate access — data scientists needing raw signal go to bronze/silver, while BI analysts only need gold's clean, aggregated tables and shouldn't have to deal with silver's more granular complexity.

A concrete example: bronze might be raw JSON clickstream events landed as-is from Kafka; silver parses that JSON into a structured, deduplicated `events` table with proper types and a validated schema; gold aggregates that into `daily_active_users_by_country`, the exact table a dashboard queries. Each layer is a distinct set of tables (often in Delta Lake or Iceberg format) rather than three unrelated systems, and the transformation logic moving data between layers — bronze-to-silver, silver-to-gold — is typically what a tool like dbt or a Spark job actually implements.

Question 38

What is a DAG, and why is it the core abstraction behind orchestration tools like Airflow?

Accepted Answer

A DAG (Directed Acyclic Graph) is a graph where edges have direction and there are no cycles — you can never follow the edges and end up back where you started. In orchestration, nodes represent tasks and edges represent dependencies ("task B can't run until task A finishes"), and the "acyclic" property is what guarantees the workflow actually has a valid execution order at all — if task A depended on task B and task B depended on task A, there'd be no way to ever start.

This abstraction fits data pipelines naturally because most pipelines really are a dependency graph: extract has to happen before transform, transform before load, and some transforms depend on multiple upstream extracts finishing first. The DAG lets an orchestrator like Airflow compute a valid topological execution order automatically, run independent branches in parallel where there's no dependency between them, and know exactly what needs to be re-run if one node fails (everything downstream of the failure, and nothing upstream or in unrelated branches).

A concrete example: a pipeline that extracts from three separate source systems, joins the results, and loads into two separate destination tables is naturally expressed as a DAG with three independent extract nodes feeding into one join/transform node, which fans out to two independent load nodes. Airflow (or Dagster, Prefect) can run the three extracts concurrently since there's no dependency between them, wait for all three before starting the join, and — if one load task fails — retry just that load without re-running the extracts or the other, unaffected load task.

Question 39

What's the difference between a broadcast join and a shuffle (sort-merge) join in Spark? How does Spark decide which to use?

Accepted Answer

A shuffle join (Spark's default for large-to-large joins, typically implemented as a sort-merge join) repartitions both sides of the join across the cluster by the join key, so matching keys land on the same executor, then sorts and merges each partition. This works for any size data but is expensive — it requires a full shuffle (network transfer) of both datasets across the cluster.

A broadcast join instead sends one entire side of the join — the smaller one — to every executor as a complete in-memory copy, so each executor can join its local partition of the large side against the full broadcasted small side with no shuffle needed on the large side at all. This is dramatically faster when applicable, because you eliminate the expensive shuffle of the large dataset entirely, at the cost of needing the small side to actually fit comfortably in each executor's memory.

Spark decides automatically based on `spark.sql.autoBroadcastJoinThreshold` (default 10MB) — if one side of the join is estimated (from table statistics) to be smaller than this threshold, Spark's Catalyst optimizer picks a broadcast join automatically. You can also force it explicitly with a hint: `df_large.join(broadcast(df_small), "key")`. The common failure mode is Spark *not* recognizing a table is actually small enough to broadcast, because statistics are missing or stale (common with data read from raw files rather than a table with maintained stats) — in that case it defaults to a shuffle join even when a broadcast would have been far cheaper, which is exactly why explicit broadcast hints are common in production Spark code rather than relying purely on auto-detection.

Question 40

What's the difference between row-based (CSV) and columnar (Parquet/ORC) file storage formats, and why do analytics pipelines prefer columnar formats?

Accepted Answer

A row-based format like CSV stores all the values of one row contiguously on disk before moving to the next row — reading any single field means reading the whole row it belongs to. A columnar format like Parquet or ORC stores all values of one column contiguously instead, grouped separately from other columns' values.

Analytics queries typically read a subset of columns across a huge number of rows — "sum revenue, grouped by region, for this quarter" only needs the `revenue`, `region`, and `date` columns, not every column in the table. With a columnar format, the query engine can skip reading every other column entirely ("column pruning"), which for a wide table with 100 columns and a query needing 3 of them can mean a 30x-plus reduction in actual bytes read from disk. Columnar formats also compress much better, because values within one column tend to be far more similar to each other than values across a whole row (e.g., a `country` column has low cardinality and compresses extremely well as a dictionary-encoded column, while that same compression opportunity is invisible in a row-major layout).

Parquet additionally stores column statistics (min/max values) per row-group, which lets engines skip entire row-groups that can't possibly match a query's filter (predicate pushdown) without even decompressing them — e.g., a filter on `date >= '2026-08-01'` can skip row-groups whose max date is earlier than that, without reading a single row. This combination — column pruning, compression, and predicate pushdown via stored statistics — is why virtually every modern data warehouse and lakehouse format is columnar, and why raw CSV is treated as a landing/interchange format rather than something you'd query directly at scale.

Question 41

What is the 'small file problem' in data lakes, why does it happen, and how do you fix it with compaction?

Accepted Answer

The small file problem occurs when a table ends up backed by a huge number of tiny files instead of a reasonable number of appropriately-sized ones — commonly caused by streaming jobs that commit a new file on every micro-batch (a Spark Structured Streaming job with a short trigger interval writing every 10 seconds will produce thousands of small files a day), or by over-partitioning a table on a high-cardinality column, spreading what should be one file's worth of data across thousands of partition directories each holding a sliver of it.

The cost isn't storage (small files don't waste much space) — it's metadata and per-file overhead. Every file read requires opening a connection, reading a footer, and (for object storage like S3) making a network round-trip; a query touching 100,000 tiny files pays that overhead 100,000 times even if the total data volume is small, and the query planner/driver also has to track metadata for every single file, which becomes its own bottleneck at scale (this is part of why Hive metastores choke on tables with millions of tiny files).

The fix is compaction: periodically rewriting many small files into fewer, appropriately-sized larger files (typically targeting 128MB-1GB per file depending on the engine). In Delta Lake this is `OPTIMIZE table_name`, in Iceberg it's `rewrite_data_files`, and both can be scheduled as a regular background job that runs after streaming writes accumulate, coalescing them without requiring the streaming job itself to change how it writes. This is exactly the kind of job that then needs to coexist with concurrent streaming writes without commit conflicts — which is where OCC (optimistic concurrency control, as in the Iceberg/Delta question above) and its retry logic become relevant, since compaction and streaming ingestion are now both writing to the same table concurrently.

Question 42

What is Z-ordering (multi-dimensional clustering) in Delta Lake / Iceberg, and how does it improve query performance beyond simple partitioning?

Accepted Answer

Simple partitioning organizes data by one column's exact value into separate directories, which is great for pruning when queries filter on that exact partition column, but does nothing to help queries that filter on a *different* column — if you partition by `date` but frequently filter by `customer_id`, partitioning gives you zero benefit for those queries, since matching customer rows are scattered across every date partition.

Z-ordering solves this by physically co-locating data based on multiple columns simultaneously, using a space-filling curve (a Z-order/Morton curve) to interleave the bits of multiple column values into a single sort key, then sorting and writing files according to that combined key. The effect is that rows with similar values across *several* columns end up clustered together in the same files — so a query filtering on any of those z-ordered columns (not just one) can still skip files whose min/max statistics (stored in the file footer/metadata) don't overlap the filter, which is the same file-skipping mechanism as partition pruning, just working across multiple dimensions at once instead of one.

```sql
OPTIMIZE events_table
ZORDER BY (customer_id, event_type);
```

The practical difference from partitioning: partitioning creates a hard physical directory structure (and too many partition columns causes the small-file problem), while Z-ordering is a soft, statistical optimization applied during file-writing (typically as part of `OPTIMIZE`/compaction) that doesn't fragment the table into more directories — it just improves the odds that a given query's relevant data is concentrated into fewer files, which matters most for high-cardinality columns that would make bad partition keys but are still commonly filtered on.

Question 43

How does Kafka handle partition reassignment during a consumer group rebalance, and what problem does the Cooperative Sticky assignor solve compared to the older eager range/round-robin assignors?

Accepted Answer

When a consumer joins or leaves a Kafka consumer group, the group coordinator triggers a rebalance to redistribute the topic's partitions among the current set of consumers. With the original "eager" assignment protocol (range or round-robin), every consumer in the group has to fully revoke *all* of its currently-assigned partitions before the new assignment is computed and handed out — even partitions that would end up staying with the same consumer after the rebalance get revoked and reassigned. This causes a global stop-the-world pause: no partition in the group is being consumed at all during the rebalance, even the majority that didn't actually need to move.

The Cooperative Sticky assignor (introduced via KIP-429) fixes this by making rebalancing incremental: consumers only revoke the specific partitions that actually need to move to a different consumer, and everything else keeps being processed uninterrupted throughout the rebalance. It does this in two phases — first computing what the new assignment would be, then only revoking the partitions whose owner is changing, followed by a second rebalance round that assigns those specific partitions to their new owners — rather than the eager protocol's blanket revoke-everything-then-reassign-everything.

The practical effect, directly relevant to something like a rebalance storm under network latency: with eager assignment, every rebalance (even one triggered by a single flaky consumer under transient latency) stops processing across the *entire* group. With cooperative sticky, only the specific partitions actually changing hands are paused, so a rebalance is far less disruptive — most partitions in the group keep flowing through the rebalance instead of all of them stalling. This is why `partition.assignment.strategy=CooperativeStickyAssignor` is now the generally recommended default for new consumer groups over the legacy `RangeAssignor`.

Question 44

What is HyperLogLog, and why would you use it instead of COUNT(DISTINCT ...) for cardinality estimation on very large streaming datasets?

Accepted Answer

`COUNT(DISTINCT column)` computed exactly requires tracking every unique value seen — conceptually a hash set that grows with the number of distinct values, which for something like "unique users in the last 30 days" across billions of events can mean gigabytes of memory just to track membership, and that cost doesn't shrink no matter how you optimize the query engine around it, because it's fundamentally a function of the cardinality itself.

HyperLogLog (HLL) is a probabilistic data structure that estimates cardinality using a small, fixed amount of memory (typically a few KB, regardless of whether you're counting a thousand or a billion distinct values) by hashing each input value and tracking statistical patterns in the hash outputs — specifically, the maximum number of leading zero bits observed across many independent hash buckets, which has a known statistical relationship to the number of distinct items that produced it. The trade-off is that it's an *estimate*, typically accurate to within about 1-2% error, not an exact count.

This matters enormously for streaming/large-scale systems because HLL structures are also mergeable — you can compute a partial HLL sketch per hour, per shard, or per streaming window, and merge those sketches together later to get an accurate estimate for a larger time range, without ever needing to re-scan the raw underlying events. BigQuery (`HLL_COUNT.INIT`/`MERGE`), Redis (`PFADD`/`PFCOUNT`), and Presto/Trino all support HLL natively. A concrete example: computing "unique visitors this month" from 30 daily pre-computed HLL sketches by merging them is nearly instant and uses a trivial amount of memory, versus running an exact `COUNT(DISTINCT user_id)` over a month of raw events, which for a high-traffic site could mean scanning and hashing billions of rows on every query.

Question 45

What is a schema registry, and how does it prevent breaking changes from propagating through a Kafka-based pipeline?

Accepted Answer

A schema registry (Confluent Schema Registry is the most common one used with Kafka) is a centralized service that stores and versions the schemas (typically Avro, Protobuf, or JSON Schema) that producers use to serialize messages onto a topic. Instead of embedding the full schema in every message (wasteful) or having consumers just hope the producer's format didn't change, producers register a schema ID with the registry and only send that small ID alongside the serialized payload — consumers fetch the actual schema from the registry by ID to deserialize correctly.

The breaking-change protection comes from compatibility rules the registry enforces at *write* time, before a bad schema ever reaches the topic. You configure a compatibility mode per subject — e.g., `BACKWARD` (new schema can read data written with the previous schema, so consumers on the old schema won't break) or `FORWARD` (old schema can read data written with the new one). When a producer tries to register a schema that violates the configured compatibility rule — say, removing a field that older consumers depend on, or changing a field's type incompatibly — the registry rejects the registration outright, and the producer fails to publish rather than silently pushing malformed or incompatible data onto the topic.

This is a much stronger guarantee than catching schema problems downstream: without a registry, a breaking producer change surfaces as a consumer crash or silent data corruption potentially hours after the bad data was already published and consumed by multiple downstream systems. With a registry enforcing compatibility, the exact same breaking change is rejected at the moment the producer tries to introduce it — before a single bad message is ever written, which is a direct, practical instance of the schema-evolution and schema-desync problems discussed elsewhere in data engineering interviews (like CDC reconciliation) being prevented rather than detected after the fact.

Question 46

How would you design a data pipeline to support GDPR 'right to be forgotten' deletion requests when your primary storage is an append-only lakehouse table?

Accepted Answer

The tension is real: lakehouse formats like Iceberg and Delta Lake are optimized around immutable, append-only writes and time-travel — the entire design assumes old snapshots stay around — while GDPR erasure requires that a specific user's data actually stop existing, including in historical snapshots, which directly conflicts with "keep every version forever."

The practical approach has a few layers. First, at write time, avoid ever landing raw PII directly into fact tables that get widely joined and copied — instead, tokenize or pseudonymize PII into a separate, narrowly-scoped `dim_user_pii` table keyed by a surrogate user ID, so a deletion only ever needs to touch that one small table, not every fact table that references the user. Second, both Iceberg and Delta Lake support row-level deletes (`DELETE FROM table WHERE user_id = ?`), implemented as either merge-on-read delete files or a rewrite of the affected data files — this handles the *current* snapshot, but by itself doesn't erase the data from historical snapshots still referenced by time-travel.

For those historical snapshots, both formats support snapshot expiration/retention policies (`VACUUM` in Delta Lake, `expire_snapshots` in Iceberg) that permanently remove old snapshot metadata and the underlying data files once they're no longer referenced by any retained snapshot — running an expiration pass immediately after a GDPR-driven delete, scoped tightly enough that it doesn't also destroy unrelated, still-needed historical snapshots, is what actually achieves "forgotten" rather than just "hidden from the current view." The full design needs an auditable, orchestrated workflow: delete request received -> row-level delete in current snapshot -> targeted snapshot expiration -> confirmation logged -> and critically, the same deletion propagated to any downstream copies (BI extracts, ML training sets, backups) the lineage graph shows depend on that user's data, since erasing it from the source table doesn't retroactively erase it from every downstream materialization.

Question 47

What is the 'thundering herd' problem in scheduled data pipelines, and how do you avoid it?

Accepted Answer

The thundering herd problem happens when a large number of scheduled jobs are all configured to run at the same clock time — everyone's DAG scheduled for `0 0 * * *` (midnight) — and they all simultaneously hit the same shared resource (a database connection pool, an API rate limit, a warehouse's compute capacity, or the orchestrator's own scheduler) at once, overwhelming it even though the total work, spread out over time, would have been perfectly manageable.

This is exactly what happened in the warehouse-queueing scenario earlier — but it's a more general pattern than just BI-vs-ETL contention. A common real instance: 200 dbt jobs across different teams all scheduled for "midnight, after the daily extract finishes," all hitting the same Snowflake warehouse at 12:00:01am, causing exactly the queuing and contention that hourly-spread scheduling wouldn't.

The fix is to deliberately stagger schedules rather than let every job cluster on a round clock number — e.g., spreading jobs across a window (`0-59/5 0 * * *` style staggering, or explicit per-job offsets like `:03`, `:08`, `:13`) so they hit shared resources in a trickle instead of a spike. For API-rate-limited external calls specifically, adding jitter (a small random delay before execution) to each job's start time achieves the same smoothing effect without needing to hand-schedule exact offsets. At the infrastructure level, the warehouse-isolation and auto-scaling approach from the BI/ETL contention question is the more robust fix, since it addresses the underlying shared-resource bottleneck rather than just spreading out when jobs ask for it — staggering helps, but isolating truly independent workloads onto separate compute removes the contention entirely rather than just reducing its peak.

Question 48

What is a watermark in stream processing, and how does it let systems like Flink or Spark Structured Streaming handle late-arriving, out-of-order events in windowed aggregations?

Accepted Answer

In stream processing, events don't necessarily arrive in the order they actually happened — network delays, retries, and mobile devices going offline mean an event timestamped 9:00am might not reach the pipeline until 9:15am, after events timestamped 9:10am have already arrived. For a windowed aggregation ("count events per 5-minute window"), the system needs some way to decide when a window is "done" and safe to emit results for, even though it can never be 100% sure no more late events will ever show up for that window.

A watermark is the stream processor's declared estimate of event-time progress: "I believe all events with timestamps earlier than time T have now been seen." It's typically computed as the maximum event timestamp observed so far, minus some allowed lateness/slack (e.g., "5 minutes behind the latest timestamp seen"). When the watermark passes a window's end boundary, the engine considers that window closed and emits its aggregated result — any event that arrives *after* that point, with a timestamp inside an already-closed window, is by definition "too late."

What happens to that too-late event depends on configuration: it can be dropped (simplest, but loses data), routed to a separate "late events" side-output for manual handling, or — if the window is configured with allowed lateness (Flink's `allowedLateness`, Spark's watermark delay threshold) — trigger a retraction and updated result for that window even after it initially closed, at the cost of having to handle downstream consumers seeing a corrected/updated value for a window they thought was already final. The core trade-off watermarks manage is latency versus completeness: a longer watermark delay waits longer before closing windows, catching more late data, but delays every result by that same amount — there's no free way to guarantee both instant results and zero missed late events, so the actual watermark delay you pick is a deliberate business decision about how much lateness you're willing to tolerate.

Question 49

What is the difference between Lambda architecture and Kappa architecture for building data pipelines that need both real-time and historical/batch views?

Accepted Answer

Lambda architecture runs two entirely separate pipelines against the same source data: a batch layer that periodically reprocesses the full historical dataset to produce accurate, complete results, and a speed layer that processes the same data in real time via streaming to give low-latency (but potentially less complete or accurate) results in the meantime, with a serving layer merging both views for queries. The rationale was that batch systems could guarantee correctness over the full history while stream processors of that era couldn't be trusted for exactly-once, fully accurate results — so you ran both and reconciled.

The major downside of Lambda is that you're maintaining two separate codebases implementing essentially the same business logic — one in a batch framework, one in a streaming framework — which is expensive to build and a real source of bugs when the two implementations drift and produce different answers for the same underlying question.

Kappa architecture is the response to that: treat everything as a stream, including "batch" processing, which becomes just replaying the stream from an earlier offset through the same streaming pipeline. There's one codebase, one processing model — if you need to reprocess history (e.g., after fixing a bug in the transformation logic), you replay the historical events from Kafka (retained long enough, or backed by tiered/infinite storage) through the same stream processor rather than through a separate batch system. Kappa became practical once streaming engines (Flink, Kafka Streams) matured enough to handle exactly-once semantics and large-scale reprocessing reliably — which is why most new architectures today default to Kappa-style (one unified stream-processing codebase) rather than maintaining Lambda's dual-codebase batch-plus-speed-layer design, reserving Lambda specifically for cases where a genuinely different, more thorough batch algorithm is needed than what the streaming engine can practically compute incrementally.

Question 50

What is a data contract, and how does it differ from just having good documentation between a data producer team and a data consumer team?

Accepted Answer

Documentation describes an agreement in prose — it tells a consumer what a table's columns mean and what format to expect, but nothing actually stops the producing team from changing the schema, semantics, or delivery guarantees without updating that documentation, and nothing automatically tells the consumer when a mismatch happens. In practice, documentation drifts from reality constantly because there's no enforcement mechanism tying the two together.

A data contract is that same agreement made machine-enforceable: a formal, versioned specification (schema, types, nullability, semantic meaning, SLAs like freshness and completeness) that's checked automatically, typically as part of the producer's CI/CD pipeline, before a change can ship. If a producer's code change would violate the contract — dropping a column a consumer depends on, changing a field from non-nullable to nullable, breaking an SLA — the contract check fails the build, the same way a schema registry's compatibility check rejects an incompatible schema before it reaches a Kafka topic (the same idea, applied more broadly than just serialization format).

Concretely, a data contract for an `orders` table might be a YAML/JSON spec declaring `order_id: required, int, unique`, `status: required, enum[pending,shipped,cancelled]`, and an SLA of "updated within 15 minutes of the source event." Tools like the Data Contract Specification, or contract-testing built into dbt via schema tests plus CI gates, implement this in practice. The organizational shift a data contract represents is treating internal data producers the way you'd treat any external API provider — consumers get an explicit, versioned, enforced guarantee about what they're depending on, rather than an informal understanding that can silently break whenever the producing team ships an unrelated change.

Data Engineer Interview Questions & Answers

Ready to practice these live?