Lemuel Calinog

home / projects

Projects

Two builds. One I made alone, where you can read every line. One at work, where the code is internal and what I can show you is the design and the decisions.

all repos (opens in a new tab)

Formula 1 analytics pipeline

Every Formula 1 season since 1950, pulled from a rate-limited public API, landed immutably in S3, loaded into Postgres, and modelled into a tested Kimball star schema with dbt. Orchestrated nightly by Airflow, served through Metabase.

read the project ↗ (opens in a new tab)

built
Solo, end to end
stack
dbt, Airflow, S3, Postgres, Metabase
grain
One row per driver, per race
coverage
1950 → current season

01 — the pipeline

The API is rate limited to 500 requests an hour, so the landing zone is immutable: raw responses go to S3 once and are never re-fetched. Everything downstream can be rebuilt from that copy without spending the budget again.

source

public API

500 requests / hour

landing

S3

Immutable raw responses

warehouse

Postgres

Loaded, then staged

model

dbt star schema

Facts, dimensions, tests

serving

Metabase

Dashboards, ad hoc SQL

orchestration Airflow: nightly, one DAG per stage, retries on the rate limit

02 — the model

Two fact tables at different grains, sharing conformed dimensions. Sprint results are a separate fact rather than a session_type column, because a fact whose grain needs a filter to be correct is a reliable source of wrong numbers.

fct_race_result fact
  • FKrace_key → dim_race
  • FKdriver_key → dim_driver
  • FKconstructor_key → dim_constructor
  • finish_position
  • points
  • status
fct_sprint_result fact
  • FKrace_key → dim_race
  • FKdriver_key → dim_driver
  • points
dim_driver dim
  • PKdriver_key
  • driver_name
  • nationality
dim_constructor dim
  • PKconstructor_key
  • constructor_name
  • base_country
dim_race dim
  • PKrace_key
  • season
  • round
  • FKcircuit_key → dim_circuit
dim_circuit dim
  • PKcircuit_key
  • circuit_name
  • country

Both facts point at the same dimensions, so a sprint weekend and a race weekend can be counted together. dim_circuit hangs off dim_race rather than off the facts: a race happens at one circuit, so the facts reach it through the race they already point at. Both calls are recorded in the repository with the alternatives that lost, so the diagram can be checked against the code rather than taken on trust.

03 — the test that earned its keep

One dbt test derives each championship total from the facts and compares it against the official standings. A row count would have passed. This did not.

tests/assert_championship_totals_match.sql
WITH derived AS (
  SELECT season, driver_key, SUM(points) AS derived_points
  FROM   fct_all_points          -- race + sprint
  GROUP BY 1, 2
)
SELECT s.season, s.official_points, d.derived_points
FROM   stg_official_standings s
JOIN   derived d USING (season, driver_key)
WHERE  s.official_points <> d.derived_points;
FAIL 1 row returned, expected 0
seasonofficialderiveddelta
2024437399−38

Sprint points were missing entirely. The fix was a second fact table and a union at the points layer. Without the test, nobody would have known to write it.

04 — why the full history mattered

~7.5 hto backfill every season since 1950, against a 500-request-per-hour budget
49% → 13%DNF rate, 1950s to 2020s. The trend only appears with the whole history loaded
1980sa bump in the decline, landing exactly where the turbo era belongs
decision logThe alternatives that lost are written down in the repository, with why.

Data warehouse build on Google Cloud

My team is building our company’s first data warehouse. Management had planned to hire a data engineer for it; we took it on ourselves, because we already understood what the business needed from the data.

built
With my team
stack
BigQuery, Dataform, Cloud Run, Zoho Analytics
my scope
One data mart, ingestion to dashboards
code
Internal, no public repository

01 — design and governance

I contributed to the data models, the architecture, and the security and governance decisions before anything was built, including how access would be limited and how changes would be provisioned.

02 — first domain delivered

I built Python ingestion, Dataform transformations and Cloud Run scheduling for the first source system we took on, served through Zoho Analytics.

03 — a data mart, end to end

Medallion layering in Dataform, from bronze raw declarations and silver staging through to dimension and fact models, mart and reporting tables. Scheduling and the reporting layer sit on top.

04 — a loader that matched by position

Raw responses landed in Bronze as CSV. BigQuery’s CSV loader matches columns by position rather than by name, so a column reordered upstream breaks the load. Moving Bronze to JSON Lines fixed it: JSON Lines matches by name, so a field that moves, or a new one that appears, lands where it belongs.

The code is internal, so the design is what I can show. The F1 pipeline is the one you can read line by line.

back to the summary