Fifty-minute CI, colliding jobs, and an append model counted twice
Cobalt SaaS runs dbt Cloud with four jobs: nightly-prod at 02:00 UTC (dbt build), hourly-metrics on the hour, six-hourly, and ci on every pull request. CI runs dbt build across the whole project and takes 50 minutes, so reviewers merge without waiting for it. Twice last week the hourly job and the nightly job overlapped on the same incremental tables, and Snowflake returned Unable to acquire lock followed by statement timeouts, leaving two models half-written. On 12 March an engineer ran the prod job manually at 09:20 to backfill a single model; that model is incremental with incremental_strategy='append' because merge had been slow, so the morning volume was counted twice and the finance dashboard showed a 190% revenue spike. The project has 780 models, 6 threads per job, and one XS warehouse per environment.
1. Rewrite the CI job so it validates a pull request in a few minutes without building all 780 models. What configuration makes the selection correct, and what has to be true for it to be safe?
Read the worked answer
CI is slow because it builds 780 models to prove that four changed. Use state-based selection with deferral so the PR builds only what it touched and its downstream, while everything else resolves to the production relations.
The command is effectively:
dbt build --select state:modified+ --defer --state ./prod-artifacts --fail-fast
In dbt Cloud this is the 'Run only modified models' job setting; the mechanics are the same.
Three preconditions make it correct: the prod-artifacts directory holds the manifest.json from the most recent production run (downloaded as a job artifact, never generated from the PR branch); the CI target writes into a scratch schema so deferred refs point at prod while new builds land in CI; and the selection is scoped deliberately - one + is usually enough, but a change to dim_customer will still pull a large closure.
Further tuning: cap the PR job at state:modified+1 and let the merge-to-main job build the deeper closure; --fail-fast stops at the first broken model instead of completing the graph; exclude snapshots from the PR job since they are append-only and slow; cache dbt deps and rely on partial parsing, which is meaningful at 780 models; give CI its own XS warehouse so it never competes with prod.
Fifty minutes to under eight is realistic. If reviewers were merging without waiting, make the check required only after it is fast - a slow gate that people bypass is worse than no gate.
2. Two jobs keep colliding on the same tables. Diagnose the lock errors and describe the structural fix, distinguishing scheduling problems from scoping problems.
Read the worked answer
There are two problems wearing one costume.
Scheduling. The hourly and nightly jobs must not be able to run concurrently over the same relations. Fix that in the orchestrator, not by hopeful spacing: model the jobs as a DAG with a real dependency (dbt Cloud job chaining, or an Airflow sensor/dataset trigger) so nightly does not start while hourly holds the tables, and hourly waits rather than races. If nightly routinely overruns into the hourly window, the schedule is wrong and the dependency is the safety net, not the fix.
Scoping. Even with dependencies, two jobs writing the same incremental table is fragile. Split by tag: hourly runs --select tag:hourly and owns only small, fast models; nightly owns everything else. Two jobs should not share a merge target unless they are chained to each other.
On the lock error itself: Snowflake takes table-level locks for DML, so two MERGE or DELETE statements against one table produce Unable to acquire lock and statement timeouts, and dbt does not retry them. The model is left missing or partially written - which is why two models ended up half-done. Make that state loud rather than silent: a not_null and unique test on the target key with severity: error, scheduled immediately after the merge job, so a half-written table fails the pipeline instead of feeding a dashboard.
Finally, restrict who can trigger prod jobs - service tokens for schedulers, approved triggers for humans - so the next incident is not caused by a well-meaning manual run.
3. A manual rerun of an append-strategy model doubled the day's rows. How do you recover, and what would make this model safe to rerun at all?
Read the worked answer
incremental_strategy='append' makes correctness depend on exactly-once execution, which no scheduler can guarantee. Every rerun - manual, retried, or overlapping - reinserts the rows. Unlike a merge on a genuine unique key, there is nothing to deduplicate against, so the second 09:20 run simply added the morning again.
Recovery, in order. First quantify: group the target by the partition column, compare the affected day to source, and confirm the duplicate set equals the rows the manual run added. Capture that run's invocation_id from run_results.json, and check whether the model carries _loaded_at or an invocation id column to make the offending rows addressable. Second, repair by partition: delete from ... where order_date = '2026-03-12' followed by a bounded rebuild, or a parameterised --full-refresh --vars limited to that window. Do not rerun append and hope. Third, tell finance before they ask, with a corrected number.
Structural fix: append is defensible for immutable event data with a natural partition, not for a recomputable aggregate. Either move the model to merge with a real unique_key (order_date plus account_id) so a rerun is idempotent, or keep append semantics but switch to delete+insert keyed on the partition column, where the adapter supports it, making reruns idempotent at partition granularity.
Guardrails: a unique_combination_of_columns test on the key at severity: error; a row-count variance test that alerts above 150% of the trailing average; and prod job permissions restricted to service tokens, with backfills run through a dedicated parameterised backfill job.