The Metric Everyone Defined Differently

There is no shortage of articles about DuckDB. Most of them cover the happy path. This one is about what happened after the happy path ended.

The first symptom was subtle. Nothing failed outright, but slowly changing dimensions kept drifting in the wrong direction, and every fix seemed to make the next one harder.

Some background

We had good intentions. We even had a document describing how slowly changing dimensions should work. The trouble was that the document and the code had quietly gone their separate ways.

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.

A simpler approach

The fix turned out to be smaller than expected. Instead of adding another layer, we removed one and leaned on what DuckDB already gives you out of the box.

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';

Nothing about this is clever, and that's the point. Anyone on the team can read it in a minute and know exactly what it does.

A few things that helped along the way:

• Make the default the safe choice, not just the documented one.
• Agree on what "done" means before the first pull request.
• Write the migration guide before writing the migration.
• Keep the old path around until the new one has survived a real incident.

Trade-offs

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

The biggest cost was social, not technical. Changing how people think about slowly changing dimensions takes much longer than changing the code.

Takeaways

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