During a recent migration from Segment to RudderStack for a client, we faced a significant challenge: the historical event data in their old CDP was, frankly, a mess. Schemas had drifted over three years, with the same logical event (`checkout_started`) appearing in four different shapes due to inconsistent instrumentation by various teams. A direct `dump-and-load` would have corrupted the new data warehouse schema and broken all downstream models.
The conventional wisdom is to transform this data *after* ingestion into the warehouse, using your warehouse's SQL capabilities or more ETL jobs. However, this leaves you with a period of time where your new, clean data pipeline is polluted with legacy noise, complicating validation and potentially breaking live dashboards.
We instead used **dbt (data build tool) to transform the event JSON *during* the extraction process, *before* it ever hit the new CDP's ingestion endpoint**. Here's the conceptual workflow:
1. **Extract** raw historical events from the old CDP's S3 archive or export API.
2. **Load** them into a transient staging area (we used a dedicated S3 bucket).
3. **Transform** using a purpose-built dbt project that reads from the staged JSON files, applies schema normalization, de-duplicates, and maps old event names to new ones.
4. **Output** the cleansed, validated events as newline-delimited JSON files conforming to the new schema.
5. **Ingest** these clean files directly into the new CDP via their batch upload feature.
The crucial dbt models for this looked less like traditional transformation models and more like declarative schema mappings. For example:
```sql
-- stg_legacy_events__transformed.sql
{{
config(
materialized='external',
format='json'
)
}}
select
-- Rename and standardize fields
user_id::string as anonymous_id,
context_page_url::string as page_url,
timestamp::timestamp as original_timestamp,
-- Map multiple legacy events to a single new one
case
when event_type = 'checkout_initiated' then 'checkout_started'
when event_type = 'payment_flow_start' then 'checkout_started'
else event_type
end as event_name,
-- Consolidate disparate payload structures into a unified `properties` JSONB
jsonb_build_object(
'plan_id', coalesce(plan_id, subscription_tier),
'amount', coalesce(amount_cents / 100.0, transaction_value),
'legacy_event_source', 'segment_v1'
) as properties
from {{ source('legacy_staging', 'raw_segment_exports') }}
where event_type is not null
```
Key advantages we observed:
* **Schema Guarantees:** The new CDP receives only well-formed events. Validation (e.g., using `dbt test` with `json_schema` macros) happens *before* ingestion, protecting the integrity of the new pipeline from day one.
* **Idempotent Backfills:** The dbt job is idempotent. If a backfill fails halfway, you simply rerun it. No risk of partial or duplicate data in the live CDP.
* **Leverages Existing Skills:** The data team already knew dbt. This avoided spinning up new, unfamiliar transformation tooling for a one-off migration.
* **Historical Single Source of Truth:** The *cleansed* historical events become the new source of truth. You can discard the raw legacy dump, simplifying future audits.
The major caveat is throughput. This is a batch process, not real-time. For terabytes of history, you must optimize your dbt materialization (we used `external` tables writing directly to S3). However, for ensuring a clean baseline before switching over live event streams, this method provided a level of control and quality that post-ingestion transformation simply could not. It turns the migration into a structured data modeling problem, which is far more comfortable terrain than a black-box pipeline.
metrics over vibes