From INFORMATION_SCHEMA to a Governed Model: A Microsoft Fabric Walkthrough

Your warehouse already publishes a complete, machine-readable description of itself. Every Fabric Warehouse and SQL analytics endpoint answers SELECT ... FROM INFORMATION_SCHEMA.COLUMNS with the tables, the columns, the types, the nullability. Every one of them answers INFORMATION_SCHEMA.TABLE_CONSTRAINTS with the keys. That description is the whole input CoreModels needs to build a governed model of the estate and then guard it.

This walkthrough takes you from two SELECT statements to a first drift audit you can read line by line. Because the extract is ANSI T-SQL, the same recipe covers plain SQL Server: if the database answers those two queries, this tutorial applies to it unchanged.

Two things to know before you start. The connector's vendor key is fabric, and its capabilities are Import, Audit, and Generate. There is no live-connection mode, and that is a design decision we made deliberately: CoreModels never holds your Fabric or SQL Server credentials. You run the queries; you keep the connection string.

Step 1 — Extract the two artifacts

The connector takes two named artifacts. Only the first is required.

information_schema — the columns-by-tables extract. Run it against your SQL analytics endpoint or SQL Server database:

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, t.TABLE_TYPE,
       c.COLUMN_NAME, c.ORDINAL_POSITION, c.DATA_TYPE, c.IS_NULLABLE,
       c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = '<YOUR_SCHEMA>'
FOR JSON PATH;   -- or export the grid as JSON

Keep TABLE_CATALOG in the projection even though it looks redundant. The governed identity of every table is catalog.schema.table, and that identity is what later reconciliation and drift scoping match on.

keys — optional, and worth the extra thirty seconds. Flattened PRIMARY KEY and FOREIGN KEY constraint rows:

SELECT tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME,
       kcu.TABLE_CATALOG, kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME,
       kcu2.TABLE_CATALOG AS REFERENCED_TABLE_CATALOG,
       kcu2.TABLE_SCHEMA  AS REFERENCED_TABLE_SCHEMA,
       kcu2.TABLE_NAME    AS REFERENCED_TABLE_NAME,
       kcu2.COLUMN_NAME   AS REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu2 ON kcu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
FOR JSON PATH;

Without keys you still get every table, view, column, reconstructed native type and nullability. With it you also get uniqueness checks and governed references between tables, composite primary keys included.

FOR JSON PATH hands you the JSON directly. If your client struggles with long JSON results, drop that line and export the result grid as JSON instead. Either way you finish with information_schema.json and keys.json.

Step 2 — Import into a CoreModels project

You need a CoreModels project — its 32-character hex id is $PROJECT_ID below — and the Admin role on it, because import writes to the graph.

Each artifact travels as a raw string inside the request body, which is exactly what jq --rawfile produces:

jq -n --rawfile info information_schema.json --rawfile keys keys.json \
  '{artifacts: {information_schema: $info, keys: $keys}}' > import-request.json

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/fabric/import/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @import-request.json

The response is a set of counts plus two honesty channels:

{ "success": true, "vendor": "fabric", "projectName": "wh_sales",
  "datasetsAdded": 12, "datasetsSkippedExisting": 0, "fieldsAdded": 96,
  "lineageEdgesAdded": 0, "lineageEdgesSkipped": 0, "nodesEnriched": 108,
  "snapshotStored": true, "lossiness": [], "errors": [] }

Read it left to right. projectName is the warehouse name — the connector takes it from TABLE_CATALOG. datasetsAdded counts tables and views written as governed Types. fieldsAdded counts columns added to datasets that were already governed; on a first import it is zero, because brand-new datasets arrive complete with their columns. nodesEnriched counts every node that received Fabric metadata — here 12 tables plus 96 columns.

lineageEdgesAdded is honestly zero and stays zero. INFORMATION_SCHEMA carries no lineage, and the connector does not invent any.

lossiness is where a successful import confesses what it approximated or could not carry; errors is only populated when it could not proceed at all. And snapshotStored: true means the parsed estate was persisted server-side — that is what makes a one-call re-audit possible later without fresh artifacts.

Import is additive by design. Existing governed nodes are never mutated or deleted, so re-running an import is always safe. Anything that changed in the warehouse surfaces through the audit instead, where a human decides what it means.

Step 3 — Run the first audit

The audit compares fresh artifacts against the governed model. It is strictly read-only and runs at Viewer role. Setting recordHistory: true opts this run into the project's rolling drift trail, which also drives the status badge:

jq -n --rawfile info information_schema.json \
  '{artifacts: {information_schema: $info}, recordHistory: true}' > audit-request.json

curl -sS -X POST \
  "https://coremodels.example.com/graph/integrations/fabric/audit/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @audit-request.json > audit-response.json

Step 4 — Read the result

Run minutes after an import, against the same extract, the drift sections should be clean. Here is what a real first audit tends to look like:

{
  "success": true,
  "vendor": "fabric",
  "projectName": "wh_sales",
  "errorCount": 0,
  "warningCount": 2,
  "infoCount": 1,
  "codes": { "key-column-undeclared": 2, "descriptions-not-extracted": 1 },
  "driftedObjects": [],
  "fingerprint": "9f3c1a72b48d05e6",
  "metrics": {
    "Datasets (estate)": "12",
    "Datasets governed": "12 / 12",
    "Fields governed": "96 / 96",
    "Governed nodes with canonical mappings": "0 / 108 (0%)",
    "Last import": "2026-02-11T09:14:22.4180000+00:00"
  },
  "findings": [
    { "section": "Conformance", "severity": "Warning", "code": "key-column-undeclared",
      "subject": "wh_sales.dbo.orders.customer_id",
      "message": "Key-shaped column has no declared PRIMARY KEY / FOREIGN KEY — relationship intent is invisible to tools." },
    { "section": "Conformance", "severity": "Warning", "code": "key-column-undeclared",
      "subject": "wh_sales.dbo.order_items.product_id",
      "message": "Key-shaped column has no declared PRIMARY KEY / FOREIGN KEY — relationship intent is invisible to tools." },
    { "section": "Coverage", "severity": "Info", "code": "descriptions-not-extracted",
      "subject": "warehouse",
      "message": "T-SQL INFORMATION_SCHEMA carries no descriptions (extended properties are not extracted) — the governed graph is the single place this estate's meaning is documented." }
  ],
  "markdown": "# fabric Schema Audit — wh_sales\n\n🟡 **0 errors · 2 warnings · 1 info** ...",
  "historyRecorded": true,
  "lossiness": []
}

Four fields carry the weight.

errorCount is the contract signal. Above zero means the estate violates governed meaning; this is the number a CI gate tests, and nothing else needs parsing to make the pass/fail decision.

codes is the compact summary — finding code to occurrence count. Codes are stable kebab-case strings, so a pipeline can allow or escalate a specific one without string-matching messages.

driftedObjects lists the distinct subjects of Drift-section findings: exactly which tables and columns moved.

markdown is a rendered report with the verdict, the metrics table, and one collapsible block per section. Paste it into a pull request comment or a ticket unchanged.

Findings land in three sections. Coverage reports what exists in the warehouse but is not yet governed (dataset-unmapped, field-unmapped). Drift reports where estate and governed model disagree (dataset-removed, field-removed, field-type-drift, contract-drift, enum-narrowed, enum-widened, enum-constraint-removed). Conformance carries the connector's Fabric rules.

The two findings above are the ones almost every warehouse produces on day one.

key-column-undeclared fires when a table column is named like a key — id, or a name ending in _id or Id — and carries no PRIMARY KEY, FOREIGN KEY, or uniqueness declaration. It only ever fires on tables, never on views. It matters because an undeclared key is a relationship that exists only in people's heads: invisible to every tool that reads the schema, including the next engineer.

descriptions-not-extracted appears exactly once per audit, as Info, in the Coverage section. T-SQL's INFORMATION_SCHEMA carries no column or table descriptions — they live in extended properties, which this extract does not cover. Rather than nag once per table, the connector records the fact once for the estate and puts the documentation question where it belongs: in the governed graph.

Where to go from here

You now have a governed model and one recorded audit run. Two Viewer-role calls confirm the state:

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

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

status returns imported, the recorded last-import state, and governedDatasets. history returns the rolling trail of audit runs, newest first, each with its counts, its codes and the fingerprint of the artifacts it ran against — so you can see the estate drifting before anyone opens a ticket about it.

The full route reference, the CI-gate recipe and the MCP tool equivalents live in the Microsoft Fabric quickstart in the CoreModels documentation.