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

# 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**: 1. Every Pull Request triggers an automated dbt build in an isolated ephemeral schema (`dbt_pr_ `). 2. Only modified models and their downstream dependencies are executed using **Slim CI** (`state:modified+ --defer`). 3. PRs can only be merged when all dbt tests pass. 4. 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: 1. **`.github/workflows/dbt_ci.yml`**: Production GitHub Actions workflow file running Slim CI with state deferral. 2. **`ci_config/profiles.yml`**: Multi-environment profile using `env_var` for credential security. 3. **`macros/drop_pr_schema.sql`**: Jinja macro to tear down ephemeral schemas on PR close. 4. **`.github/PULL_REQUEST_TEMPLATE.md`**: Engineering checklist for PR authors. 5. **`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: ```yaml 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: ```bash 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: ```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 %} ``` ### 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: 1. Open a test PR modifying a single model. 2. Verify that GitHub Actions triggers, checks out the repo, pulls the prod manifest, and executes only `state:modified+`. 3. Verify that the build succeeds and results land in `dbt_pr_ `. 4. Close the PR and verify the teardown macro executes. --- ## 💻 Starter Scaffold ### 1. `.github/workflows/dbt_ci.yml` ```yaml 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` ```yaml my_dbt_proje