Just built a custom migration script that moved 50k records in 2 hours

For a one-time migration of that volume, a custom Python script with pandas is perfectly valid. The main trade-off is that you're now responsible for all error handling and idempotency, which frameworks like dbt handle automatically. Did you include logging for failed rows, or implement a retry mechanism if a chunk fails partway through?

On performance, 2 hours for 50k records seems on the slower side. That's roughly 7 records per second. The bottleneck is often network latency or the row-by-row nature of some operations. Using larger batch sizes and a more direct connection method (like Snowflake's COPY command or a dedicated database connector) could cut that time significantly. But if it's truly a one-off, the time spent optimizing might not be worthwhile.

My one critical question: how did you validate the final record counts and data integrity? A simple row count match isn't enough when you have transformations. A quick checksum on key fields, or a spot comparison of a few hundred rows, would give you more confidence.

Hey, congratulations on getting it done! That first migration is always a bit nerve-wracking. I completely get the feeling of "did I do this right?"

> I'm wondering if this is a typical approach?

For a one-off, absolutely. I've done very similar things when moving between, say, MySQL and Postgres. The script gets the job done and you understand every step. The risk, as user621 hinted at, is usually around error handling. Did you add checksums or row counts at the end to confirm all 50k made it? That's my go-to sanity check.

On speed, yeah, 2 hours feels a bit long, but don't sweat it for a first pass. Pandas is convenient but adds overhead. For a next time, you could try using `psycopg2` to stream directly and the Snowflake connector's `execute_batch` for writes. That often speeds things up a lot. But honestly, if it ran once successfully, the time spent optimizing *after* the fact is less valuable than the experience you just gained!

The big thing frameworks give you is repeatability. If you ever need to re-run this or do something similar, maybe look at structuring your script so the transform logic is separate and can be dropped into a small Airflow DAG or a dbt model later. That's how I evolved my own messy scripts into something more maintainable. Great work! 🎉

Totally agree on the sanity check. For my migrations, I always log a count by a key status column at the end and sometimes even a quick MD5 hash of a concatenated string for a critical subset of fields. It's saved me from a "silent success" that was actually missing data more than once.

Your point about structuring for repeatability is key. I've taken a working script like this and wrapped the core transform function in a class. Later, I could import that directly into a simple Airflow operator. It let me keep the debugged logic but gain scheduling and alerting.

On the speed, `psycopg2` and `execute_batch` is a great combo. For a one-off, though, I think the 2 hours is fine. The real time sink would have been if it failed at hour 1:50 without clear logs on where to restart. That's where I'd focus my energy for the next one. Did you happen to add any checkpointing?

That checkpointing idea is gold. For my last migration, I added a simple CSV log of the last primary key processed from each chunk. If the script bombed, it could read that file on restart and pick up right after the last committed batch. Took maybe 20 extra minutes to build, but it saved a full re-run when a network blip happened halfway through.

Love the MD5 hash suggestion too! I usually just compare row counts, but a hash on a few key fields would catch those sneaky transformation errors where counts match but data is subtly wrong. Gonna add that to my template for next time.

You got the job done, and that's what matters for a first pass. The real question is whether you'd bet your job on this script running correctly next month with zero changes.

Let's talk speed: 2 hours for 50k records is about 7 records per second. That's a red flag for your method, not your skill. Pandas is a massive overhead layer for a simple pipe. For a comparison, a direct `psycopg2` fetchall combined with Snowflake's `write_pandas` in larger batches should do this in under 10 minutes on a decent network. Your bottleneck is likely the row-by-row transformation in pandas DataFrames.

On risk: The biggest one you took is idempotency. If this script fails at hour 1:45, can you restart it without creating duplicates or missing data? Frameworks enforce this. Your script probably doesn't. For a one-off, that's a calculated risk, but logging the last successful primary key or a batch checksum is a cheap safety net.

>I know tools like Airflow or dbt are great for orchestration and transformation

They are, but they're overkill for a single migration. The correct tool is often the one you can debug at 2 AM. You understood your script. That's valuable. Just know that you've built a one-time liability, not a reusable asset. If this needs to run weekly, you'll spend more time adding checkpointing and alerting than you would have just using a simple Dagster sensor or Prefect flow from the start.

Love the idea of an MD5 hash as a sanity check. I usually rely on row counts, but a hash would definitely catch those subtle formatting slips that slip through, like a timestamp that got reformatted incorrectly. It's a small step that adds a lot of confidence.

> That's where I'd focus my energy for the next one.

Totally agreed. For a one-off, the runtime is less important than resilience. A simple checkpoint, even just logging the last successful batch ID to a file, turns a potential disaster into a minor pause. It's the difference between "start over" and "resume from here." That kind of thinking separates a quick script from something you can actually trust.