In-flight processing
Overview
Filter rows, choose and compute columns, and mask values per target, and see where and when the rules apply.
In-flight processing changes what a target receives without touching the source. You set the rules per target in the Configure dialog: which rows to keep, which columns to write, and which values to mask. They apply to the initial load and to every captured change.
What you can do
| Rule | Configure field | What it does |
|---|---|---|
| Row filter | Keep rows where | Writes only the rows that match a condition, such as status = 'active'. |
| Output columns | Choose output columns | Keeps the columns you list, adds computed columns, and drops the rest. |
| Masks | An output column's Source expression, or Mask columns globally | Replaces a value with a partial mask, a keyed hash or a constant. |
Conditions and output expressions use a small SQL expression language, written in the source database's dialect. See the Expression reference.
Example
This rule sends an analytics target the real European customers of a SQL Server table dbo.Customers, with a combined name and masked contact details. customer_id is the primary key.
Keep rows where:
country_code IN ('DE', 'FR', 'IT') AND NOT is_testChoose output columns:
| Output name | Source expression |
|---|---|
customer_id | customer_id |
full_name | CONCAT(CONCAT(first_name, ' '), last_name) |
email | PARTIAL_MASK(email, 1, 3) |
phone | PARTIAL_MASK(phone, 0, 4) |
country_code | country_code |
The source rows:
customer_id | first_name | last_name | email | phone | country_code | is_test |
|---|---|---|---|---|---|---|
| 1 | Ada | Lovelace | ada@example.com | +49 30 1234567 | DE | 0 |
| 2 | Grace | Hopper | grace@example.com | +1 555 0100 | US | 0 |
| 3 | Test | User | test@example.com | +33 1 0000 0000 | FR | 1 |
The target receives one row. Customer 2 isn't in the country list and customer 3 is a test row. The columns first_name, last_name and is_test aren't created at the target:
customer_id | full_name | email | phone | country_code |
|---|---|---|---|---|
| 1 | Ada Lovelace | a***********com | **********4567 | DE |
If customer 2 later moves to FR, the update brings the row into the filter and it appears at the target.
For more examples, see Row filtering, Output and computed columns and Masking.
Requirements
Rules work with every source, capture mode and target. Each target of a pipeline has its own rules, so one target can receive masked data while another receives everything.
- Every table with a rule needs a primary key or a unique key on the source.
- A SQL Server source needs
ALLOW_SNAPSHOT_ISOLATIONon.
See Validation and limits for the full list.
Open the Configure dialog
The dialog edits one target: its table and column names and its rules.
- New pipeline: on the wizard's Tables step, select Configure beside Select all above the table list. To open the dialog on one table, hover over the table and select its own Configure link.
- Existing pipeline: on the pipeline's Overview tab, find the target in the Targets card and select Configure on its row.
The left pane lists the tables, with All tables at the top. Configured shows only the tables that carry names or rules, and each row shows what is set, such as filter, 3 columns or 1 mask.
All tables and per-table rules
All tables holds defaults for every table. A table's own rule sits in the right pane when you select the table.
| All tables setting | Applies to | Combined with a table's own rule |
|---|---|---|
| Keep rows where (all tables) | Every table that has all the columns the condition names. Other tables copy every row. | Joined with AND. The table's editor shows it as "And from All tables". |
| Choose global output columns | Every table that has all the columns the expressions name. | Ignored for a table that chooses its own output columns. |
| Mask columns globally | Each named output column, in every table where the mask's columns exist. | Ignored for a table that chooses its own output columns. |
Select Reset table to clear a table's names and rules.
When rules apply
- Initial load. Rows that don't match the condition are filtered out by the query that reads the source table.
- Streaming. Each captured change passes through the rules before the target sees it. An update that moves a row out of the filter removes it from the target. See Row filtering.
- Target tables. Prepare target schema creates each table with the output columns, not the source columns.
Rules read the original source values. The Column names you set in the same dialog then rename the output columns at the target.
Change rules on an existing pipeline
| Change | When you can make it | What happens on save |
|---|---|---|
| Rules | While the pipeline runs or is paused | Save and reload… reloads the whole pipeline. |
| Table and column names | Only while the pipeline is paused | Save stores the names. After you resume, a renamed table is written under its new name; resync it to copy its existing rows. See Table and column names. |
Saving rules asks Save rules and reload the pipeline?. Replication stops, and every table in every target of the pipeline is emptied and copied again from the source with the new rules. A paused pipeline starts loading right away.
Test a rule before you create the pipeline
In the wizard, the Configure dialog offers Test rule under a table's rule, and Test rule / check table coverage under All tables. The test checks the rule against the source schema and reports, per table, whether the rule applies, which output columns result, and which columns are missing. It can also count the matching rows without changing the source. See Check how many rows match.
