Cost, layout and query performance

you partitioned by customer id, there are two million customers, and now every query lists two million tiny folders.

Partitioning strategy

the table is partitioned by day and sorted within each day by customer, so filtering on a customer skips most blocks inside the partition.

Clustering key

the rows are stored in date order, so a range query reads a contiguous run of blocks instead of touching all of them.

Sort key

queries filter on either region or product, never both, and one linear sort order can only help one of them.

Z-ordering

the query returned four rows and the console says it read two point one terabytes.

Bytes scanned

one dashboard refreshing every five minutes turned out to be a third of the monthly warehouse bill.

Query cost

the second person to run the identical query got the answer instantly and was charged nothing.

Result cache

you doubled the warehouse size, the query took half as long, and the bill was identical.

Warehouse sizing

the cluster stayed up all weekend waiting for queries that never came, and the invoice noticed.

Idle compute

you scale up the query cluster for the month-end run and the data does not move anywhere.

Storage-compute separation

the join needs matching keys on the same machine, so the engine moves the whole dataset across the network before it can start.

Shuffle

the small dimension table is copied to every worker so the big table never has to move.

Broadcast join

both sides are too big to broadcast, so each is shuffled and sorted by the key and then walked through in step.

Sort-merge join

one side is built into an in-memory hash table and the other is streamed past it, and it is fast right up until the build side does not fit.

Hash join

the sort did not fit in memory, started writing to local disk, and the query went from forty seconds to nineteen minutes.

Spill to disk

ninety-nine tasks finished in twenty seconds and the hundredth ran for eleven minutes, because one key has forty percent of the rows.

Data skew

the stage is at nine hundred and ninety-nine of a thousand tasks complete and has been for six minutes.

Straggler task

the plan says it expects nine hundred rows from that filter, it actually returns forty million, and every choice after that is wrong.

Cardinality estimate

the engine started the join, saw that one side was much smaller than predicted, and switched strategy mid-query.

Adaptive query execution

the engine processes a thousand values of one column per operation instead of one row at a time through a chain of function calls.

Vectorised execution

the model rebuilds only the last three days each run instead of the whole four-year table, and once a month you rebuild it fully anyway.

Incremental model

you drop the table and rebuild it entirely from source, which is expensive, slow and definitely correct.

Full refresh

every query is tagged with a team, so the monthly bill can be broken down by who actually spent it.

Cost attribution