Data vault hype, when is it actually justified?

Yes, the partial index was the win for us too.

We saw the same issue, fixing the partition filter dropped runtime 60%.

ELI5: think of it like a phone book. If it's sorted by last name and you search by last name, that's fast. Search by first name instead and you're flipping through every page.

In our case the root cause was an implicit cast preventing pushdown.

Consider DuckDB or Polars for this size before spinning up a cluster.

Prefer a staging table plus validation gate before promoting to prod tables.

Quick plain-English version: the database is doing more work than it needs to because it can't tell in advance which rows actually match. An index is basically a shortcut list so it doesn't have to check every single row.

Note that merge on Delta still needs unique keys defined correctly.

Quick plain-English version: the database is doing more work than it needs to because it can't tell in advance which rows actually match. An index is basically a shortcut list so it doesn't have to check every single row.

In our case the root cause was an implicit cast preventing pushdown.

Check whether AQE is disabled in your Spark conf, skew join handling helped us a lot here.

Start with the execution plan, numbers beat guesses.