WebstaurantStore automated a dbt staging layer covering more than 700 sources in 90 days, using the same three-person data engineering team and no additional headcount. Senior business intelligence engineer Cathy Huang shared the details.

The work was prompted by a legacy SQL Server data warehouse that had reached its limits. It depended on more than 1,300 tightly coupled stored procedures that functioned largely as black boxes, and with data volume growing roughly 40 percent a year, problems spread: overloaded batch jobs risked running past their overnight windows into business hours, engineers hand-coded staging logic table by table, and the opacity of the old procedures made automated testing nearly impossible.

The new pipeline automates ingestion from landing through to a bronze layer. Instead of engineers manually mapping cryptic, abbreviated column names from legacy OLTP sources, raw system metadata goes to Snowflake Cortex LLM functions, which suggest normalized, readable column names and business definitions. Those suggestions land in an intermediate review table. Engineers audit and override them there, and the dbt Jinja macros that compile production model SQL and YAML documentation read only from that human-approved table, never directly from raw AI output.

The checkpoint let the team move quickly while keeping control, onboarding over 700 sources in 90 days - roughly a tenfold increase in ingestion speed by their own measure. Huang credited dbt resident architect Jessica Stayton with helping solve a difficult case: a major source with no watermark for tracking changes. As Huang put it, automation only pays off if the people relying on it can still trust what it produces.

Source: dbt Labs Blog