When is denormalizing a star schema into one wide table worth it in a columnar warehouse?
answer
- repeated values compress away
- a column you never select costs no scan time
- the join and its exchange simply disappear
- a one-row dimension edit becomes a mass rewrite
- ask how often the attribute changes
basics
~20 sDenormalizing pays when joins dominate query time and the flattened attributes are small, low-cardinality and rarely restated. Columnar storage compresses the repeated dimension columns heavily and unread columns cost nothing, so the space penalty is far smaller than row-store intuition suggests.
solid answer
~50 sIn a columnar engine the usual objection to a wide table — "you are storing the country 4 billion times" — is much weaker than it looks. A repeated low-cardinality attribute compresses to near nothing under dictionary and run-length encoding, and a column nobody selects is never read. What you buy is the elimination of the join and its exchange entirely: one scan, no build side, no broadcast, no estimate that can go wrong, and predicates on former dimension attributes now filter the fact scan directly, which makes them prunable by the table's own layout. What you pay is on the write side. Every attribute change now requires rewriting fact rows rather than updating one dimension row, so a slowly-changing or frequently-corrected attribute becomes an expensive backfill. The honest rule: flatten stable, small, frequently-filtered attributes; keep volatile, wide or rarely-used ones in a dimension and join to them.
code
sql · 12 lines-- star: filter reaches the fact table only through the join
SELECT d.country, SUM(f.revenue)
FROM fact_sales f
JOIN dim_store d ON f.store_id = d.store_id
WHERE d.country = 'PT'
GROUP BY d.country;
-- wide: predicate applies directly to the scanned table
SELECT country, SUM(revenue)
FROM fact_sales_flat
WHERE country = 'PT'
GROUP BY country;go deeper
Recall that a wide table repeats descriptive values on every row to avoid joining, and that columnar compression makes those repeats far cheaper than they look.
Explain the three mechanisms that make it cheap — dictionary encoding, run-length on clustered runs, and columnar projection skipping unread columns — and name the join and exchange work that disappears.
Demonstrate the write-side judgment: quantify how often the flattened attribute changes, know that a change becomes a mass rewrite in an immutable-file engine, and propose the hybrid where only stable filtered attributes are flattened.
Own it as a platform policy. Decide which attributes are flattened across all fact tables, where the source of truth lives, whether flattened tables are materialized derivatives of a star, and who pays the refresh and backfill bill.
## Two shapes for the same data The star keeps facts thin and pushes descriptive attributes into dimensions joined on surrogate keys. The wide ("one big table", flat, denormalized) shape copies the descriptive attributes into the fact rows themselves: ```sql -- star fact_sales(sale_id, store_id, product_id, date_key, revenue) dim_store(store_id, store_name, city, country, region) -- wide fact_sales_flat(sale_id, store_name, city, country, region, product_name, category, date_key, revenue) ``` Both answer the same questions. The interview question is which costs less to run, and the answer depends on facts about columnar storage that do not hold in a row store. ## Why the storage objection is weaker than it sounds Three properties of columnar engines change the arithmetic: 1. **Dictionary encoding.** A `country` column with 200 distinct values stores small integer codes plus one dictionary, not 4 billion strings. 2. **Run-length / delta encoding.** If the table is sorted or clustered so that equal values cluster together, long runs of the same country collapse to a count and a value. Ordering the table well can make a flattened attribute nearly free. 3. **Columnar projection.** A query that does not mention `store_name` never reads its bytes. In a row store, a wide row inflates every scan; in a column store, unread columns are pure disk cost, not scan cost. So the storage premium for flattening a handful of low-cardinality attributes is often small in compressed bytes, and close to zero in query I/O for queries that ignore them. ## What flattening buys on the read side - **No exchange.** The join disappears, so does its broadcast or redistribution, along with the memory it needed and the chance the planner mis-estimates the build side. - **Direct pruning.** `WHERE country = 'PT'` becomes a predicate on the fact table's own column. If the table is clustered on it, zone maps eliminate blocks outside the range — no runtime filter needed, and none of the correlation caveats that make runtime filters unreliable. - **Plan stability.** A single-table scan-filter-aggregate plan is essentially unbreakable. Multi-join star plans regress when statistics drift. - **Simpler concurrency behaviour.** No build-side memory means no per-node memory spike when many such queries run at once. ## What it costs on the write side This is where flattening actually hurts, and it is the half candidates forget. - **Updates become rewrites.** A store is reassigned to a new region. In the star, that is one row in `dim_store`. In the wide table, it is every fact row for that store — potentially hundreds of millions of rows, rewritten in an engine whose files are immutable and whose update path is a rewrite of whole blocks or partitions. - **Correction latency.** A typo in a product name is fixed instantly in a dimension; in the wide table it is a backfill job with its own compute bill. - **Historical semantics get frozen.** A flattened attribute records the value as of load time. That is sometimes exactly right (you *want* the region the sale was booked under) and sometimes exactly wrong (you want today's region for all history). The star can serve either by choosing the join; the wide table has already chosen. - **Ingest coupling.** The loader must now look up dimension attributes at write time, which adds a join to the pipeline and a dependency on dimension arrival order — late-arriving dimension rows become a real operational problem. - **Duplication across tables.** The same attribute flattened into five fact tables is five places to fix when the definition changes. ## The hybrid that is usually right Production systems rarely pick a pure extreme. The practical pattern is: flatten the attributes that are **stable, narrow, low-cardinality and filtered constantly** (date parts, country, category, channel) and keep the rest in dimensions. Alternatively, keep the star as the source of truth and materialize a flattened table for the specific dashboards that need it, accepting the extra storage and refresh cost in exchange for read speed on a known workload. That way corrections happen once in the dimension and propagate on the next refresh. ## How to reason about it in an interview Ask for the numbers before answering: how selective are the dimension filters, how large is the fact table, how often do dimension attributes change, and how many distinct query shapes hit the table. Then state the trade in one sentence — *flattening converts a per-query join cost into a per-change rewrite cost* — and say which side of that trade the workload sits on. A candidate who answers "denormalize, joins are slow" without asking about update frequency has missed the entire cost. ## Related trap: the snowflaked dimension Going the other direction — normalizing a dimension into a chain of sub-dimensions — adds a join per level, each one another exchange and another chance for the planner to pick badly. In an analytical engine the storage saved is negligible for the same compression reasons above, so snowflaking usually costs read performance for no meaningful benefit. Flattening a snowflaked dimension back into one dimension table is almost always a free win; flattening the *fact* table is the trade that needs thought.
- Why is the storage cost of a flattened low-cardinality column much lower than a row-store intuition suggests?Columnar engines dictionary-encode the column so each row stores a small code, not the string, and run-length or delta encoding collapses long runs of equal values when the table's ordering clusters them. On top of that, a query that never selects the column never reads its bytes, so the cost shows up as disk footprint rather than as scan time.
- What operational problem does flattening create that the star schema does not have?Attribute changes turn into data rewrites. Correcting a region or a product name is one dimension row in a star; in a wide table it is every historical fact row for that key, rewritten through an immutable-file engine's whole-block update path. That means backfill jobs, compute cost, and a window where the table is inconsistent.
- When does flattening actually change the semantics of a report rather than just its performance?When the attribute changes over time. A flattened value freezes what was true at load time, so reports become as-of-transaction. A star can join to the current dimension row and report everything under today's attributes instead. Neither is wrong, but the wide table has committed to one interpretation and cannot serve the other without a rewrite.
- Is snowflaking a dimension ever worth it in a columnar warehouse?Rarely for performance reasons. The storage that normalization saves is largely recovered by dictionary compression anyway, while each extra level adds a join, an exchange, and another estimate the planner can get wrong. The defensible reasons are governance and reuse of a shared sub-entity across dimensions, not query cost.
saying these in an interview costs you the question
- Says denormalize always, joins are slow in every warehouse
- Ignores the write-side cost of updating a flattened attribute
- Applies row-store storage intuition to a compressed columnar table
- Thinks a wide table slows queries that never select the wide columns
- Claims snowflaking saves meaningful storage in a columnar engine