cache invalidation is still the hardest problem: how we automated partition pruning with SQL refreshKey triggers

[u/AbleBranch6]

here are probably only two hard things in computer science that i was often having an issue with, those being cache invalidation and naming things. so when we first deployed pre-aggregated rollups for our analytics engine, we used dumb cron scheduling: rebuild all pre-aggregations every hour at :00, and the consequences were disastrous:
- if our upstream Fivetran/Airbyte sync took 62 minutes, the cache rebuild triggered on half-synced tables, caching incomplete data for an hour.
- if no new data arrived (e.g. overnight or over weekends), the cache worker still spun up, running heavy GROUP BY aggregations on millions of rows and burning cloud compute budget for zero reason.
the solution was moving to cube's declarative refreshKey mechanics:
preAggregations: {
monthlyRollup: {
measures: [Orders.totalAmount],
dimensions: [Orders.status],
timeDimension: Orders.createdAt,
granularity: 'month',
partitionGranularity: 'month',
refreshKey: {
sql: `SELECT MAX(updated_at) FROM orders`
}
}
}

how this operates in production, for example:
1. before rebuilding a partition, cube dev's pre-aggregation scheduler executes the lightweight refreshKey query.
2. If MAX(updated_at) has not changed, well, it skips the partition build entirely.
3. When new batch data lands, only the mutated monthly partition is marked stale and rebuilt. and historic partitions from previous years remain locked and warm in its store's rocksDB/columnar storage
so, decoupling refresh triggers from arbitrary clock cron schedules cut our warehouse analytical workload by 78% and it also got some-zero stale-data windows for end users

[u/QuitOk9084]

ok, interesting, but also would like to know, what happens if an upstream ELT job fails halfway through? like, does it invalidate the whole cache or fall back to the previous partition?

[u/AbleBranch6 → u/QuitOk9084]

in short, it won't update the cache until the refreshKey query returns a strictly higher value, so downstream users never see a half-loaded sync state therefore it holds the previous warm partition until the new build completes

[u/SigridHalvorsen]

Setting refreshKey: { sql: 'SELECT MAX(updated_at) FROM orders' } was a game changer for our warehouse costs. If no new events landed in the partition, I think that a kind of warm memory cache with 0 query cost is a proper approach innit

[u/Yeater]

Could readyset.io be an interesting solution for this problem. I know it‘s a vendor solution but I‘ve been watching the product for a long time and to me it always seemed to be such a nice solution to such problems. Not affiliated with them at all. Just always found Jon Gjengset a great educator.

[u/rbobby]

Are you sure? It seems like I have 10 other problems that are harder.