Data migration scripts are one of those necessary evils—incredibly repetitive, error-prone, and rarely fun to write. I used to dread them. But over the last few months, I've refined a workflow using GitHub Copilot that has turned a tedious chore into something almost efficient. I thought I'd share my step-by-step process here, focusing on how I use it as a true collaborator, not just an autocomplete tool.
It starts with the prompt. I've learned that being overly specific in my code comments is the key. Instead of just writing a function name like `transformUserData()`, I'll write a detailed comment describing the exact transformation, the source and target data shapes, and any edge cases. For example: `// Take the legacy 'member' object, flatten the nested 'contact_info' array into a single 'email' string, and handle cases where the array is empty or null.` Copilot consistently generates a more accurate first draft from this.
Then comes the review and refinement loop. I never accept the first suggestion blindly. I'll ask it to adjust the generated code iteratively—things like "add validation for the date format" or "wrap this in a try-catch with logging." This back-and-forth feels less like writing from scratch and more like guiding a very fast junior developer who knows the syntax cold. It excels at the boilerplate, letting me focus on the business logic and error handling.
The real win has been consistency across files. When you're migrating dozens of tables, maintaining the same pattern for connections, logging, and error reporting is crucial. Once Copilot picks up the pattern from the first few scripts, it replicates it perfectly in the subsequent ones, which is a huge time saver and reduces mental overhead.
Has anyone else developed a similar collaborative workflow for boring but critical tasks? I'm curious if you've found other prompt strategies or if you use it differently for database work. The goal, for me, is to offload the repetitive typing and syntax so I can keep my attention on the architecture and data integrity.
— Eric
Keep it civil, keep it real.
Interesting approach, but I can't help thinking about the hidden cost. That back-and-forth refinement loop you describe? That's developer hours, and those are expensive. You're billing someone for the time spent coaxing a better try-catch out of an AI. For a one-off migration, fine. For recurring work, I'd argue it's cheaper in the long run to write a solid, reusable script by hand once, even if it's tedious upfront.
The real "tedium" in data migrations often comes from volume and runtime, not the initial writing. Where's your breakdown on how using Copilot impacted the actual execution time or the runtime cost of the generated code? I've seen these tools produce wildly inefficient joins or loops that murder a cloud bill when scaled.
pay for what you use, not what you reserve
You're right about the runtime cost being a hidden trap, but that's exactly why I use it this way. The detailed prompting forces me to articulate constraints I'd otherwise skip over in a rush to write it myself, like specifying "use a set for O(1) lookups" or "batch updates to avoid N+1 queries." When I write solo, I often optimize later. With Copilot as a foil, I'm building the performance spec into the prompt from the start.
Your point on developer hours is valid for recurring patterns. However, if the migrations are truly similar enough for a reusable script, you're already in a proprietary workflow that's ripe for vendor lock-in. The one-off nature of most legacy migrations means the "solid, reusable script" you write by hand is often shelved after the project, and its cost is sunk anyway. The real waste is then maintaining that unused artifact.
The specificity you mention in prompts is exactly what I've found works best for LLM coding tasks, too. It forces you to think through the data contract before a single line is written, which is a great practice regardless of the tool.
However, I'd push back on "never accept the first suggestion blindly" being a universal rule. For boilerplate CRUD operations or simple mappings, the first draft is often correct and accepting it saves cognitive load. The key is knowing which parts of your script *are* boilerplate versus which contain actual business logic complexity. That discernment is the real skill.
Have you tracked any metrics on how this changes your error rate versus writing from scratch? I'd be curious if the detailed prompting leads to fewer bugs in the first run, or just shifts the debugging phase earlier into the prompt engineering stage.
garbage in, garbage out
You're right about that discernment being the skill. In my work with infrastructure-as-code, accepting the first suggestion for a generic Terraform resource block is fine, but I'd never do it for a complex `lifecycle` rule or a nuanced network policy. The risk isn't just bugs, but introducing subtle security gaps or compliance drift.
On your metrics question: I haven't tracked formal error rates, but I've observed a clear shift. The bugs become architectural or integration-related, caught in the planning stage. Instead of a syntax error in a loop, you get a mismatch between your prompt's assumed API version and the actual provider, which surfaces when you run `terraform plan`. It moves the failure earlier, but the failures are often more expensive to fix if you miss them.