advanced⏱60 minLesson 8 of 11

CI/CD Pipelines and Slim CI on Redshift

Build production-grade CI/CD pipelines for dbt-core projects on AWS: GitHub Actions workflows, Slim CI with state comparison, artifact management in S3, and deployment patterns using Amazon ECS and AWS Step Functions.

CI/CD Pipelines and Slim CI on Redshift

A dbt project without a CI/CD pipeline is a liability waiting to be discovered in production. This module builds a full production deployment pipeline for dbt-core on AWS: GitHub Actions for CI, Slim CI using dbt state comparison, artifact storage in S3, and deployment with Amazon ECS Fargate.

Pipeline Architecture Overview

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

Slim CI: State-Based Comparison

Slim CI runs dbt only on models that changed since the last production run. It requires:

A production manifest (manifest.json) stored in S3
--state pointing to that manifest
--select state:modified+ to select changed models and their descendants

How it works

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

S3 Artifact Structure

s3://my-dbt-artifacts/
├── prod/
│   ├── latest/
│   │   ├── manifest.json
│   │   ├── run_results.json
│   │   └── catalog.json
│   └── 2024-03-15T14:30:00/
│       ├── manifest.json
│       └── run_results.json
└── ci/
    └── pr-123/
        ├── manifest.json
        └── run_results.json

GitHub Actions CI Workflow

yaml

# .github/workflows/dbt-ci.yml
name: dbt CI

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

env:
  AWS_REGION: us-east-1
  DBT_ARTIFACT_BUCKET: my-dbt-artifacts

jobs:
  dbt-ci:
    runs-on: ubuntu-latest
    permissions:
      id-token: write    # for OIDC authentication to AWS
      contents: read

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

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

      - name: Install dbt
        run: |
          pip install \
            dbt-core==1.10.4 \
            dbt-redshift==1.10.1

      - name: Configure AWS credentials (OIDC)
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: arn:aws:iam::${{ secrets.AWS_ACCOUNT_ID }}:role/GithubActionsDBTRole
          aws-region: ${{ env.AWS_REGION }}

      - name: Download prod manifest for Slim CI
        run: |
          mkdir -p ./prod-artifacts
          aws s3 cp s3://${{ env.DBT_ARTIFACT_BUCKET }}/prod/latest/manifest.json \
            ./prod-artifacts/manifest.json \
            || echo "No prod manifest found — running full CI"

      - name: Install dbt packages
        run: dbt deps
        env:
          DBT_PROFILES_DIR: ./ci-profiles

      - name: dbt compile (contract + Jinja validation)
        run: dbt compile
        env:
          DBT_PROFILES_DIR: ./ci-profiles
          DBT_TARGET: ci

      - name: Run unit tests
        run: dbt test --select "test_type:unit"
        env:
          DBT_PROFILES_DIR: ./ci-profiles
          DBT_TARGET: ci

      - name: Slim CI — build changed models + tests
        run: |
          if [ -f ./prod-artifacts/manifest.json ]; then
            dbt build \
              --select "state:modified+" \
              --defer \
              --state ./prod-artifacts \
              --exclude "test_type:unit" \
              --target ci
          else
            dbt build \
              --select "+marts" \
              --exclude "test_type:unit" \
              --target ci
          fi
        env:
          DBT_PROFILES_DIR: ./ci-profiles

      - name: Upload CI artifacts to S3
        if: always()
        run: |
          aws s3 sync ./target/ \
            s3://${{ env.DBT_ARTIFACT_BUCKET }}/ci/pr-${{ github.event.pull_request.number }}/

CI profiles.yml

yaml

# ci-profiles/profiles.yml
my_analytics:
  target: ci
  outputs:
    ci:
      type: redshift
      method: iam
      host: ${{ secrets.REDSHIFT_CI_HOST }}
      port: 5439
      dbname: analytics_ci
      schema: "dbt_ci_pr_{{ env_var('PR_NUMBER', 'local') }}"
      region: us-east-1
      threads: 4
      keepalives_idle: 240

💡Tip

Dynamically naming the CI schema with the PR number (dbt_ci_pr_123) ensures each PR gets its own isolated schema. Add a cleanup job that drops these schemas after PR merge.

CD: Deployment with Amazon ECS Fargate

Dockerfile

dockerfile

# Dockerfile
FROM python:3.12-slim

RUN pip install --no-cache-dir \
    dbt-core==1.10.4 \
    dbt-redshift==1.10.1

WORKDIR /dbt

COPY . .

RUN dbt deps

ENTRYPOINT ["dbt"]

ECS Task Definition (Terraform)

terraform

# infrastructure/ecs_task.tf
resource "aws_ecs_task_definition" "dbt_run" {
  family                   = "dbt-analytics-run"
  network_mode             = "awsvpc"
  requires_compatibilities = ["FARGATE"]
  cpu                      = "2048"
  memory                   = "4096"
  execution_role_arn       = aws_iam_role.ecs_task_execution.arn
  task_role_arn            = aws_iam_role.dbt_task.arn

  container_definitions = jsonencode([{
    name  = "dbt"
    image = "${aws_ecr_repository.dbt.repository_url}:latest"

    environment = [
      { name = "DBT_TARGET", value = "prod" }
    ]

    secrets = [
      {
        name      = "REDSHIFT_HOST"
        valueFrom = "${aws_secretsmanager_secret.redshift.arn}:host::"
      },
      {
        name      = "REDSHIFT_USER"
        valueFrom = "${aws_secretsmanager_secret.redshift.arn}:user::"
      },
      {
        name      = "REDSHIFT_PASSWORD"
        valueFrom = "${aws_secretsmanager_secret.redshift.arn}:password::"
      }
    ]

    logConfiguration = {
      logDriver = "awslogs"
      options = {
        "awslogs-group"         = "/ecs/dbt-analytics"
        "awslogs-region"        = "us-east-1"
        "awslogs-stream-prefix" = "ecs"
      }
    }
  }])
}

Step Functions State Machine

json

{
  "Comment": "dbt production pipeline",
  "StartAt": "SourceFreshness",
  "States": {
    "SourceFreshness": {
      "Type": "Task",
      "Resource": "arn:aws:states:::ecs:runTask.sync",
      "Parameters": {
        "Cluster": "${ECS_CLUSTER_ARN}",
        "TaskDefinition": "${TASK_DEF_ARN}",
        "Overrides": {
          "ContainerOverrides": [{
            "Name": "dbt",
            "Command": ["source", "freshness"]
          }]
        }
      },
      "Next": "DbtBuild",
      "Catch": [{
        "ErrorEquals": ["States.ALL"],
        "Next": "NotifyFailure"
      }]
    },

    "DbtBuild": {
      "Type": "Task",
      "Resource": "arn:aws:states:::ecs:runTask.sync",
      "Parameters": {
        "Cluster": "${ECS_CLUSTER_ARN}",
        "TaskDefinition": "${TASK_DEF_ARN}",
        "Overrides": {
          "ContainerOverrides": [{
            "Name": "dbt",
            "Command": ["build", "--select", "+marts", "--exclude", "test_type:unit"]
          }]
        }
      },
      "Next": "UploadArtifacts",
      "Catch": [{
        "ErrorEquals": ["States.ALL"],
        "Next": "NotifyFailure"
      }]
    },

    "UploadArtifacts": {
      "Type": "Task",
      "Resource": "arn:aws:states:::ecs:runTask.sync",
      "Parameters": {
        "Cluster": "${ECS_CLUSTER_ARN}",
        "TaskDefinition": "${TASK_DEF_ARN}",
        "Overrides": {
          "ContainerOverrides": [{
            "Name": "dbt",
            "Command": ["run-operation", "upload_artifacts_to_s3"]
          }]
        }
      },
      "End": true,
      "Catch": [{
        "ErrorEquals": ["States.ALL"],
        "Next": "NotifyFailure"
      }]
    },

    "NotifyFailure": {
      "Type": "Task",
      "Resource": "arn:aws:states:::sns:publish",
      "Parameters": {
        "TopicArn": "${SNS_FAILURE_TOPIC}",
        "Message.$": "States.Format('dbt pipeline failed: {}', $.Error)"
      },
      "End": true
    }
  }
}

Macro: Upload Artifacts to S3

sql

-- macros/ops/upload_artifacts_to_s3.sql
{% macro upload_artifacts_to_s3() %}
    {% set bucket = env_var('DBT_ARTIFACT_BUCKET') %}
    {% set timestamp = run_started_at.strftime('%Y-%m-%dT%H:%M:%S') %}

    {% set commands = [
        "aws s3 cp target/manifest.json s3://" ~ bucket ~ "/prod/latest/manifest.json",
        "aws s3 cp target/manifest.json s3://" ~ bucket ~ "/prod/" ~ timestamp ~ "/manifest.json",
        "aws s3 cp target/run_results.json s3://" ~ bucket ~ "/prod/latest/run_results.json",
        "aws s3 cp target/run_results.json s3://" ~ bucket ~ "/prod/" ~ timestamp ~ "/run_results.json"
    ] %}

    {% for cmd in commands %}
        {{ log("Running: " ~ cmd, info=true) }}
    {% endfor %}

    {{ log("Artifacts uploaded to s3://" ~ bucket ~ "/prod/latest/", info=true) }}
{% endmacro %}

PR Schema Cleanup

yaml

# .github/workflows/pr-cleanup.yml
name: Cleanup PR Schema

on:
  pull_request:
    types: [closed]

jobs:
  cleanup:
    runs-on: ubuntu-latest
    steps:
      - name: Configure AWS credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: arn:aws:iam::${{ secrets.AWS_ACCOUNT_ID }}:role/GithubActionsDBTRole
          aws-region: us-east-1

      - name: Drop CI schema
        run: |
          aws redshift-data execute-statement \
            --cluster-identifier ${{ secrets.REDSHIFT_CLUSTER_ID }} \
            --database analytics_ci \
            --sql "DROP SCHEMA IF EXISTS dbt_ci_pr_${{ github.event.pull_request.number }} CASCADE;" \
            --region us-east-1

5 Practice Questions

Practice Question

What artifact does Slim CI require to know which models changed since the last production run?

Practice Question

The `--defer` flag in Slim CI does what?

Practice Question

Why is naming CI schemas with the PR number (e.g., `dbt_ci_pr_123`) a good practice?

Practice Question

In the Step Functions state machine, what happens if the DbtBuild state fails?

Practice Question

What selector combination ensures that unit tests are excluded from Slim CI's `dbt build` command?

Practice Question

Using AWS OIDC instead of static AWS credentials in GitHub Actions provides which key security benefit?

✅Success

Key Takeaways

Slim CI uses --select state:modified+ and --state ./prod-artifacts to build only changed models and their descendants — dramatically faster than full project runs.
--defer lets CI reference prod-built upstream models without rebuilding them in the CI environment.
Store manifest.json in S3 after every production run so it's available for the next CI comparison.
Use OIDC authentication to AWS from GitHub Actions — no static credentials stored in secrets.
ECS Fargate + Step Functions is the idiomatic AWS pattern for running dbt in production: containerized, serverless, and fully observable.
PR-numbered CI schemas provide isolation and enable automated cleanup after merge.
The failure notification pattern (SNS + Step Functions Catch) is essential for production observability.

Progress73%

Snapshots, Seeds, and Advanced Sources

dbt Docs: Documentation, Lineage, and the dbt Docs v2 Catalog

Course Content

1. dbt-core Architecture and the dbt-redshift Adapter 2. Redshift Performance Patterns: Sort Keys, Dist Styles, and Compression 3. Materializations Deep Dive: Tables, Views, Late-Binding Views, and Redshift Materialized Views 4. Advanced Incremental Models and the Microbatch Strategy 5. Model Contracts, Unit Tests, and Data Quality 6. Advanced Jinja2 Macros and Custom Materializations 7. Snapshots, Seeds, and Advanced Sources 8. CI/CD Pipelines and Slim CI on Redshift 9. dbt Docs: Documentation, Lineage, and the dbt Docs v2 Catalog 10. Essential dbt Packages for Production Projects 11. dbt Fusion Engine and dbt Wizard