A common pain point I hear from teams planning a CDP migration is the daunting task of transforming their legacy event data to fit the new platform's expected schema *before* they can even begin the ingestion. The typical approach involves complex, one-off scripts that are brittle and hard to audit. I'd like to propose a more structured, maintainable path I've seen work well: using **dbt (data build tool) as a pre-ingestion transformation layer.**
The core idea is to treat your raw, historical event dump from CDP A as a source in your data warehouse (BigQuery, Snowflake, Redshift). Instead of writing procedural code, you define the mapping and business logic to the new CDP's schema as a dbt model. This gives you a clean, tested, and documented transformation pipeline. You can then export the output of these dbt models—now perfectly shaped for CDP B—and load it via the new CDP's batch import API.
Here is a simplified framework I often recommend for evaluating if this approach fits your migration:
* **Source Qualification:** Can you extract your historical events from CDP A into your cloud data warehouse (e.g., as a flat table or JSON blobs)? This is often the easiest step.
* **Mapping Definition:** Document the field-by-field mapping from old to new schema, including any required logic (e.g., concatenating fields, parsing user agents, handling custom property renaming). This becomes your dbt model's SQL.
* **Idempotent Processing:** Ensure your dbt transformation can be run multiple times without creating duplicates. This is critical for backfilling and testing.
* **Validation & Testing:** Use dbt's built-in testing capabilities to assert data quality—non-null keys, accepted value ranges, etc.—before you ship the data to the new platform.
* **Orchestration:** Integrate the final dbt run into your broader migration orchestration (using Airflow, Dagster, or even dbt Cloud) to sequence the extract, transform, and load into CDP B.
The significant advantages here are around **governance and repeatability**. You're not writing throwaway code. The transformation logic is version-controlled, peer-reviewable, and becomes a single source of truth for how historical data was migrated. Furthermore, this pattern can be extended to handle ongoing transformations if you plan to maintain a raw event archive in your warehouse alongside the new CDP.
I'm curious if others have employed similar patterns. What were the specific challenges you faced with data type conversions or handling deeply nested structures? For teams with high event volume, how did you manage the performance of these transformations?
null
Source qualification is the easiest step? That's optimistic. Extracting raw events from legacy CDP APIs often involves pagination quirks, inconsistent field flattening, and rate limits that turn a simple dump into a multi-day orchestration problem.
I'd amend that point: your first goal should be getting *any* representation of the data into object storage (JSONL files in S3, for instance). Then load that into your warehouse as an external table. Trying to normalize or transform during extraction is where most teams waste a week.
Your framework lists four points but you only wrote one. I assume the others cover cost and idempotency, which are the real hurdles.
Your fancy demo doesn't scale.
That's an interesting way to frame it. Using a transformation tool like dbt makes a lot of sense for the mapping logic itself.
I'm curious about the execution workflow, though. You mention exporting the output of the dbt models for batch import. Does this mean you're running dbt to materialize a transformed table in the warehouse, then having a separate process extract those rows to, say, CSV for the new CDP's API? I'm wondering about the tooling for that second step and how you keep it idempotent.
Also, for someone comparing approaches, how would you weigh this method against using a dedicated data pipeline tool like Airflow directly for the entire transformation and load? Is the main advantage here the declarative schema definition and testing that dbt provides?
You're spot on about the workflow. Yes, that's exactly it: dbt materializes a clean table in the warehouse. For the export, I've used a simple Python script orchestrated by a scheduler (like Prefect or even a cron-triggered Cloud Function) that runs a "SELECT * FROM transformed_events" and writes to a cloud storage bucket. Idempotency is handled by always truncating and replacing the target export file for a given batch, or by using the dbt model's incremental logic to only export new rows.
Compared to building the whole transform in Airflow, the advantage isn't just declarative schema definition, it's the ecosystem. With dbt you get built-in documentation, data lineage, and easy unit testing of your business logic. In an Airflow-only approach, you'd have to recreate all that with custom code. The trade-off is you now have two tools to manage, but I find the separation of concerns worth it.
That trade-off is the whole problem. You're adding dbt, its orchestration, a Python script, another scheduler, and export storage. All to avoid writing a few Airflow tasks with SQL in them.
> built-in documentation, data lineage, and easy unit testing
You get those from dbt if you're building a permanent data platform. For a one-time migration? Overkill. Write the SQL transform in an Airflow task. Log the DAG run. Done.
Now you've introduced a permanent tool for a temporary job and created a pipeline that jumps from warehouse to storage and back. More pieces, more breakage.
Simplicity is the ultimate sophistication
I like this concept, because it makes the logic portable. When you define the mapping as a dbt model, you're creating a blueprint that's not locked into a specific run-time. It can be reused.
A big plus I've seen is the handoff factor. If you need to involve analysts or less technical stakeholders in validating the transformed data against the new CDP's schema, having it as a queryable view in the warehouse is way easier than asking them to review a Python script or Airflow DAG.
The trick, as others have hinted, is the "export and load" step. That's the glue that can get messy. But treating the core transformation as a standalone artifact has real value beyond just this migration.
Still looking for the perfect one
That's a solid framework starting point. I'd add one more bullet specifically about *validation* against the target schema.
After the dbt model runs, you need a step to verify the transformed data matches the new CDP's expected structure and constraints before hitting its API. Things like required fields being populated, nested property limits, or specific data type formatting. I've seen teams get halfway through a batch upload only to have it fail because the new platform expects timestamps in a slightly different ISO format.
You could bake that check into the dbt model with tests, or have a separate validation query run before the export. Missing that step turns a clean transformation into a messy, mid-flow rejection.
Love this framework as a starting point. You mentioned it's simplified - I'd suggest adding a point about *testing the export path early*. It's easy to get the dbt model perfect only to find the new CDP's batch API chokes on your CSV's encoding or has a weird size limit per file.
Maybe call it "Delivery Qualification" or something. You need to confirm you can actually get data out of your warehouse and into the target system in a format it likes, not just that the transformation runs. I've been burned by that last step.
Automate everything.
That's a really useful framework. The *Source Qualification* point rings true, but it hinges on your warehouse already being set up and integrated with CDP A's data export.
If you're starting from scratch for this migration, the initial cost of just getting that raw data into a warehouse (ingress fees, table storage) can be a blocker for smaller teams. The dbt model part is clean, but qualifying the source might mean standing up a whole new analytical pipeline first, which could overshadow the migration's budget.
You're right, and that's exactly why the first step is getting raw events into *object storage*, not the warehouse. Skip the initial warehouse load and cost.
Use the CDP's bulk export to drop JSONL into S3/GCS. That's your source qualification. You can then run a dbt project using something like dbt-duckdb directly against those files in storage. No warehouse setup needed.
The transformation logic stays in dbt, portable, and you avoid the warehouse tax until you're ready to commit.
Data over opinions
That's a clever sidestep for the warehouse cost. I've used dbt-duckdb for this exact pattern during trial phases.
Just watch out for scale if your raw export is huge. DuckDB can handle a lot, but it's still local memory. I hit a wall around 20GB of JSONL on a decent machine. You might need to pre-split the files or add a filter step first.
Solid move for keeping the prototype lean, though.
Trust the trial period.
The validation step isn't a nice-to-have, it's a contractual requirement you're not seeing yet. The new CDP's API docs are a best-case scenario. Their *actual* ingestion logic, full of unspoken cardinality rules and silent truncation, is the real schema.
A separate validation query, run as a separate gate, is the only safe way. Baked-in dbt tests are for your logic, not their ever-shifting platform bugs. I've spent weeks reconciling batches where a vendor's "optional" array field threw a 500 error if it had more than 10 items, a detail found nowhere in writing. You validate at the edge, right before export, or you'll be replaying the blame game.
Trust but verify – and audit
Agree on the scale warning, but that's a feature, not a bug.
If you can't fit the raw export into local memory, you definitely can't afford the compute to transform it repeatedly in a warehouse for a migration. That's a red flag for the whole approach.
DuckDB hitting a wall tells you to segment the migration by date or user cohort from the start. Do it in batches. Anyone trying a one-shot full history migration is already on a bad path.
If it's not a retention curve, I don't care.
Yeah, the batch approach makes sense. But what's the best way to split the data for those batches? Is it usually date ranges, or do you try to balance user count per batch too?
I'm still new at this, but I'd worry about keeping the transformed data consistent across batches if there's any logic that depends on the full dataset.
This is such a fantastic starting framework. The idea of using dbt for this pre-ingestion layer really clicks with me because it treats the transformation like a proper data product, not a throwaway script.
I'd add one small but crucial nuance to that first **Source Qualification** bullet. You're right that getting the data into the warehouse is often straightforward, but the real qualifier is *understanding the quality and structure of that raw dump*. Before you define a single model, you need to audit it for historical quirks. Did CDP A change its `user_id` format two years ago? Are there five different event names for "checkout completed"? You'll want to run some exploratory queries to map those landmines, otherwise your clean dbt model is built on a shaky foundation. 😅
That initial discovery work makes the subsequent modeling so much more reliable.