Education › Data Engineering › Guided project

Data platform with IaC and CI

The lakehouse project built pipelines by hand; this one builds the platform they run on, the way a data-platform team does: the storage layers and their access controls are Terraform, so the whole environment is reproducible and there is a dev and a prod that differ only in a variable; the dbt models are version-controlled and tested on every pull request, with a CI job that runs only the models a change actually touched; a prod deploy runs on merge; and a cost-and-runtime dashboard is built from dbt's own run artifacts so you can see which models are getting slow before the bill does. The entire stack runs free on your laptop against LocalStack, and every command was executed against it — with a clearly marked path to real AWS.

Intermediate about 6 hours 6 phases · 27 steps 0 / 27 done
Overview 1. Stand up storage the platform runs on 2. Two environments from one configuration 3. The warehouse: dbt on the platform 4. CI that runs only what changed 5. Deploy on merge 6. Watch the platform's own cost and runtime Troubleshooting
What you will have at the end

A data-platform repository where terraform apply stands up four S3 layer buckets with lifecycle rules and a locked-down access policy, dev and prod are Terraform workspaces from one config, a dbt project reads and writes those buckets through DuckDB, a CI workflow runs dbt build on pull requests using slim CI (state-based selection) so only modified models and their children run, a merge to main deploys to the prod workspace, and a dbt_metrics.py turns each run's run_results.json into a table and a dashboard of per-model runtime over time — plus a COST.md mapping every local resource to its real-AWS equivalent and price.

Before you start
  • The lakehouse pipeline project (dbt, DuckDB, the medallion layers) — this platformises it
  • The data track's modules on infrastructure-as-code for data, CI/CD for pipelines, environments, and cost
  • The DevOps track's Terraform module, and ideally the Terraform an AWS environment project — the IaC patterns are the same
  • Docker, Terraform 1.6+, Python 3.12, and the GitHub CLI
Tools you will install
  • Terraform — defines the storage layers, lifecycle rules and access policy; workspaces give dev/prod from one config ↗
  • LocalStack — runs the AWS S3 API on your laptop for free, so the same Terraform and dbt code that targets AWS runs locally ↗
  • dbt-duckdb — the transformation layer; DuckDB reads and writes Parquet in S3 through httpfs ↗
  • DuckDB — the query engine the models run on, against data in the S3 buckets ↗
  • GitHub Actions — dbt build on pull requests (slim CI), prod deploy on merge, Terraform plan/apply ↗
  • Streamlit — the platform's own dashboard: model runtimes and test results over time ↗
Repository layout at the end
data-platform/
├── infra/
│   ├── main.tf              # provider (LocalStack or AWS), layer buckets
│   ├── buckets.tf           # lifecycle rules, versioning, public-access block
│   ├── policy.tf            # least-privilege bucket policy per environment
│   ├── variables.tf         # env, endpoint, region
│   └── outputs.tf
├── warehouse/               # the dbt project
│   ├── dbt_project.yml
│   ├── profiles.yml         # duckdb + httpfs → S3, target per environment
│   ├── models/
│   │   ├── staging/
│   │   └── marts/
│   └── seeds/
├── platform/
│   ├── dbt_metrics.py       # run_results.json → history table
│   └── dashboard.py         # runtimes and test outcomes over time
├── .github/workflows/
│   ├── ci.yml               # PR: fmt, validate, dbt build (slim)
│   └── deploy.yml           # main: terraform apply prod + dbt build prod
├── Makefile
├── COST.md                  # every resource → real-AWS equivalent and price
└── docker-compose.yml       # localstack

Tick each step as you finish it — your progress is saved in this browser only (back up or restore on the hub). Every code block has a copy button. If something goes wrong, the troubleshooting section at the end covers the usual suspects.

Phase 1

Stand up storage the platform runs on

LocalStack running, and Terraform that creates the four medallion layer buckets with versioning, a lifecycle rule and public access blocked — the storage a data platform is built on.

  1. Create the project and start LocalStack. It serves the real AWS S3 API on localhost:4566, so the Terraform and dbt you write here is the same code you would point at AWS — only the endpoint changes.
    yaml
    # docker-compose.yml
services:
  localstack:
    image: localstack/localstack:4.0
    ports: ["4566:4566"]
    environment:
      SERVICES: s3
      DEBUG: "0"
    volumes:
      - "./.localstack:/var/lib/localstack"
  2. Bring it up and confirm S3 is available.
    bash
    mkdir data-platform && cd data-platform && git init -b main
# save docker-compose.yml, then:
docker compose up -d
for i in $(seq 1 30); do curl -s localhost:4566/_localstack/health | grep -q '"s3": "available"' && break; sleep 1; done
curl -s localhost:4566/_localstack/health | python3 -c "import sys,json; print('s3:', json.load(sys.stdin)['services']['s3'])"
  3. Write the Terraform provider block. The LocalStack-specific settings (dummy credentials, path-style addressing, the S3 endpoint) are isolated behind variables, so switching to real AWS is a matter of leaving them unset. (Verified against Terraform 1.13 + LocalStack 4.0.)
    hcl
    # infra/variables.tf
variable "env" {
  type = string
}

variable "s3_endpoint" {
  description = "S3 endpoint; empty string means real AWS"
  type        = string
  default     = "http://localhost:4566"
}

variable "region" {
  type    = string
  default = "us-east-1"
}

# infra/main.tf
terraform {
  required_version = ">= 1.6"
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.80"
    }
  }
}

locals {
  local_mode = var.s3_endpoint != ""
  layers     = ["raw", "bronze", "silver", "gold"]
}

provider "aws" {
  region = var.region

  # These blocks are no-ops against real AWS (credentials come from the environment there).
  access_key                  = local.local_mode ? "test" : null
  secret_key                  = local.local_mode ? "test" : null
  skip_credentials_validation = local.local_mode
  skip_requesting_account_id  = local.local_mode
  skip_metadata_api_check     = local.local_mode
  s3_use_path_style           = local.local_mode

  dynamic "endpoints" {
    for_each = local.local_mode ? [1] : []
    content {
      s3 = var.s3_endpoint
    }
  }

  default_tags {
    tags = {
      project     = "data-platform"
      environment = var.env
      managed_by  = "terraform"
    }
  }
}
  4. Define the buckets — one per medallion layer — with versioning, public access blocked, and a lifecycle rule that expires old raw data. The bucket name carries the environment, so dev and prod never collide.
    hcl
    # infra/buckets.tf
resource "aws_s3_bucket" "layer" {
  for_each = toset(local.layers)
  bucket   = "lake-${var.env}-${each.key}"
}

resource "aws_s3_bucket_versioning" "layer" {
  for_each = aws_s3_bucket.layer
  bucket   = each.value.id
  versioning_configuration {
    status = "Enabled"
  }
}

resource "aws_s3_bucket_public_access_block" "layer" {
  for_each                = aws_s3_bucket.layer
  bucket                  = each.value.id
  block_public_acls       = true
  block_public_policy      = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

resource "aws_s3_bucket_lifecycle_configuration" "raw" {
  bucket = aws_s3_bucket.layer["raw"].id
  rule {
    id     = "expire-raw"
    status = "Enabled"
    filter {}
    expiration {
      days = var.env == "prod" ? 90 : 7
    }
  }
}

# infra/outputs.tf
output "buckets" {
  value = { for k, b in aws_s3_bucket.layer : k => b.bucket }
}
    The lifecycle rule keeps raw data 90 days in prod but only 7 in dev — the same code, a different value per environment, which is exactly what environments are for. In dev you do not pay to keep months of test data.
  5. Initialise, create the dev workspace, and apply. Terraform workspaces give you isolated state for each environment from one configuration — dev and prod state never mix.
    bash
    cd infra
terraform init
terraform workspace new dev
terraform apply -auto-approve -var env=dev
terraform output buckets
AWS_ACCESS_KEY_ID=test AWS_SECRET_ACCESS_KEY=test aws --endpoint-url=http://localhost:4566 s3 ls
    You should see lake-dev-raw, -bronze, -silver, -gold. The four buckets are your medallion layers; nothing about them was created by hand, which is the point — a teammate runs the same three commands and gets the same platform.
Phase 2

Two environments from one configuration

A prod workspace standing up its own isolated buckets from the same code, and a policy resource that differs by environment — proving the environment split is real, not cosmetic.

  1. Add an access policy that is stricter in prod. A bucket policy is where you would, on real AWS, grant only the pipeline's role read/write and deny everything else. Here it denies unencrypted uploads — a control you want in prod and can relax in dev.
    hcl
    # infra/policy.tf
data "aws_iam_policy_document" "deny_unencrypted" {
  for_each = var.env == "prod" ? aws_s3_bucket.layer : {}
  statement {
    sid       = "DenyUnEncryptedPuts"
    effect    = "Deny"
    actions   = ["s3:PutObject"]
    resources = ["${each.value.arn}/*"]
    principals {
      type        = "*"
      identifiers = ["*"]
    }
    condition {
      test     = "StringNotEquals"
      variable = "s3:x-amz-server-side-encryption"
      values   = ["AES256"]
    }
  }
}

resource "aws_s3_bucket_policy" "deny_unencrypted" {
  for_each = data.aws_iam_policy_document.deny_unencrypted
  bucket   = aws_s3_bucket.layer[each.key].id
  policy   = each.value.json
}
    for_each = var.env == "prod" ? ... : {} means the resource exists only in prod. This is how you express "prod is hardened, dev is convenient" without a second copy of the code or a fork that drifts.
  2. Create the prod workspace and apply. Watch it build a separate set of buckets and add the policy that dev does not have.
    bash
    terraform workspace new prod
terraform apply -auto-approve -var env=prod
terraform output buckets
AWS_ACCESS_KEY_ID=test AWS_SECRET_ACCESS_KEY=test aws --endpoint-url=http://localhost:4566 s3 ls | grep lake-
terraform workspace list
    Eight buckets now: four lake-dev-* and four lake-prod-*, in two separate state files. terraform workspace select dev and plan shows dev is unchanged — the environments are genuinely independent.
  3. Set up remote-ish state and a plan-before-apply habit. LocalStack does not need a real backend, but you should still never apply without reading a plan. Add a wrapper and switch back to dev for the dbt work.
    bash
    cat > plan.sh <<'EOF'
#!/usr/bin/env bash
set -euo pipefail
ENV="${1:?usage: plan.sh dev|prod}"
terraform workspace select "$ENV"
terraform plan -var env="$ENV" -out="$ENV.tfplan"
EOF
chmod +x plan.sh
./plan.sh dev    # 'No changes' — the platform matches the code
cd ..
    On real AWS the state lives in an S3 backend with lockfile locking (the DevOps aws-platform project shows the exact config). Here the local state file is fine because it is a learning environment; the discipline of plan-then-apply is the transferable part.
Phase 3

The warehouse: dbt on the platform

A dbt project that reads seed data into the bronze bucket and builds tested marts, with its target driven by the same environment variable Terraform uses — so dbt writes to the dev buckets in dev and prod buckets in prod.

  1. Set up the Python environment and the dbt project.
    bash
    uv venv --python 3.12 && source .venv/bin/activate
uv pip install 'dbt-duckdb>=1.9,<2' 'duckdb>=1.1,<2' 'streamlit>=1.40,<2' 'pandas>=2.2,<3'
mkdir -p warehouse/models/staging warehouse/models/marts warehouse/seeds platform
cd warehouse
  2. Write the dbt profile. DuckDB connects to the S3 layer buckets through httpfs; the target name and the bucket prefix both come from the PLATFORM_ENV variable, so nothing is hard-coded to one environment. (Verified against dbt-duckdb 1.12.)
    yaml
    # warehouse/profiles.yml
platform:
  target: "{{ env_var('PLATFORM_ENV', 'dev') }}"
  outputs:
    dev: &s3
      type: duckdb
      path: "target/dev.duckdb"
      extensions: [httpfs]
      settings:
        s3_endpoint: "{{ env_var('S3_ENDPOINT', 'localhost:4566') }}"
        s3_url_style: "path"
        s3_use_ssl: false
        s3_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID', 'test') }}"
        s3_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY', 'test') }}"
        s3_region: "us-east-1"
    prod:
      <<: *s3
      path: "target/prod.duckdb"
    The &s3 anchor and <<: *s3 merge keep dev and prod identical except the DuckDB file path; the bucket they read is chosen by the model SQL using the env, next. On real AWS you drop the endpoint and ssl settings and DuckDB uses the default AWS endpoints with real credentials.
  3. Write the project file and a variable that turns PLATFORM_ENV into the bucket prefix, so a model can reference lake-{{ env }}-bronze without knowing which environment it is in.
    yaml
    # warehouse/dbt_project.yml
name: platform
version: "1.0"
profile: platform
vars:
  lake_env: "{{ env_var('PLATFORM_ENV', 'dev') }}"
models:
  platform:
    staging:
      +materialized: view
    marts:
      +materialized: table
  4. Seed some raw data into the bronze bucket, so the models have something to read. In a real platform an ingestion job writes here; a dbt seed stands in for that.
    bash
    cat > seeds/orders.csv <<'EOF'
order_id,customer_id,amount,status,order_date
1,7,42.50,placed,2026-09-01
2,7,10.00,cancelled,2026-09-01
3,12,99.99,placed,2026-09-02
4,3,15.25,placed,2026-09-02
5,12,60.00,placed,2026-09-03
EOF
# push the seed to the bronze bucket as parquet so the models read from S3, like production would
PLATFORM_ENV=dev python - <<'PY'
import duckdb, os
c = duckdb.connect(); c.execute("install httpfs; load httpfs")
c.execute("set s3_endpoint='localhost:4566'; set s3_url_style='path'; set s3_use_ssl=false")
c.execute("set s3_access_key_id='test'; set s3_secret_access_key='test'; set s3_region='us-east-1'")
c.execute("copy (select * from read_csv('seeds/orders.csv')) to 's3://lake-dev-bronze/orders/orders.parquet' (format parquet)")
print('seeded bronze')
PY
  5. Write the models: a staging view that reads the bronze bucket and types the columns, and a mart that aggregates daily revenue. The bucket name is built from the lake_env var, so the same SQL targets dev or prod.
    text
    -- warehouse/models/staging/stg_orders.sql
select
    order_id::int      as order_id,
    customer_id::int   as customer_id,
    amount::double     as amount,
    status,
    order_date::date   as order_date
from read_parquet('s3://lake-{{ var("lake_env") }}-bronze/orders/*.parquet')

-- warehouse/models/marts/daily_revenue.sql
select
    order_date as day,
    count(*)                                          as orders,
    sum(case when status = 'placed' then amount else 0 end) as revenue,
    sum(case when status = 'cancelled' then 1 else 0 end)   as cancellations
from {{ ref('stg_orders') }}
group by 1
order by 1
  6. Add tests. The mart's grain (one row per day) and its non-null revenue are the contract downstream consumers rely on; dbt enforces them on every run.
    yaml
    # warehouse/models/marts/schema.yml
version: 2
models:
  - name: daily_revenue
    description: One row per calendar day with revenue and order counts.
    columns:
      - name: day
        data_tests: [not_null, unique]
      - name: revenue
        data_tests: [not_null]
      - name: orders
        data_tests:
          - dbt_utils.accepted_range:
              min_value: 0
  7. Build the warehouse in dev and read the result. dbt build runs models and tests together and stops if a test fails.
    bash
    export DBT_PROFILES_DIR=$PWD PLATFORM_ENV=dev
dbt deps 2>/dev/null || true    # for dbt_utils if you added it to packages.yml; skip if not
dbt build
python -c "import duckdb; print(duckdb.connect('target/dev.duckdb').execute('select * from daily_revenue').fetchall())"
cd ..
    If dbt_utils.accepted_range errors as unknown, add a packages.yml with dbt-labs/dbt_utils and run dbt deps, or drop that one test for now. The not_null/unique tests are built in and need no package.
Phase 4

CI that runs only what changed

Pull requests format and validate the Terraform, then run dbt build against an ephemeral platform — and using slim CI, only the models a change touched (and their children) run, not the whole warehouse.

  1. Understand slim CI before wiring it. dbt writes a manifest.json describing the project; comparing a pull request's manifest against production's lets dbt build --select state:modified+ run only the models that changed and everything downstream of them. On a warehouse with hundreds of models this is the difference between a two-minute PR check and a thirty-minute one.
    bash
    # produce the 'production' manifest once and keep it as the comparison baseline
cd warehouse && export DBT_PROFILES_DIR=$PWD PLATFORM_ENV=dev
dbt compile
mkdir -p ../.dbt-state && cp target/manifest.json ../.dbt-state/manifest.json
cd ..
    In a real setup the baseline manifest is downloaded from the last successful prod run (an artifact or an S3 object), not committed. For the lab, a checked-in baseline in .dbt-state/ is enough to see the mechanism work.
  2. Write the CI workflow. It starts LocalStack as a service, applies the dev Terraform to create an ephemeral platform, seeds data, then runs dbt build with state-based selection.
    yaml
    # .github/workflows/ci.yml
name: ci
on: [pull_request]

jobs:
  terraform:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: hashicorp/setup-terraform@v3
        with: {terraform_version: "1.13.4"}
      - run: terraform -chdir=infra fmt -check
      - run: terraform -chdir=infra init
      - run: terraform -chdir=infra validate

  dbt:
    runs-on: ubuntu-latest
    services:
      localstack:
        image: localstack/localstack:4.0
        ports: ["4566:4566"]
        env: {SERVICES: s3}
        options: >-
          --health-cmd "curl -sf http://localhost:4566/_localstack/health"
          --health-interval 5s --health-retries 20
    env:
      AWS_ACCESS_KEY_ID: test
      AWS_SECRET_ACCESS_KEY: test
      AWS_DEFAULT_REGION: us-east-1
      PLATFORM_ENV: dev
      S3_ENDPOINT: localhost:4566
    steps:
      - uses: actions/checkout@v4
      - uses: hashicorp/setup-terraform@v3
        with: {terraform_version: "1.13.4"}
      - uses: astral-sh/setup-uv@v5
      - run: uv venv --python 3.12 && uv pip install 'dbt-duckdb>=1.9,<2' duckdb
      - name: Create the platform
        run: |
          terraform -chdir=infra init
          terraform -chdir=infra workspace new dev || terraform -chdir=infra workspace select dev
          terraform -chdir=infra apply -auto-approve -var env=dev
      - name: Seed and build only what changed
        working-directory: warehouse
        env:
          DBT_PROFILES_DIR: ${{ github.workspace }}/warehouse
        run: |
          python ../platform/seed.py
          if [ -f ../.dbt-state/manifest.json ]; then
            ../.venv/bin/dbt build --select state:modified+ --state ../.dbt-state --defer || \
            ../.venv/bin/dbt build
          else
            ../.venv/bin/dbt build
          fi
    The || dbt build fallback runs everything when state selection finds nothing or errors (for example on the first PR before a baseline exists). Move the seeding into a small platform/seed.py (the inline Python from phase three) so both CI and local use one script.
  3. Extract the seeding into the script CI expects, and add a Makefile so the local and CI paths are identical.
    python
    # platform/seed.py
import os

import duckdb

env = os.getenv("PLATFORM_ENV", "dev")
endpoint = os.getenv("S3_ENDPOINT", "localhost:4566")
c = duckdb.connect()
c.execute("install httpfs; load httpfs")
c.execute(f"set s3_endpoint='{endpoint}'; set s3_url_style='path'; set s3_use_ssl=false")
c.execute(f"set s3_access_key_id='{os.getenv('AWS_ACCESS_KEY_ID', 'test')}'")
c.execute(f"set s3_secret_access_key='{os.getenv('AWS_SECRET_ACCESS_KEY', 'test')}'")
c.execute("set s3_region='us-east-1'")
c.execute(f"copy (select * from read_csv('warehouse/seeds/orders.csv')) "
          f"to 's3://lake-{env}-bronze/orders/orders.parquet' (format parquet)")
print(f"seeded lake-{env}-bronze")
  4. Test the slim-CI mechanism locally: change one model and confirm state selection picks only it and its children. This is the behaviour CI relies on.
    bash
    cd warehouse && export DBT_PROFILES_DIR=$PWD PLATFORM_ENV=dev
# baseline already in ../.dbt-state; now edit the mart:
echo '-- tweak' >> models/marts/daily_revenue.sql
dbt ls --select state:modified+ --state ../.dbt-state
# -> lists daily_revenue (and nothing upstream); staging is untouched so it is skipped
git checkout models/marts/daily_revenue.sql
cd ..
    state:modified+ — the trailing + means "and everything downstream". +model would be upstream. Selecting the right side of the graph to run is most of what makes a large dbt project's CI fast.
Phase 5

Deploy on merge

A merge to main applies the prod Terraform and builds the prod warehouse, so production is only ever changed through a reviewed, merged pull request.

  1. Write the deploy workflow. It mirrors CI but targets the prod workspace and prod buckets, and runs a full dbt build (prod is the source of truth; it does not defer to anything).
    yaml
    # .github/workflows/deploy.yml
name: deploy
on:
  push:
    branches: [main]

jobs:
  prod:
    runs-on: ubuntu-latest
    environment: prod
    services:
      localstack:
        image: localstack/localstack:4.0
        ports: ["4566:4566"]
        env: {SERVICES: s3}
        options: >-
          --health-cmd "curl -sf http://localhost:4566/_localstack/health"
          --health-interval 5s --health-retries 20
    env:
      AWS_ACCESS_KEY_ID: test
      AWS_SECRET_ACCESS_KEY: test
      AWS_DEFAULT_REGION: us-east-1
      PLATFORM_ENV: prod
      S3_ENDPOINT: localhost:4566
    steps:
      - uses: actions/checkout@v4
      - uses: hashicorp/setup-terraform@v3
        with: {terraform_version: "1.13.4"}
      - uses: astral-sh/setup-uv@v5
      - run: uv venv --python 3.12 && uv pip install 'dbt-duckdb>=1.9,<2' duckdb
      - name: Apply prod infrastructure
        run: |
          terraform -chdir=infra init
          terraform -chdir=infra workspace new prod || terraform -chdir=infra workspace select prod
          terraform -chdir=infra apply -auto-approve -var env=prod
      - name: Build the prod warehouse
        working-directory: warehouse
        env:
          DBT_PROFILES_DIR: ${{ github.workspace }}/warehouse
        run: |
          python ../platform/seed.py
          ../.venv/bin/dbt build
      - name: Publish the prod manifest as the new CI baseline
        uses: actions/upload-artifact@v4
        with:
          name: prod-manifest
          path: warehouse/target/manifest.json
    environment: prod lets you add a required reviewer in the repo settings, turning deploy-on-merge into deploy-on-approval without changing the workflow. The uploaded manifest is what a real slim-CI setup downloads as its baseline, closing the loop; here it demonstrates where the artifact comes from.
  2. Add branch protection so main can only change through a reviewed PR — the whole deploy story depends on main meaning something.
    bash
    REPO=$(gh repo view --json nameWithOwner -q .nameWithOwner)
gh api -X PUT "repos/$REPO/branches/main/protection" --input - <<'EOF'
{
  "required_status_checks": {"strict": true, "contexts": ["terraform", "dbt"]},
  "enforce_admins": false,
  "required_pull_request_reviews": {"required_approving_review_count": 0},
  "restrictions": null,
  "allow_force_pushes": false
}
EOF
echo 'main now requires the ci checks to pass before merge'
    required_approving_review_count: 0 keeps a solo project mergeable while still requiring the CI checks; on a team you raise it to 1. The status-check contexts must match the job names in ci.yml (terraform, dbt).
  3. Push everything and open the first PR to see CI run, then merge and watch deploy build prod.
    bash
    git add . && git commit -m 'platform: terraform storage, dbt warehouse, CI/CD, environments'
gh repo create data-platform --public --source=. --remote=origin --push
git switch -c feat/first-model
echo '-- first change' >> warehouse/models/marts/daily_revenue.sql
git commit -am 'warehouse: touch the mart' && git push -u origin feat/first-model
gh pr create --fill && gh pr checks --watch
gh pr merge --squash --delete-branch && git switch main && git pull
gh run watch
Phase 6

Watch the platform's own cost and runtime

A metrics table built from dbt's run artifacts and a dashboard of per-model runtime over time, plus a cost document mapping every local resource to its real-AWS price — so you can see a model getting slow, and know what the platform would cost for real.

  1. Every dbt build writes target/run_results.json with each model's execution time and status. Write a script that appends each run's results to a history table, so runtime becomes a time series you can watch. (Verified against the run_results.json schema.)
    python
    # platform/dbt_metrics.py
import json
import sys
from datetime import datetime, timezone
from pathlib import Path

import duckdb


def record(run_results: str, db: str = "platform/metrics.db") -> int:
    data = json.loads(Path(run_results).read_text())
    started = data["metadata"].get("generated_at", datetime.now(timezone.utc).isoformat())
    con = duckdb.connect(db)
    con.execute("""
        create table if not exists model_runs (
            run_at timestamp, unique_id varchar, status varchar,
            execution_time double, rows_affected bigint
        )""")
    rows = 0
    for r in data["results"]:
        resp = r.get("adapter_response", {}) or {}
        con.execute("insert into model_runs values (?, ?, ?, ?, ?)", [
            started, r["unique_id"], r["status"],
            round(r["execution_time"], 4), resp.get("rows_affected", 0),
        ])
        rows += 1
    con.close()
    return rows


if __name__ == "__main__":
    n = record(sys.argv[1] if len(sys.argv) > 1 else "warehouse/target/run_results.json")
    print(f"recorded {n} model results")
  2. Run a few builds and record each, so the history has something in it. In CI you would call this after every dbt build.
    bash
    cd warehouse && export DBT_PROFILES_DIR=$PWD PLATFORM_ENV=dev
for i in 1 2 3; do dbt build > /dev/null 2>&1; python ../platform/dbt_metrics.py target/run_results.json; done
cd ..
python -c "import duckdb; print(duckdb.connect('platform/metrics.db').execute('select unique_id, count(*), round(avg(execution_time),3) from model_runs group by 1').fetchall())"
  3. Write the dashboard: per-model runtime over time, the slowest models, and the pass/fail history of tests. This is the platform watching itself — the panel a data engineer checks when someone says "the pipeline got slow".
    python
    # platform/dashboard.py
import duckdb
import pandas as pd
import streamlit as st

st.set_page_config(page_title="Platform health", layout="wide")
st.title("Warehouse — model runtime and test history")

con = duckdb.connect("platform/metrics.db", read_only=True)
runs = con.execute("select * from model_runs order by run_at").fetch_df()
con.close()

if runs.empty:
    st.info("No runs recorded yet — run dbt build and platform/dbt_metrics.py.")
    st.stop()

models = runs[runs["unique_id"].str.startswith("model.")].copy()
models["name"] = models["unique_id"].str.split(".").str[-1]

col1, col2 = st.columns(2)
col1.metric("models tracked", models["name"].nunique())
col1.metric("runs recorded", runs["run_at"].nunique())
slowest = models.groupby("name")["execution_time"].mean().sort_values(ascending=False)
col2.subheader("Slowest models (avg seconds)")
col2.dataframe(slowest.round(3))

st.subheader("Runtime over time")
pivot = models.pivot_table(index="run_at", columns="name", values="execution_time", aggfunc="mean")
st.line_chart(pivot)

tests = runs[runs["unique_id"].str.startswith("test.")]
st.subheader("Test outcomes")
st.bar_chart(tests.groupby("status").size())
  4. Run the dashboard.
    bash
    streamlit run platform/dashboard.py
# http://localhost:8501 — the slowest-models table is where a real cost investigation starts
  5. Write COST.md: map every resource in the platform to its real-AWS equivalent and price, so the free local stack teaches the real bill. This is the document a platform engineer is asked for when finance comes calling.
    text
    # COST.md — what this platform costs on real AWS

| Local (free)                 | Real AWS                          | Pricing (us-east-1, 2026)                     |
|------------------------------|-----------------------------------|-----------------------------------------------|
| LocalStack S3                | S3 Standard                       | ~$0.023/GB-month + $0.0004/1k GET             |
| DuckDB reading S3            | Athena (serverless SQL over S3)   | $5 per TB scanned — partition + columnar to cut it |
| DuckDB reading S3 (alt)     | Redshift Serverless               | ~$0.36 per RPU-hour, min charge per query      |
| dbt on GitHub Actions       | dbt Cloud or self-hosted runner   | Actions minutes; dbt Cloud per-seat            |
| Terraform local state       | S3 backend + lockfile              | negligible                                     |

## Where the money goes
- **Scan cost dominates.** Athena/Redshift charge by data scanned, so the levers are: Parquet (columnar),
  partitioning by date, and not doing `select *`. The lakehouse project's storage module already does the first two.
- **The lifecycle rule matters.** Expiring raw data at 90 days (prod) / 7 days (dev) is a direct S3 bill reduction;
  it is one Terraform block, which is why environments-as-code pays for itself.
- **The runtime dashboard is the early warning.** A model whose execution_time trends up is scanning more data;
  catch it here before it shows up as a scan-cost spike on the bill.

## To run for real
Unset `S3_ENDPOINT` (and the LocalStack-only provider settings become no-ops), supply real AWS credentials,
swap the DuckDB S3 settings for the defaults, and add an S3 backend to infra/. The dbt models do not change.
Help

Troubleshooting

terraform apply fails with InvalidAccessKeyId or a credentials error
The provider is not in LocalStack mode. Confirm var.s3_endpoint is http://localhost:4566 (the default) so local_mode is true and the dummy credentials and path-style addressing are applied. On real AWS you leave s3_endpoint empty and provide real credentials via the environment.
dbt fails with IO Error: Connection error for HTTP HEAD or 403 reading S3
DuckDB's S3 settings must match LocalStack: s3_use_ssl: false, s3_url_style: path, endpoint localhost:4566, and the same test/test credentials Terraform used. If a model reads a bucket that does not exist, run terraform apply for that environment first — dbt does not create buckets.
dbt build reads an empty table or errors that no parquet files matched
The bronze bucket has no data. Run platform/seed.py with the right PLATFORM_ENV before building; the model reads s3://lake-<env>-bronze/orders/*.parquet, which only exists after seeding. Check with aws --endpoint-url=http://localhost:4566 s3 ls s3://lake-dev-bronze/orders/.
Slim CI (state:modified+) runs everything, or nothing
It runs everything when there is no baseline manifest — expected on the first PR, and the || dbt build fallback covers it. It runs nothing when the baseline equals the current project (no changes). Confirm the baseline path with --state ../.dbt-state points at a directory containing manifest.json, and that you edited a model since it was captured.
Two workspaces seem to share state / applying prod changed dev
You did not switch workspaces. terraform workspace show before every apply; the bucket names include terraform.workspace implicitly only if you wired var.env to it — here var.env is passed explicitly, so passing -var env=prod while in the dev workspace would write prod buckets into dev state. Keep the workspace and the -var env= value the same; the plan.sh wrapper does.
LocalStack loses all buckets after a restart
The community image does not persist S3 by default across docker compose down. The compose file mounts ./.localstack for some persistence; for a clean slate that is fine — just re-run terraform apply and seed.py. Never rely on LocalStack for durable data; it is a dev target, and that is the point of also writing COST.md for the real thing.
Next

Where to go from here

Did a step fail or feel unclear? Tell me which one →