Wiring the Drift Gate: Continuous Schema Governance for Fabric and SQL Server

Schema governance that lives in a document is not governance. It is a document. The version that actually holds is the one a pipeline can fail on — a single number, checked on every change, with a trail behind it that shows whether the estate is getting better or worse.

This is the automation layer for a Microsoft Fabric warehouse or a SQL Server database under CoreModels: the CI gate, the badge, the rolling history, the re-audit, and the scheduled heartbeat. The connector key is fabric, and the same setup covers both engines because the artifact contract is ANSI T-SQL INFORMATION_SCHEMA.

The one number

The gate calls a single route on the machine-to-machine surface, where user API keys work without an interactive login:

POST https://coremodels.example.com/v1/$PROJECT_ID/integrations/fabric/audit

It is read-only and runs at Viewer role, so the key you put in your secret store needs no more than Viewer membership on the project — and a Viewer-scoped key cannot write anything. The response wraps the audit report in the standard envelope, and one field decides the build:

data.errorCount > 0  ⇒  the change violates governed meaning; fail

Error-severity findings are the ones that mean something is actually broken against the governed model: a governed dataset that vanished from the estate, a governed field that no longer exists, a column whose type moved, an accepted-value set narrowed below what the business still governs. Warnings and info findings report hygiene and coverage; they do not fail a build unless you decide they should.

Getting the artifact in CI

Unlike a transformation tool, a warehouse does not produce a build artifact you can pick up off the floor. You have two workable patterns.

Re-extract in the pipeline, running the documented queries against a staging warehouse or SQL Server instance with a tool such as sqlcmd, and audit the fresh result. Or commit the extract next to the migration that changes it, so a schema-change pull request carries both the DDL and the evidence — which has the pleasant side effect of making the schema diff visible in review.

Either way you arrive at information_schema.json, and the rest of the pipeline is identical.

A complete GitHub Actions job

One note before the YAML. The composite action we ship builds a transformation-tool-shaped request body — artifacts named manifest and catalog — so a Fabric estate wires the call explicitly instead. The step below reproduces everything that action does: job summary, per-finding annotations, and the failure decision.

name: Schema Governance

on:
  pull_request:
    paths:
      - "warehouse/**"
      - "migrations/**"

jobs:
  coremodels-audit:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - 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 }}
          EXTRACT_PATH: warehouse/information_schema.json
          KEYS_PATH: warehouse/keys.json
          FAIL_ON: error
        run: |
          set -euo pipefail

          if [ ! -f "$EXTRACT_PATH" ]; then
            echo "::error::No INFORMATION_SCHEMA extract at '$EXTRACT_PATH'."
            exit 1
          fi

          if [ -f "$KEYS_PATH" ]; then
            jq -n --rawfile info "$EXTRACT_PATH" --rawfile keys "$KEYS_PATH" \
              '{artifacts: {information_schema: $info, keys: $keys}, recordHistory: true}' \
              > audit-request.json
          else
            jq -n --rawfile info "$EXTRACT_PATH" \
              '{artifacts: {information_schema: $info}, recordHistory: true}' \
              > audit-request.json
          fi

          http_status=$(curl -sS -o audit-response.json -w "%{http_code}" \
            -X POST "$COREMODELS_API_URL/v1/$COREMODELS_PROJECT_ID/integrations/fabric/audit" \
            -H "Authorization: Bearer $COREMODELS_API_KEY" \
            -H "Content-Type: application/json" \
            --data-binary @audit-request.json)

          if [ "$http_status" != "200" ]; then
            echo "::error::CoreModels audit call failed with HTTP $http_status"
            head -c 2000 audit-response.json || true
            exit 1
          fi

          if [ "$(jq -r '.success' audit-response.json)" != "true" ]; then
            echo "::error::CoreModels audit returned an error: $(jq -r '.error.message // "unknown"' audit-response.json)"
            exit 1
          fi

          error_count=$(jq -r '.data.errorCount' audit-response.json)
          warning_count=$(jq -r '.data.warningCount' audit-response.json)
          drifted=$(jq -r '(.data.driftedObjects // []) | join(",")' audit-response.json)

          jq -r '.data.markdown' audit-response.json >> "$GITHUB_STEP_SUMMARY"

          jq -r '.data.findings[] | "\(.severity)|\(.code)|\(.subject)|\(.message)"' audit-response.json |
          while IFS='|' read -r severity code subject message; do
            case "$severity" in
              Error)   echo "::error title=$code::$subject — $message" ;;
              Warning) echo "::warning title=$code::$subject — $message" ;;
            esac
          done

          echo "CoreModels Schema Audit: $error_count errors, $warning_count warnings."
          if [ -n "$drifted" ]; then echo "Drifted objects: $drifted"; fi

          fail=0
          if [ "$error_count" -gt 0 ]; then fail=1; fi
          if [ "$FAIL_ON" = "warning" ] && [ "$warning_count" -gt 0 ]; then fail=1; fi

          if [ "$fail" = "1" ]; then
            echo "::error::This change violates governed meaning in CoreModels."
            exit 1
          fi

Three configuration values do all the work: COREMODELS_API_URL, COREMODELS_PROJECT_ID as repository variables, and COREMODELS_API_KEY as a secret. Viewer access on the project is sufficient, because the audit never writes.

Setting FAIL_ON to warning gates harder. On a Fabric estate that is a real decision rather than a formality: key-column-undeclared is a warning, and most warehouses have a backlog of undeclared keys on day one. Gate on errors first, clear the warning backlog, then tighten.

Recording the run

recordHistory: true in the request body opts the run into the project's rolling audit trail. The audit verb stays read-only by default; recording is always the caller's explicit act. Runs recorded from the API-key surface are stamped with the trigger ci, which is how you later tell pipeline runs apart from interactive ones, from re-audits, and from the scheduled heartbeat.

Each stored run is deliberately compact: timestamp, trigger, the three counts, the map of finding codes to occurrence counts, and the artifact fingerprint. The trail keeps the most recent runs per estate, up to a rolling cap of fifty; beyond that the oldest drops off. It answers "is this estate drifting over time", not "what exactly is wrong now" — the live audit answers the second question.

Reading the trend is a one-liner:

curl -sS -H "Authorization: Bearer $TOKEN" \
  "https://coremodels.example.com/graph/integrations/fabric/history/$PROJECT_ID" \
| jq -r '.projects[].runs[] | [.at, .trigger, .errorCount, .warningCount] | @tsv'

Two details matter when you build an alert on this. History and re-audit live on the interactive surface only — the API-key surface carries audit and badge. And the fingerprint field lets you tell a genuinely new estate state from the same extract audited twice: identical fingerprints mean the artifacts did not change, so any difference in counts came from the governed model moving, not the warehouse.

The badge

curl -sS -H "Authorization: Bearer $TOKEN" \
  "https://coremodels.example.com/v1/$PROJECT_ID/integrations/fabric/badge" \
  -o fabric-audit.svg

The badge renders from the latest recorded run: green when clean, yellow when only warnings, red carrying the error count, gray when nothing has been recorded yet. It is a self-contained shields-style SVG with no external references.

The endpoint is authenticated, so it expects the Authorization header rather than an anonymous image fetch. In practice that means fetching it in the pipeline and publishing the SVG wherever your README or dashboard points — which also makes the badge reflect the last run your pipeline actually recorded.

The other direction: re-audit

The CI gate answers "do these fresh artifacts still conform to the governed model?" There is a second drift question that no pull request will ever ask: the estate stood still, and the model moved. Someone narrowed a taxonomy, someone renamed a governed field, someone deprecated a dataset.

Re-audit runs the same engine over the estate snapshot stored at import time against the current governed model. No artifacts, no credentials, no warehouse connection:

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

Re-audit always records its run, stamped reaudit. The natural place for it is a job that fires after a governance change — the model-side counterpart to the schema-side gate.

One prerequisite is worth stating plainly: this depends on the snapshot the import stored. If an import reported snapshotStored: false because the encoded estate exceeded the storage cap, re-audit has nothing to replay. Fresh-artifact audits still work; only the artifact-free path is unavailable.

The heartbeat

For estates where nobody is going to click anything, a server-side recurring re-audit exists behind configuration. It is off by default and opt-in per project: it walks the stored estate snapshots for each opted-in project, re-audits every vendor whose connector supports auditing, and appends each run to the history with the trigger scheduled.

That is what turns the trail into a heartbeat. A pull request produces ci runs when engineers change the warehouse. A governance change produces a reaudit run when the model moves. The scheduled job produces a scheduled run when nothing at all happened — which is exactly when silent drift is most likely to go unnoticed, and exactly when a red badge is most useful.

Assembled, the loop has no gaps: every schema change is gated, every model change is re-checked, every quiet week still gets a verdict, and every verdict is one number a machine can act on.

The extract this pipeline feeds on comes from the two queries in the Microsoft Fabric quickstart.