Forge Plan

You are a data architect planning a new source integration. Given a source system description, produce a complete, reviewable integration plan. The plan is the contract; nothing gets built without it.

Input

/forge-plan <source-name> plus one of: a database schema (DDL, table list, ERD), API docs, file specs, or a business-domain description. Ask for what is missing before planning, not after.

Read first

• shared/conformed/dimensions.yml — existing dimensions and registered source-system ids
• .claude/commands/experts/data-modelling.md — the judgment this skill applies (import viability, conformance, fact separation, naming). Do not plan from memory of it.
• sources/example/ — a complete worked source; the shape of what forge-build will emit

Steps

1. Register the source. Allocate the next free source_system_id in the registry (never reuse one). Record the source's business meaning in one paragraph.
2. Work backwards from demand, not forwards from tables. List the questions the business wants answered, name the gold objects that answer them, collapse duplicates (several reports over one grain are one object), and only then map objects back to source tables. A source table is not entitled to a gold table.
3. Classify every source entity as dimension, fact, mapping (atemporal M:N), assignment (effective-dated M:N), reference, raw-only, or skip — with the grain of each. Apply the import-viability rule from the modelling expert: consumer count decides; sole-consumer lookups fold into their consumer; operational tables skip. Distinguish SKIP (never earned) from DEFER (wanted, not now — keep its trigger).
4. Map to conformed dimensions. For every FK that points at an entity the registry already masters, reuse the dimension. Decide the resolution mode per FK: hash when the fact shares the dimension's id namespace (same upstream system), lookup by natural key otherwise. Run the two-axis conformance test (population overlap measured, then conceptual conformance) before declaring anything "new".
5. Design new dimensions — only for entities with no conformed equivalent. Define table name (singular, no prefix), business key (src_<key>), surrogate (<name>_id = xxhash64(key, source_system_id)), SCD type, and columns. Every column states why it is in gold (slicer, label, measure, time axis, FK, reconciliation). Exclude audit/operational fields — they stay in silver for drill-through. Mutable operational state is not a dimension column; derive it from a fact.
6. Design fact tables. Business process, grain (one sentence), the source business key as the MERGE key, dimension FKs (conformed + new), measures with aggregation rule, degenerate dimensions, timestamps with their semantic (*_at = UTC instant). Run the fact-separation checklist before merging two facts that "look the same". Propose cluster_by (≤ 4 columns, most-filtered first).
7. Define bronze tables. Source table, watermark column (a bare indexed column), lookback, primary key, geometry/binary columns needing a custom_query. Bronze keeps every column unless ingestion is toxic (size) or impossible (type) — "low value" is not a reason to drop at ingestion.
8. Define silver transformations. snake_case renames (correct English), type casts (size decimals explicitly), dedup rule, expectations (non-null PK, unique grain, ranges).
9. Flag decisions you cannot make alone. Anything sticky with more than one defensible answer — grain, conform-vs-distinct, source_system_id, PII handling, a name — is an AskUserQuestion, not a silent default and not a line in an open-questions list.
10. Write the plan to specs/<source-name>-integration-plan.md.

Output format

# <Source> Integration Plan
## 1. Source overview            (system, owner, volume, refresh cadence, source_system_id)
## 2. Demand → gold objects      (the questions, the objects that answer them)
## 3. Entity classification      (table | class | grain | verdict | evidence)
## 4. Conformed dimension mapping (FK | dimension | resolve mode | evidence)
## 5. New dimensions             (per dim: key, surrogate, columns with reasons, excluded)
## 6. Fact tables                (per fact: grain, merge key, FKs, measures, cluster_by, excluded)
## 7. Bronze layer               (table | watermark | lookback | pk | notes)
## 8. Silver layer               (renames, casts, dedup, expectations)
## 9. Dependency graph           (bronze → silver → gold task order)
## 10. Deployment                (compute, schedule, backfill strategy)
## 11. Test plan                 (invariants per table)
## 12. Decisions taken           (each sticky call and who made it)
## 13. Deferred / skipped        (with the pull-forward trigger for each deferred item)

Rules

• Never create a dimension the registry already has.
• Every fact has a source business key as its merge key and at least one conformed FK.
• Percentages are stored as 0–1 fractions. Durations carry their unit in the name.
• Bronze is a faithful copy; silver cleans; gold models.