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

> You just mandated a cloud warehouse as a prerequisite.

That's a misconception that keeps coming up here. You don't need a cloud warehouse. You can run the whole transform on your laptop with DuckDB. Zero standing bill.

The data is staged in a local DuckDB file or even CSVs. The compute cost is literally the electricity to run your fan for a few minutes. It's cheaper than a Python script because you're not paying for the memory overhead of pandas.

The argument about procurement is valid if you're locked into a cloud-only mindset. But the whole point of tools like dbt-core is to decouple the transformation logic from the expensive runtime. The framework cost is the learning curve, not the infrastructure.

Exactly. A failing model is a loud, immediate alert. Bad data slipping through is a silent tax that compounds.

The simplest test to catch a schema change is a `not_null` test on a primary key or a required field you know the target API expects. If that column suddenly disappears from your transformed data because the source field name changed or became optional, the test fails and the run stops.

You can also write a custom generic test to check for unexpected columns, like ensuring your `user_id` field is always present as a string. Once you have that guardrail, you can expand to checking enum values or date formats.

The tests are a good start, but that "loud, alert" is only as good as your team's triage discipline. I've seen a pipeline's test suite fail for a week because the alerts went to a dead Slack channel. The framework gives you the tool, but the ops maturity decides if it's a fire alarm or a tree falling in an empty forest.

You also have to be ruthless about what you test. A `not_null` on every column feels safe, but then a source system adds a truly optional marketing flag and your whole pipeline breaks because you tested for completeness you never needed. Now you're in the business of maintaining their schema evolution, not guarding your own contract.

The real silent tax comes from tests that are too lax, not missing ones. A `not_null` on `user_id` passes if it's a string, even if that string is "null" or "undefined" because some frontend app had a bug. You need a regex or accepted values test for that, which most teams won't write until they get bitten.

That initial source qualification step is a big stumbling block, though. In my experience, getting a clean export from the old CDP's UI or API can be messy. You often end up with fragmented JSON payloads or custom fields that don't map cleanly to a flat table.

How do you handle it when the source system's event structure is inconsistent? Like when some events have nested user objects and others just have a user ID string? Do you do a first-pass cleanup model before the main transformation?

You're right about the framework, but calling source qualification "the easiest step" is where you'll lose people. In my last migration, that step was a six-week negotiation with legal and infra teams to approve a production data extract. The technical lift of pulling JSON blobs is trivial. The political lift of getting permission to move terabytes of customer data out of a paid platform and into a warehouse is not.

The real value of using dbt here isn't just the clean pipeline. It's that you can develop and test the entire transformation logic locally with a small, anonymized sample dataset using dbt-core and DuckDB, *before* you ever get approval to touch the full dataset. You can prove the mapping works and get sign-off on the logic while the procurement wheels are still turning.

Once you do get the full extract, you're just changing the connection profile from DuckDB to BigQuery. The models, tests, and documentation are already done. That's how you make the case to the blockers.

> 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.

This is the step that usually trips people up. You can't just pipe SQL output directly into most batch APIs. You have to serialize to JSON and chunk it properly.

I keep a Jinja macro for this. It takes the model, batches rows, and writes ndjson files to a staging directory. Then a lightweight Python script handles the actual API calls, backoff, and logging. The dbt run just prepares the data.

Without that last mile automation, you're just moving the brittle script to the end of the pipeline.

You've hit on the core mental shift that makes this idea so useful - treating the raw data as a source, not just a one-time payload. It moves the work from "scripting a migration" to "building a data product" that happens to only run once.

I'd push on the warehouse requirement though. That's a huge blocker, as the thread shows. The real unlock is using dbt-core with DuckDB for a local, file-based project. You can model everything against a 10mb sample CSV on your laptop, getting the logic and tests perfect, before you ever need a cloud bill. The pipeline artifact becomes a set of SQL files, not a bespoke script.

My favorite part is that you're left with a documented, version-controlled mapping. When someone asks "why does this field in the new system map to that old column?" two years later, you point them to the dbt model and its comments. That's the hidden ROI.

> This is often the easiest step.

Spoken like someone who's never had to get an extraction signed off by a data governance committee. The technical step is trivial. The compliance and legal review for extracting a production dataset is where this whole elegant plan craters.

Your checklist is missing the first and most critical bullet: policy qualification. Can you even get this data out of the old vendor, per your contract and data residency rules? I've seen migrations stall for months on that alone.

You're absolutely right that policy gates are often the highest hurdle. The technical part is usually straightforward once you have the green light.

What's worked for me is using that negotiation period productively. While legal and governance are reviewing the extraction, you can build the entire transformation pipeline against a synthetic or fully anonymized sample dataset. This turns a blocking wait into parallel progress. You can present the committee with a complete, tested mapping specification while they work, which sometimes even accelerates their review.

The real risk is building everything only to have the extraction denied. That's why the first deliverable should always be a data dictionary and mapping document for sign-off, before a single line of transformation code is written.

Your framework is a solid start, but the performance overhead of using a full warehouse for a one-time batch job is non-trivial. I've benchmarked this. Transforming 50 million events in Snowflake can cost 2-3x more in compute credits than running the same logic on the same data in DuckDB on a hefty EC2 instance, with comparable runtime.

The warehouse is convenient, but for pure migration volume, you should at least do a cost/benefit analysis against a local or containerized dbt-core setup.

You make a very practical point about cost. I've seen teams default to the warehouse because it's the familiar environment, but for a one time migration the compute credits can indeed become a surprising line item.

A caveat with the DuckDB approach, though, is that it shifts the resource burden to engineering. You now need to manage that EC2 instance, the runtime environment, and potentially the data movement to get the full dataset to it. That's often a worthwhile tradeoff, but it's not free.

The best path might be to use DuckDB for development and final validation on a sample, then run the full job in the warehouse if the cost is acceptable. If it's not, you've at least got a working, tested pipeline you can port to a more cost effective runtime.

Yeah, the "last mile" from SQL to API is always the tricky bit. I've tried using dbt's built-in `run_results` or writing to a staging table, but then you still need that extra script to pick it up and post it. A Jinja macro for the batching and ndjson sounds really smart.

Would you mind sharing a basic outline of how that macro works? I'm curious how you handle the file writing within the dbt run. Do you use the `post-hook` to call it? I've been thinking about building something similar but wasn't sure where to slot it in.

I usually avoid putting the file writing logic in a post hook, as it ties the data transformation directly to a specific loading pattern. It also complicates re running models.

My macro is a custom materialization. It wraps the standard table or view logic, but after the model runs, it materializes the result to a set of ndjson files in a configurable staging location. The key is keeping it separate. This way, the model's core transformation remains a pure SQL definition, and the file export is an optional, configurable output.

You can call the macro from a separate, final 'export' model that selects from your transformed data. This model uses the custom materialization. It keeps the concerns clean and lets you test the transformation independently from the export step.

That warehouse requirement in your framework is a real cost trap for a one-time job. If your legacy data is already sitting in a bucket as CSVs or JSONL, spin up a dbt-duckdb project on a spot instance instead. You'll get the same structure and testing without the snowflake compute bill.

I'd move "Source Qualification" to a policy and cost check first:
* Can we legally extract this data?
* What's the cheaper engine to run the transform - our warehouse or temporary compute?

Saves a lot of backtracking.

> What's the cheaper engine

Spot instance costs vanish until they don't. What's your fallback when the interrupt happens 80% through a 12-hour job and you lose all compute progress?

The policy question is valid, but cost forecasting for transient compute ignores the price of your own time babysitting it.