This lesson teaches that a fact in data modeling should be an atomic event that cannot be broken down further, such as a single step rather than a mile. It explains that facts are immutable and should be logged in their smallest form so they can be aggregated later. The concept is essential for data engineers and analysts building reliable data warehouses and ETL pipelines. It also highlights practical tools like SQL and dbt for handling these facts.
Insights
1. Atomic Fact Definition
1.1 A fact is a single event that cannot be decomposed further, e.g., one step taken by a Fitbit.
1.2 Using atomic facts ensures you can roll up to higher-level metrics like miles, days, or weeks.
2. Aggregation Hierarchy
2.1 Atomic facts are aggregated into larger units (steps → mile, mile → day, day → week).
2.2 This hierarchy allows flexible reporting and prevents loss of detail.
3. Immutability of Facts
3.1 Facts never change once logged; new data is appended rather than updated.
3.2 This guarantees auditability and simplifies versioning in data pipelines.
4. Fact vs Dimension Modeling
4.1 Fact tables store atomic events; dimension tables store descriptive attributes like time or user.
4.2 Separating them enables efficient joins and faster query performance.
5. Tooling for Fact Handling
5.1 SQL is used for aggregations and rollups.
5.2 dbt can orchestrate transformations and maintain lineage of fact tables.
6. Common Pitfall: Composite Logging
6.1 Logging a composite event (e.g., a mile) hides underlying steps.
6.2 It leads to loss of granularity and limits future analytical possibilities.