Astera Data Stack
Version 7
Version 7
  • Welcome to Astera Data Stack Documentation
  • Release Notes
    • Upgrading Astera 6 to Version 7
    • Release Notes for Astera 7.1
    • What is New in Astera 7.4
    • What’s New in Astera 7.4.1.221
    • What’s New in Astera 7.6
    • Upgrading to Astera 7.6
  • Setting Up
    • System Requirements
    • Installing Astera Data Integrator
    • Setting up Astera Integration Server 7
    • UI Walkthrough - Astera Data Integrator
  • Dataflows
    • Introducing Dataflows
    • Sources
      • Setting Up Sources
      • Raw Text Filters in File Sources
      • COBOL File Source
      • Database Table Source
      • Data Model Source
      • Delimited File Source
      • Email Source
      • Excel Workbook Source
      • File System Items Source
      • Fixed Length File Source
      • PDF Form Source
      • Report Source
      • SQL Query Source
      • XML/JSON File Source
    • Transformations
      • Aggregate Transformation
      • Apply To All Transformation
      • Constant Value Transformation
      • Data Cleanse Transformation
      • Data Quality Rules Transformation
      • Denormalize Transformation
      • Distinct Transformation
      • Expression Transformation
      • Filter Transformation
      • Function Transformations
      • Join Transformation
      • Merge Transformation
      • Normalize Transformation
      • Passthru Transformation
      • Rest Client
      • Route Transformation
      • Sequence Generator Transformation
      • Sort Transformation
      • Sources as Transformations
      • Subflow Transformation
      • Tree Join Transformation
      • Union Transformation
      • Web Service Transformation
    • Destinations
      • Setting Up Destinations
      • Database Table Destination
      • Delimited File Destination
      • Excel Workbook Destination
      • Fixed Length File Destination
      • SQL Statement Destination
      • XML/JSON File Destination
    • Data Logging and Profiling
      • Creating Data Profile
      • Creating Field Profile
      • Data Quality Mode
      • Record Level Log
      • Using Data Quality Rules in Astera
    • Database Write Strategies
      • Database Write Strategies
    • Text Processors
      • Fixed Length Parser
      • Fixed Length Serializer
  • Workflows
    • Adding Workflow Tasks
    • Creating Workflows
    • Using Workflow Designer
    • Customizing Workflows With Parameters
    • Workflows with a Dynamic Destination Path
    • Resuming and Rerunning Workflows in Astera
  • Subflows
    • Using Subflows
  • Functions
    • Functions Glossary
    • Introducing Function Transformations
    • Custom Functions
    • Logical
      • Coalesce (Any value1, Any value2)
      • IsNotNull (AnyValue)
      • IsRealNumber (AnyValue)
      • IsValidSqlDate (Date)
      • IsDate (AnyValue)
      • If (Boolean)
      • If (DateTime)
      • If (Double)
      • Exists
      • If (Int64)
      • If (String)
      • IsDate (str, strformat)
      • IsInteger (AnyValue)
      • IsNullOrWhitespace (StringValue)
      • IsNullorEmpty (StringValue)
      • IsNull (AnyValue)
      • IsNumeric (AnyValue)
    • Conversion
      • GetDateComponents (DateWithOffset)
      • ParseDate (Formats, Str)
      • GetDateComponents (Date)
      • HexToInteger (Any Value)
      • ToInteger (Any value)
      • ToDecimal (Any value)
      • ToReal (Any value)
      • ToDate (String dateStr)
      • TryParseDate (String, UnknownDate)
      • ToString (Any value)
      • ToString (DateValue)
      • ToString (Any data, String format)
    • Math
      • Abs (Double)
      • Abs (Decimal)
      • Ceiling (Real)
      • Ceiling(Decimal)
      • Floor (Decimal)
      • Floor (Real)
      • Max (Decimal)
      • Max (Date)
      • Min (Decimal)
      • Min (Date)
      • Max (Real)
      • Max (Integer)
      • Min (Real)
      • Pow (BaseExponent)
      • Min (Integer)
      • RandomReal (Int)
      • Round (Real)
      • Round (Real Integer)
      • Round (Decimal Integer)
      • Round (Decimal)
    • Financial
      • DDB
      • FV
      • IPmt
      • IPmt (FV)
      • Pmt
      • Pmt (FV)
      • PPmt
      • PPmt (FV)
      • PV (FV)
      • Rate
      • Rate (FV)
      • SLN
      • SYD
    • String
      • Center (String)
      • Chr (IntAscii)
      • Asc (String)
      • AddCDATAEnvelope
      • Concatenate (String)
      • ContainsAnyChar (String)
      • Contains (String)
      • Compact (String)
      • Find (Int64)
      • EndsWith (String)
      • FindIntStart (Int32)
      • Extract (String)
      • GetFindCount (Int64)
      • FindLast (Int64)
      • GetDigits (String)
      • GetLineFeed
      • Insert (String)
      • IsAlpha
      • GetToken
      • IndexOf
      • IsBlank
      • IsLower
      • IsUpper
      • IsSubstringOf
      • Length (String)
      • LeftOf (String)
      • Left (String)
      • IsValidName
      • Mid (String)
      • PadLeft
      • Mid (String Chars)
      • LSplit (String)
      • PadRight
      • ReplaceAllSpecialCharsWithSpace
      • RemoveChars (String str, StringCharsToRemove)
      • ReplaceLast
      • RightAlign
      • Reverse
      • Right (String)
      • RSplit (String)
      • SplitStringMultipleRecords
      • SplitStringMultipleRecords (2 Separators)
      • SplitString (3 separators)
      • SplitString
      • SplitStringMultipleRecords (3 Separators)
      • Trim
      • SubString (NoOfChars)
      • StripHtml
      • Trim (Start)
      • TrimExtraMiddleSpace
      • TrimEnd
      • PascalCaseWithSpace (String str)
      • Trim (String str)
      • ToLower(String str)
      • ToProper(String str)
      • ToUpper (String str)
      • Substring (String str, Integer startAt)
      • StartsWith (String str, String value)
      • RemoveAt (String str, Integer startAt, Integer noofChars)
      • Proper (String str)
      • Repeat (String str, Integer count)
      • ReplaceAll (String str, String lookFor, String replaceWith)
      • ReplaceFirst (String str, String lookFor, String replaceWith)
      • RightOf (String str, String lookFor)
      • RemoveChars (String str, String charsToRemove)
      • SplitString (String str, String separator1, String separator2)
    • Date Time
      • AddMinutes (DateTime)
      • AddDays (DateTimeOffset)
      • AddDays (DateTime)
      • AddHours (DateTime)
      • AddSeconds (DateTime)
      • AddMonths (DateTime)
      • AddMonths (DateTimeOffset)
      • AddMinutes (DateTimeOffset)
      • AddSeconds (DateTimeOffset)
      • AddYears (DateTimeOffset)
      • AddYears (DateTime)
      • Age (DateTime)
      • Age (DateTimeOffset)
      • CharToSeconds (Str)
      • DateDifferenceDays (DateTimeOffset)
      • DateDifferenceDays (DateTime)
      • DateDifferenceHours (DateTimeOffset)
      • DateDifferenceHours (DateTime)
      • DateDifferenceMonths (DateTimeOffset)
      • DateDifferenceMonths (DateTime)
      • DatePart (DateTimeOffset)
      • DatePart (DateTime)
      • DateDifferenceYears (DateTimeOffset)
      • DateDifferenceYears (DateTime)
      • Month (DateTime)
      • Month (DateTimeOffset)
      • Now
      • Quarter (DateTime)
      • Quarter (DateTimeOffset)
      • Second (DateTime)
      • Second (DateTimeOffset)
      • SecondsToChar (String)
      • TimeToInteger (DateTime)
      • TimeToInteger (DateTimeOffset)
      • ToDate Date (DateTime)
      • ToDate DateTime (DateTime)
      • ToDateString (DateTime)
      • ToDateTimeOffset-Date (DateTimeOffset)
      • ToDate DateTime (DateTimeOffset)
      • ToDateString (DateTimeOffset)
      • Today
      • ToLocal (DateTime)
      • ToJulianDate (DateTime)
      • ToJulianDayNumber (DateTime)
      • ToTicks (Date dateTime)
      • ToTicks (DateTimeWithOffset dateTime)
      • ToUnixEpoc (Date dateTime)
      • ToUtc (Date dateTime)
      • UnixTimeStampToDateTime (Real unixTimeStamp)
      • UtcNow ()
      • Week (Date dateTime)
      • Week (DateTimeWithOffset dateTime)
      • Year (Date dateTime)
      • Year (DateTimeWithOffset dateTime)
      • DateToJulian (Date dateTime, Integer length)
      • DateTimeOffsetUtcNow ()
      • DateTimeOffsetNow ()
      • Day (DateTimeWithOffset dateTime)
      • Day (Date dateTime)
      • DayOfWeekStr (DateTimeWithOffset dateTime)
      • DayOfWeek (DateTimeWithOffset dateTime)
      • DayOfWeek (Date dateTime)
      • DateToJulian (DateTimeWithOffset dateTime, Integer length)
      • DayOfWeekStr (Date dateTime)
      • FromJulianDate (Real julianDate)
      • DayOfYear (Date dateTime)
      • DaysInMonth(Integer year, Integer month)
      • DayOfYear (DateTimeWithOffset dateTime)
      • FromUnixEpoc
      • FromJulianDayNumber (Integer julianDayNumber)
      • FromTicksUtc(Integer ticks)
      • FromTicksLocal(Integer ticks)
      • Hour (Date dateTime)
      • Hour (DateTimeWithOffset dateTime)
      • Minute (Date dateTime)
      • JulianToDate (String julianDate)
      • Minute (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (Date dateTime)
    • Files
      • AppendTextToFile (String filePath, String text)
      • CopyFile (String sourceFilePath, String destFilePath, Boolean overWrite)
      • CreateDateTime (String filePath)
      • DeleteFile (String filePath)
      • DirectoryExists (String filePath)
      • FileExists (String filePath)
      • FileLength (String filePath)
      • FileLineCount (String filePath)
      • GetDirectory (String filePath)
      • GetEDIFileMetaData (String filePath)
      • GetExcelWorksheets (String excelFilePath)
      • GetFileExtension (String filePath)
      • GetFileInfo (String filePath)
      • GetFileName (String filePath)
      • GetFileNameWithoutExtension (String filePath)
      • LastUpdateDateTime (String filePath)
      • MoveFile (String filePath, String newDirectory)
      • ReadFileBytes (String filePath)
      • ReadFileFirstLine (String filePath)
      • ReadFileText (String filePath)
      • ReadFileText (String filePath, String codePage)
      • WriteBytesToFile (String filePath, ByteArray bytes)
      • WriteTextToFile (String filePath, String text)
    • Date Time With Offset
      • ToDateTimeOffsetFromDateTime (dateTime String)
      • ToUtc (DateTimeWithOffset)
      • ToDateTimeOffsetFromDateTime
      • ToDateTimeOffset (String dateTimeOffsetStr)
      • ToDateTimeFromDateTimeOffset
    • GUID
      • NewGuid
    • Encoding
      • ToBytes
      • FromBytes
      • UrlEncode
      • UrlDecode
    • Regular Expressions
      • ReplaceRegEx
      • ReplaceRegEx (Integer StartAt)
    • TimeSpan
      • Minutes
      • Hours
      • Days
      • Milliseconds
    • Matching
      • Soundex
      • DoubleMetaphone
      • RefinedSoundex
  • Report Model
    • User Guide
      • Report Model Tutorial
    • Report Model Interface
      • Field Properties
      • Pattern Properties
      • Region Properties
      • Report Browser
      • Report Options
    • Use Cases
      • Applying Pattern to Line
      • Auto-Creating Data Regions and Fields
      • Auto-Parsing
      • Creating Multi-Column Data Regions
      • Floating Patterns and Floating Fields
      • How to Work with Microsoft Word (Doc/Docx) Files in a Report Model
      • How to Work With OCR Scanned Files in a Report Model
      • How to Work With PDF Scaling Factor in a Report Model
      • Line Count
      • Pattern Count
      • Pattern is a Regular Expression
    • Exporting Options
      • Exporting a Report Model
      • Exporting Report Model to a Dataflow
    • Miscellaneous
      • Astera Report Model: Performance on Different Hardware Settings and Case Complexities
      • Microsoft Word and Rich Text Format Support
      • Importing Monarch Models
  • Project Management
    • Project Management
      • Deployment
      • Parameterization
    • Job Scheduler
      • Scheduling and Running Jobs on a Server
  • Web Services
    • Configuring Google Drive API through REST Client in Astera
    • Connecting to Eloqua using Astera REST API
    • POSTing Data Using the REST Client
    • Using the REST Client to Download a Text File
  • Metadata Management
    • Lineage and Impact Analysis
  • Connectors and Providers
    • Setting Up Oracle ODP.Net Connectivity in Astera
    • Running Microsoft Access Database Engine with Astera
    • Oracle Client Tools Setup
    • Oracle Data Load Options
  • Miscellaneous
    • Job Trace Improvements
    • SmartMatch Feature
    • Synonym Dictionary File
    • Using the Data Source Browser in Astera
  • Best Practices
    • Astera Best Practices - Dataflows
    • Cardinality Errors FAQs
    • Overview of Cardinality in Data Modeling
Powered by GitBook

© Copyright 2025, Astera Software

On this page
  • CDC Hash
  • Data Driven
  • Diff Processor
  • Slowly Changing Dimensions
  1. Dataflows
  2. Database Write Strategies

Database Write Strategies

PreviousDatabase Write StrategiesNextText Processors

Last updated 10 months ago

When your dataflow writes into a destination database table, you can configure the database write action (such as Insert, Update, Upsert, or Delete) directly in the Database Table Destination object’s properties, or enable the ‘Single port’ use by the Database Table Destination object linking it to a database write strategy.

An example of using a database write strategy is shown below.

Using a database write strategy allows you to implement advanced logic for handling data updates, using one of the strategies supported by Astera.

Astera supports the following database write strategies.

  • CDC Hash

  • Data Driven

  • Diff Processor

  • Slowly Changing Dimensions

CDC Hash

This is similar to incremental database updates in transfer settings. CDC Hash strategy adds records incrementally to the destination as new records are created in a source feeding into that destination. This improves performance by skipping the reading/writing of records that already existed at the time the dataflow previously ran.

To add a CDC Hash write strategy, open the Flow toolbox, expand the Database Write Strategy group, and drag-and-drop the CDC Hash object on the dataflow.

An example of what a CDC Hash object looks like is shown below.

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

  • Meta Object Builder screen is used 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 CDC Hash object. The fields added this way show in the list of fields inside the node and as well as in the Meta Object Builder.

  • Incremental Write Options screen:

    • Select Field for Matching Database Record – specifies a field (or a combination of fields) that will be used to match incoming records with records that already exist in the destination table.

    • Case-sensitive - Turn this option on if you need a case-sensitive match of the matching field’s value. The matching is case-insensitive by default.

    • Sort Input - Turn this option on only if the values in the matching field (or fields) are not already sorted.

  • 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, Delete, 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.

  • Incremental Transfer Information File Path – specifies the file path of the CDC file. A CDC file is required for incremental transfers. This file logs which records were previously transferred, so that appropriate records can be skipped during the next dataflow run.

  • One Port for Each Action – adds extra flexibility in letting you specify maps for each of the database write actions, such as Insert, Update, Delete, Skip, or Error. Each set of maps is completely independent of one another and will be applied according to the logic of the database write strategy.

Resetting a CDC file will result in a full transfer of data from source to destination the next time the dataflow runs.

Note: To ensure that your dataflow is runnable on a remote server, please avoid using local paths for the CDC file. Using UNC paths is recommended.

To reset a CDC file, click the Reset button on the Incremental Write Options screen.

  • General Options screen: The Comments input allows you to enter comments associated with this object.

Data Driven

Data Driven strategy processes records based on some predefined criteria, which are expressed in the form of rules. For example, insert records whose 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 are 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 on the dataflow.

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:

  • Meta Object Builder screen 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 show in the list of fields inside the node and as well as in the Meta Object Builder.

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

To add a new rule, follow these steps:

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

  2. Make the rule active by checking the Active checkbox.

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

  4. In the When the Following Condition Holds input, enter the expression making the rule. For example, LoanAmount < 320000. Or click … to open Expression Builder, a tool that allows you to visually build your rule using Record tree and Intellisense.

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

  6. The new rule is successfully configured.

  • General Options screen: The Comments input allows you to enter comments associated with this object.

Diff Processor

Diff Processor database writing 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 add a Diff Processor write strategy, open the Flow toolbox, expand the Database Write Strategy group, and drag-and-drop Diff Processor object on the dataflow.

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 screen – 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 screen: Using Pick Table screen, select the diff table for this database write strategy.

  • Select Field for Matching Database Record – specifies a field (or a combination of fields) that 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 – 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 one another and will be applied according to the logic of the database write strategy.

Slowly Changing Dimensions

The Slowly Changing Dimensions (SCD) database write strategy addresses scenarios where a field’s value for a record varies over time. Centerprise supports industry-standard Type 1 and Type 2 methods to handle slowly changing values in the selected fields of a dimension table.

The Type 1 method is used when no historical data is required on how values changed in the SCD field over time. Type 1 method simply updates a record in the dimension table, overwriting an old SCD value with a new value.

The Type 2 method makes it possible to track historical data by creating multiple records in the dimension table. The records are identified by using an extra surrogate key in addition to any other keys already present in the table. With Type 2, you have unlimited history as a new record is inserted each time a change to the SCD value is made. Additional fields, such as EffectiveDate, ExpirationDate, or Version may be used to track the timeline of SCD value updates, as well as identify the current SCD value.

To add the Slowly Changing Dimensions write strategy, open the Flow toolbox, expand the Database Write Strategy group, and drag-and-drop the SCD object on the dataflow.

An example of what an SCD object might look like is shown below.

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

  • Database Connection screen – allows you to enter the connection information for the SCD table, such as server name, database and schema, as well as credentials for connecting to the selected SCD table.

  • Pick Table screen: Using Pick Table screen, select the SCD table that will be updated by this database write strategy.

  • 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 – 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 one another and will be applied according to the logic of the database write strategy.

  • Layout Fields screen:

Using the SCD Field Type dropdown, select among the following designations for each field in the layout:

  • For SCD Type 1 updates:

    • Business Key - designates the field holding the key that is normally used to identify records in the table

    • Not Used - designates a field that is not used by the SCD update logic. However, this field may still be updated if it is mapped.

    • Update Not Allowed - designates a field that is not used by the SCD update logic. This field may not be updated even if it is mapped. An attempt to update the field will result in an error status for the entire record.

    • SCD1 - Update - designates a field that stores an SCD value. This is the value that slowly changes over time.

    • Audit - Last Changed - stores the date and time when the record with the SCD value was last updated.

    • Audit - SCD1 Change - stores the date and time when the record with the SCD Type 1 value was last updated.

  • For SCD Type 2 updates:

    • Business Key - designates the field holding the key that is normally used to identify records in the table.

    • Surrogate Key - designates the field holding an extra key that identifies versions of the SCD value with the same business key.

    • Not Used - designates a field that is not used by the SCD update logic. However, this field may still be updated if it is mapped.

    • Update Not Allowed - designates a field that is not used by the SCD update logic. This field may not be updated even if it is mapped. An attempt to update the field will result in an error status for the entire record.

    • SCD2 - Update and Insert - this field stores an SCD value changing over time. A new record will be added each time the SCD value changes. The existing record may be updated with the new ExpirationDate.

    • Current Record Designator - stores the Active Value if the record has the current version of the SCD value. Otherwise, it stores the Inactive Value. Active Value and Inactive Value should be entered in the appropriate cells in the grid next to the Current Record Designator.

    • SCD2 Effective Date - stores the effective date of the SCD value in the record.

    • SCD2 Expiration Date - stores the expiration date of the SCD value in the record. Expiration Date is Null in the record storing the current SCD value.

    • Audit - Created - stores the date and time when a new record with a new SCD value was created.

    • Audit - Last Changed - stores the date and time when the record with an SCD value was last updated.

    • Audit - SCD2 Change - stores the date and time when the record with an SCD Type 2 value was last updated.

    • Version - stores the version number of the SCD value.

Click the icon to create a new rule.

Add other rules if necessary. To delete an existing rule, select it and click the icon.