Data warehouse modeling begins with grain and meaning

A warehouse is not merely a large database or a copy of operational tables. It is a governed analytical system that preserves business meaning across sources and time while making recurring decisions economical to answer. Begin with users, decisions and contracts: which process is measured, what one row represents, which time is authoritative, which populations are included, how corrections arrive, and how quickly data must be usable. The declared grain is the most important constraint. A fact table at order-line grain cannot safely mix order-level shipping cost without an allocation rule; a daily snapshot cannot answer a point-in-time question as though it were an event log.

Facts record measurable events, accumulating processes, or periodic states. Dimensions describe the entities and contexts used to filter and group facts. A star schema normally keeps a fact table surrounded by relatively denormalized dimensions for understandable and efficient analysis. Snowflaking dimensions can reduce duplication or express complex hierarchy, but adds joins and user complexity. Normalized operational models optimize integrity and transactional change; analytical models optimize stable meaning, history and scan patterns. Neither shape is universally superior. A semantic layer can centralize measures, dimensions, entities, allowed joins and metric definitions, but it does not repair incorrect source grain or history.

Surrogate dimension keys decouple warehouse history from mutable or reused source business keys. Slowly changing dimension Type 1 overwrites an attribute when only the current value matters. Type 2 creates effective-dated versions when analysis must reproduce the attribute valid at a business time; joins require non-overlapping intervals, deterministic boundary rules and handling for early-arriving facts. Type 3 preserves limited prior state in additional columns and is less general. Conformed dimensions allow multiple fact tables to use the same definition and keys. Degenerate dimensions keep an identifier such as order number in a fact without a separate descriptive table. Role-playing dimensions reuse one dimension in roles such as order date and ship date.

Additive facts can be summed across all useful dimensions; semi-additive facts, such as account balance, cannot normally be summed across time; non-additive ratios should be derived from additive numerators and denominators. Store atomic facts where feasible and publish aggregates as governed accelerators rather than the sole history. Distinguish transaction facts, periodic snapshots and accumulating snapshots. Define null, unknown, not-applicable, late and deleted members explicitly. Avoid joins between two fact tables at incompatible grains, which can multiply rows; aggregate each side to a common grain or use an intentional bridge with allocation weights.

Logical modeling and physical design are related but separate. Partitioning, clustering, sort order, distribution, file size, compression and materialization should follow measured filters, joins, volumes and update patterns. Partition pruning or micro-partition pruning can reduce scanned data, but high-cardinality over-partitioning creates metadata and small-file costs. Distribution can colocate joins or replicate small dimensions, but skew and load cost matter. Clustering has ongoing maintenance cost and deserves representative-query evidence. Modern table formats can hide and evolve partition layouts, yet old and new files may retain different physical organizations while presenting one logical table.

Loads must be idempotent and version-aware. Use stable source identities, extraction positions and deterministic merge rules for inserts, updates, deletes and corrections. Publish related tables or snapshots coherently so consumers do not observe half a model version. Reconciliation covers row populations, key uniqueness, referential integrity, amounts, control totals, freshness, duplicate and late-event behavior—not merely job success. Lineage connects source datasets, transformations, runs and outputs, but lineage presence is not proof that the data is correct.

Physical layout mechanics that models must survive

BigQuery partitioned tables prune on ingestion-time or column partitions; clustering then sorts blocks inside partitions. Partitioning on a high-cardinality UUID creates millions of partitions and metadata cost without pruning benefit; clustering on a filter column that queries never use only adds maintenance. Redshift distribution styles (KEY, ALL, EVEN, AUTO) decide whether a join is colocated: KEY-distribute a large fact and its join dimension on the same column to avoid redistributing; ALL-replicate a small dimension; EVEN when there is no stable join key. Sort keys enable zone maps on blocks; a SORTKEY that does not match predicates will not skip. Snowflake stores columnar micro-partitions with min/max metadata; clustering depth measures how well overlapping ranges have been reduced—depth that stays high after reclustering is a cost leak, not a modeling win.

Iceberg hidden partitioning stores transform identity (identity, year, month, bucket, truncate) in metadata so writers need not add a user-visible partition column such as dt; queries on the source column still prune. Partition evolution can add a spec without rewriting old files, so a table can contain mixed physical layouts under one logical schema. Optimistic concurrency retries commits when manifests conflict; two MERGE jobs on the same snapshot without retry will fail one writer rather than silently interleave. PostgreSQL materialized views persist a query result and refresh explicitly; concurrent refresh still leaves a staleness window, and indexes on the MV are part of the serving contract. dbt MetricFlow semantic models declare entities, dimensions, measures, and metrics so consumers do not re-implement join paths; a metric that sums a semi-additive balance across dates is still wrong even if the YAML validates.

Compatibility and failure modes at grain

A new fact at a finer grain is not automatically compatible: dashboards that summed the coarser grain will double-count if both tables remain in the same semantic model without a version. SCD Type 2 intervals that use half-open [start, end) bounds versus inclusive end dates will duplicate or drop a day at month boundaries. Late-arriving dimensions that get a dummy key and later a real surrogate must not leave facts permanently on the dummy if the contract promised current-state joins. Measure bytes scanned versus bytes returned, partition pruning ratio, clustering depth or unsorted region size, MERGE conflict retries, and metric-level reconciliation against an independent control total—not only warehouse slot time. OpenLineage should bind the snapshot or partition published with the model version; a catalog screenshot of the star schema is not that binding.

History, privacy and security are model concerns. Restrict raw and sensitive dimensions, apply row/column policies at serving boundaries, minimize copied attributes, track purpose and retention, and propagate deletions through facts, dimensions, aggregates, caches, extracts, snapshots and replay. Time travel and backups need explicit retention and restore-time deletion handling. Test source schema change, business-key reuse, late fact/dimension, SCD overlap, restatement, duplicate, deletion, partial publish, concurrent writer, partition skew, small files and rollback. Measure consumer freshness, metric agreement, query tails, bytes scanned, cost, quality-rule coverage, lineage and recovery. A successful model produces stable, explainable decisions—not merely fast SQL.

Worked example: one order, $10 shipping, three lines

Order 8821: three lines totaling $90 merchandise plus $10 shipping at order grain. Finance asks for revenue including shipping.

fact grain	rows	naive SUM(merchandise + shipping)	vs $100 truth
order line, shipping copied onto every line	3	$90 + $30 = $120	+$20 double-count
order header only	1	$100	correct, loses SKU mix
line merchandise + allocated shipping ($3.33/3.34/3.33)	3	$100	matches, allocation rule is the contract

A star schema with the wrong grain is not “denormalized for speed.” It is a $20 lie per order. That table is the interview.