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.

And that’s the cookbook. You came into Course 4 knowing how to declare a schema and quench it. You’re leaving knowing how to drive — to put your intent on the object and let the tools read it instead of guessing. Every recipe was the same idea wearing different clothes: gate a deploy on the environment, make a policy enforce itself, ask the live server what it needs, carry assets that travel, generate the objects that derive from others, catch what a destructive change would drop, author the hook that owns that catch — and now, make the schema the one true home for everything you know about your data. Declare it once, where it’s true, and let the rest follow.

That’s the whole trade. Not scripting every strike by hand, but shaping the metal so it holds its own edge — a schema that carries its intent, its policy, and its meaning, and a set of tools that read all three and do the work. Put the truth on the object. The forge takes care of the rest.

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