Data warehouse modeling
Star schemas, slowly changing dimensions, and incremental ETL patterns for analytics at scale.
Fact tables
Immutable append-only tables keyed by surrogate IDs. Partition by date, cluster by foreign dimension keys. Never update a fact row — insert corrections as offsetting entries.
Dimension tables
Type 2 SCDs with valid_from and valid_to ranges. Use a current flag for query convenience but enforce range exclusivity at the constraint level.
Incremental ETL
Watermark-based extraction with idempotent merge. Deduplicate late-arriving facts via a staging table and a deterministic conflict resolver before promoting to the fact table.
Prerequisites: Familiarity with SQL window functions and columnar storage engines.