Partitioning Strategies I Wish I'd Known Earlier

I've been working with dbt for about 3 years now, and my opinions on partitioning have changed more than I expected.

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

How we got here

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

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

How it works

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

The core of it looks like this:

import polars as pl

daily = (
    pl.scan_parquet("events/*.parquet")
    .filter(pl.col("event") == "signup")
    .group_by(pl.col("ts").dt.date().alias("day"))
    .agg(pl.len().alias("signups"))
    .sort("day")
    .collect()
)

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.

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 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.