In two weeks we migrated all seven of our dbt projects (about 480 models) off AWS Glue and onto DuckDB 1.5, then three weeks later moved them all again, onto DuckDB 2.0. Each hop paid off:
- Glue to DuckDB 1.5: our core project's build time fell by 74% (3.9X faster!), from 97 minutes to 25, and our daily compute spend fell by 91%.
- DuckDB 1.5 to 2.0: on the same containers, a further 10–27% came off our warehouse build times, and 78% off our heaviest telemetry job.
The surprise was why it had been slow in the first place: build time grew with the number of models, not the size of the data.
The data estate at Flock is run on an open lakehouse architecture that used to make heavy use of AWS Glue as the backbone for both the catalog & compute components. This is done by using the dbt-glue adapter as the engine for building our models & managing our data as code. We also use DuckDB for niche compute use-cases for ad hoc analysis & regular small compute jobs.
Problem
Over time, model runtime was increasing along with costs. This made little sense given a dirty secret: most of our data isn't actually that big. We have common datasets of just a few thousand to a few hundred thousand rows. It is the exception in our data stack for tables to be tens of billions of rows (although this does exist), so why do we have compute spec'd for the exception rather than the rule?
What worked
Scalability
As our data, compute & infrastructure are hosted, we have a very high ceiling for what we can deliver and do not spend a typical day inspecting specific compute instances or dealing with storage issues.
This setup worked well in that it was hosted, effective for a small to medium project and used well known tooling which allowed us to onboard users quickly and easily. Things were working and breaking issues were rare. That said, problems were on the horizon.
What was starting to break
Performance
As we onboarded more users & projects, build times grew linearly & costs rose with them. The trendlines were all going in the wrong direction.
Operations
Troubleshooting models that fail to build in a provisioned Glue interactive session is not fun. To illustrate: a failure meant following the trail from monitoring (Datadog) to Airflow (orchestration), to our runner (a thin wrapper calling Glue), to the Glue interactive session itself, where seeing which statements ran before the failure took dark arts or CLI commands.
Additionally, we found that we were not getting parallelism we'd expect by making use of threads in dbt configurations (see below for a more detailed explanation).
The Glue v6 Detour
As these issues came to the forefront of concerns, we kicked off a spike to investigate migrating our Glue runners to v6 which had just been released & promised big savings over Glue v5. The hope that this major upgrade would solve all our problems with a small configuration change was dashed. We rolled Glue 6 out to all seven of our dbt projects & rolled it back the same afternoon. Most built cleanly, but two failed in the same way: one long, heavy statement left the Spark driver unresponsive until Glue killed the session. In one, the model hung while Spark was still planning the query, before a single job had started; in the other, it ran past the session's idle timeout mid-statement. We had theories (a very wide hash expression tripping up the new query optimiser, long-running statements not counting as session activity) but never proved a root cause because a more radical solution occurred: why use Glue (hosted Spark) at all for compute? If you're in data engineering, you probably know about DuckDB, but if not, it is an in-process database that is fast, efficient & simple enough that there's almost no time between reading about it & running it. We're pretty quackers about DuckDB.
The migration
I already use DuckDB for local ad hoc analysis & in small Airflow tasks where our compute needs were tiny, so this was not wholly new tooling but the thinking was that we could expand the implementation of DuckDB across the wider estate. Specifically, we could make use of the dbt-duckdb adapter to port multiple pre-existing dbt projects and rely on the dbt framework and our deployment strategy to ensure models are only migrated if strict parity criteria are met. The plan: swap out the compute engine under the hood (Glue > DuckDB) & reap the benefits. After speaking with the team about this fairly fundamental shift, a plan to trial this change in a non-prod facing dbt project for testing with an eye to rolling out more widely upon completion was agreed.
The first project
The first dbt project was not production facing (an ideal guinea pig). Some of what it taught us:
- Native DuckDB SQL, not a Spark compatibility layer. Our first spike used a layer of macros that let DuckDB accept Spark-style functions. It worked, but we chose to translate models into native DuckDB SQL instead: no models straddling two engines, and if we ever needed to roll back, we'd redeploy the last Glue release rather than maintain compatibility code.
- Loud differences are the easy ones. Spark-only syntax (
LATERAL VIEW explode,get_json_object, Java-style date formats) fails straight away, and a one-off script rewrote most of it for us. - Quiet differences are the real work. Some SQL runs on both engines but returns a different answer: arrays start at 1 rather than 0,
concatskips NULLs rather than returning NULL,round()on a double can land a penny out, ascending sorts put NULLs last rather than first, and casting to an integer rounds rather than truncates. - Environment assumptions break silently. A config value our models used to tell prod from dev doesn't exist under dbt-duckdb, so every prod-only guard quietly evaluated false until we replaced it.
- The write path matters. Committing to Iceberg efficiently (one snapshot per write, sensible file sizes) took several iterations of our base runner during the week.
Checking every table with AI agents
We maintain a near parallel version of production where we can test our dbt pipelines which proved to be extremely helpful, along with our data engineering MCP server which had all the authorisation & tooling required to run table comparisons across environments & validate that our changes did not impact production tables.
How it worked was: we added a dbt profile in our base dbt runner (a shared runner used by all dbt projects) to support DuckDB, we drafted pull requests which executed dbt runs (using our DuckDB runner) against our near-prod environment and then we had our production environment (still built by Glue).
Once we had what should have been exact 1:1 tables to compare, we let loose AI agents via the data engineering MCP server to run & test differences across all tables.
What differences did our agents look for? Our agents ran counts & high-level aggregates on tables, EXCEPT ALL in both directions to highlight exceptional rows between engines, roved column-by-column doing painstaking comparison on a granular level on each table that failed some sense check in the earlier stage & compared column types.
Every change was catalogued. Nothing was considered for promotion without an understood reason.
The differences we did find fell into a handful of buckets:
- Freshness. The two engines built at different times or from different source loads, so recent rows had moved on in between.
- "Today" moved. Anything calculated relative to the build date (earned premium, overdue invoices) shifts when one build runs later than the other.
- Ties. Where a model picks one row from several equal candidates, Spark & DuckDB could pick differently. We made those picks deterministic, or accepted them as known quirks.
- Floating-point residue. Running totals landing on 0.0000000001 rather than 0, or a last decimal place differing.
- Quiet engine differences. The ones above: NULL ordering, rounding, casts. We fixed every one we found; in our telemetry models we went as far as reproducing Spark's rounding at every intermediate step, so the numbers matched to the last decimal place.
- Glue's own leftovers. In a few places DuckDB was more correct, such as stale rows an old Glue merge had never cleaned up.
Finally, promoting this change to production and making sure our orchestration (Airflow) was able to build these models regularly without introducing regressions. We leaned heavily on dbt contracts & testing framework to ensure that there was no datatype drift, no assertions that went red and this gave us a high degree of confidence that the final state was non-impactful & safe to promote to production.
Writing it down as a Claude skill
After the first project was migrated, we developed a Claude skill to catalogue common pitfalls that were found and moved on to the next least impactful dbt project to repeat the process. The skill was comprised of five phases:
- Inventory & baseline checks on existing tables
- Port the SQL (this bit contained tips & guidance on how to port different SQL dialect between Spark & DuckDB)
- Orchestration
- Near-production comparison
- Release & verification
Each migration added to the skill so it was iteratively developed on the less complex & impactful dbt projects initially. When it was applied to our most mission-critical dbt projects, it had been battle-tested. Importantly, during this iterative development there was an engineer in the loop making final decisions & looking adversarially at the output. Within two weeks, we had completed the migration of all seven of our dbt projects, around 480 models in total: the six warehouse projects in just under a week, & our telemetry models the week after.
Architecture
Most of the stack didn't change. Airflow still schedules our builds, tables are still Iceberg on S3 registered in the Glue Data Catalog, and everything downstream (Athena, Looker, our data engineering MCP server) reads exactly the same tables as before. The only thing that moved is where the SQL runs.
How dbt-glue worked
The dbt-glue adapter doesn't run any SQL itself. It's a client: it sends each statement to a Glue Interactive Session and waits for the answer. A scheduled build went roughly like this:
- Airflow starts our dbt runner container on ECS. This can take between 30-60 seconds.
- dbt-glue asks Glue for an interactive session, and Glue provisions a Spark cluster behind it. That takes around a minute, paid once per build, as the session is reused across models.
- For every model, dbt-glue sends a string of statements to that session: session setup (re-sent every time a cursor is opened), the describes & checks dbt needs, the model's SQL itself, then post-hooks such as snapshot expiry.
- For every statement, the client polls Glue once a second until it completes. The session also runs one statement at a time, so adding dbt threads bought us very little.
Step 4 is where the time went. A model of a dozen rows cost around 35 seconds, of which about 1 second was its actual SQL, and about two thirds of the statements were setup. Multiply that by the 150-odd models in our core project and a build takes over an hour, regardless of how much data actually moves. Build time grew with the number of models, not the size of the data.
How dbt-duckdb works
- Airflow starts the same dbt runner container on ECS. This still takes between 30-60 seconds.
- dbt-duckdb starts DuckDB inside the dbt process itself. There's no cluster and no session to wait for.
- DuckDB attaches the Glue Data Catalog through its Iceberg extension and reads tables straight from S3.
- Each model's SQL runs locally in the container. Full rebuilds are streamed to Parquet on local disk, then committed to Iceberg as a single snapshot via PyIceberg. Incremental merges run as DuckDB SQL against the Iceberg table.
No round trip per statement means a small model now finishes in seconds.
Results
Our warehouse builds got between two and four times faster. While some of our old pipelines were lining up one statement to execute at a time, our DuckDB containers had read, transformed & written results to Iceberg tables.
Build Time Per Project
| Project | Glue (min) | DuckDB (min) | Faster by |
|---|---|---|---|
| Core | 97 | 25 | 3.9x |
| Business intelligence | 77 | 28 | 2.8x |
| Claims | 25 | 12 | 2.1x |
| Customer | 20 | 6 | 3.3x |
| Pricing | 15 | 5 | 3.0x |
Median duration of scheduled production builds, start to finish, before and after each project's cutover.
Lower Daily Compute Spending
Tradeoffs
This was not a totally free lunch: where we previously didn't have to invest much time in the optimisation of models (instead relying on Spark compute to handle heavy lifting), we introduced some new considerations.
- dbt Model Design: drafting a dbt model now requires more thoughtful consideration. What about the partitioning strategy? Materialization strategy? Filtering & incremental behaviour? In short, we have to invest more thought in our dbt models up front.
- Monitoring: Related to this, we had to implement more monitoring on the health of our containers now & spec certain Airflow tasks to higher compute containers for larger operations in code. We need to keep a closer eye on the infrastructure we are now responsible for (shudder for the OOM errors that are now something to consider)
- Runner Architecture: And what about the models that DO require big data treatment? Roughly 15 of our ~480 models needed a bigger container. I wrote at the top that most of our models were not that big, but some big beasts do roam our data estate. We've had to manage how data is spilled to disk, and made custom changes to how the base runner manages resources on the container. We cap DuckDB's memory below the container's limit, so it spills to local disk rather than taking the whole container down, and we stream big full rebuilds to Parquet files on the container's disk before committing them to Iceberg in one go.
- Table Maintenance: DuckDB writes merges as delete files rather than rewriting data, so merged tables need compacting more often. Our Glue table-maintenance spend roughly doubled, and it's now most of what's left of our Glue bill.
DuckDB 2.0
DuckDB 2.0 was released the week we finished the migration, which handed us a clean experiment: same models, same containers, same data, only the engine version changes. For our core, claims, customer, finance & pricing projects we ran a build on DuckDB 1.5.6, released the 2.0 image, then immediately ran the same build again on the same inputs. Our data engineering MCP server then fingerprinted every column of every output table from both builds & compared them. Every table that differed traced back to a source that had moved between the two builds (our core project reads live sources, so many of its tables did) or to a tie a model picks arbitrarily.
| Project | Build time cut by |
|---|---|
| Core | 27% |
| Finance | 25% |
| Customer | 12% |
| Claims | 10% |
| Pricing | 10% |
| Telemetry speed metrics | 78% |
Whole Airflow run, start to finish: a DuckDB 1.5.6 build & a 2.0 build back to back on the same day, same container & same inputs. Telemetry compares the same task on the same container, before & after its release. Our projects have grown since the October builds in the table above, so we measured 2.0 against 1.5.6 directly rather than against those medians.
The gains are all inside DuckDB: scans, joins & merges. Full rebuilds are written to Iceberg by PyIceberg rather than DuckDB, and container start-up & dbt parsing take as long as they ever did, so projects that mostly rebuild lots of small tables (pricing, customer) gained least. The biggest win came from our telemetry speed metrics, which update their tables with incremental MERGEs: each of the five big merges dropped from four to ten minutes to one to three, and the whole task from 17 minutes to under 4.
It wasn't all upside. Our business intelligence project, home to most of our biggest models, got slower on 2.0 at first. Its heavy models spilled several times more data to disk, and some failed outright when they ran out of disk to spill to (one of the tradeoffs from switching off a hosted service like Glue is that we have to consider & react to these issues). We had to dig into the query planner to understand that some model behaviour had changed after this upgrade & specific care had to be spent improving models & refactoring in how they were built. The bigger lesson was that the new engine needed this project's compute specified differently: a bigger container running four models at a time instead of eight. Because we changed the container as well as the engine, this isn't a like-for-like comparison, so business intelligence is left out of the numbers above.
Two more changes are worth checking before you upgrade: the old x -> x + 1 lambda syntax now errors by default (we rewrote ours as lambda x: x + 1), and a MERGE now fails if two source rows match the same target row. Both are better behaviour, but both need a sweep of your models first. 2.0 also uses more memory: our core build peaked 41% higher.
Sign-off
All told our data stack allowed an idea on a Friday to be a tested, estate-wide change in just a week or two. A problem was identified (performance, creeping costs) and a solution was thoroughly tested & deployed in short order. DuckDB 2.0 then took another 10–27% off our warehouse builds on the same containers (and about 78% off our heaviest telemetry job), with the outputs checked table by table against the old version. We'll keep monitoring performance as the estate grows.
About the author
