Analytics Engineering

The transformation
layer, tested

Business logic that lives inside one-off queries cannot be tested, versioned or traced. Modelling it as a layered graph turns analytics from a set of opinions into a build with a pass or fail result.

System: Passenger profiling platform
Scale: Layered model graph, refreshed nightly
Role: Sole architect and engineer
Stack: dbt / Dagster / DuckDB

The challenge

Once the data lived somewhere queryable, the question stopped being where it is and became whose definition of the metric is the right one.

The approach

Model the logic as a layered graph with declared expectations and a recorded lineage, refreshed on a schedule like any other build.

The result

One authoritative definition per metric, tracing a number to its source is a lookup rather than an investigation, and regressions surface at build time.

01RawThe records exactly as they arrived, untouched. Keeping this layer honest means any mistake made later can be found and corrected without going back to the source system.S3 / Parquet 02StagingOne thin layer that does nothing clever: it gives every column a real type, fixes names, and drops what is not needed. Keeping cleaning separate from logic is what makes the layers above readable.dbt / typed sources 03IntermediateThis is where the business rules live — what counts as a matched passenger, how a dwell window is defined, which flights belong to a terminal. Written once here rather than re-invented in each report.dbt models / Jinja 04MartsThe tables the dashboards actually read, each one the single agreed answer to a question. Every layer carries expectations that are checked on each run, including deliberate wrong-answer tests that prove the checks are capable of failing — a check that has never failed is not evidence of anything.dbt tests / Dagster assets / scheduled refresh

Fig. 05 — Every number on a dashboard can be traced back to a raw record

Hover any step to read what it doesTap any step to read what it does

The transformationlayer, tested

The challenge

The approach

The result

The transformation
layer, tested