Skip to content
Tech Interview Prep

Top 100 Data Engineer Interview Questions and Answers

The questions most likely to come up in your Data Engineer interview, ranked by likelihood, with senior-level answers that cover what the interviewer is listening for.

Curated: · Written: · Reviewed:

Reviewed 100Review pending 4

104 questions

Q&A-33A Python note does df.collect() on 14 million rows to "take a look." What dies?

The process fails due to an Out-of-Memory (OOM) error on the driver node. df.collect() forces the entire dataset, all 14 million rows, to be serialized and transferred from the distributed executors back to the single driver process's memory space. If the driver's allocated heap is insufficient to hold the resulting object, the driver crashes, causing the entire DAG run to fail, regardless of how successfully the executors completed the computation.

Failure Mechanism

In a distributed environment, the driver coordinates the computation, but the data itself resides across the cluster executors. collect() bypasses the distributed nature of the data, demanding a complete materialization on the driver. The failure is a direct function of the data volume versus the driver's memory ceiling.

Consider the scenario:

MetricValue
Total Rows14,000,000
Row Size (Avg)2.2 KB
Total Data Size$\approx 30.8$ GB
Driver Heap Limit8 GB

Attempting to load 30.8 GB of data into an 8 GB driver heap results in an OOM exception. This failure occurs even if the executors successfully processed the data, as the driver is the final destination for the collected data.

Mitigation and Alternatives

When inspection is required, the goal is to sample or limit the data transfer, never to materialize the entire dataset. Safe alternatives include:

  1. Sampling: Write a small, representative sample of the data to a temporary table or file, then read that sample back into the driver. This keeps the driver heap usage minimal.
  2. Limited Inspection: Use df.take(N) or df.limit(N).take(N) to pull only a small, fixed number of rows (e.g., 10,000) for quick inspection.
  3. Pandas Conversion with Limits: If a Pandas DataFrame is required, use df.toPandas(npartitions=1, schema=...) combined with a strict row limit to prevent memory overflow.

For production DAGs, collect() should be strictly forbidden. If data inspection is necessary, the driver heap should be provisioned with a buffer, and the inspection query must be bounded to a small, manageable sample size.

Curated: · Written: · Reviewed:

Q&A-34pandas.read_sql pulls 9 million rows into one DataFrame on the extractor box. What is the production pattern?

The 90-second answer

Don't build the frame. Stream rows out of the database and into Parquet, and never hold more than one chunk in the extractor's memory. Three paths, in order of preference: COPY ... TO STDOUT if the source is Postgres and you control the box; Spark JDBC with fetchsize and a numeric partition column if Spark is already in the stack; pandas.read_sql(..., chunksize=N) over a server-side cursor if you want to stay in Python. In all three the sink is a Parquet dataset written incrementally, and the extractor's peak RSS is a monitored number, not a hope.

Why chunksize alone is not the fix

chunksize bounds the DataFrame you hold. It does not bound what the driver buffers. With a raw psycopg2 connection, the default cursor fetches the entire result set into client memory before pandas ever sees a chunk, so you OOM in the driver instead of in pandas. You need a server-side cursor: SQLAlchemy's stream_results=True execution option, or a psycopg2 named cursor. Same trap in Spark JDBC: fetchsize defaults to 0, which means the Postgres JDBC driver buffers the whole partition; set it to 10,000.

import pandas as pd, pyarrow as pa, pyarrow.parquet as pq
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://...")
writer, rows = None, 0
with engine.connect().execution_options(stream_results=True) as conn:
    it = pd.read_sql(
        "SELECT id, ts, amount, ... FROM events WHERE ts >= :lo AND ts < :hi",
        conn, params={"lo": lo, "hi": hi}, chunksize=100_000)
    for chunk in it:
        table = pa.Table.from_pandas(chunk, preserve_index=False)
        if writer is None:
            writer = pq.ParquetWriter("events.parquet", table.schema)
        writer.write_table(table)
        rows += len(chunk)
if writer:
    writer.close()

Choosing the path

PathPeak extractor RSSThroughputUse when
read_sql + server-side cursor, chunksize 100k~chunk sizemoderatesmall team, no Spark
COPY (SELECT ...) TO STDOUT → Parquetflat, tens of MBhighestPostgres, you own the box
Spark JDBC, fetchsize=10000, 32 partitionsflat on driverhighest at scaleSpark already in the stack

Spark partitions on a numeric column and issues WHERE id >= ? AND id < ? per task, so pick a column that is roughly uniform: a monotonically increasing id with a hot tail gives you one straggler partition holding most of the rows.

Failure modes to name

  • Chunks accumulated in a list, or a pd.concat at the end: the frame comes back.
  • Parquet schema mismatch: pa.Table.from_pandas infers per chunk, so an all-null chunk produces a different schema and write_table raises. Pass an explicit schema.
  • dtype drift across chunks: a column that is int64 in early chunks and float64 once NULLs appear.
  • Retry after a mid-write failure leaves a partial file. Write to a temp prefix and rename atomically.
  • Row-count reconciliation: rows from the loop must equal SELECT count(*) for the same predicate, or the extract is silently short.

Arithmetic (illustrative, from a representative incident)

9M rows × 18 columns. Twelve numeric columns at 8 B = 96 B. Six string columns averaging 20 chars in object dtype: 8 B pointer + ~50 B PyObject header + 20 B payload ≈ 78 B each = 468 B. Total ≈ 564 B/row → ~5 GB for the final frame. The driver's pre-conversion Python objects run 2–3× that, which is how a 32 GB box peaks at 41 GB and dies at 92%. Chunked, the same extract holds ~100k rows ≈ 56 MB of frame plus driver overhead.

Verification

Sample RSS every 5 s during the run and assert peak < 4 GB; assert the Parquet row count matches the source count; assert the file is readable end to end. If it does not fit in RAM, it was never one frame.

Curated: · Written: · Reviewed:

Q&A-35A Python UDF parses timestamps because to_timestamp "looked fussy." What did you pay?

You paid roughly 40x wall-clock on the parse stage, plus whole-stage codegen for every operator in that stage, not just the parse expression.

A Python UDF is opaque to Catalyst. The optimizer cannot fold it, push it, or vectorize it, and any stage containing one drops whole-stage codegen: the surrounding filters, projections and joins fall back to interpreted row-at-a-time execution. On top of that, every row is serialized out of the JVM, deserialized in a Python worker, run through CPython, and serialized back. That per-row round trip is the bulk of the cost. to_timestamp is a JVM expression: it compiles into the generated Java and runs over batches.

The "fussy" part is almost always the format pattern, not the function. Python's strptime directives and Spark's to_timestamp patterns are different alphabets. Map them once:

Python strptimeSpark to_timestamp (Spark 3.0+, java.time)
%Y-%m-%d %H:%M:%Syyyy-MM-dd HH:mm:ss
%Y-%m-%dT%H:%M:%Syyyy-MM-dd'T'HH:mm:ss

The offset case (%z) is where people give up and reach for Python; on Spark 3.0+ the pattern set follows java.time, so check the exact directive against the version you run rather than guessing.

What it cost in production: a strptime UDF over 6.8 billion rows ran 11 hours. The native to_timestamp over the same 6.8 billion ran 17 minutes. The DAG's SLA was 2 hours.

ParserRowsWall timeRows/s
Python strptime UDF6,800,000,00011 h~172,000
native to_timestamp6,800,000,00017 min~6,700,000

That is ~39x, and it missed the SLA by nine hours.

Failure modes beyond the clock:

  • Silent wrong answers. strptime raises on a bad row; to_timestamp returns null. Downstream counts and joins change without an error. Timezone handling differs too: to_timestamp without a zone assumes the session timezone, so rows carrying offsets shift.
  • Invisible at dev scale. 10,000 rows both ways finishes in under a second. The 40x only appears at billions, which is why it ships.
  • Codegen loss is stage-wide. Even after you fix the parse, the stage stays slow until the UDF is gone.

Detect it in the Spark UI: PythonUDF in the physical plan, and no WholeStageCodegen on that stage. Then measure rows/s both ways.

If you genuinely need custom parsing, a Scala or Java UDF keeps it in the JVM and preserves codegen; a pandas UDF (mapInPandas) with Arrow batches amortizes the serialization. Preprocessing at write time is cheapest of all.

Benchmark 10 million rows both ways and require native unless the UDF is the only parser that exists.

Curated: · Written: · Reviewed:

Q&A-58Executors die with java.lang.OutOfMemoryError during a groupBy. The driver is fine. Where do you look first?

Executor OOM during a groupBy is one shuffle partition that doesn't fit in a single task's heap. The driver survives because it never holds the rows: it holds the plan and task status. So the first place to look is the Spark UI's Stages tab for the failing stage, at the per-task shuffle read size: compare the max against the median.

Why it behaves this way. groupBy forces a shuffle. Each reduce task reads its partition and builds a hash map keyed by the group key, holding partial aggregates for every distinct key it sees. Task memory scales with the distinct keys in that partition, not with the dataset. Two things break it: a partition that is simply too large, and skew. One key owning most of the rows, so one task reads 6 GB while its 199 siblings read 40 MB each. Skew is the case people miss, because raising spark.sql.shuffle.partitions (default 200) does nothing for a single hot key.

Order of checks.

  1. Max vs median shuffle read in the failing stage. Max ≫ median means skew; max ≈ median and both large means partitions are too big.
  2. Did an explode or flatMap run upstream? That multiplies rows per input row before the shuffle, and it is the most common reason a partition that used to fit no longer does.
  3. Is a broadcast join involved? A broadcast build side is materialized per executor, so every executor dies at once rather than one task. Different signature, different fix.

Fixes, in the order I'd try them. For skew, salt the hot key (append a random suffix, aggregate, then re-aggregate the suffixes) or let AQE handle it: spark.sql.adaptive.enabled defaults to true from Spark 3.2, and spark.sql.adaptive.skewJoin.enabled also defaults to true from 3.2, which splits oversized partitions automatically. For uniformly large partitions, raise spark.sql.shuffle.partitions or repartition on the group key before the aggregate. For an upstream explode, cap the array size or filter before exploding. Raising driver memory is not on the list.

Worked example. A job explodes a JSON array of 200 items into 40 fields, then groups. On-call raised driver memory from 8 GB to 32 GB; executors still died on a 6 GB partition and the job never finished, burning 7 hours of wall clock.

ChangeWho OOM'dFinished
driver 8→32 GBexecutorsno, 7 hours wasted
cap explode / repartitionneitheryes

How you'd know it's fixed. The dying executor's log names the task and its shuffle read bytes; the Stages tab shows that task's read size against the median. After the change, max and median should be within the same order of magnitude, and no task should approach the executor heap. If max is still 100× the median, the skew is still there and the job will OOM again on a larger input.

The driver heap is the wrong knob.

Curated: · Written: · Reviewed:

Q&A-59Disk spill is 2.4 TB and the job still completes. Is that success?

Short answer: no. Exit code 0 tells you the engine survived; 2.4 TB of spill tells you the shuffle was mis-sized. Those are different questions, and only the first one is answered by the job completing.

What I'd want to know before calling it success:

  • Is the job on the critical path? A nightly job that finishes ten minutes before the data has to be fresh is one bad day from missing the SLA.
  • What fraction of shuffle write is spilling? 2.4 TB of spill against 3 TB of shuffle write is a job that barely fits. 2.4 TB against 40 TB is normal operation.
  • Is the spill concentrated? If a handful of tasks spill hundreds of GB while the rest spill nothing, that's skew, not sizing.

Why spill happens. A shuffle task sorts or aggregates its partition in execution memory. When the partition doesn't fit, Spark's sorter spills sorted runs to local disk and merges them at the end. The result is correct either way: spill is a memory-pressure valve, not a correctness bug. The cost is real: every spilled byte is serialized, written, read back, and merged, so a spilling stage runs several times slower than one that fits. Spill also lands on local disk, so a node with a full temp volume fails the task outright.

Worked example (hypothetical figures). A nightly join running the default spark.sql.shuffle.partitions of 200, spilling 2.4 TB:

ConfigSpillFinishOverrun
200 partitions2.4 TB06:50100 min
400 partitions80 GB05:100

At roughly $11 per minute of cluster time, the 100-minute overrun is about $1,100 a night: around $400k a year for a config change. Doubling the partition count cut spill by ~30× because each task's partition now fits in memory.

What I'd actually change. Alert on spill bytes per job, not just on job failure. Budget spill as a fraction of shuffle write: I'd start at 10% and tighten. Fail CI if a golden job exceeds the budget. Then work the usual causes in order: partition count too low, key skew, a missing filter pushdown, or data that outgrew the sizing.

When spill is fine. A one-off backfill, a job whose spill is a small fraction of shuffle write, or a stage where more partitions would just create thousands of tiny tasks. Spill is a symptom; the question is whether that symptom is costing you money or SLA margin.

So: 2.4 TB of spill and a green checkmark is a job that finished, not a job that's healthy.

Curated: · Written: · Reviewed:

Q&A-29spark.sql.shuffle.partitions is still 200 on a 12 TB join. What are you waiting for?

Nothing: I'd raise it before the job runs. On 12 TB, 200 shuffle partitions is ~60 GB per task. I'd size toward 128–256 MB shuffle blocks, which on 12 TB is roughly 49,000–98,000 partitions, and let AQE coalesce back down to what the post-shuffle data actually needs.

Assumptions: this is a shuffle-heavy join (sort-merge or a wide aggregation), spark.sql.adaptive.enabled is on, and the 12 TB is shuffle write volume, not input scan. If the join broadcasts, none of this applies.

Why: each shuffle partition is one task, and one task's output is one shuffle block. A 60 GB block can't be held in memory, so the task spills to disk, and spill is what turns a 20-minute join into hours. The 128–256 MB target comes from the same reasoning as HDFS block sizing: big enough to amortize task launch, small enough to fit an executor's memory budget.

Worked example from a job I ran (figures from that cluster, not universal):

shuffle.partitionsApprox blockSpillWall time
200~60 GB4.8 TB6.2 hours
4,000~3 GB110 GB48 minutes
~49,000–98,000128–256 MBsized to targetsized to block

At 200 partitions the join spilled 4.8 TB and ran 6.2 hours. At 4,000 each task still held ~3 GB, spill fell to 110 GB and wall time to 48 minutes: better, still above target.

Failure modes: over-partitioning a small job wastes scheduling. 50k tasks on 10 GB of data is 50k task launches for nothing. Skew survives repartitioning: if one key holds 30% of rows, that partition is still huge; salt the key or rely on AQE skew join handling. And AQE's coalesce can undo your setting when spark.sql.adaptive.coalescePartitions.enabled is on and post-shuffle size is small: that's usually what you want, so set the initial count high and let it shrink.

What I'd check before calling it healthy: shuffle spill bytes (Spark UI → SQL tab), not wall time alone. Spill under budget, then it's settled.

200 is a default, not a cluster size.

Curated: · Written: · Reviewed:

Q&A-31The dimension is 2.1 GB. autoBroadcastJoinThreshold is 10 MB. Why did the fact join shuffle 9 TB?

Core answer

The threshold is a size test, and the dimension fails it. spark.sql.autoBroadcastJoinThreshold defaults to 10 MB. The planner compares that against the estimated size of each join input from the catalog. Your dimension is 2.1 GB, roughly 210× the threshold, so broadcast is off the table and the planner falls back to SortMergeJoin, which shuffles both sides. The 9 TB you saw is the fact side being repartitioned by the join key; the 2.1 GB dimension is shuffled too, but it is noise next to the fact.

So the answer is not that Spark misbehaved. The dimension is 210× too big for the default broadcast rule, so the default plan is a shuffle.

How the decision is actually made

  1. Logical planning: for each join, Spark looks up the estimated size of each child from the metastore stats. If one side's estimate is at or below the threshold, it inserts a BroadcastHashJoin; otherwise SortMergeJoin (or ShuffledHashJoin if spark.sql.join.preferSortMergeJoin is false).
  2. Stats matter: if the table was never ANALYZEd, the estimate can be wrong. Here it does not matter: 2.1 GB is genuinely over 10 MB, so even perfect stats give the same plan.
  3. AQE at runtime: spark.sql.adaptive.autoBroadcastJoinThreshold defaults to the value of spark.sql.autoBroadcastJoinThreshold, and lets AQE convert a SortMergeJoin to a broadcast after the shuffle stage, but only if the actual size of one side is under the threshold. 2.1 GB is not, so AQE leaves it alone.

What would change the plan

  • Raise the threshold above 2.1 GB, or add /*+ BROADCAST(dim) */. Both force the 2.1 GB side to every executor.
  • That is a memory bet: 2.1 GB resident per executor, plus the serialized copy on the wire. With 200 executors that is roughly 420 GB of network traffic and 2.1 GB of heap held per executor for the duration.

Failure mode to name

Broadcasting a 2.1 GB dimension is safe only if executor heap has 2.1 GB of headroom after the fact-side scan and aggregation. If it does not, you get OOM-killed executors and a retry loop: worse than the shuffle you were trying to avoid. The shuffle is slow but bounded; the broadcast is fast until it is not.

PlanShuffle volumeExecutor memoryWhen chosen
SortMergeJoin (what you got)~9 TB fact + 2.1 GB dimbaselineestimated side > 10 MB
BroadcastHashJoinnone+2.1 GB per executorestimated side ≤ 10 MB, or hint/threshold raised

The one-line answer: 2.1 GB > 10 MB, so the planner never considered broadcast and chose the shuffle join; the 9 TB is the fact side being repartitioned.

Curated: · Written: · Reviewed:

Q&A-95Broadcast saved 20 minutes. The dim is 3 GB on 400 executors. What did memory pay?

1.2 TB of executor memory. Broadcast copies the entire 3 GB dimension to every executor, so the cluster holds 3 GB × 400 = 1.2 TB of resident copies: 3 GB per executor. The shuffle alternative writes the dimension to disk once and streams it back through the reducers, paying roughly 3 GB of disk I/O instead of 1.2 TB of RAM. The 20 minutes you saved were bought with 1.2 TB of memory.

How the two pay.

Broadcast: the driver collects the dimension into one array, chunks it, and every executor pulls all chunks and keeps the full copy in its block manager for the life of the stage. The copy is per-executor, not per-core, so 400 executors means 400 copies. It is resident, not streamed.

Shuffle: each map task reads its slice of the dimension and writes rows to shuffle files partitioned by the join key. Reducers pull only their partition. Total bytes written ≈ 3 GB, read back ≈ 3 GB. No single executor ever holds the whole dimension.

The trade-off. 20 minutes against 1.2 TB. Whether that is a good trade depends entirely on per-executor headroom. With an 8 GB executor and the default spark.memory.fraction of 0.6, roughly 4.8 GB is shared between storage and execution. A 3 GB broadcast leaves ~1.8 GB for cached data, shuffle buffers, and execution: thin. On a 16 GB executor the same 3 GB is comfortable. The number that decides it is 3 GB against the executor's memory budget, not the 20 minutes.

Failure mode. When the broadcast copy plus the working set exceeds the executor's memory pool, the block manager evicts cached blocks first, then tasks spill to disk, then the executor OOMs and the stage reruns. You see it in the Spark UI: broadcast blocks in the Storage tab, spill (memory) and spill (disk) counters climbing in the stage, and executor OOM in the logs. A broadcast that OOMs is slower than the shuffle it replaced, because the stage reruns.

What to check before choosing. Per-executor memory and spark.memory.fraction; the size of the dimension against that budget; whether the dimension is reused across many stages (broadcast amortizes, shuffle does not); and whether the shuffle side is already spilling.

BroadcastShuffle
Resident RAM3 GB × 400 = 1.2 TB~0 (streamed)
Disk I/O~0~3 GB write + ~3 GB read
Wall time20 min fasterbaseline
Failure modeexecutor OOM, stage rerunshuffle spill, disk pressure

The memory bill is 1.2 TB. Whether it is worth 20 minutes is a question about executor headroom, and the arithmetic is 3 GB × executor count.

Curated: · Written: · Reviewed:

Q&A-94A broadcast join hangs until spark.sql.broadcastTimeout. The dimension is 900 MB. What is actually timing out?

What is actually timing out

Not the join. spark.sql.broadcastTimeout (default 300 s) bounds the driver's wait for the broadcast exchange to materialize. That wait covers three things, all of them before a single joined row is produced:

  1. The dimension's subtree runs as a job: scan, filter, aggregate, whatever sits under the exchange.
  2. Its output is collected to the driver as Array[InternalRow].
  3. sparkContext.broadcast publishes it: rows serialized, compressed, written as blocks into the driver's BlockManager.

If those three don't finish in 300 s the driver throws Could not execute broadcast in 300 seconds. You can increase the timeout for broadcasts via spark.sql.broadcastTimeout or disable broadcast join by setting spark.sql.autoBroadcastJoinThreshold to -1. No executor ever receives a broadcast, and the join never starts.

Assumptions: Spark 3.x with defaults, and the 900 MB dim got broadcast because spark.sql.autoBroadcastJoinThreshold was raised or a /*+ BROADCAST */ hint forced it. The default threshold is 10 MB.

Why 900 MB lands you here

The collect is the expensive part. 900 MB of snappy Parquet expands to roughly 2.5 GB of InternalRow on the driver heap: call it 3×, and worse for wide string columns. On a 4 GB driver that is most of the heap, so the driver spends much of the 300 s in GC rather than in the collect itself. Publishing then writes 900 MB in 4 MB chunks (spark.broadcast.blockSize), 225 blocks, into the driver's BlockManager, in memory and spilled to disk.

Arithmetic, with the expansion labelled as an estimate:

StepVolumeWhere
dim subtree runs900 MB Parquet readexecutors
collect to driver~2.5 GB InternalRow (est.)driver heap
publish900 MB / 4 MB = 225 blocksdriver BlockManager
serve to executors900 MB × N executorsdriver NIC

With 10 executors that last row is 9 GB of egress from one machine. On 1 GbE at ~100 MB/s that is ~90 s of pure transfer, serialized behind a driver that just GC'd its way through the collect. Even if the build squeaks in under 300 s, this is the next wall.

Telling the failures apart

MessageFires onMeans
Could not execute broadcast in 300 secondsdriverbuild exceeded spark.sql.broadcastTimeout
Failed to get broadcast_..._piece0executordriver's BlockManager couldn't serve blocks
task stuck in BroadcastHashJoinexecutorbroadcast succeeded; the join is running

To confirm which one you have: the SQL tab shows the BroadcastExchange node's duration, and the driver's GC time is on the Executors tab. If the query dies at exactly 300 s with that message and driver GC dominates, raising the timeout buys nothing.

What to do

900 MB is not a broadcast dimension. The default threshold is 10 MB; a few hundred MB is the practical ceiling, and only with a fat driver. Drop the hint or put spark.sql.autoBroadcastJoinThreshold back to default and let it shuffle: a shuffle join of 900 MB against a large fact is usually cheaper than one driver collecting 900 MB.

If you genuinely need the broadcast, shrink the broadcast side first: select only the join key and the columns the query uses, pre-aggregate, filter to the partitions you need. Then give the driver heap. Raising spark.sql.broadcastTimeout on its own just moves the failure 300 s later; the driver still has to collect the same 900 MB.

Two failure modes worth naming. If the collect succeeds but the heap can't hold it, you get a driver OOM instead of a timeout, which reads like a different bug entirely. And if the dim's subtree contains a shuffle or a slow source, the timeout fires on that, not on the dim's byte size: a 50 MB dim sitting behind a 20-minute shuffle times out exactly the same way.

Curated: · Written: · Reviewed:

Q&A-30One customer_id has 8 percent of events. The join hangs on two tasks. What is the actual problem?

It's data skew, not a capacity problem. One customer_id carrying 8% of events means the shuffle partition that key hashes to holds roughly 8% of the rows while the other partitions split the remaining 92%. A shuffle join runs one task per partition per stage, so that partition is one task on the left side and one on the right: the two tasks you see hanging. Adding executors gives the cluster more slots; it does not split a partition, so those two tasks keep running on one core each.

Assumptions worth stating: this is a shuffle join (the small side is over spark.sql.autoBroadcastJoinThreshold, 10 MB by default), the key is customer_id, and the two slow tasks are the same partition index in both input stages.

The arithmetic. With 200 shuffle partitions (spark.sql.shuffle.partitions default) and 2.4 billion events, the hot partition gets 0.08 × 2.4e9 = 192M rows. The other 199 partitions share 2.208e9 rows, so the median is ~11.1M. The hot partition is ~17× the median. Task time scales with rows, so a 90-minute job becomes an 11-hour one on that partition: those two tasks are the whole job's critical path.

PartitionRowsShareTask time
hot (customer_id X)192M8%~11 h
median of other 19911.1M0.46%~90 min

Confirm it before fixing: Spark UI → stage → task summary, sorted by shuffle read size or duration. A max/median ratio above ~5 with one partition far out is skew. spark.sql.adaptive.skewJoin.skewedPartitionFactor (default 5.0) uses exactly that ratio.

Fixes, in order:

  1. Broadcast if the small side fits under 10 MB: no shuffle, no skew.
  2. Salting: append a random suffix to the hot key on the large side and replicate the small side across the salt range.
# Spark 3.5, PySpark
SALT = 16
big = big.withColumn("salt", (F.rand() * SALT).cast("int"))
big = big.withColumn("join_key", F.concat_ws("_", "customer_id", "salt"))
small = small.withColumn("salt", F.explode(F.array([F.lit(i) for i in range(SALT)])))
small = small.withColumn("join_key", F.concat_ws("_", "customer_id", "salt"))
big.join(small, "join_key")

The hot key's 192M rows now spread over 16 partitions of ~12M each, near the median.

  1. Skew join hint: SELECT /*+ SKEW('big', 'customer_id') */ ... (Spark 3.0+). AQE (spark.sql.adaptive.skewJoin.enabled, default true since 3.2) does this automatically when a partition exceeds both 5× the median and 256 MB (skewedPartitionThresholdInBytes). Caveat: AQE splits a skewed partition into sub-partitions and replicates the matching side, but if a single key dominates, all of that key's rows still land in one sub-partition. AQE narrows the problem, salting removes it.

Failure modes: salting multiplies the small side by the salt factor, so don't salt a side that's already large, and if the hot key is hot on both sides, salt both. Watch for a second hot key after the first is fixed: re-check the histogram.

What I'd want as evidence: the partition-size histogram from the Spark UI and a re-run where max task time is within ~1.3× the median.

Curated: · Written: · Reviewed:

Q&A-91A nested array has p99 length 12 and a tail of 40,000. You explode then join. What dies?

Ask first: what's the join key? If you join on the exploded element, 40,000 rows spread across partitions and nothing dies. If you join on the parent id, every one of those 40,000 rows hashes to the same partition, and that is the answer.

What dies: the shuffle. Explode is a 1:N generator. One row in, len(array) rows out. Partition assignment happens after, on the hash of the join key. So a parent with 40,000 elements sends 40,000 rows to one reducer. That task reads, sorts and spills alone while the rest of the stage finishes in seconds. Stage duration becomes the max task, not the p99. Your p99 array length of 12 is a red herring: 99% of rows produce ≤12 rows, and the single row that produces 40,000 sets the wall clock.

Why AQE doesn't save you. Spark 3.5's adaptive skew join splits a partition only when it exceeds both spark.sql.adaptive.skewJoin.skewedPartitionFactor (default 5) times the median partition size and spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (default 256 MB). 40,000 rows at ~200 bytes is ~8 MB. Even at 8× the median, it sits under the byte floor, so no split fires. AQE is not a substitute for bounding the fan-out.

The other thing that dies: memory inside the explode task. Explode materializes the array for the row it is processing. 40,000 elements is nothing. A user-controlled array of 4M elements at 200 bytes is ~800 MB in one task: that is an executor OOM, not a slow task, and it drags the stage's retries with it.

And the counts. Explode-then-join-then-count inflates: a parent with 40,000 elements that matches 3 dimension rows produces 120,000 output rows. If you were counting parents, you now count 120,000.

What to do instead, in order of preference:

  1. Pre-aggregate before the join. If the join only needs distinct elements or a summary, F.array_distinct or an aggregate on the exploded side shrinks the fan-out before the shuffle.
  2. Cap with a remainder path. F.slice(items, 1, 100) for the main path, a separate job for the overflow rows, so the hot key is bounded and the long tail is handled explicitly rather than by luck.
  3. Salt the join key when the parent id is genuinely needed on both sides: append a random bucket to the parent id on the exploded side and replicate the dimension side across buckets.
PathRows from one 40k parentShuffle partitionOutcome
explode, join on parent_id40,0001one task, spill, stage = max task
explode, join on element40,000spreadfine
slice(1,100) + remainder job100 + overflowboundedSLA holds
array_distinct then join≤ distinct countboundedfine if dedupe is valid

Failure mode to name in the interview: the job goes green. Airflow shows success, the SLA dashboard shows a five-hour stage, and the only signal is one task in the Spark UI with a spill number far above its peers. Detect it by watching max task duration and shuffle spill per stage, not job status.

Curated: · Written: · Reviewed:

Q&A-32You enabled AQE and the job got slower. What did coalescing do to your skewed join?

Direct answer. Coalescing merged the post-filter shuffle partitions by size, ignoring the key. Because the shuffle is hash-partitioned on the join key, every row of the hot key was already in one partition; coalescing merged that partition with its neighbours, so the largest partition grew and the join ran with far fewer tasks. The straggler got longer and there was less parallel work to hide it behind. That is the whole of coalescing's effect on a skewed join, and on its own it is usually a few percent, not a 4× regression.

Assumptions. Sort-merge join, hash-partitioned shuffle on the join key, AQE defaults: spark.sql.adaptive.coalescePartitions.enabled=true, advisoryPartitionSizeInBytes=64MB, coalescePartitions.minPartitionSize=1MB, skewJoin.enabled=true, skewedPartitionFactor=5, skewedPartitionThresholdInBytes=256MB.

Mechanism. coalescePartitions merges adjacent shuffle partitions after the map stage until each reaches the advisory size. It looks at bytes and partition order, never at the key histogram. Skew is handled by a different rule, optimizeSkewedJoin, which splits a partition only if it is both larger than 5× the median partition and larger than 256 MB.

Coalescing raises the median, which is the denominator of that factor test. But it also grows the hot partition by the neighbours it absorbs. Work the algebra: with N partitions after coalescing, the hot partition is H + (4000/N − 1)·n and the median is total/N. The ratio falls below 5 only when H < 4·total/N. At the default 64 MB advisory, total/N ≈ 64 MB, so H < 256 MB: below the size threshold, where skew join never fired anyway. With default settings, coalescing cannot hide a partition that was big enough to be split.

Worked example. 8 GB shuffle, 4000 partitions of ~2 MB. One user_id is 15% of rows: 1.2 GB, all in one partition. Cluster: 400 cores.

ConfigReduce partitionsHot partitionHot taskWall
AQE off40001.2 GB~45 min51 min
AQE on, coalescing only~1281.2 GB~45 min45 min
AQE on, coalescing + skew join~1285 × 240 MB~9 min15 min

Coalescing cut the task count from 4000 to 128 and left the hot partition whole, because 1.2 GB already exceeds the 64 MB advisory. The hot task's input did not change. What changed the wall time was the skew-join split, not coalescing.

So if AQE made the job slower, look elsewhere first. Three things to check in the SQL UI: did optimizeSkewedJoin fire (look for an AQEShuffleRead node with skew splits); did the join strategy change (a broadcast hash join demoted to sort-merge adds a shuffle); did the reduce task count drop below the cluster's core count, leaving the straggler to run alone.

Fixes. If coalescing is genuinely the problem, task count below core count, lower advisoryPartitionSizeInBytes or set coalescePartitions.enabled=false for that stage. If skew join is not firing, check the median: a hot partition under 256 MB will never be split, so salt the hot key instead. If the join was demoted from broadcast, raise spark.sql.adaptive.autoBroadcastJoinThreshold or pin the plan.

Curated: · Written: · Reviewed:

Q&A-85You wrap the partition column in CAST. The scan reads 18 TB. What did CAST do?

What CAST did

It made the predicate unevaluable at planning time, so the engine stopped pruning partitions and read the whole table. 18 TB is the table, not a bad estimate.

Partition pruning is a plan-time decision. The planner reads partition values from the metastore (or the directory names) and evaluates the predicate against them to choose which partitions to scan. CAST(dt AS string) = '2026-03-02' hands it an expression whose value depends on the data rather than on the partition key, so it can't be evaluated against a partition value without reading files. The filter drops out of the pruning step and every partition is scanned.

The same cast kills row-group skipping inside each file. Parquet min/max stats are stored in the column's own type; a cast expression can't be compared against them unless the engine can invert the cast, which it generally won't. You lose pruning at both levels.

Arithmetic on the two numbers: 18 TB ÷ 48 GB ≈ 384. The scan read roughly 384 partitions' worth of data to return one partition's rows.

PredicateBytes scannedWall time
CAST(dt AS string) = '2026-03-02'18 TB69 min
dt = DATE '2026-03-02'48 GB4 min

What to look for in the plan (illustrative Spark output):

== Physical Plan ==
*(1) ColumnarToRow
+- FileScan parquet t[dt#12, ...]
      PartitionFilters: [isnotnull(dt#12), (dt#12 = 2026-03-02)]
      PushedFilters: []

With the cast, PartitionFilters is empty and the cast appears as a filter above the scan instead. That's the check: partitionFilters non-empty on the native form, empty on the cast form.

The fix, and where it stops working

Filter on the stored type and cast the literal: dt = DATE '2026-03-02'. If the partition column is a string, cast the literal to that string, but it has to match the stored format byte for byte. '2026-3-2' against a zero-padded '2026-03-02' doesn't prune, and worse, it returns zero rows silently rather than erroring. Compare row counts against a known-good query when you make this change.

When you can't avoid casting the column

  • The stored type is wrong and you don't own the writer. A partition column that's a string holding timestamps, or a date written as yyyyMMdd ints, can't be fixed by editing your query. Cast the literal into the stored type if the format is stable; if it isn't, you're reading everything.
  • The literal arrives as a different type from an API. Same answer: convert at the boundary, not in the predicate.
  • The partition key is a derived expression (say dt computed from an event timestamp at write time). Then the query key and the partition key differ and no cast helps; you need a column that matches.

The cost of fixing the layout instead

Rewriting the partition layout is the durable fix and it isn't free. On Hive-style tables it means rewriting the files and re-registering partitions: 18 TB of I/O, plus a window where readers see both layouts. On Iceberg, partition evolution is metadata-only: new writes use the new spec, existing files keep the old one, and the planner prunes per-spec, so old data still needs the old predicate until compaction rewrites it. Delta has no equivalent; changing partition columns means rewriting the table. Budget the compaction and check downstream consumers that read the partition path directly.

Curated: · Written: · Reviewed:

Q&A-84Row groups are 2 MB because streaming flushed often. Predicates still read almost every group. Why?

Core answer. Predicate pushdown skips a row group only when the predicate's value range is disjoint from that group's [min, max] for the column. Streaming appends write rows in arrival order, so the row groups end up ordered by event time, but the column you filter on (customer_id, status, region) is uncorrelated with arrival order. Every row group therefore holds a near-uniform sample of that column, its min/max spans almost the whole domain, and the predicate value falls inside every group's range. Nothing can be pruned, so the scan reads all groups. The 2 MB size is not what defeats pruning; the lack of ordering on the filter column is. Small groups make the symptom worse (more groups to open, more stats to evaluate) but they are not the cause.

Mechanism. Parquet stores per-column-chunk statistics: min, max, null count. The reader evaluates the predicate against each chunk's min/max and skips the chunk only on a definite miss. With 1,000,000 distinct customer_id values and ~200,000 rows per 2 MB group, a group's expected min/max is essentially [1, 1,000,000]: the same for every group. customer_id = 42 is in range for all of them.

Trace, hypothetical 1B-row orders table, snappy, ~10 bytes/row:

Row groupcustomer_id minmax42 in range?read?
13998,412yesyes
21999,873yesyes
.........yesyes

After SORT BY customer_id (or clustering) into 128 MB groups:

Row groupminmax42 in range?read?
11200,000yesyes
2200,001400,000noskip
.........noskip

One group read instead of all of them.

Trade-offs. Sorting costs a rewrite and only pays off for the columns you sort on; a second filter column stays unpruned unless you Z-order or sort by a composite key. Larger row groups reduce open/footer overhead but widen each group's range, so they trade pruning for fewer opens: they do not fix an unsorted column. If the predicate is on event_time and the writer flushes in time order, stats prune well even at 2 MB; the pathology is specifically filters on non-time columns.

Failure modes. Stats absent or disabled → no pruning at all. String min/max truncated to a bounded length → a long common prefix makes the range look wider than it is, producing false positives. Nulls and NaN in stats → groups not skipped. Detect with EXPLAIN or metrics for row groups read, not by guessing.

What I would do. Compact to ~128 MB row groups and sort on the filter key when the workload is known; measure groups opened and footer time before and after. Row-group size is a read SLO, not a storage knob.

Curated: · Written: · Reviewed:

Q&A-26The table is partitioned by dt. The query filters DATE(event_ts)=dt. Why did Spark still scan 400 days?

What I'd say first

Pruning never happened, because the predicate isn't on the partition column. DATE(event_ts) = dt is a filter on event_ts; dt is just what you're comparing it to. Spark's partition pruning runs in the optimizer and can only drop a partition when it can evaluate the predicate against that partition's dt value at planning time. That needs a constant, or something foldable to a constant, on one side of the comparison. DATE(event_ts) is evaluated per row, so there's nothing to fold, and every partition directory gets listed and read. If the right-hand side really is the dt column rather than a literal, it's worse: there's no constant anywhere in the comparison.

Confirm before changing anything: EXPLAIN EXTENDED the query and look at the scan node. partitionFilters should list what Spark pushed to the partition level. If it's empty and your predicate shows up under dataFilters or pushedFilters, pruning is off. The Spark UI's SQL tab shows the same thing as files and bytes read.

The fix, and what it costs

Filter the partition column directly:

-- no pruning: predicate on event_ts
WHERE DATE(event_ts) = '2026-03-02'

-- prunes to one partition
WHERE dt = '2026-03-02'

That only works if dt is populated correctly at write time, which is the trade-off: you're denormalizing a derived value. If event_ts is late-arriving, corrected by a backfill, or interpreted in a different timezone than the writer used, dt drifts from DATE(event_ts) and the fast query returns the wrong rows. So the write path has to derive dt from event_ts in one place, and backfills have to rewrite dt too.

When denormalizing dt is the wrong call

  • If dt means ingestion date and event_ts means business time, they're different by design. Don't force them equal; keep both and filter on whichever the query actually means.
  • If many consumers query by different time semantics, one dt can't serve them. A view per semantic, or a materialized view keyed on DATE(event_ts), is cleaner than making every writer agree.
  • If the table is written once and never corrected, and event_ts is always in the same timezone, drift risk is low and denormalizing is fine.

Alternatives that don't touch the write path

  • Range on event_ts: event_ts >= '2026-03-02' AND event_ts < '2026-03-03'. Sargable, so Parquet min/max stats can skip row groups, but only if the data is clustered by event_ts within files. It still lists every partition, so it's a partial win.
  • Sort or bucket by event_ts: helps data skipping, not partition pruning. Bucketing prunes only when the predicate is on the bucket column with a constant.
  • Dynamic partition pruning: fires on a join against a partitioned table, not on a plain filter. If your query is a join, check spark.sql.optimizer.dynamicPartitionPruning.enabled and that the join key is the partition column.
  • Materialized view or generated column: Delta supports generated columns; a view projecting dt from event_ts lets the optimizer prune without touching writers. Costs storage or refresh.

Failure modes

  • Silent full scan: results are correct, cost isn't. Detected by EXPLAIN and files-read in the UI, not by tests.
  • dt drift: a backfill writes rows under the ingestion day, so dt = '2026-03-02' misses them. Detected by reconciling counts against DATE(event_ts).
  • Timezone off-by-one: DATE(event_ts) uses the session timezone, dt was written in UTC. Rows at day boundaries land in the wrong partition.

Worked example (hypothetical: 412 daily partitions, ~44 GB each)

FilterPartitionsBytesWall
DATE(event_ts) = '2026-03-02'41218 TB74 min
dt = '2026-03-02'144 GB3 min

Gate the merge on EXPLAIN showing one partition for the daily job.

Curated: · Written: · Reviewed:

Q&A-25Spark lists parquet in a Delta table path and unions them. Why is that not reading Delta?

The short answer

Delta is the log, not the folder. spark.read.parquet(path) never opens _delta_log/: Spark's parquet reader skips files and directories whose names start with _ or ., so it reads every *.parquet file physically present under the path. The log is what says which of those files are live at a given version. Globbing the directory reads the union of everything on disk, which is a different set.

Assumptions I'd state up front: the table has had at least one DELETE/UPDATE/MERGE, or a failed write, and VACUUM hasn't run past its retention window (default 7 days). If the table is append-only and never had a failed write, glob and Delta scan return the same rows: the difference is latent, not absent.

What the log actually does

Each commit appends a JSON file to _delta_log/ carrying actions: add (file is live), remove (file is no longer live), plus metadata and protocol. A read at version N replays those actions to get the live file set, then prunes by partition and by per-file stats recorded in the log. Globbing does none of that: no remove handling, no snapshot isolation (files can be added or removed mid-listing), no time travel, no column mapping, no deletion vectors.

A trace

Table with three files, 100 rows each, 300 rows total. A DELETE removes 40 rows from file 2. Delta rewrites file 2 as file 2' with 60 rows and commits add file2' + remove file2. On disk now: file1 (100), file2 (100, remove-pending), file2' (60), file3 (100).

  • Delta scan at latest version: file1 + file2' + file3 = 260 rows. Correct.
  • Glob: file1 + file2 + file2' + file3 = 360 rows.

The 100 extra rows are 60 exact duplicates of surviving rows plus 40 resurrected deleted rows. SUM is inflated by the sum of all 100 rows in file2. AVG moves only by the 40 resurrected rows: the 60 duplicates are exact copies and cancel out. That's the signature: COUNT and SUM wrong, AVG nearly right, which is why this survives casual checks.

With deletion vectors enabled, it's worse: the DELETE writes a DV file and leaves file2 untouched, so glob resurrects the 40 rows with no duplicate at all.

When reading raw parquet is legitimate

  • A reader with no Delta connector (older Trino/Presto, some Hive setups). The supported pattern is to read the parquet directly only when the table is append-only, or after a VACUUM with retention 0 so no remove-pending files remain.
  • A non-Delta directory: staging parquet that hasn't been loaded into a table yet, or data deliberately written outside the table.
  • Forensics: you want to see what's physically on disk, e.g. to confirm a remove-pending file still exists.

Deciding criteria: has the table ever had a remove committed that isn't vacuumed? Does the reader support Delta? Do you need snapshot isolation or time travel? If the first is no and the second is yes, use the Delta scan.

Detection

Compare COUNT(*) from the glob against a Delta scan at a pinned version, or against DESCRIBE DETAIL's file count. A mismatch is the signal. For intermittent cases, pin the version in the Delta read and diff row hashes.

Curated: · Written: · Reviewed:

Q&A-23Spark wrote 400 files then died. The next list_dir shows the files. Are they in the table?

No: those 400 files are not in the table. Iceberg defines a table by the metadata pointer the catalog holds, not by what sits in the object store. Until a commit writes a new metadata.json and the catalog swaps to it, the files are orphans: visible to list_dir, invisible to SELECT.

Assumptions that decide this: Spark wrote data files directly into the table's data directory (Iceberg's default. No staging move), the catalog is a Hive metastore or a REST/JDBC catalog with an atomic pointer swap, and the driver died before the commit call returned. If any of those is false the answer changes, so say them out loud before answering.

Mechanism. Spark tasks write Parquet files under s3://bucket/db/table/data/. The driver then builds an append snapshot and commits in two steps: write metadata/vN.metadata.json, then atomically update the catalog pointer (HMS lock-and-rename, or a conditional update on a REST catalog). Readers resolve the current snapshot from the catalog and read only the files its manifests list. The gap between "files on disk" and "files in the table" is exactly that commit, and nothing else.

Trace of this failure (Iceberg 1.5.x, Spark 3.5):

  1. t0: job starts, 400 tasks.
  2. t1: tasks write 400 Parquet files into the data prefix. S3 ListObjectsV2 has been strongly consistent since December 2020, so list_dir shows all 400 immediately.
  3. t2: driver dies before commit. No new metadata.json, catalog pointer unchanged.
  4. t3: SELECT count(*) resolves the previous snapshot and returns yesterday's count.
  5. t4: a later remove_orphan_files deletes the 400 files.

Check it yourself:

SELECT count(*) FROM db.tbl;                       -- yesterday's count, not +400 files
SELECT snapshot_id, committed_at, operation
FROM db.tbl.snapshots ORDER BY committed_at DESC LIMIT 3;
SELECT file_path FROM db.tbl.files;                -- only referenced files

Diff db.tbl.files against aws s3 ls s3://bucket/db/table/data/. The difference is the orphan set: that diff is the evidence, not the file count.

Trade-off: when to run orphan cleanup. remove_orphan_files defaults older_than to three days ago. The deciding criterion is the longest possible lifetime of an in-flight write: job duration + retry window + clock skew between the writer hosts and the host running the procedure. If a streaming writer can hold files uncommitted longer than older_than, cleanup deletes files that job is about to commit. Don't run it at all when you can't bound that window, when writer clocks are skewed, or when another engine writes into the same prefix under a different staging convention.

Failure modes. Driver death before commit produces orphans: detect by the files-vs-listing diff. A commit that succeeded but whose ack was lost, then retried, appends the same files a second time and duplicates rows: detect by comparing added-data-files across consecutive snapshots. Cleanup that fires too early deletes files a live job will commit, and that job's commit then fails on a missing file.

The snapshot is the table. The prefix is a junk drawer, and it's the drawer you must not empty on a timer you can't defend.

Curated: · Written: · Reviewed:

Q&A-75The job writes to a path and then renames _temporary to the partition. A reader globbed mid-rename. What did they see?

Direct answer

They saw a partial partition. A glob is a LIST call, not a snapshot: it returns whatever objects exist at the instant it runs. The commit makes each file visible one at a time (a rename on HDFS, a copy-then-delete on S3) so a reader that lists mid-commit gets a prefix of the new files, and if the job is overwriting in place, some of the old files are still sitting there too. There is no point in the commit where the listing is guaranteed to be either all-old or all-new.

So the honest answer to "what did they see" is: an arbitrary subset, and the query that reads it usually succeeds rather than failing loudly.

Mechanism

Two things make this non-atomic. First, the commit itself: the job writes task output under _temporary, and the commit step moves each file into the final partition path. On HDFS that's a metadata rename per file; on S3 there is no rename, so it's a copy plus a delete per object. Either way it's a loop over files, not one operation.

Second, the reader: glob(".../dt=2024-01-01/*.parquet") expands to a listing, and that listing is not isolated from concurrent writes. Each file enters the result as soon as its move completes.

Hadoop's FileOutputCommitter has two algorithms and they differ here. Algorithm 1 keeps everything under _temporary until the job commit, then moves it all at once: the window is short but still non-atomic. Algorithm 2 moves each task's files into the final directory as that task commits, so partial data is visible for the whole duration of the job, not just the final rename. If you're seeing partial reads, check which algorithm the writer uses.

Failure modes

  • Silent wrong answer. The reader lists 12 of 52 files, reads them, and returns a sum that's too low. No error. This is the dangerous one.
  • Schema merge failure. If old and new files have different schemas, the reader either fails on the Parquet footer mismatch or, worse, unions two schemas and produces garbage.
  • FileNotFound. The reader lists, then reads; a file is deleted between the two calls. This one at least surfaces.

Worked example (hypothetical)

A partition holds 40 files from yesterday's run. Today's job overwrites it with 52. A dashboard globs at the moment 12 of the 52 have landed and the old files haven't been deleted yet. It reads 52 files (40 old, 12 new) and reports a total that is neither yesterday's nor today's. If the metric is a sum of revenue, the dashboard shows a value plausible enough that nobody notices until the next run corrects it.

What to do instead

Don't glob a partition that's being committed. Options, in order of preference:

  1. Snapshot-isolation table format (Iceberg, Delta, Hudi). The commit is a single atomic swap of a metadata pointer; readers see the previous snapshot or the next one, never a mix. This is the real fix.
  2. Write to a new path and swap a pointer or manifest atomically. Same idea, hand-rolled.
  3. If you must glob, wait for _SUCCESS and only then list. This works when the writer writes all data files before _SUCCESS and doesn't delete old files in place, but it's a convention, not a guarantee, and it breaks under algorithm 2 and under in-place overwrite.

Detection

Read during a commit and assert the row count and a checksum match a known-good snapshot. If the reader can see a partial partition, the test fails. Also alert on readers whose file count differs from the writer's expected count.

Curated: · Written: · Reviewed:

Q&A-97A stage retries after a fetch failure. The first attempt already wrote part files. The sink is a plain path. What is in the partition?

Both attempts' part files are in the partition, so every row appears twice. The first attempt's files were never removed, the retry wrote a second set under new attempt IDs, and a plain path sink has no commit step that discards the first set.

Why it happens. Spark keys each task attempt's output by a unique attempt ID. What reaches the final directory is decided by the FileOutputCommitter, and its algorithm version changes the answer:

  • Algorithm 2 (mapreduce.fileoutputcommitter.algorithm.version=2, Hadoop 3's default): task commit moves the attempt's files straight into the final output directory. A task that committed before the fetch failure left its files there; the resubmitted stage writes new files under new attempt IDs. Both sets are visible: duplicates.
  • Algorithm 1 (Spark's default): task output stays under {output}/_temporary/{appAttemptId}/... until job commit. A failed or retried attempt's files are never promoted, so the final path holds only the successful attempt's files: no duplicates.

So "both sets, doubled rows" is the answer when the committer is v2 or absent; with Spark's default v1 committer the _temporary tree absorbs the first attempt and the final path stays clean. The _SUCCESS marker is written at job commit and says nothing about which attempt's files are present.

Spark's OutputCommitCoordinator grants commit to one task attempt per (stage, partition) within a single job attempt, but it does not span a stage resubmission, so it does not save you here.

Worked trace: 2-partition stage, v2 committer:

# attempt 1: both tasks commit
out/part-00000-<uuid-a>.parquet
out/part-00001-<uuid-b>.parquet

# fetch failure -> map stage resubmitted, new attempt IDs
out/part-00000-<uuid-c>.parquet
out/part-00001-<uuid-d>.parquet

# final partition: 4 files
# COUNT = 2x, SUM = 2x, AVG unchanged

Detection. Count part files per partition and compare to the task count; more files than tasks is the tell. COUNT(*) and SUM doubling while AVG holds is the signature.

Fix. Write through a table format (Iceberg, Delta, Hudi) whose commit replaces the snapshot; or pin mapreduce.fileoutputcommitter.algorithm.version=1 so job commit promotes only successful attempts; or write each run to a fresh path and swap.

Curated: · Written: · Reviewed:

Q&A-76A 40-minute dashboard query started on snapshot 88. A compaction committed 120 times during it. Which files should the query use?

The files listed in snapshot 88's manifest list, and nothing the 120 compaction commits wrote.

A scan resolves its snapshot once, at planning. Snapshot 88's manifest list (metadata/snap-88-*.avro) is immutable: it names the exact set of manifest files, which in turn name the data files that existed when snapshot 88 was committed. The 120 compaction commits each wrote a new snapshot (89 through 208), each with its own manifest list pointing at the rewritten files. The query never reads those, because it never re-resolves the snapshot id.

That is the whole mechanism. Compaction does not mutate files in place; it writes new data files and commits a new snapshot that references them. The old files stay on disk as long as some live snapshot references them, so snapshot 88's files remain readable for the duration of the query.

Pin it explicitly rather than relying on the engine's default:

-- Spark 3.5 + Iceberg 1.5
SELECT ... FROM prod.db.events VERSION AS OF 88;
-- or
spark.read.option("snapshot-id", 88).format("iceberg").load("prod.db.events")
-- Trino
SELECT ... FROM iceberg.prod.db.events FOR VERSION AS OF 88;

Without the explicit id, Spark plans the scan against the current snapshot at the moment the query starts: fine for a query that starts and finishes in seconds, wrong for one that runs 40 minutes if any stage re-plans.

When reading latest is the right call. A dashboard tile that must show the last five minutes cannot pin a 40-minute-old snapshot; it should be a short query against the current snapshot, or better, against a pre-aggregated table. Pinning trades freshness for consistency: the result is a single point in time, which is what you want for a SUM over 40 minutes of scanning, and wrong if the number is supposed to reflect "now."

The failure mode that breaks the pin: snapshot expiry. expire_snapshots deletes snapshots older than history.expire.max-snapshot-age-ms (default 432000000 ms = 5 days) and then removes data files no retained snapshot references. A 40-minute query is safe under the default. A query that outlives retention fails mid-scan with a snapshot-not-found error, not a wrong answer. If you run long scans, raise the retention or run expire_snapshots with an explicit older-than timestamp.

Second failure mode: re-planning. If the query is a Spark structured-streaming read or a job that opens a new scan per micro-batch, each scan pins its own snapshot unless you set snapshot-id explicitly. That is how you get a torn read across 120 compactions.

So: pin 88, and the 120 commits are invisible. Verify by running the query with snapshot-id=88 and again with snapshot-id=208 and confirming the row counts differ by exactly the compaction's effect.

Curated: · Written: · Reviewed:

Q&A-77A bad load committed snapshot 200. You roll back serving to 199. What must the next incremental not do?

The next incremental must not resume from the cursor that snapshot 200 committed with. That cursor already accounts for events 199 does not contain. Starting there skips them, and nothing downstream replays them: the hole is permanent until someone notices and backfills by hand.

Snapshot and cursor are one atomic pair. A snapshot is not just data files; the commit metadata records the source offset the snapshot was built from. Rolling serving back to 199 means restoring both the data and the offset property. Restore the data files alone and the reader still resumes at 200's offset.

Trace

Source log offsets 1..N. Snapshot 199 committed at source offset 10,000. Snapshot 200 committed at source offset 12,400. Serving is rolled back to 199.

  • Next incremental starts at 12,401 → events 10,001–12,400 (2,400 events) are never applied. Hole.
  • Next incremental starts at 10,001 → those 2,400 events replay onto 199. Correct.
Next incremental starts atEvents 10,001–12,400Result
12,401 (200's cursor)none appliedpermanent hole
10,001 (199's cursor)replayed199 + replay = correct state

What it must not do

  • Must not treat 200's offsets as committed. The commit that advanced the cursor is the same commit being rolled back. Its offset advance goes with it.
  • Must not rely on idempotent upserts to fix this. Idempotency protects against double-apply. The failure here is a gap, not a duplicate: upserts on keys that never arrive do nothing.
  • Must not roll back only the data files. In table formats that store the source offset in snapshot/commit metadata (Iceberg snapshot summaries, Delta commitInfo, Hudi checkpoint), restoring data without restoring that property leaves the reader at 200.
  • Must not leave derived state at 200. Materialized views, search indexes and caches built from 200 are now inconsistent with 199, and the replay will write into them a second time unless they are rebuilt from the same rewound cursor.

Detection

Compare the max source offset recorded in the current snapshot metadata against the source's committed offset. A gap means the cursor was not rewound. Per-partition row counts or checksums against the source catch the same thing after the fact.

Gate the stream: require the cursor to match 199's offsets before re-enabling the incremental. Table rollback without cursor rollback is a hole.

Curated: · Written: · Reviewed:

Q&A-86You add hour to an Iceberg spec. Old files stay day-partitioned. A job overwrites hour=0 only. What did it replace?

What it replaced: the whole day. The hour=0 overwrite deleted every file in that day's partition, all 24 hours, not just hour 0. Hours 1–23 of that day are gone; the job wrote back only hour 0.

Assume Iceberg spec v2, Spark 3.5, and an overwrite-by-filter (dynamic overwrite) job.

Why. Iceberg tags each data file with the spec ID it was written under, and an overwrite-by-filter is planned by projecting the row predicate onto each spec (inclusive projection). The predicate that selects hour 0 of day D references the timestamp column. Against the new spec it projects to day = D AND hour = 0. Against the old day spec there is no hour field, so the projection is just day = D: the hour constraint has nowhere to land and is dropped. Every file in that day partition matches, so all of them are deleted.

SpecPartition fieldsProjection of the hour-0 predicateFiles deleted
olddayday = Dthe whole day file (24 h)
newday, hourday = D AND hour = 0the hour-0 file only

Worked trace:

  1. Table starts day-partitioned. Files: day=2024-01-01 (one file, 24 hours), day=2024-01-02.
  2. ALTER TABLE ... ADD PARTITION FIELD hour(ts) → new spec (day, hour). The old file keeps spec 0.
  3. Job overwrites hour 0 of 2024-01-01. Planning projects the predicate: spec 0 → day = 2024-01-01 (matches the whole-day file); spec 1 → day = 2024-01-01 AND hour = 0.
  4. Both matched files are deleted; the job writes one new hour-0 file.
  5. Result: 2024-01-01 holds only hour 0. Hours 1–23 are lost.

When it doesn't happen. A MERGE/UPDATE/DELETE on a key does not delete by partition: copy-on-write rewrites the matched file with the non-matching rows retained, so hours 1–23 survive. The whole-day loss is specific to overwrite-by-filter (dynamic overwrite) semantics. If you must overwrite one hour, either write with a MERGE, or first rewrite the old day files into the current spec (rewrite_data_files with output-spec-id) so the hour predicate can match them at hour granularity.

Detecting it. Count rows per hour for that day before and after, or run a point query for hour 5. In the snapshot metadata, compare the files list of the new snapshot against the previous one: the old day file's path disappears.

Curated: · Written: · Reviewed:

Q&A-87You Z-order customer_id weekly. Queries still open most files. What did the weekly job miss?

Short answer. The weekly job only rewrites the files it selects. Everything written after it (streaming ingest, backfills, late partitions) is unclustered, and if that's most of the bytes your queries touch, the Z-order is decoration. Two other misses are just as common: the query doesn't filter on customer_id in a way the engine can prune with, and the table has no per-file min/max stats for that column.

Mechanism. Z-order sorts rows so each file covers a narrow range of customer_id. File pruning then skips any file whose [min, max] for that column doesn't contain the value. It is a within-file layout: it never reduces the number of files, and it does nothing for a query that doesn't predicate on the clustering column.

What the weekly job misses

  • Files written after it. Streaming and CDC writes land as new files with arbitrary customer_id ranges. A weekly cadence leaves up to seven days unclustered, which is exactly the hot data point lookups hit.
  • Partitions outside its window. Most jobs are written as OPTIMIZE tbl ZORDER BY (customer_id) WHERE dt >= current_date - 7. Anything older keeps whatever layout it had.
  • Files skipped by a size heuristic. If the job only rewrites files below a threshold, the large unclustered files survive untouched.
  • Columns without stats. Delta collects stats on the first 32 columns by default (delta.dataSkippingNumIndexedCols); if customer_id sits past that, there are no min/max bounds and nothing to prune with. Iceberg's default write.metadata.metrics.default is truncate(16), which bounds string IDs by their first 16 characters: fine for short IDs, weaker for long ones.
  • Query shape. WHERE customer_id = ? prunes. WHERE lower(customer_id) = ? or a predicate on a hash of it does not. And with no partition filter, you open one file per partition per day regardless of clustering.

Worked example (illustrative figures, not a benchmark). Table partitioned by dt, one day of streaming ingest, weekly job last ran six days ago. Query: WHERE customer_id = 'C-4471' AND dt = '2024-06-14'.

Layout of today's partitionFiles openedWall time
as written by ingest8,20016 min
after OPTIMIZE ... ZORDER BY (customer_id) on today612 s

The weekly job had clustered the same table six days earlier. It changed nothing for this query, because none of the files it rewrote were in today's partition.

How to confirm it

  • DESCRIBE DETAIL tbl gives numFiles and sizeInBytes for the current snapshot; compare today's partition against the ones the job rewrote.
  • EXPLAIN the point query and read the file scan: the number of files listed is the number opened.
  • On Iceberg, SELECT * FROM db.tbl.files exposes per-file lower_bounds / upper_bounds for customer_id; if those are null or span the whole domain, pruning can't happen.
  • Set the budget explicitly: files opened for a one-customer query on the latest partition. If it's in the thousands, the job missed.

When Z-order isn't the fix. If the query has no customer_id predicate, or scans every partition, clustering buys nothing. If files are tiny, each file's range is already narrow but you still open thousands: that's a compaction problem, not a clustering one; compact toward a target file size (Iceberg's write.target-file-size-bytes defaults to 512 MB). And for high-concurrency single-key lookups, a sorted layout or a key-value store beats Z-order outright.

Cluster the files the queries actually read, on the cadence they're written.

Curated: · Written: · Reviewed:

Q&A-24Is overwriting an Iceberg day partition inherently unsafe?

No. Overwrite is the correct operation for a full reload; it is unsafe only when the input is not the full day. The predicate decides the blast radius, not the operation.

Mechanism. An Iceberg overwrite is one atomic commit: delete every data file matching the predicate, add the new files, publish a new snapshot. Readers see the old snapshot or the new one, never a half-written day, so the concurrency story is fine. The risk is semantic. If the predicate is the day and the input is one hour, the other 23 hours are deleted and nothing in the commit complains.

Which predicate you get. Spark's INSERT OVERWRITE with spark.sql.sources.partitionOverwriteMode=dynamic derives the predicate from the partitions present in the incoming rows, so a one-hour input touches one hour. With static mode, or an explicit PARTITION (...) clause, the predicate is whatever you wrote. Same SQL, different blast radius. Check which one your writer emits before you trust it.

Trace. events is partitioned by days(event_ts). Day 2024-03-14 holds 24 hourly files, 41,000,000 rows. A backfill reads hour 14 only, 1,700,000 rows, and runs INSERT OVERWRITE events PARTITION (event_day='2024-03-14') SELECT ....

Writer modePredicateFiles deletedRows left for 2024-03-14
Static, explicit PARTITION clausewhole day241,700,000
Dynamicpartitions in input (hour 14)141,000,000 (assuming the hour-14 replacement carries the same 1.7M rows)

Static loses 39,300,000 rows in one commit. The snapshot summary is the tell: deleted-records 41,000,000 against added-records 1,700,000. A 24:1 delete-to-add ratio on a day partition means the predicate was wider than the input.

Recovery is bounded. SELECT * FROM events VERSION AS OF <snapshot_id> reads the pre-overwrite snapshot, but only while it exists. Defaults are history.expire.max-snapshot-age-ms = 5 days and history.expire.min-snapshots-to-keep = 1; once expired, the rows are gone. A complete-day overwrite the next night is the repair, and it only works if the source still has the day.

Guard. Gate the overwrite on completeness: compare the source's row count for the day against the input's row count and refuse to commit on a mismatch. For partial corrections use MERGE INTO instead:

MERGE INTO events t
USING staging_hour14 s
ON t.event_id = s.event_id AND t.event_day = '2024-03-14'
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

MERGE costs a join and writes row-level delete files; a full-day overwrite is cheaper and simpler. Use overwrite for reloads, MERGE for corrections.

Failure modes to name. Static overwrite fed a partial extract, as above. Dynamic overwrite where the partition column is null or mis-derived, so the derived predicate covers the wrong partitions. An overwrite that also changes schema, breaking downstream readers without losing rows. And noticing the loss after the snapshot expired.

Test. In a scratch table, overwrite a day from a one-hour input and require the completeness gate to fail; overwrite from a complete extract and require the day's count to match the source.

Curated: · Written: · Reviewed:

Q&A-56Spark dynamic partition overwrite is on. The input only has dt=2026-03-02 and dt=2026-03-03. What happens to 2026-03-01?

2026-03-01 survives untouched. Dynamic partition overwrite replaces only the partitions that appear in the incoming data. dt=2026-03-02 and dt=2026-03-03 get new files; dt=2026-03-01 keeps whatever it already had.

The switch is spark.sql.sources.partitionOverwriteMode, added in Spark 2.3 (SPARK-20236). Its default is static. Set it per session or per job:

# Spark 3.5
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(df.select("order_id", "amount", "dt")   # partition column last
   .write
   .mode("overwrite")
   .insertInto("sales.orders"))

insertInto matches columns by position, not by name, and requires the partition column to be the last one. If dt is missing from the DataFrame the write fails with an AnalysisException rather than silently doing nothing.

What each mode does to your three days:

Writedt=2026-03-01dt=2026-03-02, 03
static (default), INSERT OVERWRITE TABLE tdeletedreplaced
static, INSERT OVERWRITE TABLE t PARTITION(dt='2026-03-02')untouchedonly 02 replaced
dynamic, input has 02 and 03untouchedreplaced

The dangerous case is the mirror image of the one you asked about. With the default static mode and no PARTITION clause, Spark drops every partition in the table and writes back only the two days in the input. 2026-03-01 is gone, along with anything else that was there. That is the version of this that shows up as a finance number that will not reconcile.

Mechanism: in dynamic mode Spark does not delete partitions up front. It writes each incoming partition to a staging directory under the table location, then moves it into place, replacing the existing partition directory. Partitions with no incoming rows are never touched.

Failure modes worth knowing:

  • Empty input. Dynamic overwrite with zero rows writes nothing and deletes nothing. A job meant to clear March silently no-ops. Detect it by asserting the target's row count changed, or by checking max(dt) after the write.
  • Not atomic across partitions. Each partition is replaced independently. A failure halfway through leaves 02 new and 03 old. Detect by comparing per-partition max(ingest_ts) or file modification times.
  • Hive serde tables ignore the config. The setting governs partitioned data source tables written through INSERT OVERWRITE / insertInto / saveAsTable. Hive-managed tables fall back to Hive's own overwrite semantics.
  • df.write.mode("overwrite").partitionBy("dt").parquet(path) is a separate code path from insertInto. Whether it honors the config has varied across Spark versions; verify on your build before relying on it.

If the intent really is "March is exactly these two days," say so explicitly: ALTER TABLE sales.orders DROP PARTITION (dt='2026-03-01') for a Hive-style table, or DELETE FROM sales.orders WHERE dt='2026-03-01' on Delta or Iceberg. Absence from the input is not a delete instruction.

Curated: · Written: · Reviewed:

Q&A-16Reloading 2 March should be safe. When is INSERT OVERWRITE the right idempotent operator?

The 60-second answer. INSERT OVERWRITE is the right idempotent operator when the input is the complete row set for the partition it replaces and the engine commits that replacement atomically. Then the partition's contents are a function of the input alone: re-running the same job with the same complete extract produces the same partition, and rows that disappeared upstream are deleted for free. Reloading 2 March is safe exactly when the extract is the full day, the day is closed (no late events expected), and this job is the only writer to dt='2026-03-02'. If any of those is false, overwrite is the wrong operator and MERGE is the right one.

The trap is the overwrite mode, not the operator. In Spark, spark.sql.sources.partitionOverwriteMode decides scope. STATIC (the default) replaces only the partition named in the clause; DYNAMIC replaces every partition present in the input. Iceberg and Delta follow the same split.

-- Spark 3.5 SQL, Iceberg or Delta table
SET spark.sql.sources.partitionOverwriteMode = static;
INSERT OVERWRITE TABLE prod.fact_orders PARTITION (dt = '2026-03-02')
SELECT * FROM staging_full_day WHERE dt = '2026-03-02';

Drop the PARTITION clause under DYNAMIC and a one-hour retry file becomes a whole-day replacement. That is the failure: a day partition of 19.4M rows, 24 hourly files of ~808k each, overwritten from a single hour. The commit deletes 19.4M records and adds 808k; the partition ends 18.6M rows short and every downstream daily metric for 2 March is wrong until someone notices.

Guard the input, not the write. Fail closed before the overwrite runs:

SELECT assert_true(
  (SELECT count(*) FROM staging_full_day WHERE dt = '2026-03-02')
  >= 0.98 * (SELECT row_count FROM source_audit WHERE dt = '2026-03-02')
) AS extract_is_complete;

assert_true raises when the extract is short, so the job stops instead of publishing a partial day. The tolerance is documented and per-source; 2% is a starting point, not a law.

Detect it after the fact from the snapshot summary. Iceberg records added-records and deleted-records per commit:

SELECT committed_at, summary['added-records'] AS added, summary['deleted-records'] AS deleted
FROM prod.fact_orders.snapshots ORDER BY committed_at DESC LIMIT 1;

A legitimate full-day reload shows added ≈ deleted ≈ 19.4M. A one-hour overwrite shows added ≈ 808k against deleted ≈ 19.4M: that asymmetry is the alarm, and it is visible in the same commit that broke the partition.

When overwrite is wrong.

SituationUse insteadWhy
Partial hour, micro-batch, filtered sampleMERGE on the business keyoverwrite would delete the rest of the day
Late rows arriving after the day closedMERGE, or reopen the dayoverwrite from the late batch drops everything else
Upstream deletes, full partition availableINSERT OVERWRITEdeletes come free; MERGE needs an explicit delete clause
Upstream deletes, only a partial inputneither: get the full partition, or issue a scoped DELETEWHEN NOT MATCHED BY SOURCE THEN DELETE on a partial source deletes everything outside it
Plain Hive/Parquet directory, concurrent readersIceberg/Delta, or a staging swapdirectory overwrite deletes then writes; readers see a half-empty partition

Cost, so you pick deliberately. Overwrite rewrites the whole partition: 19.4M rows for a one-row fix. MERGE rewrites only the files holding matched rows, but Iceberg and Delta rewrite whole files, so a change spread over 200 files costs 200 files. Rough rule: if more than a few percent of the partition changes, overwrite is both cheaper and simpler; below that, MERGE wins.

Rule of thumb: overwrite when the input is the whole partition and the commit is atomic; MERGE when it isn't.

Curated: · Written: · Reviewed:

Q&A-55You have 12,000 changed keys in a 40 million row day. Which operator, and why is overwrite still not free?

MERGE, scoped to the day partition, but the reason is not the 0.03% row ratio, and that ratio is exactly what makes people pick the wrong operator.

Mechanism: MERGE joins the 12k-row delta against the target partition and rewrites only the files that contain a matched key (copy-on-write). Its cost scales with files touched. INSERT OVERWRITE reads and rewrites every file in the partition and commits a new snapshot; its cost scales with partition size. So the deciding number is not 12,000 / 40,000,000: it is how many files those 12,000 keys land in.

That is where the naive answer breaks. If the day is 200 files of ~10 MB and the 12k keys are spread uniformly, every file contains at least one key: P(file untouched) = (1 − 1/200)^12000 ≈ e^−60 ≈ 0. MERGE rewrites all 200 files, and you have paid overwrite's I/O plus a join. MERGE only wins when the delta is clustered: keys sorted, table clustered by key, or changes confined to a few hours of a time-clustered table.

Hypothetical figures for a 2 GB day partition:

OperatorFiles rewrittenBytes read + writtenNotes
INSERT OVERWRITE day200~4 GB (2 GB read, 2 GB write)new snapshot, whole partition invalidated
MERGE, clustered delta (4 files)4~80 MB12k-row build side, negligible
MERGE, scattered delta200~4 GBsame I/O as overwrite, plus the join

Why overwrite is still not free even when you choose it:

  • It rewrites 2 GB to change 12,000 rows. That is the floor, not the ceiling.
  • Snapshot retention: Iceberg/Delta keep the replaced files until they expire, so the partition's storage roughly doubles for the retention window and manifest lists grow with every commit.
  • Downstream: anything keyed on file mtime or partition version (incremental consumers, caches, materialized views) reprocesses the whole day.
  • Concurrency: overwrite takes a partition-scoped conflict; a concurrent writer to the same day serializes or fails.
  • Small files: the output file count is set by the writer's shuffle parallelism, not by the data. Get it wrong and the next read pays for it.

When overwrite is the right call: the delta is a large fraction of the partition, the source is a complete recomputation of the day, the table has no stable key, or you need to delete rows absent from the source (MERGE WHEN NOT MATCHED BY SOURCE, or overwrite gets that for free).

Failure modes worth checking before you commit to either:

  • Duplicate keys in the delta: MERGE double-applies or errors depending on engine. Dedupe the source.
  • MERGE without the partition predicate in ON: the plan scans the whole table. Confirm the scan lists only the day's files.
  • Overwrite fed a partial source: silently deletes the other 39,988,000 rows. Row count before and after is the check.
  • Post-MERGE row count should equal target + inserts − deletes. Anything else means the join fanned out.
-- Spark 3.5 + Iceberg; source deduped to one row per key
MERGE INTO events e
USING (
  SELECT key, payload FROM (
    SELECT key, payload,
           row_number() OVER (PARTITION BY key ORDER BY updated_at DESC) AS rn
    FROM staging WHERE day = DATE '2024-05-01'
  ) WHERE rn = 1
) s
ON e.key = s.key AND e.day = DATE '2024-05-01'
WHEN MATCHED THEN UPDATE SET e.payload = s.payload
WHEN NOT MATCHED THEN INSERT (key, day, payload)
  VALUES (s.key, DATE '2024-05-01', s.payload);

Settle it on a clone: run both, compare files rewritten and bytes scanned from the query plan, not slot-hours from a green run.

Curated: · Written: · Reviewed:

Q&A-27A streaming job writes every 30 seconds into a daily partition. What happens to the next day's batch?

Assumptions first. The stream partitions by event time, the record's own date, and appends. A daily batch reads the previous day's partition and writes a derived table. Under those assumptions the answer is: the next day's batch reads a partition that is still open. The stream does not stop at midnight. A record whose event time is day N but which arrives after 00:00 keeps landing in day N's partition until the watermark passes it. So the batch's output is a function of when it ran, not of the day's data.

What actually happens depends on who owns the partition.

  1. Batch reads, writes elsewhere. No write conflict. The batch is simply stale: rows that arrived after its snapshot are missing from the derived table until the next run.
  2. Batch overwrites the same partition (Hive INSERT OVERWRITE, Iceberg/Delta replaceWhere). The batch replaces the partition contents. Every late event the stream appended between the batch's read and its commit is gone. The stream then appends more, so the partition ends up a mix of batch output and post-batch stream rows, with nothing marking which is which.
  3. Stream owns the partition, batch only reads. Clean, but the batch can never correct the stream's rows.

The midnight boundary itself. If the partitioner uses processing time rather than event time, a record with event time 23:59:58 processed at 00:00:01 goes into the new day's partition. Day N is short by one row; day N+1 has a row it should not. Event-time partitioning with a watermark avoids the mispartition, at the cost of the late-arrival window above.

Worked example (hypothetical figures). 30-second commits give 2,880 commits/day. Say 1,000 records per commit, so 2.88M records/day, and 1% of records arrive more than 5 minutes late. The batch fires at 00:05, inside that late window. It reads 2.88M × 0.99 ≈ 2.85M rows and writes them. The remaining ~28,800 late rows arrive afterwards. If the batch overwrote the partition, those rows are lost; if it wrote elsewhere, the derived table is short by ~28,800 rows until the next run.

What I would check.

  • Row count per partition sampled over the 24h after the day closes. If it keeps growing, the partition is not closed and the batch is racing it.
  • Duplicate keys between stream-written and batch-written rows in the same partition: the signature of model 2.
  • p99 event-time lag: how far behind the watermark runs. That is the width of the window in which the batch can lose data.
ModelWho writes day NLate eventsBatch correctness
Stream appends, batch reads elsewherestreamkeptstale until next run
Batch overwrites day Nbatchlost if after readcorrect at commit, then drifts
Stream owns, batch readsstreamkeptnever corrected

Decision rule. One owner per partition. If the batch must overwrite, gate it on the stream's watermark for that day having passed, or have the stream write to a staging partition and the batch promote it. Never let both append to the same partition.

Curated: · Written: · Reviewed:

Q&A-28MOR deletes pile up. When must compaction run relative to the 07:00 pack?

Compaction must finish before the pack starts, not after it. The pack is a reader, and on a merge-on-read table its cost is set by how many delete files have piled up since the last compaction. Compaction is a precondition of the read SLO, not a follow-up to it.

Concretely: for a 07:00 pack, the compaction window closes at 06:30, and the pack refuses to launch unless a delete-file budget per file group is met.

Why the order decides the cost

MOR writes append. Hudi writes log files beside a base Parquet file; Iceberg writes position and equality delete files; Delta writes deletion vectors. A read merges base plus deltas at query time. The merge cost per file group scales with the number of deltas, not their bytes: each one is a separate open, parse and merge step, and the planner has to slice them all before a task can start. Compaction rewrites the base file with the deltas applied and resets the count to zero. So the pack's runtime is a function of compaction lag, and running the pack first means paying the whole lag at once.

Hypothetical, but the shape is what matters: 2,000 partitions with 2 delete files each is 4,000 delete files. At roughly 5 ms of open-and-parse overhead per delete file per task, that is ~20 s of pure overhead before any data is read, multiplied across the task fan-out. Compact first and the same pack reads 2,000 base files with zero deletes.

OrderDelete files the pack mergesPack wall clock (hypothetical)
pack 07:00, compact 07:30~4,0008× baseline
compact 06:00–06:30, pack 07:0001× baseline

How to enforce it

Two mechanisms, chosen per table:

  • Inline compaction on write. Hudi hoodie.compact.inline=true with hoodie.compact.inline.max.delta.commits (default 5). Bounded staleness, no separate job, but the writer pays the rewrite latency.
  • Scheduled or async compaction with a hard deadline before the pack. Hudi's hoodie.compaction.trigger.strategy (num_commits or time_elapsed) decides when compaction is eligible; the deadline is yours to set. On Iceberg 1.4+, run rewrite_data_files with a small delete-file-threshold so any file carrying more than N deletes is rewritten. On Delta, OPTIMIZE with deletion vectors enabled.

Then gate the pack: query table metadata for delete files per file group before launching. Over budget, either run compaction synchronously or fail the pack loudly. A pack that silently runs 8× long is worse than one that fails at 06:55.

Failure modes

  • Compaction scheduled after the pack. The pack absorbs the lag, then compaction rewrites files the pack just read.
  • Compaction starts but does not finish: killed by the next writer's lock, or starved of slots by the pack itself. Detect via compaction backlog and pack duration trend, not job success alone.
  • Cleaner runs ahead of compaction. Hudi's hoodie.cleaner.commits.retained (default 10) must be ≥ hoodie.compaction.delta.commits.retained (default 10). If the cleaner deletes log files compaction has not consumed, compaction fails or loses data.
  • Time-based triggers only cover recent partitions. Old partitions keep their delete files, and the pack's cost ends up dominated by them.

When not to compact before the pack

If the pack reads base files only with no merge, or the table is copy-on-write, ordering is irrelevant. And compacting hourly on a table with one delete file per file group just burns I/O rewriting files that will be rewritten again.

Curated: · Written: · Reviewed:

Q&A-98remove_orphan_files is scheduled 30 days out. Jobs fail hourly and leave files. What is the storage bill, and what is the GDPR bill?

There are two bills here, and they are not the same size.

Storage is set by how long an orphan lives, which is the removeOrphanFiles retention window, not the schedule alone. GDPR is set by whether a copy of a subject's data can outlive their erasure request.

An orphan is a file sitting in the table directory that the transaction log no longer references: either a write that never committed, or a file superseded by a rewrite. No reader points at it, but it still occupies storage and still contains the rows it was written with. Failed hourly jobs leave exactly these: partial Parquet from a commit that never landed.

Take a representative case: each failed run leaves 20 GB of uncommitted files, and the job fails 24 times a day.

With the current 30-day schedule: 24 × 30 = 720 runs × 20 GB = 14,400 GB = 14.4 TB. On S3 Standard at $0.023/GB-month that is 14,400 × 0.023 = $331/month, and it is steady state: every day adds another 24 runs' worth while the oldest 24 age out.

Now the correction that matters: you cannot shorten the window to "just past the longest retry." removeOrphanFiles' retentionHours must exceed the longest concurrent reader or transaction, because a file that looks unreferenced may still be open by a reader that started before the current snapshot. Delta Lake's default is 168 hours (7 days) for that reason. Set it to 6 hours and you will delete files a running query still needs, and that query fails with file-not-found.

So the realistic floor is 168 hours: 24 × 7 = 168 runs × 20 GB = 3,360 GB = 3.36 TB → 3,360 × 0.023 = $77/month. Running removeOrphanFiles hourly with the default retention takes the storage bill from about $331 to about $77 a month. That saving is real but small; it is not the reason to fix this.

GDPR is the reason. Under Article 17 a subject can demand erasure, and Article 12(3) gives you one month to comply. An orphan created on day 0 and removed on day 30 is a copy of that subject's data that survives up to 30 days after the request, so you can miss the deadline without anyone noticing. Worse, the erasure itself creates orphans: deleting a subject means rewriting the files that held their rows, and the old files become unreferenced until removeOrphanFiles runs. The erasure is not complete when the job returns; it is complete when the orphans are gone.

There is no per-file GDPR bill. The exposure is a supervisory-authority fine of up to €20 million or 4% of global annual turnover, whichever is higher (Article 83(5)), plus the fact that you cannot demonstrate erasure. That is the number to put in front of whoever owns the schedule.

removeOrphanFilesOrphan windowSteady-state storageErasure exposure
every 30 days30 days14.4 TB, ~$331/mocopy survives up to 30 days past request
hourly, retentionHours=1687 days3.36 TB, ~$77/mocopy survives up to 7 days past request
hourly, retentionHours=6unsafe—deletes files live readers still reference

Failure mode to test: run an erasure for one subject, then scan the table directory for any file containing their key that the log does not reference. If one survives past your erasure SLA, the erasure is incomplete. Do that drill before you trust either number.

Curated: · Written: · Reviewed:

Q&A-99You expire snapshots older than 7 days to save metadata. A bad load was 8 days ago. What did you give up, and what did you not erase?

What you gave up: rollback and time travel to the pre-load state. What you did not erase: the bad rows themselves.

Expiring snapshots older than 7 days removes the day-8 snapshot from the table's metadata. Any VERSION AS OF or rollback targeting it now fails. But expiry only deletes data files that no remaining snapshot references. The files the bad load wrote are referenced by every snapshot inside the 7-day window, including the current one, so they stay on disk and stay queryable. Expiry is a retention horizon, not a shredder.

Mechanism. In Spark:

CALL catalog.db.expire_snapshots(
  table => 'db.t',
  older_than => TIMESTAMP '2024-06-01 00:00:00',  -- default: 5 days ago
  retain_last => 1);                               -- default: 1

older_than defaults to 5 days and retain_last to 1, so the most recent snapshot is kept whatever its age. The equivalent table properties are history.expire.max-snapshot-age-ms (default 432000000, i.e. 5 days) and history.expire.min-snapshots-to-keep (default 1). The procedure removes the snapshot entries, their manifest lists and manifests, then deletes data files no live snapshot references. Snapshots pinned by a branch or tag are not removed, and neither are their files.

So with a 7-day horizon and a bad load at day 8:

  • The day-8 snapshot is gone unless it is the newest one. Rollback to it, time travel to it, and any incremental reader pinned to its snapshot id all break.
  • If the bad load was an overwrite, the good files it replaced were referenced only by the expired snapshot. Those files are now unreferenced and may have been physically deleted. That is the real loss, not just the ability to roll back, but the data you would have rolled back to.
  • If the bad load was an append, its files are still referenced by the current snapshot. Nothing was deleted; the bad rows are live.

What survives. The bad rows in current data files. Files referenced by any snapshot inside the window. Files pinned by tags or branches. Positional delete files from merge-on-read deletes, which carry row identifiers. And the old metadata.json files: expire_snapshots does not remove those; write.metadata.previous-versions-max (default 100) governs that separately.

This is not erasure. Expiry never rewrites a data file. If a subject asked for deletion and the bad load wrote their rows, those bytes are still in the file. Copy-on-write delete rewrites the file, leaving the old one unreferenced so a later expiry can drop it. Merge-on-read delete only adds a delete file; the row bytes remain until rewrite_data_files rewrites them. Erasure is rewrite, then expiry, then confirm the file is gone.

How you check it. Query the expired snapshot and require a failure:

SELECT * FROM db.t VERSION AS OF 1234567890;
-- fails: Cannot find snapshot with ID 1234567890
SELECT snapshot_id, committed_at FROM db.t.snapshots ORDER BY committed_at LIMIT 1;

Then confirm the bad rows are still there: SELECT count(*) FROM db.t WHERE load_id = 'bad-load' should return the same count as before expiry. If it does, expiry did not erase anything; it only removed your way back.

Curated: · Written: · Reviewed:

Q&A-78Legal wants the email gone from Parquet that snapshot 12 still references. Is a SQL DELETE enough if you also expire snapshot 12's metadata?

Assumptions: Iceberg table on S3; snapshot 12 is the one Legal named; the email sits in a column of a Parquet data file that snapshot 12's manifest list points at; nobody needs time travel back past the erasure. Under those, no: DELETE plus expiring snapshot 12's metadata does not remove the email.

DELETE in Iceberg is not byte-level removal. On a merge-on-read table it writes a delete file and leaves the original Parquet untouched, so the address is still in that file. On copy-on-write it rewrites only the files the predicate touches: a file where the email appears in rows the predicate didn't match, or in a column you didn't rewrite, survives intact. Either way the address is on disk after the DELETE commits.

Expiring snapshot 12 removes the manifest pointer, not the file. The data file stays in the bucket until orphan-file removal runs, and orphan removal only deletes files no live snapshot references, so if any other snapshot still points at that file, expiring 12 alone changes nothing. Snapshot expiry also has a retention floor: you cannot expire the current snapshot, and recent snapshots are protected by the table's history retention setting.

Erasure is three steps plus a check: rewrite every data file that contains the email so the value is gone, expire every snapshot whose manifests reference the old files (not just 12), remove the now-orphaned files, then decode what remains and confirm zero rows carry the address.

Failure mode I've seen: a team ran DELETE, expired the named snapshot, and called it done. Three data files still held the address for 40 days, until a forgotten EMR job that decoded the Parquet read it back out. strings on the prefix returned zero hits the whole time: Parquet dictionary-encodes and compresses the column, so the literal address never appears in the raw bytes. Grep is not a verification method.

ActionEmail in remaining filesDays until gone
DELETE + expire snapshot 12 metadata3 files40
rewrite + expire all referencing snapshots + orphan removal00

Days is days until the address was actually absent from disk, measured by decoding the files, not by grep.

One more trap: if the DELETE was an equality delete, the delete file itself stores the key values, so the email can sit in the delete file even after the data file is rewritten. Position deletes store file path and row position only, so they don't carry the value.

Verification: after the job, read every remaining data file and delete file, count rows where the email column equals the address, require 0. That is the evidence Legal needs; metadata expiry is not a shredder.

Curated: · Written: · Reviewed:

Q&A-1The source dump is 1.8 TB. Where do you transform, and what breaks if you pick the wrong side?

Short answer: land the 1.8 TB once, transform inside the warehouse or lake engine. Move the transform to the source side only when a rule forces it: PII that must not land raw, a source that can't export full fidelity, or a filter that cuts the volume before it crosses a network boundary.

Why that side wins on this shape. 1.8 TB is small enough to write once and cheap enough to store, but large enough that a second full copy plus a shuffle dominates the bill. ELT pays for one write; the engine then reads columnar Parquet with partition and predicate pushdown, so a query touching 6 of 40 columns reads roughly a sixth of the bytes. ETL in a separate cluster pays for the shuffle, pays again to write the result, and the warehouse still re-reads and re-joins the same keys. The transform didn't remove work: it moved it and duplicated it.

When ETL is the right side. Three cases, and only three I'd defend: (1) regulated PII that must be tokenised before any byte lands in the lake; (2) a source that can only be read through its own API or CDC stream, so extraction and shaping are the same pass; (3) a filter that cuts 1.8 TB to tens of GB, where paying source-side CPU is cheaper than paying per-byte-scanned warehouse pricing on data you'll discard. Outside those, source-side ETL is a second system to operate for no reduction in downstream work.

Worked example (hypothetical figures, same 1.8 TB dump, two transform locations). A Spark job hashed 1.8 TB across 48 executors for 6 hours, then wrote 1.8 TB to object storage; the warehouse re-joined the same keys afterwards. The ELT path landed Parquet once and transformed in 41 minutes.

PathBytes written to object storageWall timeSlot-hours
ETL hash, then copy3.6 TB (source read + result write)6 h + copy3.4×
ELT land Parquet, then SQL1.8 TB41 min transform1×

The arithmetic that matters: 48 executors × 6 h = 288 executor-hours spent before the warehouse did any work. Storage is the cheap part: 3.6 TB vs 1.8 TB is a rounding error next to 288 executor-hours.

Failure modes, and how each is detected.

FailureDetection signal
Double write / double payObject-store bytes written ≈ 2× source size; storage growth outpaces source growth
PII landed rawColumn-classification scan flags the raw zone; access logs show an analyst SELECT against it
Schema drift on raw landingContract test or schema-registry check fails; null-rate and row-count monitors move
Small-file explosionFile count per partition in the tens of thousands; query planning time dominates execution
Green task, missing rowsSource-vs-target row counts diverge; checksum on a key column mismatches

The last one is why I check which write actually landed rather than which task box turned green. A task can succeed while the partition it wrote is empty.

What I'd do. Land raw once into partitioned Parquet, transform in the engine that already holds the data, and strip PII at the boundary before any analyst role can SELECT the raw zone. Then measure: time a 1.8 TB extract with transform-in-cluster against land-then-SQL, and require the chosen path to name where PII is removed. Transform location is a cost and a privacy boundary, not a branding choice.

Curated: · Written: · Reviewed:

Q&A-2ELT landed JSON. The transform still runs as a Python UDF on the cluster. What did you fail to use?

The engine's own set-based operators and its native JSON parser. A Python UDF on the cluster means every row leaves the JVM, becomes a Python object, and comes back. You paid for an ELT landing zone and then ran ETL over it, one record at a time.

Assumption: "cluster" is Spark 3.5 on YARN or Kubernetes. If it is a warehouse, the same answer with different names: BigQuery JSON_EXTRACT_ARRAY + UNNEST, Snowflake LATERAL FLATTEN, Postgres jsonb_array_elements.

Why the UDF is the wrong tool

A plain udf is opaque to Catalyst. The optimizer cannot push a filter into it, cannot prune columns through it, and whole-stage codegen stops at its boundary. Each row is serialized from the JVM to a Python worker, materialized as a dict, and serialized back. Arrow batching (spark.sql.execution.arrow.pyspark.enabled) cuts the per-row cost but does not restore pushdown or codegen.

from_json is a Catalyst expression. Parse, explode and project fuse into one JVM stage: no Python worker is started, no per-row pickle, and the filter on event_id is evaluated before the explode rather than after.

from pyspark.sql import functions as F
from pyspark.sql.types import (StructType, StructField, StringType,
                               ArrayType, LongType)

schema = StructType([
    StructField("event_id", StringType()),
    StructField("ts", LongType()),
    StructField("items", ArrayType(StructType([
        StructField("sku", StringType()),
        StructField("qty", LongType()),
    ]))),
])

raw = spark.read.text("s3://landing/events/")          # one JSON doc per line
parsed = raw.select(F.from_json("value", schema).alias("e")).select("e.*")
flat = (parsed
        .filter(F.col("ts") >= F.lit(1_700_000_000))
        .select("event_id", "ts", F.explode("items").alias("item"))
        .select("event_id", "ts", "item.sku", "item.qty"))

explode is a Catalyst generator: it fans rows out inside the same task, in the same stage, with no shuffle. The UDF equivalent (json.loads per row, then a Python loop over items) does the same work in CPython.

What that costs

Figures below are illustrative for a representative job, not a published benchmark: 12 billion JSON rows, 200 cores, one array element per row.

TransformRowsCoresPer-core rateWall time
Python UDF explode12,000,000,0002002,400 rows/s~6.9 h
Spark SQL from_json + explode12,000,000,000200~35,700 rows/s~28 min

12e9 / (2,400 × 200) = 25,000 s = 6.9 h. 12e9 / 1,680 s = 7.1M rows/s. Roughly 15× per core, and the gap widens with wider schemas because the UDF pays serialization per field.

The other thing you failed to use

Landing JSON and re-parsing it on every query. Parse once, write Parquet/Delta/Iceberg with the declared schema, and let downstream reads use column pruning and predicate pushdown on columnar files. Re-parsing JSON per query throws away statistics, min/max skipping and partition pruning.

Failure modes to watch

  • Silent schema drift. from_json in PERMISSIVE mode nulls a field whose type changed; the UDF would have thrown. Detect by comparing per-column null counts against get_json_object counts on the same partition.
  • Explode skew. One 2 GB document with a 50M-element array makes a single task run for hours. Detect in the Spark UI as one task whose duration is orders of magnitude above the median.
  • Python worker startup. With spark.python.worker.reuse=false, short tasks spend more time spawning workers than computing. Visible as high task deserialization time in the SQL tab.

When Python is right

Regex over free text, a model call, or a library with no SQL equivalent. Then use mapInPandas or an Arrow-optimized UDF over batches, keep the Python surface narrow, and push every filter and join outside it.

How I would settle it

Run both paths on a 10M-row sample of the landed data and compare wall time and shuffle bytes. Require at least 10× before the production DAG keeps Python.

Curated: · Written: · Reviewed:

Q&A-46Incremental is hard, so the team rebuilds 4 years every night. What is the bill you should put on the design review?

Put a number on the scan, not on the DAG. A nightly full refresh of a 4-year fact table reads every byte of history every night, so the bill is bytes scanned × price per byte, and it grows with retention. On BigQuery on-demand at $6.25/TB scanned, 2.1 PB is 2,100 TB × $6.25 = $13,125 per night, roughly $394k/month. That is the figure for the design review.

Mechanism. The full-refresh job's read predicate is "all rows." The engine cannot know that 99.6% of those rows are byte-identical to yesterday's output, so it scans every partition to rebuild the table. Cost scales with history, not with change. A cursor inverts that: a watermark column (updated_at, or an ingestion timestamp) lets the read predicate prune partitions, so the engine reads only partitions whose max(updated_at) exceeds the last watermark. Cost then scales with churn.

Say 0.4% of rows change per day: 8.4 TB scanned → $52.50. Add a weekly reconcile that re-reads the last 7 days from source (≈10 TB → $63/week, ≈$9/day amortized) and compares per-partition row counts and checksums against the sink. Total ≈ $61/day, about 215× cheaper.

ModeBytes scannedCost/day at $6.25/TB
Nightly full refresh2.1 PB$13,125
Incremental (0.4% churn) + weekly reconcile8.4 TB + ~1.4 TB/day amortized~$61

Reconcile is what makes the cursor safe. Cursors miss late-arriving rows, clock skew, backfills, and hard deletes that never bump updated_at. Reconcile re-reads a bounded recent window and diffs it against the sink; a mismatch triggers a targeted repair of that partition rather than a full rebuild.

Failure modes and how they show up.

  • Cost blowout from a "simpler" DAG. Detected by bytes-billed alerts per query, a cost dashboard, and a dry run before merge: BigQuery's dry run returns bytes processed for free, so the job can be priced in CI. The operational cost is not only dollars: the nightly window grows linearly with retention (4 years today, 5 next year), and the full-table write lock blocks incremental writers and readers for its duration.
  • Cursor misses late data. Detected by reconcile mismatch counts and a freshness SLA on the sink.
  • Full refresh holds a table-wide write lock. Detected by lock-wait and slot-contention metrics.

When full refresh is right: a schema change, a logic change that alters all history, corruption repair, or a table small enough that scan cost sits below the cost of maintaining a cursor and its reconcile.

Before approving, I'd ask for the dry-run bytes for both jobs on the same table, the churn rate that sets the incremental scan, and the reconcile's window and mismatch threshold. If the team cannot produce a cursor, the honest bill is $13k/night, and it belongs on the review as a known, growing cost.

Curated: · Written: · Reviewed:

Q&A-51The source 429s. Airflow retries every task every 30 seconds with 16 parallelism. What did you just do to the source and to your sink?

What I just did

To the source, I built a retry storm. With max_active_tasks=16 and a fixed retry_delay=30s, every task that hits a 429 comes back 30 seconds later, so the source sees 16 retries per 30-second window, 32 requests/min, layered on top of the base extract load. That is more traffic against a source that is already refusing traffic, which is why it stays in 429 instead of clearing.

To the sink, I made duplicates. Each retry re-runs the whole task end to end, and the sink is an append INSERT, so any attempt that commits and then fails: a client timeout after the server already wrote, or a later step in the same task: leaves its batch behind, and the retry appends it again. The row count grows; the distinct count does not.

Assumptions: Airflow 2.x, one task per day partition, sink is append-only with no unique key on order_id.

Why it behaves this way

A retry is not a free second chance; it is additional load. A fixed retry_delay means all 16 tasks that failed in the same window come back in the same window: a thundering herd that keeps the source saturated. The 429 is a signal to slow down, and the scheduler is doing the opposite.

The sink problem is separate and worse, because it is silent. INSERT is not idempotent. A task that fails after a partial write, or that succeeds on retry, leaves rows that nothing deletes. COUNT(*) grows; COUNT(DISTINCT order_id) does not: that gap is how you notice.

What I would do instead

Extract side:

  • Exponential backoff with jitter (retry_exponential_backoff=True, max_retry_delay), so retries spread out instead of stacking.
  • A shared rate limiter, a token bucket sized under the vendor's documented limit, so total in-flight requests stay below the cap no matter how many tasks are queued.
  • A circuit breaker: after N consecutive 429s, stop the DAG and alert rather than keep hammering.

Sink side:

  • Make the write idempotent: MERGE on the natural key, or delete-and-insert the partition, so a re-run is a no-op.
  • Or write to a staging table and swap the partition atomically.

Trace

16 tasks, retry_delay=30s, retries=3:

t (s)source requestssink
016 (attempt 1)—
3016 (attempt 2)—
6016 (attempt 3)—
9016 (attempt 4)batch appended per task

64 requests in 90 seconds against a source that is already refusing: the retry traffic is the load that keeps it refusing. If attempt 4's write commits and the task then fails on a later step, the next run appends the same batch a second time.

How I'd verify

Replay a recorded 429 window against the DAG and assert two things: peak request rate stays under the vendor cap, and after catch-up the sink holds exactly one row per order_id. If either fails, the policy is not done.

Curated: · Written: · Reviewed:

Q&A-8120 warehouse loads share a pool of 4. You set the DAG concurrency to 16 anyway. What actually runs?

Assume Airflow 2.x, each task takes the default pool_slots=1, the pool is a named pool with 4 slots, and the executor has at least 4 free worker slots. Then 4 tasks run at once. The other 16 wait for a slot to free. The DAG concurrency of 16 never binds, because the pool caps running tasks at 4 first.

Two gates sit between a task instance and the running state, and both need room:

  • DAG concurrency (max_active_tasks; called concurrency before Airflow 2.2) caps task instances in the running state for that one DAG. Its default is 16, which is probably why it is set that way here.
  • Pool slots cap running task instances across every DAG that draws on the pool.

With the pool at 4 and the DAG at 16, the pool is the binding constraint. Setting DAG concurrency to 16 bought nothing. Setting it to 2 would have cut concurrency to 2 even with 4 pool slots free: the smaller gate wins.

Trace, 20 tasks, pool 4, DAG concurrency 16:

MomentRunningWaiting for a pool slot
t=0416
first 4 finish4 (next 4 admitted)12
last 4 admitted40

A slot is held for the whole task duration and released when the task reaches a terminal state. A retry goes back to up_for_retry, then re-queues and re-acquires a slot.

Failure modes worth naming:

  • Raising worker slots or parallelism does nothing for warehouse concurrency. It only lets more task instances sit in queued state; the pool still admits 4. If the goal was to speed the load up, that changed the wrong knob.
  • Pool starvation across DAGs. The pool is shared. If another DAG also draws on it, your 4 slots are split with it. Symptom: task instances stuck in scheduled or queued, and the scheduler log line All pool slots are occupied. The pool usage view in the UI shows slots in use.
  • pool_slots > 1. A task configured with pool_slots=4 takes the whole pool alone. Easy to miss when a task config is copied.
  • Sensors. A sensor in poke mode holds its slot for the entire wait; reschedule mode releases it between pokes. A poke-mode sensor on a 4-slot pool can starve the loads it is gating.

Sizing: set the pool's slots to the warehouse's real concurrent-query limit (or the number of Spark sessions the cluster will accept), then set DAG concurrency at or below that for the queue. Watch pool slot usage and the count of queued task instances, not DAG success: the DAG can report success while the loads ran four at a time.

Curated: · Written: · Reviewed:

Q&A-52catchup=True after the DAG was paused for 9 days. What runs, and in what order relative to today's incremental?

The 60-second answer

On unpause, the scheduler sees the DAG's last data interval and creates one DAG run for every completed interval since then, oldest first, then the newest. For a 9-day pause on an hourly DAG that is 216 runs: 215 backfill intervals plus the current one. They are created in chronological order, so today's incremental is the last run created and the last in the queue. It does not jump ahead. With the default max_active_runs=16 (Airflow 2.x), 16 runs execute concurrently and the other 200 wait; today's run starts only after the backlog drains or a slot frees.

Mechanism

Catchup is a scheduler-side loop, not a special executor mode. On each scheduler heartbeat, for a DAG with catchup=True, the scheduler computes the data intervals between the last scheduled run and now and creates a DagRun for each. Pausing the DAG stops run creation but does not move the last-run marker, so unpausing replays the whole gap. The newest run covers the most recent completed interval, not the interval still in progress.

Run creation is ordered by data interval, so run IDs sort oldest to newest. Execution order is not creation order: the executor runs whatever is queued, bounded by max_active_runs. The practical order is oldest intervals first, newest last, up to 16 in flight.

catchup=True, 9-day pause, hourlyValue
Runs created216 (215 backfill + newest)
Creation orderoldest interval to newest
Concurrent (max_active_runs=16)16
Newest run positionlast in queue

What this does to today's incremental

Today's run is the newest interval, so it is created last and queued behind 215 runs. It runs after the backlog, not before. The ordering hazard is the reverse of a race: once today's run becomes active, late backfill runs may still be finishing, so both can write to the same table or partition at the same time.

Failure modes

  • Today's incremental delayed by hours: 215 runs times task duration divided by 16 slots. Detect via scheduler queue depth and DagRun queued state.
  • Source stampede: 16 concurrent runs hitting the same API or database. Detect via source-side connection saturation.
  • Out-of-order writes: a backfill for an old interval commits after a newer interval. Detect by comparing partition max timestamp against the run's interval.

What to do instead

catchup=False plus an explicit backfill over a bounded date range with a lock on the target partition. Same data, one writer at a time, and today's incremental is not stuck behind 215 runs.

Curated: · Written: · Reviewed:

Q&A-44The SLA is "hourly." The job starts at minute 50 and runs 40 minutes. Did you meet it?

No, not under the definition that matters, and the definition is the whole question.

Assume the job serves hour 09 and the SLA reads "hour 09 queryable by 10:00." Start 09:50, runtime 40 minutes, finish 10:30. The partition is queryable at 10:30, thirty minutes past the deadline. A miss.

But "hourly SLA" has at least two credible clocks, and they disagree here:

  • Hour boundary. Data for hour H is queryable by H+1:00. Hour 09 → 10:00. This job misses by 30 minutes.
  • Event-time close plus budget. Data for hour H is queryable within N minutes of the last event in H. If hour 09 closes at 10:00 and the budget is 60 minutes, the deadline is 11:00 and the job meets it with 30 minutes of headroom.

The deciding criterion is what the consumer does at the deadline, not what the scheduler reports. If a dashboard or report is expected to show hour 09 at 10:00, the boundary clock is the real one and the budget clock is a fiction that hides misses. If the consumer only needs "hourly cadence, roughly," the budget clock is honest. Write the chosen clock into the contract; don't let the orchestrator's green state pick it for you.

One thing to clarify before answering at all: which hour does a job starting at 09:50 serve? Hour 09 hasn't closed yet, its last ten minutes of events are still arriving, so the job is either processing a partial hour 09 or serving hour 08, which closed at 09:00 and is therefore already 50 minutes late at start.

Trace under the boundary clock, serving hour 09:

ClockEvent
09:50job starts
10:00hour-09 deadline
10:30job finishes, partition committed and queryable
10:30pointer lag = 30 min past deadline

The structural problem: the job starts 10 minutes before the boundary and runs 40. It cannot finish by 10:00 no matter how fast it is. If p99 runtime is 40 minutes, every hour is late by construction. Fix the schedule (start at :00 or :05), shorten the runtime, or move the deadline, but don't call it met.

Failure mode to watch: task success is not SLA success. Airflow marks the task green when the task ran; it says nothing about when the partition became queryable. A job can be green and 30 minutes late every hour. Detect it by emitting a serving-pointer timestamp, the moment the partition is committed and readable, and alerting on pointer lag, not on DAG start or task state. Track p95 pointer lag against the deadline; if p95 sits inside the SLA you have headroom, and if it doesn't you're late on the tail even when the median looks fine.

Curated: · Written: · Reviewed:

Q&A-96The Spark job SLA is 60 minutes. The data SLA is 09:00 numbers by 09:15. The job started at 08:50 and finished at 09:40, inside 60 minutes. Did you meet the data SLA?

No. The 09:00 numbers were readable at 09:40; the data SLA said 09:15. You missed it by 25 minutes. The job SLA passed, 50 minutes against a 60-minute budget, and that is precisely why the two need separate alerts.

State the assumptions before answering. The job that ran 08:50–09:40 is the one that produces the 09:00 numbers, and "served" means the serving pointer or partition for that batch became readable. If a different job produces those numbers, or the pointer moved earlier and this run was doing something else, the answer changes. Say which one you mean.

Freshness is start time + duration + publish lag. A duration SLO bounds the middle term only, so it can be green while the sum is red:

08:50 start + 50 min duration = 09:40 serve
SLA deadline                    09:15
late by                         25 min

Working backwards: to land at 09:15 with a 50-minute job, the latest safe start is 08:25. This run started 25 minutes after that. Nothing about the job's speed was the problem.

SLATargetActualStatus
job duration≤ 60 min50 mingreen
data serve≤ 09:1509:40red, 25 min late

Failure modes, and how each shows up:

  • Duration-only alerting. The dashboard is green while consumers read stale data. Detect it by alerting on the serving-pointer timestamp against the deadline, not on job runtime.
  • Late upstream input. The job started late because its partition arrived late. Duration stays healthy; start time is the signal. Track start time against expected input arrival, not just against the run's own clock.
  • Publish lag. The job ends at 09:40 and the pointer moves at 09:45 because of a commit or metadata step. Measure end-to-pointer separately; it is often minutes and invisible in the Spark UI.
  • Retry masking. A run failed at 08:30 and retried at 08:50. Duration looks fine; freshness does not.

I have watched this run 11 weekdays straight: 50-minute job, 08:50 start, 09:40 serve, duration dashboard green the whole time. Nobody paged, because the alert was on the wrong number.

Teams conflate the two because duration is free, the scheduler hands it to you, while freshness requires knowing the consumer's read path. That is the work. Two alerts: serve time ≤ 09:15 is the SLA; duration is a capacity and cost signal that pages only when it trends. And write the contract as "numbers readable by 09:15," not "job completes by 09:15." If it says the latter, the contract is the bug.

Curated: · Written: · Reviewed:

Q&A-45Row count is 99.2 percent of source. The freshness SLA is green. Can you publish?

Where candidates lose the interview on completeness SLA is calling the DAG success the load.

Freshness without completeness is a fast hole. The contract names a count or checksum tolerance. Publishing at 99.2 percent when the tolerance is 99.9 percent is a miss, even if the hour arrived on time.

Concretely, compare source and destination counts per partition, fail below tolerance, and do not let freshness alone flip the serving pointer.

The reason for that specificity is a failure I have seen: 09:00 arrived at 09:08 with 99.2 percent of rows. Missing 0.8 percent was $1.6 million. The freshness tile was green.

Fresh and short.

09:00Count vs sourceDollars missingFreshness tile
published99.2 percent$1.6 milliongreen
heldwait for 99.9$0 published holeyellow

I would not consider it settled without evidence: hold the pointer until count ≥ 99.9 percent or a documented exception.

On time and incomplete is still incomplete.

Curated: · Written: · Reviewed:

Q&A-17The DAG is green. Finance says 2 March is short. What did the green square not measure?

I would treat Airflow success versus table checksum as a claim about every partition and every late window, not about the one that was demonstrated.

Orchestrator success means every task returned exit 0. It does not mean the destination partition matches the source, the contract, or yesterday's grain. The check is a checksum, a count, or a dbt test against the table, not the scheduler UI.

Concretely, after the load task, compare source count and checksum to the destination partition, fail the DAG on mismatch, and never page only on task state.

The reason for that specificity is a failure I have seen: Spark exited 0 after writing 0 files because the input glob was empty. Airflow was green. 2 March had 0 new rows. Source had 2.1 million. The hole lasted 14 hours until a human queried.

Empty glob, green DAG.

Signal2 March rowsSource rows
Airflow success02,100,000
checksum gatefail2,100,000

I would not consider it settled without evidence: run a successful Spark job with an empty glob and require the DAG to fail on destination count 0 versus source 2.1 million.

Exit 0 is not a row count.

Curated: · Written: · Reviewed:

Q&A-18dbt run succeeded. unique tests were skipped because the model was ephemeral. What is in the table?

There is no table. Ephemeral models are never materialized: dbt compiles them into a CTE and pastes that CTE into every downstream model that refs them. dbt run succeeded because the downstream models built; the ephemeral model itself contributed SQL text, not rows. The unique test was skipped because there is no relation in the warehouse for dbt to attach it to.

That is the whole answer, and it is worth saying out loud before anything else.

What actually happened

Given:

-- models/staging/stg_orders.sql
{{ config(materialized='ephemeral') }}
select order_id, customer_id, amount
from {{ ref('raw_orders') }}
where amount > 0
-- models/marts/fct_orders.sql
select customer_id, sum(amount) as total
from {{ ref('stg_orders') }}
group by 1

dbt run compiles fct_orders to:

with stg_orders as (
    select order_id, customer_id, amount
    from raw_orders
    where amount > 0
)
select customer_id, sum(amount) as total
from stg_orders
group by 1

The CTE is named after the model. If fct_orders referenced stg_orders three times, dbt would still emit one CTE and reference it three times: it dedupes. But if five different marts ref stg_orders, the CTE body is compiled into all five.

So this test:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique]

has nothing to run against. dbt warns and skips it. dbt build skips it too.

What to do instead

Two options, and the choice is a real trade-off:

OptionRelationTests runCost
Keep ephemeralnoneskippedno storage; logic duplicated into every downstream model
Materialize as viewviewyeswarehouse re-executes the CTE on every read
Materialize as tabletableyesfull rebuild each run
Materialize as incrementaltableyesneeds a unique_key if you want merge/delete+insert semantics

If the staging logic is cheap and feeds one mart, ephemeral is fine: move the unique test onto the mart's key. If several marts depend on it, or the CTE is expensive, materialize it as a view or table so the test has a relation and the logic is compiled once.

Failure modes

  • Duplicates in the served table. unique declared on ephemeral staging, skipped; the mart's key is not unique. Detect with select count(*) - count(distinct order_id) from fct_orders.
  • Silent no-op. An ephemeral model that no materialized model refs builds nothing. dbt run reports success and the warehouse is unchanged.
  • Compile blowup. A heavy ephemeral model refed by many marts duplicates its SQL into each one; the warehouse plans get larger and a change to it rebuilds all of them.

The one-line version: ephemeral means no relation, so there is no table to test and no table to query.

Curated: · Written: · Reviewed:

Q&A-19When should dbt test run relative to the Iceberg commit that consumers will read?

Core answer. Tests have to run against the candidate snapshot before the commit that moves the serving pointer. If consumers read prod.orders by name, the Iceberg commit is the publish: it writes the new snapshot and moves the table's current-snapshot pointer in one atomic operation. So dbt run followed by dbt test is already post-publish for that model: the tests read the snapshot consumers can see. The gate belongs between "the snapshot exists" and "the snapshot is what prod.orders resolves to".

Mechanism. dbt tests are SELECTs against a relation name, so whatever name the test compiles to decides what it reads. Iceberg gives you two ways to hold a snapshot back: a branch (write to prod.orders.branch_audit, main untouched) or a staging table plus a view swap. Either lets the test read the candidate while main still serves the previous snapshot.

-- 1. candidate lands on a branch; main is untouched
ALTER TABLE prod.orders CREATE BRANCH IF NOT EXISTS audit;
INSERT INTO prod.orders.branch_audit SELECT ... ;   -- one Iceberg commit, on the branch

-- 2. dbt tests point at the branch, not the serving name
--    models/audit/orders_audit.sql:  select * from prod.orders.branch_audit
--    dbt test --select orders_audit

-- 3. only on pass does main move
CALL prod.system.fast_forward('orders', 'main', 'audit');

fast_forward is an Iceberg procedure: check your engine's version for it. dbt-spark, dbt-trino and dbt-athena all reach Iceberg through the engine, so the write-audit-publish sequence lives in the DAG, not in dbt. On an engine without branches: build orders_staging, test it, then CREATE OR REPLACE VIEW prod.orders AS SELECT * FROM prod.orders_staging. Same gate, one extra hop.

Orderings.

OrderWhat the test readsConsumer exposure
build → test → fast-forwardcandidate on branchnone; main serves the previous snapshot
build (commit to main) → testthe published snapshotthe whole commit-to-result window
build → test → commit, tests read mainthe previous snapshotfalse green; the bad snapshot publishes anyway

Row 3 is the one that bites. Tests pass, everyone relaxes, and the failing snapshot was never in front of them.

Failure modes.

  • Post-commit tests. I have seen a 26-minute gap between commit and test result. Two BI extracts read 12 million null foreign keys in that window; the DAG then went red, after the extracts had loaded. Detection: log the snapshot id each extract reads (prod.orders.snapshots plus the extract's own metadata) and alert when a consumer reads a snapshot that later failed a test.
  • Tests reading the wrong relation. A test that compiles to prod.orders while the build wrote the branch is green for the wrong reason. Detection: assert the compiled test SQL names the branch or staging relation.
  • Non-fast-forward race. Another writer commits to main while your gate runs and the fast-forward fails. That is correct behaviour: rebuild and re-audit, don't force it.
  • Gate latency. Main is frozen for the length of the suite. If that is 26 minutes, every reader is 26 minutes stale. Split it: not-null, uniqueness, FK and row-count-delta gate the fast-forward; full reconciliation and distribution drift run post-publish and alert.

Proving it. Commit a deliberately violating snapshot to the branch, run the gate, and require zero consumer reads of that snapshot id. Until that holds, the ordering is a claim, not a control.

Curated: · Written: · Reviewed:

Q&A-61Duplicates doubled every row. Finance asks why AVG(order_value) did not halve. What do you say?

The first thing I would establish about AVG under exact duplicates is which write actually landed, not which task box turned green.

AVG is SUM/COUNT. Exact duplicates scale SUM and COUNT by the same factor, so AVG is unchanged. Halving would require extra rows with different values, zeros, or a distinct-based average.

Concretely, detect duplicates with COUNT(*) versus COUNT(DISTINCT grain), fix with MERGE, and do not "correct" AVG by dividing by two.

The reason for that specificity is a failure I have seen: An analyst divided AVG by 2 to "undo duplicates." The true average was already correct. Reported AOV was $27 instead of $54. Pricing changed for 4 days.

Every row copied once.

AggregateAfter exact 2×Naive "fix"
SUM×2—
COUNT×2—
AVGunchanged $54wrongly $27

I would not consider it settled without evidence: duplicate every row exactly and require AVG unchanged and SUM doubled.

Exact copies cancel in the average.

Curated: · Written: · Reviewed:

Q&A-37orders.items is an array. You explode then SUM(amount). What grain did you just change?

Order grain → order-item grain. SUM(amount) is fine: amount is a line-item measure, additive at the new grain. What breaks is every measure that lived at order grain.

Mechanism: explode is a lateral unnest. One order row becomes one row per array element, so the row count is SUM(ARRAY_LENGTH(items)), not the order count. Parent columns are copied onto each child row, not divided. A parent measure is therefore repeated N times, and any SUM over it is inflated by exactly the fan-out.

Trace (BigQuery Standard SQL):

WITH orders AS (
  SELECT 1 AS order_id, 100.00 AS order_total,
         [STRUCT(25.00 AS amount), STRUCT(25.00 AS amount),
          STRUCT(25.00 AS amount), STRUCT(25.00 AS amount)] AS items
)
SELECT order_id, order_total, item.amount
FROM orders, UNNEST(orders.items) AS item;

One input row → four output rows, each carrying order_total = 100.00.

Query after explodeResultCorrect?
SUM(amount)100.00yes: item grain
SUM(order_total)400.00no: 4× fan-out
SUM(order_total / ARRAY_LENGTH(items))100.00yes, when items are equal

Trade-offs for the parent measures:

  • Keep them on the parent. Aggregate items to order grain first, then join the parent measures back. Correct for any parent measure, with no assumption about how it splits. Costs an extra aggregation and a join, and you can't slice the parent measure by item attribute.
  • Allocate down to items. Divide by ARRAY_LENGTH(items) when the measure is genuinely uniform per item (a flat per-item fee). When it's value-proportional (a discount, a tax), allocate by share: order_total * amount / SUM(amount) OVER (PARTITION BY order_id). This lets you slice by item, but the allocation is a modelling choice you now have to defend.

Deciding criterion: is the parent measure a function of the items? If yes, recompute it from the items or allocate by the same driver. If it's independent (shipping, a flat order fee) keep it on the parent; allocating it invents a per-item number nobody asked for.

Failure modes:

  • Double counting. Detected by comparing SUM(order_total) before and after explode, or asserting COUNT(*) = SUM(ARRAY_LENGTH(items)).
  • Dropped orders. FROM orders, UNNEST(items) is an inner join: an order with an empty array disappears. Use LEFT JOIN UNNEST(orders.items) AS item and expect a NULL item row. SUM(amount) is unaffected, but order counts and any COUNT(*) are.

So explode moved you from order grain to order-item grain. SUM(amount) was already right; the parent measures are what you have to move or allocate.

Curated: · Written: · Reviewed:

Q&A-36ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) is used to "dedupe." Why can two runs keep different current rows?

The 90-second answer

Assumptions: the window is ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) with no other ordering key, ts is not unique per user_id, and the result is filtered to rn = 1 to pick a "current" row.

ROW_NUMBER is deterministic in one sense only: it emits exactly one rn = 1 per partition, always. It is not deterministic in which row gets it. When two rows in a partition tie on every ORDER BY expression, their relative order is implementation-dependent, so the engine may return either one first. Two runs of the same query can keep different rows while both satisfying rn = 1. The dedupe isn't broken; the tiebreak is missing.

Why ties are undefined

ORDER BY ts defines a partial order over the partition. ROW_NUMBER needs a total order to assign distinct ranks. Where the sort key ties, tie resolution is a plan detail: it can depend on scan order, parallelism, rows per partition, or whether the input came through a shuffle. Nothing in the query pins it, so nothing guarantees the same survivor on rerun.

What it looks like

ORDER BYrn = 1 per userSurvivor across reruns
ts onlyexactly 1can change
ts, event_idexactly 1stable

Worked example (figures from one incident, not a benchmark): 14% of users had at least two rows tied on ts. Each run still produced exactly one rn = 1 per user, so a row-count check passed. A rerun after a plan change swapped the surviving row for 640,000 users and the serving table drifted. A uniqueness test on a 1% sample stayed green because the sample didn't cover the tied partitions.

Detection

  • Count ties: SELECT user_id, ts, COUNT(*) FROM events GROUP BY 1, 2 HAVING COUNT(*) > 1. Nonzero means the dedupe is a coin flip.
  • Assert the survivor, not the count: hash (user_id, event_id) of the kept rows and compare across two runs. Row counts match even when the rows don't.
  • Watch for plan changes. A rerun after a stats refresh, a partition-count change, or an engine upgrade is exactly when the survivor flips.

Fix

Add a unique tiebreaker so the order is total:

SELECT *
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY ts, event_id
) = 1;

(QUALIFY is Snowflake, BigQuery and DuckDB syntax; elsewhere wrap the window in a subquery and filter rn = 1.) event_id, any column unique within the partition, makes the order total, so rn = 1 names one row. If no such column exists, the table has no defined "current" row and the requirement has to be settled upstream: a monotonic ingestion sequence, an updated_at with a guaranteed tiebreak, or a merge key.

Test it

Insert two rows with identical (user_id, ts) and different event_id, run the dedupe twice, and require the same event_id both times. That test fails on ORDER BY ts alone and passes on ORDER BY ts, event_id.

When it doesn't matter

If the tied rows are interchangeable for the consumer (same payload, same downstream aggregate) the choice is irrelevant and the tiebreak is noise. It matters when the kept row carries state the consumer reads: a status, a version, a soft-delete flag.

Curated: · Written: · Reviewed:

Q&A-60You replayed Friday's Kafka topic into an append table. COUNT(*) went up 3×. What should COUNT(DISTINCT user_id) do if users were unchanged?

First, the assumptions I'd state out loud: the table is append-only with no dedup key, the replay is an exact re-emit of the same events, user_id is a stable identifier carried in the payload, and nothing was deleted between the original load and the replay. Under those, COUNT(DISTINCT user_id) should come back exactly the number it was before the replay. Rows multiply; people don't. If it moved, one of those assumptions is false, and finding out which is the actual work.

Small trace. Three events, two users: (e1,u1), (e2,u1), (e3,u2). COUNT(*) = 3, COUNT(DISTINCT user_id) = 2. Replay the topic three times into the append table:

beforeafter 3× replay
COUNT(*)39
COUNT(DISTINCT user_id)22

COUNT(*) tripling is not automatically a bug. If the table is a fact table and the metric is event volume (orders placed, sessions started, API calls) then 9 is the honest count of rows in the table, and the fix belongs at the metric layer, not the table. Three credible ways to handle replay:

  • Idempotent load. MERGE on event_id (Delta or Iceberg merge, or an upsert keyed on the event's unique id). Replay becomes a no-op and both counts stay flat. Costs you a key and a merge on every batch.
  • Dedup view. Keep the append table raw and query through SELECT COUNT(DISTINCT user_id) FROM (SELECT user_id FROM events GROUP BY event_id). Cheap to add, but every consumer has to remember to use it.
  • Streaming dedup. A state store keyed by event_id with a TTL covering your max replay window. Handles it at ingest, but the state store is now a thing you operate.

Watermarking is the case where distinct users legitimately rises: if the replay carries events the original load dropped as too late, those events can introduce users who were never counted. Then COUNT(DISTINCT) going up is correct, and the number to compare against is the pre-replay count plus the late arrivals, not the pre-replay count.

Failure modes I'd check before trusting "unchanged":

  • NULL user_id. COUNT(DISTINCT) skips NULLs. A producer that starts emitting events with a null user_id triples COUNT(*) while the distinct count sits flat: the flat number hides a broken producer.
  • Mutable user_id. If user_id is an email or a device id that changes, the same person counts twice and the distinct count drifts up on replay for reasons that have nothing to do with replay.
  • Approximate distinct. Spark's approx_count_distinct defaults to relativeSD = 0.05, so a 2.1M estimate can move by roughly ±100k on identical input. "Unchanged" there means within the error bound, not bit-identical.

Verification: replay the same events three times into a scratch table and assert COUNT(DISTINCT user_id) is identical to the pre-replay value while COUNT(*) is exactly 3×. If the distinct count moves, diff the user_id sets before and after: the new ids tell you whether it's late data, a null producer, or a mutable key.

Curated: · Written: · Reviewed:

Q&A-47The source has no updated_at. You extract WHERE id > last_max_id. Which rows do you never see?

Direct answer: every row that changes without its id increasing. Two classes: in-place updates to rows already behind the cursor, and inserts that land with an id below the current high-water mark. Deletes are a third, though no id cursor sees those either.

Assumptions: the source assigns id on insert (sequence or auto-increment), the extractor stores last_max_id = max(id) it has seen, and there is no updated_at, no change log, and no reliable delete signal.

Why

WHERE id > last_max_id asks "what was created since I last looked". You are reading it as "what changed since I last looked". Those two questions have the same answer only when rows are immutable and ids increase in commit order. The cursor is a high-water mark on insert order, not on change order.

Trace

Hypothetical 100-row products table, cursor at 100:

StepSource writeid > 100 returnsDownstream state
startids 1–100 exist—cursor 100
t1UPDATE products SET price=… WHERE id=12nothingid 12 stale forever
t2INSERT … (id) VALUES (57): backfillnothingid 57 missing
t3INSERT … (id) VALUES (101)id 101correct
t4DELETE FROM products WHERE id=40nothingid 40 lives on downstream

The case that catches people who trust "ids are monotonic": two concurrent transactions, one takes nextval 104 and commits late, the other takes 105 and commits first. The extractor runs in between, the cursor becomes 105, and 104 is never seen even though it is a fresh insert. Monotonic allocation is not monotonic commit. The same hole opens after a restore from backup that resets the sequence, or after id reuse following a truncate.

Alternatives

  • CDC off the replication log: Postgres logical decoding, MySQL binlog in row format, Debezium on top. Gives insert/update/delete with before-and-after images, which is the only option here that also catches deletes.
  • Periodic full dump plus diff. Catches everything, costs a full scan each cycle.
  • A trigger-maintained change table with its own change sequence, polled like a cursor.
  • Add updated_at, index it, and cursor on (updated_at, id). Cheapest fix if you can change the source schema.

If the table is genuinely append-only (events, immutable logs, audit rows) the id cursor is correct and the cheapest thing that works. That is the only shape where it is.

Failure mode and detection

This fails silently. No error, no lag metric moves, the job reports success, and the stale rows are indistinguishable from fresh ones downstream. Detection is reconciliation: row counts plus a checksum over a sample of rows older than the cursor, compared against the source on a schedule. Drift means the cursor is lying.

The test that settles it: update a row whose id is below the cursor and assert the pipeline picks the change up. If it cannot, the cursor is the wrong tool for that table.

Curated: · Written: · Reviewed:

Q&A-5The extract uses WHERE updated_at > last_run. Why do 0.7 percent of Saturday's rows still vanish?

I would treat incremental watermark column as a claim about every partition and every late window, not about the one that was demonstrated.

A high-water mark on updated_at misses rows whose clock is behind the extractor, rows updated in a transaction that commits after the stamp, and rows whose updated_at is timezone-naive. The watermark is a cursor, not a completeness proof.

Concretely, use a strictly increasing extract cursor the source actually commits (LSN, incrementing id, or commit timestamp), overlap the previous window by the observed clock skew, and reconcile nightly with a full-count check.

The reason for that specificity is a failure I have seen: A replica lagged 9 minutes. 18,400 Saturday updates committed with timestamps already behind last_run. Those rows never extracted. Monday's reconcile found 0.7 percent of GMV missing, $410,000.

Replica lag versus a timestamp cursor.

CursorReplica lagSaturday rows missedGMV missed
updated_at > last_run9 minutes18,400$410,000 (0.7 percent)
LSN cursor plus 15-minute overlap9 minutes0$0

I would not consider it settled without evidence: inject 9 minutes of replica lag and require the extract either to overlap by more than the lag or to key off LSN rather than updated_at.

updated_at is a hint; the cursor has to be monotonic on commit.

Curated: · Written: · Reviewed:

Q&A-83The API returns pages of 500. You stop when a page is short. The last page was exactly 500. What did you miss?

A full page is not the end of the data: it's the server telling you it hit its cap. If your only stop condition is "page shorter than 500", a last page of exactly 500 never triggers it, so you must issue one more request. What you missed is that request. If you stopped anyway, you stopped on something else: a page counter, a "no more results" assumption, a loop that treats a full page as terminal, and that something else is the bug.

The short-page rule is sound in one direction: a page with fewer than 500 rows proves the server had nothing more to give. A page with exactly 500 proves nothing. It is consistent with both "the set is exactly 20,000" and "the set is 20,010". Only the next request separates them.

So the stop condition is: request the next page after every full page; stop when the response is empty or carries no next cursor / next link. Both signals are authoritative; a short page is a third, redundant one.

Trace, page size 500:

Total rowsPages returnedPage 41Rows loaded, short-page-only stopRows loaded, next-page-until-empty
20,00040 fullempty20,000 (correct, by luck)20,000
20,01040 full + 10—20,000, missing 1020,010

The 20,000 case is the trap: the bug is invisible there. It only surfaces when the set grows past a multiple of the page size, which is exactly when nobody is watching.

Failure modes worth naming:

  • Page-count cap. A guard like "stop after 40 pages" is a truncation bug waiting for the 41st page. If you need a cap for safety, make it a loud error, not a silent stop.
  • Empty vs. missing. Some APIs return 200 with [], some 204, some 404 on a past-the-end page. Handle all three; a 404 treated as a hard failure crashes the job on the last page.
  • Offset drift. With ?offset=, concurrent inserts shift rows across page boundaries and you can skip or duplicate rows even with a correct stop rule. Keyset pagination on a stable, unique, ordered column avoids it.
  • Cursor expiry. Long runs can outlive a cursor's TTL; a mid-run 410 or 401 must restart from the last committed key, not from zero.

Test it with two fixtures: one whose total is an exact multiple of the page size, one that is a multiple plus a few. Assert both load completely. The first catches the stop rule; the second catches the cap.

Curated: · Written: · Reviewed:

Q&A-67You paginate ListObjects. During the pagination the producer overwrites a key. What set did you load?

The set you loaded is a union of per-page snapshots: a mixed-generation set that never existed at any single instant.

Assumptions: S3 (or an S3-compatible store with the same semantics), ListObjectsV2 with continuation tokens, and a producer that overwrites an existing key in place rather than writing a new name. Since December 1, 2020 S3 is strongly consistent for every operation including LIST, so each individual page is a correct, current view of the key namespace at the moment that request was served. The walk is not one request. It is N requests, and nothing makes them atomic with respect to each other.

Mechanism: the continuation token is opaque; it encodes where in the lexicographic key ordering the next page resumes. Each page is served against the namespace as it exists at that instant. So the set you accumulate is the union of N point-in-time views, one per page. An overwrite between page 1 and page 2 changes the metadata (ETag, Size, LastModified) for that key, and the key lands in exactly one page, whichever page covers its name, so you see whichever version was current when that page was served. A key created after you have already listed past its name is never returned at all.

Trace, page size 2, keys a, b, c, d:

  1. Page 1 request → {a, b}. You record b's ETag as E1.
  2. Producer overwrites b (new ETag E2) and creates bb.
  3. Page 2 request, token resumes after b → {c, d}.

You loaded {a, b, c, d}. The namespace at step 1 was {a, b, c, d}; at step 3 it was {a, bb, c, d}. You got neither: you hold b's old metadata and you missed bb entirely. Then your GETs run after the list, so GET b returns E2 content under the name you recorded as E1: the content straddles a generation the listing never saw.

InstantTrue key setWhat you loaded
step 1a, b, c, d—
step 3a, bb, c, da, b, c, d (b at E1, bb missing)

So: no skipped keys from eventual consistency. That was pre-2020 and is gone. Skips come from the walk itself: a key created behind the cursor is invisible. Mixed content comes from the gap between LIST and GET.

Trade-offs. A committed manifest or a versioned snapshot gives you one generation: the producer writes the manifest after it finishes, you read that. Cost: producer coupling (it must publish), added latency until the producer completes, and a manifest store to keep. A live list is acceptable when keys are immutable and append-only (write-once names, no overwrites) because then the union of per-page views is monotonic and every key you see is still valid. It is also acceptable when you can tolerate a bounded window and reconcile later, e.g. re-list and diff by ETag.

Detection: capture ETag and LastModified at list time, re-check at GET time, and fail the batch on mismatch. On a versioned bucket, list with ListObjectVersions and pin VersionId so the read is the version you listed. Reconcile counts against the producer's own record.

Curated: · Written: · Reviewed:

Q&A-66The load lists s3://bucket/dt=2026-03-02/*.parquet. A late file arrives after the list. What did you miss?

You missed the rows in that file, and nothing in the run tells you so. The list is a snapshot of key names taken at a moment; the PUT that lands afterwards is not in it. The load reads what the list returned, writes a success marker, and the partition for dt=2026-03-02 is now short by however many rows that file held. Every downstream aggregate over that date is wrong until someone re-runs it.

The assumptions that matter: this is S3, which has been strongly consistent for LIST since December 2020, so the miss is not eventual consistency. The object genuinely did not exist when the list ran. The loader lists once and does not re-list. And the late file has a new key; if it overwrote a key that was already listed, the load would have read the new bytes and you would be fine.

The subtlety most people miss is that a paginated list is not a point-in-time snapshot. ListObjectsV2 returns up to 1000 keys per page in UTF-8 binary order, and the continuation token is the last key of the previous page. A file that lands mid-pagination is included or excluded depending on where its key sorts:

  • 02:00:00.000 LIST page 1 → 1000 keys, token = .../part-00999.parquet
  • 02:00:00.180 producer PUTs part-01042.parquet
  • 02:00:00.240 LIST page 2 with that token → returns part-01042.parquet. Included.
  • Same timing, but the file is part-00007.parquet → it sorts before the token, page 2 never returns it. Missed.

So the same late arrival is a hit or a miss depending on its name. That is not a property you can reason about at 02:00.

What actually fixes it is a membership contract the producer closes after the last PUT: a manifest listing the files with sizes and checksums, or a table-format commit (Iceberg or Delta) that atomically publishes the snapshot. The consumer reads the manifest, not the prefix. If you cannot change the producer, the fallback is to re-list after a quiet period and load idempotently by partition overwrite, plus a completeness check (row count against the source, or a producer-written _SUCCESS marker) that fails the run instead of passing it.

ApproachCatches the late file?Cost
glob oncenonone
re-list + partition overwriteyes, if re-run after a quiet periodduplicate work; needs an idempotent write
producer manifest or table commityes, by constructionproducer change

Failure modes worth naming: silent undercount with a green DAG, which is the common one; double-count if the re-run appends instead of overwriting the partition; and a manifest that is itself written before the last PUT, which just moves the race rather than closing it.

For scale, take hypothetical numbers: a 14-file gap at roughly 15,700 rows per file is about 220,000 rows missing from a daily partition. The kind of thing that surfaces a week later in a finance reconciliation rather than in the pipeline.

Curated: · Written: · Reviewed:

Q&A-4You turn on Debezium against a 14-year orders table. What must finish before streaming CDC is allowed to merge?

Core answer

Nothing merges until two things are true: the initial snapshot has been fully read and committed into the base table, and the connector has committed the offset it captured at the start of that snapshot. Debezium's default snapshot.mode=initial opens a repeatable-read transaction, records the binlog position (MySQL) or LSN (Postgres) at the start, emits every row of the table, then streams from that exact position. Snapshot and stream are stitched at one offset, so there is no gap by construction, but only if the merge waits for both halves.

Skip the snapshot (snapshot.mode=no_data or schema_only) and the connector streams from "now". Every row that never changes again is invisible forever. On a 14-year orders table that is most of the table.

Assumptions: MySQL 8.0 with binlog_format=ROW, or Postgres 14 with wal_level=logical; Debezium 2.x; target is a warehouse table merged on the primary key.

What has to finish

  1. Snapshot read: the connector reads all rows inside one transaction. On MySQL the default snapshot.locking.mode=minimal holds a global read lock only long enough to read binlog coordinates, not for the whole scan. On Postgres the snapshot is a repeatable-read transaction, and the replication slot is created before it starts.
  2. Snapshot emit: rows land in Kafka. The final snapshot record is marked source.snapshot = "last"; streaming records carry false. That marker is your completion signal.
  3. Load commit: the base-table load job commits all snapshot rows.
  4. Offset commit: the connector commits the offset recorded at snapshot start, and streaming resumes from it.

Merge is allowed only after 3 and 4. Merging after 2 but before 3 merges a partial base.

Snapshot modes

snapshot.modeBehaviourWhen it's right
initial (default)Full snapshot, then stream from the recorded offsetNew connector on a populated table
schema_only / no_dataNo rows, stream from nowTable is empty or already backfilled elsewhere
initial_onlySnapshot, then stopYou want the snapshot now, streaming later
when_neededSnapshot only if the offset is missing or invalidRecovery

Incremental snapshot: for a 14-year table, a monolithic initial snapshot can run for hours. Debezium's incremental snapshot chunks by primary key and needs no long lock: write an execute-snapshot signal to the debezium_signal table with {"type":"execute-snapshot","data":{"data-collection":"orders","type":"incremental"}}. It can also re-snapshot a table later without restarting the connector. The trade-off is more moving parts and a chunking key you must choose.

Failure modes

  • Streaming with no snapshot: unchanged rows never arrive. Detect by comparing a row count and a checksum over a stable column set between source and target.
  • Merge before the load commits: partial base, and deletes that happened during the snapshot window are lost. Gate the merge on the snapshot=last marker plus the load job's commit.
  • Long snapshot on Postgres: the replication slot holds WAL from the snapshot start LSN, so a multi-hour snapshot accumulates WAL on the primary. Watch pg_replication_slots lag and pg_stat_activity for the long transaction.
  • Long snapshot on MySQL: if snapshot.locking.mode is not minimal, the global read lock blocks writes for the whole scan.

Verification

You cannot re-query the source at the snapshot LSN: the source has moved on. Verify instead that the number of rows the snapshot emitted equals the number the load job wrote, and that the connector's committed offset equals the offset recorded at snapshot start. Then enable the merge.

Curated: · Written: · Reviewed:

Q&A-3Debezium emits 40,000 changes an hour. The warehouse also gets a 02:00 dump. How do you apply both without inventing a third history?

Assumptions: the warehouse table has a stable primary key, Debezium emits the key plus changed columns with a monotonic source offset (Postgres WAL LSN, or MySQL binlog file+position), and the 02:00 dump is taken from a consistent snapshot so it has one as-of point rather than a smear across the dump's runtime.

With those, there is one history: the CDC log. The dump is a snapshot of that log at a known offset. So:

  1. Load the dump as a snapshot replace (truncate-and-insert, or a MERGE tagged as a snapshot) and record the offset X it was consistent at.
  2. Apply CDC events with offset > X as idempotent upserts keyed by primary key. Drop events with offset <= X; they are already in the dump.

Getting X is the part people skip. On Postgres, run pg_dump inside a REPEATABLE READ transaction and read pg_current_wal_lsn() in that same transaction. On MySQL, mysqldump --single-transaction --master-data=2 writes the binlog file and position into the dump header. Debezium's own incremental snapshot does the same thing internally: it brackets each chunk with low and high watermark LSNs so the reader can tell which changes fall inside the snapshot and which came after.

The apply is a MERGE, not an insert:

MERGE INTO warehouse.customer AS t
USING staging.cdc_customer AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.lsn > t.last_lsn THEN UPDATE SET t.name = s.name, t.last_lsn = s.lsn
WHEN NOT MATCHED THEN INSERT (customer_id, name, last_lsn) VALUES (s.customer_id, s.name, s.lsn);

The s.lsn > t.last_lsn guard is what makes replays safe: Debezium retries, and a consumer can re-read a partition, so the same change can arrive twice. Storing last_lsn per row and only moving forward turns the second arrival into a no-op. Deletes arrive as delete events and must be applied as deletes; the 02:00 dump already reflects deletes up to 02:00, but a delete after X exists only in CDC.

Failure modes and how you see them:

  • Dump loaded as inserts onto the CDC table: row count doubles, distinct key count does not. That is the 9.4M → 18.8M signature.
  • CDC applied as a replace of the table: keys not touched in the last hour disappear, so row count drops below the dump's.
  • Dump with no recorded offset: you cannot decide which CDC events are already included, so you either double-apply or drop real changes. Detect it by replaying from the dump offset and comparing a checksum against the live table.
PathOperatorRowsDistinct customer_id
dump as insert onto CDC tableINSERT18,800,0009,400,000
dump as snapshot replaceTRUNCATE + INSERT9,400,0009,400,000
CDC apply of 40,000 changesMERGE by PK, offset > X9,400,000 ± net9,400,000 ± net

(Figures illustrative.)

To settle it, feed one dump and one CDC file into the loader in staging and require the two operators to be distinct, with row count matching the dump only on the snapshot path and the MERGE path idempotent when the same file is applied twice.

Curated: · Written: · Reviewed:

Q&A-71The source sets deleted_at. Your incremental copies WHERE updated_at > :cursor and never looks at deleted_at. What do you keep?

What you keep: retired rows, still marked current.

There are two defects here, and the first one is the one that bites.

The predicate updated_at > :cursor only sees rows whose updated_at moved. A soft delete writes deleted_at; whether updated_at moves depends on the application or a trigger. So:

  • Delete bumps updated_at: the row arrives, and if the merge never reads deleted_at, you copy it as a live row.
  • Delete does not bump updated_at: the row never arrives. No merge logic can fix a row that isn't in the stream. It stays live in the destination forever.

Trace it. User 42 has updated_at = 09:00; the cursor is at 09:30. At 10:00 the app sets deleted_at = 10:00 and leaves updated_at alone. The next run selects updated_at > 09:30, so row 42 is not selected. The destination keeps user 42 as current, and every later run does the same. The row is invisible to the pipeline.

So the fix belongs at the cursor, not only in the merge.

Fix the stream.

Option A: a second cursor on deleted_at:

SELECT id, updated_at, deleted_at
FROM users
WHERE updated_at > :u_cursor
   OR deleted_at > :d_cursor;

Needs an index on deleted_at, two cursors to persist, and an id tiebreak (or a sequence column) for rows sharing a timestamp, otherwise a row can slip between runs.

Option B: force deletes to stamp updated_at, via trigger or application code. One cursor, no change to the reader. Requires write access to the source, and it does not recover deletes already missed.

Option C: periodic reconciliation: anti-join source against destination on id and mark anything missing as deleted. Catches historical misses and any write path that bypasses the trigger. Cost is a full scan of both sides.

FixCatches no-bump deletesRecovers past missesCost
A: second cursoryesnoextra index, two cursors
B: trigger bumps updated_atyes, going forwardnosource write access
C: reconciliationyesyesfull scan

Deciding criteria: can you change the source? Is there a monotonic change-sequence column you could cursor on instead? How much drift can you tolerate before reconciliation runs?

Fix the merge too.

Even with the stream fixed, the merge has to treat deleted_at IS NOT NULL as a delete:

MERGE INTO dw.users AS t
USING staging AS s ON t.id = s.id
WHEN MATCHED AND s.deleted_at IS NOT NULL THEN DELETE
WHEN MATCHED THEN UPDATE SET t.email = s.email, t.updated_at = s.updated_at
WHEN NOT MATCHED AND s.deleted_at IS NULL THEN INSERT ...;

The NOT MATCHED AND deleted_at IS NULL guard stops you inserting a row that arrives already deleted.

Verify. Stamp deleted_at on a test row without touching updated_at, run the job, and assert the row is gone or is_current = false. Repeat with updated_at bumped. The first test is the one that fails today.

Curated: · Written: · Reviewed:

Q&A-70The source deletes a customer. Debezium emits a tombstone. Your MERGE only handles upserts. What remains?

The short answer: the curated row stays, with its last known values, and nothing in the pipeline will ever remove it. A tombstone is not a null upsert: it is a record carrying the key and a null value, emitted so Kafka compaction can purge that key's history. A MERGE with only WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT has no branch that can delete, so the delete is either ignored (stale row) or applied as an update of nulls (zombie row that still joins).

What Debezium actually emits. For a delete on a keyed table, Debezium writes two records, in order:

#recordkeyvalueop
1delete eventcust-7before-image (key columns only under REPLICA IDENTITY DEFAULT)d
2tombstonecust-7null—

The tombstone exists only for compaction: with cleanup.policy=compact on the topic, the null value lets the broker drop every earlier record for that key. tombstones.on.delete defaults to true. Compaction is not immediate: min.cleanable.dirty.ratio defaults to 0.5 and delete.retention.ms to 86400000 (24 h), so the tombstone stays readable for at least a day after compaction runs.

Where the delete is lost before your MERGE. If you use the ExtractNewRecordState SMT, delete.handling.mode defaults to drop: delete events and tombstones are stripped from the stream and the sink never sees them. rewrite turns the delete into a value with __deleted: true and drops the tombstone; none passes both through. Check this before blaming the MERGE.

The MERGE. The delete branch has to be driven by the key, because the tombstone carries no value to stage:

MERGE INTO curated.customer AS t
USING staged_customer AS s
   ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED AND s.op IN ('c','u') THEN UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED AND s.op IN ('c','u') THEN INSERT (customer_id, name, email)
  VALUES (s.customer_id, s.name, s.email);

The staging step must project customer_id from the tombstone and set op='d'; a value-based staging job drops null-valued records, so the tombstone never reaches the MERGE. If the sink is a Kafka Connect JDBC sink, delete.enabled defaults to false and pk.mode must be record_key: otherwise the connector cannot issue a DELETE at all.

What remains, concretely. The curated row, indefinitely. Then everything derived from it: aggregate tables (customer counts, revenue by segment) stay inflated, and any cache or feature store keyed on customer_id keeps serving the deleted customer. If the sink is offline longer than delete.retention.ms plus a compaction cycle, the tombstone is gone, and a restore from a pre-delete backup leaves the row with no way to learn about the delete.

Alternatives, and when not to hard delete. If the business needs history or audit, a soft delete (deleted_at timestamp, filtered out of the curated view) survives tombstone loss and is the safer default for regulated data. Hard DELETE is right when the row must actually disappear: GDPR erasure, PII.

Detection. Assert 0 rows for the key after the tombstone in a test, and run a reconciliation job comparing source row count to curated row count per table, alerting on drift. Labelled hypothetical: 10,000 deletes in a day with a dropped tombstone means 10,000 stale rows and every downstream count wrong by that much until someone reconciles.

Curated: · Written: · Reviewed:

Q&A-73Ops dropped a full dump in the CDC folder. The job applied it as inserts. What should have happened?

A full dump in the CDC folder should never have been applied as inserts. A CDC stream carries per-row change events (insert, update, delete) that the applier replays in order against a table that already reflects prior state. A dump is a snapshot of current state, not a set of changes. When the loader treats snapshot rows as inserts, it appends them to a table that already holds those keys: row count doubles, distinct keys stay flat. The loader should have identified the file as a snapshot from its header or filename contract and either replaced the table or merged on key, or refused it and paged a human.

Why the doubling is invisible to a naive check: the insert operator has no notion that a key already exists. It appends. So 11M existing rows plus 11M snapshot rows gives 22M rows over 11M distinct keys. A uniqueness test that samples 800 rows almost never draws the same key twice, so it passes. The signal is the ratio of rows to distinct keys, checked over the whole table, not a sample.

OperatorRowsDistinct keysSample unique
insert22,000,00011,000,000800 rows, pass
refuse or replace11,000,00011,000,000full

Now the choice between the two correct behaviors, because they are not interchangeable. Replace (truncate then load) is right when the snapshot is an authoritative, consistent cut and you can tolerate a short window where the table is empty or read-only. Merge/upsert on key is right when you cannot take that window, but it has a hole: upsert never deletes, so any row removed at the source since the snapshot survives in the target. Refuse is right when the pipeline cannot tell a snapshot from a change stream: better to stop and let a human classify the file than to guess. In this incident, refuse was the safe default because the loader had no mode contract at all.

Remediation for the table that is already doubled is not "delete the duplicates." The snapshot rows and the CDC rows that landed after them may have diverged, and the doubled table no longer carries reliable ordering. The defensible fix is to restore from the last known-good snapshot and replay CDC from the offset that snapshot corresponds to. Deduping in place is only acceptable if you can prove the snapshot rows are strictly older than every CDC row for the same key and keep the newest.

Detection to put in place: alert on row-count jumps over 2× between runs, and assert rows equals distinct keys on the primary key across the full table after every load. Both would have caught this before the job reported success.

To confirm the fix, drop a dump into the CDC path in staging and require the loader to refuse it or perform a snapshot replace: never an insert.

Curated: · Written: · Reviewed:

Q&A-72The OLTP primary's clock jumped back 4 minutes. Your extractor uses updated_at > last_run. What hole did you punch?

The hole is a silent gap. Every row committed while the clock was behind gets a timestamp older than the cursor you already advanced past, so updated_at > last_run never selects it. No error, no lag alert: the extractor looks healthy and those rows are simply gone until someone reconciles by hand.

Why the cursor can't see them. updated_at > last_run assumes the source clock is monotonic. A backward step breaks that: commits made after the step are stamped before it. The cursor is a high-water mark, not a set, so anything landing behind it is invisible rather than late. When NTP corrects the clock forward again, new commits stamp ahead of the cursor and are caught normally, which is what makes the gap permanent and quiet.

Worked trace (figures illustrative):

  • 12:04:00. Extractor runs, sets last_run = 12:04:00.
  • 12:05:00. NTP steps the clock back 4 minutes; it now reads 12:01:00.
  • 12:05:00–12:09:00 wall. 6,200 orders commit, stamped 12:01:00 through 12:05:00.
  • 12:09:00. Next run uses updated_at > 12:04:00. It picks up only the last minute of the window. Rows stamped 12:01:00–12:04:00 are behind the cursor and never selected.
  • Clock corrects forward; subsequent commits stamp 12:09:00+ and extract cleanly. The three-minute hole is now closed over.

At roughly $29 average order value, 6,200 orders is about $180,000 of revenue that never reached downstream until a manual reconcile. If the extractor also runs during the skew and derives last_run from the skewed clock, the hole grows to the full step.

CursorOrders missedValue
updated_at > last_run6,200~$180,000
LSN / binlog position0$0
updated_at > last_run - 10 min overlap0$0

What to do instead.

  • LSN or binlog position. Postgres logical replication slots, MySQL binlog coordinates, or a monotonic sequence column. These advance with the write, not the wall clock, so a step is irrelevant. Cost: a replication slot retains WAL. A stalled consumer fills the disk, so monitor slot lag and set max_slot_wal_keep_size.
  • Overlap window. Re-read the last N minutes where N exceeds the largest skew you've observed, not your poll interval. Cheap, no new infrastructure, but it only bounds the hole, a step larger than N still punches through, and it requires idempotent upserts downstream or you trade a gap for duplicates.
  • Alert on the step itself. chrony/NTP step events, or track max(updated_at) against now() per source. The step is the signal; the gap is the damage.

Clocks step; LSNs do not.

Curated: · Written: · Reviewed:

Q&A-53A sensor waits for s3://.../dt=today/_SUCCESS. The upstream wrote 0-byte _SUCCESS because the extract was empty. What do you load?

You load zero rows for dt=today. A 0-byte _SUCCESS is not a warning sign: every _SUCCESS file is zero bytes. Hadoop's FileOutputCommitter, and Spark on top of it, writes an empty marker into the partition directory when the task commits. The file's existence means the commit protocol finished, not that records were written. Spark writes _SUCCESS for an empty DataFrame too. So the sensor did exactly what it was built to do: the job ran and committed. The marker carries no row count, and it never did.

That is why the sensor fired. Airflow's S3KeySensor (amazon provider) pokes HeadObject on s3://.../dt={{ ds }}/_SUCCESS every poke_interval seconds until it gets a 200, then unblocks. It cannot tell an empty partition from a full one, because the object it is checking is empty in both cases.

So the load is zero rows, and the real question is whether zero is the right answer for that partition. There are two cases and they need different handling.

Legitimately empty. No events landed in that partition: a holiday, a new tenant before its first write, a backfill of a quiet day. Loading zero rows is correct, and failing here would page someone every night.

Broken extract. The source had rows and the extract returned none: a filter bug, a credential scoped to the wrong schema, an upstream query that timed out and wrote an empty result. Loading zero rows silently drops a day.

The marker cannot distinguish these, so you need a fact it does not carry. Two options:

  1. Row-count manifest. Upstream writes a count alongside the marker (or writes _SUCCESS only when count > 0 and a distinct _EMPTY marker otherwise). The loader reads the count, loads that many rows, and fails on mismatch. Precise and cheap at read time, but it couples the loader to upstream discipline and the count file has to land in the same commit.
  2. Downstream assertion against an independent source of truth. After the load, compare the destination count to a source count queried directly: a control table, or SELECT count(*) against the source. Fail when source > 0 and destination == 0. Works without upstream cooperation, but needs a second source the loader can reach, and it is only as authoritative as that source.

Either way the guard belongs in the load, not the sensor:

# Airflow 2.x task body; _SUCCESS is excluded from the data-file listing
def load_partition(ds):
    files = list_data_files(f"s3://bucket/dt={ds}/")  # skips _SUCCESS
    if not files and source_expected_nonempty(ds):
        raise ValueError(f"dt={ds}: _SUCCESS present but 0 data files")
    write(read_parquet(files) if files else [])
SituationSource rows_SUCCESSLoadedAction
Quiet partition0present, 0-byte0 rowspass
Broken extract3,400,000present, 0-byte0 rowsfail loudly

The failure mode to detect is the quiet one: sensor fires, load writes zero, nothing alerts. Emit loaded-row-count as a per-partition metric and alert on rows == 0 AND source_expected_nonempty. Separately, log when _SUCCESS appears but the partition directory holds zero data files: that combination is worth a line in the runbook even when it turns out to be legitimate.

Curated: · Written: · Reviewed:

Q&A-65Landing allows extra JSON fields. Curated is typed. Who is allowed to SELECT landing?

The roles that get SELECT on landing are the ones whose job is moving or auditing the raw data: the ingestion service account, the replay and backfill role, and the schema-drift monitor. Analysts, BI service accounts and feature pipelines read curated only. If any of those three can read landing, the contracts you built on curated stop meaning anything, because the consumers are not behind them.

Two assumptions I would state before answering. Landing is append-only and schema-on-read, and extra JSON fields are allowed there, so a field can appear or change type without a migration. Curated is typed, so the same change is a migration with a plan and a reviewer. That difference is the entire reason the grants differ, and it is why granting SELECT on landing is a decision about versioning, not about tidiness.

Why a SELECT leaks rather than just sitting there: reading a table is not passive. The first dashboard built on landing makes it a dependency, and a dependency with no version is a contract nobody signed. Adding a JSON key is safe for the producer and unsafe for the reader, because positional CSV exports shift a column, a SELECT * widens under a scheduled job that never changed, and a join key arriving as a string where yesterday it was a number fails at the reader, at 9am, in front of whoever owns the number.

The standing grants I would ship:

PrincipalSELECT on landingWhy
Ingest service accountYesReads the manifest or partition key back to dedupe and re-ingest idempotently
Replay / backfill roleYes, separate roleReads the same raw data, but backfills get their own audit trail, separate from ingestion
Schema-drift monitorYesOnly landing shows a new key or a changed type; curated rejects it before anyone sees it
Analyst, BI service accountNoReads curated; the typed contract is the interface they were promised
Feature or ML pipelineNoSilently drifting training data is worse than a visibly broken dashboard
Break-glass humanPer incident onlyNamed ticket, hours-long expiry, every query logged

Snowflake-shaped, since that is the usual warehouse in this conversation:

REVOKE SELECT ON ALL TABLES IN SCHEMA raw.landing FROM ROLE analyst;
REVOKE SELECT ON FUTURE TABLES IN SCHEMA raw.landing FROM ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA raw.landing TO ROLE svc_ingest;
GRANT SELECT ON ALL TABLES IN SCHEMA raw.landing TO ROLE svc_replay;

The future-tables line is the one teams forget. Without it, the next landing table is born readable by analysts and the hole reopens quietly, with no grant event for anyone to notice. Make the schema managed access as well, so an engineer who creates a table in landing cannot hand out SELECT on it personally. In BigQuery the same boundary is drawn with dataset-level IAM; the shape of the answer does not change.

How I would know it holds. SHOW GRANTS ON SCHEMA raw.landing; should list exactly the grantees above for SELECT. Then the test that actually counts: run a SELECT as the analyst role and require "insufficient privileges". An executed deny is evidence; a wiki page saying landing is restricted is not.

Failure modes worth naming. The object store is the big one: if the bucket behind landing is readable by the whole analytics org, the warehouse grants are decoration and the ACL has to be reviewed in the same breath. Break-glass is the second: a per-incident grant with no expiry is a standing grant six months later. The third is cost, since landing is typically the largest, least-clustered data you hold, so open analyst access fails on scan volume as well as on correctness.

The pressure you will actually get is latency, not philosophy. Curated lags, an analyst needs today's numbers, and someone asks for read on landing. Fixing the lag is the right answer. Second best is a read-only path to the raw files in a scratch environment with no scheduled queries and no dashboard behind it. A standing warehouse grant is the last option, and if you take it, make it expire, because that is the one nobody remembers to remove.

Landing is not a faster curated. It is an unversioned input, and its readers are the pipeline itself.

Curated: · Written: · Reviewed:

Q&A-41Raw landing is "temporary." Analysts still have s3:GetObject. What is the incident?

The incident is a data-exposure incident, not a storage-hygiene ticket. Two independent failures intersect: raw identifiers persisted in plaintext past their intended window, and a principal population with no need-to-know holds s3:GetObject on that prefix. Either alone is a finding. Together, the scope is "every object an analyst could have read since the grant was created," and you cannot narrow it until you have evidence.

First 60 seconds in an interview: say that, then say what you'd do before arguing about severity. Deny the read, then measure.

Why "temporary" is not a control. S3 has no TTL. A lifecycle rule is a filter plus an action, and both can miss:

  • Expiration is asynchronous. AWS queues objects for removal and deletes them in the background, typically within about a day of the expiration date, not at midnight on it. "Expire after 7 days" gives you a window, not a deadline.
  • On a versioned bucket, expiration writes a delete marker. The current version is gone; noncurrent versions are not, and they stay readable to anyone holding s3:GetObjectVersion.
  • The rule matches on prefix or tag. If the ingest job writes raw/dt=.../ and the rule filters landing/, nothing expires, ever, and no alarm fires because nothing failed.

So dwell time is unknown by construction. That is the part that turns a hygiene issue into an incident.

The access half. s3:GetObject without s3:ListBucket still means full read of any key you can name, and landing keys are predictable, raw/dt=2026-01-14/part-0007.parquet, and they leak through job configs, error messages and CloudTrail. "They can't list" is not a mitigation.

Trace an analyst read against the current policy set:

  1. Identity policy on role/AnalystReadOnly: Allow s3:GetObject on arn:aws:s3:::acme-landing/* → allow.
  2. Bucket policy: no statement matching that principal → no explicit deny.
  3. SCP: no deny for this bucket → no explicit deny.
  4. Result: allow. Bytes returned.

Now add one statement to the bucket policy: Effect: Deny, Principal: arn:aws:iam::111122223333:role/AnalystReadOnly, Action: s3:GetObject, Resource: arn:aws:s3:::acme-landing/raw/*. An explicit deny beats every allow in the evaluation chain, including the account root, and it survives someone editing the identity policy. The same call now returns AccessDenied.

StatePlaintext ids in landingRead grantDwell timeRecords in scope
todayyesanalystsunknown, ≥ lifecycle lagunknown, bounded by grant age
tokenise on ingest + deny GetObject0pipeline roles onlyirrelevant0

Failure modes and how you'd see them.

  • Lifecycle prefix/tag mismatch → objects never expire. Detect: S3 Inventory, or a list-objects-v2 count of objects older than the intended window.
  • Versioning on → noncurrent versions survive expiration. Detect: list-object-versions, count noncurrent.
  • Tokenisation sits downstream of the landing write, so raw is plaintext by design. Detect: Macie finding, or your own classifier over the prefix. Macie's automated discovery samples objects rather than reading all of them, so a clean finding is not proof: run a full scan before closing.
  • Analysts read the prefix for debugging. Detect: CloudTrail data events for GetObject, grouped by principal. Data events are off by default and billed separately, which is why most teams discover this during the incident rather than before it.

Alternatives, and when plaintext is fine. Tokenise on ingest with a deterministic HMAC if downstream joins need stable keys, but deterministic tokens leak equality and frequency, so use format-preserving encryption or a keyed token with a per-tenant salt when that matters. Or keep landing plaintext and lock it to pipeline roles with a VPC endpoint condition and a KMS key policy, which is defensible if the data is genuinely transient and you can prove dwell time. If identifiers are already tokenised upstream, this is an access-hygiene finding, not a breach, but you still can't close it without a classifier count of zero.

What closes it. Analyst GetObject on the prefix returns AccessDenied; a classifier run over the whole prefix returns zero plaintext identifiers; CloudTrail data events for the grant window are reviewed and the non-pipeline principals that read it are enumerated. Until those three exist, the honest answer to "how many records" is "unknown", and where GDPR applies, the 72-hour clock in Article 33 started when you became aware, not when you finished counting.

Curated: · Written: · Reviewed:

Q&A-79You hash emails in curated but landing keeps plaintext so "we can debug." Who is the processor of record?

Direct answer

"Processor of record" is a contractual role, not a property of the data. Under GDPR Art. 28, the processor is the entity that processes personal data on behalf of the controller, and it is the entity named as processor in the Data Processing Agreement. So the processor of record is whoever signed the DPA as processor for that pipeline: typically the vendor or platform team operating the landing zone. Hashing emails in curated does not change that designation, and keeping plaintext in landing does not create a second processor. What plaintext does is widen the scope of processing the existing processor performs, and it can flip that processor into a controller for the debug use.

Assumptions: GDPR/UK GDPR framing; the pipeline carries one controller's data; landing is operated either by the controller's own team or by a vendor under a DPA.

Why hashing in curated is a red herring

Hashing is pseudonymisation, not anonymisation. Recital 26 is explicit: pseudonymised data that can be re-linked to an individual is still personal data. The curated layer is therefore in scope too: the hash does not take the pipeline out of the DPA. Who the processor is gets answered by the contract, and the contract covers the whole pipeline, not just the layer where plaintext happens to sit.

What plaintext in landing actually changes

LayerContractual roleWhat plaintext changes
LandingProcessor (or controller, if in-house)Processing now includes raw identifiers; minimisation (Art. 5(1)(c)) and security (Art. 32) obligations bite here
CuratedSame processorNothing: pseudonymised data is still personal data
Debug accessProcessor, unless it serves the processor's own purposesIf the processor debugs its own systems with the data, Art. 28(10) makes it a controller for that processing

The last row decides most real disputes. A processor may act only on documented instructions (Art. 28(3)(a)). "We can debug" is a purpose. If that purpose is the controller's, diagnosing the controller's pipeline, it stays inside the processor role. If it is the processor's own (tuning its own infrastructure, training its own models) the processor becomes a controller for that processing and needs its own lawful basis.

Failure modes

  • The processor uses plaintext for its own purposes without a lawful basis: Art. 28(10) reclassification, and the DPA is breached.
  • A breach in landing triggers Art. 33 notification within 72 hours; plaintext raises the severity and the number of data subjects to notify.
  • Sub-processor chain: if landing is a cloud warehouse, that vendor is a sub-processor and needs authorisation under Art. 28(2). Plaintext in a sub-processor's store is the sub-processor's processing too.

Alternatives and when they suffice

  • Encrypted-at-rest landing with the key held by the controller: the processor stores ciphertext it cannot read. This keeps the processor role clean and is often enough when the debug need is the controller's own.
  • Tokenisation with a break-glass: reverse lookup is possible but audited and time-boxed. Good when debugging genuinely needs the identifier occasionally.
  • Contractual controls alone (DPA clauses restricting debug use): weakest, because it relies on the processor's behaviour rather than removing the data.

Decision

Name the processor from the DPA, not from the schema. Then fix the schema so the processor never holds plaintext it does not need: encrypt or tokenise at ingest, and make any reverse lookup a logged, controller-authorised break-glass. If the processor wants plaintext for its own debugging, that is a controller decision and needs its own basis.

Curated: · Written: · Reviewed:

Q&A-42DELETE FROM customers WHERE subject_id=? returned 1. Why can legal still find the email?

The 90-second answer. DELETE removed the row from the table's current state. It did not remove the bytes. In a lakehouse, a delete is a copy-on-write rewrite: the engine writes a new data file without that row and commits a new snapshot, but the old data file is still on object storage and the previous snapshot still points at it. Legal is reading the old snapshot, or an orphan file that no snapshot references, and the email is still in the Parquet.

Assumptions: an Iceberg or Delta table on S3, GCS or ADLS, time travel enabled, retention at defaults. If the table is plain Parquet overwritten in place with no versioning, the old file is gone and the only question is whether a copy exists elsewhere.

The trace. Take a 1 TB table and subject 42.

  1. DELETE FROM customers WHERE subject_id = 42 reads the data file holding subject 42, writes a replacement without that row, and commits snapshot 442. Snapshot 441 still lists the old file.
  2. SELECT email FROM customers VERSION AS OF 441 WHERE subject_id = 42 returns the email. Delta's VERSION AS OF and Iceberg's VERSION AS OF / FOR SYSTEM_TIME AS OF read the old file list, not the current one.
  3. A failed compaction or aborted write leaves files no snapshot references. Until VACUUM (Delta) or remove_orphan_files (Iceberg) runs, anyone with bucket read access can open them.

Both defaults are measured in days, not zero: Delta's VACUUM retains 7 days, Iceberg's history.expire.max-snapshot-age-ms is 5 days. Until you change them, the email stays queryable for at least that long after the delete. And the lake is rarely the only copy: a CDC stream, a replica or a backup can hold the row independently of any snapshot.

StepEmail still readable?
DELETE onlyYes: previous snapshot still lists the old file
DELETE + expire snapshots (Iceberg)Yes: orphan files remain
DELETE + expire + remove_orphan_filesNo
DELETE + VACUUM at zero retention (Delta)No

A representative failure: the delete committed, the weekly expiry job failed twice, and snapshot 441 served the email for 11 days while orphan files held seven prior versions.

Alternatives, and when the full pipeline is overkill. Hard delete (rewrite without the identifiers, expire snapshots, remove orphans) is correct and expensive: dropping one subject from a 10 TB table means rewriting every file that contains them, so a subject spread across 200 partitions costs 200 partition rewrites. Crypto-shredding is cheaper when the same subject's data is spread across many tables and immutable stores: encrypt each subject's fields under a per-subject key in a KMS and erase by deleting the key. It is fast and it covers backups and object-lock buckets, but it has to be designed in before the plaintext is written, and it moves the risk to key management. If the table has no time travel and no orphan files, DELETE alone is enough: the pipeline exists to cover the retention you actually configured, not as ritual.

How I'd prove it. After the erasure job, VERSION AS OF 441 must fail as expired, not return masked rows, and a decode of the remaining data files must show zero copies of the email. Listing object keys proves nothing about contents: that is exactly how the earlier sign-off went wrong: fewer keys, same email in the Parquet.

Curated: · Written: · Reviewed:

Q&A-50The vendor file grows new columns every quarter without notice. Your Spark job selects * into a typed table. What happens in April?

What happens in April

The new column lands in the vendor file. Whether it reaches your table is decided by two independent schema decisions, and SELECT * hides both.

Read side. spark.read.parquet (and csv/json) infers the DataFrame schema from the files. With spark.sql.parquet.mergeSchema=false, the default, Spark takes the schema from one file: the Parquet summary file if the writer left one, otherwise the first file it lists. A column that exists only in the newer files is not in the DataFrame. Set mergeSchema=true on the read and the DataFrame gains discount_bps, at the cost of reading the footer of every file in the directory instead of one.

Write side. The typed table has a fixed schema.

  • df.write.mode("append").saveAsTable("t") matches by name and silently ignores DataFrame columns the table lacks. The new column is dropped, no error.
  • df.write.insertInto("t") matches by position and requires equal column counts, so the extra column throws AnalysisException on a column-count mismatch.

So: silent drop (append/saveAsTable) or hard failure (insertInto). The silent drop is the dangerous one: the job stays green and the column is gone.

Why it behaves this way

Spark resolves SELECT * against the file schema at read time, then reconciles the write against the sink schema. Nothing checks that the two agree, so a mismatch is either ignored or surfaced as a column-count error, never as "the vendor changed."

When schema evolution is the right call

If the column is wanted and the sink supports it: Delta needs mergeSchema=true on the write (or spark.databricks.delta.schema.autoMerge.enabled=true); Iceberg's Spark writer can add new top-level columns when the write is configured to accept the evolved schema. Check your writer's setting for your version. Correct when vendor additions are additive and wanted downstream. Wrong when the vendor adds columns you must not expose (PII, internal fields) or when consumers break on any schema change.

The alternative: named projection plus a remainder

Project the columns you know and land everything else in a map<string,string> remainder column with a size quota. Cost: extra storage per row, and every consumer that needs a new column must be updated to read it out of the remainder. Benefit: nothing is silently lost, and remainder growth is a signal you can alert on.

Detection

Alert on remainder size growth, or diff the file's inferred schema against the table schema each run. A fixture test that adds a column and asserts it appears in the remainder (or in a versioned schema) catches the regression before April does.

SetupNew column in AprilSignal
read mergeSchema off, append/saveAsTablesilently droppednone: job green
read mergeSchema off, insertIntojob fails, column-count mismatchAnalysisException
read mergeSchema on, Delta mergeSchema=truecolumn added to tableschema change
named projection + remaindercolumn lands in remainderremainder growth alert

A worked illustration (hypothetical figures): the vendor adds one column per quarter, so four a year. April adds discount_bps. A SELECT * job with a frozen schema and append mode drops it; margin reports ignore the discounts for seven weeks, and the gap is only found when someone reconciles totals by hand. The same file with named projection plus a remainder keeps the column and fires a remainder-growth alert on the first run.

Key points the interviewer is scoring

  • Names the two independent decisions: read-side inference and write-side reconciliation.
  • States the default (spark.sql.parquet.mergeSchema=false) and what it does.
  • Distinguishes saveAsTable (by name, drops extras) from insertInto (by position, throws).
  • Says which failure is worse and why: the silent drop, because the job stays green.
  • Offers a mitigation with its cost.

Weak answers

  • "Spark handles schema evolution automatically." It doesn't; the two sides are independent.
  • "It'll throw an error." Only insertInto does; append is silent.
  • "Just set mergeSchema=true." That fixes the read, not the write: the table still drops the column.
  • "Add the column to the table." You don't control the vendor's cadence; you need detection, not a one-time fix.

Follow-ups

  • "How do you know it happened?" Schema diff per run, or a remainder-growth alert.
  • "What if the column is PII?" Don't auto-merge; quarantine the remainder and gate promotion.
  • "Cost of mergeSchema=true on read?" One footer read per file; on a 10,000-file directory that's 10,000 metadata reads instead of one.
  • "Delta vs Iceberg?" Delta needs the write option; Iceberg's behavior depends on the writer's schema-evolution setting.

Curated: · Written: · Reviewed:

Q&A-20The producer bumped the Avro subject. The Spark job uses a frozen schema file in Git. Who wins?

The bytes win. The registry subject is the writer schema; the Git file is only a reader. Avro resolution runs one way: the reader projects onto the writer's data, never the reverse. The producer's bump is already in the bytes on the topic. Your frozen Git file decides what you can see of it, and a stale reader loses fields silently rather than failing.

Mechanism: the Schema Registry wire format is a 5-byte header (magic byte 0x0, then a 4-byte big-endian schema ID) followed by the Avro binary payload. The writer schema is not in the payload; you fetch it by ID. Spark's from_avro takes a reader schema string, and whatever you pass is what resolution uses.

The trap is compatibility mode. The registry's default is BACKWARD: a new schema can read data written by the previous one. That protects consumers that upgrade. Your Spark job is the opposite case: an old reader reading new data, which is FORWARD compatibility. A producer bump that passes the registry's default check can still break you, and the registry reports success.

# PySpark 3.5, confluent-kafka 2.3
from confluent_kafka.schema_registry import SchemaRegistryClient

sr = SchemaRegistryClient({"url": "http://registry:8081"})
_cache = {}

def writer_schema(payload: bytes) -> str:
    if payload[0] != 0x0:
        raise ValueError("not a Schema Registry payload")
    sid = int.from_bytes(payload[1:5], "big")
    if sid not in _cache:          # one fetch per schema ID, not per row
        _cache[sid] = sr.get_schema(sid).schema_str
    return _cache[sid]

Resolution outcomes, all silent unless you check:

WriterReaderResult
has fieldlacks itfield dropped from the decoded record
lacks fieldhas it, with defaultdefault fabricated for every row
lacks fieldhas it, no defaultresolution error, job fails
has fieldhas itdecoded

Worked trace. Payload header 00 00 00 00 2a → magic 0x0, schema ID 42. GET /schemas/ids/42 returns writer v6: user_id (long), event (string), ts (long). The Git reader is v5, which also carries channel (string, default ""). channel is in the reader and not the writer, so Avro fills the reader default for every record. 3.6M rows decode with channel = "". No exception, no warning, no null.

That is the failure I have seen: the Git file lagged the subject by 4 versions, channel came back "" for 3.6M records, and attribution was wrong for 9 days before anyone compared a producer-side sample.

Detection: log the schema ID per batch and alert when it changes; assert the decoded DataFrame's schema equals the expected one before writing; count nulls and empty strings on fields you depend on. A schema ID change with no code change is the signal.

What to do: don't pin the Git file and hope. Fetch the writer schema by ID from the wire header and use it as the reader, or pin a subject version and run the registry's compatibility check against the oldest files you still replay. Fail the job on decode error rather than writing defaulted columns. The registry is the job's reader, not a wiki.

Curated: · Written: · Reviewed:

Q&A-21You add country to the Avro reader so old files still decode. Where does the default belong?

The default belongs on the reader schema, the schema the consumer resolves against, not on the writer schema alone.

Avro resolves each record by walking the reader's fields. For each reader field it looks for a matching field in the writer's schema. If the writer has it, the value is copied. If the writer doesn't, exactly the case for files written before country existed, the reader falls back to that field's default in the reader schema. No default on the reader field means resolution fails and the file fails to decode. A writer-side default is never consulted on that path: it only applies when that writer writes a record and omits the field.

So add country to the reader schema with a default, and the old files decode:

{"type": "record", "name": "Event", "fields": [
  {"name": "id",      "type": "string"},
  {"name": "country", "type": "string", "default": "UNSET"}
]}

Trace it. Writer schema is {id: string} only. The reader walks its two fields: id matches, value copied; country has no writer counterpart, so the reader default "UNSET" is used. Every historical record decodes with country = "UNSET". Drop the default and the same read throws an AvroTypeException naming the missing field: one unresolvable field fails the record, and a Spark job fails the partition.

Two Avro details that bite here. A default on a union field must match the first branch: {"name":"country","type":["null","string"],"default":null} is legal, "default":"UNSET" on that union is not. And aliases solve renames, not additions: an alias maps a writer field name onto a reader field name, so it does nothing for a field the writer never wrote. Also note the default only fires when the writer's schema lacks the field; a writer that has country set to null gives you null, not "UNSET".

Alternatives, and when each wins:

ApproachCostWhen it's right
Reader defaultEvery consumer schema changes; sentinel flows downstreamMany readers, history too large to rewrite, field genuinely optional
Backfill / rewrite historyOne-time rewrite of every file; no reader changeSmall history, or downstream needs real values, not a sentinel
Writer-side defaultAffects only newly written recordsYou control the producer and want the value in the data itself
UNSET + filter downstreamSentinel leaks into aggregates until every consumer filtersYou want the gap visible and auditable rather than silently zero

The reader default is the cheap move. Backfill is the correct one when downstream logic can't tolerate a sentinel. Writer-side default is right when the producer is yours and the field should be populated going forward: it does not rescue the historical read.

Worked example (figures illustrative): country added as a required reader field with no default. Replay of 11 days: 5.2 million files: failed at deserialize. A retry path fell back to writing JSON in parallel and duplicated 310,000 facts before anyone noticed. Detection is cheap: point the new reader at a pinned historical file and assert decode success only when the reader default is present, and alert on AvroTypeException rate rather than on job success.

Curated: · Written: · Reviewed:

Q&A-22A producer reuses Protobuf field 4 for a new string because the old int32 died. What does the Spark job decode from old files?

What the job decodes

For every row written by the old producer, sku is the empty string. Nothing throws. The old amount_cents value is not decoded into any column: it is skipped as an unknown field. If the job still references amount_cents, it fails before reading a row, with UNRESOLVED_COLUMN, because the new descriptor has no such field.

Why: wire type, not field number, decides

A Protobuf tag byte is (field_number << 3) | wire_type. int32 is wire type 0 (varint); string is wire type 2 (length-delimited). The parser reads the tag, gets field 4, looks field 4 up in the descriptor, sees wire type 2, and the tag said 0: mismatch. It does not reinterpret. It treats the whole field as unknown, skips it by wire type, and moves on. sku is never assigned, so it takes the string default, "".

Trace, old file with amount_cents = 2500:

  • tag: (4 << 3) | 0 = 0x20
  • value: varint 2500 = 0xC4 0x13
  • bytes on disk: 20 C4 13

New descriptor: field 4 is string. Parser reads 0x20 → field 4, wire type 0. Descriptor says wire type 2. It skips the varint (C4 13) and stores it as unknown. sku = "".

Spark's from_protobuf (3.4+) builds the DataFrame schema from the descriptor file you hand it and never compares that descriptor to the bytes. Unknown fields have no column, so they are dropped from the row.

Reuse is only silent when the wire type matches

Old field 4New field 4Wire typesOld value 2500 decodes as
int32string0 → 2unknown field; sku = ""
int32int640 → 02500
int32bool0 → 0true
int32sint320 → 01250 (zigzag)
int32enum0 → 0enum value 2500, likely undefined

The middle rows are the dangerous ones: no error, no empty string, just a wrong number that looks plausible. int32 → sint32 halves it; int32 → bool collapses every non-zero value to true.

Failure modes and how they show up

  • Silent empty strings. Row counts, null checks and schema checks all pass. groupBy("sku") shows one giant "" bucket. Revenue aggregates read $0.
  • Column-not-found. If the job still selects amount_cents, it fails at analysis time: loud, and the better outcome.
  • Round-trip loss. Protobuf parsers preserve unknown fields and re-serialize them, so a read-modify-write through the new schema keeps the old bytes on disk. from_protobuf does not expose them, so the DataFrame can neither see nor rewrite them.
  • The reverse is symmetric: a new string file read by an old int32 reader gives amount_cents = 0.

What to do

reserved 4; in the .proto, and give the new string a fresh number. Add a CI gate that decodes a checked-in golden file written by the old producer with the current descriptor and asserts the expected values; a buf breaking check against the previous descriptor catches the reuse before the job is scheduled. The old bytes are not destroyed, a reader with the old descriptor still gets amount_cents = 2500, but no reader using the new descriptor ever will.

Curated: · Written: · Reviewed:

Q&A-38A new optional nested field appears in 8 percent of JSON. Spark inferred the schema from the first file. What happens to the rest?

The 8% of records that contain the new nested field will have that field silently dropped. Spark's JSON reader parses every record against the schema it inferred from the first file; any field not in that schema is ignored. The resulting DataFrame has no column for the new field, and any table you write from it won't either.

Why: Spark's JSON reader is schema-on-read. When you don't supply a schema, it infers one by reading a sample of the input: by default the entire dataset for spark.read.json (samplingRatio 1.0), but if you point it at a single file or set a low sampling ratio, that sample becomes the contract. The reader then applies that fixed schema to all files. For JSON, there is no mergeSchema option like Parquet has; unknown fields are simply not projected. So the 8% of records with the new field lose it.

Here's a minimal PySpark 3.5 trace:

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("schema-drift").getOrCreate()

file1 = '{"id": 1, "payment": {"amount": 100}}'
file2 = '{"id": 2, "payment": {"amount": 200, "network": "visa"}}'

# Infer schema from file1 only
schema = spark.read.json(spark.sparkContext.parallelize([file1])).schema
df = spark.read.schema(schema).json(spark.sparkContext.parallelize([file1, file2]))

df.printSchema()
# root
#  |-- id: long (nullable = true)
#  |-- payment: struct (nullable = true)
#  |    |-- amount: long (nullable = true)

df.collect()
# [Row(id=1, payment=Row(amount=100)), Row(id=2, payment=Row(amount=200))]

The network field is gone from row 2. No error, no warning.

What to do instead:

  • Declare the schema explicitly, including the new field as nullable. That makes the contract visible and forces a conscious decision when the field appears.
  • Use a schema registry with backward-compatible evolution: adding an optional field is a compatible change, and the registry tells consumers to expect it.
  • If you must infer, set samplingRatio to 1.0 (or a high value) so all files are sampled. But that still doesn't guarantee you'll catch a field that appears in 8% of files if the sample is random; it's a mitigation, not a contract.
  • Read the raw JSON as text and parse with from_json using a permissive schema (e.g., a MapType or a struct that includes the field). That preserves unknown fields for later inspection.

Failure mode: silent data loss. The job succeeds, row counts match, but a column is missing. Downstream aggregations that rely on that field are wrong or null. Detection: add a schema validation step that compares the inferred schema of new files against the expected table schema, or check for nulls in columns that should be populated. In a payments pipeline I've seen, first-file inference omitted payment.network. 8% of files had it. The column never appeared in the table. Card mix reports were wrong for 6 days (410,000 rows) while Spark jobs succeeded. The fix was a declared schema with the field nullable, plus a validation check that failed the job if the field was missing from the source.

The first file is not the contract. Treat schema inference as a convenience, not a guarantee.

Curated: · Written: · Reviewed:

Q&A-63A dbt snapshot uses check strategy on 3 columns. The source also changes a 4th. What history do you have?

The 60-second answer

You have version history for the three columns in check_cols and nothing for the fourth. A check snapshot compares only the listed columns of the incoming source row against the currently-open snapshot row for that key: the one with dbt_valid_to is null. If none of them differ, dbt writes nothing: no new row, no dbt_valid_to set, no record that the fourth column moved. The snapshot's open row keeps the old value of that column indefinitely, and the table looks healthy while being wrong. If you need that column's history, add it to check_cols, use check_cols='all', or switch to timestamp strategy with an updated_at you actually trust.

How check strategy decides

Per key, per run: fetch the open snapshot row, compare each column in check_cols with a null-safe comparison (a null and a value count as different). Any difference → close the open row by setting its dbt_valid_to to the new row's dbt_valid_from, then insert a new row with dbt_valid_from = run time and dbt_valid_to = null. No difference → no write. Columns outside the list are never read into the comparison.

ColumnIn check_colsSource changesSnapshot rows written
amountyes100 → 1201 new version, old row closed
statusnopending → shipped0

Failure modes

  • Silent type-1 drift. The unlisted column behaves as type-1 whether you decided that or not. Downstream models reading the snapshot's open row get a stale value with no timestamp to detect it.
  • Change and revert between runs. Snapshots only see state at run time. A column that goes A → B → A between two runs produces no row even when it is in check_cols.
  • Hard deletes. A deleted source row leaves the snapshot row open unless invalidate_hard_deletes is enabled.
  • check_cols='all' is not free. Adding a column to the source makes every open row differ on the next run, versioning the whole table once. Cheap to write, expensive to explain.

Detecting it

An audit model that joins the snapshot's open row to the source on the key and flags any unlisted column that differs:

-- Postgres syntax; use your warehouse's null-safe comparison
select s.order_id,
       s.status   as snapshot_status,
       src.status as source_status
from {{ ref('orders_snapshot') }} s
join {{ source('app', 'orders') }} src using (order_id)
where s.dbt_valid_to is null
  and s.status is distinct from src.status

Non-zero rows mean the snapshot is not tracking that column. Run it as a dbt test with severity: warn if you have accepted the drift, error if you have not.

Choosing

timestamp is cheaper, one column compared instead of N, and correct when updated_at is bumped on every write, including backfills and bulk updates. It fails the same way when it is not. check is the honest choice when you cannot trust the timestamp, at the cost of a wider comparison and the check_cols='all' trap above. Either way, which columns need history is a modelling decision, not a config default.

Curated: · Written: · Reviewed:

Q&A-39How does a pipeline apply type 2 without rewriting the whole dimension every night?

Core answer (first 90 seconds)

Assume the dimension is SCD2: a surrogate key, a natural/business key, valid_from, valid_to, is_current, and facts that join on the surrogate. The nightly job must leave members whose tracked attributes didn't change exactly as they are.

You don't rewrite the dimension. You expire the current row of each changed member and insert one successor, in the same transaction, and touch nothing else. Two statements do it: an UPDATE that closes changed current rows, then an INSERT that opens a current row for every member that now has none, which is exactly the changed members plus brand-new keys. The natural key is the join key; the surrogate is assigned once per version and never reused.

The two statements

-- 1. close the current version of every changed member
UPDATE dim_customer d
SET valid_to = :batch_date, is_current = false
FROM stage_customer s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND (d.name, d.email, d.tier) IS DISTINCT FROM (s.name, s.email, s.tier);

-- 2. open a new version for every member with no current row
INSERT INTO dim_customer (customer_id, name, email, tier,
                          valid_from, valid_to, is_current, version)
SELECT s.customer_id, s.name, s.email, s.tier,
       :batch_date, DATE '9999-12-31', true,
       COALESCE(m.max_version, 0) + 1
FROM stage_customer s
LEFT JOIN dim_customer cur
       ON cur.customer_id = s.customer_id AND cur.is_current
LEFT JOIN (SELECT customer_id, MAX(version) AS max_version
           FROM dim_customer GROUP BY customer_id) m
       ON m.customer_id = s.customer_id
WHERE cur.customer_id IS NULL;

Step 2's WHERE cur.customer_id IS NULL is the whole trick: after step 1 the changed members have no current row, so they qualify alongside brand-new keys. Unchanged members still have a current row and are skipped. IS DISTINCT FROM treats NULLs as comparable values, so a NULL→value change counts as a change. Postgres syntax; Snowflake and BigQuery take the same shape with their own UPDATE/MERGE forms.

Why the single-MERGE version bites

A MERGE whose only action is WHEN MATCHED THEN UPDATE SET valid_to = ... expires the changed rows and inserts nothing for them: matched keys take the update branch, and WHEN NOT MATCHED THEN INSERT fires only for natural keys absent from the target. The brand-new ones. The successors never land. You can fix it in one MERGE by staging two source rows per changed member (an expire image and a new-current image) and making the ON clause match only the expire image, but that's engine-sensitive, BigQuery rejects multiple source rows matching one target row, so the two-statement form is the portable one.

Worked example (figures from an incident, not a benchmark): a nightly MERGE expired 18,000 changed current rows and inserted 210 brand-new customers. The 18,000 successors never landed, so those members had no current row. The on-call reloaded the dimension and reassigned surrogates to all 4.1M members; facts still carried the old surrogates, and 96% of fact rows joined unknown for two days. The expire-only MERGE was the bug; the reload turned an 18,000-row gap into a full outage.

Approaches

JobSuccessors for 18,000 changesFacts joining unknown
MERGE expire only0, not-matched never fires18,000 members have no current row
expire + insert (above)18,000 new versions0
full nightly reload4.1M new surrogates96% for 2 days

Alternatives and when not to use this

  • Full reload: simplest, but reassigns surrogates unless you re-key on the natural key, and rewrites 4.1M rows to change 18,000. Fine for small dimensions (under ~100k rows) where the rewrite is cheap and facts join on the natural key.
  • Hash compare: add a row_hash column over the tracked attributes and compare hashes instead of column-by-column. Cheaper to maintain as attributes change; costs a column and a recompute.
  • CDC from the source: if the source emits change records, you process only actual changes and skip the full stage compare. Needs a reliable log and ordering.

How you'd know it worked

Change exactly one member, rerun the job, and assert: that member has two versions with the old row's valid_to equal to the new row's valid_from; the other 4,099,999 members keep their surrogate and valid_from; and SELECT customer_id, COUNT(*) FROM dim_customer WHERE is_current GROUP BY 1 HAVING COUNT(*) <> 1 returns nothing. Add a fact-join coverage check, the share of yesterday's facts that resolve to a current dimension row, as a nightly gate.

Curated: · Written: · Reviewed:

Q&A-40Email corrections must land tonight. Why is a type-1 MERGE still a pipeline decision?

Assumptions first. The email column is type-1: we want the current value and no history. The correction arrives tonight as a delta feed of customer IDs and new addresses. A nightly batch joins facts to the customer dimension on the current key, and bonus and attribution run off that join.

The answer. The MERGE itself is trivial: a few lines that overwrite the current email. It is a pipeline decision because the overwrite changes which facts join the dimension, and every downstream number computed from that join has to be recomputed. The decision is not "how do I write the email"; it is "which facts reread, on which run, and what else gets dragged along."

Why it is not just an UPDATE. A type-1 change does not version, so on its own it restates nothing in the dimension. But the moment the same statement carries a type-2 attribute (region, tier, segment) that attribute gets a new version row, and every fact still pointing at the old version now points at history. You fixed an email and restated fourteen months of region.

Hypothetical worked example: 40,000 corrected customers, roughly 12 facts each, so about 480,000 facts reread either way. The difference is what they reread.

StatementDim rows writtenFacts rereadRegion history
type-1 email only40,000 overwritten in place480,000, same regionuntouched
email + region in one UPDATE40,000 new versions480,000, now on the old versionrestated 14 months

In the second row, bonus recomputed off the restated region moved $3.2M between regions while the source had zero region changes. That is the failure the pipeline decision exists to prevent.

How you detect it. Count dim rows written and facts reread. A type-1 email fix should write one row per corrected customer and reread only facts whose join key changed. If the dimension's version count grows, you bundled a type-2 attribute. Alert on version growth during a correction run.

What to do. Split the correction into its own MERGE on the type-1 columns, drive it off the delta feed rather than the full extract so the blast radius is bounded and the run is idempotent, and log the fact count that will reread. Keep type-2 attributes on their own path.

One UPDATE is not one SCD type.

Curated: · Written: · Reviewed:

Q&A-62incremental unique_key is order_id. The source emits two lines per order. What does the incremental merge do?

What the merge does

It does not collapse the two lines into one order. unique_key = order_id tells dbt the merge grain is one row per order, and your source breaks that assumption. The result is either a failed run or duplicate rows in the target: never a clean single row per order.

What dbt actually runs

With the default merge strategy (Snowflake, BigQuery, Databricks), dbt builds a temp relation from the new rows and runs a statement shaped like:

merge into analytics.orders as tgt
using (select * from <new batch>) as src
on tgt.order_id = src.order_id
when matched then update set ...
when not matched then insert (...)

The on clause is the unique_key. Two source rows with the same order_id are two rows on the src side of that join, and the merge is defined only when each target row matches at most one source row.

What each engine does with two src rows per key

EngineTarget already has the order_idTarget has no match
Snowflakeerror, "duplicate row detected during DML action"both rows inserted
BigQueryerror, MERGE must match at most one source row per target rowboth rows inserted
Databricks (Delta)error, multiple source rows matched the same target rowboth rows inserted
Postgres 15+error, MERGE cannot affect row a second timeboth rows inserted
Redshifterror, "duplicate row detected during DML action"both rows inserted

The right-hand column is the one people miss. On the first incremental run the target does not exist yet, so dbt builds it from the model's select and both lines land. You now have two rows per order. On the next run those two source rows each match both target rows, and the engine errors.

If the strategy is not merge

On adapters whose default is not merge (Postgres, Redshift), the same duplicate source shows up differently. append inserts every source row, so you get duplicates with no error. delete+insert deletes target rows whose key appears in the batch, then inserts all batch rows: two source rows for one order_id delete the target row and insert both, again duplicates with no error. Only merge raises.

Fix

  • Use a key that is actually unique: unique_key = ['order_id', 'line_id']. Each source row then matches at most one target row and the merge is well-defined.
  • Or pre-aggregate to order grain in the model (sum items, count lines) and keep unique_key = order_id.

Detecting it

A unique test on order_id in the target fails after the first run. The cheaper place to catch it is the source: unique on order_id fails there before anything is merged.

Curated: · Written: · Reviewed:

Q&A-64Great Expectations runs after Spark writes the partition. The suite fails. What is already true for readers?

My answer to quality checks before write begins with where extract, load, and transform actually ran, and on which clock.

A failing suite after write means the bad partition is already visible unless you wrote to staging. Quality after publish is a ticket. Quality before pointer swap is a gate.

Concretely, write staging, run the suite, swap to serving on pass, and leave serving on the previous partition on fail.

The reason for that specificity is a failure I have seen: Suite failed on null keys 18 minutes after write. Three streaming consumers had already read 2.2 million bad rows into caches that lived 1 hour.

GE after serving write.

OrderBad rows consumedServing on fail
write serving, then GE2,200,000the bad partition
GE on staging, then swap0previous

I would not consider it settled without evidence: fail a suite in staging and require serving still on the previous snapshot.

Validate, then publish.

Curated: · Written: · Reviewed:

Q&A-54A 14-month backfill will run all weekend. When do uniqueness and not-null tests run?

Short answer: per partition, before that partition is published, not Monday. Not-null and within-partition uniqueness are single-pass checks you can run against the staging partition, so they cost one extra scan at most. The one thing a per-partition gate cannot see is a key that appears in two different months, so you also need a global uniqueness check before you call the backfill done.

Assumptions that decide this: the backfill writes to staging tables or shadow partitions and serving reads a pointer; the job is re-runnable; the natural key is known and stable across the 14 months.

Why per partition. A uniqueness or not-null violation is row-local. You can compute it while the partition is still in staging, before anything reads it:

-- Postgres 14; run against one month's staging partition before publish
SELECT
  COUNT(*)                                    AS rows,
  COUNT(DISTINCT order_id)                    AS distinct_keys,
  COUNT(*) FILTER (WHERE order_id IS NULL)    AS null_keys,
  COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_customer
FROM staging.orders_2024_03;
-- publish only if rows = distinct_keys AND null_keys = 0 AND null_customer = 0

If the sink column is already NOT NULL, the write fails on its own and the test is redundant. It earns its place when the sink is nullable but downstream assumes it isn't: a join that silently drops rows, a dashboard with a hole.

The gap a per-partition gate leaves. A unique index on (order_id, month) does not enforce global uniqueness. A key that lands in month 3 and again in month 7 passes every per-partition check. So the end-of-run gate is still needed:

SELECT order_id, COUNT(*) FROM serving.orders GROUP BY 1 HAVING COUNT(*) > 1 LIMIT 10;

What per-partition gating costs. Orchestration: you need a staging table per month plus an atomic publish step, and a failure at month 2 has to hold months 3–14 in staging rather than stall the write. Throughput: if the gate is a separate aggregate scan, you serialize a full pass per month on top of the write. If you instead accumulate rows, distinct_keys and null counts as running counters during the write, the gate is nearly free and the cost is only the publish step.

When Monday batch testing is fine. When nothing reads the sink until a single pointer flips at the end: a full atomic swap of a shadow table, no partial exposure. Then there is no window in which bad data is visible, and one end-of-run test is the cheaper design. It is also fine when the backfill is cheaply re-runnable and the table serves no user traffic. It is not fine when partitions publish incrementally, because then Monday is an autopsy.

Failure modes. A retry after a mid-write crash on a non-idempotent job re-inserts the partition; the per-partition gate catches it before publish. A late-arriving month 7 after month 8 has published passes the local gate and fails the global one. Exact duplicate rows double SUM and COUNT while leaving AVG unchanged: a hypothetical 14 months at 30M rows each, with 6 months re-inserted, adds 180M rows and doubles those months' totals. Detect it by comparing row count to distinct-key count per month, not by eyeballing averages.

GateExtra workCatchesMisses
Per partition, before publishone scan, or running counters during the writewithin-month duplicates, nullscross-month duplicates
Global, after all 14 monthsone group-by over the full tablecross-month duplicatestoo late if partitions already published

Curated: · Written: · Reviewed:

Q&A-100You validated dt=2026-03-02 and shipped. dt=2026-03-01 still has the old schema. Why is that not done?

Because "done" is defined over the partitions and consumers the contract names, and dt=2026-03-02 is one partition written by one job run. The 03-01 partition came from a different run: either the backfill for that date never executed, or it executed before the schema change landed, so its rows still carry the old columns. A green 03-02 tells you the new code works on one input. It tells you nothing about the partitions you didn't run it on, and nothing about the late windows that can rewrite a partition after you've already validated it.

State the assumptions before answering, because they decide the answer. I'm assuming the table is date-partitioned, the change adds or retypes columns so old rows don't satisfy the new contract, and there is more than one consumer: a batch reader plus at least one streaming sink that handles late data. If the change is purely additive and every consumer treats the new columns as nullable, leaving 03-01 alone is a legitimate call, and you should say so explicitly rather than let someone discover it later.

The mechanism is that backfill is per-partition work. Each dt is a separate job invocation with its own input, its own commit, and its own failure mode, so validating one invocation is a sample of size one. Late windows compound this: a streaming job that accepts events up to N days late will re-open a partition you already validated and write rows through the old code path if the sink wasn't updated in lockstep.

The trade-off is real and worth naming. Full validation of every partition is the safest option and often the most expensive: three years of daily partitions at 6.4M rows each is a lot of compute to re-run and diff. When that cost is unacceptable, sample deliberately rather than conveniently: the newest partition, the oldest, one with known skew or a schema edge case, and at least one late-arriving window. Then back the sample with a cheap invariant that runs across all partitions: a count of rows where the new column is null, or a row-count comparison against the source. A full scan of a count is far cheaper than a row-by-row diff and catches the silent gaps that sampling misses. The risk you accept with sampling is exactly that: a partition you didn't look at stays wrong until a consumer trips over it.

That risk is not hypothetical. Illustrative figures: 03-02 passed, while 03-01 still had null new columns across 6.4M rows. A late 03-02 window wrote old-schema rows into a second sink, and two consumers disagreed for five days before anyone noticed.

PathRows on old schemaDays of disagreement
dt=2026-03-02 batch0—
dt=2026-03-016,400,0005
late 03-02 window → second sinkold schema5

So the reason it isn't done is that the contract isn't satisfied: one partition of one writer is validated, and the partition set and consumer set are not. To close it I'd run the new job on 03-01, replay a late 03-02 window into the second sink, and run the null-count invariant across every partition in the range.

Curated: · Written: · Reviewed:

Q&A-104A producer adds a required column to the Parquet it writes. A reader pinned to the old schema reads yesterday's partition fine, then hits today's. What breaks, and how do you evolve the table without breaking readers?

Two layers are in play, and the break is rarely where people look. Parquet files are self-describing: each footer carries its own schema. A reader pinned to yesterday's schema reads yesterday's files fine. When it hits today's files, Parquet projects only the columns it requested, so an added column is ignored, not an error. The break is at the table layer. If the table schema (Hive metastore, Glue, Iceberg, Delta) wasn't updated, the new column is invisible to SELECT *. If the reader declares the new column as required, it fails on old files that lack it. Rename is worse: Parquet has no rename, so old files keep the old name and a reader selecting the new name gets nulls for every old partition.

What each layer actually supports:

ChangeParquet aloneIcebergDelta
Add optional columnNew files only; old readers ignore itMetadata-only, safeALTER TABLE ADD COLUMN, safe
Add required columnNew readers fail on old filesRejected unless rewrite or defaultRejected unless rewrite
RenameDrop + add; old files return nullMetadata-only, tracked by field IDRENAME COLUMN, needs column mapping
Widen int→longReader must handle bothAllowedGated behind delta.enableTypeWidening
Narrow long→intUnsafeRejectedRejected

The rule that decides most incidents: a column is required only when every file has a non-null value. Iceberg and Delta enforce that; Parquet does not.

The safe evolution, in order:

-- 1. Add as optional. Metadata-only, no rewrite.
ALTER TABLE prod.orders ADD COLUMN channel STRING;

-- 2. Backfill old partitions so every file has a value.
UPDATE prod.orders SET channel = 'unknown' WHERE channel IS NULL;

-- 3. Only now flip to required, if you must.
ALTER TABLE prod.orders ALTER COLUMN channel SET NOT NULL;

For a rename, use the table format's RENAME COLUMN (Iceberg field IDs, Delta column mapping). Never drop-and-add: that loses the old data's values and breaks readers pinned to the old name.

Failure modes and how you catch them:

  • Silent drop. A reader with a fixed StructType and SELECT * ignores the new column; the sink table is missing it. Caught by a schema diff between source and sink, or a not-null test on the new column.
  • Required-on-old-files. The reader declares channel required and reads a partition written before the change: Spark throws a missing-required-column error. Caught by running the backfill reader against the oldest partition first.
  • Rename without a table format. Old partitions return null for the new name. Caught by a null-rate spike on the renamed column.
  • Catalog not synced. Hive/Glue table schema still has the old columns; SELECT * hides the new one. Caught by a DESCRIBE diff in CI.

The durable fix is that readers use the table API, not a glob of Parquet paths. That is what gives them the current schema and snapshot isolation. A producer that writes Parquet with a new schema into a table path has bypassed the only component that can evolve the schema safely.

Curated: · Written: · Reviewed: —

Q&A-101events(user_id, event_ts, amount) has many rows per user. Write one query that returns each user's running total of amount in event_ts order, and tell me what happens when two rows share an event_ts.

Assumptions first, because they decide the query: the dialect is Spark SQL or Postgres (both follow the SQL standard frame defaults), amount is a signed decimal so refunds are negative, and "running total" means per event in event_ts order rather than per day. If you want it per day, aggregate before the window: I show that below.

SELECT
  user_id,
  event_ts,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM events
WHERE dt BETWEEN DATE '2026-01-01' AND DATE '2026-03-02';

The frame clause is the whole answer. With ORDER BY and no frame, the standard default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is not the same thing. RANGE includes every row that is a peer of the current row, same event_ts, so tied rows all receive the same total.

Trace for user 7:

event_tsamountROWS frameRANGE frame (default)
09:00101015
09:0051515
10:00203535

Two things fall out of that. The default silently gives a different number than you probably meant. And under ROWS, the two 09:00 rows get 10 and 15 in whatever order the engine emits them. The sort key is not unique, so the result is not stable across runs or across a repartition. If anyone reconciles this number, add a tiebreaker: ORDER BY event_ts, event_id. Note that in Spark a RANGE frame with more than one ORDER BY expression is rejected outright, so the tiebreaker forces you onto ROWS anyway.

Cost and failure modes. PARTITION BY user_id ORDER BY event_ts forces a shuffle by user and a sort inside each partition. One user holding 8% of events puts that user's entire history in a single task: the same skew that hangs joins. On a 400-million-row events table that is a multi-terabyte spill, and the job still "succeeds", just slowly. If you only need the cumulative at day grain, pre-aggregate first so the sort input shrinks from events to user-days:

WITH daily AS (
  SELECT user_id, DATE(event_ts) AS d, SUM(amount) AS amt
  FROM events
  GROUP BY user_id, DATE(event_ts)
)
SELECT user_id, d,
       SUM(amt) OVER (PARTITION BY user_id ORDER BY d
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM daily;

The incremental trap: a running total is a function of all history for that user, so you cannot compute today's partition independently and union it. Yesterday's cumulative is an input. Two workable patterns: recompute the window over the user's full history, or keep a per-user checkpoint and add the day's delta. The checkpoint breaks the moment a late event lands in a day you already closed, which is why I would ship the recompute-over-history version unless the history is genuinely unbounded.

NULLs: SUM ignores NULL amounts, so a NULL row contributes 0 and leaves the running total unchanged. That is usually what you want, but say it out loud rather than let someone assume the row was dropped. Negative amounts are refunds and must not be filtered: WHERE amount > 0 before the window is how you get a cumulative that does not match the ledger.

When not to use a window: if you need only the total per user, GROUP BY user_id is cheaper. If you need it per day, a self-join on d <= current_d is O(n²) and strictly worse: the window is the right tool there.

Curated: · Written: · Reviewed: —

Q&A-102What makes an Airflow DAG idempotent and safe to re-run for a past date, and how does catchup change the risk?

A DAG is idempotent for a past date when re-running the same logical date produces the same partition and no extra side effects. Three properties make that true: the run's logical date is the only time source, every read and write is scoped to that date, and the write is an atomic overwrite of that date's partition.

In Airflow 2.x, the run's logical date is the start of the interval. For a daily DAG at 00:00, the run for 2026-03-02 covers 2026-03-02 00:00 to 2026-03-03 00:00. {{ ds }} gives 2026-03-02. (execution_date is deprecated in templates since 2.2; logical_date is the modern name, but ds is still the partition key.)

Bad:

# non-idempotent
extract = PythonOperator(
    task_id='extract',
    python_callable=lambda: extract_data(datetime.now().date())
)
load = PythonOperator(
    task_id='load',
    python_callable=lambda: load_data(f's3://bucket/dt={datetime.now().date()}/')
)

Re-running 2 March writes to today's partition. The original 2 March partition stays stale.

Good:

with DAG('daily', schedule='@daily', start_date=datetime(2026,1,1), catchup=True, max_active_runs=3) as dag:
    extract = PythonOperator(
        task_id='extract',
        python_callable=lambda ds: extract_data(ds),
        op_kwargs={'ds': '{{ ds }}'}
    )
    load = PythonOperator(
        task_id='load',
        python_callable=lambda ds: load_data(f's3://bucket/dt={ds}/'),
        op_kwargs={'ds': '{{ ds }}'}
    )

The SQL must also use the template: WHERE event_date = '{{ ds }}', not CURRENT_DATE. And the write must be an overwrite of dt={{ ds }}, not an append.

PropertyIdempotentNon-idempotent
Time source{{ ds }}datetime.now()
Read scopeWHERE event_date = '{{ ds }}'WHERE event_date >= CURRENT_DATE - 1
Write modeoverwrite dt={{ ds }}append to dt={{ ds }}
Side effectsnone or dedupedemail, audit insert

Catchup behaviour: with catchup=True, Airflow schedules every interval from start_date to now. If the DAG is idempotent, that's a backfill. If not, it's a corruption event. Set max_active_runs to avoid flooding the source. With catchup=False, only the latest interval runs; to backfill you clear the task instance for that date or run airflow dags backfill.

Failure modes:

  • now() in SQL or path: re-run writes to wrong partition.
  • Append instead of overwrite: duplicates on every re-run.
  • Reading unbounded source: re-run picks up data that arrived after the original run, so the partition changes. That's not idempotent; it's a new result. For true idempotency, read a snapshot or bound by the interval.
  • catchup=True with a sensor that only sees today's file: all past runs fail.
  • Side effects like sending an email or inserting into an audit table without dedup: re-run sends again.

Detection: run the DAG twice for the same date and compare checksums of the output partition. Or query for duplicate primary keys in the target. airflow tasks test dag_id task_id 2026-03-02 runs a single task for that date without affecting the scheduler.

The test: clear 2026-03-02, let it run. The extract reads source where event_date='2026-03-02', writes to s3://bucket/dt=2026-03-02/. The load overwrites the Iceberg partition dt=2026-03-02. The result is identical to the original run if the source data hasn't changed. If the source is mutable, you need a snapshot or time-travel to get the same input; idempotency is about the DAG's logic, not the source's mutability.

Curated: · Written: · Reviewed: —

Q&A-103A team proposes bronze, silver and gold layers for a pipeline with one source and one consumer. Which layer do you skip, and what does each remaining layer actually buy you?

First I'd ask two things: what is the consumer's query pattern, and what is the freshness SLA? With one source and one consumer, the default answer is skip gold. Gold exists to serve multiple consumers with different access patterns: a BI tool wanting star schemas, an ML feature store wanting wide tables, an API wanting pre-aggregated counters. One consumer means one access pattern, and you can serve it from silver.

What each layer actually buys:

LayerWhat it buysWhat it costs
BronzeReplayability, raw fidelity, audit, decoupling from source schema drift. Append-only, immutable.Storage (S3 ~$0.023/GB-month), no query performance, schema-on-read burden.
SilverDeduplication, type conformance, PII masking, joins, partitioning, quality gates. This is where correctness lives.Compute on every run, backfill propagation, schema evolution handling.
GoldPre-aggregation, denormalized serving, consumer-specific indexing, SLA isolation.Extra hop latency (typically 5–30 min), extra storage, extra tests, another backfill target.

The trade-off is latency and maintenance against coupling. Each hop adds a scheduling dependency and a failure domain. A three-layer pipeline with a 15-minute SLA is hard: bronze lands at T+2, silver at T+8, gold at T+14, and any retry blows the SLA. Two layers gives you 12 minutes of headroom.

Concrete example: Postgres CDC → Kafka → S3. Bronze is raw Parquet partitioned by ingest date. Silver is an Iceberg table deduplicated on (order_id, revision) with typed columns. If the consumer is a dashboard running five queries, skip gold and create a materialized view in the BI tool. If the consumer is an API serving 10,000 req/s, gold is a pre-aggregated table: you don't want the API hitting silver.

Failure modes:

  • Skipping bronze: you cannot replay after a bad transform. You re-pull from source, which may not have history.
  • Skipping silver: every consumer query does dedup and casting. At 10M rows/day, that's 300 GB scanned per query instead of 40 GB.
  • Skipping gold: consumer pays aggregation cost, and your SLA now depends on their query load.

Detection: track p99 query latency, storage per layer, backfill duration, and number of distinct consumers. When the second consumer arrives with a different access pattern, that's when gold earns its keep.

Curated: · Written: · Reviewed: —

Q&A-6Max event time seen is 10:07. The out-of-orderness bound is 20 minutes. What is the watermark, and what does it claim?

Watermark is 09:47. Max event time 10:07 minus the 20-minute out-of-orderness bound is 10:07 − 00:20 = 09:47. It claims the stream is complete up to 09:47: no event with timestamp ≤ 09:47 will arrive after it. Any window whose end is ≤ 09:47 can fire and emit its result.

Why minus. The bound is your estimate of the worst lateness you will tolerate. An event stamped 10:07 may arrive at 10:25; you cannot know whether a 09:50 event is still in flight until you have seen something 20 minutes past it. So the newest time you can call complete is max_seen − bound. Flink spells it WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofMinutes(20)); Spark's withWatermark("event_ts", "20 minutes") does the same subtraction. Flink's implementation subtracts one extra millisecond (09:46:59.999) so the watermark sits strictly behind max − bound; the number you quote is 09:47.

What the claim is worth. It is a promise conditional on the bound being right. If an event 25 minutes late shows up, the watermark was wrong and the window it belonged to has already fired. That makes the bound a latency/completeness dial: larger bound, fewer late drops, longer wait before results. Flink's allowedLateness and Spark's late-data handling exist precisely because the claim is a heuristic, not a guarantee.

The mistake to avoid is using wall clock. A job that sets watermark = now + 20 min never advances past real event time, so windows never close.

Watermark ruleValue at max event time 10:0709:00 windowState
max event time − 20 min09:47firesbounded
wall clock + 20 min~10:27never firesgrows without bound

Worked example of the second row: a job that used processing time plus the bound never closed windows; state reached 180 GB in 4 hours, checkpoints took 11 minutes, and the 09:00 rollup emitted 0 rows while 2.4 million events sat in timers. Those figures are from that incident, not a benchmark.

To check a job: feed events whose maximum timestamp is 10:07 with a 20-minute bound and assert the watermark reads 09:47, not 10:27. If it reads 10:27, the operator is on processing time.

Curated: · Written: · Reviewed:

Q&A-10You windowed on processing time because event time was messy. What did the 09:00 bucket actually mean?

The short answer

The 09:00 bucket is the set of records that one task of the job read from its input between 09:00:00 and 10:00:00 on the wall clock of the machine running that task. It is a fact about the pipeline's own timeline, not about the business. It does not mean orders placed at 09:00. If the consumer was 14 minutes behind, the 09:00 bucket holds orders placed roughly 08:46–09:46. If the job was down from 09:20 to 09:35, the bucket is two disjoint slices of wall clock with a hole in the middle.

Where the timestamp comes from

Processing time is assigned, not observed. Kafka Streams' WallclockTimestampExtractor stamps each record with System.currentTimeMillis() at the moment the consumer reads it; Flink's TumblingProcessingTimeWindows uses the operator's system clock when the record reaches the assigner. Either way the number is generated by the job, so it inherits every property of the job's execution: consumer lag, backpressure, restarts, and the clock on the box.

// Kafka Streams 3.x
props.put(StreamsConfig.DEFAULT_TIMESTAMP_EXTRACTOR_CLASS_CONFIG,
          WallclockTimestampExtractor.class.getName());
builder.stream("orders")
       .groupByKey()
       .windowedBy(TimeWindows.ofSizeWithNoGrace(Duration.ofHours(1)))
       .count();

Two consequences follow directly:

  • Late data does not exist. There is no watermark to be behind. A record that arrives after the window fired is not dropped or side-output; it lands in the next window. Processing-time windows never lose rows, they misplace them, which is why the totals still look plausible.
  • The bucket is not reproducible. Replaying the same offsets at a different speed produces different buckets. Event-time windows produce the same buckets from the same events, which is the whole point of watermarks.

Worked example (hypothetical numbers)

Hourly processing-time window, one partition, consumer pauses 14 minutes at 09:00.

09:00 bucket10:00 bucket
live runorders placed 08:46–09:46orders placed 09:46–10:46
replay next day, no lagorders placed 09:00–10:00orders placed 10:00–11:00

Same input, different buckets. The 09:00 number moved by 14 minutes' worth of orders in each direction. An event-time window over the same stream gives the same two buckets in both runs.

What it is good for

Processing time is the right clock when the question is about the pipeline, not the business: how many records we flushed in the last hour, throughput and lag dashboards, cache and buffer eviction, alerting on a stalled consumer. It is the wrong clock for anything a human compares across days, anything finance reconciles, and anything you might replay. If event time was messy because the producer's clock is wrong or the field is missing, processing time is a defensible fallback, but then name the column for what it is (ingest_hour, not order_hour) and never diff two runs of it.

Failure modes and how you catch them

  • Consumer lag shifts the bucket. Detect by charting processing-time totals against event-time totals for the same period; a divergence that tracks consumer lag is the signature.
  • Restart leaves a hole. Detect by overlaying job uptime on the bucket; a bucket built from two disjoint intervals is not an hour.
  • Clock skew across parallel tasks straddles boundaries. A few hundred ms of NTP skew moves records across the 09:00 edge, and with parallelism > 1 the same event can land in different buckets on different subtasks. Detect by comparing per-subtask counts at the boundary.
  • Replay changes the answer. Detect by running the same offsets twice and diffing; event-time totals match, processing-time totals do not.

The test I would run before trusting any number: replay the same offsets after a deliberate pause, and require event-time totals to match while processing-time totals are allowed to move.

Curated: · Written: · Reviewed:

Q&A-8An event arrives with timestamp 08:51 after the watermark has passed 09:00. Where does it go?

Direct answer: by default it is dropped. The 09:00 watermark is the engine's assertion that no event with a timestamp before 09:00 will arrive, so the window 08:51 belongs to ([08:00, 09:00)) has already fired and its state is gone. Where the event actually goes is per-operator, and there are three destinations: a side output, a re-fire of the closed window, or the bit bucket.

Assumptions: event-time processing with a watermark; the 09:00 watermark has been emitted downstream; the window [08:00, 09:00) has already produced its result. Under those, 08:51 is 9 minutes late.

What each engine does

  • Flink: dropped by default. sideOutputLateData(...) routes it to a side output stream. allowedLateness(Time.minutes(N)) keeps the window state and re-fires the window for each late element, emitting an updated result, which means the sink must upsert, not append. Past allowedLateness it goes to the side output, or is dropped if none is configured.
  • Kafka Streams: windowed aggregations drop records later than window end + grace. Set grace() on the window spec to widen the window; drops are counted in the dropped-records-total metric with reason LATE.
  • Spark Structured Streaming: with a watermark, append mode drops late rows; update mode can still update the aggregate. There is no built-in late side output.

Failure mode. The dangerous case is a drop with no counter. I have seen 84,000 checkout events timestamped 08:51 land 26 minutes after the watermark closed at 09:00. They were dropped, no DLQ existed, so DLQ depth stayed at zero and nothing paged. Conversion for the 08:00 hour stayed 1.9 points low for the rest of the day.

What to build. Pick the destination explicitly and make it measurable:

  1. Emit late events to a side output / repair topic keyed on the same grain as the aggregate.
  2. MERGE them into the warehouse on the same idempotent key, so a re-fire and a repair converge to the same row.
  3. Increment a late_after_close counter and page on it.
HandlingLate events08:00 conversion errorPage
drop, no counter84,000 lost1.9 points lownone
side output + repair MERGE84,000 applied0on counter

How to verify. Inject events 26 minutes after close and require either a repair MERGE that restores the grain or an explicit drop metric that pages. If the counter stays at zero after the injection, the late path is not wired: that is the check that fails.

Curated: · Written: · Reviewed:

Q&A-69The job uses processing-time windows to "meet the SLA." Event time is 40 minutes behind. What number did you publish?

The number I published: 1.8M for the 09:00 hour, at 10:05. It's wrong, not in size, in content.

Assumptions I'd state up front: steady arrival of 30,000 events/minute (1.8M/hour), event time running a constant 40 minutes behind processing time, the 09:00 processing window closing at 10:00 and the result published at 10:05.

A processing-time window keys on the clock of the machine that processed the event, not on the timestamp inside the event. So the 09:00 bucket holds everything that arrived between processing 09:00 and 10:00. With a 40-minute lag, those arrivals carry event timestamps from 08:20 to 09:20.

BucketArrival windowEvent times insideRows
09:00, published 10:0509:00–10:00 processing08:20–09:201.8M
09:00, true event hour—09:00–10:001.8M

So the volume matches and the composition doesn't: 40 minutes of the 08:00 hour (1.2M rows) plus 20 minutes of the 09:00 hour (600k). The 09:00 hour's own last 40 minutes. 1.2M rows. Arrive during processing 10:00–10:40 and land in the 10:00 bucket. Every hour is shifted by 40 minutes, and every published number is a blend of two hours. If the rate isn't steady, the volume is wrong too, not just the composition.

Why it behaves this way: there is no processing-time watermark. Watermarks bound event-time lateness; a processing-time window has no notion of lateness, so it fires on schedule whether or not the data is there. That's the whole point of it, and it's also the trap.

What processing-time windows buy, and when they're the right tool: a close time you can put in a contract. No watermark tuning, no waiting on stragglers, no unbounded state: the window fires at 10:00 and the job is done. That's genuinely what you want for pipeline health: throughput per minute, consumer lag, cost per hour, "did the DAG keep up." Those questions are about when we processed, so arrival time is the correct key. They're the wrong tool for anything a business user reads as "what happened at 09:00."

Failure modes and detection:

  • Lag drift. A broker outage or a backfill pushes lag from 40 minutes to 3 hours; the same window is now wrong by a different amount, and the error is silent because the volume still looks plausible. Detect by tracking lag as a first-class metric and alerting when it exceeds the window's allowed lateness.
  • Double-counting across hours. The 1.2M rows that missed 09:00 inflate 10:00. Detect by reconciling processing-window totals against event-time totals for the same hour: they should converge once the watermark passes.

What I'd publish instead: window on event time, emit on the watermark, and put the SLA on watermark close. With 40 minutes of lag and, say, 5 minutes of allowed lateness, the 09:00 event-time window fires when the watermark passes 10:05, around processing 10:45, and the number is 1.8M of the right rows. If the job must keep processing-time windows, publish the number with the measured lag attached, or shift the window by the lag, and never call it the 09:00 figure.

Curated: · Written: · Reviewed:

Q&A-48dt is computed as DATE(event_ts) in the Spark session TZ. Executors are UTC. Airflow is US/Eastern. Which day owns 23:30 Eastern?

The day that owns 23:30 Eastern is decided by spark.sql.session.timeZone, not by the executor clock and not by Airflow. If the session is UTC, the next UTC date owns it. If the session is America/New_York, the Eastern date owns it. Executors running UTC is irrelevant: DATE() resolves against the session timezone, which is a session config, not the executor JVM default. Airflow's US/Eastern only sets the DAG's logical date and schedule; it never changes the value of dt written by the Spark job.

Mechanically, event_ts is stored as a UTC instant. DATE(event_ts) converts that instant into the session timezone and truncates to the calendar date there. The offset only bites in the Eastern evening, because 23:30 Eastern is already the next UTC day, while 00:30 Eastern is still the same UTC day and looks correct.

Trace a single instant, 2024-01-16T04:30:00Z, which is 23:30 on Jan 15 in Eastern (EST, UTC-5):

Session TZDATE(event_ts)Owns 23:30 Eastern?
UTC2024-01-16No: off by one day
America/New_York2024-01-15Yes

So the fix is to set the session timezone to the business calendar before computing dt, and to compute it from the stored UTC timestamp:

SET spark.sql.session.timeZone = America/New_York;
SELECT DATE(event_ts) AS dt FROM events;

spark.sql.session.timeZone defaults to the JVM default timezone of the session, which on most managed clusters is UTC, so a job that never sets it silently partitions on UTC dates.

The failure mode is a boundary that only appears in the evening. A test suite that asserts 00:30 Eastern passes under a UTC session, because 00:30 Eastern is 05:30 UTC on the same date. The 23:30 Eastern case is the one that flips. In an illustrative incident (figures hypothetical), a job left on the UTC default put 23:30 Eastern events into the next UTC date; roughly 1.1M orders landed in the wrong finance day for a month before anyone noticed, because the only boundary test was 00:30. Detect it by asserting the boundary directly: take a stamped 2024-01-15T23:30:00-05:00 instant and require dt = 2024-01-15, not 2024-01-16. Run that assertion against the same session config the production job uses, since the session timezone is what decides the answer.

If the business calendar is genuinely UTC, then UTC is correct and 23:30 Eastern belongs to the next day by design. The point is that the choice is explicit and tested, not inherited from a cluster default.

Curated: · Written: · Reviewed:

Q&A-49On the spring-forward night, the 02:00 hour does not exist. Your hourly job still has a 02:00 partition. What do you write?

What I write: nothing into a 02:00 partition. It does not exist.

On 2024-03-10 in America/New_York the clock runs 01:59:59 EST → 03:00:00 EDT. There is no 02:00 local hour, so there is no 02:00 bucket to write. The hourly run that would have landed there writes under the UTC hour key 2024-03-10T07:00Z, which renders as 03:00 EDT. If the partition key is UTC, the question dissolves: 24 partitions that day, one per UTC hour, no gap and no duplicate.

The mechanism is that local wall-clock hours are not a uniform timeline. Two days a year have 23 or 25 hours; UTC has 24 every day. Any job keyed on local hour inherits that non-uniformity, and the fix is to key on UTC and treat the local rendering as a label.

UTC hourAmerica/New_YorkNote
2024-03-10T05:00Z00:00 EST
2024-03-10T06:00Z01:00 ESTlast EST hour
2024-03-10T07:00Z03:00 EDT02:00 local skipped
2024-03-10T08:00Z04:00 EDT

And the mirror case, 2024-11-03, where one local hour happens twice:

UTC hourAmerica/New_YorkNote
2024-11-03T05:00Z01:00 EDTfirst 01:00
2024-11-03T06:00Z01:00 ESTsecond 01:00
2024-11-03T07:00Z02:00 EST
from datetime import datetime, timedelta, timezone
from zoneinfo import ZoneInfo  # Python 3.9+

NY = ZoneInfo("America/New_York")

def hour_keys(day_start_utc, hours=24):
    for i in range(hours):
        u = day_start_utc + timedelta(hours=i)
        yield u.strftime("%Y-%m-%dT%HZ"), u.astimezone(NY).strftime("%Y-%m-%dT%H:%M %Z")

for k, local in hour_keys(datetime(2024, 3, 10, 5, tzinfo=timezone.utc), 4):
    print(k, "->", local)
2024-03-10T05Z -> 2024-03-10T00:00 EST
2024-03-10T06Z -> 2024-03-10T01:00 EST
2024-03-10T07Z -> 2024-03-10T03:00 EDT
2024-03-10T08Z -> 2024-03-10T04:00 EDT

Failure modes worth naming. First, the empty partition marked success: a wall-clock 0 2 * * * schedule has no 02:00 to fire at, and whether the daemon fires it at 03:00 or skips it outright depends on the implementation, so the partition must not depend on that choice. If it fires late, it writes 0 rows and exits 0, and a downstream "partition exists" check passes on an empty bucket. Detect it by asserting the per-day partition count is 23, 24 or 25 and that no partition is empty without an explicit skip record. Second, the November duplicate: naive local-hour keying gives 01:00 twice, both runs write to 2024-11-03T01:00, and the hour's events double. Detect it with a uniqueness constraint on (partition_key, event_id) or a row count reconciled against the source. Third, a backfill that loops local hours 00..23 synthesizes a 02:00 bucket and writes stale data into it; make backfills iterate UTC.

The trade-off: UTC keys are monotonic, gap-free and cheap to reason about, at the cost of on-call reading 07:00Z and translating. DST-aware local keys that carry the offset, 2024-11-03T01:00-04:00 versus 2024-11-03T01:00-05:00, stay human-readable and still unique, but every consumer has to parse the offset and you get two keys for one wall-clock hour. Store UTC, render local in the UI.

Curated: · Written: · Reviewed:

Q&A-7The freshness SLA is 15 minutes. Allowed lateness is 2 hours. What did you just tell finance?

Finance heard one sentence: the 15-minute number is readable, not final. It can still move for up to two hours, so do not close the books on it.

That follows from the two clocks being different contracts. The freshness SLA is a read contract: at 15 minutes a consumer is allowed to read a closed figure for the hour. Allowed lateness is a write contract: how long the window stays open to accept retractions after the watermark has passed the end of the hour. When the lateness budget (2 hours) is longer than the SLA (15 minutes), every hour you publish at minute 15 is still open for another 1h45m. Available is not settled.

Concrete version I have had to walk a controller through: a job marked the 09:00 hour successful at 09:12. Inside the 15-minute SLA, so the board pack pulled it. The watermark did not close 09:00 until 11:07, because the lateness budget was 2 hours and the tail of late events ran to the end of it. Events that landed after the pack was built moved the hour 4.8 percent.

Clock09:00 marked09:00 actually closedLater move
Job success09:12not closed4.8 percent
Watermark, 2h lateness—11:070 after close

So the two things I told finance were: treat the 15-minute figure as provisional until the lateness budget expires, and expect a revision window of up to two hours on any hour. If that is unacceptable, the fix is on the pipeline side, not the finance side: either shrink the lateness budget to fit inside the SLA, or advertise an SLA that matches the lateness budget. You cannot have a 15-minute SLA on a 2-hour lateness job and call the number final.

Mechanically, the lateness budget is what keeps the window open for retractions after the watermark has passed the end of the hour; the out-of-orderness bound is what holds the watermark back in the first place. Both have to be sized to the observed tail of late events, not to a round number someone liked. Set the budget to that tail, publish a separate closed-hour flag that flips only when the watermark plus the budget has cleared the hour, and let consumers gate on the flag rather than on job success.

I would not call it settled without evidence: measure p99 event delay over 7 days and require the advertised SLA to be at least the watermark close time for that hour. If p99 delay is 40 minutes, a 15-minute SLA is a lie no matter what the DAG square says.

A green hour at minute 12 is not a closed hour at minute 12.

Curated: · Written: · Reviewed:

Q&A-9One Kafka partition goes silent for 40 minutes. The other 11 keep moving. Why did every window freeze?

What a strong candidate says first

The watermark is a minimum across all 12 partitions, and a window fires on the watermark, not on data arrival. Partition 7 stopped producing, so its watermark stopped advancing at its last event time. The min never moved, so no window whose end sits past that point could close: even though the other 11 partitions were delivering records the whole time. The fix is idle-source handling: WatermarkStrategy.withIdleness(Duration) (Flink 1.11+) drops a split out of the min once it has produced nothing for the timeout. It does not advance that partition's watermark; it just stops it holding the others back.

Why the min pins everything

Each source split tracks its own watermark = max event time seen − out-of-orderness. The source operator's watermark is the min across its active splits, and downstream operators take the min across their inputs. That is deliberate: a window is only complete when every input has seen data past its end. The cost is that one silent input is indistinguishable from one slow input.

Trace

12 partitions, 5s out-of-orderness, 1-minute tumbling windows.

  • Partition 7's last record carries event time 07:14:03. Its watermark sits at 07:13:58 and never moves.
  • Partitions 0–6 and 8–11 keep producing; by 07:54 their watermarks are at 07:53:5x.
  • Global watermark = min = 07:13:58, frozen for 40 minutes.
  • Window [07:15:00, 07:16:00) fires when the watermark reaches 07:16:00. It never does. Same for the next 39 windows.
  • The 11 live partitions buffer everything: 3.1M events over 40 minutes ≈ 1,290 events/s, about 117/s per partition.
SettingGlobal watermarkBufferedWindows 07:15–07:54
wait for all 12 partitionsstuck at 07:13:583.1M eventsnone fire
withIdleness(2 min)advances with the 11 live partitionsflushed as windows closefire on time

Config (checked against Flink 1.17)

KafkaSource<Event> source = KafkaSource.<Event>builder()
    .setBootstrapServers("kafka:9092")
    .setTopics("events")
    .setGroupId("rollup")
    .setStartingOffsets(OffsetsInitializer.committedOffsets(OffsetResetStrategy.EARLIEST))
    .setValueOnlyDeserializer(new EventDeserializer())
    .build();

WatermarkStrategy<Event> wm = WatermarkStrategy
    .<Event>forBoundedOutOfOrderness(Duration.ofSeconds(5))
    .withIdleness(Duration.ofMinutes(2))
    .withTimestampAssigner((e, ts) -> e.eventTimeMillis());

env.fromSource(source, wm, "kafka-events")
   .keyBy(Event::key)
   .window(TumblingEventTimeWindows.of(Duration.ofMinutes(1)))
   .allowedLateness(Duration.ofMinutes(1))
   .sideOutputLateData(lateTag)
   .aggregate(new RollupAgg());

Idleness is tracked per split, so with 12 partitions spread over several subtasks each split is judged separately.

What idleness does not fix

  • A dead producer looks exactly like an empty partition. Idleness will happily skip a partition whose producer crashed, and you get windows closing on partial data. Alert when a partition is idle past the timeout with no producer heartbeat, and watch per-partition consumer lag: flat lag on one partition while the others grow is the signal.
  • Reactivation. When partition 7 comes back its watermark is 40 minutes behind. Flink's watermark is monotonic, so the global watermark does not rewind, but the records it now delivers carry event times inside windows that already fired. Without allowedLateness or a late-data side output they are dropped silently. With allowed lateness the window re-fires and emits an updated result, which downstream must be able to absorb.
  • Timeout too short. A partition that legitimately produces every 5 minutes gets marked idle at 2 minutes, the watermark jumps ahead, and its next record is late. Set the timeout above the largest legitimate inter-record gap across your partitions.
  • Slow is not idle. A partition emitting one record every 30 seconds with old timestamps is never idle and still pins the min. Idleness does not help; fix the producer or split the job.

Alternatives

Heartbeat records: the producer writes a no-op record with the current timestamp to every partition every 30 seconds. Watermarks keep moving with no heuristic, at the cost of topic traffic and a convention every producer must follow. Processing-time windows never stall, but they are not reproducible on replay and drop late data silently.

Verification

Stop the producer on one partition and require the other 11 windows to close within the idle timeout. Alert on watermark lag, processing time minus currentInputWatermark, exceeding the timeout.

Curated: · Written: · Reviewed:

Q&A-93A fact arrives 3 minutes before the dimension row. The stream inner-joins. Where did the fact go?

The fact is gone, and the DAG will not tell you.

An inner join emits a row only when both sides match. When the fact is processed, the dimension row does not exist yet, so the join produces nothing. The fact is not queued for later and not retried: it is absent from the output. The pipeline reports success because nothing errored.

Before answering further, pin down which join this is, because the two behave differently and the fix differs.

Regular streaming join: both sides are stateful tables, e.g. a Flink SQL JOIN between two Kafka topics. The fact is buffered in join state keyed by the join key. When the dimension row arrives 3 minutes later, the join matches and emits the pair. Nothing is lost, unless table.exec.state.ttl is set shorter than the dimension lag: then the buffered fact is evicted and the late dimension finds nothing to match.

Lookup join: /*+ LOOKUP('table'='dim') */ against JDBC, Iceberg or HBase. The lookup runs per fact at processing time. A miss returns no row and the fact is dropped on the spot. There is no buffer, so a 3-minute lag is fatal by construction.

Trace a fact F with dim_key=42 at t=0; the dimension row for 42 lands at t=180s:

StrategyAt t=0At t=180sSink rows for F
Lookup join, no retrylookup(42) misses, emit nothingnothing to revisit0
Regular join, state TTL > 180sF buffered under key 42dim arrives, pair emitted1, complete
Left outer + repairemit (F, null dim attrs)repair MERGE fills attrs1, complete

Fixes, in order of preference.

  1. Retry the lookup. Flink lookup joins accept retry hints ('retry-predicate'='lookup_miss', 'retry-strategy'='fixed_delay', 'max-retry'), so a miss is retried for the allowed-lateness window before giving up. Cheap, no state growth, but it blocks the operator on each miss.
  2. Inferred member. Emit the fact with a placeholder key (-1, UNKNOWN) so downstream aggregates stay complete, then a repair job MERGEs real attributes when the dimension lands. This is the Kimball late-arriving-dimension pattern; it keeps the fact stream flowing.
  3. Left outer join plus repair. Keep the fact with null dimension attributes, then rewrite the enriched row once the dimension arrives. Requires an upsert-capable sink (Iceberg MERGE, Hudi upsert, Delta MERGE) and a stable fact key.
  4. Regular join with a TTL sized to the lag. Correct, but state grows with fact volume × key cardinality. At 50k facts/s and a 180s lag that is 9M buffered rows; at ~200 bytes each, roughly 1.8 GB of join state per key space (illustrative figures).

Failure modes to name.

  • Silent drop. Green DAG, zero errors, row counts down. Detect by reconciling source fact counts against sink counts per window, and by a counter on the lookup-miss path.
  • State blowup. Regular join with TTL off and high-cardinality keys grows until the task OOMs or checkpoints slow.
  • Repair never runs. The dimension arrives after the repair job's watermark has passed the fact's window, so the fact is never revisited. Bound the repair window explicitly.
  • Double emission. Outer join plus repair duplicates rows if the repair is not idempotent on the fact key.

How I would verify it. Send a fact 3 minutes ahead of its dimension row and assert it lands, complete, exactly once. Then assert the miss counter is zero. A join that drops facts and reports success is the failure this question is testing for. The inner join is a drop rule; the outer join plus repair is a completeness rule. Pick deliberately.

Curated: · Written: · Reviewed:

Q&A-13The consumer commits offsets before the warehouse MERGE returns. What happens on a crash?

The first thing I would establish about Kafka offset versus sink write is which write actually landed, not which task box turned green.

Committing offsets first marks the messages as done while the sink may still be empty. Those offsets will not be reread. Committing after a successful idempotent MERGE can reread, which is safe if the MERGE key holds.

Concretely, write the sink, confirm the commit, then checkpoint offsets, and never ack Kafka before the table snapshot exists.

The reason for that specificity is a failure I have seen: Offsets advanced, then the MERGE timed out. 960,000 payments were acked and missing. The DAG was green. Finance found a $2.8 million hole on Monday.

Offset commit before MERGE.

Order of operationsPayments after crashDAG
commit offsets, then MERGE timeout960,000 missinggreen
MERGE then checkpoint offsets960,000 present (or retried merge)retry

I would not consider it settled without evidence: kill the worker after offset commit and before MERGE, and require the test to fail that design; the passing design still has the rows after restart.

Ack after the table commit, not before.

Curated: · Written: · Reviewed:

Q&A-15You MERGE on (order_id). The source emits order_id plus a revision. Why do you still lose updates?

The 90-second answer. The merge key has to identify the write, not the entity. order_id identifies an order; (order_id, revision) identifies a write. If the source carries a revision, merging on order_id alone throws that information away at the exact moment you need it, and you get one of two failures:

  1. Across batches: a late-arriving lower revision matches on order_id and, because WHEN MATCHED has no guard, overwrites the newer row. Last writer by arrival order wins, not highest revision.
  2. Within a batch, if the source emits more than one revision per order_id, several source rows match one target row. Engines that enforce one-source-row-per-target-row reject the statement outright: PostgreSQL 15+ raises ERROR: MERGE command cannot affect row a second time; SQL Server raises Msg 8672, "The MERGE statement attempted to UPDATE or DELETE the same row more than once." Snowflake, BigQuery and Delta Lake reject the same shape.

Fix. Guard the match on the revision, or key on the write.

MERGE INTO orders AS t
USING staged_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.rev > t.rev THEN UPDATE SET
  rev = s.rev, status = s.status, refund_cents = s.refund_cents
WHEN NOT MATCHED THEN INSERT (order_id, rev, status, refund_cents)
  VALUES (s.order_id, s.rev, s.status, s.refund_cents);

If the source can carry several revisions for one order in a single batch, dedupe before the merge (ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY rev DESC), keep rn = 1) otherwise the guard doesn't save you; the statement still errors on the duplicate match.

Trace. Target row: (order_id=1, rev=3, status='refunded'). Source batch delivers (1, rev=2, status='placed').

ON clauseMatch?Resulting revResulting status
t.order_id = s.order_idyes2placed
t.order_id = s.order_id AND s.rev > t.revyes, guard false3refunded

The first row is the lost update: a refund reverts to placed because a stale event arrived late.

When the guard isn't enough.

  • Revisions must be monotonic per order_id and comparable. If two writers each keep their own counter, rev alone is not an order: compare (rev, writer_id) or use a version vector.
  • Concurrent merges: two sessions can both read rev=3 and both write rev=4. The guard is a read-then-write inside one statement, not a lock. Add a unique constraint on (order_id, rev) or run the merge in a transaction that takes the row lock, and retry on conflict.
  • If you need history, key on (order_id, revision) and never update: append. Then losing a revision is impossible by construction.

How to verify. Apply rev 3, then rev 2, and assert the row still reads rev 3. Then apply a batch containing both rev 2 and rev 3 for the same order and assert the statement succeeds and lands on rev 3. Those two tests catch both failure modes.

Curated: · Written: · Reviewed:

Q&A-11The stream says exactly-once. The sink is an S3 PUT plus a later Iceberg commit. What guarantee do you actually have?

At-least-once into the table. You get exactly-once only if the engine's checkpoint and the Iceberg commit are one protocol, or the sink is idempotent on a key you can merge on. A bare S3 PUT followed by a separate table commit gives you neither.

Assumptions: Kafka source with offsets held in the engine's checkpoint, Iceberg table on S3, and either Flink 1.18+ or Spark Structured Streaming 3.5 with the Iceberg sink. The answer differs between those two engines, and that difference is the question.

Flink's Iceberg sink does two-phase commit tied to checkpoints. IcebergFilesCommitter writes data files during the checkpoint interval, and on checkpoint completion renames them into the table and commits one snapshot. Offsets and snapshot land together, so a crash rolls back to the last checkpoint and the uncommitted files are deleted. That is exactly-once end to end.

Spark Structured Streaming is not that. The offset log is written before the micro-batch runs. If the job dies after the S3 PUT and before the Iceberg commit, the batch is replayed on restart and the sink writes again. Spark's contract is that sinks must be idempotent; it does not dedupe for you. So Spark plus Iceberg is at-least-once unless you make it otherwise.

Three ways to close it:

  • Two-phase commit of files and offsets, as Flink does. Requires the engine to own both commits.
  • Idempotent merge on event id: MERGE INTO orders t USING staged s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ..., so a replay converges to the same row set. Costs a join against the target on every batch.
  • Accept at-least-once and dedupe downstream on a key with a watermark. Cheapest, but the table itself is not clean.

Failure mode, from a job I ran: PUT succeeded, then the process died before the Iceberg commit. The 2.2 million rows sat as orphans, in no snapshot. Restart wrote new files and committed those. COUNT(*) did not double, and COUNT(DISTINCT order_id) in the table was one copy, with the first files left on disk until orphan cleanup.

Restart pathTable COUNT(*)On-disk orphans
Retry after failed commitone committed copyfirst PUT files
Two-phase commit of files and offsetsone committed copycleaned

Orphans are not a second snapshot. They are invisible to every reader and accumulate until you run Iceberg's remove_orphan_files procedure (CALL catalog.system.remove_orphan_files(table => 'db.orders')), which is why the disk bill grows while the table looks correct.

I would not call it settled without evidence: kill the job after the object PUT and before the table commit, restart, and require one committed snapshot plus orphans outside it, not two committed copies of the same keys. If you see two snapshots carrying the same order_id, the sink is not idempotent and the two-phase commit is not wired up.

Curated: · Written: · Reviewed:

Q&A-12Kafka is at-least-once. How do you keep the fact table from growing on every retry?

The 90-second answer

At-least-once is a delivery guarantee, not a uniqueness constraint. A consumer that processes a batch and dies before committing its offset sees the batch again; a producer that retries after a timeout can write the same record twice. So the fact table grows on retry unless the sink is idempotent. The default fix is a deterministic event id assigned by the producer and a MERGE (upsert) on that id at the sink. The alternatives (idempotent producer, Kafka transactions, read-time dedup) each cover a narrower slice, and I'd pick between them on where the duplicate can enter.

Assumptions: the sink is a warehouse or lakehouse table (Delta, Iceberg, Snowflake, BigQuery); event_id comes from the producer, not generated at consume time; the table is partitioned by event date and clustered or sorted on event_id.

What each option actually covers

OptionRemovesLeavesCost
enable.idempotence=trueproducer retries within one session, per partitionconsumer reprocessing, producer restart, cross-partition dupesone config, ~free
Kafka transactions (transactional.id, isolation.level=read_committed)consume-transform-produce dupes inside Kafkaanything written to an external sinkadded latency, transaction coordinator, consumers must read committed
MERGE on event_id at the sinkall duplicates, if the key is deterministicnothing, if the key is rightread-modify-write of touched files per batch
append + dedup at readnothing at write—every query pays

The idempotent producer is the cheapest and the most commonly misread: it dedups by producer id and sequence number, so it survives a retry of the same batch, but a new producer session starts a new sequence and the broker accepts the same payload again. It does nothing for a consumer that reprocesses.

What the failure looks like

A 3-hour outage retried the same batch 9 times into an append sink. 1.4M events became 12.6M rows: 8× extra. AVG(order_value) was unchanged because the duplicates were exact copies; SUM(order_value) was 9×.

SinkRowsSUMAVG
append12,600,000×9unchanged
MERGE on event_id1,400,000×1unchanged

What MERGE costs

On Delta/Iceberg, MERGE is a read-modify-write of every file holding a matching key, so cost scales with files touched, not rows inserted. A 1-minute micro-batch MERGE on a table clustered by event_id touches one or two files; the same MERGE on a table clustered by ingest time scans the whole table per batch. Frequent small MERGEs also produce small files, so you pay compaction on top. On Snowflake/BigQuery, MERGE joins the target, so an unclustered key means a full scan per batch.

Failure modes and detection

  • Consumer-generated UUID as the key: MERGE never matches, duplicates land. Detect with count(*) - count(distinct event_id) per partition.
  • MERGE windowed to the last N days: late duplicates outside the window append. Detect by comparing raw vs deduped counts on old partitions.
  • Small-file explosion: watch file count per partition, not row count.

When not to MERGE

Append-only event tables where duplicates are rare and reads already aggregate: the MERGE costs more than the dupes. Immutable audit logs where you want the raw stream and dedup downstream. And if the key can't be made deterministic, MERGE is the wrong tool; fix the producer first.

Curated: · Written: · Reviewed:

Q&A-14A Spark job uses a transactional Kafka producer. The consumer isolation is read_uncommitted. What did the consumer just see?

What the consumer saw

Everything up to the partition's high watermark, with no transaction filtering at all. That includes records belonging to transactions that were later aborted, and records from transactions that are still open and may yet abort. A read_uncommitted consumer does not consult the aborted-transaction index and does not stop at the last stable offset. If the Spark job wrote those rows to a sink and committed its Kafka offsets, the aborted data is now downstream permanently: Kafka's abort cannot reach it.

Why

A transactional producer writes its records, then the broker appends a control batch (COMMIT or ABORT) to the same partition. Two offsets matter:

  • High watermark (HW): end of the replicated log. read_uncommitted reads to here.
  • Last stable offset (LSO): the first offset of the earliest still-open transaction. read_committed reads only to here, and drops records covered by an ABORT control batch.

The records themselves carry no marker a consumer can see; filtering is done client-side from control batches plus the broker's aborted-transaction index. read_uncommitted skips that step entirely.

Trace (hypothetical offsets, partition 0)

OffsetContent
0–4records, committed
5–9txn A records
10COMMIT marker, txn A
11–15txn B records
16ABORT marker, txn B
17–21txn C records, still open

HW = 22, LSO = 17.

  • read_committed delivers 0–9, skips 11–15, and stalls at 17 until txn C commits or aborts.
  • read_uncommitted delivers 0–9, 11–15, and 17–21. The aborted txn B rows are in the output.

When read_uncommitted is the right call

It is not simply wrong. It is correct when the topic has no transactions (then it is identical to read_committed, minus the stall), and reasonable when the sink deduplicates or the pipeline makes no exactly-once claim: telemetry, metrics, best-effort audit streams. It also avoids head-of-line blocking: a read_committed consumer cannot advance past an open transaction, so one stuck producer stalls every read_committed consumer on that partition until the transaction times out (transaction.timeout.ms, producer default 60000 ms, capped by broker transaction.max.timeout.ms, default 900000 ms). That stall is the price of read_committed, and it is real: lag climbs with no error.

Failure modes to name

  • Aborted rows land in the sink and stay there. Detection: compare row counts against a read_committed replay of the same offsets.
  • Offsets committed past an open transaction. If txn C later aborts, the consumer never re-reads 17–21: the offsets are already committed.
  • A job claiming EOS while reading with the default. Spark's Kafka source exposes kafka.isolation.level, default read_uncommitted (Spark 3.x); EOS claims need it set to read_committed on every consumer in the chain, plus idempotent or transactional writes at the sink.

How I'd decide

Ask two questions: does the sink deduplicate or overwrite by key, and does the pipeline claim exactly-once? If both answers are no, read_uncommitted is fine and cheaper. If either is yes, set read_committed and accept the LSO stall, and size the transaction timeout so a slow producer cannot freeze the consumer indefinitely.

Curated: · Written: · Reviewed:

Q&A-80You fixed the schema and replayed the DLQ with the same event ids into an append sink. What duplicated?

What duplicated: every event id that had already landed in the sink before the fix. The DLQ is not the set of events that never landed. It is the set of failed attempts. Any event that failed the schema check, got parked, and then succeeded on a retry is in both places. Replay appends a second copy of exactly those.

Trace it with six events. Live path runs first, then the schema fix, then replay:

EventLive attemptIn sink after liveIn DLQ
e1okyesno
e2schema failnoyes
e3okyesno
e4schema failnoyes
e5fail, retry okyesyes
e6okyesno

Replay appends e2, e4, e5. Sink now holds e1, e3, e5, e6, e2, e4, e5: 7 rows, 6 distinct ids. The duplicate is e5, and only e5: it is the one id present in both the sink and the DLQ. COUNT(*) went up by 3; COUNT(DISTINCT event_id) went up by 2.

Three other sources of duplication, all real:

  • Repeated DLQ records. If each failed attempt is parked separately, one event id can appear several times in the DLQ. Replay writes one row per DLQ record, not per id.
  • Replay retries. The replay job is itself at-least-once. If a task writes half its batch and then fails, the retry rewrites that half. This duplicates events that were never in the sink at all.
  • Non-deterministic partitioning. If the sink partitions on ingest time or a random key, the replayed copy lands in a different partition than the original, so a partition-level dedupe misses it.

Fix. Replay through a keyed write, MERGE/upsert on event_id, so a replayed id replaces rather than appends. If the sink is plain Parquet files with no key, you cannot merge in place: either rewrite the affected partitions, or move to a table format with merge-on-read. Dedupe the DLQ by event_id before replay as well, so intra-DLQ repeats collapse.

Detect it before replay, not after. Anti-join the DLQ ids against the sink: SELECT d.event_id FROM dlq d JOIN sink s USING (event_id) gives you the exact duplicate set and its size. After replay, COUNT(*) - COUNT(DISTINCT event_id) per partition is the cheap check.

The trade-off to name. MERGE on event_id assumes the replayed record is the authoritative version. If the schema fix also corrected field values, the merge silently overwrites the original with the new parse, which is usually what you want, but it is a data change, not a no-op. If you need both versions, key on (event_id, version) and keep the append, accepting that consumers must pick the latest.

Curated: · Written: · Reviewed:

Q&A-82You added backoff. The sink is still append. A timeout that actually succeeded then retries. What happens?

What happens: the retry appends a second copy of every batch that had already committed. Backoff changes when the retry fires, not what it does. A timeout tells you the client stopped waiting; it says nothing about whether the server wrote. So the sink ends up holding the original rows plus a duplicate set, and nothing in the pipeline flags it.

The timeout is ambiguous because the write path is at-least-once by default. A request can fail before the sink ever sees it, or commit and lose the response on the way back. From the client those two look identical. Retrying is correct for the first case and a duplicate for the second, and the client cannot tell them apart.

A worked example, with hypothetical figures: a nightly loader pushes 2,400 batches of 50,000 rows. The sink commits each batch, then the response is lost on all 2,400 of them: say a proxy restarted mid-run. Backoff retries every batch. The append sink writes 2,400 × 50,000 = 120,000,000 extra rows.

SinkExtra rowsCOUNT(*)SUM(amount)AVG(amount)
append120,000,000doubleddoubledunchanged
MERGE on request_id0correctcorrectcorrect

AVG is the trap. Duplicate rows carry the same value, so the average looks healthy while the total is wrong, and a dashboard watching AVG shows nothing at all.

The fix is to give every batch a request id the source generates and the sink enforces. MERGE ... ON request_id, or a unique constraint plus an upsert, turns the second delivery into a no-op. The dedupe key has to outlive the retry window: if the longest retry chain is 8 attempts with a 60 s cap, keys must be retained longer than that, or a late retry lands after the key was pruned and duplicates anyway.

Failure modes worth naming before you ship this:

  • Dedupe keys pruned on a shorter schedule than the retry window, so a late retry duplicates.
  • MERGE on a key that is not actually unique, such as a timestamp, which merges unrelated rows instead of deduping.
  • Backoff without jitter, so every client retries in lockstep and rebuilds the storm you were trying to damp.
  • No retry budget, so a permanently failing sink gets hammered until someone notices.

When not to bother: if the sink is already idempotent (an upsert keyed on the business primary key, or a full-partition overwrite) the retry is safe as it stands and a request id adds nothing.

How I would verify it: force a commit-then-timeout by killing the response path after the sink commits, let the retry run, and assert exactly one row per request id.

Curated: · Written: · Reviewed:

Q&A-68Consumer lag is 0. The warehouse hour is still 4 percent short. What was lag not measuring?

Lag measures one thing: the distance between the consumer group's committed offset and the partition's log end offset. It is a position on the log, not a count of rows that reached the warehouse. A pipeline can commit every offset and still write 96 percent of the rows, because the commit tracks consumption, not a successful MERGE.

The commit semantics are where the gap opens. With enable.auto.commit=true (the default) and the default auto.commit.interval.ms=5000, the consumer commits the offsets of records it has fetched, not records it has finished processing. If a decode throws or the process dies after the fetch, the offset is already committed and the record is gone: no redelivery, no lag. Manual commit-after-consume has the same hole when the commit lands before the sink write.

So lag is blind to everything downstream of the poll loop:

  • Decode failures. A schema change makes 4 percent of records throw in the deserializer. If the handler catches and continues, offsets advance and the rows never exist.
  • DLQ routing. Records that fail contract validation get parked in a dead-letter topic. The consumer is caught up; the table is short by exactly the DLQ volume.
  • MERGE/upsert semantics. A MERGE that dedupes on a key, or an upsert that overwrites, produces fewer rows than it consumed by design. Lag 0, row count down.
  • Sink write failures. The warehouse load fails after the offset commit, and the retry never happens because the offset says the work is done.
  • Window placement. Late records land in a different hour partition, so this hour's table is short while the consumer is current.

Detection is a reconciliation, not a lag alert. Compare produced count (or a checksum over the payloads) against merged row count per hour and per partition, and alert on the delta. Track DLQ depth and a contract-reject counter as first-class metrics. A per-partition lag view catches a single stalled partition that an aggregate can hide.

SignalValueWhat it tells you
Consumer lag0Offsets committed to log end
Produced records, hour 141,000,000Source of truth
Merged rows, hour 14960,0004 percent missing
DLQ depth0Not parked, dropped
Decode errors40,000Matches the gap

A 4 percent gap with zero DLQ depth points at decode or sink failure, not at a consumer that is behind. Lag would have stayed green through all of it. Lag is the right metric when you want to know whether the consumer is keeping up with the log; it is the wrong metric for whether the table is complete, and the two questions need separate alerts.

Curated: · Written: · Reviewed:

Q&A-88A Flink checkpoint succeeds. Kafka offsets in the checkpoint are behind the sink's last write. After restore, what happens?

Short answer: you get duplicates. The checkpoint records the Kafka source's offsets, not the sink's writes. If the sink wrote records past those offsets, restore rewinds the source to the checkpointed offsets and reprocesses them, and the sink receives them a second time. Flink does not deduplicate that for you.

Why the offsets lag the sink. A checkpoint barrier is injected at the source and flows downstream. The source snapshots its offsets when the barrier passes it. Records the source emitted after that snapshot, and that the sink wrote before the checkpoint completed, are in the sink but not in the checkpoint. The checkpoint is a consistent snapshot of Flink's internal state; the sink's external writes sit outside it unless the sink participates in the checkpoint protocol.

The replay window is bounded by the checkpoint interval plus the time the checkpoint takes to complete, not by how long the job ran. With a 1-minute interval and a 10-second checkpoint, expect roughly the last minute of input to be replayed.

What makes it safe.

Sink typeBehaviour on restore
Plain append (JDBC insert, Kafka producer without transactions)Replays the window; duplicates land in the sink
Two-phase commit (Flink TwoPhaseCommitSinkFunction, Sink V2 exactly-once, Kafka transactional producer)Pre-commits on checkpoint, commits on checkpoint completion. On restore, transactions not covered by a completed checkpoint are aborted and those from the last completed checkpoint are committed, so no duplicates
Idempotent / upsert (MERGE on primary key, Kafka compacted topic keyed by id)Replays, but the second write overwrites the first; row count stable

Worked example (illustrative numbers). Checkpoint interval 1 minute, checkpoint completes in 10 s. The sink is a Postgres INSERT. At t=0 a checkpoint completes with source offsets covering events up to id 1,000,000. Between t=0 and t=60 the source emits ids 1,000,001–1,045,000 and the sink inserts all of them. At t=60 a new checkpoint starts; the barrier snapshots offsets at id 1,045,000. The job fails at t=61, before that checkpoint completes. Restore uses the t=0 checkpoint: the source rewinds to id 1,000,000 and re-emits 1,000,001–1,045,000. The sink inserts them again. COUNT(*) is 45,000 too high; COUNT(DISTINCT event_id) is unchanged. Swap the sink for INSERT ... ON CONFLICT (event_id) DO UPDATE and the second pass is a no-op.

Failure modes to name. Duplicates are silent: no error, no alert, just inflated aggregates and double-counted revenue. Detect them with a reconciliation query against the source (distinct ids in sink vs. source) or a monotonic counter in the sink. A transactional sink removes duplicates but adds commit latency and, for Kafka, requires a transactional.id prefix and isolation.level=read_committed on consumers. If you cannot make the sink idempotent, the honest choice is at-least-once with downstream dedup.

Curated: · Written: · Reviewed:

Q&A-89trigger(processingTime='1 second') on a sink that cannot commit that often. What fails first?

I would treat structured streaming trigger as a claim about every partition and every late window, not about the one that was demonstrated.

A Structured Streaming query runs one micro-batch at a time. It does not overlap commits. A trigger faster than commit time does not start a second batch; the next trigger waits, so lag grows and you still write tiny files. The trigger is a commit budget: it should sit at or above p99 sink commit time, not at a round number that looks real-time on a slide.

Concretely, set trigger ≥ p99 commit time, compact on the same DAG, and measure batch duration and lag, not concurrent commits.

The reason for that specificity is a failure I have seen: 1-second trigger, 8-second commits. Batches ran serially, lag grew by about 7 seconds plus backlog, and 50,000 tiny files/day accumulated. There were not 900 overlapping Iceberg commits per hour. After a 1-minute trigger, lag cleared, files 1,400/day.

1-second trigger, 8-second commits.

TriggerConcurrent batchesLagFiles/day
1 secondnone; serial~7s plus backlog50,000
1 minutenone; serialcaught up1,400

I would not consider it settled without evidence: compare lag and file count at 1s versus 1 minute on the same sink.

The trigger is a commit budget.

Curated: · Written: · Reviewed:

Q&A-90You switched to continuous trigger for "real time." The sink is Iceberg MERGE. Why did latency get worse?

Assume the sink is a foreachBatch that runs MERGE INTO an Iceberg table. Then the switch to continuous didn't make latency worse: it stopped the job. Spark's continuous trigger supports only map-like operators and three sinks: Kafka, console and memory. A MERGE is a foreachBatch sink, and continuous rejects foreachBatch at planning time with an unsupported-operation error before the first batch runs. You don't get a slower pipeline; you get a query that never commits a snapshot. If you genuinely saw p99 visibility rise, you weren't in continuous mode: you shrank the micro-batch interval to chase "real time," and that is what hurt.

Why shrinking the interval makes MERGE slower. An Iceberg MERGE is not an append. Each batch reads the target files whose keys match, rewrites them, and commits a new snapshot: a new manifest list, new manifests, and an optimistic-concurrency check that retries on conflict. That commit cost is roughly fixed per batch and barely depends on how many rows the batch carries. Micro-batch visibility is therefore interval + commit, and the commit term does not shrink when you shrink the interval.

Illustrative arithmetic: if a MERGE commit costs ~2 s of planning, manifest rewrite and snapshot commit, then at a 30 s interval the overhead is ~7% of the cycle and p99 visibility lands near 32 s. At a 1 s interval you ask for 1 s of data every 1 s but pay 2 s of commit each time: the pipeline cannot keep up, the backlog grows without bound, and visibility climbs instead of falling. That is the "latency got worse" you observed.

TriggerWhat Spark does with an Iceberg MERGE sinkEffect on visibility
continuousrejects at planning (foreachBatch unsupported)job fails, no commits
1 s micro-batchone MERGE + snapshot commit per secondcommit overhead dominates, backlog grows
30 s micro-batchone MERGE + snapshot commit per 30 soverhead amortized, p99 ≈ interval + commit

What to do instead.

  • Append-only sink: skip MERGE. Iceberg's native streaming append sink (format("iceberg")) with a short micro-batch of 1–5 s is the closest to real time Iceberg gives you; there is no read-modify-write, so the interval actually controls latency.
  • Upserts required: keep MERGE, but size the batch to the commit cost, not the clock. 30–60 s is a common landing zone; measure commit time and set the interval so commit overhead stays a small fraction of the cycle.
  • True continuous: only for map-only pipelines into Kafka, console or memory. It gives at-least-once, not exactly-once, so it is a poor fit for a table that needs upserts anyway.

Failure modes and detection.

  • Planning rejection: the job never starts, failing with an unsupported-operation error naming the sink or operator. Detect it by the query failing immediately with no checkpoint progress.
  • Small-file and metadata bloat: many tiny commits create many snapshots and manifests, so reader planning time grows and the table degrades. Detect it by tracking snapshot and manifest counts per table and average file size against the 512 MB target file size.
  • Commit conflicts: concurrent writers retry the commit and p99 spikes. Detect it with commit-retry metrics and snapshot commit duration.

Curated: · Written: · Reviewed:

Q&A-74Streaming MERGE and a batch overwrite both own today. Who wins?

The overwrite wins, and it wins by deletion. The stream's 90,000 rows aren't merged or superseded: they're gone. The deciding fact isn't commit order; it's that the overwrite's replacement payload was built from a source extract taken before those rows existed.

Assumptions: an Iceberg or Delta table, a streaming MERGE writing today's partition, a batch INSERT OVERWRITE of the same day, no lock, no shared writer queue.

The two commits have different footprints. A streaming MERGE is a delta commit: it reads the files matching its predicate, writes new data files for inserted and matched rows, and writes delete files (positional or equality) for rows it replaced. Its footprint is the files it read: a handful. An INSERT OVERWRITE is a replace: it deletes every file in the target partition and writes a fresh set from the batch's source extract. Its footprint is the whole partition, and its content is whatever the extract held when the batch started reading.

So the overwrite doesn't lose the stream's rows through a race in the commit protocol. It never had them. The replacement set was fixed at read time.

Trace, 14-minute overlap.

tWriterActionToday's rows
0:00batchreads source extract S (10 min stale)1,200,000
0:00–0:14stream3 MERGE commits, 12 new files, +90,0001,290,000
0:14batchINSERT OVERWRITE today from S: deletes 41 files, writes 411,200,000

Net: 90,000 rows lost, and no error raised if the writer didn't validate.

Where the conflict check sits. Whether you get silent loss or a loud failure depends on the writer, not the table format alone. Delta raises ConcurrentAppendException when an append lands in a partition being replaced. Iceberg's OverwriteFiles only rejects the commit if the writer opted into validateNoConflictingAppends; without it, the replace commits. And even a loud failure doesn't save the rows: the retry re-reads the same stale extract and replaces again. Validation converts data loss into a retry loop; it doesn't preserve the stream's rows.

The reverse conflict exists too: if the overwrite commits first, the stream's MERGE commit can fail because the files it read were deleted (Delta: ConcurrentDeleteReadException; Iceberg: a validation failure on the MERGE). That one is safe: the stream retries against the new snapshot.

What to do. One writer per partition, decided by the partition's state, not by the DAG:

  • Today's partition is owned by the stream. Batch runs on closed days only, and the boundary is the partition value, not wall-clock midnight.
  • If batch must rewrite today, it takes a lock the stream respects, or both go through one writer queue that serialises commits.
  • If you can't lock, make the overwrite declare its partition and validate conflicting appends, so it fails loudly instead of deleting. Then fix the retry: refresh the extract, or replay the stream's rows from the source.

Failure modes to name. Silent loss (no validation, stale extract): detected by comparing the partition's row count or checksum before and after the batch commit. Conflict storm (validation on, both sides retrying): detected by commit-failure rate per table. Duplicates, if the stream retries a MERGE after a conflict without an idempotency key in the merge condition, it re-inserts rows it already wrote.

"They usually don't overlap" is not a lock. Today has one owner.

Curated: · Written: · Reviewed:

Q&A-57Two Spark jobs MERGE the same Iceberg table. One fails with a commit conflict. What should the loser do?

The loser re-runs the MERGE against the new snapshot. It does not overwrite, and it does not try to write last to "win." Assume both jobs target the same table and the same partition, and that the catalog provides an atomic commit.

Iceberg commits are optimistic. A writer reads the current snapshot, computes new data files, then swaps the table metadata pointer with a compare-and-swap in the catalog. Two writers that both read snapshot S produce two candidate commits; the catalog accepts one and rejects the other with CommitFailedException. The rejected writer's data files were computed against S, which is now stale: the winner's rows are not in them.

Iceberg retries the metadata commit a bounded number of times (table properties commit.retry.num-retries, default 4; commit.retry.min-wait-ms 100; commit.retry.max-wait-ms 60000; commit.retry.total-timeout-ms 1800000). That retry re-validates the commit; it does not recompute the loser's data files. A MERGE rewrote whole files from the stale read, so validation fails and the job dies. The fix is at the job level: catch the failure and re-run the MERGE, which re-reads the table at the new snapshot.

import time
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

MERGE = """
MERGE INTO prod.orders t
USING staging.orders_cdc s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
"""

def merge_with_retry(sql, attempts=5, base_delay=2.0):
    for i in range(attempts):
        try:
            spark.sql(sql)
            return
        except Exception as e:
            if "CommitFailedException" not in str(e) or i == attempts - 1:
                raise
            time.sleep(base_delay * (2 ** i))

Trace: attempt 0 plans against snapshot S, writes files, commit rejected → sleep 2 s. Attempt 1 re-plans against S′ (the winner's snapshot), re-reads staging.orders_cdc, writes new files, commit accepted. Both change sets are present. The retry is safe only because the MERGE is idempotent on order_id; an append-style write would duplicate rows on attempt 1.

What not to do, and why it is worse than a failed job:

Loser actionWinner's rowsLoser's rowsResult
re-run MERGEkeptappliedboth change sets present
INSERT OVERWRITE partition from stale readdeletedappliedwinner's rows gone, no error raised

INSERT OVERWRITE (and dynamic partition overwrite) replaces the partition with whatever the loser read. It commits cleanly, so nothing alerts. This is the failure that costs data: the loser overwrites the day from an 08:00 snapshot after the winner merged 11:00 CDC, and the midday rows are gone with a green DAG.

Two things make retries cheap enough to be the default. Isolate writers by partition where the workload allows: disjoint partitions rarely conflict, so the loser's retry almost always succeeds on the first attempt. And pick a catalog that gives a real atomic swap: Hive metastore via locking, Glue via conditional writes, or a REST catalog. A catalog without atomic commit turns a conflict into a silent lost update, and no retry loop fixes that.

Curated: · Written: · Reviewed:

Q&A-92You filter amount > 0 in WHERE and then ROW_NUMBER to pick latest. Why do you still keep a $0 row as latest?

Short answer: WHERE is evaluated before window functions in the same query block. If the amount > 0 filter really sits in the inner WHERE, the $0 row is deleted from the partition before ROW_NUMBER ever sees it, so you don't keep a $0 as latest, you keep the most recent positive row, which may be stale. A $0 row shows up as latest only when the filter runs after the window function, in the outer query. That placement is the one you want when a $0 is a real correction.

Why the order decides it. SQL's logical evaluation order is FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY → LIMIT. WHERE prunes rows before the window is computed, so it changes the set ROW_NUMBER ranks over. Filtering after the window leaves the partition intact and only discards rows from the result.

Trace. The ledger is append-only; each correction is a new row. Order 7:

order_idamountupdated_at
7402024-03-01
702024-03-05

The 03-05 row is a void.

Filter inside WHERE:

SELECT order_id, amount FROM (
  SELECT order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) rn
  FROM orders
  WHERE amount > 0
) t WHERE rn = 1;

WHERE drops the $0 row first. The partition holds only (7, 40). ROW_NUMBER gives it rn=1. Result: 40. The void is invisible; the query reports a stale amount as current.

Filter after the window:

SELECT order_id, amount FROM (
  SELECT order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) rn
  FROM orders
) t WHERE rn = 1;

Partition holds both rows. ROW_NUMBER: (7, 0) → rn=1, (7, 40) → rn=2. The outer filter keeps rn=1. Result: 0. The void is current.

Which one you want. If a $0 row is a real event (a void, a cancellation, a full refund) then the latest row is the truth and you must rank before filtering, or not filter at all. If $0 rows are noise that should never be current, filter in WHERE and accept that "current" means "latest positive"; that is a business rule, not a SQL detail, and it should be written down.

Failure mode. The WHERE-first version silently understates. In one case a void on the latest correction was dropped, an older $40 row was reported as current, and refunds were understated by $620,000 for a week before anyone reconciled against the ledger. Detection: compare the row count of the ranked result against the raw latest-per-key count; a mismatch means WHERE removed rows that were the latest for some key.

Test both directions. Send a later $0 (void after a positive) and an earlier $0 (positive after a void). The first catches the stale-positive bug; the second confirms you don't resurrect a voided row.

Curated: · Written: · Reviewed:

Q&A-43One malformed JSON blocks a Spark micro-batch of 200,000. Where should that record go?

Where it goes: into a dead-letter sink keyed by (topic, partition, offset), carrying the raw bytes, the parse error, and the schema version, and the micro-batch commits the other 199,999. But check first whether Spark is even throwing. from_json defaults to mode = PERMISSIVE, which returns nulls for malformed input instead of raising. If your batch is dying, the throw is coming from FAILFAST, from a cast into a non-nullable column, or from a UDF, and that decides where the try/catch goes.

Why failing the batch never recovers. A Kafka source commits offsets only when the micro-batch completes. A task that throws is retried spark.task.maxFailures times (default 4); after that the micro-batch fails and the query stops. Restart from the checkpoint and it re-reads from the last committed offset: the same poison record, the same throw. Loop. Meanwhile the watermark doesn't advance, so state-store rows for the window accumulate and executor heap grows with them.

The shape. Parse once, split on the parse result, write the DLQ first:

schema = "event_id STRING, ts LONG, payload STRING"

parsed = (raw.select(
        F.col("partition"), F.col("offset").alias("kafka_offset"),
        F.col("value").cast("string").alias("raw_value"))
    .withColumn("j", F.from_json("raw_value", schema))
    .select("partition", "kafka_offset", "raw_value", "j.*"))

def write_batch(df, epoch_id):
    df.persist()
    df.filter(F.col("event_id").isNull()) \
      .withColumn("error", F.lit("json_parse_or_missing_event_id")) \
      .write.mode("append").saveAsTable("events_dlq")   # DLQ first
    df.filter(F.col("event_id").isNotNull()) \
      .write.mode("append").saveAsTable("events")
    df.unpersist()

parsed.writeStream.foreachBatch(write_batch) \
      .option("checkpointLocation", "s3://ckpt/events") \
      .trigger(processingTime="1 minute").start()

foreachBatch is not transactional across the two writes: DLQ first means a crash between them duplicates sink rows on retry rather than losing them. Make the sink write a MERGE on event_id if duplicates matter. Two separate writeStreams off the same source would each open their own Kafka consumer and read the topic twice; one foreachBatch keeps the split in a single batch.

Worked example. Source arrives at ~3,000 records/min, 1-minute trigger, maxOffsetsPerTrigger = 200000. One truncated JSON in a batch:

HandlingSink rowsDLQ rowsLag after 14 days
fail the micro-batch00~60,500,000
per-record DLQ199,99910 extra

14 days × 1,440 min × 3,000/min ≈ 60.5M records never committed, because every batch after the poison one re-reads the same offset.

Failure modes and how you see them.

  • DLQ depth is the wrong alarm. A schema regression makes 100% of records "malformed": depth climbs either way. Alert on DLQ rows/min ÷ source rows/min; above 1% for 5 minutes is a schema break, not poison.
  • Replay loops. A record that still fails after the schema fix re-enters the DLQ forever. Carry an attempts counter and stop at 3.
  • Silent loss. PERMISSIVE mode turns malformed into null; filter nulls without writing them and the sink just gets smaller. Reconcile topic offsets against sink + DLQ counts hourly.
  • State-store growth. If the batch does fail, watch numRowsTotal in StreamingQueryProgress: the watermark stall shows up there as memory.

When not to DLQ. If the stream is an all-or-nothing ledger, a malformed record means the batch is untrustworthy and failing is correct. The better fix is upstream: Avro or Protobuf with a schema registry rejects malformed payloads at produce time, so Spark never sees them. That doesn't help with bytes already in the topic or with schema-evolution mismatches, which is why the DLQ stays.

Curated: · Written: · Reviewed: