3 Steps to Accelerate Data Warehouse Migrations to Snowflake

First, decide how many accounts you will need. In legacy platforms, you typically had database instances, but in Snowflake, the setup revolves around accounts. At a minimum, you should set up production and development environments. Depending on your testing strategy, you may also need additional environments for different stages of testing.

Once the environments are set up, it’s crucial to implement the proper security measures. Start with the network policy to ensure only authorized users within your VPN can access the Snowflake system.

Snowflake’s user access control is role-based, so administrators must define roles according to business needs. Once the roles are defined, create the user accounts and enforce multi-factor authentication (MFA) and/or single sign-on (SSO) for all users. Additionally, you’ll need to set up service accounts for all the automatic periodic processes.

During migration, you’ll also need to define specific roles for the users executing the migration itself. Although the roles for nonproduction environments may differ, remember that during migration, you will be dealing with real data, so don’t skimp on security. 

In development, the migration team generally has more freedom when deploying changes to the structure or code. These are active development environments, and you don’t want to block the migration team with excessive security restrictions. However, it’s still important to maintain a robust security model, even in nonproduction environments.

Since the security model in Snowflake differs from that of many legacy platforms, this migration is a good opportunity to rethink your access model. Clean up the hierarchy of users who need access to your system and ensure only the necessary users can access specific resources.

Snowflake uses a consumption-based pricing model, meaning costs are tied to usage. As you define roles, coordinate with your finance team to track which departments are using Snowflake and how. Snowflake also allows you to tag database objects, which can be used to track ownership at the business level, helping align usage with departmental cost allocation.

Security and environment setup are complex tasks that need to be planned upfront. You may consider redesigning your access model to ensure the new platform is manageable in the long run. Setting this up correctly will lay a strong foundation for a secure and efficient migration to Snowflake.

With the environment set up, the security model updated, and the DDL model converted, deployed objects in Snowflake are structurally equivalent to those in the original platform. The next recommended step is data migration.

It’s important to differentiate between historical data migration and new data addition. Historical data migration refers to taking a snapshot of the data at a specific point in time and transferring it to Snowflake. It’s recommended to first perform an exact copy of the data without any transformation into Snowflake. This initial copy will put some load on the legacy platform, so it’s best to do it only once and store it in Snowflake as something typically called the golden copy.

During this phase, you’re still stabilizing the ingestion processes. If an issue arises and you need to re-run any process, you can start from the golden copy without affecting the production legacy database. Additionally, many typical transformations — such as collation, null handling, trailing spaces, case sensitivity and encoding — are required to address the semantic differences between how the legacy platform and Snowflake handle data.

At the end of the migration, it’s recommended to store data in a native Snowflake format, using native data types. This is the stage where you must resolve the platform's semantic differences (and also make necessary adjustments during the code conversion phase). Important note: Business users need to be educated about these semantic differences, as they may notice discrepancies in the data, particularly during the testing phase. It’s essential they are prepared to handle these differences.

Once the golden copy is in place, you can share the transformed data in a new database. Snowflake features, such as cloning, allow you to use the data for development or QA without physically duplicating it. Cloning is an efficient operation since it doesn’t require physical data duplication.

Legacy systems and Snowflake may use different encodings. It’s crucial to account for this, especially to avoid incorrect handling of extended ASCII characters, which could affect downstream processes.

Another best practice concerns the size of the export files when moving data from the legacy system into Snowflake. Files in the 200–400 MB range are ideal for efficient loading. Snowflake can process data in parallel, so splitting the data into multiple files allows them to be imported simultaneously. After exporting the data from the legacy system, you can split it into multiple files, upload them to a cloud bucket, and use the COPY command to load the data into Snowflake.

After migrating the historical data, the next step is migrating the data ingestion process, bringing in live data from various sources. Typically, this process follows an extract, transform load (ETL) or extract, load, transform (ELT) model, depending on when and where the data transformation occurs before it becomes available to business users.

In the legacy system, the ingestion scripts are designed to send data to the old platform. Now, they need to be modified to send data into Snowflake.

This reinforces the importance of the planning phase. All legacy data sources and their associated ingestion pipelines need to be identified, and for each one, a clear migration path must be defined. There are several approaches to this:

1. Repointing the current pipeline: Redirect the existing ingestion pipeline so the destination database is now Snowflake, keeping the overall ingestion framework largely unchanged.
2. Redesigning the ingestion process: Alternatively, the ingestion process can be entirely redesigned. Some customers build their own custom ingestion frameworks, while others adopt industry-standard tools. While the motivation to avoid vendor lock-in is understandable, building a custom framework (often in Python) requires significant time and effort, which can sometimes be underestimated. This can have a notable impact on the overall migration timeline, especially when developing a custom ingestion framework for the first time.

Now that the database has both historical data and live pipelines continuously importing new data, the next step is to extract value from this data through BI. Reporting can be done using standard BI tools or custom queries. In both cases, the SQL sent to the database may need to be adjusted to meet Snowflake's requirements. These adjustments can range from simple name changes (common during migration) to syntax and more complex semantic differences. All these need to be identified and addressed.

As with the ingestion process, it's crucial to review all legacy platform usage and incorporate those findings into the migration plan. There are generally two types of reports to consider: IT-owned reports and business-owned reports. It’s usually easier to track down IT-owned reports, but business-owned reports and complex queries created by business users require a different approach.

Business users are a key stakeholder in the migration process and should be included in the RACI matrix during the planning phase. They should be trained on how Snowflake operates and should clearly understand the platform differences. This will enable them to modify their custom queries and reports as needed. Typically, a parallel training track for business users, followed by office hours with migration experts who can help address platform differences and guide users through the adjustments they need to make is recommended.

Business users are ultimately the ones who "accept" the migration. You might have completed the technical migration from an IT perspective, but if business users aren't involved, they may still rely on thousands of reports that are crucial for running the business. These reports must be updated to work with Snowflake to ensure the business can fully transition from the legacy platform.

Don't forget about the business users! Migration projects derail at the last minute when business users are "surprised" by the effort required to convert their custom reports.

At this stage, the data is validated, an equivalent system is set up, all the ETLs have been migrated, and reports have been verified. Ready to go live?

Not so fast — there are still a few critical considerations before final promotion to production. First, your legacy application may consist of multiple components or services. Ideally, you should migrate these applications one by one (although parallel migration is possible) and promote them to production in the same order. During this process, ensure your bridging strategy is in place so business users don’t have to query both Snowflake and the legacy system. Data synchronization for applications that haven’t been migrated yet should happen behind the scenes through the bridging mechanism. If this isn't done, business users will have to work in a hybrid environment, and they must understand the implications of this setup.

When you're finally ready for the cutover, ensure all stakeholders are aligned and understand that from this point forward, Snowflake will be the system of record, not the legacy platform. You’ll need final and formal sign-offs from all stakeholders before proceeding. Any reports that were not migrated are now the responsibility of the business users. This is why it’s crucial not to involve users at the last minute — they should be part of the process from the start and should be aware of the migration timeline.

Additionally, verify that all permissions have been properly granted. For example, if you are using active directory-based roles, ensure these are created and configured in Snowflake.

A few additional scenarios are typically left to the end, but they shouldn’t be overlooked:

• Surrogate keys: If you are using surrogate keys, be aware that their lifecycle may differ between the legacy and Snowflake systems. These keys need to be synchronized during the cutover.

• Cutover timing: Depending on your industry, there may be more or less favorable times during the year for performing a cutover. Consider the timing carefully.

Legacy platform licensing: Don’t forget you may face hard deadlines related to the licensing of the legacy platform. Be sure to plan your cutover around any such deadlines.

Once a system has been migrated to Snowflake, it enters normal maintenance mode. All software systems are living organisms requiring ongoing maintenance. This phase after migration is referred to as optimize and run, and it is not part of the migration itself.

Optimization and continuous improvement are ongoing processes that happen after migration. At this point, your team will take full ownership of the system in Snowflake. The system will continue to evolve, and optimization will be driven by usage patterns.

In general, jobs in Snowflake tend to run faster than on the original platforms. If performance doesn’t meet expectations, you may need to run some optimizations to fully leverage Snowflake's unique architecture. Snowflake provides various query analysis tools that can help identify bottlenecks, enabling you to optimize specific parts of the workflow.

During the optimization phase, you may need to revisit different aspects of the system. The advantage is you are already benefiting from Snowflake’s capabilities, and optimization tasks will become part of your regular maintenance routine.

As a recommendation, you should focus on addressing only critical performance issues during the migration phase. Optimization is best treated as a post-migration effort.