AGENT SKILL

retail-build-warehouse

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.

License 许可
Apache-2.0
所属仓库
Kemetra/Seshat-BI
最近更新
2026-11-11
Stars
★ 2
Forks
0

01 前言 Front Matter

name
retail-build-warehouse
description
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.

03 SKILL.md 正文

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 .sql files. That is in-scope -- writing reviewable migration text, no side effects, the same category as source-mapping authoring mappings/.
  • 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 db extra, Principle VIII). Authoring -> static seshat check -> a HUMAN applies the SQL -> only then retail-validate. Never claim the silver/gold tables exist or were validated.

Preconditions (STOP unless all hold)

  1. Canonical gate signal: mappings/<table>/readiness-status.yaml -> stages.mapping_ready.status == pass WITH a matching approvals[] entry (RS1). Its human-readable mirror mappings/<table>/unresolved-questions.md Gate 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.
  2. mappings/<table>/source-map.yaml parses and carries grain, primary_key, per column type/rename/drop/pii/gold_placement, the gold_star, derived_columns.
  3. Read assumptions.md (the business literals the YAML cannot hold: junk-filter list, CASE maps, is_return value set, sentinels, mojibake range) and source-profile.md (row counts to annotate, the date span for the calendar).
  4. Pick the next contiguous migration number NNNN over warehouse/migrations/ (S4a).
  5. Adapter checkpoint -- surface the choice before writing SQL by hand. Run seshat orchestration-assess and 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 says already_adopted, prefer the adapter over new hand-written SQL. If it says consider, state that and let the human choose -- do NOT adopt an adapter on your own, and do NOT treat consider as permission to skip authoring. If it says not_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:

  1. src CTE -- TRIM every text column (kills whitespace-variant phantom distincts).
  2. Encoding/mojibake whitelist on display columns (e.g. regexp_replace(... keep chr 32-126 + the domain's needed ranges ...)), sourced from assumptions.md.
  3. filtered CTE -- junk-row filters BEFORE ''->NULL (WHERE col NOT IN (..., '')). MUST come before step 4 -- once '' becomes NULL, IN ('') stops matching.
  4. ''->NULL via NULLIF(trim(x), '') (folded into the casts below).
  5. Casts via NULLIF(trim(x), '')::type -- money/qty numeric, dates date, leading-zero ids stay TEXT, ordinals smallint (RC7).
  6. Numeric-based row filters on the CAST value, not text ('0.0' <> '0').
  7. Derived columns -- is_return from the AUTHORITATIVE column (never the measure sign), business rollups with ELSE 'UNMAPPED'.
  8. Sentinel UPDATEs after the SELECT -- fill grouping-dim text NULLs with the assumptions.md sentinel; 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 = -1 in 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 the dim_date bullet 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. 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). Leave the date FK bare and let NOT NULL reject the unmatched row.
  • Transaction ids with no attributes -> degenerate dims on the fact, not a dim.
  • dim_date = contiguous generate_series(from, to, '1 day') over the map's date span -- NEVER SELECT 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 -1 member breaks refresh and time-intelligence even though the SQL succeeds and every other check stays green.
  • FK constraints + FK indexes AFTER load (so -1 members 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 its approvals[] entry is absent), or the Gate status: CLEARED mirror does not agree, or any open row remains.
  • A column's silver_type / missing_policy / gold_placement is missing or ambiguous -> never guess a type or invent a placement.
  • The junk-filter NOT IN (...) values are not pinned in assumptions.md.
  • The authoritative returns column / the is_return value 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_date span is absent -> never hardcode a guessed calendar range.
  • A pii: true column is not decision: drop.
  • A dim max()/GROUP BY 1: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)

  1. 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).
  2. 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).
  3. Print the apply command and the sequence, then STOP: psql "$DATABASE_URL" -f warehouse/migrations/NNNN_create_silver_<table>.sql then the gold file, in numeric order. Then retail 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-orchestrate skill.
  • 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 filled mappings/retail_store_sales/).
  • The live half (after a human applies): the retail-validate skill.

04 同仓库更多 Skills

Kemetra
Kemetra/Seshat-BI

fake-skill

A fixture skill with declaring frontmatter, used as positive shipped evidence.

★ 2
Kemetra
Kemetra/Seshat-BI

fake-skill

A fixture skill with declaring frontmatter, used as positive shipped evidence.

★ 2
Kemetra
Kemetra/Seshat-BI

bare-skill-no-frontmatter

暂无简介

★ 2
Kemetra
Kemetra/Seshat-BI

fake-skill

A fixture skill with declaring frontmatter, used as positive shipped evidence.

★ 2
Kemetra
Kemetra/Seshat-BI

retail-kpi-knowledge

Retail KPI / metric-contract reasoning layer for BI agents in the Seshat BI project. Use when an agent must reason about the BUSINESS MEANING of a retail KPI before any implementation — defining a KPI in business terms, classifying additivity (fully/semi/non-additive), identifying grain, listing required source fields, resolving ambiguity (gross vs net, VAT, returns, cost method, same-store), choosing an MVP KPI pack, reviewing a metric contract for completeness, or preparing a clean handoff to an implementation layer. Also use it to classify KPI sufficiency or prepare an owner policy decision packet. This is a meaning/definition + review layer, not an executor: it does not write DAX/SQL/Python, grant readiness, approve policy, or design dashboards. See INDEX.md for live/planned routes; registry.yaml is the authoritative machine-readable inventory.

★ 2
Kemetra
Kemetra/Seshat-BI

bi-sql-knowledge

SQL reasoning, validation, reconciliation, transformation, diagnostics, and anti-pattern knowledge layer for Seshat BI agents. Use when a task involves reasoning about source SQL: source profiling, table grain, keys & uniqueness, joins & fan-out amplification, aggregation correctness, COUNT/NULL semantics, deduplication, validation queries, reconciliation queries, silver/gold transformation logic, window/date-time analytics, SQL anti-patterns, or basic SQL performance reasoning, or interpretation of a supplied sanitized PostgreSQL JSON execution plan. The SQL counterpart to bi-dax-knowledge. It is a thinking & validation layer, not a database executor, runtime validator, dbt/Dagster project, or automatic tuning service.

★ 2