From One Wide Table To A Joinable Analytics Mart
The new learning in this task is not a new API or a new dashboard. It is a new way to organize the curated data so it behaves like a small warehouse: dimensions describe things, facts record events, and keys make joins reliable.
What Exactly Was New?
Task 01 answered: Can I ingest, clean, validate, and store data?
Task 02 answers: Can I model that stored data like an analytics database?
The original table still exists. The new task adds a second layer derived from it:
dim_location, dim_parameter, dim_sensor, and
fact_air_quality_measurement.
Before And After
Before: Curated Landing Table
air_quality_measurements is wide and convenient.
- One row includes location, pollutant, sensor, value, timestamps, and quality fields.
- Great for a first dashboard and simple SQL.
- Repeats location and pollutant descriptions on every measurement row.
After: Relational Analytics Mart
The same information is split by meaning.
- Locations live once in
dim_location. - Pollutants live once in
dim_parameter. - Sensors connect locations to pollutants in
dim_sensor. - Readings live in
fact_air_quality_measurement.
The New Tables
dim_location
One row per monitoring location.
location_id is the primary key.
dim_parameter
One row per pollutant and unit pair.
pm25|ug/m3 is an example key.
dim_sensor
One row per sensor.
Links a location to a pollutant.
fact_air_quality_measurement
One row per reading.
Stores the timestamp and measured value.
Why the word grain matters
| Table | One Row Means | Example Question |
|---|---|---|
dim_location | One monitoring location | Which cities do we monitor? |
dim_parameter | One pollutant/unit pair | Which pollutants exist in the dataset? |
dim_sensor | One sensor at a location for a pollutant | Which sensors measure PM2.5? |
fact_air_quality_measurement | One sensor reading at one timestamp | Which readings are highest? |
Sample Rows From The New Tables
These rows are small on purpose. The goal is to see what each table stores and how the keys connect.
dim_location
| location_id | location_name | country_code | provider_name | latitude | longitude |
|---|---|---|---|---|---|
| 8118 | New Delhi | IN | AirNow | 28.63576 | 77.22445 |
| 910001 | Mumbai | IN | Sample Provider | 19.076 | 72.8777 |
| 910002 | Delhi | IN | Sample Provider | 28.6139 | 77.209 |
Each row is one monitoring location.
dim_parameter
| parameter_key | parameter | parameter_display_name | unit |
|---|---|---|---|
o3|ppm | o3 | Ozone | ppm |
pm25|ug/m3 | pm25 | PM2.5 | ug/m3 |
Each row is one pollutant and unit pair. Many sensors can point to the same parameter row.
dim_sensor
| sensor_id | location_id | parameter_key |
|---|---|---|
| 23534 | 8118 | pm25|ug/m3 |
| 23535 | 8118 | o3|ppm |
| 999101 | 999001 | pm25|ug/m3 |
| 999102 | 999001 | o3|ppm |
Each row is one sensor at one location measuring one pollutant.
fact_air_quality_measurement
| measurement_key | location_id | sensor_id | parameter_key | measured_at_utc | value |
|---|---|---|---|---|---|
910002:9100021:2026-07-29T14:00:00Z | 910002 | 9100021 | pm25|ug/m3 | 2026-07-29T14:00:00Z | 86.2 |
910010:9100101:2026-07-29T14:00:00Z | 910010 | 9100101 | pm25|ug/m3 | 2026-07-29T14:00:00Z | 63.4 |
910007:9100071:2026-07-29T14:00:00Z | 910007 | 9100071 | pm25|ug/m3 | 2026-07-29T14:00:00Z | 57.7 |
Each row is one measurement event. Location and pollutant names come from joins.
Refresh Flow
1. Land Curated Rows
The pipeline writes or updates air_quality_measurements.
2. Rebuild Dimensions
The mart refresh derives locations, parameters, and sensors from the landing table.
3. Rebuild Facts
The fact table is rebuilt from valid measurement rows with keys back to dimensions.
This is idempotent for the local learning project: rerunning the same sample does not duplicate facts. Larger systems often evolve this into incremental merge logic or dbt models.
Practice Query
This query is the main new behavior: facts joined to descriptive dimensions.
select
location.location_name,
parameter.parameter_display_name,
parameter.unit,
fact.value,
fact.measured_at_utc
from fact_air_quality_measurement as fact
join dim_location as location
on location.location_id = fact.location_id
join dim_parameter as parameter
on parameter.parameter_key = fact.parameter_key
order by fact.value desc;
Run the full workbook:
sqlite3 data/curated/air_quality_pulse.db < sql/task_02_relational_modeling_queries.sql
Task 02 Files
| File | Purpose |
|---|---|
src/air_quality_pulse/relational_mart.py | Creates and refreshes the relational mart. |
tests/task_02/test_relational_mart.py | Proves the mart is joinable and idempotent. |
sql/task_02_relational_modeling_queries.sql | Practice queries for table counts, joins, grain checks, and query plans. |
docs/tasks/02-relational-modeling.md | Markdown version of the learning task. |