WebstaurantStore Uses Snowflake Cortex AI to Automate dbt Staging for 700+ Sources
A three-person data engineering team at WebstaurantStore automated a staging layer for more than 700 sources in 90 days without adding headcount, using Snowflake Cortex LLM functions and dbt. According to the team, ingestion speed rose roughly tenfold.
- A three-person WebstaurantStore team automated staging for more than 700 sources in 90 days without adding headcount.
- The old SQL Server warehouse used more than 1,300 tightly coupled stored procedures, and data volume grew roughly 40 percent a year.
- Snowflake Cortex LLM functions propose readable column names and business definitions from raw metadata.
- Suggestions go to a review table where engineers audit or override them, and dbt Jinja macros read only from that approved table.
- The team measures the result as roughly a tenfold increase in ingestion speed.
A legacy warehouse that hit its limits
The project began with a legacy SQL Server data warehouse that had reached its limits, according to Cathy Huang, senior business intelligence engineer at WebstaurantStore, as described in a dbt Labs blog post. The system relied on more than 1,300 tightly coupled, effectively black-box stored procedures.
As the business grew, data volume rose roughly 40 percent a year. The post lists several resulting problems:
- Overloaded batch jobs risked running past their overnight windows and into business hours.
- Engineers hand-coded staging logic table by table to keep up.
- The opacity of the old stored procedures made automated testing nearly impossible.
How the automated pipeline works
The team's solution automates ingestion from landing through to a bronze layer. Engineers previously had to manually map cryptic, abbreviated column names from legacy OLTP sources. In the new setup, the pipeline feeds raw system metadata into Snowflake Cortex LLM functions, which propose normalized, readable column names and business definitions.
Those suggestions do not go straight into production. They land first in an intermediate review table, where engineers audit each one and override it when needed. The dbt Jinja macros that compile the production model SQL and YAML documentation read only from that human-approved table, never directly from the raw AI output.
Speed without losing control
According to the post, this checkpoint let WebstaurantStore move quickly while keeping control over what reaches production. The team onboarded more than 700 sources in 90 days, which it measures as roughly a tenfold increase in ingestion speed. The work was done by the same three engineers who built the framework.
Huang also credited Jessica Stayton, a dbt resident architect, with helping solve a difficult case: a major source that had no watermark to track changes by.
What it means for data teams
The case shows a pattern for teams facing large backlogs of legacy sources: use an LLM to draft the tedious naming and documentation work, but route the output through a human-approved layer before any code is generated. In the post, Huang put the principle this way: automation only pays off if the people relying on it can still trust what it produces.
The figures come from WebstaurantStore's own account of its project and are not independently verified in the source.
