Additional requirements for Databricks

To decide which fast apply algorithm to use for your Databricks target, consider these characteristics:

• GroupByTableNetEffectConvertUpdates uses a single thread. Select this if parallelism is not required.
• ParallelizeByHashNetEffectConvertUpdates is a parallel algorithm apply algorithm where the tables are split amongst the apply threads through a hashing algorithm. This ensures all apply threads are given a uniform distribution of the workload, even if some tables are busy and others are relatively idle.
• ParallelizeByTableNetEffectConvertUpdates is a parallel algorithm apply algorithm where the tables are split amongst the apply threads through a round-robin algorithm. Select this if your tables have roughly the same activity level.

Perform these steps to configure the fast apply algorithm properties:

1. Set the UOW threshold to a value greater than or equal to <number of tables in a typical UOW> * global_max_batch_size * 2.
2. Set the number of apply threads initially to 4 (for parallel fast apply algorithms) and increase as needed to achieve the desired throughput.

Important aspects to consider for effective use and management of Data bricks:

• To avoid exceeding Databricks' 255 SQL parameter limit, set the CDC system property target_apply_single_multirow_statement_column_values_inline to true to append insert or delete column values as literal strings instead of SQL parameters.
• To improve the mirroring performance, increase the CDC system property global_max_batch_size to a value much larger than the default (50), typically 2000 or more. However, be cautious of very long SQL statements that may degrade performance. Increase the batch size until performance starts to decrease for the typical workload. Ensure that the fast apply threshold is set substantially higher than the value defined for this property, because a batch cannot extend across UOW boundaries.
• To improve the table refresh (load) performance, increase the CDC system properties jdbc_refresh_max_batch_size and jdbc_refresh_commit_after_max_operations to larger values (for instance, 5000 or more).
  Note: jdbc_refresh_max_batch_size cannot exceed the value set for jdbc_refresh_commit_after_max_operations.
• Enable parallel refresh by setting target_allow_parallel_jdbc_refresh to true and specifying the desired level of parallelism (target_num_refresh_apply_threads, default is 4).
• To apply multiple inserts and deletes in a single batch for optimal performance, set target_apply_insert_batch_via_single_statement and target_apply_delete_batch_via_single_statement to true.

CDC requires a set of target table columns that uniquely identify each row. This set of columns is referred to as the replication key. To ensure optimal performance and reliability, please consider the following guidelines:

Use Primary Keys as Replication Keys

The most effective replication key columns are the table’s primary key columns.

When a primary key constraint is declared, CDC automatically selects those columns as the replication key.

Declare Primary Key Constraints in Databricks

In Databricks workspaces that support Unity Catalog (with a default catalog other than Hive), primary key constraints are supported.

It is strongly recommended to declare primary key constraints on all target tables.

Define Replication Keys When No Primary Key Exists

If a primary key constraint is not declared, explicitly define replication key columns that logically form a unique key.

Ensure these columns are declared as NOT NULL to maintain data integrity.

Prefer Single‑Column Primary Keys

Multi‑column primary keys can significantly slow down multi‑row delete operations due to Databricks SQL optimizer limitations.

Whenever possible, declare a single‑column primary key to improve performance.

Add the JDBC option at the beginning of the file if not present. Ensure there is a trailing space separating it from the existing options. Once added, the dmts64.vmargs file must look like:

--add-opens java.base/java.nio=ALL-UNNAMED -Dorg.apache.commons.logging.Log=com.datamirror.ts.util.trace.TraceSimpleLog -Dcom.datamirror.ts.instance=%TSINSTANCE% com.datamirror.ts.bootstrap.Launcher com.datamirror.ts.commandlinetools.script.Startup