Data Transmission Service:Configure ETL in DTS tasks

Usage notes

Procedure

1. Create a sync task. For details, see Sync solutions.

2. In the Advanced Configurations step, set Configure ETL to Yes.

3. In the text box, enter your DSL script based on the data processing DSL syntax.

   To filter records where id is greater than 3, use e_if(op_gt(id, 3), e_drop()). This drops any record where the id value exceeds 3, so it is not synchronized to the destination.

   

4. Complete the remaining steps as needed.

Usage notes

Procedure

1. Log on to the Data Synchronization Tasks page of the new DTS console.

2. In the row for the target sync task, click the icon and select Modify ETL Configurations.

3. In the Advanced Configurations step, set Configure ETL to Yes.

4. In the text box, enter the updated DSL script.

   To filter records where id is greater than 3, use e_if(op_gt(id, 3), e_drop()). This drops any record where the id value exceeds 3, so it is not synchronized to the destination.

   

5. Complete the remaining steps as needed.

Data filtering

Use data filtering to sync only a subset of records—for example, to replicate recent orders to an analytics database, exclude test data from a production sync, or enforce region-specific data residency rules by dropping records that don't belong.

• Drop records where id > 10000:

  e_if(op_gt(`id`, 10000), e_drop())

• Drop records where name contains "hangzhou":

  e_if(str_contains(`name`, "hangzhou"), e_drop())

• Drop orders placed before a cutoff date:

  e_if(op_lt(`order_timestamp`, "2015-02-23 23:54:55"), e_drop())

• Drop records matching multiple conditions (AND):

  e_if(op_and(str_contains(`name`, "hangzhou"), op_gt(`id`, 1000)), e_drop())

• Drop records matching either condition (OR):

  e_if(op_or(str_contains(`name`, "hangzhou"), op_gt(`id`, 1000)), e_drop())

Data masking

Use data masking when the destination database is accessed by a broader audience than the source—for example, when syncing production data to a reporting system or sharing data with a third party. Masking lets you protect personally identifiable information (PII) while preserving data for analytics.

Replace the last four digits of a phone number (positions 7–10) with asterisks:

e_set(`phone`, str_mask(`phone`, 7, 10, '*'))

Change tracking

Use change tracking when downstream consumers need to know when a row was last modified—for example, to implement incremental processing pipelines, detect stale records, or audit data freshness without querying the source database.

Add a dts_sync_time column to all tables for INSERT, UPDATE, and DELETE operations:

e_if(op_or(op_or(
        op_eq(__OPERATION__, __OP_INSERT__),
        op_eq(__OPERATION__, __OP_UPDATE__)),
        op_eq(__OPERATION__, __OP_DELETE__)),
    e_set(dts_sync_time, __COMMIT_TIMESTAMP__))

To apply this only to a specific table (for example, dts_test_table):

e_if(op_and(
      op_eq(__TB__,'dts_test_table'),
      op_or(op_or(
        op_eq(__OPERATION__,__OP_INSERT__),
        op_eq(__OPERATION__,__OP_UPDATE__)),
        op_eq(__OPERATION__,__OP_DELETE__))),
      e_set(dts_sync_time,__COMMIT_TIMESTAMP__))

Audit logging

Use audit logging when you need a full history of data changes in the destination table—for example, to meet compliance requirements, support rollback scenarios, or build a change log without a separate audit system. This pattern writes every incoming change as an INSERT, so the destination table accumulates a complete record of all modifications.

Write the DML operation type and commit timestamp into the destination row, then convert all operations to INSERT:

e_compose(
    e_switch(
        op_eq(__OPERATION__,__OP_DELETE__), e_set(operation_type, 'DELETE'),
        op_eq(__OPERATION__,__OP_UPDATE__), e_set(operation_type, 'UPDATE'),
        op_eq(__OPERATION__,__OP_INSERT__), e_set(operation_type, 'INSERT')),
    e_set(updated, __COMMIT_TIMESTAMP__),
    e_set(__OPERATION__,__OP_INSERT__)
)

Distinguish full and incremental data

Use this pattern when the destination table receives both full migration data and incremental changes, and downstream consumers need to tell them apart—for example, to skip reprocessing historical rows in a streaming pipeline, or to validate that the initial load completed before applying incremental logic.

During a full migration, __COMMIT_TIMESTAMP__ is 0 (1970-01-01 08:00:00, adjusted for the local time zone). During incremental migration, it holds the actual log commit time. The script below writes True or False into an is_increment_dml column:

e_if_else(__COMMIT_TIMESTAMP__ > DATETIME('2000-01-01 00:00:00'),
    e_set(`is_increment_dml`, True),
    e_set(`is_increment_dml`, False)
)

Built-in variables

The following built-in variables are available in all DSL scripts.

Constants

Expression functions

Global functions