Data migration advanced configuration

Data migration Workflows and Workers have a full YAML and TOML configuration surface documented in Data migration configuration reference. Most projects don’t need to hand-edit that file: the Snowflake AIM Agent for Data Warehouses generates it for you and walks through the fields that matter for your source platform, then shows you the result before anything runs.

This page describes common situations where you’ll want to go beyond the defaults, the prompt that gets you there, and where to find the exact property if you want to review or edit it yourself.

Tip

After the agent generates a workflow, it always shows you the YAML and asks whether you want to change anything before running it. You can request any of the scenarios below at that point, or ask for them up front.

Choosing an extraction strategy

Most platforms support a server-side export strategy that writes data directly to object storage (for example unload for Redshift, dbms_cloud for Oracle, write_nos for Teradata). When your source supports one and the prerequisites are in place (external stage, credentials, platform permissions), use it for all tables from that source. Data never flows through the Worker, and you avoid mixing regular with a server-side strategy on the same source without a good reason.

Use regular extraction only when data volume is genuinely small, or when object storage and the external stage aren’t set up yet. Once prerequisites are ready, switch the whole workflow to the server-side strategy rather than reserving it for “large tables only.”

Prompt:

Use UNLOAD extraction for all tables in this migration

The agent selects the bulk strategy for your source platform, sets up the required external stage, and sets extraction.strategy and extraction.externalStage across the workflow. See Extraction strategies for the full list and platform availability.

Overriding partition keys

AIM DMV partitions each table for parallel extraction. When you don’t specify columnNamesToPartitionBy, it infers a key automatically from source catalog metadata: a clustered, sort, or distribution key when the source platform exposes one, otherwise a unique index, otherwise the first non-boolean column as a fallback. Inference can happen when the workflow runs, so you won’t always see the chosen key up front. A value you set yourself always takes precedence over an inferred one.

If you already know a good partition column, set it explicitly. That avoids uneven partitions from a weak fallback selection on large or unusually shaped tables.

A partition key must be a real, physical column name (single or composite). Persisted computed columns (SQL Server) and virtual columns (Oracle) qualify because they appear as real columns in the source catalog. Bare SQL expressions (for example MOD(id, 10)), pseudo-columns (for example Oracle ROWID, ROWNUM, or ORA_ROWSCN), and hidden system columns (for example SQL Server %%physloc%%) are not valid partition keys: AIM DMV quotes each configured name as an identifier, so those values won’t resolve. To partition on a derived value, add a persisted or virtual computed column on the source and name that column in columnNamesToPartitionBy.

Note

Pseudo-columns and system columns can be used as watermark columns for incremental sync (for example Oracle ORA_ROWSCN), but not as partition keys. See Incremental sync after the first load.

Prompt:

Partition the ORDERS table by ORDER_DATE

The agent sets columnNamesToPartitionBy on the tables you name. See Automatic partition key selection.

Note

Views don’t expose the catalog metadata needed for automatic inference. If your migration includes a view, tell the agent which column to partition by.

Migrating INTERVAL columns

AIM DMV can migrate source INTERVAL columns either to a native Snowflake INTERVAL (intervalHandling: "interval", the default) or to text (intervalHandling: "varchar"). Neither option is universally better.

On platforms whose interval type can mix year-month and day-time fields in one value (for example PostgreSQL), "interval" maps the column to Snowflake INTERVAL DAY TO SECOND and folds the year-month part into day-time. That keeps a native interval column, but months and years are treated as fixed-length spans, so calendar-accurate year-month precision is lost. Choose "varchar" when you need to preserve the original interval text, including exact year-month fields.

Prompt:

For the EVENTS table, store INTERVAL columns as text instead of native INTERVAL so we keep exact year-month fields

The agent sets intervalHandling: "varchar" on the tables you name (or leaves the default "interval" when you prefer a native day-time interval). See INTERVAL data type handling for the property reference, and your Migrating Data from … page for platform-specific notes.

Note

Apache Iceberg™ targets always store INTERVAL columns as VARCHAR, because Iceberg has no native interval type.

Preparing a custom extraction plugin

The built-in extraction strategies (regular, unload, write_nos, dbms_cloud, tpt) cover most sources. If your environment needs a different extraction path, for example a proprietary driver, a bulk-export tool not covered by a built-in strategy, or a custom authentication flow, you can supply your own extraction plugin and register it on a Worker’s source connection.

At a high level, preparing a plugin means:

  1. Implement the extraction contract. Write a class that, given a query or export statement, produces the extracted data at a destination the Worker can pick up. Most plugins only need to implement the “write the dataset” step; the Worker handles staging and cleanup around it.
  2. Match the expected output format. Write Parquet (preferred) or a delimited CSV with no header row, so the Worker’s loader can read it the same way it reads built-in extractor output.
  3. Package it so the Worker can import it. Install it as a Python package on the Worker, or make it available on the Worker’s PYTHONPATH.
  4. Register it in the Worker configuration. Set plugin_class on the relevant [connections.source.*] section to the plugin’s fully qualified class name.

Prompt:

I have a custom extraction plugin for this source. Help me register it in the Worker configuration.

The agent points you to the Worker TOML for your connection and sets plugin_class for you once you confirm the class name and that it’s importable on the Worker. See Using a custom extraction plugin in the configuration reference for the exact TOML property and an example.

Warning

A Worker imports and runs plugin_class in-process with its own privileges. Only point plugin_class at code you trust, and set it in Worker configuration only, never as part of a workflow that could be supplied by someone else.

Incremental sync after the first load

Once an initial full load completes, switch qualifying tables to incremental synchronization instead of re-extracting everything on every run.

Prompt:

Set up incremental sync for the ORDERS table using the UPDATED_AT column

The agent sets synchronization.strategy: watermark and watermarkColumn, or checksum when there’s no reliable watermark column, and asks whether to also track deletions. The watermark can be a pseudo-column or system column not in the migrated schema (for example Oracle ORA_ROWSCN when there’s no UPDATED_AT column). For checksum, you can supply a custom aggregate expression such as MAX(ORA_ROWSCN). See SynchronizationStrategy model.

Migrating to Apache Iceberg™ tables

On supported source platforms, you can land migrated data in Apache Iceberg™ tables instead of native Snowflake tables.

Prompt:

Migrate the SALES schema to Iceberg tables using my external volume MY_EXTERNAL_VOLUME

The agent sets target.tableType: "iceberg" and fills in target.icebergConfig for you. See Iceberg configuration.

Reducing lock contention on the source

If migration reads are blocked by production writers, you can opt in to anti-locking query hints. This trades consistency guarantees (dirty reads) for reduced blocking, so use it only when locking is actually a problem.

Prompt:

Source reads are getting blocked by production traffic. Can we reduce locking during migration?

The agent explains the platform-specific tradeoff and, if you confirm, sets the appropriate queryModifiers (for example WITH (NOLOCK) on SQL Server). See Anti-locking and query modifiers for per-platform defaults and the pros and cons.

Renaming or remapping columns and types

Prompt:

Rename CUST_NM to CUSTOMER_NAME during migration, and map the source NUMBER(38,10) columns to a smaller precision

The agent sets columnNameMappings and columnTypeMappings on the affected tables. See TableConfiguration model.

Limiting a migration to a subset of rows

Useful for an initial test run or a table you only want to partially migrate.

Prompt:

For the first test run, only migrate orders where IS_DELETED = 0

The agent sets whereClauseCriteria on the affected table. See TableConfiguration model and Initial testing.

Related content