Repair run duplicated claims, and an unreproducible 45-day backfill
An insurer runs one Lakeflow job with 26 tasks: 4 ingestion tasks, 12 parallel silver_* notebook tasks, a for-each task fanning out to 8 SQL tasks, and 2 gold tasks. The job is triggered on file arrival, runs every 2 hours, and is configured with max_concurrent_runs = 1 and queueing enabled. The silver_claims task intermittently fails on a transient SQL warehouse timeout. The on-call engineer clicked Repair run, the run succeeded, and finance reported duplicated claim lines that evening. Separately, a pricing-rule fix had to be replayed across the last 45 days of partitions. An engineer copied the notebook into a _backfill folder, edited the hard-coded date constant in the copy, and triggered it manually from the workspace UI. Two weeks later nobody could reproduce the numbers from that backfill, and three days of the live schedule overlapped with it.
1. What does Repair run actually re-execute, and why did it produce duplicates? What property must the tasks have before repair is safe?
Read the worked answer
Repair run re-executes only the tasks that failed or were skipped, plus everything downstream of them, using the original run's parameters and a new run identifier. It never reruns the tasks that already succeeded. So the ingestion and the other eleven silver tasks were not repeated - silver_claims and its two gold descendants were.
Duplicates therefore mean silver_claims is not idempotent: it appends a batch of claim lines, and the repaired attempt appended the same lines a second time. Repair is only safe when every task's write is idempotent - MERGE on a claim line business key, INSERT OVERWRITE on the affected partition, or a replaceWhere predicate covering the batch. The gold tasks downstream inherit the same requirement, since they were also re-executed.
Two follow-on changes: give silver_claims task-level retries with a backoff so a transient warehouse timeout heals without a human, and record the batch identifier in a control table so a repeated batch can be detected and refused. Then add a duplicate check to the gold reconciliation rather than trusting the operator to notice.
2. Critique the backfill approach and design a reproducible replacement for replaying 45 days.
Read the worked answer
Copying a notebook into _backfill and hard-coding a date fails on three counts: the code has no lineage (it exists only in the workspace, not in Git), the constant is invisible to the next person, and the manual trigger had no concurrency control, so it collided with the live schedule on the same Delta tables.
Replacement: parameterize the pipeline on a run_date or start_date/end_date pair, deployed from a Databricks Asset Bundle so the backfill references a specific commit rather than a workspace copy. Wrap the range in a for-each task that iterates over the dates with a bounded concurrency (for example 5) so the SQL warehouse is not saturated, and set max_concurrent_runs = 1 on the job so the backfill and the live schedule cannot write simultaneously - or better, write to a partition-overwrite pattern so they can coexist.
Log every invocation's job run id, parameters, and commit SHA to an audit table; that record is what makes the numbers reproducible two weeks later. And when the fix touches only some partitions, scope the range to exactly those instead of blindly replaying 45 days.
3. With a file-arrival trigger, `max_concurrent_runs = 1`, and queueing enabled, what happens when files land while a run is in progress? Is queueing the right setting here?
Read the worked answer
With concurrency capped at one and queueing enabled, an arrival during an active run does not start immediately - it waits for a free slot. Depending on how far behind the job is, the operator sees queued runs rather than skipped ones, which feels safer but hides a backlog problem. Each queued run also carries the state captured when it was triggered, so a queue of stale runs can execute in sequence long after the files were superseded.
For an Auto Loader ingestion task this queueing is mostly wasteful: the stream is checkpointed, so the next run picks up every file that arrived since the last checkpoint whether or not intermediate runs executed. Queued duplicates add compute cost and prolong contention on the same Delta tables without improving completeness.
The stronger pattern is max_concurrent_runs = 1 without queueing, so an arrival during an active run is dropped and the following run absorbs all pending files in one larger microbatch. Then measure end-to-end lag from file arrival to bronze visibility. If the lag is still too high, the fix is trigger frequency and the ingestion path itself, not a deeper queue.