SUBMISSION: Capstone 2 — Build a Production CI/CD Workflow with GitHub Actions & Slim CI

🎯 Final Course Capstone Deliverable: Architect the enterprise CI/CD pipeline for dbt. Implement GitHub Actions workflows that run Slim CI builds with state deferral and ephemeral test schemas.

🏢 Business Scenario & Executive Prompt

Your data team has scaled from 2 to 12 analytics engineers. Team members frequently run manual builds from local laptops, pushing breaking models to production and overwriting each other's sandbox schemas.

Your challenge is to establish an Enterprise CI/CD Workflow with GitHub Actions:

Every Pull Request triggers an automated dbt build in an isolated ephemeral schema (dbt_pr_<pr_number>).
Only modified models and their downstream dependencies are executed using Slim CI (state:modified+ --defer).
PRs can only be merged when all dbt tests pass.
Ephemeral schemas are automatically cleaned up when the PR is merged or closed.

🎯 Required Deliverables

Submit a .zip archive or link to your GitHub repository containing:

.github/workflows/dbt_ci.yml: Production GitHub Actions workflow file running Slim CI with state deferral.
ci_config/profiles.yml: Multi-environment profile using env_var for credential security.
macros/drop_pr_schema.sql: Jinja macro to tear down ephemeral schemas on PR close.
.github/PULL_REQUEST_TEMPLATE.md: Engineering checklist for PR authors.
README.md: Runbook documenting the CI/CD architecture and branch protection rules.

🛠️ Step-by-Step Implementation Guide

Step 1 — Multi-Environment `profiles.yml`

Never commit credentials to git. Use ci_config/profiles.yml with environment variables:

my_dbt_project:
  target: ci
  outputs:
    ci:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: TRANSFORMER_CI
      database: ANALYTICS_CI
      warehouse: CI_WH
      schema: "dbt_pr_{{ env_var('PR_NUMBER') }}"

Step 2 — Implement Slim CI with State Deferral

In .github/workflows/dbt_ci.yml:

Download the latest production manifest.json from S3 or cloud storage.

Execute:

dbt build --select state:modified+ --defer --state ./prod_state

Why Slim CI? If your project has 400 models but the engineer only edited 1 model, Slim CI runs that 1 model and its direct downstream tests in 45 seconds instead of rebuilding 400 models for 45 minutes!

Step 3 — Author the Teardown Macro (`drop_pr_schema.sql`)

When a PR closes, drop the temporary schema to keep your warehouse clean:

{% macro drop_pr_schema(pr_number) %}
  {% set schema_name = 'dbt_pr_' ~ pr_number %}
  {% set drop_query %}
    DROP SCHEMA IF EXISTS ANALYTICS_CI.{{ schema_name }} CASCADE;
  {% endset %}
  {{ log("Dropping ephemeral CI schema: " ~ schema_name, info=True) }}
  {% do run_query(drop_query) %}
{% endmacro %}

Step 4 — Author the Pull Request Template

Create .github/PULL_REQUEST_TEMPLATE.md to ensure every engineer confirms tests and performance impacts before requesting a review.

Step 5 — Verify CI/CD End-to-End

Test the complete workflow:

Open a test PR modifying a single model.
Verify that GitHub Actions triggers, checks out the repo, pulls the prod manifest, and executes only state:modified+.
Verify that the build succeeds and results land in dbt_pr_<number>.
Close the PR and verify the teardown macro executes.

💻 Starter Scaffold

1. `.github/workflows/dbt_ci.yml`

name: dbt Slim CI

on:
  pull_request:
    branches: [main]
    paths:
      - 'models/**'
      - 'macros/**'
      - 'snapshots/**'
      - 'dbt_project.yml'

jobs:
  dbt_ci:
    name: Run dbt Slim CI Build
    runs-on: ubuntu-latest
    env:
      DBT_PROFILES_DIR: ./ci_config
      PR_NUMBER: ${{ github.event.pull_request.number }}
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}

    steps:
      - name: Checkout Code
        uses: actions/checkout@v4

      - name: Set up Python 3.10
        uses: actions/setup-python@v5
        with:
          python-version: '3.10'

      - name: Install dbt & Dependencies
        run: |
          pip install dbt-snowflake
          dbt deps

      - name: Download Production Manifest for Slim CI
        run: |
          mkdir -p prod_state
          aws s3 cp s3://company-dbt-artifacts/prod/manifest.json ./prod_state/manifest.json
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}

      - name: Execute Slim CI Build
        run: |
          dbt build --select state:modified+ --defer --state ./prod_state

2. `ci_config/profiles.yml`

my_dbt_project:
  target: ci
  outputs:
    ci:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: TRANSFORMER_CI
      database: ANALYTICS_CI
      warehouse: CI_WH
      schema: "dbt_pr_{{ env_var('PR_NUMBER') }}"

3. `macros/drop_pr_schema.sql`

{% macro drop_pr_schema(pr_number) %}
  {% set schema_name = 'dbt_pr_' ~ pr_number %}
  {% set drop_query %}
    DROP SCHEMA IF EXISTS ANALYTICS_CI.{{ schema_name }} CASCADE;
  {% endset %}
  {{ log("Dropping ephemeral CI schema: " ~ schema_name, info=True) }}
  {% do run_query(drop_query) %}
{% endmacro %}

4. `.github/PULL_REQUEST_TEMPLATE.md`

## Summary of Changes
<!-- Describe what models were modified or created -->

## Business Rationale
<!-- Why is this change necessary? -->

## Validation Checklist
- [ ] `dbt build --select state:modified+` passes locally
- [ ] Primary key uniqueness and non-null tests pass
- [ ] No hardcoded database or schema names used
- [ ] Descriptions added to `schema.yml`

📊 100-Point Evaluation Rubric

Evaluation Dimension	Weight	Target Standard (Pass ≥ 80%)
1. Ephemeral PR Schema Isolation	25 pts	Dynamic branch/PR schema naming (`dbt_pr_<number>`) preventing developers from clobbering each other or prod.
2. Slim CI & Deferral Configuration	25 pts	Accurate syntax: `dbt build --select state:modified+ --defer --state <path>` to build only modified nodes against prod artifacts.
3. Production Profiles & Secret Security	25 pts	Clean separation of credentials via environment variables (`env_var`); zero hardcoded passwords in git.
4. Teardown Hygiene & Runbook	25 pts	Scripted drop of ephemeral schemas post-PR merge/close and complete README instructions.

📤 Submission Instructions & Guidelines

Deliverable Formats: Upload a .zip archive containing your .github/ folder, ci_config/, and macros/, or provide your GitHub repository / PR link directly in the Submission Notes.
Dialect Compatibility: The GitHub Actions workflow and Jinja profiles can be adapted to Snowflake, BigQuery, Databricks, Redshift, or Postgres.
Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
Upload: Use the drag-and-drop file upload zone below.
Confirmation: Click Submit Assignment to complete the final course capstone!

Assignment Instructions