Small Data Is Underrated

I've been working with Python for about 9 years now, and my opinions on backfills have changed more than I expected.

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

What was going wrong

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

The setup was ordinary: Python at the edges, DuckDB 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.

What we changed

The fix turned out to be smaller than expected. Instead of adding another layer, we removed one and leaned on what Python 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';

This alone let us delete a surprising amount of code elsewhere in the project.

A few things that helped along the way:

Make the default the safe choice, not just the documented one.
Measure before and after, even if it's just a rough number in a spreadsheet.
Keep the old path around until the new one has survived a real incident.

Trade-offs

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

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

Wrapping up

Six months in, nobody on the team wants to go back. That's about the best review a change like this can get.