Test and Profile Migrations on Production-Like Data

Schema and migration tests can load production snapshots or synthetic rows with dataset blocks, and the --profile flag measures what each statement does over them.

A migration that passes on an empty schema can still lock a table for minutes in production. A dataset gives the test production-like rows, and a profile reports what each statement does to them.

Loading a Production Snapshot

A dataset block registers the rows of a test, and the dataset command loads them where it appears in the test case. This test loads an RDS snapshot export from S3, without its largest table, and asserts over the loaded rows:

dataset "rds_snapshot" "prod" {
  src     = "s3://backups/exports/prod-2026-10-13/appdb?region=us-east-1"
  exclude = ["analytics.events*"]
}

test "schema" "invoices" {
  dataset {
    use = dataset.rds_snapshot.prod
  }
  exec {
    sql    = "SELECT count(*) FROM billing.invoice_lines l JOIN billing.invoices i ON i.id = l.invoice_id"
    output = "9000"
  }
}

export AWS_PROFILE=exports-reader
atlas schema test --dev-url "docker://postgres/18/dev" --url "file://schema.sql" .

-- PASS: invoices (5.38s)
PASS

Tables load in foreign-key order with their primary keys kept, so joins and new inserts behave as they do in production. A csv dataset loads a directory of CSV files the same way. In a migration test, the dataset loads after a migrate command, and the migrations after it run over the loaded rows.

Sampling

A test rarely needs the whole export. A sampler loads the first, last, or random rows of each table, together with every row they reference, so no foreign key is left dangling. A composite sampler picks a sampler per table:

sampler "random" "large" {
  rows = 10000
}

sampler "head" "first" {
  rows = 100
}

sampler "composite" "prod" {
  table {
    match = ["analytics.events"]
    use   = sampler.random.large
  }
  table {
    match = ["billing.plans"]
    all   = true
  }
  table {
    match = ["*"]
    use   = sampler.head.first
  }
}

This sampler draws 10,000 random rows of the events log, keeps every plan, and takes the first 100 rows of every other table.

Masking

mask blocks rewrite sensitive columns before a dataset writes them, with the same grammar as Data Scripts masks:

mask "names" {
  columns = ["billing.users.full_name"]
  method  = HASH
  salt    = var.mask_salt
}

mask "customers" {
  columns   = ["billing.tenants.display_name"]
  method    = PARTIAL
  keep_left = 1
}

mask "tickets" {
  columns  = ["billing.support_tickets.body"]
  entities = [ALL]
}

mask "pii" {
  use = [mask.names, mask.customers, mask.tickets]
}

dataset "rds_snapshot" "masked" {
  src     = "s3://backups/exports/prod-2026-10-13/appdb?region=us-east-1"
  include = ["billing.*"]
  sampler = sampler.composite.prod
  masks   = [mask.pii]
}

• HASH with a salt gives a value the same digest in every table, so masked values stay unique and joinable.
• PARTIAL keeps the first letter of each customer name.
• An entity mask replaces the emails, phone numbers, card numbers, and IP addresses found in free text, and keeps the rest of the value:

Before reading any row, Atlas checks that each masked column can hold the masked values, and fails the test with the column and the reason, such as a key column or a unique column masked with a method other than HASH.

Generating Synthetic Data

Without an export, a synthetic dataset generates the rows from the schema: keys are distinct, foreign keys reference generated rows, and column blocks set the values a check or a query plan depends on:

dataset "synthetic" "invoices" {
  table "billing.tenants" {
    rows = 50
    column "slug" {
      value = format("tenant-%d", row.index)
    }
  }
  table "billing.invoices" {
    rows = 200000
    column "state" {
      value = random::int(1, 100) <= 5 ? "open" : "paid"
    }
  }
}

Here, one invoice in twenty is open, as in production, so a test can assert that a query for open invoices uses a new index over 200,000 rows.

Profiling

The --profile flag of atlas migrate test and atlas schema test reports the locks a statement takes and for how long, the tables it rewrites, and the disk, log, and temporary space it needs. The following migration adds a column and fills it in one statement:

ALTER TABLE orders ADD COLUMN digest text;
UPDATE orders SET digest = md5(note);

The test generates 2,000,000 orders before it applies the version:

dataset "synthetic" "orders" {
  table "public.orders" {
    rows = 2000000
  }
}

test "migrate" "20261104093012" {
  # Migrate to one version before the tested version.
  migrate {
    to = "20261103101500"
  }
  # Generate the rows.
  dataset {
    use = dataset.synthetic.orders
  }
  # Apply the tested version over the generated rows.
  migrate {
    to = "20261104093012"
  }
}

atlas migrate test --dev-url "docker://postgres/18/dev" --profile .

The profile of the migration over the 2,000,000 rows prints the following. The output is abridged:

migrate#3 20261104093012                9.84s
  rows     public.orders                2,000,000 updated
  read     public.orders                2,000,000 rows, 1 sequential scan
  size     public.orders                185.3MB -> 432.0MB (+246.7MB)
  log      721.3MB written, 721.3MB while the statements ran
  ! lock public.orders: takes AccessExclusiveLock for 9.842s, 9.806s of it after the statement, blocking reads and writes (20261104093012_add_orders_digest.sql:1)

Lines that start with ! are findings: statements that would block or slow down production. In the full report, each one is followed by why it matters and a safer way to write the statement.

The cost of a statement depends on the size of the table it runs on, so reading the SQL rarely shows it. A profile measures it during the test run, before the change is merged.

Coding agents can read the report too. It applies to any code that runs on the database: a migration generated with Atlas, a hand-written query, or a function, trigger, or procedure. After writing it, an agent runs the tests with --profile, measures the impact of the change on the database, and fixes the statements the report flags before it proposes the change.

Notes

• Profiling is supported on PostgreSQL, MySQL, MariaDB, and SQL Server.
• atlas tool docker --profile starts a dev database set up for profiling.
• Datasets and profiling are in preview and open to a subset of Teams and Enterprise customers. To get access, reach out through your Atlas support channel or message us on Intercom.

Next Steps

Read the Testing with Data guide to load, sample, mask, and generate test data, and the Profiling guide to profile migrations, queries, and database code.