Postgres Blue-Green dbt Schema Swap with Kestra

id: postgres-dbt-blue-green-schema-swap
namespace: company.data

description: |
  Execute dbt transformations in an isolated staging schema, verify model completeness,
  and perform an atomic zero-downtime schema swap into production PostgreSQL.

inputs:
  - id: postgres_host
    type: STRING
    displayName: PostgreSQL Host
    description: Hostname of the target PostgreSQL database server.
    defaults: "localhost"

  - id: postgres_port
    type: INT
    displayName: PostgreSQL Port
    description: Port number for the PostgreSQL service.
    defaults: 5432

  - id: postgres_db
    type: STRING
    displayName: PostgreSQL Database Name
    description: Target database name where models are built and swapped.
    defaults: "analytics"

  - id: postgres_user
    type: STRING
    displayName: PostgreSQL Username
    description: Database user with DDL privileges to create and rename schemas.
    defaults: "postgres"

  - id: live_schema
    type: STRING
    displayName: Live Production Schema
    description: Production schema queried by live BI dashboards and services.
    defaults: "analytics_live"

  - id: staging_schema
    type: STRING
    displayName: Staging Schema
    description: Ephemeral schema where dbt builds and tests candidate models in isolation.
    defaults: "analytics_staging"

  - id: backup_schema
    type: STRING
    displayName: Backup Rollback Schema
    description: Schema holding the previous live state immediately after swap for
      instant rollback.
    defaults: "analytics_backup"

  - id: dbt_git_repo
    type: STRING
    displayName: dbt Project Git Repository
    description: URL of the version-controlled dbt repository containing models and tests.
    defaults: "https://github.com/kestra-io/dbt-example"

  - id: dbt_git_branch
    type: STRING
    displayName: dbt Git Branch
    description: Target Git branch or tag to build.
    defaults: "main"

  - id: dry_run
    type: BOOL
    displayName: Dry Run Audit Mode
    description: When true, builds models in staging and audits counts without
      swapping into production.
    defaults: true

tasks:
  - id: prepare_staging_schema
    type: io.kestra.plugin.jdbc.postgresql.Queries
    description: Recreates an empty staging schema to guarantee a clean build environment.
    url: "jdbc:postgresql://{{ inputs.postgres_host }}:{{ inputs.postgres_port }}/{{
      inputs.postgres_db }}"
    username: "{{ inputs.postgres_user }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    fetchType: NONE
    sql: |
      DROP SCHEMA IF EXISTS {{ inputs.staging_schema }} CASCADE;
      CREATE SCHEMA {{ inputs.staging_schema }};

  - id: run_dbt_staging
    type: io.kestra.plugin.core.flow.WorkingDirectory
    description: Clones the dbt project and builds all models and tests targeting staging.
    tasks:
      - id: clone_repository
        type: io.kestra.plugin.git.Clone
        description: Clones the version-controlled dbt models repository.
        url: "{{ inputs.dbt_git_repo }}"
        branch: "{{ inputs.dbt_git_branch }}"

      - id: build_dbt_models
        type: io.kestra.plugin.dbt.cli.DbtCLI
        description: Runs dbt deps and dbt build strictly against the staging schema.
        containerImage: ghcr.io/kestra-io/dbt-postgres:latest
        profiles: |
          my_dbt_project:
            outputs:
              target_env:
                type: postgres
                host: "{{ inputs.postgres_host }}"
                user: "{{ inputs.postgres_user }}"
                password: "{{ secret('POSTGRES_PASSWORD') }}"
                port: {{ inputs.postgres_port }}
                dbname: "{{ inputs.postgres_db }}"
                schema: "{{ inputs.staging_schema }}"
                threads: 8
                connect_timeout: 30
            target: target_env
        commands:
          - dbt deps
          - dbt build

  - id: verify_staging_models
    type: io.kestra.plugin.jdbc.postgresql.Query
    description: Audits the built staging schema to confirm tables were materialized
      before promotion.
    url: "jdbc:postgresql://{{ inputs.postgres_host }}:{{ inputs.postgres_port }}/{{
      inputs.postgres_db }}"
    username: "{{ inputs.postgres_user }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    fetchOne: true
    sql: |
      SELECT count(*)::int AS model_count
      FROM information_schema.tables
      WHERE table_schema = '{{ inputs.staging_schema }}'
        AND table_type = 'BASE TABLE';

  - id: evaluate_swap_gate
    type: io.kestra.plugin.core.flow.If
    description: Gates atomic swap on successful model materialization and non-dry-run mode.
    condition: "{{ outputs.verify_staging_models.row.model_count > 0 and not
      inputs.dry_run }}"
    then:
      - id: atomic_schema_swap
        type: io.kestra.plugin.jdbc.postgresql.Queries
        description: Atomically promotes staging to live inside a single transactional
          block.
        url: "jdbc:postgresql://{{ inputs.postgres_host }}:{{ inputs.postgres_port }}/{{
          inputs.postgres_db }}"
        username: "{{ inputs.postgres_user }}"
        password: "{{ secret('POSTGRES_PASSWORD') }}"
        fetchType: NONE
        sql: |
          BEGIN;
          DROP SCHEMA IF EXISTS {{ inputs.backup_schema }} CASCADE;
          CREATE SCHEMA IF NOT EXISTS {{ inputs.live_schema }};
          ALTER SCHEMA {{ inputs.live_schema }} RENAME TO {{ inputs.backup_schema }};
          ALTER SCHEMA {{ inputs.staging_schema }} RENAME TO {{ inputs.live_schema }};
          COMMIT;

      - id: notify_swap_success
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Dispatches atomic promotion success notice to Slack.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        messageText: |
          :white_check_mark: *Zero-Downtime Blue-Green Schema Swap Complete*
          Execution: `{{ execution.id }}`
          Database: `{{ inputs.postgres_db }}`
          Promoted Schema: `{{ inputs.live_schema }}` (Materialized Tables: `{{ outputs.verify_staging_models.row.model_count }}`)
          Previous Live Preserved At: `{{ inputs.backup_schema }}`
    else:
      - id: log_dry_run_or_empty
        type: io.kestra.plugin.core.log.Log
        description: Logs audit outcome when dry run is active or staging models are empty.
        message: "Staging verification completed (Models built: {{
          outputs.verify_staging_models.row.model_count }}). Atomic swap skipped
          (dry_run={{ inputs.dry_run }})."

errors:
  - id: cleanup_staging_on_error
    type: io.kestra.plugin.jdbc.postgresql.Queries
    description: Purges the orphaned staging schema on build failure, preserving production.
    url: "jdbc:postgresql://{{ inputs.postgres_host }}:{{ inputs.postgres_port }}/{{
      inputs.postgres_db }}"
    username: "{{ inputs.postgres_user }}"
    password: "{{ secret('POSTGRES_PASSWORD') }}"
    fetchType: NONE
    sql: |
      DROP SCHEMA IF EXISTS {{ inputs.staging_schema }} CASCADE;

  - id: notify_failure_slack
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Dispatches incident card to Slack confirming live schema remains untouched.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    messageText: |
      :rotating_light: *dbt Build Failed in Staging - Production Protected*
      Execution: `{{ execution.id }}`
      Database: `{{ inputs.postgres_db }}`
      Staging schema `{{ inputs.staging_schema }}` was discarded.
      Live schema `{{ inputs.live_schema }}` was not modified.

triggers:
  - id: daily_dbt_run
    type: io.kestra.plugin.core.trigger.Schedule
    description: Scheduled daily blue-green warehouse refresh. Shipped disabled by default.
    cron: "0 2 * * *"
    disabled: true

  - id: git_dispatch_webhook
    type: io.kestra.plugin.core.trigger.Webhook
    description: Webhook trigger for automated CI/CD dispatch upon Git merge.
    key: "{{ secret('DBT_WEBHOOK_KEY') }}"

Running dbt transformations directly against production database schemas introduces substantial operational risk. If an upstream batch contains malformed records or a downstream model fails midway through dbt build, production marts are left in a half-mutated, corrupted state that immediately impacts live BI dashboards and user queries.

This blueprint orchestrates a zero-downtime blue-green schema deployment pattern for dbt Core on PostgreSQL. All models and tests are compiled and executed strictly inside an isolated staging schema. Once model materialization and assertions pass verification, Kestra executes an atomic schema swap in a single transaction block. If any step fails, the staging schema is dropped and live production tables remain completely untouched.

How it works

prepare_staging_schema (io.kestra.plugin.jdbc.postgresql.Queries) drops any previous staging schema and creates a clean, isolated schema environment.
run_dbt_staging (io.kestra.plugin.core.flow.WorkingDirectory) clones the Git repository and executes dbt deps and dbt build targeting the staging schema inside an isolated container.
verify_staging_models (io.kestra.plugin.jdbc.postgresql.Query) queries PostgreSQL catalog metadata to verify that tables were successfully materialized in staging.
evaluate_swap_gate (io.kestra.plugin.core.flow.If) checks that materialized tables exist and dry_run is false. When approved, atomic_schema_swap performs an instant transactional rename:
- Renames current live schema to a backup schema for rollback safety.
- Renames verified staging schema to live schema.
- Dispatches an audit notification via notify_swap_success.
The errors block intercepts any failure during staging execution, purges the incomplete staging schema via cleanup_staging_on_error, and notifies Slack that production was protected.

What you get

Zero downtime for downstream BI queries, Looker dashboards, and analytics consumers during long-running dbt builds.
Full immunity against partial model build failures corrupting production data marts.
Instant rollback capability with the previous live state retained in the backup schema.
Dry-run audit mode enabled by default to preview staging materialization without touching live tables.

Who it's for

Analytics Engineers running dbt Core on PostgreSQL, Amazon RDS, or Cloud SQL.
Data Platform Engineers designing reliable CI/CD and deployment gates for analytics.
Data Architects requiring zero-downtime guarantees for business-critical reporting.

Why orchestrate this with Kestra

Coordinating blue-green warehouse deployments across Git clones, containerized dbt CLI runs, multi-statement database DDL transactions, and conditional alerting requires brittle bash glue when built manually. Kestra provides declarative task sequencing, native container runners, transactional SQL execution, and dedicated failure recovery handlers in a single reproducible workflow.

Pitfalls

Active Lock Contention: ALTER SCHEMA ... RENAME requires an AccessExclusiveLock on the schema. If long-running analytical queries are executing in the live schema, the rename will queue behind them. Set statement_timeout or lock_timeout in PostgreSQL if strict cutover windows are required.
Cross-Schema References: Ensure dbt models reference other models via {{ ref(...) }} rather than hardcoding static database schema names, so models resolve dynamically to the staging target.

Prerequisites

PostgreSQL 12+ database accessible by the Kestra worker.
Database user with permissions to CREATE, DROP, and RENAME schemas.
Slack incoming webhook endpoint for promotion notifications.

Secrets

POSTGRES_PASSWORD: Password for the PostgreSQL connection user.
SLACK_WEBHOOK_URL: Webhook URL for dispatching deployment notifications.
DBT_WEBHOOK_KEY: Secret authentication key for external CI trigger dispatch.

Quick start

Configure POSTGRES_PASSWORD and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
Adjust postgres_host, postgres_db, and schema names in the flow inputs.
Execute the flow with dry_run: true to verify dbt model building in staging without mutating live tables.
Run with dry_run: false to execute the full blue-green promotion.

How to extend

Add automated schema diff checks comparing row counts between backup and new live schemas.
Add automated rollback triggers that swap the backup schema back if downstream dashboard alerts fire.
Extend the dbt CLI command to run dbt snapshot or custom analytical seed steps before promotion.

Zero-Downtime Blue-Green dbt Schema Swap on PostgreSQL

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Pitfalls

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Zero-Downtime Blue-Green dbt Schema Swap on PostgreSQL

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Pitfalls

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

NFS New File Extension Compliance Gate

Automated OpenLineage Metadata Extraction and Marquez Governance Pipeline

NocoDB Database Platform Health, Storage Engine, and Project Sentinel

Search OpenAlex for Related Scholarly Works

Detect and Resume Stuck Paused NSQ Channels with Accumulating Backlog

Write-Audit-Publish (WAP) and Atomic Merge Gate for Apache Iceberg with Project Nessie

More Infrastructure Blueprints

NetBox Drift Reconciliation with GitHub Audit Trail

Netdata Node Health and System Alarm Sentinel

Netlify Deploy Failure Sentinel

NetBird Mesh Peer Connectivity and Security Sentinel

Netskope Security Alert Volume Sentinel

Run Ansible Against NetBox Devices by Role and Site

New to Kestra?