# Databricks — Deep dive: Anatomy of an Import: How a Unity Catalog Estate Becomes a Governed Graph Take a single row of a Unity Catalog extract: # Anatomy of an Import: How a Unity Catalog Estate Becomes a Governed Graph Take a single row of a Unity Catalog extract: ```json { "table_catalog": "main", "table_schema": "sales", "table_name": "orders", "table_type": "MANAGED", "table_comment": "Customer orders.", "column_name": "order_total", "ordinal_position": 4, "full_data_type": "DECIMAL(10,2)", "is_nullable": "NO", "comment": "Grand total." } ``` By the time a CoreModels import finishes, that row is part of a governed Type with vendor identity `main.sales.orders`, an Element `order_total` whose governed primitive is Double, a NotNull check, two descriptions, and a metadata record that still reads `DECIMAL(10,2)` - character for character. This article is the map of everything in between: what lands where in the graph, how native types are translated, what the connector's audit rules check, and the places where the mapping is deliberately, explicitly lossy. ## The shape of the pipeline The Databricks connector never writes to the graph itself. It parses the three artifacts - `information_schema` (required), `keys`, `lineage` - into a vendor-neutral estate snapshot: datasets with fields and normalized checks, plus lineage edges. The shared integration layer then writes in two passes. Schema shape - types, fields, references, controlled values - flows through the same hardened schema writer every CoreModels import uses. Estate facts that governed schema deliberately does not hold - native types, check details, materializations, lineage, import state - ride the graph's extensibility instead: a vendor metadata mixin, a dedicated lineage relation group, and a state node. The payoff: vendor quirks never leak into governed meaning, and governed meaning never depends on vendor quirks. ## Identity and datasets A dataset's vendor identity is its three-part name, `catalog.schema.table`, and the estate as a whole is registered under the catalog's name. That identity is written onto the Type as a queryable mapping - standard `databricks`, URI `main.sales.orders` - which is what later audits, status reads, and cross-vendor reconciliation resolve against. The Type's label stays the bare table name; the full name also rides along as the physical name, which DDL generation later prefers. Unity Catalog's `table_type` maps to a materialization vocabulary: | `table_type` | Materialization | Governed kind | |---|---|---| | `MANAGED` (and anything unrecognized) | `table` | Table | | `EXTERNAL` | `external_table` | Table | | `VIEW` | `view` | View | | `MATERIALIZED_VIEW` | `materialized_view` | View | Table comments become Type descriptions; column comments become Element descriptions. Rows are processed in `ordinal_position` order so element ordering mirrors the lakehouse, and a duplicated column name within one table keeps its first occurrence. Tables-versus-views totals are recorded as facts on the import state, so status calls can report the estate's composition without re-reading it. ## Columns and the type mapping Every column carries two type facts, kept deliberately separate. The **native type** - `full_data_type` exactly as Unity Catalog reported it - is preserved verbatim in vendor metadata. The **governed primitive** comes from a family-based mapping shared by our warehouse connectors, with an explicit approximation flag whenever precision, length, or structure is lost: | Unity Catalog type | Governed primitive | Approximated? | |---|---|---| | `INT`, `BIGINT`, `SMALLINT`, `TINYINT` | Integer | no | | `DECIMAL(p,0)` | Integer | yes - the precision bound is dropped | | `DECIMAL(p,s)`, s > 0 | Double | yes - exact decimal to binary double | | `FLOAT`, `DOUBLE` | Double | no | | `BOOLEAN` | Boolean | no | | `DATE`, `TIMESTAMP` | DateTime | no | | `STRING` | String | no | | `VARCHAR(n)`, `CHAR(n)` | String | yes - the declared length is dropped | | `ARRAY<…>`, `MAP<…>`, `STRUCT<…>`, `BINARY`, anything else | String | yes - no structural home | | missing type | String | yes - a declared guess | The two-track design is why nothing is lost in practice: the governed primitive is what the graph reasons in and what every other format export inherits, while the verbatim native type is what drift audits compare and what generation writes back out - a `DECIMAL(10,2)` column round-trips as `DECIMAL(10,2)`, not as the Double it was governed as. ## Checks, keys, and the composite-key rule `is_nullable = "NO"` becomes a NotNull check - the only check the required artifact yields on its own. The optional `keys` artifact is where constraint semantics come from, and it is parsed with one rule we consider non-negotiable: **primary-key rows are grouped by constraint name before any uniqueness is asserted.** A single-column primary key marks its column NotNull and Unique. A composite key marks every member NotNull and adds a dataset-level marker naming the member columns - it never labels individual members as unique, because individually they are not. Modeling composite keys as per-column uniqueness is a quiet lie many schema tools tell; the audit would then miss real duplicate risks. Foreign-key rows become governed references: the element points at the target Type (resolved by its `catalog.schema.table` identity, with the referenced column recorded), plus a relationship check for bookkeeping. These references are what generation later turns back into `FOREIGN KEY … REFERENCES` clauses, and what gives agents join paths to reason over. ## Lineage Each `system.access.table_lineage` row becomes one edge in the integration's Depends-On relation group, and direction matters: the edge runs from the *produced* table to the tables it reads - `orders_summary` depends on `orders`. Pairs are deduplicated, and an edge is flagged external when either end is not part of the current extract, so a table fed from another catalog shows up honestly as depending on something outside the governed estate rather than silently dropping the fact. ## What rides the vendor metadata mixin Beside every imported node sits one value of the `Databricks Unity Catalog Metadata` mixin - the estate-bookkeeping channel, with properties namespaced by the vendor key: the raw native type (`databricksDataType`), the materialization, the physical relation name, a JSON summary of the node's checks (`databricksChecks`), and the vendor-side description. Two properties do double duty downstream: generation reads NOT NULL from the checks JSON (governed schema intentionally has no schema-level nullable - that constraint lives at the estate boundary and is declared as lossiness there), and it reads the native type in preference to any primitive fallback. Unlike governed nodes, mixin values are refreshed on re-import. That is the additive contract stated precisely: governed meaning is never mutated by an import, while estate bookkeeping always reflects the latest extract. A state node completes the picture, recording the last import's timestamps, versions, counts, facts, and a fingerprint: the first sixteen hex characters of a SHA-256 over the concatenated artifacts, the same value audit reports and history entries carry, so "did anything change?" is a string comparison. The parsed snapshot itself is also persisted (size-capped; more below) to power artifact-free re-audits. ## The audit rules Audits report in three sections. Coverage (`dataset-unmapped`, `field-unmapped`) and drift (`dataset-removed`, `field-removed`, `field-type-drift`, `enum-constraint-removed`, `enum-narrowed`, `enum-widened`, `contract-drift`) are shared across all connectors - a retyped column produces, for example: ```json { "section": "Drift", "severity": "Error", "code": "field-type-drift", "subject": "main.sales.orders.order_total", "message": "Field type changed since the last import.", "detail": "governed: DECIMAL(10,2), estate: STRING" } ``` Read the `detail` carefully: type drift is judged on the *native* type strings - the value recorded in the metadata mixin at import versus the `full_data_type` in the fresh extract - compared case- and whitespace-insensitively, so `decimal(10, 2)` and `DECIMAL(10,2)` are the same type. That is stricter than comparing governed primitives, which would shrug at `DECIMAL(10,2)` widening to `DECIMAL(18,4)`: both map to Double, but only one is the number your reports were reconciled against. On top of these, the Databricks connector contributes two conformance rules of its own: | Code | Severity | Fires when | |---|---|---| | `table-no-comment` | Info | a table or view has no comment - undocumented assets resist governance and agent grounding | | `key-column-undeclared` | Warning | a *table* column named `id` or `*_id` has no declared PK/FK and no uniqueness or relationship check | The second rule is deliberately narrow: it skips views, and it goes quiet the moment you declare the informational constraint in Unity Catalog - which is exactly the behavior you want from a nudge. ## Generation: the round trip Generate emits one artifact, `coremodels_delta_tables.sql`. For the estate above: ```sql CREATE TABLE IF NOT EXISTS main.sales.orders ( order_id BIGINT NOT NULL COMMENT 'Key.', customer_id BIGINT NOT NULL, status STRING COMMENT 'Allowed values: draft, active, closed.', order_total DECIMAL(10,2) NOT NULL COMMENT 'Grand total.', CONSTRAINT pk_orders PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES main.sales.customers ) USING DELTA COMMENT 'Customer orders.'; ``` Everything traces back to the graph: column types are the recorded native types (primitive fallbacks only for governed-first columns that never came from an import), NOT NULL comes from the checks JSON, the primary key comes from the governed uniqueness check, the foreign key from the governed reference, and the allowed-values comment from a governed taxonomy. String literals are escaped backslash-aware, because Databricks SQL treats backslash as an escape inside single-quoted strings. ## Where the mapping is honest about its limits - **Views are not generated.** They are derived objects; generation skips them with a declared lossiness record rather than emitting DDL that pretends to define them. - **One informational PRIMARY KEY per table, no UNIQUE constraints.** Unity Catalog has no enforced UNIQUE, so only the first governed key column becomes the PK constraint - informational, as UC defines it. - **Taxonomies ride comments.** Allowed values appear on column COMMENTs; there is no enforced CHECK constraint to compile them into. - **Cross-schema foreign keys narrow.** The documented `keys` query is catalog-scoped while the required extract is schema-scoped, so an FK can point at a table outside the snapshot. The column is then written as a plain value rather than a governed reference, with a SemanticNarrowing record naming the target - extract both schemas together if you want the reference governed. - **Malformed optional artifacts degrade, not fail.** A `keys` or `lineage` file that is not valid JSON is ignored with a lossiness record; only an unusable `information_schema` fails the import - including the honest edge cases "the extract contains no rows" and rows carrying no `table_name` at all. - **The snapshot has a cap.** Past roughly 1.5 MB encoded, import reports `snapshotStored: false` with a lossiness record; fresh-artifact audits still work, but artifact-free re-audit has nothing stored to run against. - **There is no live connection.** CoreModels never holds Databricks credentials; live sync is a declared-but-deferred capability, and every byte the connector sees came from an extract you ran yourself. Every one of those limits is reported in-band - as lossiness on a successful call, never as a silent omission. That is the property we optimize this connector for: not that the mapping is perfect, but that it is precisely, inspectably imperfect. The extraction queries and the API calls that exercise all of this are in the Databricks quickstart in the CoreModels documentation.