Build a data dictionary from schema metadata

Somewhere in your org there’s a spreadsheet that’s supposed to say which columns hold sensitive data — who owns them, how long you keep them, what’s PII. It was accurate the day someone made it. It hasn’t been right since. Every schema change since then quietly drifted away from it, and nobody noticed until an audit asked.

The problem isn’t the spreadsheet. It’s that the metadata lives away from the schema it describes. So it rots. Let’s put it back where it belongs — on the definition itself — and let the schema keep the record honest.

The lever: Extensions is a store, not just a switch

You’ve used Extensions all through this course as input — a flag a gate reads, a value a default pulls in. But Extensions accepts any JSON you want, at every level of the model: tables, columns, indexes, keys. That makes it an authoritative metadata store. Hang the business facts right on the object:

{
  "Schema": "dbo", "Name": "Customer",
  "Extensions": { "BusinessDomain": "Identity", "DataOwner": "identity-team", "RetentionPolicy": "7y" },
  "Columns": [
    { "Name": "Email", "DataType": "NVARCHAR(256)", "Nullable": false,
      "Extensions": { "BusinessName": "Email Address", "SensitivityLevel": "PII", "DataSteward": "privacy-office" } }
  ]
}

Now the sensitivity of Email lives on Email, in the same file, under the same review, in the same commit as the column itself. It can’t drift from the column, because it is the column’s definition.

The recipe: a dictionary that can’t drift

Metadata on the objects is good; metadata you can query is better. So read the whole template’s model — every table, every column, all their Extensions — and build a table from it. The whole-model token hands you the entire template as a JSON array; shred it, and MERGE a DataDictionary:

MERGE dbo.DataDictionary AS tgt
USING (
    SELECT t.SchemaName, t.TableName, t.BusinessDomain, t.DataOwner,
           c.ColumnName, c.SensitivityLevel, c.DataSteward
    FROM OPENJSON(@json) WITH (
             SchemaName     NVARCHAR(128) '$.Schema',
             TableName      NVARCHAR(128) '$.Name',
             BusinessDomain NVARCHAR(128) '$.Extensions.BusinessDomain',
             DataOwner      NVARCHAR(128) '$.Extensions.DataOwner',
             Columns        NVARCHAR(MAX) '$.Columns' AS JSON
         ) t
    CROSS APPLY OPENJSON(t.Columns) WITH (
             ColumnName       NVARCHAR(128) '$.Name',
             SensitivityLevel NVARCHAR(64)  '$.Extensions.SensitivityLevel',
             DataSteward      NVARCHAR(128) '$.Extensions.DataSteward'
         ) c
) AS src
ON tgt.SchemaName = src.SchemaName AND tgt.TableName = src.TableName AND tgt.ColumnName = src.ColumnName
WHEN MATCHED THEN UPDATE SET tgt.SensitivityLevel = src.SensitivityLevel, tgt.DataSteward = src.DataSteward
WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...)
WHEN NOT MATCHED BY SOURCE THEN DELETE;   -- a column leaves the model, its row leaves the dictionary

@json is the whole-model token (OPENJSON on SQL Server, jsonb_array_elements on PostgreSQL, JSON_TABLE on MySQL — the shred differs, the shape doesn’t). Deploy, and “every PII or Confidential column and who stewards it” is one SELECT. Change Email to Restricted, drop a column, re-quench — the dictionary updates the changed row and deletes the dropped one. It runs on every deploy, so it’s never stale. It can’t be stale — it’s computed from the declared metadata each time.

Two notes worth carrying. First, the schema still validates its own shape, but your metadata is yours — so if you want to enforce it (every column must declare a SensitivityLevel from an approved list, or the PR fails), that’s a second-pass JSON Schema, covered in Course 6, Module 3. Author the metadata here; enforce it there. Second, an authoring gotcha: token substitution is plain text and expands even inside comments — so don’t write the whole-model token’s braces in a comment, or you’ll inline the entire JSON and break the script.

The aha: your schema knows the business

Your schema files were never just columns and types. They know who owns the data, how sensitive it is, how long it’s kept — you were just storing that somewhere else, where it rots. Put it on the object and it rides with the definition: version-controlled, reviewed, deployed, always current. Then a single script turns it into whatever you need. Here it’s a data dictionary. The same move drives replication topology, obfuscation rules, BI exposure, code generation — anything you can compute from metadata. Declare the truth once, on the thing it’s true of, and derive the rest.

Check yourself: Why can a data dictionary built from Extensions never drift from the schema, the way a separate spreadsheet does?

Because it isn’t separate. The metadata lives in Extensions on the table and column definitions themselves — same file, same commit, same review as the schema. A deploy-time script reads the whole model on every quench, shreds the Extensions, and MERGEs the dictionary — updating changed rows and deleting rows for columns that left the model. It’s computed from the declared source of truth each time, so it’s always in step with it.

So the schema becomes the one true home for everything you know about your data — not just its shape, but its meaning, its owners, its sensitivity. Declare it once, where it’s true, and let a script derive the rest on every deploy. A data dictionary is only the first thing you can build this way.

The next thing is topology. That same move — put the intent on the object, let a hook read it — drives which tables replicate, and that’s exactly where Recipe 10 takes it: mark a table in Extensions, and a deploy-time hook reads the whole model and shapes a reporting replica to match, every quench. Come see it close out the course.

Got a data dictionary you’ve been keeping by hand — a spreadsheet that’s already wrong? Email me at forgebarrett@schemasmith.com — I read every one. More’s coming from the forge.

Until then, may your schemas carry every truth worth keeping, and everything you need flow from the one you declare.

— Forge

Mark this lesson complete