Keep a data dictionary in sync with the schema: check drift, push docs down

Every SQL warehouse and database that matters, Postgres, Snowflake, BigQuery, Redshift, Databricks SQL, exposes an INFORMATION_SCHEMA (or an equivalent catalog) listing tables, columns, and data types as queryable views. It cannot be stale: it is the schema, published as data. Any dictionary you maintain elsewhere is a projection of these views plus one human column, the description.

That framing tells you exactly what can drift and what cannot. Names and types drift the moment someone runs an ALTER the dictionary does not know about. Descriptions cannot drift in the same way, but they can orphan: a description for a column that no longer exists, or a live column with no description at all. Both failure modes are detectable with one query.

If your dictionary is loadable as a table (a seed file, a synced sheet, a catalog export), a FULL OUTER JOIN against the catalog view yields the complete drift report: columns that exist in the schema but not the dictionary, and dictionary rows describing columns that are gone. Run it on a schedule and fail loudly on rows, exactly like a test.

-- Drift report: undocumented columns and stale dictionary rows.
WITH live AS (
  SELECT table_name, column_name
  FROM information_schema.columns
  WHERE table_schema = 'analytics'
),
dict AS (
  SELECT table_name, column_name
  FROM analytics.data_dictionary
)
SELECT
  COALESCE(l.table_name,  d.table_name)  AS table_name,
  COALESCE(l.column_name, d.column_name) AS column_name,
  CASE
    WHEN d.column_name IS NULL
      THEN 'undocumented: live column missing from dictionary'
    WHEN l.column_name IS NULL
      THEN 'stale: dictionary row for a dropped column'
  END AS drift
FROM live l
FULL OUTER JOIN dict d
  ON  l.table_name  = d.table_name
  AND l.column_name = d.column_name
WHERE l.column_name IS NULL OR d.column_name IS NULL;

The stronger fix removes the separate dictionary entirely. dbt already holds model and column descriptions in its YAML; the persist_docs config pushes them into the warehouse on every run, as relation and column comments on Snowflake and as descriptions on BigQuery. The docs then live where the schema lives, versioned with the transformations that create the columns, and every catalog tool that reads warehouse metadata sees them.

Without dbt, the same result comes from plain DDL: COMMENT ON TABLE and COMMENT ON COLUMN statements checked into the same migrations that alter the schema. A migration that adds a column without its comment is visible in review, which is precisely the coupling a spreadsheet can never give you.

# dbt_project.yml: push YAML descriptions into the warehouse
models:
  my_project:
    +persist_docs:
      relation: true
      columns: true

A dictionary answers "what is this column"; it cannot answer "how do these tables relate". That is a diagram, and it deserves the same source-coupled treatment. Datadef accepts pasted DDL or dbt-style definitions as a prompt, so the ER view of your marts is generated from the schema rather than drawn beside it, and an agent connected to the Datadef MCP server can update it when models change. Shared public, the diagram embeds by URL in the wiki page next to the dictionary and follows edits within minutes. The Snowflake-specific version of this loop is in living diagram from Snowflake.

No tool writes the descriptions. persist_docs moves them, the drift query finds the gaps, but a human still has to know that amount_cents excludes tax and say so. Budget for that; the automation only guarantees the writing happens once, in one place.

On the Datadef side: it diagrams the schema and its relationships, it is not a data catalog and does not manage column-level prose. For catalog features, lineage-aware search and ownership, look at the tools in data catalog best practices. The diagram regeneration is agent- or CI-invoked, never automatic, and the live embed requires a public project.

• Keeping data documentation in sync
• Keep database schema docs in sync
• A living diagram from Snowflake
• Data catalog best practices