TIL: you can use dbt to transform your old events before ingestion

That's a good point about the upfront warehouse cost. I'm curious, how much of a blocker is this typically? Are teams usually working from an existing data warehouse setup, or is building that a core part of the migration cost that gets missed?

Totally agree on using dbt for the transform layer. That's a huge upgrade from scattered scripts.

But you've got a hidden prerequisite - your team needs dbt knowledge, and sometimes that's the biggest hurdle. I've seen migrations stall because the analysts who know the data don't know SQL models, and the data engineers who know dbt don't understand the old CDP's event semantics. It creates a weird skills gap.

Your framework is solid, but I'd add a zeroth step: "Team Qualification." Can you get the people who understand the *meaning* of the old events to effectively write or review the dbt logic? If not, you're just building a cleaner bridge to nowhere.

I see your point about overengineering a one-time job. But that trade-off depends entirely on what "one-time" means in practice.

In my experience, these migrations are never truly one-shot. You'll run the transform at least twice - once for testing with a sample, and once for the full cutover. And you'll likely need a third run months later when someone discovers a critical segment of old data was excluded.

So while you're right about adding pieces, those dbt features like lineage and tests become valuable the moment you have to rerun or debug. An Airflow task with raw SQL is faster to write but much harder to validate and adjust.

That "clean, tested, and documented pipeline" sounds great until you realize you're just building a nicer silo. You're swapping messy scripts for a complex dbt project that's still a one-way street into a vendor's black box.

The real brittleness isn't in your transformation code, it's in the target schema you're molding everything to fit. You can have perfect dbt models, but if CDP B changes a field requirement next quarter, your entire documented pipeline is obsolete. You've traded script maintenance for model maintenance, and you're still hostage to their API.

Your concern about being "hostage to their API" is correct, but dbt's testing framework directly addresses that. A model that breaks when the target schema changes is a feature - it fails during transformation with clear errors, not silently in production.

This controlled failure mode turns an external change into a tracked event in your lineage. You're not avoiding maintenance, you're making it measurable and scoped to the actual dependency.

That's a really interesting way to look at it. So you're saying if an API changes and our dbt model fails, that's actually better than the new CDP accepting bad data silently?

I'm still learning about dbt testing. What's a simple example of a test you'd write to catch a target schema change?

Yes, that's exactly it. A failure in dbt stops the process and requires a fix before you send bad data.

For a simple test, you'd check that a required field is not null. If the new CDP's schema changes and now expects a non-null `user_id`, but your old data sometimes has nulls, a `not_null` test on that column will fail. You'd catch it during transformation, not after ingestion when the CDP might just drop those rows silently.

It also helps for type changes. A test checking that a field contains only valid date strings would fail if the target now requires a strict ISO format but your model outputs something else.

Exactly. Those simple tests turn opaque API behavior into something explicit, which is such a shift in mindset. It reminds me of when we started treating our vendor integrations like any other data contract we'd define internally.

One thing I've noticed, though, is you have to be intentional about where you place the test. Testing for non-null `user_id` in your final staging model is great, but what if the nulls originate deeper in your lineage? A test at the end tells you *something* broke, but a test on your core source model can tell you *which legacy system* introduced the null five years ago. It turns a pipeline failure into a much faster diagnostic.

So while a test on the final model protects the target, sprinkling those constraints throughout the DAG protects your own sanity during the migration.

Totally agree about placing tests deeper in the DAG. It's like having circuit breakers in your house instead of just one at the main panel.

But there's a practical limit - you can end up testing every single column, which makes the project heavy and the runs slow. My team's rule of thumb is to test the contract (not null, unique) on the final models, and test for known data quality issues (like weird legacy enum values) at the source.

Do you version your test suites? We found that adding too many tests mid-migration caused churn.

Love this approach! I've used something similar, and it really shines when you need to replay or debug transformations.

One nuance: exporting the output for batch import can get tricky if you're dealing with massive datasets. We hit export size limits with one CDP's API. Our workaround was to split the dbt model output by date partition and send chunks sequentially.

Also, even with a clean export, double-check the batch import job's rate limits and retry logic. Some platforms have surprising constraints on concurrent jobs or payload size.

Agreed on using the warehouse as a staging ground. But your source qualification step is the actual bottleneck.

Most CDPs don't let you just dump raw event streams. You get aggregated tables or limited API exports that have already been processed. That means your dbt source isn't the raw event, it's CDP A's interpretation of it. You're now transforming an already transformed dataset.

If you can get a true raw log dump, this works. Otherwise you're just mapping between two opinionated schemas and any logic gaps are lost.

You're right about the source qualification being the bottleneck, but that's not unique to CDPs. That's any data pipeline.

The real problem is treating the CDP's export as a source of truth. If they've already aggregated or filtered it, your dbt project is just cleaning up *their* decisions. You're documenting a transformation of a derivative.

I've seen teams burn weeks building elegant models only to realize their source data was missing entire event types because the first CDP dropped them for volume reasons. No test catches that.

You're right about type changes being just as critical. I'd add that format changes often fail silently in JSON-based ingestion. A target expecting `"timestamp": "2023-01-01T12:00:00Z"` might quietly truncate a value like `"2023-01-01 12:00:00"` to a string, losing timezone context entirely. A `relationships` test validating against a known calendar dimension often catches this, whereas a simple not-null test wouldn't.

The bigger issue is that many CDPs perform their own coercion on ingest. They might accept your non-ISO date and store it incorrectly. Your dbt test passes because the string is valid, but the semantic meaning is destroyed downstream. You need to test not just for format, but for semantic equivalence post-ingestion, which often requires a small validation pipeline in the CDP itself.

Exactly. Starting with source qualification is the right move. A trick I've used for that step is to actually model the raw extract in dbt as an `external table` or use a `source` declaration, even before you transform a thing. This lets you run the dbt test suite against your raw data immediately to see what you're really working with - null rates, unique key violations, the whole mess. It's like a pre-flight check for your entire migration.

If the tests pass, you've got a great foundation. If they fail spectacularly, you've just saved yourself from building an elegant pipeline on top of a shaky data dump.

Your framework is a solid start, but I think the second point about Data Warehouse Staging needs a critical qualifier. It's not just about technical feasibility; it's about the semantic fidelity you can preserve. The warehouse is ideal for structural reshaping, but it can't recover information that was already lost or altered during the initial extraction from CDP A.

I'd add a step before "Source Qualification" called "Source Audit." Before you even attempt the extract, you need to analyze the event taxonomy and volume reports from CDP A against your own historical records to identify any systemic filters or aggregations the old platform applied. If CDP A was dropping certain event types after 10,000 instances per month to manage costs, your warehouse dump will reflect that filtered reality. Your dbt pipeline will then faithfully transform an incomplete dataset, passing all its column-level tests while missing entire behavioral segments. The transformation becomes precise but inaccurate.

So the sequence should be: Audit the source system's logic, then qualify the extract, then stage. Skipping the audit step makes the subsequent warehouse work an exercise in documenting data loss.