Skytrax Reviews Analytics Platform

End-to-end airline review analytics: scrape AirlineQuality.com → stage on S3 → load Snowflake → dbt star schema (medallion) → Mode dashboards.

Source status: AirlineQuality.com (Skytrax) is permanently closed. This platform was built from historical scrapes collected while the site was live; the EL path is a replayable S3/Snowflake archive, not an ongoing feed from that domain.

This repo is the umbrella — project narrative, architecture, and links into the part repos. Implementation lives in the extract-load, transformation, and dashboard repositories below.

_{The platform in 21 seconds — scale, governance, verdict. Silent by format; the full walkthrough is the deck linked below.}

Platform walkthrough — Show → Why → What-if deck (index.html) covering modeling, transformation, governance, insight, and DataOps.

Demo runbook — live commands per pillar (including break-it-live branches demo/contract-break and demo/bad-data on the transformation repo).

Self-selection bias: Skytrax reviews are self-reported. Passengers with extreme experiences are more likely to post, so KPIs are directional, not population-level.

Live dbt docs: https://d38l3fc9bckvbz.cloudfront.net

Same governed logic in dbt (average_rating, rating_band, recommended) on MARTS.AGG_AIRLINES_REVIEW (Mode grain: unweighted avg across airlines) — industry bar + three Mode slices.

KPI snapshot (2026-07-30): deck / Mode baseline numbers below. Re-verify live with the SQL in docs/demo-runbook.md before the interview if the warehouse has moved.

Volume note: Part 1 lands ~160k+ rows across four review types (airline / seat / lounge / airport). The ~117k figure is the airline-review grain in marts / Mode industry bar — not the full scrape.

Source                 Extract              Lake                 Load                 Warehouse + Transform              Consumers
────────               ───────              ────                 ────                 ────────────────────              ─────────
AirlineQuality.com  →  Python scraper  →   S3 raw/<type>/   →  COPY INTO        →   Snowflake RAW                   →  Mode (Delta · Frontier · Spirit)
  (site closed;          + cleaner            processed/<type>/   + LOAD_AUDIT         SOURCE → INTERMEDIATE → MARTS      dbt Docs (CloudFront)
   historical HTML)      (Airflow tasks)      quality gate                             dbt: stg → int → dims + fct        Analyst DEV_* 

Orchestration (spans extract → load → transform)
  Airflow (Astronomer) · Dataset-chained crawl → process → snowflake · cosmos DbtDag

Control plane (provisions + ships)
  Terraform (AWS + Snowflake) · GitHub Actions slim CI / defer-favor-state CD · OIDC (keyless GHA → AWS)

Repo: skytrax_reviews_extract_load

Three Airflow DAGs chained via Datasets (no cron guesswork between stages):

S3 layout

s3://skytrax-reviews-landing-<account-id>/
  raw/<type>/YYYY/MM/raw_data_YYYYMMDD.csv
  processed/<type>/YYYY/MM/clean_data_YYYYMMDD.csv

<type> ∈ airlines | seats | lounges | airports

• Versioning, AES256, lifecycle (IA after 30d), public access blocked
• Idempotent daily files + Snowflake file-level COPY INTO dedupe
• PII: tag-based masking on CUSTOMER_NAME / NATIONALITY (Terraform)
• All landing + RAW objects managed with Terraform

Repo: skytrax_reviews_transformation

Grain: one row per review_id (one customer review submission).

• CI (PR): merge-base state → SQLFluff → dbt clone → build/test state:modified+ / state:new+
• CD (main): OIDC → download prod manifest → dbt build --select state:modified+ --defer --favor-state → upload docs/manifest to S3 → CloudFront invalidate
• IaC: Snowflake RBAC/warehouses/schemas + AWS artifacts bucket, CloudFront, OIDC provider — all Terraform

Same mart (AGG_AIRLINES_REVIEW / FCT_REVIEW), three Mode dashboards — each with one distinctive insight and one action. KPIs match the Mode industry baseline above (avg rating 2.59, recommend 40%).

Repo: airline_customer_exp_analysis (Mode)

Repo: frontier-reviews-dashboard (Mode)

Repo: spirit_airlines_dashboard (Mode)

• Mentor / Stakeholder: Nhan Tran
• Team Lead / Analytics Engineer: Mark Pham

• Data Analysts: Trang Dam, Gwennie Nguyen, Jenny Tran, Mia Tran, Alyssa Le
• Data Engineers: Leonard Dau, Thieu Nguyen, Viet Lam Nguyen
• Software Engineers: Tien Nguyen, Anh Duc Le
• Data Scientists: Robin Tran, Trung Dam
• Scrum Master: Hien Dinh

1. Expand sources — on-time performance / DOT complaints alongside reviews
2. Conformed facts for seat / lounge / airport review types (already in RAW)
3. Allegiant Mode slice (complete the ULCC peer set already used in Frontier’s benchmark)

Done (no longer “next”): MetricFlow semantic layer on avg_rating / pct_recommended (and airline-prefixed agg metrics) — see Insight / MetricFlow slides and dbt/models/marts/*_semantic.yml.

Skytrax Global Airlines Analytics Project