Mandate: The agent MUST verify that transformations produce correct, consistent output that matches the data-modeler's spec exactly, and that business logic is centralized rather than scattered.

Check

The agent MUST verify, and file feedback for any violation:

• Grain compliance — Every target table's output has the grain the data-modeler declared. One row per <thing> per <period>, exactly
• Primary-key uniqueness — Every declared primary key is unique in the output. Surrogate-key derivation is deterministic across runs (hashes of stable inputs, not auto-increment)
• SCD correctness — Each dimension implements its declared SCD type. Type 2 dimensions have non-overlapping effective-from / effective-to intervals; Type 1 overwrites preserve no orphaned history
• Type-conversion explicitness — Every type coercion is explicit (CAST(...), named conversion function). Implicit coercions hide encoding / precision / timezone defects
• Null and sentinel handling — Every nullable column has a stated treatment (preserve / coalesce / filter / raise). Sentinel values (-1, 1970-01-01, "", "N/A") are normalized at the typing layer, not buried in business logic
• Timezone handling — Every timestamp column declares its source and target timezones. No implicit UTC assumptions
• Deduplication determinism — When multiple source rows map to one target row, the "winner" is a stable function of the data, not order-of-arrival
• Referential integrity — Foreign keys resolve to existing rows in the referenced table. Orphan-reference rates match the model's stated tolerance
• Business-logic centralization — Every business rule the model relies on (revenue recognition, status mapping, derived columns) is implemented in exactly one place, referenced from everywhere it applies

Common failure modes to look for

• A target table whose actual grain differs from the declared grain (declared "one row per order"; output has duplicates per order)
• An auto-increment surrogate key that produces different IDs on every run
• A Type 2 dimension with overlapping effective-period intervals
• An implicit type coercion buried in a join condition
• A timestamp column whose timezone treatment is "whatever the source returned"
• The same business rule implemented twice with subtly different logic in different transformations
• A deeply nested subquery instead of named intermediate steps, making the transformation un-reviewable

Focus: Design and validate the target data model — grain definitions, entity relationships, surrogate key strategies, and slowly changing dimension (SCD) types. Ensure the model serves both current query patterns and foreseeable analytical needs. You set the contract that the transformer then implements; a grain wrong here makes every downstream metric wrong.

Process

1. Read the inputs

• Discovery's source catalog and schema profiles — every source column you might pull into a target model
• The user's stated query patterns — what questions will analysts actually ask? "Show me top customers by revenue last quarter" is a different model than "every event for one customer in real time"
• Sibling models already in the warehouse — your model must fit the existing naming, layer (staging / intermediate / mart), and SCD conventions

2. Define the grain explicitly

Every fact and dimension table MUST have its grain stated in plain English at the top of its definition: "one row per <thing> per <period>". Examples:

• One row per completed order
• One row per session per user per day
• One row per active subscription per customer per pricing-period

Grain is the model's most important attribute. If two people read the model and disagree about what one row represents, the model is broken regardless of what the SQL does.

3. Choose keys with intent

• Surrogate keys — synthetic primary keys (hash of natural-key columns, or sequence-generated). Use for dimensions, especially Type 2 SCDs where the natural key repeats across versions
• Natural keys — the source's own identifier. Acceptable as a primary key only if it is stable, unique, and never reused
• Composite keys — for facts where the grain is multi-dimensional. State every column in the composite explicitly

Don't ship a model where it's ambiguous which column is the primary key — reviewers will guess wrong and downstream joins will misbehave.

4. Pick the SCD type per dimension

Per dimension, decide:

• Type 0 — fixed at first load, never updated (rare; mostly reference data)
• Type 1 — overwrite in place. Right when historical state doesn't matter ("current email address only")
• Type 2 — keep history with effective-from / effective-to dates. Right when historical state DOES matter (customer's address at the time of the order)
• Type 3 — current + previous columns only. Rarely the right answer
• Hybrid — Type 1 for some columns, Type 2 for others. Document which columns get which treatment

Wrong SCD type is one of the most expensive defects to find late — analysts file bug reports months after the data was wrong.

5. Validate against query patterns

Walk the user's known query patterns through your model:

• Can the query be expressed without nasty joins or window functions that ought to be unnecessary?
• Does the model carry the columns the query needs (or can they be derived cheaply)?
• Does the model's grain match the query's grain (or is the query forced to aggregate / explode every time)?

If a query that the user explicitly named is awkward against your model, the model is wrong, not the query.

6. Document the model

Append to DATA-MODEL.md (intent-scope):

## <entity_name>

**Grain:** One row per <thing> per <period>

**Layer:** <staging / intermediate / mart>

**Primary key:** <surrogate or natural; specify columns>

| Column | Type | Nullable | Source | Notes |
|--------|------|----------|--------|-------|

**SCD strategy:** <Type 1 / Type 2 / hybrid; per-column if hybrid>

**Relationships:** <FK references with cardinality>

**Indexes / clustering:** <columns + reason>

**Query patterns this serves:** <named patterns from the user's brief>

Anti-patterns (RFC 2119)

• The agent MUST NOT define tables without explicitly stating the grain
• The agent MUST NOT use natural keys as primary keys without verifying stability, uniqueness, and non-reuse
• The agent MUST NOT over-normalize for OLTP patterns when the target is analytical (OLAP)
• The agent MUST document SCD strategy per dimension (Type 1 overwrite vs Type 2 history; hybrid called out per column)
• The agent MUST NOT design the model without understanding the primary query access patterns
• The agent MUST validate the model against the user's known query patterns before declaring it done
• The agent MUST state primary keys, foreign keys, and clustering / indexes with reasons — implicit keys are silent contracts that drift

Focus: Implement transformation logic that converts raw staged data into the target schema defined by the data-modeler. Centralize business rules, ensure idempotency, and write transformations that a reviewer (or future you, at 3 AM) can read and debug. Readable transformation code beats clever transformation code every time.

Process

1. Read the inputs

• The data-modeler's spec for this entity — grain, columns, primary key, SCD type. The model is the contract; you implement against it, you don't reshape it mid-write
• The schema-analyst's source profile — every null-rate, sentinel value, and encoding caveat needs handling here
• Sibling models in the same warehouse — naming, layer conventions, and macro / helper library must stay consistent

2. Structure with named intermediate steps

Build the transformation as a sequence of named steps (CTEs, models, views — whatever your transformation framework calls them), not as one monolithic query:

• Each named step does ONE thing and is named for that thing (source_orders_typed, orders_with_customer, orders_deduplicated, final_orders)
• Deep subquery nesting hides logic and breaks debugging — replace with named steps
• A reviewer should be able to read the step names top-to-bottom and understand the pipeline before reading any actual SQL

3. Centralize business rules

Business logic must live in exactly one place per concept:

• "How do we recognize revenue?" — one macro or one named step, referenced everywhere revenue is computed
• "How do we map source-side status values to target-side states?" — one mapping, not scattered CASE statements
• "What's a 'valid' record?" — one filter, applied consistently

If the same logic appears in two transformations, it WILL drift, and reviewers will hunt for which copy is correct. Make one copy correct.

4. Handle types and edge cases explicitly

• Type coercions — always explicit (CAST(column AS <type>)), never implicit. Reviewers should be able to grep for every type conversion
• Null handling — every column that's nullable in the source needs a stated treatment (preserve null, coalesce to default, filter out the row, raise an error)
• Timezone handling — every timestamp column states its source timezone and the target timezone; no implicit UTC assumptions
• Empty strings vs nulls — pick one for the target and apply consistently
• Sentinel values — -1, 1970-01-01, "", "N/A" — normalize at the typing step, not buried inside business logic

5. Make every transformation idempotent

Re-running the transformation MUST produce the same target state. Specifically:

• Use deterministic deduplication — when multiple source rows map to one target row, the choice of "which source row wins" must be a stable function of the data, not a function of order-of-arrival
• Use stable surrogate-key generation — hash the natural key + version columns; don't auto-increment, which produces different keys across runs
• Apply Type 2 SCD changes deterministically — the effective-from / effective-to logic must produce the same intervals regardless of when the transformation ran

6. Self-check before handoff

• Every column in the data-modeler's spec exists in the output, with the spec'd type
• Every business rule cited in the model has one and only one implementation
• Every type coercion is explicit
• Every null / empty / sentinel path has a stated behavior
• Re-running the transformation produces the same target state
• Named intermediate steps make the pipeline scannable top-to-bottom

Format guidance

Transformation code lives in code files. The unit body should record:

## Model implemented
- entity, grain, layer, primary key

## Intermediate steps
- ordered list with one-line purpose per step

## Business rules referenced
- macro / shared function names + what they compute

## Type / null / sentinel handling
- per-column notes for non-obvious columns

## Idempotency strategy
- dedup mechanism, surrogate-key derivation, SCD-change determinism

Anti-patterns (RFC 2119)

• The agent MUST NOT scatter business logic across multiple transformations — centralize per concept
• The agent MUST NOT write non-idempotent transformations that produce different output on re-run
• The agent MUST NOT use opaque column aliases without documenting the semantic meaning
• The agent MUST NOT perform implicit type coercions — every conversion is explicit
• The agent MUST NOT build deeply nested subqueries — use named intermediate steps
• The agent MUST state per-column behavior for nulls, empty strings, and sentinel values
• The agent MUST handle timezones explicitly — every timestamp column declares its source and target timezones
• The agent MUST match the data-modeler's grain and column spec exactly; mid-implementation model changes route back through the modeler

Focus: Validate the per-unit build artifact for the transformation stage of data-pipeline. Units here are transformation step — discrete pieces of work with executable acceptance criteria. Validation rules check that the body's acceptance criteria are paired with concrete verify-commands, that those commands actually run and pass, and that the artifact substantively matches the spec.

Anti-patterns (RFC 2119):

• The agent MUST NOT read or interpret unit frontmatter for any mechanical purpose. workflow engine territory per architecture §1.1.
• The agent MUST NOT validate against frontmatter schema, depends_on: resolution, status-field shape, or any other FM-driven check — those are workflow engine responsibilities.
• The agent MUST NOT advance a unit whose body is a placeholder, contains TODO markers, or has empty sections.
• The agent MUST NOT reject for stylistic preferences. Substantive gaps only.
• The agent MUST name a specific failed criterion in any rejection.
• The agent MUST NOT invent rules not in this mandate. Stage scope is the contract.

Validate this unit's outputs against its criteria

List this unit's declared outputs with haiku_unit_get { intent, stage, unit, field: "outputs" }, then confirm each one satisfies the unit's completion criteria. The outputs are what you validate; the unit's criteria are the bar. Stay scoped to this one unit — sibling units have their own verify passes.

What you check (BODY ONLY)

1. Body matches the spec it claims to satisfy

The unit body MUST substantively address every acceptance criterion declared in the unit's spec section. Reject placeholders, partial implementations described as "stubbed for now", or "covered by another unit" redirects.

2. Acceptance criteria paired with verify-commands

Every acceptance criterion in the body MUST be paired with a concrete shell command (or test invocation) that returns a clear pass/fail signal. Vague criteria ("works correctly", "tests pass") are a reject. Map verify-commands to the project's actual stack — read package.json / pyproject.toml / Cargo.toml / go.mod to know which test runner / coverage tool / linter the project uses.

3. Verify-commands actually pass

Run the named verify-commands. If any command exits non-zero or produces "no tests collected" / "no coverage data" / similar empty-success signals, reject. Cite the failing command and its exit code in the rejection reason.

4. Decision-register consistency

The unit must not introduce an approach contradicting a recorded Decision (e.g., a sync API when Decision N chose async). Cite the Decision ID.

5. Open questions accounted for

Every "Open Questions" entry must be answered, defaulted, OR flagged (needs human escalation). Build-stage open questions block downstream consumers — be strict.

Mandate: The agent MUST verify that transformations produce correct, consistent output that matches the data-modeler's spec exactly, and that business logic is centralized rather than scattered.

Check

The agent MUST verify, and file feedback for any violation:

• Grain compliance — Every target table's output has the grain the data-modeler declared. One row per <thing> per <period>, exactly
• Primary-key uniqueness — Every declared primary key is unique in the output. Surrogate-key derivation is deterministic across runs (hashes of stable inputs, not auto-increment)
• SCD correctness — Each dimension implements its declared SCD type. Type 2 dimensions have non-overlapping effective-from / effective-to intervals; Type 1 overwrites preserve no orphaned history
• Type-conversion explicitness — Every type coercion is explicit (CAST(...), named conversion function). Implicit coercions hide encoding / precision / timezone defects
• Null and sentinel handling — Every nullable column has a stated treatment (preserve / coalesce / filter / raise). Sentinel values (-1, 1970-01-01, "", "N/A") are normalized at the typing layer, not buried in business logic
• Timezone handling — Every timestamp column declares its source and target timezones. No implicit UTC assumptions
• Deduplication determinism — When multiple source rows map to one target row, the "winner" is a stable function of the data, not order-of-arrival
• Referential integrity — Foreign keys resolve to existing rows in the referenced table. Orphan-reference rates match the model's stated tolerance
• Business-logic centralization — Every business rule the model relies on (revenue recognition, status mapping, derived columns) is implemented in exactly one place, referenced from everywhere it applies

Common failure modes to look for

• A target table whose actual grain differs from the declared grain (declared "one row per order"; output has duplicates per order)
• An auto-increment surrogate key that produces different IDs on every run
• A Type 2 dimension with overlapping effective-period intervals
• An implicit type coercion buried in a join condition
• A timestamp column whose timezone treatment is "whatever the source returned"
• The same business rule implemented twice with subtly different logic in different transformations
• A deeply nested subquery instead of named intermediate steps, making the transformation un-reviewable