source-mapping

The source-mapping gate is the kit's one load-bearing rule (constitution Principle IV): before any silver.* SQL is written, the source MUST be profiled and mapped into committed, reviewed artifacts. This skill runs that gate: profile -> author the five artifacts -> stop at judgment calls -> hard-stop before silver. It formalizes Phases 1-4 of the medallion playbook; the playbook stays authoritative on HOW to decide, the templates on WHAT to record.

Scope boundary (read first)

This skill profiles a source and authors the mapping artifacts, then STOPS. It does NOT write silver.* or gold.* SQL, does NOT call pbi-cli or Power BI Desktop, and does NOT decide the judgment calls Principle V reserves for a human. Silver is downstream of an APPROVED map -- approval is the reviewer's action, not this skill's.

The five artifacts (write the blanks with seshat scaffold-source <table>)

Run seshat scaffold-source <table> to materialize the canonical blanks into mappings/<table>/. Do NOT hand-author these files, and do NOT copy from a templates/ path: templates/ exists only in the Seshat development repo, so that instruction fails for anyone who installed the kit as a tool -- and a hand-written source-map.yaml silently misses the canonical shape that seshat validate and the gold stages require (issue #488). The scaffolder ships inside the package and never overwrites an existing file.

Per ADR 0003 (mapping-artifact location), a table's filled set lives in mappings/<table>/:

1. source-profile.md -- Phase 1 numbers (this skill fills the mechanical ones).
2. source-map.yaml -- the machine-readable spine (grain+PK first, per-column keep/drop/rename/type/PII/gold-placement, the gold star, derived columns).
3. assumptions.md -- which RC1-RC16 defaults were ADOPTED vs DEVIATED (each deviation cites its triggering data fact).
4. unresolved-questions.md -- the build-blocking judgment calls + who answers.
5. reconciliation-report.md -- the blank the later live run fills (RC16).

Cleaning chain (canonical order -- the HYBRID walk)

A fixed, dependency-ordered chain. The shape is hybrid: order-sensitive steps run as GLOBAL passes, intra-column steps run PER-SURVIVOR (each kept column resolved in one bundle), cross-row steps run as a GLOBAL tail. The agent recommends a default at every step; the human decides (Principle V). NONE advance to silver -- the gate (D) is the stop.

Per-step loop: present the step (purpose + default + its behavior on the table) -> human chooses keep / enhance / change the STEP -> apply -> next. The walk both cleans the table AND lets the pipeline itself be refined.

A. GLOBAL-FIRST (decide across ALL columns):

1. Keep / Drop (Phase 2.1 / RC3) -- FIRST. One global pass over the inventory.
2. Grain & PK (Phase 2.0 / RC1, RC2) -- VALIDATES that the key survived step 1; ERROR (or surface) if a needed key column was dropped. May choose a GENERATED SURROGATE key over the natural grain (first-class option); if so, keep the natural key SILVER-ONLY for the uniqueness/dedup proof and expose only the surrogate to gold (a bare surrogate is unique by construction and cannot detect a double-load).
3. PII (Phase 2.2 / RC4) -- EARLY. A flagged NAME is NOT auto-PII: distinguish individual/patient data (sensitive -> drop default) from STAFF names (KPI dimensions) and COMPANY names (B2B org) -- the latter two are legitimate non-sensitive attributes.

B. PER-SURVIVOR (one kept column at a time, decided TOGETHER): 4. Rename (2.3) + Type (2.5 / RC7) + Missing-value (2.4 / RC5,RC6) + Gold placement. Missing-value is decided HERE, per column (no global ''->NULL baseline), and ONLY for columns whose profiled missing_count > 0 (skip 0-blank columns). A present-but-wrong value (e.g. a site code in a customer id) is a VALUE REMAP in a derived column, NOT a missing-value sentinel.

C. GLOBAL-TAIL (cross-row): 5. Row filters (2.6) -- which rows to drop (junk/zero-value); state the count and reconcile (source - dropped = silver, accounting for OVERLAP). ORDERING (load-bearing): a filter that targets BLANKS must evaluate PRE-sentinel (before step B's sentinel substitution), else trim(col)='' matches 0 rows and those rows wrongly survive. 6. Derive (2.7-2.8 / RC8, RC11, RC12) -- is_return from the AUTHORITATIVE column; value remaps; surrogate keys (generate the fact SK AFTER filters so it numbers surviving rows); analyst-supplied rollups; flat hierarchy. The date dim carries NO -1/unknown/sentinel member (so Power BI can mark it a date table, S8); an unmatched fact date fails via Date_SK NOT NULL.

D. GATE (hard stop; Principle IV): 7. Named-human review -- recording the judgment-call ANSWERS is NOT map approval. No silver.* SQL until the full map is reviewed.

Naming: warehouse SQL identifiers are snake_case (product_id, gross_sales, sale_sk) -- rule S1 flags quoted non-snake_case columns (a quoted mixed-case name forces a case-sensitive Postgres column, a footgun). PascalCase is a Power BI model-layer display choice (rename in the semantic model), NEVER a SQL identifier.

Per-column vs global is by DEPENDENCY, not preference: global when a decision is table-wide (PII policy) or cross-row (filters); per-column when it depends only on that column's own keep decision. See the medallion playbook, Phase 2, for the rationale.

Procedure

1. Locate

Confirm the bronze schema.table exists. Ask the analyst/agent for the candidate PK column(s) to test (grain is decided FIRST, Phase 2.0 / RC1).

2. Profile (mechanical) -- via seshat profile

Run the mechanical profiler over a read-only connection and record the numbers into mappings/<table>/source-profile.md. Use the shipped CLI verb (the internal seshat.profile.profile() API is not reachable on a pip/pipx install -- #400):

# needs the `db` extra + a DSN (DATABASE_URL / ANALYTICS_DB_* / --dsn)
seshat profile --table bronze.<table> --pk <pk_a>,<pk_b>            # markdown to paste
seshat profile --table bronze.<table> --pk <pk_a>,<pk_b> --format json

The output gives: row_count, column_count, per-column missing_count / missing_pct (measured ''OR NULL, NEVER IS NULL alone -- the load-bearing trap, RC5) / distinct_cardinality, and the candidate-PK proof (total, distinct_pk, null_pk, is_unique). The markdown form is already shaped for the source-profile.md Shape / Per-column / Candidate-PK sections; paste each into the source-profile table and the Candidate grain & PK section.

3. Profile (semantic) -- PROPOSE, do not invent (Principle V)

The semantic rows -- code<->label 1:1 rate, dimension fan-out (id -> name), hierarchy multi-parent, the AUTHORITATIVE returns column, money-relationship identities, cross-file drift -- need the table's MEANING. profile.py does NOT compute these. PROPOSE each from the data + column names, then raise it as an unresolved-questions.md entry for human confirmation. Never invent a business rollup, a PII ruling, or the returns column.

4. Author the map and assumptions

Starting from the RC1-RC16 defaults, fill source-map.yaml (grain+PK first, then per-column decisions, the gold star, derived columns) and assumptions.md (adopted vs deviated, each deviation citing its triggering data fact). Keep all text ASCII, snake_case silver names, short paths (Windows 260 limit).

5. Stop-and-ask (Principle V)

Raise unresolved-questions.md entries, each with a who-must-answer owner, for: business-rollup mapping (analyst supplies the full value->group table), PII publish-safety (governance sign-off; default drop), grain ambiguity (candidate PK not unique on the data), sentinel-vs-null choice, and any build-blocking question.

6. GATE -- hard stop (Principle IV)

Emit the reconciliation-report.md blank and STOP. Before stopping, guarantee the decision ledger exists even when Step 5 raised zero questions -- run seshat mapping-mirror --table <table>. It writes a stub only if unresolved-questions.md is absent (status derived from the committed readiness-status.yaml, never invented) and never overwrites the ledger; the downstream dbt and Dagster gates hard-require this file. State plainly: no silver.* SQL may be written until the map is reviewed and approved. Hand the filled set to the reviewer; do not proceed to silver.

Deferred/live-boundary mode (no DSN or no db extra)

If resolve_dsn(...) returns None or psycopg2 is not installed, do NOT traceback and do NOT pretend a profile ran. The live boundary is deferred BY DESIGN: credentials + the optional db extra are user-supplied under constitution Principle VIII. In this mode:

• Report the boundary and print the exact enable steps: pipx inject seshat-bi psycopg2-binary (or pip install "seshat-bi[db]"), then set DATABASE_URL (or the ANALYTICS_DB_* vars) in the gitignored .env. Never commit a real DSN.
• STAY USEFUL: run seshat scaffold-source <table> to write the five canonical blanks into mappings/<table>/ (it needs no DB), fill their STRUCTURE, mark the mechanical profile numbers [PENDING LIVE PROFILE], still drive the semantic stop-and-ask (Step 3) and the gate (Step 6).

See also

• Gate + principles: the kit architecture note, Sec 5; constitution Principles IV, V.
• Method / defaults: the medallion playbook; the retail cleaning-defaults decision record (RC1-RC16).
• Live half (after silver/gold exist): the retail-validate skill.
• A filled instance: a filled worked example (an example, never the universal schema).

Orchestration

When a table is being driven end-to-end, the retail-orchestrate conductor skill sequences this verb with the others and runs the self-heal loop against the gate exit code. This skill stays single-purpose: it does its job and STOPS. The loop (run gate -> classify findings -> auto-fix mechanical / HARD-STOP judgment calls -> re-run) lives ONLY in retail-orchestrate, never here.