The Data Model for a Clay-to-Warehouse Lead Pipeline

Clay enriches beautifully and forgets everything. That is fine - it was built to be an enrichment and orchestration surface, not the place your pipeline lives. But run the same account through a Clay table twice and you will feel the gap: no stable identity, no dedupe, no memory across runs. The fix is a warehouse behind Clay, and the unglamorous part nobody writes down is the data model that connects them. This is that model, the way I run it behind StackSwap with Clay and Prospeo landing into Supabase. It is the same skeleton as the signal-scoring pipeline, pointed at the enrichment layer specifically.

Clay is not your system of record

Clay is a spreadsheet that calls APIs - phenomenal for that, and the wrong shape for permanence. The first time you re-enrich an account, you need answers Clay does not keep: is this the same company I saw last month, which fields changed, what is the canonical row. Those are system-of-record questions, and a record needs stable keys and dedupe that an enrichment surface is not built to hold. So Clay stays the enricher and the warehouse becomes the memory. Trying to make Clay both is how you end up with five rows for one account and no idea which is true.

The three layers: land, clean, serve

Land. A raw table that stores Clay's output exactly as it arrives, append-only. You never transform on the way in. If Clay changes a column, this table absorbs it without breaking anything downstream. Clean. The entities table, keyed on a stable natural identifier and upserted - one row per real company or person, deduped, with the fields you actually trust. This is what the rest of your stack reads. Serve. The joined view that scoring, routing, and reps consume - entities joined to their signals and activities. Disposable and rebuildable from the layers beneath it. Land-clean-serve is the spine. It is what lets a Clay schema change land harmlessly in the raw layer instead of corrupting the records your motion runs on.

Keys and idempotency

The single decision that makes or breaks this pipeline is the key. Use a natural key the outside world agrees on - the registered domain for a company, a normalized email for a person - and upsert on it, never insert. Then a Clay re-run converges onto the existing row instead of spawning a new one. The trap, and I stepped in it, is keying on Clay's own row id: it is not stable across runs, so every re-run writes a full set of duplicates and your "pipeline" quietly doubles. Natural key plus upsert is the whole defense.

What broke

Keyed on the wrong thing. My first version keyed the clean layer on Clay's row id. Every table re-run produced a parallel universe of duplicate accounts, and the scores built on top counted the same company several times. Re-keying on domain and switching to upsert collapsed them back to one row each. Schema drift reached the clean layer. Before I split land from transform, a Clay column rename broke the cleaned table directly. Landing the raw output untouched and transforming in SQL afterward contained it - now a Clay change is a one-file transform edit, not a pipeline outage.

Where this leaves you

A clean warehouse behind Clay is what turns one-off enrichment into a pipeline you can score, route, and trust over time - it feeds the signal-scoring pipeline and the enrichment-on-create webhook the same clean entities. GTM OS is the hosted version of this, warehouse and all, run from one place on your own AI keys; the Operator Playbook has the skills to model it into your own Postgres. Keep the enrichment on its own rails. Run the memory from one place.