Database Write Strategies

The Database Write Strategies available in Astera Centerprise provide a convenient way for you to make updates and insertions in a database table. They can be used to implement advanced logic for handling data updates, thus promoting seamless data integration and warehousing.

In this article, we will be discussing the Database Write Strategies available in Astera Centerprise. Let’s take a look at a sample use case.

Here, we are writing data from a source table in one database to a destination table in another database. The source data consists of customers’ information from a fictitious organization, and the organization would like to have control over which records are written to the destination table.

Centerprise supports the following database write strategies:

• Data Driven
• Database Diff Processor
• Source Diff Processor

Data Driven

The Data Driven write strategy processes records based on some predefined criteria expressed in the form of rules. For example, insert records where LTV > 80, or delete records where CreatedDtTm < UpdatedDtTm.

Each rule has a database action associated with it, such as Insert, Update, Delete or Error. If a record does not pass a rule, the record will be tried against the next rule on the list, until all rules have been exhausted.

To add a Data Driven write strategy, open the flow Toolbox, expand the Database Write Strategy group, and drag-and-drop the Data Driven object onto the dataflow designer.

An example of what a Data Driven object looks like is shown below.

The following properties are available to help you configure the logic of your database update:

The Layout Builder window allows you to add or remove fields in the field layout, as well as select their data type.

Note: To quickly add fields to the layout, drag-and-drop the node Output port of the object whose layout you wish to replicate into the node Input port of the Data Driven object. The fields added this way will show in the list of fields inside the node and as well as in the Layout Builder.

The Data Driven Write Strategy Conditions window allows you to manage rules and associate them with database actions.

To add a new rule, follow these steps:

1. Click the add condition icon to create a new rule.

   

2. Type a descriptive name for the rule in the Description field.

3. Activate the rule by checking the Active checkbox.

4. Select a database action, such as Insert, Update, Delete or Error, from the Perform Database Action dropdown list. This action will be applied when the rule holds.

5. In the When the Following Condition Holds input, write an expression for the rule. For example, LoanAmount < 320000. Or click … to open the Expression Builder where you can visually build your rule using Record tree and IntelliSense.

6. Click Compile to check for any syntax errors in your rule. The status should read “Successful” for a successful compilation. Close the Expression Builder.

7. The new rule has been successfully configured.

8. You can add multiple rules if necessary. To delete an existing rule, select it and click the remove condition icon.

   

General Options window

The Comments input allows you to enter comments associated with this object.

Database Diff Processor

The Diff Processor database write strategy synchronizes records between two tables. The destination table is compared against a diff table, and any differences between the two tables are reconciled in the destination table.

To apply a Diff Processor write strategy, open the Toolbox, expand the Database Write Strategy group, and drag-and-drop the Diff Processor object onto the dataflow designer.

An example of what a Diff Processor object may look like is shown below.

The following properties are available to help you configure the logic of your database update:

Database Connection window – Allows you to enter the connection information for the diff table, such as Server Name, Database and Schema, as well as credentials for connecting to the selected diff table.

Pick Table window:

In the Pick Table window, select the diff table for this database write strategy.

Select Field for Matching Database Record – Specifies a field (or a combination of fields) which will be used to match records from the diff table with records that exist in the destination table.

Output Options:

• Single Output: This is the default selection when using a database write strategy. Single Output means that the database action, such as Insert, Update, Skip or Error will be chosen by the database write strategy’s logic rather than specified by the user. Using a Single Output is recommended when a database write strategy is applied.
• One Port for Each Action – This adds extra flexibility in letting you specify maps for each of the database write actions, such as Insert, Update, Skip or Error. Each set of maps is completely independent of another, and will be applied according to logic of the database write strategy.