Modern warehouses (Snowflake, BigQuery, Redshift, Databricks, ClickHouse, DuckDB) all use columnar storage. The choice changes everything about what "good" schema design means.
What columnar storage actually does
Row storage (OLTP):
[order_id=1, customer=Anuj, region=IN, revenue=500]
[order_id=2, customer=Priya, region=IN, revenue=300]
[order_id=3, customer=Raj, region=US, revenue=800]
All columns of one row sit together on disk. Reading row 2 reads the whole row in one I/O.
Columnar storage (OLAP):
order_id : [1, 2, 3]
customer : [Anuj, Priya, Raj]
region : [IN, IN, US]
revenue : [500, 300, 800]
All values of one column sit together. Reading the revenue column reads only the revenue values; the other columns are untouched.
Why this matters for analytics
SELECT SUM(revenue) FROM orders WHERE region = 'IN':
- Row storage: read every row's full width (50+ columns), pick out two values. 50× the I/O of what you actually need.
- Columnar storage: read only
regionandrevenuecolumns. 25× cheaper for a 50-column table.
For wide-scan aggregation queries — the dominant analytics workload — columnar is 10-100× faster.
What changes about schema design
1) Wide tables are no longer a sin
In OLTP design, wide tables are a smell — every row read has to load all 80 columns even if you only need 3. In columnar warehouses, unused columns cost essentially nothing per query. You can have 80-column fact tables without performance penalty (within reason).
This is why denormalized, flat marts work well in warehouses. The cost of "extra" columns is paid only at write time and at storage; query time is per-column-touched.
2) Column compression is excellent
Columnar engines apply per-column compression — run-length encoding for low-cardinality columns, dictionary encoding for repeated strings, delta encoding for sorted integers. A region column with 5 distinct values compresses to nearly free per row.
This is why "duplicated dimension attributes" don't cost what they used to:
fct_orders_flat:
order_id, customer_country, customer_segment, customer_signup_date, ...
The country column is one of N distinct countries — compression handles the duplication.
3) Predicate pushdown to columns
Modern engines push WHERE filters down to the column scan level. Filtering WHERE region = 'IN' reads only the region column to identify matching row IDs, then reads only those rows' other columns. Massively efficient.
For this to work, your filters should be on actual columns (not on expressions). WHERE region = 'IN' works. WHERE UPPER(region) = 'IN' doesn't.
4) Sorting and clustering matter
Columnar engines store data in micro-partitions (Snowflake) or blocks (BigQuery, Redshift). Each block has min/max metadata for each column. If your filter is on a sorted column, the engine can skip most blocks entirely.
Example: a 100B-row table sorted (clustered) by event_date. A WHERE event_date = '2026-05-25' filter reads ~0.01% of blocks. The rest are pruned by min/max metadata before any I/O.
This is why partitioning and clustering choices (next lesson) are so important.
5) Joins benefit from broadcasting small dimensions
Most engines broadcast small dimension tables (dim_country with 200 rows) to every worker node — no shuffle, near-free join cost. Big dim-table joins (millions of rows in the dimension) trigger shuffles and become expensive.
Design rule: keep dimensions reasonably small. If a dimension grows to tens of millions of rows, consider whether everything in it really belongs there.
What stays the same
- Foreign keys, semantics, dimensional model — the logical design is identical to row-based warehouses.
- Indexing concepts — replaced by clustering / partitioning in columnar systems but the same idea: tell the engine which columns you'll filter on.
- Grain, additivity, SCDs — all the dimensional-modeling fundamentals.
The physical layout changes; the logical model doesn't.
Common columnar-design mistakes
- Treating it like row storage — over-normalizing to avoid duplication that compresses to nearly nothing.
- **SELECT *** — defeats the columnar advantage. Always select needed columns.
- Filters on function-of-column —
WHERE DATE(event_time) = '2026-05-25'blocks column pruning. Rewrite as range. - Frequent single-row UPDATEs — columnar engines aren't built for this. Use INSERT-only patterns + Type 2 SCDs or merge operations.
Takeaway
Columnar storage flips the cost model. Wide flat tables become cheap; SELECT * becomes expensive; clustering matters more than indexing; small dimensions stay small. Design with these in mind and your warehouse queries run 10-100× faster.