Validation and limits

Check what processing rules need, when they are validated, every limit, and what each validation message means.

SQL Flow checks processing rules before any row moves, and refuses a rule it can't apply exactly rather than approximating it. This page lists the requirements, the limits and the messages you can see.

Requirements

Snapshot isolation on SQL Server sources

After a crash, a pipeline with rules repairs the rows it may have left half-applied, reading the source under snapshot isolation so it takes no locks. The wizard's Prepare step and the start therefore refuse a SQL Server source where snapshot isolation is off. The refusal starts with:

• repair_source_snapshot_isolation_required when READ_COMMITTED_SNAPSHOT is on but ALLOW_SNAPSHOT_ISOLATION is off;
• repair_source_row_versioning_required when neither is on.

SQL Flow never changes the setting itself. Turn it on, replacing <database_name>:

ALTER DATABASE [<database_name>] SET ALLOW_SNAPSHOT_ISOLATION ON;

When rules are checked

1. While you type. The Configure dialog checks sizes, names and required fields. A table with a problem shows it in red in the table list, and Save or Done selects the table and shows the reason.
2. Test rule. In the wizard, Test rule checks each rule against the source schema and reports problems per table. See Overview: Test a rule.
3. Prepare and start. Prepare target schema, and the pipeline start, check every rule against the source schema. A rule that fails is refused with its code, and nothing is created or moved.
4. Each row. Some problems depend on values, such as a cast that would round. They stop the pipeline when the row is processed. See Row errors.
5. Source schema changes. A schema change on a table that a rule covers holds the covered tables until you reload the pipeline. See Schema changes.

Limits

Messages in the Configure dialog

Saving rules on an existing pipeline

Expression errors

A rule problem is reported as a code followed by where it is, for example unsupported-function at expression. Test rule shows the code alone. The examples are written in T-SQL for a SQL Server source.

Row errors

These codes stop the pipeline when a row's value can't be processed exactly. The expression is accepted at the start, and the pipeline stops when it processes the example value.

To recover, fix the rule or the data, save the rules, and let the pipeline reload.

Validate a rules file with the CLI

The CLI checks the structure of a rules file locally, without the source: the fields, the version, the table names, the sizes and the counts. It doesn't parse the expressions, so expression errors appear only in Test rule, in Prepare target schema and at the start. Replace <rules.json> with the file and list the source tables the pipeline replicates:

sqlflow processing validate --file <rules.json> --tables <schema.table>,<schema.table>

It prints Processing structure valid; source schema and runtime eligibility have not been checked. Use sqlflow processing describe with the same options to print a JSON summary instead.

A rules file uses the source table names as keys under tables. Set "version" to 1 for per-table rules only, 2 when you use globalPredicate, and 3 when you use globalColumns, globalMasks or maskKeys. This file uses every field:

{
  "version": 3,
  "globalPredicate": "TenantId IN (1, 2, 3)",
  "globalMasks": [
    { "name": "Email", "expression": "HASH_MASK(Email, 'customer')" }
  ],
  "maskKeys": {
    "customer": { "version": "v1", "secretRef": "env:CUSTOMER_MASK_V1" }
  },
  "tables": {
    "dbo.Orders": {
      "predicate": "status = 'active'",
      "columns": [
        { "name": "OrderId", "expression": "OrderId" },
        { "name": "Email", "expression": "HASH_MASK(Email, 'customer')" },
        { "name": "LineTotal", "expression": "CAST(Quantity * UnitPrice AS DECIMAL(18,2))" }
      ]
    },
    "dbo.Customers": {
      "predicate": "DeletedAt IS NULL"
    }
  }
}

See also

• Overview
• Expression reference
• Row filtering
• Schema changes
• CLI overview and authentication