Zero to First Audit: Governing a Databricks Unity Catalog Estate with CoreModels

This is a working session, not a tour. By the end of it you will have a governed model of a Unity Catalog schema inside CoreModels, and you will have run your first drift audit against it — without installing anything in your workspace and without handing us a single credential. Everything CoreModels learns about your lakehouse arrives as JSON files you extract yourself from the Databricks SQL editor.

The connector's vendor key is databricks, and it supports three capabilities: Import (your estate becomes a governed graph), Audit (fresh extracts are checked against that graph), and Generate (the graph emits Delta CREATE TABLE DDL back out). Today we use the first two.

What you need

• Access to the Databricks SQL editor with read rights on system.information_schema for your catalog. Only the information_schema extract is required; the other two are optional.
• A CoreModels project and its 32-character hex project id. We'll call it $PROJECT_ID.
• A CoreModels login token, $TOKEN. Import requires the Admin role on the project; the audit only needs Viewer.
• curl and jq on your machine.

We write the API base URL as https://coremodels.example.com; substitute your deployment's host.

Step 1 — Extract three files from the SQL editor

Run each query in the Databricks SQL editor and download the result as JSON. The first is required; it is the estate's shape — every table, view, and column in the schema you scope it to:

SELECT c.table_catalog, c.table_schema, c.table_name, t.table_type, t.comment AS table_comment,
       c.column_name, c.ordinal_position, c.full_data_type, c.is_nullable, c.comment
FROM system.information_schema.columns c
JOIN system.information_schema.tables t
  ON t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.table_catalog = '<YOUR_CATALOG>' AND c.table_schema = '<YOUR_SCHEMA>';
-- Download the result as JSON from the SQL editor.

Save it as information_schema.json.

The second extract is optional but worth the minute it takes: Unity Catalog's informational PRIMARY KEY and FOREIGN KEY constraints, flattened to one row per constraint column. These become uniqueness checks and governed references in the model — composite keys included:

SELECT tc.constraint_type, tc.constraint_name,
       kcu.table_catalog, kcu.table_schema, kcu.table_name, kcu.column_name,
       rc_kcu.table_catalog  AS referenced_table_catalog,
       rc_kcu.table_schema   AS referenced_table_schema,
       rc_kcu.table_name     AS referenced_table_name,
       rc_kcu.column_name    AS referenced_column_name
FROM system.information_schema.table_constraints tc
JOIN system.information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
LEFT JOIN system.information_schema.referential_constraints rc ON rc.constraint_name = tc.constraint_name
LEFT JOIN system.information_schema.key_column_usage rc_kcu ON rc_kcu.constraint_name = rc.unique_constraint_name
WHERE tc.table_catalog = '<YOUR_CATALOG>';

Save it as keys.json. The third is table lineage from the system access tables, which becomes Depends-On edges between your governed tables:

SELECT DISTINCT source_table_full_name, target_table_full_name
FROM system.access.table_lineage
WHERE target_table_full_name IS NOT NULL AND source_table_full_name IS NOT NULL;

Save it as lineage.json. That is the whole extraction: three read-only queries, three JSON downloads, no service principals, no personal access tokens, nothing shared.

Step 2 — Import

The import call sends the raw file contents as string values inside an artifacts object. jq --rawfile does the escaping for you:

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

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

A successful import answers with counts and two honesty channels:

{ "success": true, "vendor": "databricks", "projectName": "main",
  "datasetsAdded": 18, "datasetsSkippedExisting": 0, "fieldsAdded": 150,
  "lineageEdgesAdded": 12, "lineageEdgesSkipped": 0, "nodesEnriched": 168,
  "snapshotStored": true, "lossiness": [], "errors": [] }

What just happened in the graph: every table and view became a governed Type whose identity is its full catalog.schema.table name; every column became an Element with its full_data_type preserved verbatim; is_nullable = NO became a NotNull check; PK rows were grouped by constraint so composite keys are modeled honestly (members marked NotNull plus a dataset-level composite-key marker — never a fake per-column unique); FK rows became governed references to their target Types; and lineage rows became Depends-On relations, with sources outside your extract flagged as external.

Two fields deserve a second look. lossiness is the success-side honesty channel: an import can succeed and still tell you exactly what it approximated or dropped. And snapshotStored: true means CoreModels persisted the parsed estate snapshot, which is what makes later one-call re-audits possible without fresh extracts.

Import is additive. Run it again tomorrow and existing governed nodes are never mutated — detecting change is the audit's job, which is where we go next.

Step 3 — Run your first audit

The audit compares a fresh extract against the governed model. It is strictly read-only and runs at Viewer role; recordHistory: true additionally asks it to record the run in the project's rolling audit trail:

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/databricks/audit/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @audit-request.json

Because you just imported this same extract, your first audit should come back clean on coverage and drift — which is precisely the point: you have established the baseline. The response carries machine-readable counts, coded findings, and a PR-ready Markdown report. A finding looks like this:

{ "section": "Conformance", "severity": "Warning", "code": "key-column-undeclared",
  "subject": "main.sales.orders.customer_id",
  "message": "Key-shaped column has no declared PRIMARY KEY / FOREIGN KEY — Unity Catalog supports informational constraints; declare the intent." }

That is a real finding you may well see on day one: a column named customer_id on a table with no declared key constraint. Unity Catalog supports informational constraints, so the fix is to declare the intent in Databricks — and your next audit confirms it.

Step 4 — Read the result

Findings arrive in three sections:

• Coverage — what the governed model doesn't know about yet: dataset-unmapped, field-unmapped. New tables and columns land here until you import them.
• Drift — where the estate and the governed meaning disagree: dataset-removed, field-removed, field-type-drift, enum-constraint-removed, enum-narrowed, enum-widened, contract-drift. These are the findings that should stop a merge.
• Conformance — Databricks-specific best practice: table-no-comment (Info, an undocumented table or view) and key-column-undeclared (Warning, shown above).

The single number that matters for automation is errorCount: greater than zero means the extract violates governed meaning. warningCount and infoCount are advisory. The markdown field is the same report formatted for humans — paste it into a PR comment or a runbook. The fingerprint is a content hash of your artifacts, so you can tell at a glance whether two runs saw the same estate.

Because you passed recordHistory: true, this run is now the first entry in your audit trail, and the project's status badge is live:

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

Green means clean, yellow means warnings, red means errors, gray means no recorded runs. Yours should be green — or yellow with a to-do list of undeclared keys and missing comments, which is a perfectly good first day.

Where you are now

You have a governed model of your Unity Catalog schema, a recorded baseline audit, and a badge that tells the truth. From here the loop extends in three directions: wire the audit into CI so schema changes are checked on every pull request (the machine-to-machine v1 surface accepts user API keys for exactly this), call the re-audit endpoint whenever the governed model changes to check it against the last-known estate, and use Generate to emit governed Delta DDL back out of the graph. Each of those is one HTTP call on the same connector you just used.

For the condensed version of this walkthrough, see the Databricks quickstart in the CoreModels documentation.