DuckDB Replaced Half of Our Data Stack

For a long time I treated data quality checks as something to worry about later. Then a small change to our DuckDB setup turned into a week of debugging, and "later" arrived all at once.

The setup was ordinary: DuckDB at the edges, Polars doing most of the heavy lifting, and a handful of scripts nobody wanted to own. It worked well until usage roughly doubled over a single quarter.

Where we started

Most of the complexity was accidental. Over the years we'd layered three different approaches to data quality checks on top of each other, and each one made sense at the time.

When we finally measured, the numbers were worse than anyone guessed. Around 28% of the time spent on a typical change went into working around data quality checks rather than building anything.

The fix

The key insight was to treat data quality checks as part of the interface rather than an implementation detail. Once we did that, most of the other decisions followed naturally.

Stripped of the project-specific bits, it looks like this:

SELECT
  user_id,
  event_at,
  LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS previous_event_at
FROM events
WHERE event_at >= CURRENT_DATE - INTERVAL '30 days';

It's tempting to generalize this into a library. Resist that urge until you've needed it in at least three places.

A few things that helped along the way:

Delete dead code aggressively; it's the cheapest refactor there is.
Make the default the safe choice, not just the documented one.
Write the migration guide before writing the migration.

Caveats

None of this is free. The new approach is a bit more verbose, and newcomers occasionally ask why we don't just use dbt for everything. That's a fair question with a boring answer: we tried, and it made data quality checks harder to reason about.

We still haven't solved partitioning. That's a post for another day, and honestly, I'm not sure we'll get it right the first time either.

Closing thoughts

That's it for now. Next time I'll dig into backfills, which turned out to be closely related.