The Same Sentence, Twice

Thursday afternoon, a proposal lands: move the warehouse off BigQuery onto Snowflake. Six months, €240,000 of professional services, and — the sentence everyone finds reassuring — we will run both in parallel until you are confident.

Three people have already checked the part they know about, and all three are right. The engineering lead has run the dbt project through a SQL translator: 172 models, not one fails. The finance lead has the vendor’s claim of 30% off compute. The analytics lead thinks six months is tight but possible.

Nobody has asked whether the translation means the same thing.

The migration is scoped on the repository, because the repository is the thing with a plan-shaped boundary. Count from the other end — every distinct SQL statement the warehouse ran in ninety days (132,120 executions, 1,468 a day):

The repository is 19.39% of the distinct SQL. The threshold for “load-bearing” barely matters, because the distribution is in two lumps: 1,548 statements ran on fewer than five of the ninety days (most ran once), and 1,356 ran on five days or more. 793 of those (58.5%) are outside the repository.

1,356 statements have to be translated, not 563. The plan is costed on the fifth of the corpus that has a git history.

All 172 model statements were transpiled by sqlglot 30.21.0 to four targets. Every one translates; nothing raises. Classify the output instead:

The same project takes 4.14× as many silent changes into Snowflake as into Databricks, from identical source text through identical software. Translation risk is a property of the pair, not of your code.

The rules, each produced by running the construct and reading the vendor’s docs:

• SAFE_CAST becomes a throwing CAST (Snowflake only — the tool emits TRY_CAST for Databricks, Trino and DuckDB, and Snowflake has TRY_CAST). 15 statements.
• DIV(x, y) becomes CAST(x / y AS INT) (Snowflake, Trino). ZetaSQL: “Returns the result of integer division of X by Y.” Snowflake: “Converting a FLOAT value to an INTEGER value rounds the value.” DIV(7,2) = 3; CAST(7/2 AS INT) = 4. 7 statements.
• _PARTITIONDATE survives as an identifier — loud, and therefore free. 8 statements.
• APPROX_COUNT_DISTINCT changes estimator — the name maps, the sketch does not. 7 statements.
• DATE(timestamp) inherits the column’s new type. 8 statements.

The translator is good at syntax and blind to data: every false friend is a construct whose meaning depends on the data rather than the grammar, which is exactly the set no syntactic tool can get right.

And the tool beats the people. 43 of 172 statements order rows without saying where NULLs belong. sqlglot writes the explicit NULLS FIRST/NULLS LAST into every one, because it has a table of each dialect’s default. The analyst retyping a dashboard tile does not.

BigQuery treats NULL as the smallest value: first ascending, last descending. Snowflake’s DEFAULT_NULL_ORDERING is LAST: last ascending, first descending. Opposite on both.

Now the most ordinary model in any warehouse:

select * from (
  select *, row_number() over (
             partition by listing_id order by updated_at desc) as rn
  from stg_listing_version
) where rn = 1

Of 420,000 listings, 7,647 carry a row from the 2019 bulk import whose updated_at was never populated. Under BigQuery’s defaults that row sorts last and is ignored. Under Snowflake’s it sorts first and wins — the current state of those listings becomes their 2019 values.

The project was built twice over one copy of the data, changing nothing but that setting:

GMV is identical to the cent, and not by luck — a sum over facts cannot see how the facts are labelled, which is why 37 of the 48 columns compared are untouched and the one number everybody watches is among them. What moves is every figure that reads an attribute off the dimension: 16 of the top twenty brands, the worst by 82 places.

Snowflake’s TIMESTAMP is an alias resolved through TIMESTAMP_TYPE_MAPPING, whose default is TIMESTAMP_NTZ — “All operations are performed without taking any time zone into account.” sqlglot renders the BigQuery type as TIMESTAMPTZ and gets it right; a person writing the target DDL writes TIMESTAMP.

The company trades in Berlin. Of 1,200,000 orders, 23,925 (1.99%) fall on a different calendar day under the two readings:

• Every single day differs — all 730 of 730 daily totals move, by up to 1.99%.
• The year does not — 2025 differs by €154.53 on €28,822,751.06, which is 0.000536%.

The annual reconciliation will pass; the daily trading number will be wrong in a way nobody can reproduce, because whether it is wrong depends on what time of night the orders came in.

“Run both until they agree” is a stopping rule, and a stopping rule has to be able to fire. Build the project twice on the same engine, same settings, same data, changing only the physical order of the rows in the source table:

11 of 48 columns differ — the same 11 as the two-engine comparison — and 5,139 listings get a different current state. The warehouse does not agree with itself.

The reason: 7,448 listings (1.77%) have two versions written at the identical timestamp by a bulk re-list. order by updated_at desc does not say which of two equal values comes first, so both answers are correct.

Two other irreducible sources, one of them not what people expect:

• Floating-point summation order is not the problem. sum(cents)/100.0 versus sum(cents/100.0) differs on 685 of 731 days — by at most 2.9×10⁻¹⁰ euro. Real, and three orders of magnitude below a cent.
• Approximate counters are. One engine’s own approximate distinct count sits between 0.74% and 36.39% from the exact answer (272,630 against 293,587 buyers). Two engines’ sketches have no reason to agree at all, and nothing fails when they do not.

So the parallel run ends when somebody decides to stop. That decision needs writing down in advance: which objects must agree, to what tolerance, and who signs.

A migration is the largest full refresh the company will ever run, and Lesson 14 established what a full refresh does to anything the pipeline did not produce.

And the instruments. 7 of 8 monitors built in Lessons 06–33 can be ported at all (L27’s slots-per-job reads INFORMATION_SCHEMA.JOBS_TIMELINE, which has no equivalent schema), and the median one needs 365 days of history before it says anything; the longest needs 548. The quarter in which the company is least able to notice that a number has moved is the quarter immediately after it moved every number in the warehouse.

Six months assumes the platform holds still. It changed 320 times last year (Lesson 33). Port at r statements a year over a corpus of N; the platform invalidates a ported statement every time it changes one, λ times a year spread over the corpus:

dP/dt = r − (λ/N)·P ⟹ P(t) = (rN/λ)(1 − e^(−λt/N))

Two readings. The six-month plan requires 12.5 statements ported, run, diffed and signed off every working day for six months. And the ceiling: at 320 ports a year the count asymptotes exactly at the corpus and never arrives; at 120 a year it stops at 37.5% forever. Below the rate at which the platform changes there is no migration, only a second warehouse permanently 62.5% out of date.

The model treats statements as equal and change as evenly spread. Change is not evenly spread, and that is usable: port the stable statements first and the volatile ones last — rework is paid only on what has already been ported.

The whole warehouse costs €43,800 a year (100 reserved slots). The quote is €240,000.

• The quote alone is 5.48 years of the entire warehouse bill.
• Against the claimed 30% compute saving (€13,140/yr), it pays back in 18.3 years.
• If the new platform’s compute were free, it would still take 5.48 years.

Whatever the reason for this migration is, it is not the bill — so the business case has to be written in the thing it actually is. This one was not.

Internal labour, published as a sweep rather than an estimate:

€240,000 buys 136 minutes per load-bearing statement — two and a quarter hours to find it, read it, understand what it was for, translate it, run it, diff it against the old answer and get it signed off. Eighteen months of running both costs €118,224 on top.

The debate is between the first and the last. The interesting one is the fourth: 612 tiles compute 47 distinct measures, so the porting surface falls 41.7% and the dialect stops being written in 612 places. It is also the only option that removes the silent changes rather than relocating them — and its price is honest: deciding what the 47 measures mean is the project of Lesson 20, where one word had 864 defensible definitions.

The third option sounds like it solves this and does not. It removes the second copy of the data. It removes 0 of the 29 silent divergences, because those live in the dialect and not in the storage.

1. How many distinct SQL statements did the warehouse run last quarter, and what share are in the repository? A guess is the first piece of work.
2. For the pair of engines we are considering, which constructs translate cleanly and mean something different? Name three.
3. What is the stopping rule for the parallel run, as a list of objects, tolerances and names — and who signs each line?
4. Which of our monitors needs history to work, and what is the plan for the months when none of them do?
5. Which tables hold history the sources cannot reproduce — snapshots, manual corrections, erasures, hand-typed reference data — and are they on the copy list rather than the rebuild list?
6. What does the business case say if the compute saving is zero?

pip install duckdb
python3 - <<'SQL'
import duckdb
con = duckdb.connect()
con.execute("""create table v as select * from (values
  (1, 0, null::timestamp, 'A'),          -- the 2019 import, no clock
  (1, 1, timestamp '2026-03-04 09:12:00', 'NWT'),
  (1, 2, timestamp '2026-07-19 14:02:00', 'NWT')
) t(listing_id, seq, updated_at, grade)""")
q = """select grade from (select *, row_number() over (
         partition by listing_id order by updated_at desc) rn from v) where rn = 1"""
for engine, setting in [('BigQuery ', 'nulls_first_on_asc_last_on_desc'),
                        ('Snowflake', 'nulls_last_on_asc_first_on_desc')]:
    con.execute(f"set default_null_order = '{setting}'")
    print(engine, '->', con.execute(q).fetchone()[0])
SQL

One table, one query, two documented defaults, two answers. Then:

1. Pull your own ninety-day query log (INFORMATION_SCHEMA.JOBS, QUERY_HISTORY), group by normalised statement hash, and compare the distinct count to the number of models in your repo. That ratio is the lesson.
2. Take the three most-run statements not in the repository and find out who owns them.
3. pip install sqlglot, transpile ten of your own models to the proposed engine, and read the output, not the exit code.

A warehouse migration is priced as moving data and is actually re-deriving every agreement the platform has accumulated: which engine default each number was computed under, which copies of the SQL exist and who owns them, which history was never derivable, what the monitors knew. The data is the cheap part; it is the only part with a vendor behind it.

• The repository is the fifth of the problem that has a plan-shaped boundary. 19.4% of this platform’s distinct SQL is in it; the migration was costed on that fifth.
• Translation risk belongs to the pair, not to your code. The identical project takes 29 silent changes into one engine and 7 into another.
• “Run both until they agree” is not a plan, because they never will. The same engine, twice, over the same data, disagrees on 11 of 48 columns.

The twenty-second failure shape — the dialectal

The first shape that needs two systems to exist at all. Every row is correct in both warehouses. Every test passes in both. Each engine implements its own documented semantics exactly, and neither is wrong. The unit of failure is a default nobody chose — NULL ordering, a type alias, a rounding mode, an estimator — set by a vendor, inherited by a company, visible only while both engines exist. Its signature is that it is created by an act of improvement, and that the one number everybody watches is immune to it by construction.

Vocabulary

Three pieces, all run rather than described. The translation campaign is real sqlglot 30.21.0 over 172 real GoogleSQL statements; the classification rules each came from running the construct and reading the vendor’s documentation. The distribution of constructs is synthetic, so the share affected is this platform’s while the fact that each construct is affected is the vendor’s. The laboratory is one DuckDB database of 1,200,000 orders and 1,056,597 listing versions, with twelve models built twice under each engine’s documented NULL ordering and every column of every model compared; DuckDB stands in for both engines configured to each one’s documented default — it is not Snowflake and the lesson does not claim it is. The arithmetic is closed form over a deterministic job log.

Verification re-runs every published figure by independent SQL and closed form: 52 checks, 52 matched, 0 failed. A clean-directory run of the shipped generator reproduces figures.json byte-identically: 468 scalar values, 0 differences, md5-stable across runs and directories.

Sources read rather than recalled: Snowflake’s ORDER BY, ROUND and datetime data-type pages (“The default is TIMESTAMP_NTZ”; “All operations are performed without taking any time zone into account”; “Converting a FLOAT value to an INTEGER value rounds the value”); ZetaSQL’s mathematical_functions.md and date_functions.md (“Returns the result of integer division of X by Y”; “Rounds halfway cases away from zero”; and for DATE(timestamp), “the default time zone, which is implementation defined, is used” — the standard leaves it to the implementation, which is this whole lesson in one clause); and sqlglot’s own dialect table, where BigQuery is nulls_are_small and Snowflake nulls_are_large. Rounding, the thing everyone worries about, agrees: both engines round halves away from zero.