Fact tables and dimension tables are the two foundational structures in data warehouse design. Fact tables store measurable business events (sales, transactions, metrics) with numerical values and foreign keys, while dimension tables provide descriptive context (products, customers, time, location). Three types of fact tables exist: transaction, periodic snapshot, and accumulating snapshot. Dimension types include slowly changing dimensions (SCD), conformed dimensions, and junk dimensions. Common design mistakes include mixing data types, overloading dimension tables, neglecting SCDs, and inconsistent naming conventions. Best practices emphasize keeping fact and dimension tables clearly separated, using narrow data models, and storing timestamps in UTC for consistent time-based analysis.
