What consolidating 8 source systems taught me about ELT design

At my last job I consolidated 8+ source systems into a single Snowflake analytics layer. Product data, marketing platforms, CRM exports, billing, a couple of spreadsheets that everyone swore were temporary and were obviously permanent. Here’s what survived contact with reality.

Land raw, transform later. This is standard ELT advice and it earned its keep within a month. Early on I did light “cleanup” during extraction on one source. Then I needed a field I’d dropped, and reconstructing history for it was miserable. Raw layers are cheap in Snowflake. Store everything, exactly as the source sent it, timestamps and warts included. Opinions belong in dbt models where they’re versioned and reversible.

Incremental extraction is a correctness feature, not a performance one. Full reloads hide sins. If your daily load rewrites the world, a bug in yesterday’s data silently self-heals and you learn nothing. Pulling on change-data timestamps forced me to actually understand each system’s update semantics. Two of the eight had “updated_at” columns that lied. Better to find out on purpose.

The spreadsheet sources deserve real engineering. It’s tempting to treat the CSV-shaped sources as beneath the architecture. Those were the ones that broke most. A marketing platform changes its export headers without notice; a human renames a column in a shared sheet. The flimsier the source, the more validation it needs at the door.

One naming convention, enforced early, even when it feels pedantic. With eight systems, “customer_id” means eight different things. We prefixed everything by source in the raw layer and only unified identity in the modeled layer, where the join logic could be tested and documented. Every hour spent on this repaid itself the first time someone asked “which system does this number come from” and the answer was in the table name.

The payoff for all of it: 500K+ records a day landing tested and standardized, and manual data prep down about 45% because analysts stopped re-consolidating the same sources by hand.

None of these lessons are original. All of them are the kind of thing you nod at in a blog post and then skip under deadline. I skipped some. That’s how I know.