Snowflake Migration Series — Lesson 3: Unearthing the Logic: A Realistic Guide to Business Logic Translation
The Reality: Tools are Accelerators, Not Finishers
tl;dr
When migrating data warehouses, one of the biggest challenges is translating years of business logic from an older system like Informatica to a newer, more flexible platform like dbt. It’s easy to wish for a quick, magic button solution, but the reality is that this process is complex and requires careful work. The key is to change how we see this task: it’s not just about converting code, but about uncovering and thoughtfully redesigning the underlying business logic to better support the organization. We assess first (scan) to map what’s safe to automate, then convert what’s safe and redesign the rest.
In Lesson 1, RetailCorp shipped a thin, working slice. In Lesson 2, we assessed the legacy world while we kept building. Now we face the messy heart of migration, which means years of business rules spread across stored procs, webservices, ETL tools, and scripts. The goal is not to copy code line-by-line. The goal is to carry the meaning of the logic into a simpler, Snowflake‑native shape.
The Challenge
At RetailCorp, we found hundreds of routines doing the same thing in different ways. Some updated facts row by row. Some used lookups that made sense years ago. When we tried a straight convert, it ran for the most part, but it was slow and confusing. When we stepped back and asked, What is this trying to do for the business? What is the value the business needs? The answer was simple: upsert orders, keep history, calculate totals the same way every time. We rebuilt those ideas with fewer moving parts.
Tools Are Just Accelerators
Automated conversion tools are powerful and even required. They can speed up migration by handling a big volume of transformations. However, expecting these tools to create a perfect, one-to-one conversion is a mistake. The truth is, these tools are accelerators to help us get to value faster, and they will not finish the job. They give you a starting point, but a successful migration requires a tool-assisted, expert-led approach. AI is even accelerating the success you get from tools. You need to budget for tools to handle most of the work and for expert humans to oversee the most complex and valuable parts of the process.
Rethinking Logic, Not Just Rewriting Code
When it comes to a legacy system, the most crucial patterns often can't be translated directly. For instance, a tool might convert an Informatica dynamic lookup into pure SQL, but the outcome would be extremely inefficient on a platform like Snowflake. We need to translate the intent, not the syntax.
At this point, having an expert in the loop becomes crucial. Someone who thoroughly understands both the source and target platforms recognizes that the goal is to capture the business intent, not the exact code. They'd identify the inefficient tool output and redesign the pattern to utilize a native feature of the new platform, such as an intermediate table or statement in Snowflake. This expert-led redesign ensures the new system is not only accurate but also efficient and easy to maintain.
That being said, from what I've seen, you can guide the system with best practices. Essentially, since your code converter relies on AI, you or your vendor can provide your own expertise. I might even share those guidelines in a future post.
First, we scan with the converter in assessment mode to identify what’s auto-friendly and what needs redesign. Then, we convert the safe parts in conversion mode and send everything else to engineers for native Snowflake/dbt patterns. That’s the entire tool-assisted, expert-led process.
A Tool-Assisted, Expert-Led Strategy
Setting realistic expectations is really important. A good migration plan recognizes that while tools can help speed up about 70% of the translation work, the remaining 30% needs a skilled human touch. This isn't a sign that the tools are failing; it's simply the reality when moving complex business logic that has been developed over many years. It’s that human expertise that truly makes automation effective.
Remember to handle migrations thoughtfully. Doing so helps ensure your business transitions smoothly without losing any of the key elements that keep it running well. Never lose focus on the value to RetailCorp you are trying to create.
How AI Enables Expertise
LLMs are the perfect partner for experts in this tool-assisted, expert-led model. They serve as a powerful co-pilot, boosting the expert’s ability to deliver high-value redesign work.
- Speeding Up Redesigns: An expert can share inefficient, tool-generated code with an LLM and request a platform-native redesign. For instance: "Rewrite this cursor-based SQL logic as a single, set-based MERGE statement optimized for Snowflake."
- Brainstorming New Patterns: When dealing with a complex legacy pattern, an expert can use an LLM to explore different options. For example, they might ask, "What are the typical patterns for implementing transaction control in dbt on Snowflake? What are the pros and cons of each approach?" Sometimes it could be easier to do this part with Snowflake Cortex complete, as it has the context of the DDLs for schemas and tables, or pass those as part of AI questions.
- Documenting Intent: Once an expert rewrites a piece of logic, they can use an LLM to create documentation for the new approach that the rest of the team can follow. For example, "Explain why this MERGE statement is a more efficient way to implement the original dynamic lookup logic, and document it in Markdown format."
- Troubleshooting Faster: If you cannot immediately identify the error and why it is failing, run the error through AI for faster diagnosis. It is much faster to collaborate with the expert to reach a conclusion instead of spending hours or days trying to find a solution or waiting for support. “Help me troubleshoot my problem, see error log #error# and give potential fixes.”
The List: Short Checklist for Logic Translation
1) Assess and Triage: Run a converter in assessment mode to get coverage % and hotspots, and tag each object: Auto (convert), Assist (AI + review), Manual (expert redesign).
2) Convert what’s safe: Batch-convert DDL and straightforward SQL and land output in a repo; open a PR labeled “Converted (needs review)”.
3) Redesign the hard parts: Replace cursors/row-by-row with set-based MERGE,Turn nightly “monster jobs” into streams + tasks or dbt incrementals, and collapse “mystery joins” into clear, tested models with one owner.
4) Lock truth with tests: Add four basics: not_null, unique, relationships, accepted values, and treat tests as the contract between tech and business.
5) Use AI as a co-pilot, not a pilot: Ask for a Snowflake-native rewrite (e.g., “convert to one MERGE”), and generate first-pass docs and error explanations; humans approve.
6) Write down why: One paragraph per redesign: We changed X to Y because Red (impact: speed/cost/clarity).
7) Prove it: Diff legacy vs new for the top tables; attach a pass/fail report to the ticket, and green two cycles in a row = ready to cut over.
8) Celebrate and retire: Remove the old jobs. Close the loop with stakeholders. Celebrate.
Conclusion
By blending smart tools, expert insights, and the growing capabilities of large language models, you can more easily handle the tricky parts of translating logic. This approach helps you create a new platform that’s strong, fast, and up-to-date.
In the adventure of migrating business logic, remember that tools are like fast cars — they can accelerate your progress, but won’t get you to the finish line alone. While automated tools handle much of the heavy lifting, a skilled expert is essential to navigate the complexities and redesign logic for efficiency. Think of them as your trusty GPS, helping you avoid the potholes and ensure everything functions smoothly in its new home.
So, as you dive into this migration journey, embrace a tool-assisted, expert-led strategy. Mix the power of automation with human insight for a successful transition. With a dash of humor and a sprinkle of patience, you’ll not only keep your data safe but also transform it into something stronger and more efficient — just like a well-planned move! Happy migrating!
Keep a watch for Lesson 4, which is coming in a week or so. Meanwhile, catch up to the series, Lesson 1 and Lesson 2.
I am Augusto Rosa, a Snowflake Data Superhero and Snowflake SME. I am also the Head of Data, Cloud, & Security Architecture at Archetype Consulting. You can follow me on LinkedIn.
Subscribe to my Medium blog https://blog.augustorosa.com for the most interesting Data Engineering and Snowflake news.
Top comments (0)