Skip to content

DATABRICKS 2026-10-06

Read original ↗

Scaling and Operating a Large dbt Project on Databricks: IFCO's Data Team

Summary

IFCO runs one of the world's largest reusable-packaging pools — hundreds of millions of crates and pallets moving fresh produce across 50+ countries, tracked through a full wash-sort-reuse life cycle. Its data platform team, with Databricks Forward Deployed Engineering, rebuilt the semantic layer (governed dbt models that turn billions of raw tracking events — barcode scans, RFID reads, GPS/Bluetooth/temperature tracker pings — into business KPIs: cycle time, loss, breakage, wash cost, pool size). The transformation logic stays in dbt and compiles down to concrete Delta Lake read/write behaviors on Databricks. By getting incremental settings right on the right data grain — and by diagnosing from the actual executed query plan rather than dbt compile output — they cut the core semantic-layer job's daily runtime by >60% (≈7h → 2h20m), retired a nightly full refresh, and reduced daily compute cost 58%. The debugging sequence was then packaged as a repeatable AI-agent skill so the diagnosis scales with the number of models, not the number of engineers who remember how to do it.

Why the problem is hard (three properties)

  1. Scale. Billions of tracking events/day. Consolidation collapses repeated pings per asset into far fewer activity rows, but the KPI tables are still large enough that rebuilding from scratch is expensive.
  2. Unpredictable late arrivals. Most observations land in an expected window, but some feeds lag by weeks and a few by months on no fixed schedule. A pipeline that assumes today's data is the full picture of yesterday "will quietly report wrong history." (A canonical late-arriving-data problem.)
  3. Historic reconciliation. Asset activity is a sequence. A late scan dropped into the middle of an asset's timeline changes everything the pipeline already concluded after it (where the asset went next, when its cycle started, which KPI bucket it landed in). A late arrival forces the pipeline to recompute already-published state, not just append a row. Goal: emit a good estimate quickly and converge to truth as late data lands, without reprocessing everything each night.

Key takeaways

  • A dbt incremental model is, underneath, a set of Delta read/write behaviors. Most of the win came from one principle: make each run touch as few rows as possible, and cut them as early as possible — the read side first, because every row avoided never reaches the expensive per-asset sorts, shuffles, and writes downstream. (Source: this article)
  • Cluster on the columns you filter and join on. Liquid clustering keyed to the grain each model is queried by (for asset activity: asset id + event date) lets the engine skip files instead of scanning them — the enabler for every other technique (data skipping).
  • Choose the incremental strategy deliberately. The choice follows two questions: does each row have a stable key, and are you updating rows in place or replacing a group at once?
  • merge is the default for keyed, dedup-heavy upserts. Keyed on the real grain (asset_id, event_date_time) the equi-join predicate turns on dynamic file pruning: incoming batch key values skip target files that can't contain a match, so the write touches only the slice it changes. Clustering on the same keys the merge matches on keeps pruning tight. (DBT_INTERNAL_DEST / DBT_INTERNAL_SOURCE are dbt's aliases for target table and incoming batch in the generated statement.)
  • A row-hash guard — a matched_condition comparing a surrogate hash of each row — skips rewriting rows that did not actually change, saving writes and keeping the downstream change feed clean (reduces write amplification).
  • delete+insert deletes a whole group of rows by key and reinserts it — simpler when a run re-derives a group as a unit and rows carry no stable identity, at the cost of rewriting the group even where nothing changed. At very large volumes, benchmark the two rather than assume.
  • Bound the write to a recent window. The same predicate mechanism, used as a time bound instead of an equi-join, restricts the MERGE to match a recent slice of the destination rather than the whole table. Crucially, the predicate keys on when a row was ingested, not when the event happened — so a months-old event is still caught as long as it landed recently. The window only has to cover the gap between data landing and this job processing it. Set it too narrow and late data is silently skipped: it does not error, it just never gets processed.
  • Only recompute what changed. Models scope work to the assets touched by new or late data, identified from an ingestion watermark, and read a window wider than they write so late events are captured without a full refresh. (The practical shape of incremental view maintenance.)
  • Keep Delta tidy. Heavy incremental tables turn on optimized writes + auto-compaction, or hand maintenance to Predictive Optimization, so frequent merges don't leave a small-file read tax.
  • The discipline is applying these on the right grain, then confirming — from the actual query plan — that the engine really prunes rather than silently scanning.

Worked example — the consolidation model (85% of runtime in one sort)

The busiest model consolidates observations from every tracking technology into one location-aware stream per asset, computing movement with window functions partitioned by asset, ordered by event time. When an observation carries no explicit location, it falls back to Databricks SQL's built-in H3 functions, which map lat/long to a hexagonal grid cell so "same place" is a cheap cell-ID + grid-distance comparison instead of repeated geographic-distance math.

Diagnose from the real plan, not dbt compile. dbt compile renders the model's SELECT with refs resolved, but for an incremental model that is not the statement Databricks executes — behind it dbt generates temporary views, scans of the destination, and the final write. The only way to find where time and memory go is to read the actual executed query plan, stage by stage, from query history. (This is measurement-driven micro-optimization applied to a query engine.)

Read that way, the plan was damning: scanning billions of rows, spilling hundreds of GB to disk, ~85% of time in a single per-asset window sort + shuffle — in effect rebuilding the whole table every run. Three root causes:

  1. The "changed" asset set never shrank. An upstream timestamp was regenerated on every run instead of carried through from source, so almost every asset looked new → "when everything looks dirty, an incremental run quietly becomes a full refresh."
  2. The per-asset recompute had no bound. Window functions looked all the way back through each asset's history, dragging years of history through the sort even for a small changed set.
  3. The window stage computed columns the final query never used — including a second, forward-looking window whose entire output was discarded (dead work).

Each fix follows directly from its cause: carry the real ingestion timestamp through upstream models so the changed set reflects genuinely new data; bind the recompute to a recent window; drop the unused columns and the forward window; cluster on the grain the model is queried by; and run the whole graph as parallel per-model tasks on serverless. Together: >60% runtime cut and the nightly full refresh removed.

From one debugging session to a repeatable agent skill

The diagnosis sequence — read the real executed plan, check read side and write side, trace each symptom to a root cause — is not specific to this model. It was packaged as an agent skill: a playbook an AI agent runs on demand. It pulls the actual statement family from query history (not dbt compile), reads scan-side metrics (files pruned, rows read, spill) and write-side metrics (rows written vs. deleted — amplification only shows on the write side), and checks the same three failure classes: a changed-set that never shrinks, an unbounded per-asset recompute, and wasted work. The output is a report, not a silent fix: each finding is stated with evidence (rows scanned, spill bytes, plan node), paired with a proposed change, and nothing is applied until approved; the same before/after metrics are then re-measured to close the loop. The gain is consistency, not novelty — it finds the same class of issue on the next model before it becomes a 60%-runtime escalation.

Running a large dbt project on Databricks Jobs

Databricks Workflows (Lakeflow Jobs) treats dbt as a first-class task type. The naive version runs the whole project as a single dbt task — but it's a black box: one model fails and the entire job fails, with no per-model visibility, rerun, or watch. At scale that's an operational risk.

The fix is to run the dbt graph as individual Databricks tasks, one per model, test, seed, and snapshot. IFCO generates that graph with databricks-dbt-factory, an MIT-licensed open-source library that reads the dbt manifest + a job template and produces a Databricks Asset Bundle job with a task per node. Per-task granularity only pays off if each task is cheap to start — three mechanisms:

  • Notebook task type. A small shared runner notebook triggers dbt through its Python API; dbt works out the SQL and sends it to the SQL warehouse, so the notebook compute crunches no data.
  • Base environments. A pre-built serverless Python environment snapshot already containing dbt-databricks, so each task skips the pip install.
  • Manifest injection. A pre-built manifest is handed straight to dbt (parse skipped), and each task writes artifacts to a private local dir — the difference between minutes of re-reading the project and useful work.

With overhead small, the fan-out gives ops task-level visibility, targeted reruns of only the failed model + its dependents, per-model logging/alerting/ testing, and an extensible runner (load secrets, tag runs with a git SHA, post to Slack). Everything deploys as Asset Bundles through a path-aware GitHub Actions matrix, and per-model runtime + cost are tracked from query tags and system tables into a dashboard with alerts — so a regression shows up in a day, not in a monthly bill.

Testing and quality at scale

  • Every model carries an owner and a uniqueness test; primary keys tested unique
  • not null at error severity; models with real logic (window functions, multiple joins, non-trivial macros) require unit tests.
  • dbt-bouncer blocks commits violating these, alongside sqlfluff on the Databricks dialect; contracts enforced on layers external consumers read.
  • Cost of testing managed too: view-based checks are materialized or folded into fewer passes (a view-based check recomputes the view every run); basic checks become column constraints; tests scoped to incremental data. Locally, developers defer to a production manifest so only changed models build while upstreams read from prod. In CI, unit + sampled data tests run on changed models before merge.

Operational numbers (before → after)

Measure Before After
Daily runtime, core job ≈ 7 hours 2h 20min (~66% ↓)
Nightly full refresh required to keep KPIs correct retired
Rows scanned/run, consolidation model ≈ 25 billion (growing daily) −75% of rows scanned
Daily compute cost — −58%
Assets recomputed/run almost the full pool ≈ 3–5% of the pool
  • Change Data Feed. Delta's Change Data Feed lets a downstream model read just the rows that changed upstream — shrinking reconciliation from "scan a recent window" to "process the exact rows that moved."
  • Lower-latency ingestion. Move the once-a-day load to Auto Loader or a low-latency Lakeflow Connect connector — freshness from a day to minutes without re-architecting downstream.
  • Declarative streaming transforms. Run the same logic continuously as a Lakeflow Declarative Pipeline reading a stream, with stateful processing handling per-asset reconciliation as events arrive. Gating question is business need, not technology.

Caveats

  • Vendor-authored case study (Databricks + its Forward Deployed Engineering on a customer, IFCO). Numbers are self-reported; no reproducible configs, cluster sizes, or absolute cost figures given.
  • The "skill" is described conceptually; no code, prompt, or agent-framework details are published.
  • Techniques (merge vs delete+insert, dynamic file pruning, liquid clustering, ingestion-watermark windows) are Databricks/Delta-specific in syntax though the principles (touch fewer rows; bound recompute by ingestion time; diagnose from the executed plan) generalize to any incremental-processing engine.

Source

Last updated · 854 distilled / 2,448 read