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, which loads into a transient copy of the target table in a PREFLIGHT_<workflowId> schema. Your configured target tables are never written to. Your Orchestrator and Workers send one partition per table along the same path a real load takes: source connection, extraction, staging, and the load into a table with your target’s columns and types.

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.

Before you run a preflight¶

• The real target tables must already exist. AIM DMV creates each transient copy with CREATE TRANSIENT TABLE IF NOT EXISTS <copy> LIKE <real_target>, so the copy takes its structure from the real target. A normal migration creates a missing native target from the source schema, but a preflight can’t: if the real target doesn’t exist, that table’s setup task fails. Deploy the converted DDL first. See CREATE TABLE … LIKE.
• The services must be running. A preflight uses the same Orchestrator, Workers, warehouse, and compute pool as a real load, and it’s billed the same way while it runs. If the services suspended themselves, resume them first. See Resuming services before the next wave and Controlling cost on SPCS.
• The migration role must be able to create schemas in each target database. See Target database privileges.

What a preflight does¶

Which rows a preflight loads¶

A preflight caps each table at one partition of its partition plan, not at a number of rows:

• A table that fits in one partition is loaded in full. With automatic partition sizing, a small table usually gets a single partition, so the preflight moves the whole table. That isn’t a sample, and it takes as long as a real load of that table.
• A table with several partitions loads only one. It’s picked by its position in the plan, not at random, and it can hold few rows or none. A table can complete with no rows loaded.
• The rest of the table isn’t read. Values that appear only in other partitions, such as unusually long strings or out-of-range dates, aren’t tested.

Reading a preflight result¶

A preflight that completes shows that, for each table, one partition was extracted, staged, and loaded into a copy of the real target, with the Workers, credentials, extraction strategy, and type mappings in this workflow. Check it as you would any workflow, with scai data migrate status <WORKFLOW_ID> or the TABLE_PROGRESS view and PARTITION_METADATA table described in SnowConvert AI database objects. AIM DMV records only the partition it ran, so those counts describe that partition, not the whole table.

A table that fails shows a problem to fix before the real load, such as a missing target table, source connectivity or credentials, the extraction strategy’s prerequisites, such as its external stage, or a value that doesn’t load into the target’s column type.

A completed preflight doesn’t show:

• How a full load performs. Duration, throughput, cost, and load on the source all depend on the partitions it didn’t run.
• That the rest of the data loads. Only one partition per table was read.
• That loads into the real targets succeed. A preflight never writes to them.
• That the migrated data is correct. A preflight doesn’t validate data. After the real load, run data validation.

To look at the rows a preflight loaded, set preflightKeepSchema: true. Otherwise the schema is dropped when the workflow ends.

When to run a preflight¶

Run a preflight before the first real load of a set of tables, once their DDL is deployed and the Workers are set up. It’s most useful when something in the path is new: the first wave, a new source server, a new extraction strategy, or a new Worker deployment.

Keeping the preflight schema¶

With preflightKeepSchema: true, AIM DMV doesn’t queue the drop task, and it restores CHECK constraints on the copies after the load so they match the real targets. The schema then stays until you drop it, and its tables keep using storage.

Inspect the copies:

SHOW TABLES IN SCHEMA SALES_DB.PREFLIGHT_42;
SELECT COUNT(*) FROM SALES_DB.PREFLIGHT_42.SALES_DB_PUBLIC_ORDERS;

When you’re done, drop the schema in each target database the workflow used:

DROP SCHEMA IF EXISTS SALES_DB.PREFLIGHT_42 CASCADE;

Cleaning up after a preflight¶

Without preflightKeepSchema, the drop is a cleanup task in the workflow. Once every other task has finished, whether it succeeded or failed, the Orchestrator runs DROP SCHEMA IF EXISTS <target_database>.PREFLIGHT_<workflowId> CASCADE in each target database. The schema stays until an Orchestrator runs that task.

You’re responsible for dropping:

• Schemas you kept with preflightKeepSchema: true.
• Any PREFLIGHT_<workflowId> schema left behind, for example when no Orchestrator ran the cleanup task.

The schema drop doesn’t remove staged files. cleanUpTransientResources governs those, as for any workflow. See Cleaning up transient resources.

Three related things are easy to confuse:

Use preflight when you want to check that one partition per table gets through the pipeline without touching the real targets. Use whereClauseCriteria when you want a real sample of rows in the real target.

TableConfiguration model¶

Defines the settings for migrating a single table.

Views and dynamic tables in migration¶

Data migration copies source tables into Snowflake tables. Before it extracts an entry, AIM DMV reads the source object’s type with the same catalog classification that validation uses (see How catalog kinds are classified):

• Table: the entry is migrated.
• View, including a materialized view: the entry fails before extraction with '<name>' is a VIEW, not a TABLE. Data migration does not support views. Remove the entry, or migrate the view’s base tables instead.
• Not found: the entry fails with a table-not-found error.
• Unrecognized catalog kind: the entry is migrated, and the task records a warning that the object type is unrecognized and results may be unexpected.

The target must be a Snowflake table or a Snowflake-managed Iceberg table. AIM DMV loads the target with COPY INTO, and incremental synchronization can also run MERGE and DELETE against it. Dynamic tables are read-only, so a dynamic table isn’t a supported migration target. AIM DMV doesn’t detect a dynamic-table target before it loads, so the load fails. To populate a dynamic table, migrate its base tables and let Snowflake refresh it.

Validation is different: it can compare a source view with a Snowflake table, including a dynamic table. See Source and target object types.

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.

Type mappings, storage choices, and how to validate the same tables are in Apache Iceberg™ tables (spec v2 and v3). On spec v3 with FULL_INGEST, a timestamp with an offset becomes TIMESTAMP_LTZ(6). TIME WITH TIME ZONE and TIMETZ also become TIMESTAMP_LTZ(6). The staged value has no date, so the load attaches it to 1970-01-01.

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 trackModifications and trackDeletions are off by default. With the watermark strategy, leaving them off changes what an incremental run does to updated and deleted source rows: updated rows are appended as a second version, and deleted rows stay on the target. See Choosing a synchronization strategy.

Choosing a synchronization strategy¶

The following table compares the strategies by what each one compares, what a run reloads, how it treats updates and deletes, and where it applies. Each strategy has its own subsection after the table.

none (default)¶

• Compares: Nothing. The run doesn’t read the target or the previous run’s state. The partition stores only the strategy name.
• Reloads: Every row in every partition, on every run.
• Inserts, updates, and deletes: Every run appends the extracted rows. Source updates and deletes never reach the target. A rerun against a populated target duplicates every row.
• Required keys or columns: None.
• Performance and cost: One full extract and load per run. Cost grows with table size, and the source does no change detection.
• Blind spots and failure cases: Rerunning against a target that already has rows duplicates them. Empty the target, or switch to full-replace, before a second load.
• Native and Iceberg: Supported on native targets and on Iceberg targets. A second none run against an existing Iceberg table fails with IcebergTargetExistsError instead of appending.
• Choose it when: The table loads once, such as the initial load of a set of tables, or the table is small enough to reload in full.
• Avoid it when: The workflow reruns against a populated target without clearing it, or the target must follow later source changes.

full-replace¶

• Compares: Nothing. Like none, it doesn’t compare the source with the target or with a previous run.
• Reloads: Every row in every partition, on every run. Before each partition loads, the target rows for that partition are deleted. An unpartitioned table is cleared with a whole-table delete.
• Inserts, updates, and deletes: After a successful run, the target holds the source rows for each partition the run covers. Updates and deletes are reflected because the partition is rebuilt. Any row in the target partition that the source doesn’t supply is deleted, including rows written by other processes.
• Required keys or columns: None. It doesn’t need a watermark or a primary key. It rejects checksumExpression and trackDeletions.
• Performance and cost: The same extraction cost as none, plus one DELETE on the Snowflake target per partition. The first load skips the delete because the target doesn’t exist yet.
• Blind spots and failure cases: The delete runs before the load. If the load fails after the delete, the partition has no rows until a later run completes. The next run overwrites any manual change to the target.
• Native and Iceberg: Native targets only. An Iceberg target is rejected at configuration validation, with a message that begins “Synchronization strategy ‘full-replace’ is not supported for Iceberg targets”. scai data migrate create-workflow accepts full-replace for an Iceberg target, and the orchestrator then rejects it. The delete runs on Snowflake, so it works for every supported source platform.
• Choose it when: You want a repeatable full reload that matches the source on each run, and the table has no reliable watermark or change signal. Use it to replace rows left by an earlier none run.
• Avoid it when: The target holds rows the workflow doesn’t own, the table is too large to reload on every run, or the target is an Iceberg table.

checksum¶

• Compares: One checksum per partition, computed on the source on every run and compared with the value stored by the last successful run. The default checksum hashes a normalized set of columns. checksumExpression replaces the hash with a SQL aggregate over the partition.
• Reloads: The checksum of every partition, on every run. A partition whose checksum changed is cleared and reloaded in full. Unchanged partitions aren’t extracted.
• Inserts, updates, and deletes: A change that moves a partition’s checksum is reflected for that whole partition, so updated and deleted rows in a changed partition are reflected too. A change the checksum can’t see isn’t reflected. See Changes a checksum may not detect.
• Required keys or columns: None. The partition columns set how much data a changed row reloads. checksumExpression must not contain ;.
• Performance and cost: The source computes the checksum of every partition on every run, even when nothing changed. Cost grows with table size. An expression over an indexed row-version or SCN column is usually much cheaper than the default hash.
• Blind spots and failure cases: The default hash skips or normalizes some column types, so changes confined to them don’t trigger a reload. A custom expression sees only what it measures. A partition whose expression returns NULL is treated as unchanged on later runs. The first run always extracts every partition.
• Native and Iceberg: Supported on native targets. Supported on a Snowflake-managed Iceberg table this migration creates from a relational extract (catalog: SNOWFLAKE, with no sourceDataStage). A later run reuses that table when an earlier run of the same table completed, and it clears and reloads a partition whose checksum changed. scai data migrate create-workflow rejects checksum for an Iceberg target before the workflow starts.
• Choose it when: The table has no reliable monotonic column, such as a dimension table, and you need any change detected at partition granularity.
• Avoid it when: Reloading one changed partition is too costly for the table, or the checksum misses the columns that change and no expression fixes that.

watermark¶

• Compares: The highest value of watermarkColumn stored by the last successful run. Before COPY INTO, a run reads the maximum from its staged rows and keeps it as a candidate. The candidate becomes the stored maximum only when the partition completes, after COPY INTO succeeds.
• Reloads: Rows whose watermark value is greater than the stored maximum. With trackDeletions, the run also extracts the full primary-key list of each partition.
• Inserts, updates, and deletes:
  • Inserts are appended.
  • Updates are appended as a second version unless trackModifications: true. With it, changed rows are merged into the target by primary key. This applies to warehouse loading only.
  • Deletes aren’t reflected unless trackDeletions: true. With it, target rows whose primary key is missing from the source snapshot are deleted before the new rows load. Deletion tracking runs on incremental runs, not on the initial load.
• Required keys or columns: watermarkColumn is required and must increase on every change you want to migrate. It can be a pseudo-column or system column that isn’t migrated, such as Oracle ORA_ROWSCN. primaryKeyColumns is required for trackDeletions. trackModifications merges only when a primary key is available. Keys can be configured or inferred.
• Performance and cost: Each run reads the watermark column of each partition. trackDeletions scans the full primary-key set of each partition on every run, which adds load on the source for large partitions. trackModifications stages the changed rows in a transient table and runs a MERGE on the warehouse.
• Blind spots and failure cases:
  • A row updated without advancing watermarkColumn is never extracted again.
  • A row whose watermark value isn’t above the stored maximum is never extracted, including late-arriving rows with older values.
  • Deletes aren’t seen without trackDeletions.
  • An empty partition has no candidate, so the stored maximum doesn’t change and the next run starts from the same point.
  • If COPY INTO succeeds but the partition doesn’t reach completion, the stored maximum doesn’t advance. The next run extracts those rows again and appends them, unless trackModifications merges them by primary key.
• Native and Iceberg: Supported on native targets. Supported on the same Snowflake-managed Iceberg tables as checksum. The first load registers the extracted Parquet with ADD_FILES_COPY. A later run with trackModifications: true and a primary key merges changed rows into the table. resetPartitions: enabledWithDedup is rejected for an Iceberg target. trackModifications is rejected with loading.strategy: snowpipe. trackDeletions works with Snowpipe. scai data migrate create-workflow rejects watermark for an Iceberg target before the workflow starts.
• Choose it when: The table has a reliable column that increases on every change you need to migrate, such as UPDATED_AT, and a full reload is too costly. Add trackModifications and trackDeletions when the source updates or deletes rows.
• Avoid it when: No column increases on every change, updates don’t change the column, or the source backfills rows with older values.

State and reruns¶

• A run stores its state per partition in PARTITION_METADATA.SYNCHRONIZATION_DATA. none and full-replace store only their strategy name. checksum stores the checksum. watermark stores the column and the stored maximum.
• A table with no stored state is extracted in full, whichever strategy you choose. A table that’s empty on the first run has no stored maximum, so the first run after rows appear extracts all of them.
• A preflight records partition metadata like any other run. Don’t run one between a full load and later checksum or watermark runs of the same tables. See When to run a preflight.

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 group and need each workflow to land on the right group, 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. There’s no per-table affinity: every task that the Orchestrator creates for the workflow carries the workflow’s value.
• Worker TOML: [application].affinity
• SPCS service: AGENT_AFFINITY in the container environment

The Orchestrator matches workflows by affinity too, through DATA_MIGRATION_ORCHESTRATOR_AFFINITY. Leave it unset. See Orchestrator affinity.

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, including tasks that carry an affinity.
• A Worker with an affinity also picks up tasks without affinity.
• Otherwise, the Worker’s affinity must equal the task’s affinity exactly. The comparison is case-sensitive: S1 doesn’t match s1.
• A Worker affinity can include * wildcards. For example, Worker affinity team-* matches workflow affinities team-blue and team-red. Only the Worker (or Orchestrator) value is a pattern; the workflow’s value is always compared as literal text. If a Worker affinity contains *, any % or _ in that value also acts as a wildcard, so don’t use those characters there.

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

Project default affinity¶

Every SnowConvert AI project has a default affinity, so a workflow and a Worker that you create in the same project match even when you never choose a value. The default has the form <project-name>-<id>, for example northwind-3f9a1c2b: the project name in lowercase with other characters replaced by hyphens, followed by the first eight hexadecimal digits of the project ID. It’s stored as default_affinity in .scai/config/project.yml, or in .scai/config/project.local.yml, which takes precedence. Change it with scai project defaults set --affinity <value>.

When you omit --affinity, these commands apply the project default:

• scai data migrate generate-config and scai data validate generate-config write it as the workflow’s affinity.
• scai data worker setup writes it as the SPCS service’s AGENT_AFFINITY.
• scai data worker generate-config writes it as [application].affinity.

A value that you pass with --affinity replaces the default for that command only. This has two consequences when you build Workers or workflows outside these commands:

• A Worker whose affinity you set by hand, for example AGENT_AFFINITY: s1 in a service specification, doesn’t pick up workflows that carry the project default. Generate those workflows with --affinity s1, or set affinity: s1 in the YAML.
• A workflow that you generate with --affinity s1 isn’t picked up by Workers that carry the project default. When you submit it, scai data migrate create-workflow (or scai data validate create-workflow) warns that the workflow’s affinity differs from the project’s default affinity. The warning is expected when you route on purpose; check that a running Worker carries the same value.

Orchestrator affinity¶

The Orchestrator reads its affinity from DATA_MIGRATION_ORCHESTRATOR_AFFINITY and claims only the workflows that match it, using the rules above. When the variable is unset, the Orchestrator claims every workflow and creates each workflow’s tasks with that workflow’s affinity, which is what routes them to the right Workers. scai data orchestrator setup and scai data orchestrator start never set it.

Keep the Orchestrator without an affinity, including when you route work to several Worker groups. One Orchestrator serves every affinity. If you set the variable, workflows whose affinity doesn’t match stay pending. The Orchestrator logs its value at startup as Orchestrator affinity: <value>, where None means unset.

Affinity in rate-limit rules¶

The AFFINITY column of a rate-limit rule is compared with each task’s affinity exactly, without wildcards. Set it to the workflow affinity you want to cap. A rule without AFFINITY applies to tasks of every affinity.

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, BigQuery EXPORT DATA bucket).

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

SQL Server cet_as:

defaultTableConfiguration:
  extraction:
    strategy: cet_as
    externalStage: TARGET_DB.DATA_MIGRATION.AZURE_LANDING

BigQuery export_data:

defaultTableConfiguration:
  extraction:
    strategy: export_data
    externalStage: TARGET_DB.DATA_MIGRATION.GCS_EXPORT_STAGE

Cloud direct:

defaultTableConfiguration:
  extraction:
    strategy: cloud_direct
    externalStage: TARGET_DB.DATA_MIGRATION.CLOUD_LANDING

Teradata tpt (legacy alias for regular):

tpt is accepted for compatibility and runs as regular.

    extraction:
      strategy: tpt

Constraints, identity columns, and sequences¶

AIM DMV data migration moves table data. It creates each target table with its columns and data types and loads rows; it does not author source constraints (CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY) or identity columns, auto-increment columns, or sequences from the source schema. Convert that DDL with SnowConvert — it translates constraints and identity/sequence definitions into Snowflake equivalents — and apply it as part of schema conversion. During a data load, CHECK constraints and generators are handled differently.

CHECK constraints¶

You don’t need to drop or re-create CHECK constraints by hand. When a target table already has CHECK constraints (for example, ones created by SnowConvert), AIM DMV temporarily removes them before loading the table and restores them after the load completes. This keeps constraints that would otherwise reject in-flight rows during COPY INTO from blocking the migration, and leaves them in place once the load finishes — no manual step is required.

During a preflight dry run the constraints are removed but not restored (the transient preflight schema is discarded anyway), unless you set preflightKeepSchema: true.

Identity columns and sequences¶

Migration copies the values in an identity/auto-increment column (or a sequence-fed column) as ordinary data, but it does not create or advance the target’s generator. Snowflake fixes a sequence’s or identity column’s starting value at creation time and has no runtime “reseed”, so create the target generator with a START past the highest migrated value:

-- 1. Read the loaded maximum.
SELECT MAX(ID) AS max_id FROM TARGET_DB.PUBLIC.ORDERS;

-- 2. Create the sequence starting after it (substitute max_id + 1).
CREATE SEQUENCE IF NOT EXISTS TARGET_DB.PUBLIC.ORDERS_SEQ START = <max_id + 1> INCREMENT = 1;

-- 3. Use it as the column default for new inserts.
ALTER TABLE TARGET_DB.PUBLIC.ORDERS
  ALTER COLUMN ID SET DEFAULT TARGET_DB.PUBLIC.ORDERS_SEQ.NEXTVAL;

Do this once the final data load completes. For incremental sync, reseed at cutover, when writes move to Snowflake — otherwise later incremental loads would collide with generator values already handed out. SnowConvert emits the sequence/identity DDL during schema conversion; adjust its START to clear the migrated range.

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
prefer_native_driver = true
set_context_info = true

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

Platform-specific [connections.source.*] and cloud target 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 settings live in the workflow YAML and the Worker TOML. Environment variables matter in three places:

• The Orchestrator process reads its Snowflake connection, metadata locations, and tuning settings from its environment.
• Settings shared by the Orchestrator and Workers, such as task retries and metadata storage mode, are read by both processes. Set them to the same values on both.
• The Worker container image that scai data worker setup deploys to SPCS has no Worker TOML of its own. At startup, its entrypoint builds the Worker TOML from environment variables. A Worker that you start from a TOML file on your own host doesn’t run that entrypoint, so the Worker container variables don’t affect it unless a row says otherwise. Set the TOML key listed in each table instead.

The tables in this section use these columns:

• Required: whether the process fails, or the strategy doesn’t work, without the variable.
• Default: the value used when the variable is unset. “None” means the setting is left out.
• TOML key (Worker tables only): the Worker TOML key that the container entrypoint writes, which is also the key to set on a Worker you run from TOML.
• Set by scai: whether scai writes the variable into the SPCS service specification. “No” means you add the variable to the specification yourself.

Values marked Secret must come from a Snowflake SECRET bound in the specification’s secrets section, not from env, because DESCRIBE SERVICE shows env values. See Passing other secrets to the Worker.

Orchestrator settings¶

The Orchestrator reads these variables from its own environment. On SPCS, add the ones that scai doesn’t set to the Orchestrator service specification:

Orchestrator and Worker settings¶

Both processes read these variables. Set each one to the same value for the Orchestrator and every Worker, except DM_IDLE_SHUTDOWN_MINUTES, which each process applies to itself:

A Worker that you run from TOML also reads the SECRET_MANAGERS_* variables. See Resolving credentials from an external secret manager.

Idle shutdown settings¶

The Orchestrator and every Worker shut themselves down after a period without work. Idle shutdown is always on, and each process measures its own period:

• scai sets neither DM_IDLE_SHUTDOWN_MINUTES nor [idle_shutdown] minutes, so the services it creates use 60 minutes.
• DM_SPCS_SUSPEND_AGENT_SERVICE names one Worker service. Every other Worker service is suspended only by its own idle shutdown.
• If a Worker can’t suspend its own service, it stays up and retries after 1, 2, 4, 8, and 16 minutes, then waits for another full idle period.
• A suspended service stays suspended until you resume it with scai data orchestrator start or scai data worker start.

For what this means when you run several Workers, and how to resume them before the next wave, see Idle shutdown with several Workers.

Worker container: Core settings¶

These variables apply to every Worker container, whatever the source platform:

The image fixes some TOML values that no environment variable changes: task_fetch_interval = 5 and debug_mode = false for every source, use_bcp = true for SQL Server, and auth_method = "standard" with auto_detect_driver = false for Redshift. To change them, run the Worker from your own TOML.

Worker container: Snowflake connection¶

On SPCS, keep the default connection. The Worker then authenticates with the service’s OAuth token and uses the SNOWFLAKE_ACCOUNT, SNOWFLAKE_HOST, SNOWFLAKE_DATABASE, and SNOWFLAKE_SCHEMA variables that Snowflake sets in every service container. Don’t set those four yourself. Snowflake doesn’t set SNOWFLAKE_WAREHOUSE. For more information, see Snowpark Container Services: SQL execution. The following table lists each variable:

Worker container: SQL Server and Azure Synapse¶

These variables apply when DATA_SOURCE_TYPE is sqlserver or azure_synapse. On every start, a SQL Server container installs Microsoft ODBC Driver 18 and the BCP tools, and an Azure Synapse container installs Microsoft ODBC Driver 18, whichever driver the connection uses. regular extraction from SQL Server always uses BCP in the container. The cet_as variables apply to the cet_as strategy; see Migrating Data from SQL Server. The following table lists each variable:

Worker container: Amazon Redshift¶

These variables apply when DATA_SOURCE_TYPE is redshift and a table uses the unload strategy. regular extraction ignores them. See Migrating Data from Amazon Redshift. The following table lists each variable:

Worker container: Oracle¶

These variables apply when DATA_SOURCE_TYPE is oracle. The container uses the oracledb thin driver and installs no Oracle client. The DBMS_CLOUD variables apply to the dbms_cloud strategy; see Migrating Data from Oracle. The following table lists each variable:

Worker container: Teradata¶

These variables apply when DATA_SOURCE_TYPE is teradata. On every start, the container installs the teradatasql driver from PyPI. It includes no Teradata ODBC driver and no tbuild. The WRITE_NOS_* variables apply to the write_nos strategy; see Migrating Data from Teradata. The following table lists each variable:

Choose exactly one write_nos credential mode. scai data worker setup forwards the location, function mapping, and authorization name, but not WRITE_NOS_ACCESS_ID or WRITE_NOS_ACCESS_KEY. To use that mode on SPCS, bind both from secrets yourself.

Teradata TPT selection¶

tpt in a workflow is a legacy alias for regular. The Worker, not the workflow, decides whether a Teradata bulk extract uses Teradata Parallel Transporter (TPT):

• It uses TPT when use_tpt_for_bulk is true (the default) and tbuild is found on PATH or at TPT_TBUILD_EXECUTABLE.
• Otherwise it extracts through teradatasql, and tpt_max_sessions and tpt_delimiter have no effect.

The Worker container image doesn’t include tbuild, so TPT never runs on SPCS and SPCS Workers always extract through teradatasql. To use TPT, run the Worker from TOML on a host with Teradata Tools and Utilities installed. For a server-side export on SPCS, use write_nos.

Worker container: cloud_direct¶

These variables configure the [connections.target.s3], [connections.target.gcs], or [connections.target.blob] section that the cloud_direct strategy writes to. The container writes no target section unless CLOUD_DIRECT_OBJECT_STORE and a bucket or container name are both set. The following table lists each variable:

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)