Wiring a Snowflake Drift Gate: CI, the Badge, and the Rolling Trail

Schema drift has two directions, and most teams only guard one. The warehouse can move away from the agreed model — a column retyped in a Friday migration, a table quietly dropped. But the model can also move away from the warehouse — someone tightens an allowed-value list in governance and nobody re-checks the estate. This article wires up automation for both directions against a Snowflake estate governed in CoreModels: a CI gate on the machine-to-machine API, a status badge, the rolling audit history, one-call re-audits, and the scheduled heartbeat that keeps the loop honest between changes.

The one number your pipeline needs

Everything below rests on a single contract. The machine-to-machine audit route:

POST https://coremodels.example.com/v1/{PROJECT_ID}/integrations/snowflake/audit

accepts user API keys (a JWT — the interactive graph/integrations/... surface uses your login token instead), runs read-only at Viewer role, and returns the audit report inside the standard ApiResponse envelope, so the counts live under data.*. The gate semantics are deliberately blunt: data.errorCount > 0 means the change violates governed meaning — fail the build. Warnings and infos are advisory tiers you can escalate later; errors are drift against the governed model (removed datasets or fields, type drift, narrowed enums, contract drift) and they stop the merge.

A Viewer-scoped API key is all the pipeline needs, because the audit never writes. Store it as a secret; nothing in this setup requires giving CI anything stronger.

The CI job

Snowflake's artifact is a metadata extract, not a build output, so the job has two halves: produce (or pick up) the extract, then audit it. Two workable patterns for the first half: re-extract the schema in a prior step using your existing Snowflake tooling, or — simpler and fully credential-free in CI — require schema-change pull requests to commit the refreshed information_schema.json alongside the migration. The extract is one documented query in Snowsight over INFORMATION_SCHEMA.COLUMNS joined to TABLES.

The gate itself is curl and jq:

- name: CoreModels Schema Audit
  env:
    COREMODELS_API_URL: ${{ vars.COREMODELS_API_URL }}
    COREMODELS_API_KEY: ${{ secrets.COREMODELS_API_KEY }}
    COREMODELS_PROJECT_ID: ${{ vars.COREMODELS_PROJECT_ID }}
  run: |
    set -euo pipefail
    jq -n --rawfile info information_schema.json \
      '{artifacts: {information_schema: $info}, recordHistory: true}' > audit-request.json
    curl -sS -o audit-response.json -X POST \
      "$COREMODELS_API_URL/v1/$COREMODELS_PROJECT_ID/integrations/snowflake/audit" \
      -H "Authorization: Bearer $COREMODELS_API_KEY" -H "Content-Type: application/json" \
      --data-binary @audit-request.json
    test "$(jq -r '.success' audit-response.json)" = "true"
    jq -r '.data.markdown' audit-response.json >> "$GITHUB_STEP_SUMMARY"
    test "$(jq -r '.data.errorCount' audit-response.json)" -eq 0

Read the three test/jq lines as three distinct checks. First, .success must be true — a false there means the audit could not run at all (unusable artifact, unknown vendor), which is a broken pipeline, not clean drift, and it should fail loudly rather than pass silently. Second, the data.markdown field is the PR-comment-ready human report; appending it to the step summary means a failing build explains itself without anyone re-running anything. Third, the actual gate on data.errorCount.

For teams already using our GitHub Action for dbt gating: the composite action's artifact inputs are manifest-shaped, so for Snowflake the curl form above is the straightforward recipe — same endpoint, same envelope, same semantics.

recordHistory: true and why the gate should set it

The audit verb is strictly read-only by default; recording a run into the project's rolling audit history is opt-in bookkeeping. In CI you want it on. Every gated run then lands in the trail with trigger ci, its counts, its per-code totals, and the artifact content fingerprint — which gives you two things later: a drift timeline you can actually read, and the ability to correlate "when did field-type-drift first appear" with a specific pipeline run.

The trail is read back from the interactive surface:

curl -sS "https://coremodels.example.com/graph/integrations/snowflake/history/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN"

Each run record carries at, trigger (audit, reaudit, ci, or scheduled), the three counts, the codes map, and the fingerprint. Identical fingerprints across runs tell you the estate didn't change between them — useful when deciding whether a new warning came from the warehouse or from a governance edit.

The badge

The latest recorded run also renders as a shields-style SVG on the same API-key surface:

curl -sS -H "Authorization: Bearer $COREMODELS_API_KEY" \
  "https://coremodels.example.com/v1/$COREMODELS_PROJECT_ID/integrations/snowflake/badge" \
  -o snowflake-audit.svg

Green means the latest run was clean, yellow warnings only, red errors, gray no recorded runs yet. Because the route needs the API key, the practical README pattern is to fetch the SVG in a scheduled job and publish the file wherever your README can reference it — the badge is self-contained, so it embeds anywhere an image does. Note the gray state is informative in itself: a project whose badge is gray has never recorded a run, which usually means recordHistory was never turned on.

The other direction: reaudit

The CI gate catches the warehouse moving. When the governed model moves — a type corrected, a taxonomy narrowed, a description rewritten — you don't have a fresh extract in hand, and you shouldn't need one. At import time, CoreModels stores the parsed estate snapshot; the reaudit verb replays the same audit engine over that stored snapshot against the current governed model:

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/snowflake/reaudit/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" -d '{}'

No artifacts, no Snowflake credentials, no vendor API calls — one POST with an empty body. Reaudit always records its run (trigger reaudit); it exists to extend the trail, so recording isn't optional there. Two operational notes: reaudit lives on the interactive surface only — the v1 API-key surface carries exactly audit and badge — and it depends on the stored snapshot. Very large estates that exceeded the snapshot storage cap (around 1.5 MB encoded) will have reported snapshotStored: false at import with a lossiness record saying so; for those, fresh-artifact audits still work but reaudit has nothing to replay.

A cheap habit that pays for itself: run reaudit from a hook or pipeline whenever governance changes merge, exactly the way the schema gate runs when warehouse changes merge. Then both directions of drift produce history entries, and the trail becomes a genuine record of the relationship between model and estate rather than a log of one side's changes.

The heartbeat

Gates fire on changes. Drift, though, sometimes arrives without a pull request — a hotfix applied straight in the warehouse, a governance edit made out of band. For that, deployments can enable a scheduled server-side re-audit: a background worker, off by default and switched on per deployment through the Integrations:ScheduledReaudit configuration, that periodically re-audits opted-in projects against their stored snapshots and records the runs into the history (trigger scheduled). The effect is that the badge and the trail stay truthful even during weeks when nobody pushes anything: if a governance change last Tuesday broke conformance with the last-known estate, the heartbeat's next run turns the badge red without waiting for the next PR to stumble over it.

The loop, assembled

Put together, the automation is four small pieces with one shared engine: the CI gate audits fresh extracts on every schema PR and fails on errorCount > 0; recordHistory feeds every gated run into the rolling trail; reaudit (manual or heartbeat-scheduled) checks the stored estate whenever the model moves; and the badge broadcasts the latest recorded verdict to anyone who looks. None of these steps can write to the governed model — the entire loop observes and reports, and changing meaning stays where it belongs, with people.

The Snowflake quickstart in our docs carries the extraction queries, the full route reference for both HTTP surfaces, and the same CI snippet ready to paste.