I spent 12 Hours rebuilding my Junior year project: Part 2 - The Transformation Layer

A Guess post from Minh Pham. A weekend project to boost your data engineer career.

With only $7/month (billed annually), you can access all the materials you need to grow from junior → senior DE.

• 200+ deep-dive data engineering articles

• practice-spark: 65 LeetCode-style problems to practice Spark SQL/DataFrame

• learn-spark/dbt/airflow: CLI tools to master Spark/dbt/Airflow

• If you’re a student with an education email, use this 50% ANNUAL DISCOUNT

• If you’re a Vietnamese user, please DM me for an upgrade due to payment issues. As compensation for the inconvenience, you’ll get 50% OFF the annual plan.

---

Intro

This is a guest post from Minh Pham, the hardest-working and most enthusiastic data engineer I’ve ever known. Minh will share his learning on data engineering best practices via a multi-part hands-on project. This is the second part, which is about the transformation layer.

For the first part, you can read here.

If you enjoy it, please react to the article or share it so we know you like it. Plus, Minh is more motivated to write the next part. As you might know, writing an article is hard, but writing an article to guide a hands-on project is 10x harder, as you have to prepare for a lot of things.

—

This is the second part of my series in which I build end-to-end data pipelines, following best practices I learned at Insurify. In part 1, we built the ingestion pipeline - scraping airline reviews, staging them in S3, and loading them into Snowflake.

Now we pick up where that left off: transforming raw data into analytical models, setting up proper access controls for a small team, and building a CI/CD pipeline that rebuilds only what changed.

---

The Transformation Pipeline

Overall architecture details can be found here.

Tech stacks:

• dbt (dbt-snowflake): Transformation layer - Kimball star schema

• Snowflake: Data warehouse - all RBAC managed by Terraform

• Terraform: Infrastructure as Code - Snowflake + AWS resources

• GitHub Actions: CI/CD - slim CI on PRs, defer/favor-state CD on merge

• AWS S3: Artifact storage - manifests, run results, dbt docs

• AWS CloudFront: CDN for hosting dbt docs

• AWS IAM OIDC: Keyless authentication for GitHub Actions

• SQLFluff: SQL linting - lowercased keywords, trailing commas, explicit aliases

• Apache Airflow (Astronomer, cosmos): Orchestration

Data Model

I want to create a simple star schema model that has a fct_reviews table alongside multiple dimensions to support slicing and dicing. I also follow the Kimball process when creating this data model

• Define the business process: analyzing customer reviews

• Define the grain: each review a user submitted

• Define the dimensions: Aircraft, customer, airline, date, location

• Define the facts: review_id, is_verified, seat_type,…..

Prerequisite

I would expect you to have completed Part 1, so you have a Snowflake account with raw data loaded. You’ll also need:

• An AWS account (free tier is more than enough)

• The terraform-admin AWS CLI profile from Part 1

• Python 3.12+

• Git

• Docker (for local Airflow)

If you haven’t set up AWS or Terraform, follow the guides in Part 1, Steps 3-4.

Step 1: Clone the repo and set up your environment

The repo link is here: Clone it and set up a Python virtual environment:

git clone <https://github.com/MarkPhamm/skytrax_reviews_transformation.git>
cd skytrax_reviews_transformation

python -m venv dbt_venv
source dbt_venv/bin/activate
pip install -r requirements-dev.txt

requirements-dev.txt includes dbt-snowflake, sqlfluff, pandas, and other dev tools. The production CI only uses requirements.txt (dbt + sqlfluff).

---

With only $7/month (billed annually), you can access all the materials you need to grow from junior → senior DE.

• 200+ deep-dive data engineering articles

• practice-spark: 65 LeetCode-style problems to practice Spark SQL/DataFrame

• learn-spark/dbt/airflow: CLI tools to master Spark/dbt/Airflow

• If you’re a student with an education email, use this 50% ANNUAL DISCOUNT

• If you’re a Vietnamese user, please DM me for an upgrade due to payment issues. As compensation for the inconvenience, you’ll get 50% OFF the annual plan.

---

Step 2: Set up Snowflake Infrastructure with Terraform

Before we can run dbt, we need to create the Snowflake database, schemas, roles, users, and warehouses. Everything is defined in terraform/snowflake/main.tf. I actually find this easier than just running the create command in the UI. As more and more analysts enroll in the project, I would just need to change my Terraform file and run terraform apply.

What Terraform creates for us

The Snowflake Terraform code is split across 7 files, each handling one concern:

terraform/snowflake/
├── providers.tf     # How Terraform connects to Snowflake
├── variables.tf     # Input definitions (passwords, account info, defaults)
├── main.tf          # Shared locals (warehouse sizes, schema maps)
├── warehouses.tf    # Compute warehouses (XSMALL → XLARGE)
├── databases.tf     # Database + all schemas
├── roles.tf         # Roles + role hierarchy
├── grants.tf        # All privilege grants + ownership transfers
├── users.tf         # Users + role-to-user assignments
├── outputs.tf       # Values printed after apply (for CI/CD config)
└── terraform.tfvars # Actual secret values (never committed)

providers.tf - Snowflake connection

Tells Terraform how to authenticate with Snowflake. It uses ACCOUNTADMIN because it needs full power to create roles, users, and grants. All credentials come from variables (defined in variables.tf, values in terraform.tfvars) so nothing sensitive is hardcoded.

variables.tf - Input definitions

Declares every input Terraform needs: Snowflake org/account, admin credentials, user passwords, database name, and warehouse auto-suspend timeout. Passwords are marked sensitive = true so Terraform redacts them from all output. Variables with a default (like database_name = "SKYTRAX_REVIEWS_DB") are optional - the rest are required and must be provided in terraform.tfvars.

main.tf - Shared locals

Contains locals blocks - internal variables reused across other files. Three maps:

• warehouse_sizes - list of warehouse sizes to loop over (XSMALL through XLARGE)

• prod_schemas - the 5 production schemas that the transformer role needs access to

• dev_schemas - per-developer schemas (adding a new analyst here automatically grants them permissions via the loops in grants.tf)

warehouses.tf - Compute warehouses

Creates 5 warehouses of increasing size using a for_each loop over warehouse_sizes:

All warehouses have auto_suspend = 60 (shuts down after 60 seconds of inactivity) and auto_resume = true (wakes up when a query hits it). This keeps costs near zero when nobody is running queries.

databases.tf - Database and schemas

Creates the database SKYTRAX_REVIEWS_DB and 9 schemas inside it:

Why separate schemas for each environment? At Insurify, we follow the same pattern. Production models go to SOURCE/INTERMEDIATE/MARTS, CI runs write to STAGING (flat, gets wiped after each PR), and each developer gets their own DEV_* schema so they can run dbt locally without stepping on each other’s toes.

roles.tf - Roles and hierarchy

Creates three project-scoped roles and wires them into a hierarchy:

ACCOUNTADMIN
  └── SYSADMIN
        └── SKYTRAX_ADMIN (full control over project database)
              ├── SKYTRAX_TRANSFORMER (read/write on production schemas)
              └── SKYTRAX_ANALYST (read-only on MARTS + write on own dev schema)

The TRANSFORMER role is what dbt uses in CI/CD and production; it can create/replace tables and views across all production schemas. The ANALYST role is for humans - they can only read from MARTS and write to their own dev schema. Wiring SKYTRAX_ADMIN up to SYSADMIN follows Snowflake best practices, so all custom roles are accessible from the top.

grants.tf - Privileges and ownership

The largest file - handles all permission grants. It uses for_each loops over local.prod_schemas and local.dev_schemas, so adding a new schema to those maps in main.tf automatically propagates permissions. The grants break down into:

• Warehouse grants - TRANSFORMER gets USAGE + OPERATE, ANALYST gets USAGE only, ADMIN gets full control

• Database grants - TRANSFORMER gets USAGE + CREATE SCHEMA, ANALYST gets USAGE only, ADMIN gets all privileges

• Schema grants - TRANSFORMER gets USAGE + CREATE TABLE/VIEW on all production schemas; ANALYST gets USAGE on MARTS only + full read/write on dev schemas

• Future grants - Automatically apply permissions to any tables/views created in the future (critical for dbt, which creates new objects on every run)

• Ownership grants - Transfers ownership of all current and future tables/views in production schemas to TRANSFORMER. This is needed because dbt uses CREATE OR REPLACE, which requires ownership

users.tf - Users and role assignments

Creates 5 users and assigns each one a role:

Each user gets a default warehouse (XSMALL), default role, and default namespace (database.schema). The two service accounts (PROD_DBT and DBT_CICD) both use the TRANSFORMER role but are kept separate so you can audit who did what and revoke one without affecting the other. PROD_DBT is for the Airflow scheduler running daily production builds. DBT_CICD is for GitHub Actions - it runs in the STAGING schema during PRs and deploys to production schemas on merge.

outputs.tf - Post-apply values

Prints useful values after terraform apply - database name, warehouse names, usernames, role names, and schema names. These are handy for configuring dbt profiles, CI/CD pipelines, and BI tool connections.

Configure your variables

cp terraform/snowflake/terraform.tfvars.example terraform/snowflake/terraform.tfvars

Edit terraform/snowflake/terraform.tfvars:

snowflake_organization_name = "MYORG"
snowflake_account_name      = "MYACCOUNT"
snowflake_admin_user        = "your_username"
snowflake_admin_password    = "your_password"

prod_dbt_password        = "choose_a_password"
cicd_user_password       = "choose_a_password"
gina_analyst_password    = "choose_a_password"
vicient_analyst_password = "choose_a_password"

You can find your org and account from your Snowflake URL: https://MYORG-MYACCOUNT.snowflakecomputing.com.

Plan and Apply

cd terraform/snowflake
terraform init
terraform plan
terraform apply

Type yes when prompted

Verify in Snowflake

Log in to the Snowflake UI and verify:

• You should see SKYTRAX_REVIEWS_DB with all the schemas

• Under Admin → Users & Roles, you should see the 4 new users and 3 new roles

• Under Admin → Warehouses, you should see the 5 compute warehouses

---

Step 3: Set up AWS Infrastructure with Terraform

The AWS module creates resources for CI/CD artifact storage and dbt docs hosting. Everything lives in terraform/aws/.

What Terraform creates for us

The AWS Terraform code is split across 7 files, each handling one concern:

terraform/aws/
├── providers.tf     # How Terraform connects to AWS
├── variables.tf     # Input definitions (region, project name, bucket name, GitHub repo)
├── main.tf          # Shared data sources (AWS account ID)
├── s3.tf            # S3 bucket for dbt artifacts
├── cloudfront.tf    # CloudFront CDN for dbt docs hosting
├── iam.tf           # GitHub Actions OIDC provider + IAM role
├── outputs.tf       # Values printed after apply (for GitHub Secrets)
└── terraform.tfvars # Actual values (never committed)

Let me walk through what each file creates.

providers.tf - AWS connection

Tells Terraform how to authenticate with AWS. It uses the terraform-admin CLI profile (set up in Part 1) and tags every resource with Project, Environment, and ManagedBy so you can track costs and ownership.

variables.tf - Input definitions

Declares every input Terraform needs: AWS region (defaults to us-east-1), environment name, project name prefix, S3 bucket name, and the GitHub repository (in owner/repo format) for OIDC scoping. Only artifacts_bucket_name and github_repository are required - the rest have sensible defaults.

main.tf - Shared data sources

A small file that looks up the current AWS account ID via data "aws_caller_identity". This avoids hardcoding your account ID anywhere - other files reference it as data.aws_caller_identity.current.account_id.

s3.tf - S3 bucket for dbt artifacts

Creates the S3 bucket that stores three types of artifacts: S3 Bucket (skytrax-reviews-dbt-artifacts-<account_id>) - Stores three types of artifacts:

s3://skytrax-reviews-dbt-artifacts-<account_id>/
├── manifests/manifest.json       # Production state for defer/favor-state
├── run_results/run_results.json  # Last deploy results
└── docs/                         # dbt docs site (HTML + JSON)
    ├── index.html
    ├── catalog.json
    └── manifest.json

cloudfront.tf - CDN for dbt docs

Serves dbt docs globally via CloudFront CDN. Contains three resources:

• Origin Access Control (OAC) - allows CloudFront to read from S3 without making the bucket public. CloudFront signs every request to S3 using SigV4, so the bucket stays private.

• CloudFront Distribution - the CDN itself. Points to the docs/ prefix in the S3 bucket, serves index.html as the default root object, caches for 5 minutes (default_ttl = 300), and redirects HTTP to HTTPS.

• S3 Bucket Policy - two policy statements: one allows CloudFront to read any object via OAC, and one allows public read on the manifests/* prefix (so developers can curl the production manifest for local defer builds).

Why CloudFront instead of EC2? I actually built the EC2 + nginx approach first (the code is still in disabled/ec2.tf.disabled and disabled/vpc.tf.disabled).

It worked, but it cost ~$8/month, required OS patching, needed a cron job to sync from S3, and required a whole VPC setup (subnet, internet gateway, route table, security group).

CloudFront is $0 on the free tier, fully managed, instant updates, and needs only 3 Terraform resources. The EC2 approach was more educational, but for a static site like dbt docs, CloudFront is the right tool.

iam.tf - GitHub Actions OIDC and IAM role

This is the key security piece. Instead of storing long-lived AWS access keys in GitHub Secrets (which can leak and need rotation), we use OpenID Connect (OIDC) so GitHub Actions can assume an IAM role directly. The file creates three resources:

• OIDC Identity Provider - registers GitHub’s OIDC issuer (token.actions.githubusercontent.com) with AWS. The client_id_list is set to sts.amazonaws.com (the audience claim), and the thumbprint_list is GitHub’s stable TLS certificate fingerprint. This is a one-time setup per AWS account.

• IAM Role (skytrax-reviews-github-actions-role) - the role GitHub Actions assumes. Its trust policy uses a StringLike condition on the sub claim scoped to repo:<owner>/<repo>:*, so only workflows from your specific repo can assume it. No other repo can use this role.

• IAM Role Policy - grants three permissions: s3:GetObject/PutObject/DeleteObject on the artifacts bucket (for uploading manifests, run results, and docs), s3:ListBucket (for sync operations), and cloudfront:CreateInvalidation (for busting the docs cache after deploy)

GitHub Actions Runner
  │
  ├─ 1. Request OIDC token from GitHub's token endpoint
  │     (includes repo, branch, and event in "sub" claim)
  │
  ├─ 2. Call aws-actions/configure-aws-credentials
  │     (passes OIDC token + role ARN to AWS STS)
  │
  ├─ 3. AWS STS validates token against registered OIDC provider
  │     - Checks audience = "sts.amazonaws.com"
  │     - Checks subject matches "repo:MarkPhamm/skytrax_reviews_transformation:*"
  │
  └─ 4. STS returns temporary credentials (15 min default)
        (workflow can now call S3 and CloudFront APIs)

outputs.tf - Post-apply values

Prints the values you’ll need for GitHub Secrets configuration: the S3 bucket name, CloudFront distribution ID and domain name, the GitHub Actions IAM role ARN, and the OIDC provider ARN.

Configure your variables

cp terraform/aws/terraform.tfvars.example terraform/aws/terraform.tfvars

Edit terraform/aws/terraform.tfvars:

artifacts_bucket_name = "skytrax-reviews-dbt-artifacts"
github_repository     = "MarkPhamm/skytrax_reviews_transformation"

The bucket name will automatically be suffixed with your AWS account ID.

Plan and Apply

cd terraform/aws
terraform init
terraform plan
terraform apply

After it finishes, Terraform outputs the values you'll need for GitHub Secrets:

Outputs:

github_actions_role_arn       = "arn:aws:iam::XXXXXXXXXXXX:role/skytrax-reviews-github-actions-role"
artifacts_bucket_name         = "skytrax-reviews-dbt-artifacts-XXXXXXXXXXXX"
cloudfront_distribution_id    = "XXXXXXXXXXXX"
cloudfront_domain_name        = "XXXXXXXXXXXX"

Save these - we'll need them in Step 5.

---

Step 4: Run dbt locally

Now the Snowflake infrastructure is ready. Let’s connect dbt to it and run our models.

Set environment variables

Set these based on your Snowflake user. Each developer gets their own dev schema:

export SNOWFLAKE_ACCOUNT=your_snowflake_account
export SNOWFLAKE_USER=your_user
export SNOWFLAKE_PASSWORD=your_password
export SNOWFLAKE_ROLE=SKYTRAX_ANALYST
export SNOWFLAKE_SCHEMA=DEV_your_name  # e.g., DEV_MARK

Add these to your ~/.zshrc or a .envrc so you don’t have to set them every session.

Verify connection and run models

cd dbt
dbt deps --profiles-dir ./     # install dbt packages (dbt_utils, dbt_expectations)
dbt debug --profiles-dir ./     # verify Snowflake connection
dbt run --profiles-dir ./       # run all models (writes to your dev schema)
dbt test --profiles-dir ./      # run data quality tests

The profiles.yml lives inside the dbt/ directory and uses environment variables for all credentials - no hardcoded secrets.

Understanding the data model

The transformation follows a classic staging → intermediate → marts pattern:

Staging (stg__skytrax_reviews) - A 1:1 view on top of the raw source table. Adds a review_id via row_number(). Minimal transformation, just mirrors the source.

Intermediate (int_reviews_cleaned) - This is where the business logic lives. Null handling with coalesce(..., 'unknown'), column renaming (verify → is_verified, aircraft → aircraft_model, review → review_text), and type standardization.

Marts - The star schema following Kimball methodology:

All dimensions use dbt_utils.generate_surrogate_key for deterministic surrogate keys. The fact table joins to all 5 dimensions - with role-playing dimensions for dates (submitted vs. flown) and locations (origin, destination, transit). It also calculates average_rating across all non-null rating columns and a rating_band (bad/medium/good).

Local defer builds (against production)

Once there's a production manifest in S3 (of which there’s not yet), you can run only your changed models locally while referencing production for everything else:

mkdir -p dbt/prod_state
curl -o dbt/prod_state/manifest.json \\
  <https://skytrax-reviews-dbt-artifacts-203110101827.s3.amazonaws.com/manifests/manifest.json>

cd dbt
dbt run \\
  --select state:modified+ \\
  --defer \\
  --favor-state \\
  --state prod_state \\
  --profiles-dir ./

This is the same pattern the CD pipeline uses - only rebuild what you changed, reference production for everything else. The manifest is publicly readable from S3 (the bucket policy allows s3:GetObject on the manifests/* prefix).

However, at this time, since we haven’t even set up the CI/CD pipelines just yet, there would be no artifacts, nor would an S3 bucket exist. In this case, you can just cd into the dbt dir and run dbt_run, which will run all your models locally and output to your dev_schema.

---

Step 5: Set up CI/CD with GitHub Actions

This is where everything comes together. We have two workflows:

• deploy_main.yml - Continuous Deployment on merge to main

• pr_checks.yml - Continuous Integration on pull requests

Configure GitHub Secrets

Go to your GitHub repo → Settings → Secrets and variables → Actions. Add these secrets:

dbt-ci-init/action.yml - reusable in both CI and CD

action.yml is a shared function that both workflows call to avoid repeating the same setup 7+ times. Every job in pr_checks.ymland deploy_main.yml runs on a fresh VM — nothing is pre-installed. So every job needs to:

1. Install Python

2. Create a venv and pip install dependencies

3. Run dbt deps

4. (Optionally) download artifacts from a previous job

Instead of copy-pasting those 15+ lines into every single job, you write it once in action.yml and call it with one line:

name: ci-init
uses: ./.github/actions/dbt-ci-init

# =============================================================================
# dbt CI Init -- Reusable Composite Action
# =============================================================================
# This is a shared "function" that every job calls to avoid repeating
# Python/venv/dbt-deps setup 7+ times across the workflow.
# Callers invoke it with:  uses: ./.github/actions/dbt-ci-init
# =============================================================================

name: "dbt CI Init"
description: "Checkout, Python venv, dbt deps, and optional artifact downloads."

# ---------------------------------------------------------------------------
# Inputs (parameters callers can pass in -- all optional with defaults)
# ---------------------------------------------------------------------------
inputs:
  python_version:
    description: "Python version for the venv"
    default: "3.12"
    required: false
  download_base_manifest:
    description: "Download base_manifest artifact to ./base_state"
    default: "false"
    required: false
  download_changed_models:
    description: "Download changed_models artifact"
    default: "false"
    required: false
  download_prod_manifest:
    description: "Download prod_manifest artifact to ./prod_state (for dbt clone)"
    default: "false"
    required: false

# ---------------------------------------------------------------------------
# Steps -- runs directly in the caller's job (composite, not Docker)
# ---------------------------------------------------------------------------
runs:
  using: "composite"
  steps:
    # Install Python (needed because dbt is a Python tool).
    - name: Setup Python
      uses: actions/setup-python@v5
      with:
        python-version: ${{ inputs.python_version }}

    # Create a virtual environment and install all Python packages from requirements.txt
    # (dbt-snowflake, sqlfluff, etc.). shell: bash is required in composite actions.
    - name: Create venv and install dependencies
      shell: bash
      run: |
        python -m venv dbt_venv
        source dbt_venv/bin/activate
        pip install --upgrade pip
        pip install -r requirements.txt

    # Install dbt packages declared in packages.yml (e.g. dbt_utils).
    # --profiles-dir ./ tells dbt to find profiles.yml in the dbt/ directory, not ~/.dbt/.
    - name: dbt deps
      shell: bash
      working-directory: ./dbt
      run: |
        source ../dbt_venv/bin/activate
        dbt deps --profiles-dir ./

    # --- Conditional artifact downloads ---
    # Artifacts are files uploaded by Job 1 (setup_env) and downloaded by later jobs.
    # Each job runs on a fresh VM, so artifacts are how jobs share data.

    # Download the list of changed model names (text file from Job 1).
    - name: Download changed_models artifact
      if: inputs.download_changed_models == 'true'
      uses: actions/download-artifact@v4
      with:
        name: changed_models

    # Download the merge-base manifest for state:modified comparison.
    - name: Download base_manifest artifact
      if: inputs.download_base_manifest == 'true'
      uses: actions/download-artifact@v4
      with:
        name: base_manifest
        path: dbt/base_state

    # Download the production manifest so dbt clone knows where prod tables live.
    - name: Download prod_manifest artifact
      if: inputs.download_prod_manifest == 'true'
      uses: actions/download-artifact@v4
      with:
        name: prod_manifest
        path: dbt/prod_state

CI

Link to the CI pipeline here.

When you open a PR against main, pr_checks.yml runs. This is slim CI - it only lints, compiles, runs, and tests the models you actually changed. This is intentional, cause when dbt model scales with a long-running model, the CI process can significantly lower development time. The CI process will have its dedicated dbt-project.yml

The CI pipeline has 7 sequential jobs:

• Setup & Detect Changes: It computes the merge-base SHA between your PR branch and main, checks out the base code, runs dbt parse on both, and uses dbt ls --state base_state --select state:modified state:new to find which models changed. The merge-base approach is important - it compares your PR against the point where your branch diverged from main, not against the latest main. This avoids false positives from other PRs that merged while you were working.

  • It also detects macro changes - if a macro file changed but no models were detected as modified, it selects all models (since macro changes can affect any dependent model).

• Lint SQL - Runs sqlfluff lint on only the changed .sql files. Uses git diff --name-only against the merge-base to find them. Our linting rules (configured in setup.cfg) enforce lowercased SQL, trailing commas, explicit column aliases, and shorthand casting (:: instead of CAST()).

• Compile Changed Models - Runs dbt compile on only the changed models. This catches Jinja errors, missing refs, and syntax issues without hitting Snowflake.

• Run Changed Models - Actually executes the changed models against Snowflake in the STAGING schema. Before running, it uses dbt clone to create zero-copy clones of production tables in the staging schema, so that unchanged upstream models remain available without rebuilding.

• Test Changed Models - Runs dbt tests on the changed models in the STAGING schema.

• Run Downstream Models - Runs all models downstream of the changed ones (using the model_name+ selector), excluding the changed models themselves (already run in Job 4).

• Test Downstream Models - Tests all downstream models to make sure your changes didn't break anything further down the DAG.

CD

Link to the pipeline here.

When you merge a PR to main, the CD pipeline kicks off. Here’s the flow:

• Check out code + configure AWS via OIDC and install dbt, run dbt debug to verify Snowflake connection

• Download production manifest from S3 (if exists)

• Run

  dbt build --select state:modified+ --defer --favor-state --state prod_state

• dbt docs are generated → uploaded to S3 → invalidate CloudFront cache

• Upload manifest + run_results to S3 for next deploy

• Send email notification (success or failure) - for monitoring CD process

The key insight is incremental deploys. Instead of rebuilding all models on every merge, we only rebuild what changed using state:modified+ and their downstream dependencies. --defer means unchanged models reference existing production tables. --favor-state means when resolving deferred refs, prefer the production state. This saves significant Snowflake compute credits.

If this is the very first deploy (no prior manifest in S3), it falls back to a full dbt run + dbt test.

The workflow also has concurrency control - only one deploy runs at a time. If a second push happens while a deploy is running, it queues instead of cancelling. This prevents race conditions on the production manifest.

How the manifest flow works

This is the part that confused me before building it. Here's the full lifecycle:

First deploy (no manifest):
  → dbt run (full build) → dbt test
  → Upload manifest.json to S3

Second deploy onward:
  → Download manifest.json from S3
  → dbt build --select state:modified+ --defer --favor-state --state prod_state
  → Upload new manifest.json to S3

CI on PR:
  → Build merge-base manifest locally (dbt parse on main's code)
  → dbt ls --state base_state --select state:modified
  → Only lint/compile/run/test changed models

Each deploy uploads its manifest, so the next deploy can diff against it. The CI pipeline doesn't touch S3 at all - it builds its own baseline manifest from the merge-base code. This means CI is completely independent of production deploys.

---

Step 6: dbt Docs Hosting

dbt docs are auto-generated and hosted on CloudFront. Every merge to main triggers:

1. dbt docs generate - produces index.html, catalog.json, manifest.json

2. aws s3 sync - uploads to s3://bucket/docs/

3. aws cloudfront create-invalidation - busts the cache

In my case, the docs are at: https://d38l3fc9bckvbz.cloudfront.net

The bucket is private - CloudFront accesses it via Origin Access Control (OAC). This means nobody can bypass CloudFront and hit S3 directly (except for the manifests/* prefix, which is public so developers can curl the production manifest for local defer builds).

---

Step 7: Set up Local Airflow (Optional)

If you want to schedule dbt runs with Airflow, the dbt-dags/ directory contains an Astronomer project using the cosmos provider.

cd dbt-dags
astro dev start

The Airflow UI is at http://localhost:8083. The skytrax_dbt_transformation DAG runs all dbt models as the PROD_DBT user via the cosmos provider - it automatically converts your dbt project into Airflow tasks.

You’ll need to set up a Snowflake connection in dbt-dags/.env via AIRFLOW_CONN_SNOWFLAKE_DEFAULT (same format as Part 1’s Airflow setup).

---

Outro

That’s it! You now have a fully working transformation pipeline:

• Star schema with 5 dimensions and 1 fact table, following Kimball methodology

• Slim CI that only lints, compiles, runs, and tests changed models on PRs

• Incremental CD that only rebuilds modified models and their downstream dependencies

• Keyless auth via OIDC - no static AWS credentials anywhere

• Full IaC - every Snowflake and AWS resource managed by Terraform

• Per-user dev schemas for safe local development

• Auto-hosted dbt docs on CloudFront, updated on every deploy

If you followed along from Part 1, you’ve built a complete data pipeline - from scraping raw data off the web, through S3 staging and Snowflake loading, all the way to a production-grade star schema with proper CI/CD and infrastructure as code. Everything is code, everything is reproducible, everything is version-controlled.

The repo is here: https://github.com/MarkPhamm/skytrax_reviews_transformation. Feel free to clone it, break it, and make it your own.