Leading E-Commerce Company
8
DAYS
[ abstract ]
A leading e-commerce company moved 400 dbt models from Snowflake to Iceberg tables on open-source Spark in 8 days. An agent rewrote every model, each one had to match Snowflake's own numbers on a 4.20% scale model, and matched models ran beside Snowflake on full data before the switch. None of the 400 drifted.
| from | Snowflake, 400 dbt models |
| to | Iceberg tables, OSS Spark |
| scale model | 4.20% of the bytes, whole users |
| checked per model | row counts, sums, nulls, distributions |
| wall clock | 8 days |
| merged as | 1 PR |
[ context ]
The SQL wasn't the scary part. What sat on top of it was: the finance P&L mart, the growth dashboards, and the attribution models marketing reads every morning. If a number moved during the migration, someone outside the data team would notice first.
Migrations like this usually run for a quarter. People rewrite models by hand, spot-check a few tables, and then argue about a cutover date. The team wanted a different rule: nothing switches until it matches.
[ method ]
- 01
Build the scale model
Hash on user_id, keep 4.20% of users and every row they touch in every table. Full history, joins intact, so per-user numbers stay exact.
- 02
Pick the order
Their team chose it: staging first, then dimensions and facts, finance and attribution last. Nothing moved until everything it read from had matched.
- 03
Rewrite and check
The agent translated each model from Snowflake SQL to Spark SQL and ran it on the scale model. If the numbers didn't match Snowflake's, it got the diff back and tried again.
- 04
Run in parallel
Matched models ran on full data next to Snowflake for the rest of the migration, so volume problems had a chance to show up.
- 05
Switch
The day the last model matched was the cutover date. One PR, reviewed by their team.
[ the run log ]
Match log. All 400 models.
[ what missed, and why ]
Most models matched in two attempts. The ones that took longer failed on the same handful of dialect differences, which is roughly what you'd expect from Snowflake to Spark:
attempt 01
stg_orders
OSS Spark has no QUALIFY, so the dedupe step got dropped in translation.
showed up as · row count 3.1% high
fix · rewrote it as ROW_NUMBER() in a subquery
attempt 03
fct_sessions
Timestamps without a time zone met Spark's session time zone. Sessions near midnight landed on the next day.
showed up as · daily counts off at day boundaries
fix · pinned the session time zone to UTC and cast explicitly
attempt 09
fct_revenue
Integer division. Snowflake returns a number with six decimal places, Spark returns a double.
showed up as · sum(revenue) off in the ninth decimal
fix · cast to DECIMAL(38,2) before aggregating
attempt 27
fct_attribution
Snowflake and Spark put NULLs at opposite ends of a sort by default, so ROW_NUMBER() picked a different last touch when a timestamp was missing.
showed up as · channel distribution shifted, KS 0.012
fix · made NULLS LAST explicit
[ result ]
| models migrated | 400 |
| models with drift | 0 |
| wall-clock | 8 days |
| attempts to match | 2 – 40 |
| PRs merged | 1 |
[ who did what ]
Their data platform team picked the order, read the evidence for each model instead of the SQL, and ran the cutover. They didn't rewrite models by hand.
“We stopped arguing about the cutover date. The log was the cutover date.”
[ notes ]
- n1
A scale model catches wrong numbers. It can't catch problems that only exist at full volume, like skew or memory pressure. That's why matched models still ran in parallel on full data before the switch.
- n2
The two slowest models (fct_attribution and mart_growth, up to 40 attempts) both depend on window functions over sessions. We now handle NULL ordering and session time zones before the first attempt.
[ read next ]
Working on something like this? Chat with Founders →