CI/CD: Terraform, dbt, and Schema Evolution

In modern data engineering, Snowflake has become a leading cloud data warehouse, but managing its infrastructure and data transformations manually is error-prone and unscalable. Enter CI/CD—Continuous Integration and Continuous Deployment—which brings software engineering best practices to data pipelines. This tutorial focuses on three key tools: Terraform for infrastructure-as-code, dbt (data build tool) for SQL transformations, and strategies for schema evolution. You'll learn how to define Snowflake resources (warehouses, databases, roles) in Terraform, version-control your dbt models, and handle schema changes without breaking production. We'll walk through a real-world production incident where a schema change caused a downstream outage, and how to debug it. By the end, you'll have a production-ready CI/CD pipeline that ensures your Snowflake environment is reproducible, testable, and resilient.

1. Setting Up Terraform for Snowflake

Terraform allows you to define Snowflake resources as code. Start by installing the Snowflake provider. Create a main.tf file with provider configuration. Use variables for sensitive info like account and private key. Define a virtual warehouse, database, and schema. Example: resource 'snowflake_warehouse' 'my_wh' { name = 'my_warehouse' warehouse_size = 'X-SMALL' auto_suspend = 60 }. Then run terraform init, plan, apply. Best practice: use remote state (e.g., S3) and separate environments (dev, prod) with workspaces.

provider "snowflake" {
  alias = "admin"
}

resource "snowflake_warehouse" "my_wh" {
  provider       = snowflake.admin
  name           = "MY_WH"
  warehouse_size = "X-SMALL"
  auto_suspend   = 60
}

resource "snowflake_database" "my_db" {
  provider = snowflake.admin
  name     = "MY_DB"
}

resource "snowflake_schema" "my_schema" {
  provider   = snowflake.admin
  database   = snowflake_database.my_db.name
  name       = "MY_SCHEMA"
}

2. Managing dbt Models and Tests

dbt transforms data in your warehouse using SQL SELECT statements. Initialize a dbt project with 'dbt init'. Configure profiles.yml with Snowflake connection details. Create models in the models/ folder. Use Jinja templating for dynamic SQL. Example: a model that aggregates sales by month. Add tests using schema.yml: define unique, not_null, and custom tests. Run 'dbt run' to build models and 'dbt test' to validate. Use 'dbt docs generate' for documentation.

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
FROM {{ ref('orders') }}
GROUP BY 1

3. Schema Evolution Strategies

Schema evolution is inevitable. Use backward-compatible changes: add columns as NULLable, avoid renaming/dropping without deprecation. In dbt, use 'on_schema_change' to automatically add new columns. For breaking changes, create new models and deprecate old ones. Use Snowflake's Time Travel to rollback if needed. Example: adding a column to a source table. Update dbt model to include the new column. Test with 'dbt test' before deploying.

version: 2

models:
  - name: monthly_sales
    columns:
      - name: month
        tests:
          - unique
          - not_null
      - name: total_sales
        tests:
          - not_null

sources:
  - name: raw
    schema: public
    tables:
      - name: orders
        loaded_at_field: loaded_at
        freshness:
          warn_after: {count: 1, period: day}

4. Building a CI/CD Pipeline with GitHub Actions

Automate testing and deployment. Create a .github/workflows/ci.yml file. Trigger on pull requests to main. Steps: checkout code, set up Terraform, run 'terraform plan' for dev. Set up dbt, run 'dbt deps', 'dbt run', 'dbt test'. If all pass, merge. Then a deploy workflow runs 'terraform apply' and 'dbt run' on prod. Use secrets for Snowflake credentials. Example workflow snippet.

name: CI
on: [pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Setup Terraform
        uses: hashicorp/setup-terraform@v2
        with:
          terraform_version: 1.5.0
      - name: Terraform Plan
        run: terraform plan
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
          SNOWFLAKE_PRIVATE_KEY: ${{ secrets.SNOWFLAKE_PRIVATE_KEY }}
      - name: Setup dbt
        uses: dbt-labs/actions/setup-dbt@v1
      - name: dbt Run and Test
        run: |
          dbt deps
          dbt run
          dbt test

5. Zero-Downtime Deployments with Blue-Green Strategy

For critical pipelines, use blue-green deployment. Maintain two environments (blue and green). Deploy changes to the inactive environment, test, then switch traffic. In Snowflake, this can be achieved by using separate schemas or databases. Use dbt's 'target' configuration to point to different schemas. Example: blue schema 'prod_v1', green schema 'prod_v2'. After validation, update the view or alias to point to the new schema. Use Terraform to manage the switch.

snowflake:
  target: blue
  outputs:
    blue:
      type: snowflake
      schema: prod_v1
      ...
    green:
      type: snowflake
      schema: prod_v2
      ...

6. Monitoring and Rollback Strategies

Even with CI/CD, issues can slip through. Use dbt's 'source freshness' to detect stale data. Set up alerts on dbt test failures. For rollbacks, Snowflake Time Travel allows you to restore tables to a previous state (up to 90 days). Use 'UNDROP' for dropped objects. In dbt, use 'dbt run --full-refresh' to rebuild models from scratch. For Terraform, use 'terraform state rm' to remove resources and re-import. Example: rolling back a dbt model.

-- Restore dropped table using Time Travel
ALTER TABLE my_table UNDROP;

-- Or query historical data
SELECT * FROM my_table AT (TIMESTAMP => '2025-01-01 12:00:00'::TIMESTAMP);