Task 02: Relational Modeling

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.

New Concept
Grain
What one row means
New Tables
3 dims + 1 fact
Location, parameter, sensor, reading
New Safety
Foreign keys
Facts must match dimensions
New Skill
Joins
Analytics across related tables

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

TableOne Row MeansExample Question
dim_locationOne monitoring locationWhich cities do we monitor?
dim_parameterOne pollutant/unit pairWhich pollutants exist in the dataset?
dim_sensorOne sensor at a location for a pollutantWhich sensors measure PM2.5?
fact_air_quality_measurementOne sensor reading at one timestampWhich 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_idlocation_namecountry_codeprovider_namelatitudelongitude
8118New DelhiINAirNow28.6357677.22445
910001MumbaiINSample Provider19.07672.8777
910002DelhiINSample Provider28.613977.209

Each row is one monitoring location.

dim_parameter

parameter_keyparameterparameter_display_nameunit
o3|ppmo3Ozoneppm
pm25|ug/m3pm25PM2.5ug/m3

Each row is one pollutant and unit pair. Many sensors can point to the same parameter row.

dim_sensor

sensor_idlocation_idparameter_key
235348118pm25|ug/m3
235358118o3|ppm
999101999001pm25|ug/m3
999102999001o3|ppm

Each row is one sensor at one location measuring one pollutant.

fact_air_quality_measurement

measurement_keylocation_idsensor_idparameter_keymeasured_at_utcvalue
910002:9100021:2026-07-29T14:00:00Z9100029100021pm25|ug/m32026-07-29T14:00:00Z86.2
910010:9100101:2026-07-29T14:00:00Z9100109100101pm25|ug/m32026-07-29T14:00:00Z63.4
910007:9100071:2026-07-29T14:00:00Z9100079100071pm25|ug/m32026-07-29T14:00:00Z57.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

FilePurpose
src/air_quality_pulse/relational_mart.pyCreates and refreshes the relational mart.
tests/task_02/test_relational_mart.pyProves the mart is joinable and idempotent.
sql/task_02_relational_modeling_queries.sqlPractice queries for table counts, joins, grain checks, and query plans.
docs/tasks/02-relational-modeling.mdMarkdown version of the learning task.