IFCO Cuts Core dbt Job Runtime by Over 60% on Databricks

Billions of tracking events, late data and history to correct

IFCO operates reusable plastic containers (RPCs) that carry fresh produce from growers and packers to distribution centers and retailers. The crates then return to service centers to be washed, sorted and sent out again, across more than 50 countries. Every crate and pallet is tracked, and that data feeds KPIs such as cycle time, loss, breakage, wash cost and pool size.

The company built a semantic layer, a set of governed dbt models that turn raw tracking events into business KPIs. It combines barcode scans on wash lines, RFID reads at dock doors, and battery-powered trackers that report GPS position, nearby Bluetooth beacons and temperature.

Databricks describes three difficulties. Billions of tracking events arrive per day. Some feeds lag by weeks, and a few by months, on no fixed schedule. And because asset activity is a sequence, a late scan changes what the pipeline already concluded about everything after it. The pipeline must therefore recompute state it has already published. The stated goal is to emit a good estimate quickly and converge to the truth as late data lands, without reprocessing everything each night.

Touch as few rows as possible

The post, written with Databricks Forward Deployed Engineering, says most of the gain came from one principle: make each run touch as few rows as possible, and cut them as early as possible. The transformation logic stays in dbt, and each incremental setting maps to a concrete Delta Lake write behavior. The techniques described are:

• Liquid clustering on the columns used to filter and join, such as the asset and the event date, so the engine can skip files instead of scanning them.
• A deliberate incremental strategy. For keyed, dedup-heavy upserts, merge is the default. Keyed on asset_id and event_date_time, its equi-join predicate enables dynamic file pruning, so a write touches only the slice it changes. A row-hash guard skips rewriting rows that did not change. The alternative, delete+insert, is simpler when a run re-derives a group as a unit, but it rewrites the group even where nothing changed.
• A bounded write window. On higher-volume upstream models, a time bound restricts the MERGE to a recent slice of the destination. Because it keys on ingestion time rather than event time, an event that is months old is still caught if it landed recently.
• Recomputing only what changed. Models scope work to assets touched by new or late data, identified from an ingestion watermark, and read a wider window than they write.
• Tidy Delta tables. Heavy incremental tables use optimized writes and auto-compaction, or Predictive Optimization, to avoid a small-file read tax.

The post warns that a window set too narrow causes late data to be silently skipped: it does not error, it just never gets processed.

Reading the executed plan, not the compiled SQL

The busiest model consolidates observations from every tracking technology into one location-aware stream per asset. It uses window functions partitioned by asset and ordered by event time, and falls back to Databricks SQL's built-in H3 functions when an observation has no explicit location. H3 maps each latitude and longitude to a hexagonal grid cell, so "same place" becomes a cheap comparison of cell IDs and grid distance. According to the post, it was by a wide margin the single biggest consumer of runtime.

The team's first step was to look at what actually ran. For an incremental model, the compiled SELECT is not the statement Databricks executes, because dbt generates temporary views, scans of the destination table and a final write around it. The post argues the only way to find where time and memory go is to read the executed query plan stage by stage from query history.

Read that way, the model was scanning billions of rows, spilling hundreds of gigabytes to disk, and spending about 85 percent of its time in a single per-asset window sort and shuffle. In effect, it was rebuilding the entire table on every run.

What it means for dbt teams on Databricks

The summary of the post also says IFCO packaged this plan-based diagnosis into a repeatable skill that runs on every model. It recommends running the dbt project as one task per model on Databricks Jobs using the open-source databricks-dbt-factory, rather than as one opaque job. Databricks says this gives per-model visibility, targeted reruns and enforced testing.

For teams with large incremental dbt projects, the lesson drawn in the source is to apply these settings on the right data grain and then confirm from the actual query plan that the engine really prunes rather than silently scanning.