How to Separate Dev and Prod Warehouses in CI

A separate CI target on a dev database keeps pull request runs off the production warehouse entirely.

Define distinct ci and prod targets in profiles.yml, then select the target by branch so PRs use dev and main uses prod.

Steps

• Add ci and prod outputs with different databases and warehouses.
• Use the ci target on pull_request.
• Gate the prod target behind a protected environment on main.

profiles.yml

analytics:
  outputs:
    ci:
      type: snowflake
      database: DEV
      warehouse: DEV_WH
      schema: "pr_{{ env_var('GITHUB_RUN_ID', 'local') }}"
    prod:
      type: snowflake
      database: PROD
      warehouse: PROD_WH
      schema: analytics

Workflow

jobs:
  pr:
    if: github.event_name == 'pull_request'
    runs-on: ubuntu-latest
    steps:
      - run: dbt build --target ci
  deploy:
    if: github.ref == 'refs/heads/main'
    environment: production
    runs-on: ubuntu-latest
    steps:
      - run: dbt build --target prod

Gotchas

• Grant the CI role write access to dev only, never to the prod database.
• A per-PR schema keeps concurrent pull requests from colliding in the dev database.

Related guides