Migrating a 35-million-row dbt project from Athena to Databricks

There is a class of data-warehouse migration that looks like a syntax exercise from the outside and turns out to be a complete architectural rewrite from the inside. You have a dbt project that runs against Athena. You’re moving it to Databricks. The SQL is mostly portable, the dbt machinery is mostly the same, the data is the same. Surely this is a port, not a rewrite.

It isn’t. The project this article is about — twenty-seven fact tables, twelve dimensions, eight intermediates, computing rolling 365-day sales attribution over thirty-five million order lines — survived the syntax conversion intact and then took hours to run on Spark. Restructuring it took several weeks of careful work, the kind that doesn’t show up in commit history as a single visible “the migration” commit. This article is about that work.

The original shape: rolling windows, all the way down

The Athena project’s central pattern was the rolling-365-day window: for each order line, compute aggregates over the trailing year of activity for that customer, that product, that segment. There were six of these computations across the most-used fact tables, written as Presto window functions:

SELECT
    order_line_id,
    customer_id,
    SUM(margin) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '365' DAY PRECEDING AND CURRENT ROW
    ) AS rolling_365d_margin,
    -- ...five more like this
FROM order_lines

On Athena (Presto / Trino), this runs reasonably. The engine’s window-function implementation is internally bounded: for each row, it scans backward through the partition until it finds the first row outside the range, then stops. If the partition is dense enough that most rows fall within the year, the scan is cheap.

On Spark, the same query takes hours. Spark’s window function with a RANGE predicate evaluates the range for every row — there’s no internal early termination. For a partition with N rows where most fall within the window, you get O(N²) work. Thirty-five million order lines, partitioned by customer with some customers having tens of thousands of lines, hits this hard. The first benchmark of the unmodified port ran for over four hours and was killed.

This is one of those Spark-vs-Presto edge cases that doesn’t show up in tutorials — both engines accept the same SQL and return the same result, yet their cost models for it are radically different.

Decision: pre-aggregate to daily, then run the rolling window

The fix is to reduce N before the expensive window computation runs. For most rolling-window calculations, the per-row precision of the input doesn’t matter — what matters is the daily total. If you collapse the order lines to daily aggregates first (sum margin per customer per day), the window function then runs over a much smaller set.

Stage 1: Order lines              35M rows
                                    ↓ GROUP BY customer_id, order_date
Stage 2: Daily customer aggregates  ~5M rows
                                    ↓ Rolling 365d window
Stage 3: Per-day rolling totals     ~5M rows
                                    ↓ JOIN back to order lines on (customer_id, order_date)
Stage 4: Per-line attribution       35M rows

The pipeline is four stages — three of which are cheap, one of which is bounded now that it’s running over five million rows instead of thirty-five. The expensive window computation went from O(35M²) work to O(5M²) work, and crucially it now fits in memory and parallelises cleanly across executors.

The third-stage CTE looks like this:

-- daily_customer_margin: 5M-row pre-aggregate
SELECT
    customer_id,
    order_date,
    SUM(margin) AS daily_margin
FROM order_lines
GROUP BY customer_id, order_date

-- rolling_365d: window over the daily pre-aggregate
SELECT
    customer_id,
    order_date,
    SUM(daily_margin) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '365' DAY PRECEDING AND CURRENT ROW
    ) AS rolling_365d_margin
FROM daily_customer_margin

The same logic, on a tenth of the data. The runtime collapses from hours to minutes.

This isn’t a Spark-specific trick — it’s a refactor that improves both engines, but on Athena it was an optimisation and on Spark it was the difference between runs and doesn’t run. The lesson generalises: window functions over large partitions are the wrong default in Spark, and pre-aggregation is almost always the right intermediate.

Decision: split monolithic models into intermediates that materialise

The Athena version of the central fact table was a single 600-line dbt model with seven CTEs piled into one SELECT. This compiles fine and runs as a single Spark job — but a single Spark job that big is a debugging nightmare. If the rolling window stage fails, you can’t tell from the failure which CTE was at fault, and you can’t restart from where the failure happened.

The Databricks rewrite splits the monolith into intermediate models that each materialise as a real table:

order_lines             # raw source
  → int_daily_aggregate # Stage 1 + 2 collapsed
  → int_rolling_365d    # Stage 3, the expensive one
  → fact_order_impact   # Stage 4, the join back

Each intermediate is a materialized: incremental dbt model with its own tests. If something breaks, you know which stage. If you need to re-run, you re-run from that stage. The cost is more storage (each intermediate is a real table on Databricks), but on Databricks storage is cheap and rerun-from-failure is the property you actually want.

The decision rule: if a single CTE chain is doing all the work, you have one failure mode and one debugging surface. Materialising the steps doubles the storage and quarters the time you spend chasing where the bug is.

Decision: re-test the SQL semantics, not the pipeline

This is the part of the migration that’s least visible from the outside and most likely to bite you. Spark and Presto agree on most SQL. They disagree on enough of it to matter.

The disagreement that cost the most time was date_sub. In Presto:

TIMESTAMP '2026-02-15 14:32:01' - INTERVAL '7' DAY
-- → 2026-02-08 14:32:01  (timestamp preserved)

In Spark:

date_sub(TIMESTAMP '2026-02-15 14:32:01', 7)
-- → 2026-02-08          (truncated to DATE at midnight)

Same operation by name, different return type, different value. In our pipeline, we computed a “low end of the matching window” by subtracting seven days from a feedback timestamp. The Athena version preserved the time-of-day. The Spark version truncated. The result was that boundary windows shifted by up to 24 hours, and a small fraction of orders fell into different windows on the two systems.

This produced exactly the kind of bug that’s invisible to most testing: the row counts were almost right, the totals were almost right, the schema was right. The downstream fact_user_month aggregate was 2.1% off, and that was the only signal. Tracing it back through the dependency chain to find that the date arithmetic semantics differed took the better part of two days.

The fix was a one-line change:

-- Before (Spark, broken):
low_range = date_sub(created_time, 7)

-- After (Spark, correct):
low_range = created_time - INTERVAL 7 DAY

After the change, the row counts on both sides matched to the row, and five downstream tables that had been showing small drifts all resolved at once. The diff at the top of the chain rippled out into everything that depended on it.

The lesson I’d most want a future me to remember is that when you migrate SQL between engines, the syntax porting is the easy part — the semantics differ in the corners, which is exactly where the bugs hide, so test the corners on purpose, because the same query is not the same query.

What this didn’t try to be

I want to be clear about what isn’t in this migration, because the absences are themselves design decisions:

• It isn’t a streaming migration. The pipeline is batch, runs nightly, doesn’t try to keep both warehouses live in parallel.
• It isn’t a “lift and shift”. The SQL was rewritten where Spark needed it, not just translated.
• It isn’t an opportunity for a complete model overhaul. The fact-table grain stays the same, the column names stay the same, downstream consumers don’t have to change their queries. The migration is invisible to readers of the data.

These are deliberate scope choices. A migration is already a fragile multi-week project; adding “and we’ll redesign the data model while we’re at it” is how migrations slip into half-finished rewrites that ship neither the new system nor the old one cleanly.

What I’d carry forward

Three lessons, in roughly the order they cost time:

1. Pre-aggregate before window functions on Spark. Always. The cost model is different from Presto/Trino and a window over 35M rows that ran in fifteen minutes elsewhere will burn an executor on Spark. Halve the data before the window stage, and run-time falls off a cliff.
2. Materialise intermediate steps in long pipelines. Storage is cheap on Databricks; debugging time is not. A four-stage pipeline that materialises each stage is easier to operate than the monolithic CTE that compiled it.
3. The semantic differences are the dangerous bugs. Schema migration tools catch type mismatches. Test suites catch row counts. The bugs that survive both are the ones where the SQL is identical and the meaning of the SQL has changed. Plan for them; they’re going to happen.

The final shape of the project — twenty-seven fact tables, three-stage pipeline per fact, materialised intermediates, validated end-to-end against the legacy system — looks much like the original from the outside. The work was almost entirely under the surface, in places that don’t change the output but change everything about whether it can be operated. That’s most of what serious migration work is.