A serious data warehouse on GitHub Actions and dbt — without an orchestrator

A few years ago we were standing up the operational layer of a Snowflake data warehouse. The team was two engineers in an IT department of single digits, and the brief was to keep the operational surface small — whatever we picked would land on the same two people to run.

The reasonable expectation was that we’d need an orchestrator: Dagster, Prefect, Airflow, pick one. The first question on the table was “which orchestrator?”

The answer turned out to be “none”. A few years in, with a hundred-plus dbt models, a half-dozen ingestion pipelines, scheduled backups, weekly clones, and a continuous data-diff against the source-of-truth Postgres, the warehouse ran on GitHub Actions and nothing else.

What we needed, written down before choosing tools

The warehouse had a small number of things to do, on a schedule:

1. On every PR — clone the production warehouse into a sandbox, run dbt build against the sandbox, post results to the PR. Block merge on test failures.
2. On merge to main — run dbt build against the production warehouse, in production hours.
3. Continuously — pull data from the operational Postgres into Snowflake (Airbyte handled this; we just monitored it).
4. Weekly — clone production into the dev environment so engineers always had fresh data without paying for the clone storage.
5. Weekly — rotate a four-database rolling backup (this week, last week, two weeks ago, three weeks ago).
6. Every two hours — run a row-level data diff between Postgres and Snowflake, push the result as a Prometheus metric.

What we did not need: real-time streaming, sub-minute latency, dynamic DAG generation, cross-region failover, multi-team access controls beyond what GitHub already did, or fine-grained task retries — the kind of things that make orchestrators valuable.

Decision: GitHub Actions was the scheduler

GitHub Actions had cron triggers, secrets retrieval, conditional steps, and a marketplace of pre-built actions for the AWS- and Snowflake-shaped tasks. The capabilities it lacked compared to a real orchestrator — a DAG visualiser, task-level dependency management, an analyst-facing UI — turned out to map onto things we already had or didn’t need: dbt’s lineage graph was the DAG visualiser, sequential YAML steps covered within-workflow dependencies, cross-workflow dependencies were few enough that we hardcoded them, and the analyst-facing surface was git push and the GitHub UI for reruns.

The argument most often made for picking an orchestrator is observability of long-running pipelines — when something fails, where in the DAG it failed, what the recovery state is, what runs were impacted. dbt provided most of that through dbt docs, the manifest.json artifact, and the test suite. GitHub Actions provided the rest: logs were searchable, failures were linkable, reruns were one-click.

The PR workflow: a Snowflake clone every time

The most useful thing the warehouse did was preview changes against real production data on every PR. The mechanism was Snowflake’s zero-copy clone: cloning a database creates a writable snapshot that shares storage with the original until something is written. The marginal cost of a per-PR clone was approximately zero.

A simplified version of the PR workflow:

name: PR — dbt build against cloned production
on:
  pull_request:
    paths: ["dbt/**"]

jobs:
  pr-dbt:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: ./.github/actions/install-python-poetry-dbt-aws
      - uses: aws-actions/aws-secretsmanager-get-secrets@v2
        with:
          secret-ids: |
            DBT_ENV_SECRET_SNOWFLAKE_PASSWORD, prod/snowflake/dbt_user
          parse-json-secrets: true
      - run: |
          poetry run dbt run-operation clone_prod_to_pr \
            --args "{pr_number: ${{ github.event.number }}}"
      - run: poetry run dbt build --target pr --threads 32
      - if: always()
        run: poetry run dbt run-operation drop_pr_clone \
            --args "{pr_number: ${{ github.event.number }}}"

The clone_prod_to_pr macro was a thin dbt wrapper around CREATE DATABASE pr_NN CLONE production. The matching cleanup macro dropped it. The clone existed for the lifetime of the PR run; if you wanted to inspect it after, you re-ran the workflow with a flag. (if: always() is best-effort — a runner that’s hard-cancelled mid-step can leave an orphan pr_NN database, so we ran a separate weekly sweep job that dropped any clone whose PR was closed. Worth knowing if you copy this pattern: the cleanup needs a backstop.)

--threads 32 against a clone obviously needed a Snowflake warehouse sized to handle that concurrency — for our PR builds we pointed at a LARGE warehouse with auto-suspend at 60 seconds; on an XSMALL you’d queue and miss the PR build SLA the team expected. Warehouse size was a config, not a code, decision; it got reviewed when the dbt project’s average build time started creeping up.

What’s hidden in there is a composite action that consolidated the boring setup — install Python, install Poetry, install Poetry dependencies (cached), install dbt, configure AWS credentials. Twenty workflows reused it, and adding a new workflow was six lines instead of fifty.

The clone macro, in detail

Refreshing the dev warehouse from production weekly was a different shape of clone — same primitive, different orchestration around it:

{% macro clone_prod_to_dev() %}
    DROP DATABASE IF EXISTS CORE_BAK;
    ALTER DATABASE CORE RENAME TO CORE_BAK;

    CREATE DATABASE CORE_REPLICA AS REPLICA OF
        AWS_EU_CENTRAL_1.PROD_ACCOUNT.CORE;
    ALTER DATABASE CORE_REPLICA REFRESH;

    CREATE DATABASE CORE CLONE CORE_REPLICA;
    DROP DATABASE CORE_REPLICA;
{% endmacro %}

A few details worth knowing if you’re copying this. The rename-to-backup happened before the drop so that, if the clone failed, the previous dev database was recoverable by renaming CORE_BAK back. The replica intermediate existed because cross-account clones in Snowflake had to go through a replica at the time (clones themselves are account-local). And there was no transaction wrapper around the macro on purpose — Snowflake doesn’t transact DDL, and trying to make the sequence atomic ends up worse than a clean failure mid-way.

The cross-account piece has aged. Snowflake’s Database Replication & Failover Groups (introduced in 2022) and the more recent cross-cloud / cross-account replication features make the explicit CREATE DATABASE … AS REPLICA OF step unnecessary for this kind of one-way refresh — a Replication Group with auto-refresh handles the cross-account hop natively, and the macro reduces to a CLONE against the locally-replicated database. A fresh build today would use that. The shape of the macro (rename-then-clone-then-drop) stays the same; the replica intermediate goes away.

The four-week rolling backup

The backup workflow was the simplest piece of the system:

{% macro backup_and_rotate() %}
    DROP DATABASE IF EXISTS CORE_BAK_4;
    ALTER DATABASE IF EXISTS CORE_BAK_3 RENAME TO CORE_BAK_4;
    ALTER DATABASE IF EXISTS CORE_BAK_2 RENAME TO CORE_BAK_3;
    ALTER DATABASE IF EXISTS CORE_BAK_1 RENAME TO CORE_BAK_2;
    CREATE DATABASE CORE_BAK_1 CLONE CORE;
{% endmacro %}

The IF EXISTS on every rename mattered: in the first three weekly runs the higher-numbered backups didn’t exist yet, and a bare ALTER DATABASE … RENAME TO would have failed. With the guard, the macro was safe from week one, and after the first month it stabilised at four backups with one rotated out per week. The storage cost was small but not zero — clones share storage with CORE while rows are unchanged, but every modified row in CORE keeps a copy alive in the oldest clone whose Time Travel pointer references it, so the bill grew roughly with weekly write volume rather than total table size. The recovery procedure for a corrupted production database was one SWAP away — ALTER DATABASE CORE SWAP WITH CORE_BAK_1 — and we used it twice, both times back online under ten minutes.

This was operational backup, not disaster recovery. If the entire Snowflake account had been lost — an unrecoverable mistake by a privileged user, an unauthorised credential rotation, a vendor outage — the clones would have gone with it. The DR layer was a separate weekly job that walked every table in CORE and COPY INTO-d it as Parquet to a stage in a different AWS account, with a Glue catalogue entry for restore-time discoverability. The export ran from the cronjob framework I wrote about in another post; restore was CREATE TABLE ... USING TEMPLATE plus COPY INTO TABLE FROM @stage, scaling roughly linearly with data volume — measured in hours for a full restore at our scale, which we considered acceptable given the failure mode it covered. The two systems had different failure modes deliberately, and we tested the DR restore once a quarter against a fresh empty Snowflake account to make sure the procedure still worked.

Authentication: AWS Secrets Manager, not GitHub Secrets

The workflows authenticated to AWS via OIDC (no static keys), and to Snowflake via username/password fetched from AWS Secrets Manager at the start of each job. We deliberately didn’t use GitHub Secrets for application credentials.

The reasons were operational. Secrets Manager had rotation, audit logging, fine-grained IAM access policies, and a single source of truth. GitHub Secrets had none of these — they were per-repo, opaque to audit, and rotation meant clicking through the UI. Centralising on Secrets Manager meant a credential leak was a one-place fix and a misconfigured secret was a one-place check.

The action that pulled secrets was aws-actions/aws-secretsmanager-get-secrets; it wrote them to environment variables for the rest of the job. The DBT_ENV_SECRET_ prefix is dbt’s convention for secrets it should redact from log output — specifically dbt’s own log output. If the workflow runs with set -x, or any step echoes the env it sees, the secret can still appear in the GitHub Actions log unmasked. Belt-and-braces practice is to call ::add-mask:: on the value at the step that fetches it, which the newer versions of the secrets action do automatically; older versions did not.

What would have forced us to leave Actions

Honest accounting. If any of the following had been true, GitHub Actions would have stopped being the right answer:

• Sub-minute scheduling. Actions runners had a 30-90 second spin-up; if we’d needed to run something every 30 seconds, that overhead would have dominated.
• A team larger than ~20 data engineers. The “everyone reads the workflow YAML” model breaks down at organisational scale; past that, you want a system with a UI and runbooks.
• Strict SLA recovery. Actions could be slow when GitHub had incidents; if the warehouse had needed a 99.95% scheduling SLA we’d have wanted our own scheduler.
• Real-time streaming pipelines. Actions is fundamentally batch; streaming wants Kafka, Flink, Dagster’s sensor model, etc.
• Multi-region disaster failover. Actions is single-region (US); we never needed cross-region orchestration.

None of these were true for us, and none of them are intrinsic to running a warehouse — they’re scale problems that show up beyond a certain size, not properties of the workload itself.

What this got right

The payoff was uniformity at the substrate level — composite actions, Secrets Manager retrieval, dbt invocation, all the same across every job. An analyst who learned one workflow had learned all of them, a bug fixed in the composite action propagated everywhere, and onboarding a new pipeline took an afternoon of writing dbt models plus ten minutes of plumbing the workflow.

For a mid-sized warehouse, the problem an orchestrator solves is rarely the problem the orchestrator vendors are advertising — dbt already handles the DAG, GitHub Actions already handles the scheduling, and Snowflake already handles the data, so the only thing left to build was the YAML between them. That turned out to be an afternoon of writing a composite action and a few macros, rather than the engineer-months it would have taken to onboard Dagster.