How Ecolab used an AI migration agent to shrink 84,000 lines of legacy SQL by 6x

Tomasz Kaczmarek, technical lead on the migration project at Ecolab, and Kel, an engineer from migration partner Datafold, presented "From DML soup to dbt structure." Ecolab makes the hygiene, food safety, and water treatment products used behind the scenes at hotels, restaurants, hospitals, and manufacturers worldwide.

Ecolab's "base model" spans more than 400 interdependent dimensions and facts fed by more than 3,000 source systems, built up over years of project-by-project additions. It got complex enough that Ecolab created a dedicated "firefighter" role just to handle production issues.

The legacy system layered stage tables, temp tables, key-mapping tables, and Snowflake streams on top of the core model to handle late-arriving data and surrogate keys, all orchestrated by Snowflake tasks calling JavaScript stored procedures. Because it only reprocessed new or changed rows, older records quietly fell out of sync with each other, creating data quality issues nobody had fully mapped.

Ecolab wanted transparent, well-documented, version-controlled transformation logic instead of logic buried inside a Snowflake table. That meant moving from biweekly manual releases to automatic, ad hoc ones, and dropping the long code freezes that no longer made sense once changes could ship this fast.

Datafold's migration agent starts by mapping the full lineage, codebase, and semantic layer, then pulls in institutional knowledge from wikis and meeting recordings to fill in context that was never written down. It translates the legacy logic into more than 40 separate dbt projects with clear ownership, using dbt Mesh to handle the cross-project pieces.

Every translated model gets checked against production data through automated data diffing, and the agent has to justify any expected differences using the legacy code, the new code, and the original requirements. Anything unexplained loops back through the agent until it's resolved.

For one flagship dimension alone, the rebuild cut the model count from 409 to 170 and removed roughly 70,000 lines of code and 1,000 data connections. Across the migration as a whole, Ecolab is seeing roughly 6 times less code and 4 times fewer data connections than the legacy system.

As a side effect, the new process already looks SOC 2-ready, thanks to the Git-based version control and automated testing and deployment that came bundled with the migration, even though compliance wasn't the original goal.

Data owners were initially wary that any mismatch with the legacy numbers meant the new pipeline was wrong, worried their own users would assume "bad data." Showing them a visual, row-level data diff instead of just a written explanation changed minds quickly, and roughly 90 tables were approved within the first four weeks of review.

The team's lessons: migrations run more like an agile process than a waterfall one, since seeing the first working dbt project immediately surfaces new naming and structure requests. Small cosmetic changes, like renaming a column, are expensive for an agent because each one can burn multiple model calls, so it pays to batch cosmetic fixes until after the underlying data is verified correct.

Kaczmarek admitted he was initially a bottleneck himself, reviewing everything by hand out of what he called "trust issues," and said he's still learning to let the process carry more of that weight. Cutover to the new models is being kept out of scope for this phase; Ecolab plans to switch the underlying tables behind its existing reporting layer only once every dimension and fact has been reviewed and approved.

Watch every keynote and breakout from dbt Summit 2026 on demand. Watch dbt Summit 2026 on demand →