Anatomy of an Import: How a Snowflake Estate Becomes a Governed Graph

Take one column — ORDER_TOTAL NUMBER(38,2) in ANALYTICS.PUBLIC.ORDERS — and follow it from an INFORMATION_SCHEMA row to a governed graph node. That single trace touches everything interesting about the Snowflake connector in CoreModels: how identity is minted, how native types map (and where they approximate), what rides the vendor-metadata mixin, how keys and dependencies become checks and lineage, which audit rules are Snowflake's own, and where the connector honestly declines to guess. This article is that trace, written for people who want to know exactly what the import does before pointing it at production metadata.

Parsing: tolerant on shape, strict on meaning

The connector consumes three JSON extracts — information_schema (required), keys, object_dependencies — produced by documented Snowsight queries. Parsing is tolerant by construction: each artifact may be a bare row array, a single-cell ARRAY_AGG(OBJECT_CONSTRUCT(*)) export, or a {"rows": [...]} wrapper, and row keys match case-insensitively because Snowflake uppercases identifiers. Tolerance has limits, stated rather than fudged: an information_schema payload that isn't valid JSON, contains no rows, or carries no TABLE_NAME values fails with a structured error (the last case answers "not an INFORMATION_SCHEMA extract?"). The optional artifacts fail softer — an unparseable keys or object_dependencies payload is ignored with a lossiness record saying so, never an exception.

One subtle case the parser handles for you: the documented recipes encourage concatenating extract runs, and concatenation (or case-only-distinct quoted identifiers) can produce duplicate column rows. The first row wins; every duplicate becomes a StructuralDrop lossiness record naming the exact column, so nothing disappears without a paper trail.

Identity and structure

Every table or view becomes a governed Type whose vendor identity is the fully qualified DB.SCHEMA.TABLE name — ANALYTICS.PUBLIC.ORDERS for our column's table. That identity is written onto the node as a queryable mapsTo value (standard snowflake, URI = the vendor id), which is what later audits, status reads, and cross-estate reconciliation match against. The node's label stays the native name; the dataset's kind (Table or View) and materialization (table, view, materialized_view) come from TABLE_TYPE; COMMENTs become descriptions on the way in.

Columns become Elements on their Type, ordered by ORDINAL_POSITION. The parser reconstructs the parameterized native type from the extract's parts before anything else happens: DATA_TYPE = NUMBER plus precision 38 and scale 2 becomes the string NUMBER(38,2); a VARCHAR with CHARACTER_MAXIMUM_LENGTH = 256 becomes VARCHAR(256); a text type without a length becomes TEXT. The whole vendor-side project is fingerprinted too — a SHA-256 over the concatenated artifact payloads, truncated to sixteen hex characters — which is the value audit history entries later carry so you can tell identical estates apart from changed ones at a glance.

Type mapping: family-based, honest about loss

Native types map to CoreModels' intermediate representation through a shared warehouse type map, family-based on the base type name, with an explicit approximated flag whenever precision, length, or structure is dropped:

Snowflake type	Governed primitive	Approximated?
NUMBER(38,0), INT, BIGINT, SMALLINT, TINYINT, BYTEINT	Integer	parameterized forms: yes (precision bound dropped)
NUMBER(38,2) and any scale > 0	Double	yes — exact decimal to binary double
FLOAT, FLOAT4, FLOAT8, DOUBLE, REAL	Double	no
BOOLEAN	Boolean	no
DATE, DATETIME, TIMESTAMP_NTZ, TIMESTAMP_TZ, TIMESTAMP_LTZ	DateTime	no
TIME	String	yes — time-of-day has no IR slot
TEXT, STRING	String	no
VARCHAR(n), CHAR(n)	String	yes when a length was declared (length dropped)
VARIANT, OBJECT, ARRAY, GEOGRAPHY, anything unrecognized	String	yes — no structural home

Scale is the pivot for the NUMBER family: scale zero is integer-shaped, anything else is a decimal and decimals approximate. So our ORDER_TOTAL NUMBER(38,2) lands as a governed Double with a recorded approximation — and this is where the design earns its keep: the approximation never destroys the original. The exact string NUMBER(38,2) survives in the Snowflake Metadata mixin on the element, alongside the recorded checks and description (materialization and the physical name ride the same mixin on the dataset's Type). The governed primitive is for cross-format reasoning; the mixin is for round-tripping. When DDL is later generated, the native type from the mixin wins over the IR default, so the column comes back out as NUMBER(38,2), not FLOAT.

Keys become checks — with a composite-key subtlety

IS_NULLABLE = NO becomes a NotNull check directly. The keys artifact does the more interesting work. SHOW PRIMARY KEYS emits one row per key column, so the parser groups rows by constraint name before deciding what they mean: a single-column primary key yields NotNull plus Unique on that element, but a composite key yields NotNull on each member and a dataset-level composite_primary_key check listing the member columns — because labeling each member of a composite key as individually unique would be a false claim, and the graph shouldn't contain one.

SHOW IMPORTED KEYS rows (foreign keys) attach a relationship check to the referencing column and a reference to the target table and column, which the import writes as a governed expected-type relation between the two Types. Declared keys are informational in Snowflake — the connector treats the declaration itself as the governed fact.

Dependencies become lineage

Each ACCOUNT_USAGE.OBJECT_DEPENDENCIES row becomes an edge in the graph's Depends On relation group, deduplicated by from/to pair. When the referenced object isn't in the extract — a cross-database reference, say — the edge is flagged external in the parsed snapshot and skipped at import, counted in lineageEdgesSkipped rather than written as a graph edge: the dependency stays on record in the stored snapshot even though its target isn't governed here. Lineage is exactly the kind of estate fact that deliberately does not ride the schema-shaped path; it's written natively into the graph alongside the vendor metadata, keeping the type system clean while the operational facts stay queryable.

The audit rules Snowflake contributes

Beyond the neutral engine's coverage and drift checks (unmapped datasets and fields; removed datasets and fields, type drift, enum changes, contract drift), the Snowflake connector contributes three conformance rules with precise trigger conditions:

• semi-structured-column (Warning) — the column's base type is VARIANT, OBJECT, or ARRAY. The warehouse will happily store anything in these; the governed model can say nothing about what's inside, and the finding says so: the inner schema is invisible to consumers and agents.
• key-column-undeclared (Warning) — a column named ID or ending in _ID (case-insensitive), on a table (views are exempt), with no declared reference and no Unique or Relationship check. The naming says "key"; the metadata doesn't. Relationship intent that lives only in a naming convention is invisible to every tool downstream.
• table-no-comment (Info) — the table or view has no COMMENT. Undocumented objects resist governance and agent grounding alike.

A finding arrives fully qualified, down to the column:

{ "section": "Conformance", "severity": "Warning", "code": "key-column-undeclared",
  "subject": "ANALYTICS.PUBLIC.ORDERS.CUSTOMER_ID",
  "message": "Key-shaped column has no declared PRIMARY KEY / FOREIGN KEY — relationship intent is invisible to tools." }

Generation: the loop back out, with its own honesty

The generator emits a single coremodels_tables.sql of CREATE OR REPLACE TABLE statements. Native types recorded at import take precedence; governed-first entities without one fall back to IR defaults (NUMBER(38,0), FLOAT, BOOLEAN, TIMESTAMP_NTZ, TEXT). NOT NULL derives from recorded checks. Because a table may carry several unique columns but Snowflake allows only one PRIMARY KEY clause, the first becomes the primary key and the rest become UNIQUE constraints. Foreign keys re-emit as FOREIGN KEY ... REFERENCES declarations. And since Snowflake has no enforced CHECK constraints, governed taxonomies land where Snowflake can actually carry them — in column COMMENTs, as an "Allowed values: ..." suffix on the description. Even escaping is deliberate: backslashes are doubled before quotes, because Snowflake treats backslash as an escape inside string literals and a description ending in one would otherwise break out of its comment.

Generation also refuses two things by design. Views are skipped with declared lossiness — their definitions are SQL, not governed structure, and fabricating them would be a guess. And a governed type with no elements is skipped too, because an empty column list is a Snowflake syntax error that would break the entire shared script.

Limits, stated plainly

The connector's boundaries are documented behavior, not surprises. There is no live warehouse connection — CoreModels never holds Snowflake credentials, and a live-sync capability is declared but deferred. Very large estates can exceed the stored-snapshot cap (~1.5 MB encoded); import then reports snapshotStored: false with a lossiness record, fresh-artifact audits keep working, and only the no-artifacts re-audit path is unavailable. Streams, tasks, stages, and dynamic tables sit outside schema-governance scope for now. And the required/nullable distinction is itself declared lossiness at the graph boundary — CoreModels has no schema-level nullability, so NotNull lives in the recorded checks that generation reads back.

That's the whole journey: one column in, one governed element out, with its exact native type preserved, its checks recorded, its approximations confessed, and a DDL path that can reproduce it. The Snowflake quickstart in our docs has the extraction queries and API calls to run the trace on your own schema.