Data Dictionary

Data Dictionary (DD-1 … DD-18)

When an application consumes data, "what does this field mean, where does it come from, and which code touches it" is the question this skill answers. It builds a data dictionary by self-contextualizing from whatever inputs exist — code, documentation, direct user statements — and, when a database is reachable, confirming everything against the live data itself.

Source of truth for the deterministic machinery — SQLite introspection, ~100-row sampling, grain/field inference, the fixed provenance vocabulary, corroboration, the reference/relational map builders, and the serializer — is scripts/data_dictionary/data_dictionary.py (stdlib-only, unit-tested). This skill is the contract + the LLM-judgment workflow; that module is the machine. Do not re-implement the deterministic pieces in prose — call the module.

The standard artifact (DD-7, DD-16)

The skill always writes ONE standard-named pair so that initialization can check whether it already exists:

• docs/DATA_DICTIONARY_MAP.md — the human view (last_built frontmatter).
• docs/data-dictionary.json — the machine sidecar (schema: data-dictionary/v1).

The artifact contains: the DB name (when known), the schema name (when inferable), every table with its inferred grain (and, when a live engine exposes usage statistics, a measured usage block — R4 below), every field with a definition + type + provenance + confidence + corroboration, an extensive by-field / by-table reference map (which code calls which tables/fields), and a relational / blend map — including non-DB relations expressed only in code (e.g. census data merged onto individuals on zip code).

Sequencing (DD-6, DD-16)

Run AFTER codebase mapping and integration mapping: map the codebase → map the integrations → build the data dictionary for the data the application consumes. During initial codebase exploration (Phase −1 of the pipeline), check whether docs/DATA_DICTIONARY_MAP.md already exists and is fresh before rebuilding (same freshness discipline as the other *_MAP.md artifacts).

Workflow

Step 1 — Branch by input type (DD-2)

• Code present → Step 2 (code analysis).
• Documentation present → Step 3 (doc analysis).
• Direct user input (the user states definitions) → seed the field list with those definitions as provenance direct-user-input (still corroborated in Step 4). Any combination is valid — run every applicable step and merge.

Step 2 — Code analysis (DD-3, DD-4)

Find table references by searching the code iteratively and recursively: follow objects that mask/wrap database connections (ORM models, query builders, repository/DAO layers, connection factories) until you reach where queries or data-access calls are actually generated — that is where the data model lives. When the code alone is insufficient, read the code itself, README files, and inline comments to infer which fields and tables are pulled (provenance direct-code-comment for anything a comment states). Record every site as a code reference {file, line, table, field} — these become the by-field/by-table reference map via build_reference_map(...). Capture code-only joins (e.g. a merge on zip) as code_joins for the relational/blend map.

Step 3 — Documentation analysis (DD-5)

Examine provided documentation, documentation found in the codebase, and user-provided inputs for field information. Map any definitions found onto the current field list, extending the list where new fields are discovered.

Step 4 — Field definition: two passes + corroboration (DD-8 … DD-14)

1. First pass — provided context (DD-8). Start from any directly supplied definitions (user input / code comments). Pass these to the engine as provided_defs ({"table.field": {definition, provenance, claims_key?, expected_type?}}).
2. Second pass — live inspection (DD-9, DD-10). When provided context is exhausted AND a database is reachable, connect and read the schemas/tables; sample roughly the first 100 records per table and examine the data row-wise. The engine's SQLite adapter (build_from_sqlite) does this end-to-end; other engines follow the same shape with their own driver + credentials.
3. Dimensionality / grain inference (DD-11). infer_grain(...) derives the grain (one-row-per-what) from the declared PK + sampled uniqueness (e.g. a "Customers" table: hypothesize customer-level, read the columns, conclude the true grain — customer vs. customer/store-location level).
4. Field inference (DD-12). infer_field(...) reasons from name + declared type + sampled values (e.g. address1/2/3 → address lines; zip5 whose values are all 5-digit → "5-digit ZIP code"; *_id → identifier). Surface the evidence used.
5. Provenance (DD-13). Every field stores a sourcing indicator from the FIXED vocabulary — direct-user-input, direct-code-comment, inference, live-data — defined once in PROVENANCE_TYPES. Extend it only in the engine.
6. Corroboration (DD-14). EVERY provided definition is verified against the real data, not only key claims. corroborate_definition(...) checks each provided_defs entry: a claims_key entry routes to corroborate_key_claim(...) (the classic conflict — the user says a table keys on customer_id, but the data shows it keys on a hash/name, so the dictionary reflects the REAL field), and an expected_type entry is checked against the sampled values' actual dtype/format (e.g. "this column is a boolean flag" on a free-text column is flagged). Any conflict surfaces (⚠) and downgrades confidence to low.

Step 5 — Persist (DD-15)

write_artifact(...) writes the standard pair into docs/. If MemPalace is available, mine the artifact into it per mempalace-integration (mempalace --palace <palace> mine docs/DATA_DICTIONARY_MAP.md --wing <wing>), and include it in the documentation set. MemPalace-absent → the on-disk artifact is the deliverable; persistence is best-effort.

Step 6 — Usage-grounded importance (R4, optional)

Where a live engine exposes usage statistics, importance is measured, not guessed. build_from_sqlite(...) accepts an optional injected usage_stats adapter — a UsageStatsSource whose table_usage(table) returns {read_recency, write_recency, row_volume} for a table, or None when it has no stats. A non-None result attaches a per-table usage block at provenance live-data (it IS measured); a None OMITS the block entirely — never zero-filled. An unmeasured table and an idle table are not the same thing, and a fabricated zero would rank a live table as dead — so absent stats are silent, not zero.

The engine shapes are described, not bundled (the plugin ships no DB drivers): SQL Server reads recency from sys.dm_db_index_usage_stats and row volume from sys.dm_db_partition_stats; Postgres reads pg_stat_user_tables. SQLite has no such catalog, so SqliteUsageStats honestly returns None for every table (the block is omitted). A dictionary built with no usage source is byte-unchanged. The provenance vocabulary is UNCHANGED — usage rides the existing live-data.

Offline stakeholder review round-trip (R5)

The fields that most need a human — confidence == "low" OR a corroboration conflict (⚠) — are sent to a stakeholder as a CSV, edited offline, and read back through the corroboration gate (a human claim is corroborated like any other, never transcribed).

• generate-review (data_dictionary.py generate-review --dictionary <json> --out <csv>) writes a CSV (stdlib csv) of those fields, columns table, field, current_definition, confidence, conflict, usage_volume, proposed_definition (the last blank for the stakeholder). Rows sort by R4 usage.row_volume (descending; fields without usage last). Every row is redacted through scripts/helpdesk/logit.py::redact_evidence — the reused privacy engine, never re-implemented — at the default summary level, which drops the sample-derived conflict detail (the ⚠ fact survives); --privacy full keeps the detail.
• apply-review (data_dictionary.py apply-review --dictionary <json> --review <csv> [--db <sqlite>]) reads the edited CSV and takes each non-blank proposed_definition as a direct-user-input correction on its exact table.field, routed through corroborate_definition against the table's sampled rows. A correction that conflicts with the data is flagged ⚠ and confidence-downgraded, not accepted as truth; with no reachable rows the correction is applied but stays honestly uncorroborated (needs_corroboration). The CSV round-trips by the fixed column names, so a returned correction lands on the field it was written from — no column drift.

JSON-LD export (R6)

The dictionary + lineage sidecars are machine-complete, so they can be emitted as JSON-LD for the external catalog / lineage ecosystems Stage 6 of data-engineering-exploration names (OpenLineage / Marquez / DataHub / dbt). The emitter is scripts/data_dictionary/jsonld_export.py — stdlib-only (json; NO rdflib, NO pyld). JSON-LD is just JSON with an @context.

• export_dictionary_jsonld maps each dictionary table to a schema:Dataset (a dcat:Catalog wraps them); each field becomes a schema:variableMeasured schema:PropertyValue carrying schema:propertyID (the field), schema:description (the definition), and ct6:provenance / ct6:confidence — definition, provenance, and confidence preserved verbatim. A table's measured usage block (R4) rides its Dataset node as ct6:usage (a table-level measure attached once, never duplicated per field).
• export_lineage_jsonld maps the lineage graph to PROV-O: a data_asset node → prov:Entity, a function / endpoint node → prov:Activity, a reads edge → the activity prov:used the entity, a writes edge → the entity prov:wasGeneratedBy the activity; calls / serves / serves_route / modifies / originates ride documented prov / ct6 relations. Only edges present in the graph are emitted; an unknown edge kind is skipped-with-note, never invented (the node/edge vocabulary is single-sourced from hooks/lineage_graph.py).
• export_combined emits one JSON-LD document (@context binding the real vocabulary URLs + @graph). validate_jsonld checks a resolvable @context, every node an @id + @type, unique @ids, and referential closure; a round-trip (roundtrip) proves no loss / no fabrication. The export-jsonld CLI writes the document (an absent sidecar → an empty but valid @graph, never a fabricated triple).

Honest boundary. The emitter is verified against the schema.org / DCAT / PROV-O shapes — structural validation + round-trip. No live external-consumer ingestion (OpenLineage / Marquez / DataHub) is verified, and none is wired. A downstream consumer, when one is adopted, points at this output; the emitter emits only what the sidecars contain (no fabricated triples, provenance preserved).

Maintenance discipline (DD-17, DD-18)

Whenever an agent does database development — any operation that adds/modifies tables, data types, column names, or relations — it MUST update DATA_DICTIONARY_MAP.md in the same change (rebuild the affected tables via the engine, re-corroborate). And every agent verifies, before claiming done, that its documentation — including the data dictionary — is up to date. This is the data counterpart of the existing documentation-currency discipline.

Honest boundary — live inspection needs a reachable DB (DD-9/DD-10)

Live schema read + 100-row sampling require a database that is actually reachable with valid credentials. When no database is reachable, build with build_from_inputs(...) (the no-DB path) from code + docs + provided context only: every field is marked inference / direct-* (never live-data), and the artifact's live_inspection block records ran: false with the reason — the serializer renders this as a "Live inspection: NOT run" line so the artifact itself states that live inspection did not run. Do NOT fabricate sampled data or claim live-data provenance for a database that was never connected — an un-inspected field is honestly inference, not a guess dressed as fact. Likewise an inferred key seen in only a handful of sampled rows (< MIN_KEY_SAMPLE) is hedged in the grain string and never asserted above medium confidence — N distinct values in N rows is not proof of a key.

Cross-references

• scripts/data_dictionary/data_dictionary.py — the deterministic engine (the machine).
• scripts/data_dictionary/jsonld_export.py — the stdlib JSON-LD emitter (R6): the sidecars as schema.org/DCAT + PROV-O (shapes validated + round-trip; no live consumer wired).
• skills/data-lineage-mapping — the CDLG asset-lineage layer (functions ↔ data assets); complementary: this skill defines the FIELDS, that one traces who reads/writes them.
• skills/intake-and-mapping — Phase −1 sequencing (codebase → integration → data dictionary).
• skills/mempalace-integration — the persistence hook (DD-15).
• skills/documentation-currency — the sibling currency discipline the maintenance rule (DD-17/18) mirrors.