# Microsoft Fabric — Automation: 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.

# 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.

```yaml
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:

```bash
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

```bash
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:

```bash
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.