The migration notes said "off by at most one." It was two days.

We moved a set of reporting logic from SQL Server to Databricks and rewrote it in dbt. It computes the numbers the shop floor checks every day: due dates, lead times, days delayed, whether an order shipped on time.

Most of the work was mechanical, one function name swapped for another. The trouble was the other kind of change, where the SQL runs, the numbers look plausible, and they are slightly off. We hit a few of those, and I only found one of them while writing this.

Layers

An upstream ingestion job lands the raw tables in bronze, and dbt takes over from silver. Silver cleans, deduplicates and standardizes, with one folder per business domain (orders, sales, units, scans, employees), all materialized as tables. Gold joins across domains, also as tables. Marts are the KPIs the dashboards read, materialized as views.

We changed one thing about schema names. dbt's default is <target schema>_<custom name>, and we kept it in dev, so each developer's models land in a schema prefixed with their own name (jack_silver) and nobody overwrites anybody. In prod, the rewritten generate_schema_name uses plain names like silver and gold and gives each marts subfolder its own schema, so whoever builds a dashboard finds tables by domain.

The easy part

These change the spelling, not the result:

Watch the LIKE row. SQL Server's LIKE understands character classes like [A-Z] and Spark's doesn't. Copied over unchanged, it doesn't fail. It just never matches.

DATEDIFF takes its arguments the other way around

-- SQL Server: start first, then end
DATEDIFF(day, start_date, finish_date)

-- Databricks: end first, then start
datediff(finish_date, start_date)

Copy it over unchanged and nothing errors. The sign flips and every lead time comes out negative, which at least you notice the first time you look at the output.

One week off, which is two business days

Lead time excludes weekends. On SQL Server it was written as:

DATEDIFF(day, start_date, finish_date) - DATEDIFF(week, start_date, finish_date) * 2

DATEDIFF(week, …) counts Sunday boundaries crossed, whatever SET DATEFIRST says, not seven-day periods. That makes it a count of weekends crossed, and the whole expression gives exact business days. I checked every combination that starts and ends on a weekday, for spans of 0 to 14 days, and it was right in all of them.

In the migration it became:

datediff(finish_date, start_date) - floor(datediff(finish_date, start_date) / 7) * 2

floor(days / 7) counts complete seven-day periods. The migration notes say the two differ by at most one week at the boundaries, with behavior "preserved closely but not identically." That's true of weeks. Multiply by two, though, and it's two business days. I worked through it again for this post:

Of the 55 combinations, 20 come out two days high. Any span that crosses a weekend without covering a full seven days is affected: start on Friday, finish on Monday, and one business day becomes three. Lead times and days delayed are both overstated.

To get SQL Server's week count in Spark, pick a fixed Sunday as an anchor, work out which week each date falls in, and subtract:

floor(datediff(finish_date, date'2000-01-02') / 7)
  - floor(datediff(start_date, date'2000-01-02') / 7)   -- 2000-01-02 was a Sunday

If both dates are always weekdays, datediff(date_trunc('week', finish_date), date_trunc('week', start_date)) / 7 works too. I compared both against SQL Server's DATEDIFF(week) on 40,000 date pairs. The anchor version matched on every pair, and the date_trunc version matched whenever both dates were weekdays.

So when a note says "off by at most one," it's worth one more step: work out what that one becomes in the number people actually read.

Spark has no time type

SQL Server code uses CAST(ts AS time) a lot, for example to check whether a scan fell inside a shift. Spark SQL has no time type, so we format the timestamp as 'HH:mm:ss' and compare strings:

date_format(scan_ts, 'HH:mm:ss') BETWEEN '06:00:00' AND '14:30:00'

That works because HH:mm:ss is fixed-width, zero-padded, 24-hour time, so alphabetical order is time order. If one place slips to H:mm or to a 12-hour clock, '9:00' sorts after '10:00' and the comparison is quietly wrong.

Which time zone current_date() uses

Fields like is_on_time compare against today. GETDATE() returns the database server's local time, while Databricks' current_date() uses the session time zone. If that's left at the default UTC, then in California "today" turns into tomorrow at 5 p.m. during daylight saving time.

These models are also materialized as tables, so today's date is fixed when each build runs. The on-time rate on the dashboard is the on-time rate as of the last build.

A custom function: first a UDF, then a macro

The old system had a scalar function that pulled a value by key out of strings shaped like 1000=…;1010=…, and two models used it. It moved in two steps: a UDF on Databricks as a stopgap, then a dbt macro that expands into a regular expression:

{% macro kv_value(field, key) %}
    nullif(trim(regexp_extract({{ field }}, '(?:^|;){{ key }}[=:]([^;]*)', 1)), '')
{% endmacro %}

With the macro there's no function to deploy and grant in every environment, and the expanded SQL shows up in dbt compile output. The cost is that the regex is copied into every model that uses it, so changing it means rebuilding everything downstream.

Three checks to do during the migration

"Off by at most one" made it from the migration notes into the models we run today, so checking whether results look reasonable isn't enough. If I did it again, I'd do these three things during the migration:

1. Build an edge-case table for every rewritten expression: Friday to Monday, across month and year boundaries, around midnight. Run both engines and compare row by row. That's how the 55 combinations above were checked.
2. Compare monthly aggregates side by side: average lead time and on-time rate per month from the old and new models, with a threshold that stops the migration when they diverge.
3. Diff rows on the primary key: join old and new results on the key and list every row where any field differs.