352 lines
8.0 KiB
Markdown
352 lines
8.0 KiB
Markdown
## Definition
|
|
|
|
A **Fact Table** is a table in an analytical data model that records **business events, transactions, or measurable observations**.
|
|
|
|
Examples of business events include:
|
|
|
|
- A user watching a video
|
|
- A customer making a purchase
|
|
- An advertisement being displayed
|
|
- A website session occurring
|
|
- A subscription being created
|
|
|
|
Fact tables typically contain:
|
|
|
|
1. **Keys** that connect the event to descriptive information stored in [[Dimension Table|Dimension Tables]].
|
|
2. **Measures** representing numerical values that can be analyzed.
|
|
|
|
---
|
|
|
|
## Simple Mental Model
|
|
|
|
Think:
|
|
|
|
> **Fact Table = What happened, and how much?**
|
|
|
|
For a media company:
|
|
|
|
> Someone watched something, somewhere, at a certain time, for 125 seconds.
|
|
|
|
The **event** is the viewing.
|
|
|
|
The **125 seconds** is a measure.
|
|
|
|
The information describing who, what, when, and where comes from [[Dimension Table|Dimension Tables]].
|
|
|
|
---
|
|
|
|
## How It Works
|
|
|
|
Imagine a table called:
|
|
|
|
`fact_viewing`
|
|
|
|
|viewing_id|content_id|user_id|platform_id|date_id|watch_seconds|
|
|
|---|---|---|---|---|---|
|
|
|1001|501|7821|3|20260719|125|
|
|
|1002|502|9921|1|20260719|1800|
|
|
|1003|501|8812|2|20260719|340|
|
|
|
|
Each row represents something that happened.
|
|
|
|
For example:
|
|
|
|
> User 7821 watched Content 501 on Platform 3 for 125 seconds.
|
|
|
|
The columns:
|
|
|
|
- `content_id`
|
|
- `user_id`
|
|
- `platform_id`
|
|
- `date_id`
|
|
|
|
connect the event to [[Dimension Table|Dimension Tables]].
|
|
|
|
The column: `watch_seconds` is a [[Measure]].
|
|
|
|
Measures are values that can often be aggregated.
|
|
|
|
Examples include:
|
|
|
|
- Watch seconds
|
|
- Revenue
|
|
- Quantity
|
|
- Cost
|
|
- Clicks
|
|
- Impressions
|
|
- Number of sessions
|
|
|
|
For example:
|
|
|
|
`SUM(watch_seconds)`
|
|
|
|
could be used to calculate total watch time.
|
|
|
|
---
|
|
|
|
## Grain
|
|
|
|
One of the most important properties of a Fact Table is its [[Grain]].
|
|
|
|
The grain, or **granularité**, answers:
|
|
|
|
> **What does ONE ROW represent?**
|
|
|
|
Before designing a Fact Table, its grain should be clearly defined.
|
|
|
|
For example, `fact_viewing` could have the grain:
|
|
|
|
> One row represents one viewing session for one piece of content by one user.
|
|
|
|
If a user watches the same episode three separate times:
|
|
|
|
`3 viewing sessions = 3 rows`
|
|
|
|
But another Fact Table could have a different grain:
|
|
|
|
> One row represents one user/content/day combination.
|
|
|
|
In that model, watching the same episode three times on the same day might produce:
|
|
|
|
`1 row`
|
|
|
|
with:
|
|
|
|
`session_count = 3`
|
|
|
|
The grain determines which calculations are valid.
|
|
|
|
For this reason, one of the most useful questions when examining analytical data is:
|
|
|
|
> **"What does one row represent?"**
|
|
|
|
---
|
|
|
|
## Example
|
|
|
|
Imagine a report states:
|
|
|
|
> Total audience: 1,200,000
|
|
|
|
The underlying Fact Table has this grain:
|
|
|
|
> One row = one viewing session.
|
|
|
|
If the calculation is:
|
|
|
|
`COUNT(viewing_id)`
|
|
|
|
then the result is actually counting **viewing sessions**, not necessarily viewers.
|
|
|
|
One person could watch five times:
|
|
|
|
`5 viewing sessions`
|
|
|
|
but still represent:
|
|
|
|
`1 unique viewer`
|
|
|
|
Depending on the data model, calculating unique viewers might instead require something such as:
|
|
|
|
`COUNT(DISTINCT user_id)`
|
|
|
|
Understanding the [[Grain]] helps determine whether a metric actually represents what its label claims.
|
|
|
|
---
|
|
|
|
## Types of Fact Tables
|
|
|
|
Not every Fact Table represents an individual transaction.
|
|
|
|
Three common types are:
|
|
|
|
### Transaction Fact Table
|
|
|
|
One row represents an individual event.
|
|
|
|
Examples:
|
|
|
|
- One purchase
|
|
- One viewing session
|
|
- One ad impression
|
|
|
|
### Periodic Snapshot Fact Table
|
|
|
|
One row represents the state of something at a regular interval.
|
|
|
|
Examples:
|
|
|
|
- Daily subscriber count
|
|
- Monthly account balance
|
|
- Weekly inventory level
|
|
|
|
### Accumulating Snapshot Fact Table
|
|
|
|
One row tracks a process as it progresses through multiple stages.
|
|
|
|
For example:
|
|
|
|
Order placed → Order processed → Order shipped → Order delivered
|
|
|
|
The same row can be updated as the process progresses.
|
|
|
|
---
|
|
|
|
## How It Fits Into the Bigger Picture
|
|
|
|
As we learned in [[Medallion Architecture]], data can progressively move from raw to business-ready:
|
|
|
|
Bronze → Silver → Gold
|
|
|
|
A simplified media data architecture could look like:
|
|
|
|
Source systems
|
|
↓
|
|
[[ETL vs ELT]]
|
|
↓
|
|
Bronze: Raw viewing events
|
|
↓
|
|
Silver: Clean and standardized viewing events
|
|
↓
|
|
Gold: Analytical models
|
|
↓
|
|
`fact_viewing`
|
|
↓
|
|
Power BI
|
|
|
|
A Fact Table can therefore be part of the business-ready data model exposed to analytics tools.
|
|
|
|
The Fact Table does not normally exist alone.
|
|
|
|
It connects to [[Dimension Table|Dimension Tables]], creating analytical models such as a [[Star Schema]].
|
|
|
|
---
|
|
|
|
## My Company / Real-World Context
|
|
|
|
In a media company, potential Fact Tables could include:
|
|
|
|
- `fact_viewing`
|
|
- `fact_web_sessions`
|
|
- `fact_ad_impressions`
|
|
- `fact_subscriptions`
|
|
- `fact_linear_audience`
|
|
|
|
For `fact_viewing`, the measures might include:
|
|
|
|
- `watch_seconds`
|
|
- `sessions`
|
|
- `starts`
|
|
- `completions`
|
|
|
|
The Fact Table could connect to dimensions describing:
|
|
|
|
- Content
|
|
- Users
|
|
- Platforms
|
|
- Devices
|
|
- Dates
|
|
|
|
This could allow Power BI to answer questions such as:
|
|
|
|
> How many hours were watched last month?
|
|
|
|
> Which content generated the most viewing?
|
|
|
|
> Which platforms have the highest engagement?
|
|
|
|
> How has viewing changed over time?
|
|
|
|
The accuracy of those answers depends heavily on the [[Grain]] and the definition of the underlying [[Measure|Measures]].
|
|
|
|
---
|
|
|
|
## CTO Perspective
|
|
|
|
A CTO does not necessarily need to personally design every Fact Table.
|
|
|
|
However, when reviewing data or analytics, understanding the underlying Fact Table helps determine whether the reported metric is meaningful.
|
|
|
|
A particularly powerful question is:
|
|
|
|
> **"What's the grain of the underlying Fact Table?"**
|
|
|
|
Or, in simpler language:
|
|
|
|
> **"What exactly does one row represent?"**
|
|
|
|
This helps identify situations where:
|
|
|
|
- Sessions are presented as users
|
|
- Transactions are presented as customers
|
|
- Views are presented as audience
|
|
- Aggregated data is incorrectly aggregated again
|
|
|
|
A technology leader should also understand where the business logic behind important metrics is defined and whether different analysts are working from the same governed model.
|
|
|
|
This connects directly to [[Data Governance]].
|
|
|
|
---
|
|
|
|
### Questions to Ask
|
|
|
|
- What does one row in this Fact Table represent?
|
|
- What is the [[Grain]]?
|
|
- What are the primary measures?
|
|
- Which [[Dimension Table|Dimension Tables]] does it connect to?
|
|
- Where is the business logic defined?
|
|
- Can this measure safely be summed?
|
|
- Are we counting events or unique entities?
|
|
- Can we trace this metric back through [[Data Lineage]] to the original source?
|
|
- Are all analysts using the same governed definition?
|
|
|
|
---
|
|
|
|
## Meeting Scenario
|
|
|
|
**Situation:**
|
|
|
|
An analyst presents:
|
|
|
|
> "Our total audience increased by 25% this month."
|
|
|
|
You know the underlying data comes from viewing events, but you don't know exactly how "audience" was calculated.
|
|
|
|
**Possible response:**
|
|
|
|
> "Before we interpret the increase, can you clarify how we're defining audience and what the grain of the underlying Fact Table is? Are we measuring unique viewers, viewing sessions, or total views?"
|
|
|
|
The analyst responds:
|
|
|
|
> "We're counting viewing sessions."
|
|
|
|
You could respond:
|
|
|
|
> "Then I think we should be careful calling this audience growth. What we can confidently say is that viewing sessions increased by 25%. If we want to measure audience growth, we should use the appropriate unique-viewer definition."
|
|
|
|
This changes the conversation from challenging the analyst to clarifying the **definition and interpretation of the metric**.
|
|
|
|
---
|
|
|
|
## Key Takeaways
|
|
|
|
- A **Fact Table** records business events or measurable observations.
|
|
- A [[Measure]] is a numeric value being analyzed.
|
|
- [[Grain]] defines exactly what one row represents.
|
|
- Understanding the grain is essential before interpreting metrics.
|
|
- Fact Tables connect to [[Dimension Table|Dimension Tables]] to provide context.
|
|
- Fact Tables and Dimension Tables commonly form a [[Star Schema]].
|
|
- A CTO should always understand what is actually being counted before accepting a KPI.
|
|
|
|
## Related Concepts
|
|
|
|
- [[Dimension Table]]
|
|
- [[Grain]]
|
|
- [[Measure]]
|
|
- [[Star Schema]]
|
|
- [[Medallion Architecture]]
|
|
- [[ETL vs ELT]]
|
|
- [[Data Warehouse]]
|
|
- [[Data Governance]]
|
|
- [[Data Lineage]]
|
|
- [[Semantic Layer]] |