Share this on:
What You'll Learn
LumenData’s expertise in data platforms have enabled us to work with diverse clients, be it pharma, retail or fintech. Recently with one of our online retail clients ,we helped them with setting up Snowflake from scratch and migrating most of their workloads to the consolidated Snowflake warehouse.
As the client had a lean team of technical experts, we were tasked with migration of Snowflake objects(tables, views, stored procedures, and functions) from lower env to higher env i.e., (dev->qa and qa->prod).
We began with simple Snowflake based stored procedure, but it got evolved to Streamlit based application that helped both technical and non-technical stakeholders to migrate code from lower to higher environments.
The Migration Challenge
Data/code migrations between environments are a standard part of the data engineering lifecycle. However, this comes with several challenges:
- Technical Complexity
Migrations traditionally require SQL expertise and understanding of DDL operations. - Manual Processes
Copy-pasting and manually modifying DDL statements are error prone. - Inconsistent Naming Conventions
Ensuring proper database and schema name changes across environments. - Lack of Visibility
Difficulty previewing changes before execution, which can lead to wrong code pushed to higher environments. - Access Control Challenges
Managing correct permissions across environments has its own challenges.
LumenData’s Solution
As part of a standard practice, the data industry follows below practices:
- CI/CD development along with version control tools like(GitHub, Bitbucket).
- Commercial products like Snowflake's SnowConvert, Redgate, or Informatica.
- Using database replication features or third-party ETL tools.
We are in the process of building and standardizing these processes in upcoming phases. But for initial phases and kickstart the migration process we developed this solution:
- Helping technical stakeholders & non-technical stakeholders with limited expertise in both CI/CD and code migrations to easily execute processes.
- Customized solution tailored to their specific problem or workflow.
Our Streamlit based approach sits in a sweet spot and particularly valuable because:
- Solution is lightweight. No extra jargon or added complexity.
- It can be implemented quickly and maintained easily.
- We are leveraging Snowflake-native features and capabilities.
Finally, there is no universal “correct approach” – the optimal solution is dependent on specific client needs, technical maturity and expertise, team composition. Our solution specifically caters to organizations transitioning towards self-service data operations and maintaining appropriate controls with limited technical expertise.
The Snowflake-Streamlit Code Migration Tool
We have already discussed in detail reg client challenge and solution approach. Let’s deep dive into functionality and implementation.
Core Functionality
The application guides users through a simple workflow:
1. Source selection – Choose the source database, schema, object type, and specific object
2. Target Configuration – Select the target environment (QA/PROD) and customize database/schema names
In the highlighted section, we can see the application has skipped target environment creation as Target database and schema already exists. In case target database and schema doesn’t exist, it creates env automatically. (Need proper access and permissions for the same)
3. Preview changes – Review the original and modified DDL statements side-by-side
Once clicked on Initiate Migration Button, it validates source database schema, object and target database schema. If validation is successful, then it proceeds with previewing of Original and Modified Statements.
4. Confirmation and execution – Verify and execute the migration with proper safeguards
We can see from the above screenshot, once the user confirms the migration, the codes get migrated to respective environments.
Advantages of the UI-Based Approach
Democratized Data Operations
- The most significant advantage with our solution is it enables non-technical stakeholders to perform code migrations.
- Business analysts, QA teams, and project managers without SQL expertise can now participate in the deployment process.
- Reduces bottlenecks and dependencies on technical teams.
Process Standardization
- Consistent naming conventions across environments.
- Proper verification steps before execution
- Appropriate permission checks based on user roles
- Clear visibility into what's being migrated
Operational Efficiency
- Migration tasks that used to take around 15-30 minutes of careful SQL work are now completed in seconds.
- Elimination of configuration errors that often-required troubleshooting.
- Reduced need for technical assistance during routine migrations
Future Enhancements
We are still working on improving and enhancing this tool that would improve efficiency for larger releases.
Enhanced Validation: Working on adding strong pre-validation checks that could verify relevant dependencies exists in the target environment before attempting migration.
Migration Templates: We are in the process of creating migration templates that would further help the process for frequently migrated objects.
Simplified and Enhanced UI: Streamlit offers rich UI experience, and we are leveraging further to optimize and enhance UI to make it more user friendly.
Also Read: Migration from Oracle Exadata to Snowflake
About LumenData:
LumenData is a leading provider of Enterprise Data Management, Cloud and Analytics solutions and helps businesses handle data silos, discover their potential, and prepare for end-to-end digital transformation. Founded in 2008, the company is headquartered in Santa Clara, California, with locations in India.
With 150+ Technical and Functional Consultants, LumenData forms strong client partnerships to drive high-quality outcomes. Their work across multiple industries and with prestigious clients like Versant Health, Boston Consulting Group, FDA, Department of Labor, Kroger, Nissan, Autodesk, Bayer, Bausch & Lomb, Citibank, Credit Suisse, Cummins, Gilead, HP, Nintendo, PC Connection, Starbucks, University of Colorado, Weight Watchers, KAO, HealthEdge, Amylyx, Brinks, Clara Analytics, and Royal Caribbean Group, speaks to their capabilities.
For media inquiries, please contact: marketing@lumendata.com.
Authors
Senior Consultant
