guerrilla2799 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.
| Condition | Why it justifies a warehouse |
|---|---|
| Product usage data must join to CRM data | The one case native CRM reporting genuinely cannot do |
| Three or more systems each hold part of the customer record | Copies diverge, and reconciliation is now a standing job |
| A metric definition change takes over a week to propagate | Definitions living in tool configs rather than in code |
| Someone on the team can write SQL and will maintain models | Without this the warehouse becomes a very expensive lake |
| Agents need a complete customer view that no single system holds | The AI-readiness case, and it is real |
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.
| Check | Requires a warehouse? |
|---|---|
| One resolvable identity per account and per person | No. Requires an object model |
| Fields an agent needs are populated and current | No. Requires enrichment and a refresh queue |
| A complete customer view is retrievable in one query | Sometimes. This is the real warehouse case |
| Definitions are consistent wherever the agent reads them | Sometimes. A dictionary often suffices |
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-syncfor the identity layer - Routes to:
reporting-architecturewhen the problem is definitional,martech-stack-auditfor the buy decision - Pairs with:
data-hygiene,agent-observability - See also:
docs/stack.mdlayer 8,docs/architecture.mdon seams