retail-build-warehouse
Skill2 starsChanged 4 months agoApache-2.0View on GitHub
Author the silver + gold migration SQL for a mapped retail table from its APPROVED source-map, then STOP before executing. Use after a table's mapping gate is CLEARED in the Seshat BI repo and someone asks to build the silver/gold SQL, write the migration, or fill the retail-orchestrate build seam. Author-and-stop: it writes warehouse/migrations/*.sql FILES only, never connects to a database, never applies the SQL, and HARD-STOPS at any judgment call the map does not already answer.
- Deletes or force-pushes
In this repository60 other files it gives its agents.
- AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- approval-consoleSkill
- approval-evidence-packSkill
- business-knowledge-interviewSkill
- capabilitiesSkill
- consumer-data-dictionarySkill
- cross-table-lineageSkill
- dagster-orchestration-adapterSkill
- dashboard-designSkill
- dashboard-intelligenceSkill
- dbt-transformation-adapterSkill
- evidence-pack-generatorSkill
- first-hour-compassSkill
- friendly-pr-reviewerSkill
- grain-confidence-reviewerSkill
- kpi-contract-builderSkill
- pbi-mcp-write-adapterSkill
- pbip-workflowSkill
- pbip-xraySkill
- pbir-authoring-adapterSkill
- portfolio-watchSkill
- powerbi-dashboard-designSkill
- pr-readiness-reviewerSkill
- readiness-viewerSkill
- release-notes-generatorSkill
- report-intent-interviewSkill
- retail-control-roomSkill
- retail-discover-portfolioSkill
- retail-governSkill
- retail-initSkill
- retail-onboard-tableSkill
- retail-orchestrateSkill
- retail-scaffoldSkill
- retail-semantic-checkSkill
- retail-validateSkill
- run-next-readinessSkill
- seshat-studioSkill
- showcase-buildSkill
- source-mappingSkill
- speckit-analyzeSkill
- speckit-checklistSkill
- speckit-clarifySkill
- speckit-constitutionSkill
- speckit-git-commitSkill
- speckit-git-featureSkill
- speckit-git-initializeSkill
- speckit-git-remoteSkill
- speckit-git-validateSkill
- speckit-implementSkill
- speckit-planSkill
- speckit-specifySkill
- speckit-tasksSkill
- speckit-taskstoissuesSkill
- bi-analyst-knowledgeSkill
- bi-bigdata-knowledgeSkill
- bi-dax-knowledgeSkill
- bi-python-knowledgeSkill
- bi-sql-knowledgeSkill
- retail-kpi-knowledgeSkill
The file
We split this skill into the rules it gives an agent
6 rules. About 0 are common, 0 are distinctive, and 6 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈0
Distinctive · estimate
This author's own choices. Worth a look.
6
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 30
You NEVER execute: Do not open a DB connection, do not run the migration, do not run the Phase-5 PK dry-run or the Phase-6 orphan/reconcile …
- Very specific: line 46
Read assumptions.md (the business literals the YAML cannot hold: junk-filter list, CASE maps, is_return value set, sentinels, mojibake …
- Very specific: line 80
Sentinel UPDATEs: after the SELECT -- fill grouping-dim text NULLs with the assumptions.md sentinel.
- Very specific: line 103
NEVER COALESCE(dd.date_sk, -1): a real out-of-span date would be silently bucketed to Unknown while the coverage checks stay green (RC19).
- Very specific: line 118
STOP and raise an unresolved-questions.md row (do not guess) if:
- Very specific: line 136
Run seshat check (static, local, no DB) -- the authored SQL must exit 0.
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
retail-build-warehouse
Turns an APPROVED mappings/<table>/source-map.yaml into the two migration .sql
files -- silver (typed/cleaned flat table) then gold (Kimball star) -- in the
order the medallion playbook proves load-bearing. It fills the silver/gold
[SEAM] the retail-orchestrate conductor parks at.
The agent is the runtime: this is a procedure you follow, authoring SQL by adapting
the proven warehouse/migrations/0003/0004 shape. There is NO codegen engine, NO
.sql.tmpl template, and NO retail build CLI -- by design (the source-map cannot
carry the transform logic; an engine would emit only boilerplate). That is a
recorded design decision, not an omission to be fixed.
Author vs execute (read first)
- You AUTHOR
.sqlfiles. That is in-scope -- writing reviewable migration text, no side effects, the same category assource-mappingauthoringmappings/. - You NEVER execute. Do not open a DB connection, do not run the migration, do
not run the Phase-5 PK dry-run or the Phase-6 orphan/reconcile checks -- those need
live data and are the deferred DB-write seam (creds + the
dbextra, Principle VIII). Authoring -> staticseshat check-> a HUMAN applies the SQL -> only thenretail-validate. Never claim the silver/gold tables exist or were validated.
Preconditions (STOP unless all hold)
- Canonical gate signal:
mappings/<table>/readiness-status.yaml->stages.mapping_ready.status == passWITH a matchingapprovals[]entry (RS1). Its human-readable mirrormappings/<table>/unresolved-questions.mdGate status: CLEARED(ZERO open rows) MUST agree. A missing readiness-status file,mapping_ready != pass, or a mismatch between the two -> STOP (Principle IV). Never self-grant. mappings/<table>/source-map.yamlparses and carries grain, primary_key, per column type/rename/drop/pii/gold_placement, the gold_star, derived_columns.- Read
assumptions.md(the business literals the YAML cannot hold: junk-filter list, CASE maps, is_return value set, sentinels, mojibake range) andsource-profile.md(row counts to annotate, the date span for the calendar). - Pick the next contiguous migration number
NNNNoverwarehouse/migrations/(S4a). - Adapter checkpoint -- surface the choice before writing SQL by hand. Run
seshat orchestration-assessand REPORT its verdict to the human before authoring migrations. This repo ships two transformation/orchestration adapters (dbt, Dagster); hand-written migrations are the default path, so without this step they get bypassed silently rather than declined on purpose (issue #489). The assessor is read-only: it emits a categorical verdict (consider/not_recommended/already_adopted), never a numeric score, and it never adopts anything. If it saysalready_adopted, prefer the adapter over new hand-written SQL. If it saysconsider, state that and let the human choose -- do NOT adopt an adapter on your own, and do NOT treatconsideras permission to skip authoring. If it saysnot_recommended, proceed here and say so.
Silver: the load-bearing Phase-5 order (NUMBERED -- do NOT reorder)
Author warehouse/migrations/NNNN_create_silver_<table>.sql. The order is
load-bearing; a reordering passes seshat check (the gate is order-blind) yet is
WRONG. Transcribe in this exact sequence, adapting the proven 0003:
srcCTE -- TRIM every text column (kills whitespace-variant phantom distincts).- Encoding/mojibake whitelist on display columns (e.g.
regexp_replace(... keep chr 32-126 + the domain's needed ranges ...)), sourced fromassumptions.md. filteredCTE -- junk-row filters BEFORE''->NULL (WHERE col NOT IN (..., '')). MUST come before step 4 -- once''becomes NULL,IN ('')stops matching.''->NULL viaNULLIF(trim(x), '')(folded into the casts below).- Casts via
NULLIF(trim(x), '')::type-- money/qtynumeric, datesdate, leading-zero ids stayTEXT, ordinalssmallint(RC7). - Numeric-based row filters on the CAST value, not text (
'0.0' <> '0'). - Derived columns -- is_return from the AUTHORITATIVE column (never the measure
sign), business rollups with
ELSE 'UNMAPPED'. - Sentinel UPDATEs after the SELECT -- fill grouping-dim text NULLs with the
assumptions.mdsentinel; leave fact NULLs as NULL.
Wrap: SET client_encoding TO 'UTF8'; BEGIN; CREATE SCHEMA IF NOT EXISTS silver;
DROP TABLE IF EXISTS silver.<t>; CREATE TABLE silver.<t> AS WITH ... SELECT ...;
then the sentinel UPDATEs, then ALTER TABLE ... ADD PRIMARY KEY (...), then
CREATE INDEX ..., then COMMIT;. Bare DROP+CREATE in one txn (S4b layer-aware
allows this for derived silver/gold). UTF-8 no BOM. Write the file in ONE Write call.
Flag ADD PRIMARY KEY as UNVERIFIED-UNTIL-APPLIED: PK uniqueness is only
provable on transformed data (the live dry-run), which is the deferred seam.
Gold: the Kimball star (Phase 6)
Author warehouse/migrations/NNNN+1_create_gold_<table>_star.sql, adapting 0004:
- One fact at silver grain + one conformed dim per business entity.
- Surrogate
_sk(GENERATED BY DEFAULT AS IDENTITY); keep natural keys as attrs. - Unknown member at
_sk = -1in every ENTITY dim (INSERT ... OVERRIDING SYSTEM VALUE VALUES (-1, ...)), then real rows collapsed 1:1 by natural key (GROUP BY). The DATE dim is the EXCEPTION -- see thedim_datebullet below; S6 (ERROR) requires the member on entity dims, S8 (ERROR) FORBIDS it on the date dim, so "every dim" without the carve-out authors SQL the gate rejects. - Fact FK columns
COALESCE(d.x_sk, -1)via LEFT JOIN -- for ENTITY dims only. NEVERCOALESCE(dd.date_sk, -1): a real out-of-span date would be silently bucketed to Unknown while the coverage checks stay green (RC19). Leave the date FK bare and letNOT NULLreject the unmatched row. - Transaction ids with no attributes -> degenerate dims on the fact, not a dim.
dim_date= contiguousgenerate_series(from, to, '1 day')over the map's date span -- NEVERSELECT DISTINCT date(S7; missing days break time-intel). It carries NO-1/NULL unknown member (S8, ERROR): it becomes a marked date table (dataCategory: Time), which Power BI validates as unique/contiguous/no- nulls, so a-1member breaks refresh and time-intelligence even though the SQL succeeds and every other check stays green.- FK constraints + FK indexes AFTER load (so
-1members and rows exist first). - Idempotent: drop fact before dims (FK order), recreate all in one txn.
Judgment stops (fail LOUD -- never satisfy with a silent default)
STOP and raise an unresolved-questions.md row (do not guess) if:
stages.mapping_ready.status!=pass(or itsapprovals[]entry is absent), or theGate status: CLEAREDmirror does not agree, or any open row remains.- A column's
silver_type/missing_policy/gold_placementis missing or ambiguous -> never guess a type or invent a placement. - The junk-filter
NOT IN (...)values are not pinned inassumptions.md. - The authoritative returns column / the
is_returnvalue list is absent -> never derive is_return from a measure sign (RC8). - A categorical/billing CASE map or business rollup is incomplete -> only
ELSE 'UNMAPPED'is an allowed sentinel; never invent an arm. - A sentinel is requested but its 0-collision safety was not verified (RC6).
- The
dim_datespan is absent -> never hardcode a guessed calendar range. - A
pii: truecolumn is notdecision: drop. - A dim
max()/GROUP BY1:1 collapse where the map did not confirm id->attribute is 1:1 (max() silently picks one value if it is actually 1:many).
After authoring (the handoff)
- Run
seshat check(static, local, no DB) -- the authored SQL must exit 0. The SQL family is S1, S2, S3, S4a, S4b, S5, S6, S7, S8 (there is no bare "S4", and S8 is real -- do not cite the family as a "S1-S7" range). - Self-review the order: diff your silver SQL against the numbered checklist
above. The checker is order-blind; this manual step is the only catch for a
reordering bug (e.g.
''->NULL before the junk filter). - Print the apply command and the sequence, then STOP:
psql "$DATABASE_URL" -f warehouse/migrations/NNNN_create_silver_<table>.sqlthen the gold file, in numeric order. Thenretail validate --source-map mappings/<table>/source-map.yaml.
seshat check exit 0 is NECESSARY, not SUFFICIENT. It proves form (snake_case,
schemas, idempotent shape, -1 member present, generate_series used). It does NOT
prove correctness -- right row count, no sentinel collision, complete enums, penny
reconciliation. That is proven ONLY by the live retail-validate after a human
applies the SQL. Say this in your handoff; do not let green read as "correct".
Orchestration
retail-orchestrate invokes this skill at its silver/gold build phase (the [SEAM]
it previously only parked at). This skill still STOPS at the execute boundary -- the
conductor does not apply SQL either; a human applies, then the conductor resumes at
retail-validate. The self-heal loop lives in retail-orchestrate, not here.
See also
- The conductor: the
retail-orchestrateskill. - The method + skeletons: the medallion playbook, Phase 5/6 + Appendix A/B.
- The proven reference:
warehouse/migrations/0003_create_silver_retail_store_sales.sql,0004_create_gold_retail_store_sales_star.sql. - The approved input:
mappings/<table>/(e.g. the filledmappings/retail_store_sales/). - The live half (after a human applies): the
retail-validateskill.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

