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 (CLI).

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.

Preflight: a bounded dry run¶

Setting preflight: true runs the migration as a bounded smoke test. Each table is capped at one partition, and the data lands in a transient PREFLIGHT_<workflowId> schema rather than your configured target schema. It exercises the whole pipeline (connectivity, extraction, staging, type mapping, and load) without touching production tables.

preflight: true
preflightKeepSchema: false # true leaves PREFLIGHT_<workflowId> in place for inspection

Run it the same way you run any other workflow. AIM DMV drops the transient schema when the workflow ends unless preflightKeepSchema is true.

Three related things are easy to confuse:

Use preflight when you want to prove the pipeline works end to end. Use whereClauseCriteria when you want a real sample of rows in the real target.

TableConfiguration model¶

Defines the settings for migrating a single table.

ColumnTypeMapping and ColumnNameMapping models¶

columnTypeMappings overrides the default source-to-target type mapping for a table, and applies to both the extraction query and the COPY INTO load. columnNameMappings renames columns on the way to the target. Set either on a table entry or on defaultTableConfiguration.

ColumnTypeMapping:

ColumnNameMapping:

defaultTableConfiguration:
  columnTypeMappings:
    - sourceType: SUPER
      targetType: VARCHAR
    - sourceType: GEOMETRY
      targetType: VARCHAR
  columnNameMappings:
    - sourceName: CUST_NM
      targetName: CUSTOMER_NAME

Apache Iceberg™ targets are the most common reason to need this. Iceberg has no equivalent for Snowflake VARIANT, OBJECT, ARRAY, GEOGRAPHY, or GEOMETRY, so map any source type that would land on one of those to VARCHAR or STRING. See Iceberg configuration.

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.

What makes a good partition key¶

A good partition key has high cardinality (many distinct values) and distributes rows roughly evenly across those values. AIM DMV splits the table by dividing the key’s value range into equal-sized buckets, so a column with few distinct values (for example a status flag with three possible values) or a heavily skewed distribution (most rows sharing one value) produces uneven partitions — some Workers receive far more data than others.

Composite keys: when no single column meets both criteria, supply two or more columns in columnNamesToPartitionBy. AIM DMV partitions on the combination, which multiplies cardinality and can smooth out skew. For example, if STATUS has low cardinality but (STATUS, REGION) together produce many well-distributed value pairs, use the composite.

Numeric and date columns — especially surrogate keys and timestamps — are the preferred choices: they have high cardinality, sort naturally, and let range predicates use source indexes efficiently. VARCHAR columns can also work, but avoid columns with consistently long text values (over roughly 1,000 characters): range predicates on very wide strings are expensive to evaluate and can slow extraction significantly.

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.

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¶

Accepted values for strategy:

See Extraction strategies for YAML examples and platform prerequisites.

SynchronizationStrategy model¶

Both incremental strategies carry a cost and a coverage caveat:

• checksum recomputes a checksum on the source for every partition on every run, so the detection pass itself has a fixed cost proportional to table size. A custom checksumExpression over an indexed row-version or SCN column is usually much cheaper than the default per-column hash.
• watermark only reads the watermark column, but it can’t see rows that changed without the watermark advancing.

Changes a checksum may not detect¶

The default partition checksum hashes a normalized subset of columns. Some types are skipped outright. A row whose only change falls into one of the categories below won’t be detected, so its partition won’t be re-extracted.

This applies to synchronization.strategy: checksum during migration and to incremental validation probes, which use the same hashing.

If one of these applies to a column you need to track:

• Truncate the target table and run a full re-migration (strategy: none) to reconcile.
• Switch to watermark if the table has a reliable monotonic column.
• Use row-level validation to detect the difference after the fact.

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 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.

Matching metadata locations between the Orchestrator and Workers¶

If you override where AIM DMV keeps its metadata, the Orchestrator and every Worker have to agree. The Orchestrator reads environment variables; Workers read the matching [application] TOML keys.

The database and migration schema keys are read from TOML or the CLI only at Worker runtime. Use the matching [application] TOML key on each Worker to match the Orchestrator’s environment variable.

See Customizing metadata database and schema names for the privileges these objects need.

Resolving credentials from an external secret manager¶

Worker TOML examples show credentials inline for brevity. On a local or otherwise non-SPCS Worker, you can instead resolve them at startup from an external secret store, so no plaintext password lives in the file.

Register providers in a separate TOML file and point the Worker at it with the SECRET_MANAGERS_CONFIG_FILE environment variable:

[secret_managers.providers.vault]
type = "rest"
base_url = "https://vault.example.com/v1/secret/data/dea"

Then reference a resolved value in a connection field using the provider scheme, naming the provider, path, and field:

[connections.source.sqlserver]
username = "my_user"
password = "vault://sqlserver/prod#password"

The [secret_managers] configuration goes in its own separate TOML file, not in the Worker TOML. Use SECRET_MANAGERS_CONFIG_FILE to point the Worker at it.

AIM DMV can also resolve a field by running a shell command, for example $( aws secretsmanager get-secret-value --secret-id prod/db )#password.

Environment variables¶

Most configuration lives in the workflow YAML and Worker TOML. These environment variables cover the settings that don’t:

Orchestrator:

Worker:

On SPCS, Snowflake injects service identity variables such as SNOWFLAKE_ACCOUNT, SNOWFLAKE_HOST, and SNOWFLAKE_WAREHOUSE automatically. Don’t set them yourself.

Metadata storage mode¶

AIM DMV keeps its orchestration metadata (the task queue, table metadata, and partition metadata) in Snowflake Hybrid Tables when your account supports them. At bootstrap it probes for Hybrid Table support and falls back to standard TRANSIENT tables when they’re unavailable, which is common on trial and lower-tier accounts.

Override the probe with SNOWFLAKE_METADATA_STORAGE_MODE when the result is ambiguous, for example when the account can’t create Hybrid Tables.

What to expect on the standard path:

• Correctness holds. Under heavy concurrent task claiming you may see more internal retries, but retries alone aren’t a sign of corruption.
• Metadata TRANSIENT tables have no Time Travel. Orchestration state can be rebuilt, but not recovered to a point in time. This doesn’t affect your migrated data.
• Task-queue throughput is lower. Each pull takes more round trips, and without hybrid secondary indexes the queue scan does more work. Latency degrades as the pending backlog grows.

Rate limiting¶

Rate limiting caps how many extraction tasks run concurrently against a source system or any other shared resource. Use it when extraction concurrency is overwhelming the source, and prefer it to lowering max_parallel_tasks globally or stopping Workers: Workers stay up and matching tasks simply wait in pending.

Rules are rows in the RATE_LIMIT table in your migration metadata schema, not properties in the workflow file. Each rule limits how many executing tasks whose SCOPE matches a SQL LIKE pattern can run at once. A task scope looks like Table[MY_DB.SALES.ORDERS]::Loading or Table[MY_DB.SALES.ORDERS]::Partition[3]::Extraction.

-- At most 5 concurrent loading tasks across any table:
INSERT INTO SNOWCONVERT_AI.DATA_MIGRATION.RATE_LIMIT (SCOPE_PATTERN, TARGET_CONCURRENT_TASKS)
  VALUES ('Table[%]::Loading', 5);

-- Cap partition loads for one workflow:
INSERT INTO SNOWCONVERT_AI.DATA_MIGRATION.RATE_LIMIT (SCOPE_PATTERN, WORKFLOW_ID, TARGET_CONCURRENT_TASKS)
  VALUES ('Table[%]::Partition[%]::Loading', 42, 2);

-- Pause every task against one source database:
INSERT INTO SNOWCONVERT_AI.DATA_MIGRATION.RATE_LIMIT (SCOPE_PATTERN, TARGET_CONCURRENT_TASKS)
  VALUES ('Table[MY_DB.%]::%', 0);

Remove a rule with DELETE, or disable it by setting ENABLED = FALSE. An empty RATE_LIMIT table means no limiting at all.

Cleaning up transient resources¶

AIM DMV temporarily stages data in Snowflake stages while a workflow runs. cleanUpTransientResources controls when those staged files are deleted. It’s a top-level property in both workflow types.

Underscores are accepted, so on_success works the same as on-success.

cleanUpTransientResources: on-success

Target tables, metadata rows, and anything in the source database are not removed, regardless of the setting.

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)