Warehouse and Pipes

Decide whether GTM data belongs in a warehouse yet, and design the layers if it does. This skill says no more often than yes, and the no is the more valuable answer.

When to use

• Somebody proposes buying a warehouse, a CDP, or a reverse ETL tool
• Metric definitions live in five systems and changing one takes a week
• Product usage data needs to join to CRM data
• Assessing whether the data is structurally ready for agents

Inputs

• Reads: workspace/data/object-model.md, workspace/stack/inventory.csv
• Needs from user: the systems holding customer data, whether product usage data exists, the team's SQL capability, and the specific question that cannot currently be answered

That last input decides everything. A warehouse with no unanswerable question behind it is infrastructure looking for a purpose.

Workflow

1. Run the justification test before designing anything

A warehouse is justified when at least two of these are true. One is not enough.

If fewer than two are true, say no and name the cheaper fix. Usually the cheaper fix is a metrics dictionary (reporting-architecture) or a single one-way sync (data-model-and-sync). Both cost a fraction of a warehouse and solve the actual complaint.

The most common false positive: two dashboards disagree, so somebody proposes a warehouse. A warehouse lets the same two definitions disagree faster and at higher resolution. That is a definitional problem.

2. Understand what the architecture actually is

Four layers, and the naming confusion in this space hides how simple it is.

SOURCES          CRM · product events · billing · web · sequencer
   │
   │  ELT: extract and load raw, transform later
   ▼
RAW              landed, unmodified, append-only, never queried directly
   │
   │  transformation, version-controlled, tested
   ▼
MODELED          the layer where definitions live. One definition, in code
   │
   ├──────────────────────► BI and reporting
   │
   │  reverse ETL: push modeled fields back out
   ▼
ACTIVATION       CRM fields · sequencer · ads audiences · agent context

The modeled layer is the entire point. It is where "qualified opportunity" gets defined once, in code, in version control, with a test. Everything downstream reads that definition instead of reimplementing it.

The activation arrow back into the CRM is what makes it operational rather than analytical. A warehouse that only feeds dashboards is a reporting project. One that pushes scores and segments back into the tools reps use is infrastructure.

3. Transform in the warehouse, not on the way in

Load raw and transform after. Two reasons that matter in practice.

Raw data you kept can be re-transformed when the definition changes. Raw data you transformed on ingest is gone, and recovering it means a backfill from the source, which is often rate-limited or impossible.

And transformation logic in a version-controlled repo is reviewable, testable, and attributable. The same logic inside a sync tool's UI is a black box that one person understands.

4. Keep the model layer small and opinionated

The failure mode is a warehouse with four hundred models nobody can trace.

staging/     one model per source table. Rename, cast, no business logic
core/        the conformed entities: accounts, contacts, opportunities, activity
marts/       purpose-built tables, one per consuming use case

Rules that keep it maintainable:

• Business logic lives in core/ only. If two marts each compute win rate differently, the definition belongs upstream
• Every model in core/ has a test on its key. Uniqueness and not-null, at minimum
• A mart with no consumer gets deleted. Unused models are the same liability as unused skills

5. Decide activation carefully, because this is where it gets dangerous

Reverse ETL writes into systems of record. That is exactly the seam where the overwrite disaster happens.

[ ] Every field written back is listed, with its direction-of-truth rule
[ ] No field where a human is authoritative is written by the warehouse
[ ] Writes are additive to dedicated fields, never over shared ones
[ ] A distribution alert exists on every written field
[ ] Sync failure alerts on error rate AND on distribution shift

The fields worth activating are computed ones: scores, tiers, segment labels, health, propensity. Not names, not titles, not sources. Compute in the warehouse, write to a field the warehouse owns.

6. The AI-readiness question, answered honestly

"Is our data ready for AI agents" reduces to four checks, and none of them require a warehouse.

Two of four are object-model and hygiene problems. Route to data-model-and-sync and data-hygiene first, because a warehouse built on an undefined account key inherits the ambiguity at higher cost.

7. Cost it honestly

Warehouse compute and storage        $n / year
ELT tool, priced on rows or volume   $n / year
Transformation tooling               $n / year
Reverse ETL, priced on records       $n / year
Analytics engineering time           <n> days / month, at loaded cost
                                     ← the largest line, and the one omitted

The time line is the real cost. Warehouse projects fail on maintenance capacity far more often than on tooling budget.

Output

• Writes: workspace/data/data-architecture.md
• Prints: the justification test result with a count, the layer design if justified, the activation field list with direction-of-truth rules, the AI-readiness answer, and the full cost including the time line

Rules & quality bar

• Two justification conditions minimum, or say no and name the cheaper fix
• Never recommend a warehouse for a definitional problem. Route to reporting-architecture
• Load raw, transform in the warehouse. Keep what you landed
• Business logic in one layer. Two marts computing the same metric differently means it belongs upstream
• Only computed fields get activated back, into fields the warehouse owns
• Distribution alerts on every written-back field. This is where the overwrite disaster lives
• Route AI-readiness to the object model and hygiene first. Two of the four checks are not warehouse problems
• Cost the analytics engineering time. It is the largest line and the reason these projects stall

Related skills

• Requires: data-model-and-sync for the identity layer
• Routes to: reporting-architecture when the problem is definitional, martech-stack-audit for the buy decision
• Pairs with: data-hygiene, agent-observability
• See also: docs/stack.md layer 8, docs/architecture.md on seams