Data migration configuration reference¶

This page documents the workflow YAML and Worker TOML settings for Data Migration. For platform-specific prerequisites, auth methods, extraction strategies, and data type mappings, see the per-platform pages linked from Data migration.

For SnowConvert AI CLI commands that generate and submit these configs, see Data migration.

Workflow configuration reference¶

The Data Migration Workflow configuration file is a YAML file. The CLI and Snowflake AIM Agent for Data Warehouses accept .yaml or .yml files.

Top-level object¶

When defaultTableConfiguration is present, each object in tables is merged with those defaults: shared fields apply to every table unless the same field is set again on a specific table entry.

TableConfiguration model¶

Defines the settings for migrating a single table.

QueryModifiers model¶

SourceTargetIdentifier model¶

Additional target properties¶

The following optional fields apply only to the target object.

Iceberg configuration (target.icebergConfig)¶

Used when target.tableType is "iceberg". Account setup follows Snowflake’s Apache Iceberg™ tables documentation.

Partition size¶

Partition size is controlled at the TableConfiguration level with two flat, mutually exclusive fields: targetPartitionSizeMb and targetPartitionSizeRows. When both are omitted, the orchestrator uses auto sizing.

Specify at most one of the two fields; setting both on the same table is a configuration error.

Automatic partition key selection¶

When columnNamesToPartitionBy (and, for incremental sync, primaryKeyColumns) is omitted for a table that needs partitioning, AIM DMV infers a key automatically from source catalog metadata rather than scanning the full table sequentially:

1. Clustered, sort, or distribution key columns, when the source platform exposes one.
2. A unique index, when no clustering key is available.
3. The first non-boolean column in the table’s schema, as a last-resort fallback. Boolean columns are never selected because they don’t provide enough distinct values to split partitions evenly.

Inferred keys are cached per source table and reused by later workflows against the same table, so inference runs only once per table. A manually specified columnNamesToPartitionBy (or primaryKeyColumns) always takes precedence over an inferred key.

Set columnNamesToPartitionBy explicitly when you know a better partitioning column than the one AIM DMV would infer, or when a fallback selection (such as a non-unique first column) would create unevenly sized partitions.

Each configured name must be a real, physical column in the source table. Persisted computed columns (SQL Server) and virtual columns (Oracle) qualify. Bare SQL expressions, pseudo-columns (for example Oracle ROWID, ROWNUM, or ORA_ROWSCN), and hidden system columns are not valid: AIM DMV quotes partition key names as identifiers in ORDER BY and range predicates, so those values won’t resolve. To partition on a derived value, add a persisted or virtual computed column on the source and reference that column name.

Load segmentation¶

When a large number of files are staged for a single partition (common with Redshift UNLOAD), the orchestrator can split the COPY INTO into multiple parallel statements, each targeting a subset of files. All resulting statements fan in to the same successor task.

When loadSegmentation is omitted, a single COPY INTO loads all files in the partition (the default). Files larger than the target size get their own segment, and Snowflake’s 1,000-file FILES limit is enforced per statement.

    loadSegmentation:
      targetSegmentSizeMb: 5000

ExtractionStrategy model¶

See Extraction strategies for YAML examples and platform availability.

SynchronizationStrategy model¶

Affinity¶

Affinity routes workflow tasks to specific Workers. Use it when you run more than one Worker pool and need each workflow to land on the right pool, for example SPCS Workers for one source and on-premises Workers for another, or separate pools per team.

Set affinity on the workflow YAML and the same value (or a matching wildcard) on the Worker:

• Workflow: top-level affinity in the migration or validation YAML
• Worker TOML: [application].affinity
• SPCS service: AGENT_AFFINITY in the container environment

The string format is yours to define. Examples of valid values: sql-server, DEV_SERVER, onprem-redshift, ::blue::.

Matching rules:

• A task without affinity is picked up by any Worker.
• A Worker without affinity picks up any task.
• A task with a given affinity is not picked up by a Worker with a different affinity.
• A Worker affinity may include * wildcards. For example, Worker affinity team-* matches workflow affinities team-blue and team-red.

Validation workflows use the same affinity field and the same matching rules as migration.

INTERVAL data type handling¶

AIM DMV migrates source INTERVAL columns to a native Snowflake INTERVAL target by default, extracting each value as Snowflake-parseable text and loading it with an explicit cast. Set intervalHandling at the workflow root, defaultTableConfiguration, or on an individual table to control this behavior.

Neither option is universally better. On source 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 (via total seconds on PostgreSQL). That keeps a native interval column but loses calendar-accurate year-month precision. Choose "varchar" to preserve the original interval text instead. See INTERVAL columns and intervalHandling on the PostgreSQL migration page for the tradeoff.

tables:
  - source:
      databaseName: MY_DB
      schemaName: sales
      tableName: orders
    target:
      databaseName: TARGET_DB
      schemaName: sales
      tableName: orders
    intervalHandling: interval
  - source:
      databaseName: MY_DB
      schemaName: sales
      tableName: legacy_intervals
    target:
      databaseName: TARGET_DB
      schemaName: sales
      tableName: legacy_intervals
    intervalHandling: varchar

Iceberg targets always coerce INTERVAL columns to VARCHAR, regardless of intervalHandling, because Iceberg has no native interval type. See platform-specific notes on the Migrating Data from … pages for interval support by source platform.

Extraction strategies¶

Each table can set extraction.strategy (or inherit from defaultTableConfiguration.extraction). Worker TOML holds connection and platform-specific options (UNLOAD credentials, WRITE_NOS location, TPT tuning).

Regular (default):

tables:
  - source:
      databaseName: MY_DB
      schemaName: sales
      tableName: orders
    target:
      databaseName: TARGET_DB
      schemaName: sales
      tableName: orders
    columnNamesToPartitionBy:
      - order_id

Redshift unload:

    extraction:
      strategy: unload
      externalStage: TARGET_DB.PUBLIC.S3_EXTERNAL_STAGE

Teradata write_nos:

    extraction:
      strategy: write_nos
      externalStage: TARGET_DB.SALES.MY_CLOUD_STAGE

Oracle dbms_cloud:

    extraction:
      strategy: dbms_cloud
      externalStage: TARGET_DB.DATA_MIGRATION.ORA_EXPORT_STAGE

Teradata tpt:

    extraction:
      strategy: tpt

Worker configuration¶

The Worker configuration file uses TOML (https://toml.io/) format. For cloud data migration, set selected_task_source to "snowflake_stored_procedure" and provide a matching [task_source.snowflake_stored_procedure] section.

Example minimal Worker config:

selected_task_source = "snowflake_stored_procedure"

[task_source.snowflake_stored_procedure]
connection_name = "my-snowflake"

[application]
max_parallel_tasks = 4
task_fetch_interval = 30

[connections.source.sqlserver]
username = "username"
password = "password"
database = "database_name"
host = "127.0.0.1"
port = 1433

[connections.target.snowflake_connection_name]
connection_name = "my-snowflake"

Platform-specific [connections.source.*] examples live on each Migrating Data from … page.

Using a custom extraction plugin¶

Set plugin_class on the source connection to replace the built-in extractor entirely. The Worker imports and runs your class in place of the regular (or other built-in) strategy for that connection:

[connections.source.sqlserver]
plugin_class = "acme_ext.my_extractor.MyExtractor"
username = "my_user"
password = "my_password"
database = "my_database"
host = "db.example.com"
port = 1433

See Preparing a custom extraction plugin for what the plugin must implement and how to package it.

Anti-locking and query modifiers¶

User-configured anti-locking hints are off by default. Unless you set queryModifiers, AIM DMV runs plain source queries. Modifiers apply to source queries only, never the Snowflake target.

Source platforms fall into two groups:

• MVCC / snapshot engines (Oracle, PostgreSQL, Amazon Redshift, and Snowflake itself): readers don’t block writers, so anti-locking hints aren’t needed.
• Lock-based engines (SQL Server and Azure Synapse): shared locks on reads can block or be blocked by production writers. Opt-in hints such as WITH (NOLOCK) avoid blocking but use read-uncommitted semantics (dirty reads). AIM DMV never auto-applies these hints.

Teradata is a special case: AIM DMV prepends LOCKING ROW FOR ACCESS on every Teradata source scan automatically. This is Teradata’s native, safe row-access pattern (not a dirty read) and cannot be disabled.

Per-platform behavior¶

Pros and cons¶

Workflow example¶

Set a default on defaultTableConfiguration and override per table when needed:

defaultTableConfiguration:
  queryModifiers:
    objectModifier: " WITH (NOLOCK)"
tables:
  - source:
      databaseName: MY_DB
      schemaName: sales
      tableName: orders
    target:
      databaseName: TARGET_DB
      schemaName: sales
      tableName: orders
    queryModifiers:
      objectModifier: " WITH (NOLOCK)"

Worker TOML example¶

You can also set query modifiers in Worker TOML under the source connection:

[connections.source.sqlserver.query_modifiers]
object_modifier = " WITH (NOLOCK)"
select_modifier = ""

Related content¶

• Data migration
• Data migration advanced configuration
• The SNOWCONVERT_AI database
• Data validation configuration reference
• Data migration (CLI)