Ecolab cuts legacy SQL sixfold with Datafold's AI migration agent and dbt

A base model that grew too complex

Ecolab makes hygiene, food safety and water treatment products used at hotels, restaurants, hospitals and manufacturers worldwide. Its data "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. According to the case study, it became complex enough that Ecolab created a dedicated "firefighter" role 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 deal with late-arriving data and surrogate keys. Snowflake tasks calling JavaScript stored procedures orchestrated all of it. Because the system reprocessed only new or changed rows, older records quietly fell out of sync with each other, which created 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 also meant moving from biweekly manual releases to automatic, ad hoc ones, and dropping long code freezes.

How the migration agent works

Datafold's agent first maps the full lineage, codebase and semantic layer. It then pulls in institutional knowledge from wikis and meeting recordings to fill in context that was never written down. The legacy logic is translated into more than 40 separate dbt projects with clear ownership, with dbt Mesh handling the cross-project pieces.

Every translated model is checked against production data through automated data diffing. 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 is resolved.

The results so far:

• For one flagship dimension, the model count fell from 409 to 170, with roughly 70,000 lines of code and 1,000 data connections removed.
• Across the whole migration, Ecolab sees roughly 6 times less code and 4 times fewer data connections than the legacy system.
• The new process already looks SOC 2-ready, thanks to Git-based version control and automated testing and deployment, even though compliance was not the original goal.

Winning over data owners and lessons learned

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

The team shared several lessons. Migrations run more like an agile process than a waterfall one, because the first working dbt project immediately surfaces new naming and structure requests. Small cosmetic changes such as renaming a column are expensive for an agent, since each can burn multiple model calls, so it pays to batch them until the data is verified correct.

Kaczmarek said he was initially a bottleneck himself, reviewing everything by hand out of what he called "trust issues," and that he is still learning to let the process carry more of that weight.

What comes next

Cutover is 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. The figures are Ecolab's own results as reported by dbt Labs, and the final production switch has not yet happened.

For teams facing large, poorly documented SQL estates, the case describes an approach that pairs automated translation with row-level verification and human review, and treats the process as iterative rather than a one-shot conversion.