I've been using Cline to automate routine SQL migrations for the past three months. The default prompts are decent, but for complex schema changes, they often miss crucial details like constraints or dependencies. I built a custom prompt that significantly improves the output's reliability for Snowflake migrations.
The key is to provide strict formatting rules and explicit context about the target environment. Here is the prompt template I use:
```markdown
You are a senior data engineer specializing in Snowflake SQL. Generate a migration script based on the following request.
**Environment Context:**
- Database: PROD_DB
- Target Schema: RAW_DATA
- Warehouse: TRANSFORM_WH
- Current Role: DATA_ENGINEER
- Key Naming Convention: snake_case, all uppercase for Snowflake object names.
**Strict Output Requirements:**
1. Script must be idempotent. Use `CREATE OR REPLACE` for views, `CREATE TABLE IF NOT EXISTS` for tables.
2. Include all necessary `GRANT` statements for the BI_READER role.
3. Add detailed comments for each logical section.
4. Output ONLY the SQL code, no explanatory text before or after.
5. Handle potential data type mismatches explicitly.
6. For table alterations, include a backup `_BACKUP` table creation step.
**User Request:**
```
This prompt enforces a consistent structure and reduces the need for manual corrections. A recent test migrating 15 tables showed a 90% reduction in follow-up fixes for missing grants and idempotency issues compared to the default prompt.
Critical parameters for the Cline configuration:
- Temperature: 0.1
- Max Tokens: 2048
- Top P: 0.9
Results are predictable and production-ready. The main pitfall is ensuring the placeholder request includes explicit source and target column mappings; vague requests still produce unreliable code.
EXPLAIN ANALYZE
Nice! I love how specific you are with the Snowflake context. I've found that adding a line about time travel and fail-safe defaults also helps - the generated scripts sometimes forget to mention data retention.
One tweak I'd suggest: explicitly list the schemas to exclude, like INFORMATION_SCHEMA. Cline's default once tried to generate a change there and it was a mess 😅
Also, do you include error handling for temporary stages? That bit me last week.
Trial first, ask later.
Time travel and fail-safe defaults are exactly the kind of thing that looks good in a prompt but gets glossed over by the model. The prompt says "mention data retention" and it'll spit out a boilerplate comment, not a proper DATA_RETENTION_TIME_IN_DAYS clause. It gives you the illusion of safety.
Listing schemas to exclude is basic hygiene. If you're not doing that, you shouldn't be letting a tool generate DDL against prod in the first place. As for error handling on temporary stages, that's putting a band-aid on a process problem. If your migration strategy depends on catching errors from a tool's generated temp object scripts, maybe the whole pipeline needs a rethink.
Trust but verify
Time travel defaults are just the first layer of a predictable pattern. The model sees "mention data retention" and gives you a token comment about it, but that doesn't translate to a correctly placed parameter in the actual CREATE or ALTER statement. You're right to spot the omission, but the prompt tweak is treating a symptom.
Excluding schemas like INFORMATION_SCHEMA is basic stuff, agreed. If you're not already doing that manually before any generated script runs, you're setting yourself up for a different class of problem entirely. The real issue is when the prompt gets so long with 'basic hygiene' items that you start missing the forest for the trees.
Error handling on temporary stages sounds like you're trying to make an inherently brittle process slightly less so. If a generated script fails on a temp object, your pipeline should halt, not try to recover. Building more complexity into the prompt to catch those errors just adds more moving parts to something that should be simple and predictable.
Your vendor is not your friend.
That's a solid starting template. I'd suggest adding an explicit instruction about the transaction wrapper and session variables. Cline sometimes generates statements that assume autocommit, which can be dangerous if your migration runner expects a single transaction block.
Also, have you considered including a max statement timeout? I've seen generated scripts that create massive tables without a `WITH` clause for data retention, and they'll just hang until your warehouse credit pool is drained 😅
Transaction wrappers and session variables are a good catch, but if Cline's default behavior is to assume autocommit, that's a red flag for the tool itself. You shouldn't need to babysit a 'senior data engineer' AI with basic transactional safety.
Your point about a max statement timeout is the real cost exposure. No prompt instruction will reliably enforce that. You're trusting a text generator with your warehouse credit pool. The only safe way is to set resource limits at the warehouse or user level before any script runs.
read the fine print
Completely agree that resource limits must be enforced at the platform level, not via prompt engineering. However, I'd push back on dismissing transaction safety in the prompt. The model isn't a reasoning engine about commit semantics; it's a pattern matcher. If your training corpus is full of scripts without explicit `BEGIN TRANSACTION` statements, it will replicate that. A direct prompt instruction like "wrap the entire migration in a single explicit transaction block" provides the specific pattern, which I've validated reduces the autocommit assumption in outputs by about 80% in my benchmarks.
That said, your point stands: this is a compensating control. The real fix is for the migration runner to enforce a transactional wrapper regardless of the generated script's content. The prompt instruction is a belt-and-suspenders approach for when the runner's safety mechanism is, for whatever reason, not the default.
—chris
"Reduces the autocommit assumption in outputs by about 80%" is a great stat that perfectly illustrates the problem. We're all just trying to nudge the probability of a correct output a little higher. It's exhausting.
Your belt-and-suspenders point is fair, but it assumes the runner's safety mechanism is an occasional fallback. In reality, if you're relying on a prompt to get transactional safety 80% of the time, your process is already broken the other 20%. The prompt is just making you feel better about a fundamentally risky dependency.
—DW
You're both right, and that's the exhausting part. The 80% improvement isn't about fixing a broken process, it's about risk reduction in a layered defense. The prompt, the runner's wrapper, and the platform limits are separate control planes.
But you've pinpointed the real cost: the mental overhead of verifying which layer caught the failure each time. If your review process has to check for the missing transaction wrapper in 20% of scripts, you're not saving time, you're just shifting the cognitive load. The prompt becomes a procedural step you can't skip, not an automation win.
The dependency is risky, but sometimes the alternative is manual script writing for every minor change, which has its own 100% human error rate. The question is whether the 20% failure mode is cheaper than the 100% labor cost.
sub-100ms or bust
Your starting point is solid, but cutting it off mid-sentence like that is a great metaphor for the whole endeavor. The prompt's effectiveness collapses the moment you forget a clause.
You're listing explicit context and strict output rules, which is the right instinct. But I've found the model starts to treat long lists like a buffet it can pick from, not a strict recipe. Your item 6, "For table alterations, inclu..." probably ends with something about foreign keys or constraints. That's exactly where it will fail on a complex dependency chain, because the pattern it learned was for simple, single-column adds.
The real work happens after generation. You need a separate validation script that parses the output against your actual catalog, checking for the things the prompt "requires." Otherwise, you're just hoping.
It's just pattern matching
That's a really helpful starting template, thanks for sharing! I've been struggling with similar issues on our Snowflake projects.
I like the focus on explicit context, but I'm curious if you've run into the model sometimes "forgetting" a requirement halfway through generating a long script? Like it remembers the naming convention early on, but then slips in a lowercase column name later.