End-to-end airline review analytics: scrape → S3 → Snowflake → dbt star schema → Mode dashboard.
One governed metric grain, three airline levers, five pillars, Show → Why → What-if.
Source was AirlineQuality.com (Skytrax) — the site is permanently closed;
the platform runs on historical scrapes landed while it was live.
Mark Pham · Analytics Engineering ownership across EL + Transform + DataOps
EL repo — Dataset-chained Airflow DAGs (crawl → process → snowflake)EL repo — S3 landing zone raw/ + processed/
Required stack + extras
Tech stack — and why I chose each
AirflowAWS S3SnowflakeGitHub Actions + dbt
Layer
Choice
Why (trade-off)
Warehouse
Snowflake
Managed cloud DWH — COPY INTO bulk loads, RBAC, tag-based masking, compute decoupled from storage.
Transform
dbt Core
Tests, docs, contracts, incremental, defer/state — portable vs Cloud lock-in for this project.
BI
Mode Analytics
Warehouse-direct, SQL-first — every chart is a defensible query; Delta / Frontier / Spirit Mode slices from analyst partners.
Orchestration
Airflow + Datasets + cosmos
Event-driven crawl→process→load; cosmos makes dbt graph first-class tasks.
Ingestion
Custom Python scraper
AirlineQuality.com had no public API (and is now permanently closed) — Fivetran/Airbyte couldn’t land HTML reviews; polite crawl + parse into a replayable S3 archive instead.
Landing
AWS S3
Replayable raw archive; decouple scrape from load; cheaper than warehouse history.
IaC
Terraform (AWS + Snowflake roots)
Every bucket, role, schema, warehouse, mask, CloudFront, OIDC provider — reproducible, reviewable, no click-ops drift.
CI/CD + Auth
GitHub Actions + OIDC
PR: SQLFluff + slim state:modified+ build; CD: defer/favor-state; GHA assumes AWS IAM via OIDC — no static cloud keys.
Pillar 1 · Data Modeling & Architecture
Kimball star + Medallion layers
1
Business process
Customer review submissions on AirlineQuality.com (site now permanently closed — historical scrape).
2
Grain Most important
One row per review in fct_review.
3
Dimensions
Customer · Airline · Aircraft · Location · Date.
4
Facts
Service ratings → average_rating / recommended.
Bronze
RAW / S3
S3 raw/ + processed/
RAW.AIRLINE_REVIEWS · LOAD_AUDIT
Silver
SOURCE → INT
stg__skytrax_reviews
int_reviews_cleaned
Gold
MARTS
5 dims + incremental fct_review
fct_review · agg_airlines_review + snap
Keys
Deterministic surrogates · role-playing date + location (origin / dest / transit).
Transaction fact
One event row per review in fct_review —
not periodic snapshot / accumulating.
{{ config(
materialized='incremental',
unique_key='review_id',
incremental_strategy='merge',
) }}
-- updated_at > max(source_updated_at) -- pure HWM
-- lookback only if filtering on event time
-- backfill: --full-refresh
EL — process DAG (clean → S3 processed → quality)EL — snowflake DAG (COPY INTO + audit)
Problem: Only 5.7% of reviewers would recommend Frontier — worst of the three. In‑flight entertainment scores 1.09/5, below Allegiant and Spirit on the same fare tier.
Action: Invest first in Economy entertainment and seat comfort so cheap fares still feel worth it.
Problem: Overall rating stuck at 1.59/5. Entertainment (1.11) and Wi‑Fi (1.13) drag scores down; Business travellers are the unhappiest group.
Action: Guarantee working Wi‑Fi / entertainment, rebuild the Business product, and fix operations at the worst airports (MIA / MEX / GOT).
Delta — Economy vs Premium driver correlationsFrontier — ULCC recommendation peer gapSpirit — Business travellers lowest
Pillar 4 · Semantic layer
MetricFlow — ask for a metric, not a join
What is a semantic layer?
A governed translation layer between business language
(“average rating by band”) and warehouse SQL
(tables, joins, grain, aggregations). One definition of each metric —
Mode, CLI, and future tools all consume the same logic.
Why it matters
One source of truth — stop copy-pasting KPI SQL across dashboards
Grain-safe — metrics know the fact grain; less accidental double-count
Team project, honestly framed: I led extract–load, transformation, and DataOps end-to-end; analyst partners own the Mode insight layer — and every metric's SQL and grain is mine to defend.
Close
Show it. Justify it. Bend it.
Skytrax is a full AE story: dimensional medallion model, incremental dbt, governed PII,
three Mode levers (Delta cabin · Frontier ULCC · Spirit chronic), and DataOps that only rebuilds what changed.
Questions welcome — pick an area and we’ll go deeper.
The spine
EL repo → land & load Transform repo → dims/facts + CI/CD Dashboard repos → airline levers