Backfills Are a Product Feature

I've been working with Snowflake for about 7 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 setup was ordinary: Snowflake at the edges, pandas 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.

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.

Trying something different

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

Here's a trimmed-down version of what we ended up with:

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:

Make the default the safe choice, not just the documented one.
Pair on the first few changes so the knowledge doesn't live in one head.
Write the migration guide before writing the migration.

Where this breaks down

There are cases where this falls apart. If your team is very small or your traffic is very spiky, the extra moving parts may not pay for themselves.

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

Wrapping up

I don't think there's a universal answer here, but I hope this gives you a few ideas to try on your own project. If you do, I'd love to hear how it goes.