Data migration

The Data Migration feature provides a fault-tolerant, scalable solution for moving data from external sources into Snowflake. This feature is specifically designed for cases where you are moving data from a system you plan to decommission.

Architecture overview

The Data Migration feature uses two main components: an Orchestrator and one or more Workers.

  • The Orchestrator connects to the Snowflake account. It requires privileges to create and operate the SNOWCONVERT_AI database, where metadata is stored.
  • One or more Workers connect to both the source system and the Snowflake account. Workers read data from the source system and upload it to a Snowflake stage. Workers pick up tasks created by the Orchestrator and process them in parallel.
  • Files uploaded to the Snowflake stage are copied into the target tables using a COPY INTO statement submitted and monitored by the Orchestrator.

Deployment options

The Orchestrator and Workers can be deployed in multiple ways:

  • Both on Snowpark Container Services (in the Snowflake account).
  • Both in the customer’s environment, including custom hardware, virtual machines, or containers.
  • Orchestrator on Snowpark Container Services and Workers in the customer’s environment, or the other way around.

Prerequisites

Before you use Data Migration, make sure the following are in place:

  • Snowflake AIM Agent for Data Warehouses: The agent will install and manage the worker and orchestrator runtimes and their dependencies as needed.
  • Snowflake access: Connections for the Orchestrator and Workers in your Snowflake config.toml or connections.toml, using a role that can create the SNOWCONVERT_AI database and its objects. The first time the Orchestrator starts, it creates that database and related resources if they do not exist yet. On later runs, use a role that can administer SNOWCONVERT_AI and its objects; sticking with the same role you used for the initial creation is the simplest way to avoid permission issues.
  • Source connectivity: For typical source databases, an ODBC driver on the machine where Workers run. Oracle Workers require Oracle Instant Client and a registered ODBC driver. PostgreSQL Workers use the bundled Npgsql driver (no ODBC install). Programmatic Access Tokens (PATs) are recommended for Snowflake connections; see Connecting to Snowflake with a PAT.
  • Hybrid Tables: Hybrid Tables must be enabled and available in your Snowflake account and region for this feature. Review Hybrid tables and Hybrid tables limitations so you understand relevant platform requirements.
  • Snowpark Container Services (optional): If you deploy the Orchestrator or Workers on Snowflake compute, your account needs SPCS. See the Snowpark Container Services overview. Running both components outside Snowflake does not require SPCS.

Source-platform specifics

The Worker connects to your source using the TOML [connections.source.*] sections described in Worker configuration. Use the tabs below for connectivity, extraction style, and target options that are unique to each platform.

For Teradata only, the migration workflow’s extraction.strategy chooses among regular (default: partitioned SQL through the Worker to Parquet, then the internal migration stage), write_nos (Teradata WRITE_NOS writes Parquet to S3, Azure Blob, or GCS; Snowflake loads via an external stage you align to that bucket), and tpt (the Worker runs TPT EXPORT with tbuild, converts delimited output to Parquet, then uses the same internal stage path as regular).

  • Driver: Install a supported Microsoft ODBC Driver for SQL Server on the Worker host (the Worker auto-detects a suitable driver when none is set). You can pin a driver with odbc_driver in TOML.
  • Authentication: Use SQL authentication (username / password) or Windows integrated security (use_windows_auth = true on Windows Workers).
  • Encryption: Optional encrypt and trust_server_certificate keys follow ODBC Driver 17 vs 18 defaults; set explicitly for hardened or lab environments.
  • Bulk copy: Optional use_bcp = true enables BCP-style bulk extraction when supported for faster pulls from SQL Server.
  • Extraction path: Data is read through the Worker using regular extraction (ODBC/BCP). Size partitions with partitionSize and columnNamesToPartitionBy in the workflow YAML.

Usage

To migrate data using this solution, complete the following high-level steps:

  1. Start the Orchestrator.
  2. Start the Workers.
  3. Create a Data Migration Workflow.
  4. Monitor the Data Migration Workflow until completion.

A Data Migration Workflow is essentially an action or goal for the system to complete, such as migrating a specific set of tables with a given configuration. You can submit multiple workflows simultaneously and monitor them. The Orchestrator breaks Data Migration Workflows into smaller tasks, which typically involves splitting a table into partitions before extracting its data and loading it to Snowflake.

Ask the Snowflake AIM Agent for Data Warehouses to run cloud data migration steps by prompting

Migrate data

Considerations and recommendations

Connecting to Snowflake with a PAT

It is recommended to use Programmatic Access Tokens (PAT) for connections used by the Orchestrator and Workers. This ensures there is no need to constantly authenticate through the browser or with an authenticator app. You will need to establish a Network Policy or temporarily bypass the requirement for a Network Policy (this can be done from Snowsight).

Running Orchestrator and Workers on SPCS

To leverage Snowflake compute for these tasks:

  1. Prepare Docker images that bundle the Orchestrator and Worker runtimes and configuration (see Snowflake guidance for SPCS service images).
  2. Push those Docker images to an Image Repository in Snowflake.
  3. Execute the Orchestrator and/or Worker images using Snowpark Container Services.

Keep the following in mind:

  • It is recommended to execute them as Services, not Jobs.
  • It is possible to run only one component (Orchestrator or Workers) in SPCS and the other on a different platform.
  • It is a good practice to monitor the SPCS service and suspend it when it is not being used.
  • Depending on the network configuration of the source system, you may need to configure an External Access Integration so that these services can connect to your source system.

Initial testing

It is recommended to deploy the DDL for the tables you want to migrate before starting data migration. This ensures the target type matches the behavior you want to see in the table and its related views and procedures. You can convert DDL from your source dialect into Snowflake SQL using the code conversion capabilities of the Snowflake AIM Agent for Data Warehouses.

Note

If you don’t deploy the DDL for the tables before starting data migration, the types will be inferred, which may not be as accurate as required.

Managing Workers

The time it takes to complete a workflow depends on many variables, but the number of Workers (and threads per Worker) has the greatest impact, as it determines how many extraction tasks can be executed in parallel. Consider the following:

  • It is not necessary to run two Workers on the same machine. If you want more parallelism on a single machine, increase the thread count instead.
  • Network bandwidth greatly affects Worker speed and is shared between threads of a Worker.
  • Even with many Workers and threads running in parallel, the source system might not have enough resources to handle the load.
  • Keep a low Worker count to avoid overloading your source system.
  • Consider stopping some or all Workers when the source system is already under heavy load from other operations.