End-to-End Analytics Platform

Skytrax Reviews Analytics Platform

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

Delta Air Lines aircraft
Insight layer — Delta · Frontier · Spirit Mode slices on the same marts

Agenda · ≤ 1.5h

Five pillars. One rhythm.

Click a block to jump — every pillar runs Show → Why → What-if.

Every pillar: Show Why What-if

End-to-end · how the data flows

Architecture at a glance

SOURCE EXTRACT LAKE LOAD WAREHOUSE + TRANSFORM CONSUMERS Terraform · GHA · OIDC control plane · provision + deploy AirlineQuality closed site shut down permanently Python scraper BS4 · thread pool Python cleaner normalize · ISO dates S3 raw / processed type · YYYY/MM quality gate COPY INTO S3 → Snowflake LOAD_AUDIT reconcile rows Snowflake + dbt Snowflake RAW → SOURCE → MARTS dbt models stg → int → dims + fct dbt CI/CD slim CI · defer deploy dbt run ✓ Mode airline dashboard dbt Docs CloudFront Analysts DEV_* · MARTS Airflow on Astronomer crawl → process → snowflake · cosmos DbtDag · quality + reconcile solid = primary flow · dotted = control plane
primary data flow control plane → S3 + warehouse Repos: umbrella · extract_load · transformation · delta · frontier · spirit

System map

Each repo is one part of the pipeline

01

Extract

Scrape 4 review types in parallel → date-partitioned CSVs.

skytrax_reviews_extract_load
02

Stage + Load

Clean → S3 → quality gates → COPY INTO RAW.

skytrax_reviews_extract_load
03

Transform

Medallion → star: dims, incremental fact, airline agg + snapshots.

skytrax_reviews_transformation
04

Orchestrate / Ops

Airflow + cosmos · slim CI/CD · Terraform · email on fail.

extract_load + transformation
05

Insight

Same marts → Mode: Delta · Frontier · Spirit.

delta · frontier · spirit
Airflow DAGs list for skytrax crawl process snowflake
EL repo — Dataset-chained Airflow DAGs (crawl → process → snowflake)
S3 landing bucket raw and processed folders
EL repo — S3 landing zone raw/ + processed/

Required stack + extras

Tech stack — and why I chose each

Airflow skytrax_crawl DAG graph
Airflow
S3 raw partitioned files
AWS S3
Snowflake schemas and users
Snowflake
GitHub Actions dbt CI tests passing
GitHub Actions + dbt
LayerChoiceWhy (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.

Star schema ERD fct_review and dimensions
Show — star ERD · 5 dims + fct_review

Pillar 1 · Show

dbt lineage — staging → dims → fact → exposure

dbt lineage graph including agg_airlines_review and snapshots
Show — live dbt docs lineage · source → stg → dims / fact → agg + snap · exposure

Pillar 2 · Data Transformation

ELT practices + incremental fact

Pipeline inside dbt

  • Staging: dedup + deterministic review_id
  • Intermediate: clean / normalize
  • Incremental fct_review merge on review_id
  • HWM on updated_at — no lookback (watermark moves with each change)
  • Lookback only for event-time filters (e.g. date_submitted)
  • Airline agg table + check-strategy snapshot
  • Contract + macros (clean_airline_name, apply_pii_mask)
{{ 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
Airflow process DAG
EL — process DAG (clean → S3 processed → quality)
Airflow snowflake load DAG
EL — snowflake DAG (COPY INTO + audit)

Pillar 2 · Aggregates & history

Airline scorecards + SCD2 score drift

agg_airlines_review

  • Grain: one row per airline with ≥1 fact review
  • Rollup from fct_review + dim_airline
  • Volume: review / verified / recommended / layover counts
  • Quality: avg_rating + 7 service averages
  • Bands: good / medium / bad / unknown counts
  • Rates: pct_recommended, pct_verified
  • Contract enforced — scorecard-safe for Mode

Why not query the fact every time?

Pre-aggregated airline grain for ranking & peer compare — Mode and analysts hit a stable scorecard table instead of re-rolling 160K+ fact rows.

snap_agg_airlines_review

  • Schema: SNAPSHOTS · unique key airline_id
  • check strategy on metric columns
  • New SCD2 version when counts / avgs / rates change
  • dbt_loaded_at excluded — no-op rebuild ≠ false change
  • Also: snap_skytrax_reviews (timestamp) audits review edits upstream
{% snapshot snap_agg_airlines_review %} {{ config( schema='SNAPSHOTS', unique_key='airline_id', strategy='check', check_cols=[…metrics…], -- not dbt_loaded_at ) }} select … from {{ ref('agg_airlines_review') }} {% endsnapshot %}

Pillar 3 · Data Governance

Quality · observability · sensitive data

Quality + observability

  • EL: schema / null-rate / rating-range; reject by date
  • EL: COPY INTO → LOAD_AUDIT
  • dbt: unique / not_null / relationships / accepted_values
  • dbt_expectations + unit + singular + Spirit fleet plausibility (warn)
  • Freshness: warn 3d / error 7d · Elementary volume anomaly on fct_review

Security & PII

  • Tag masking on name / nationality (RAW)
  • PII_HASH_MASK on dim_customer
  • ANALYST sees hash; TRANSFORMER sees clear
  • RBAC + DEV_* schemas; failure alerts on EL + CD email
Snowflake ANALYST role select from dim_customer with hashed customer_name and nationality
Show — USE ROLE SKYTRAX_ANALYST · SELECT * FROM MARTS.DIM_CUSTOMER — CUSTOMER_NAME / NATIONALITY hashed

Pillar 3 · PII at read time

How PII_HASH_MASK works

Plaintext stays in dim_customer. Snowflake decides what each role sees on SELECT.

1. Terraform creates the policy

Object: MARTS.PII_HASH_MASK · masking_policies.tf

case
  when is_role_in_session('SKYTRAX_ADMIN')
    or is_role_in_session('SKYTRAX_TRANSFORMER')
  then val
  else sha2(val, 256)
end
Exempt · ADMIN / TRANSFORMER → plaintext Masked · ANALYST / Mode → SHA-256

2. dbt re-attaches after every build

Table rebuild drops column masks — post-hook re-applies. TRANSFORMER needs APPLY.

post_hook="{{ apply_pii_mask(['customer_name', 'nationality']) }}"

alter table ...dim_customer modify
  column customer_name set masking policy ...pii_hash_mask,
  column nationality   set masking policy ...pii_hash_mask
Query
SELECT customer_name
FROM dim_customer
Snowflake
Applies PII_HASH_MASK
ADMIN / TRANSFORMER
"Jane Doe"
Real value
Any other role
a3f2c1…
sha2(val, 256)

Same row, different sessions — joins and filters on customer_name / nationality also see the role-specific value.

Pillar 3 · Show → Why → What-if

Data Governance

Show

Where it lives

  • quality.py + LOAD_AUDIT
  • Masking Terraform + PII_HASH_MASK on dim_customer
  • Live: USE ROLE SKYTRAX_ANALYST → hashed name / nationality
  • Live: USE ROLE SKYTRAX_TRANSFORMER → clear text
  • dbt test · LOAD_AUDIT · Elementary volume warn
Why

Trade-offs

  • Non-blocking quality (quarantine by date)
  • Hash mask keeps joins usable
  • Policy at warehouse read-time
  • Elementary in warehouse — anomalies + test history survive beyond GHA logs
  • Warn first — volume baseline needs history; don’t fail deploys while learning
What-if

Live adaptations

  • Fail the DAG if >5% of dates quarantine
  • “Volume cliff overnight” → Elementary warn → promote to error after baseline is trusted
  • Publish edr report beside CloudFront dbt docs
  • Row access policy by airline partner · time-travel rollback after a bad load
ANALYST role dim_customer with hashed PII columns
Show — ANALYST: dim_customer name / nationality hashed
Snowflake RBAC users and medallion schemas
Show — RBAC + schemas (RAW→MARTS); TRANSFORMER sees clear

Pillar 4 · Data Insight

Industry bar vs Mode focus airlines

Same grain as Mode — unweighted airline avgs from agg_airlines_review (553 airlines · 117k reviews).

Industry

Mode baseline · avg across airlines

2.59
Avg rating
2.63
Value for money
2.62
Food & beverage
3.02
Cabin staff
1.62
Wi‑Fi
2.7
Seat comfort

Delta

Below industry · 2,912 reviews

2.49
Avg rating vs 2.59
2.25
Value for money vs 2.63
2.58
Food & beverage vs 2.62
2.87
Cabin staff vs 3.02
2.34
Wi‑Fi vs 1.62
2.6
Seat comfort vs 2.7

Spirit

Chronic lows · 4,698 reviews

1.59
Avg rating vs 2.59
1.60
Value for money vs 2.63
1.36
Food & beverage vs 2.62
1.95
Cabin staff vs 3.02
1.13
Wi‑Fi vs 1.62
1.6
Seat comfort vs 2.7

Frontier

Weakest of set · 3,533 reviews

1.43
Avg rating vs 2.59
1.39
Value for money vs 2.63
1.26
Food & beverage vs 2.62
1.78
Cabin staff vs 3.02
1.06
Wi‑Fi vs 1.62
1.5
Seat comfort vs 2.7

Pillar 4 · Data Insight

One insight, one action — per airline

Delta

Dashboard PDF →

  • Problem: One fix won’t work — Economy cares about staff / food / value; Premium cares about seat / dining. Wi‑Fi is weak for both.
  • Action: Split the product by cabin (Economy value, Premium comfort) and roll out better Wi‑Fi fleet-wide, starting at ATL / JFK / LAX.

Frontier

Dashboard PDF →

  • 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.

Spirit

Dashboard PDF →

  • 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 correlation heatmap
Delta — Economy vs Premium driver correlations
ULCC recommendation rate comparison
Frontier — ULCC recommendation peer gap
Spirit satisfaction by traveller type
Spirit — 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
  • Self-serve — discover metrics + dimensions; engine compiles joins
  • Versioned in git — metric changes review like model code

What we defined (dbt MetricFlow)

  • fct_review_semantic.yml + airline agg semantic model
  • 23 metrics — avg_rating, pct_recommended, airline avgs…
  • Dimensions: rating band · seat type · traveller · airline name · time
  • List → slice → validate against the warehouse — all from CLI

mf list metrics mf list dimensions --metrics avg_rating mf validate-configs

mf list metrics — 23 metrics found
mf list metrics — 23 governed metrics
mf list dimensions for avg_rating
mf list dimensions — 8 dims for avg_rating
mf validate-configs all checks pass
mf validate-configs — 0 errors vs warehouse

Pillar 4 · Semantic layer

Demo MetricFlow — query the metrics

After dbt parse, ask for metrics by name. MetricFlow compiles the SQL.

Review grain · fct_review_semantic.yml

# overall mf query --metrics review_count,avg_rating,pct_recommended # by rating band mf query --metrics avg_rating,review_count \ --group-by rating_band # by traveller type mf query --metrics pct_recommended,review_count \ --group-by type_of_traveller # over time mf query --metrics review_count,avg_rating \ --group-by metric_time__month \ --start-time 2024-01-01 \ --end-time 2025-12-31

Airline rollup · agg_airlines_review_semantic.yml

# average rating by airline mf query --metrics airline_avg_rating \ --group-by airline_entity__airline_name # top recommenders (at least 100 reviews) mf query --metrics airline_pct_recommended \ --group-by airline_entity__airline_name \ --where "{{ Metric('airline_review_count', group_by=['airline_entity']) }} >= 100" \ --order -airline_pct_recommended \ --limit 10

pct_recommended is a ratio metric — MetricFlow builds recommended / reviews for you.

Pillar 5 · DataOps

Branching, CI/CD, release

Release process

  • Feature branch → PR → slim CI → merge main → CD
  • CI: merge-base → SQLFluff → dbt clone → state:modified+
  • CD: OIDC → --defer --favor-state → docs to CloudFront
  • CD emails me on success or failure (Gmail SMTP, if: always())
  • Breaking type change → one-time prod --full-refresh
S3 dbt artifacts bucket
Prod manifest / run_results on S3
CloudFront dbt docs distribution
CloudFront dbt docs hosting
GitHub Actions slim CI tests 33 pass
Show — slim CI: 33 dbt tests PASS on changed + downstream models

Pillar 5 · DataOps

Terraform · OIDC · CI/CD — how the platform is run

Terraform (IaC)

Nothing important is click-ops.

  • EL repo: S3 landing, IAM, Snowflake RAW tables, stage, LOAD_AUDIT, PII tags/masking
  • Transform repo: warehouses, RBAC, SOURCE/INTERMEDIATE/MARTS/DEV_*, artifact bucket, CloudFront, OIDC provider + IAM role
  • terraform apply stays manual on purpose (infra blast radius)

OIDC auth

GitHub Actions never stores static AWS keys.

  • Terraform creates GitHub OIDC provider + deploy role
  • CD job: aws-actions/configure-aws-credentials → short-lived creds
  • Role can: read/write dbt artifacts S3, invalidate CloudFront
  • Snowflake still uses service users (DBT_CICD / PROD_DBT) via GitHub secrets

CI / CD release

Ship only what changed.

  • CI (PR): merge-base → SQLFluff → clone prod → build/test state:modified+
  • CD (main): download prod manifest → dbt build --defer --favor-state
  • Publish docs → S3 → CloudFront invalidate
  • Email alert: CD always notifies me — failure lands in inbox with run link
  • Airflow/cosmos schedules ongoing prod dbt; GHA ships code
GitHub Actions CI
CI — slim PR checks
S3 artifacts
Artifacts bucket (Terraform)
CloudFront
CloudFront docs (Terraform + OIDC invalidate)

Pillar 5 · Keyless AWS auth

How OIDC works

GitHub Actions gets short-lived AWS creds — no static access keys in secrets. Only AWS_ROLE_ARN is stored.

1. Terraform creates trust

terraform/aws/iam.tf — OIDC provider + IAM role scoped to this repo.

# OIDC provider
url = "https://token.actions.githubusercontent.com"
client_id_list = ["sts.amazonaws.com"]

# Role trust (AssumeRoleWithWebIdentity)
aud = "sts.amazonaws.com"
sub = "repo:MarkPhamm/skytrax_reviews_transformation:*"
# * covers main (CD) + pull/*/merge (CI)

2. Workflow assumes the role

deploy_main.yml / pr_checks.yml — one step, temporary creds (~15 min).

- uses: aws-actions/configure-aws-credentials@v4
  with:
    role-to-assume: ${{ secrets.AWS_ROLE_ARN }}
    aws-region: us-east-1

# Role can: S3 dbt artifacts R/W + CloudFront invalidate
# Snowflake still uses DBT_CICD / PROD_DBT secrets
1
GHA job
Requests OIDC JWT from GitHub (repo, ref, aud)
2
STS
configure-aws-credentials sends token + role ARN
3
Validate
AWS checks OIDC provider + trust policy (aud / sub)
4
Creds
Short-lived keys → S3 artifacts + CloudFront

Why: no long-lived cloud keys to rotate or leak. Scoped to one repo; expires with the run.

Team & ownership

What I own vs partner work

I built — EL

skytrax_reviews_extract_load

  • Airflow DAGs, scraper, S3 layout
  • Quality gates + LOAD_AUDIT
  • Terraform AWS + Snowflake RAW/masking

I built — Transform / Ops

skytrax_reviews_transformation

  • Star schema, incremental fact
  • Slim CI / defer CD / OIDC
  • RBAC, docs hosting, cosmos DAG

Partners — Insight

delta · frontier · spirit

  • Three Mode dashboards & analyses
  • I expose marts + document exposures
  • I can still defend SQL/metric grain

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

Every answer: Show · Why · What-if