Project · Analytics engineering
Airbnb analytics, the Modern Data Stack way
End-to-end analytics engineering: S3 → Snowflake → dbt, Medallion layers, SCD Type 2 history and tested models.
- 3Medallion layers
- SCD2history via dbt snapshots
- 4materialisation types
Architecture
- 01AWS S3raw files
- 02Snowflake rawexternal stage
- 03dbt silverclean · typed · tested
- 04dbt goldstar schema · OBT
- 05BIanalysis-ready
dbt snapshots capture every change to slowly-changing entities (SCD Type 2).
Context
Public Airbnb data is messy, changes over time, and is big enough to make shortcuts hurt — a good playground for production-grade analytics engineering.
The challenge
Build a warehouse an analyst could trust: secure ingestion, clear layers, a history of changes, and tests that fail loudly before bad data reaches a dashboard.
What I built
- Secure ingestion from AWS S3 into Snowflake using storage integrations and external stages, scoped with IAM.
- A Medallion architecture: raw → silver → gold.
- Modular dbt models using incremental, ephemeral, table and view materialisations, with parameterised Jinja macros.
- Dimensional modelling for BI: a star schema and a One Big Table.
- Change history with SCD Type 2 using dbt snapshots.
- Generic and singular dbt tests (uniqueness, not-null, referential integrity) with severity levels, and environments managed through a Git workflow.
Outcomes
- 01A layered warehouse where every model traces back to its source.
- 02Point-in-time history: what any record looked like on any date.
- 03Tests with severity levels — warnings versus blocking failures.
Stack
- dbt Core
- Snowflake
- AWS S3
- AWS IAM
- Jinja
- SQL
- Git
Facing a similar problem?
I’m happy to talk it through — whether you’re hiring or need a freelance mission.