# Database Table Source

Astera Database Source provides the functionality to retrieve data from a database table. Additionally, Database Table Source provides change data capture functionality to perform incremental reads. Database Table sources supports multi-way partitioning which partition database table into multiple chunks and reads these chunks in parallel. This features brings about major performance increase for database reads. Database source enables you to specify where clause and sort order to control the result set.

## **Incremental Read**

Database Table Source provides incremental read functionality based in the concept of audit fields. Incremental read is one of the three change data capture approaches supported by Astera. Audit fields are fields that are updated when a record is created or modified. Examples of audit fields include created date time, modified date time, and version number.

Incremental read works by keeping track of the highest value for the specified audit field. On the next run, only the records with value higher than the saved value are retrieved. This feature is useful in situations where two applications need to be kept in sync and the source table maintains audit field values for rows.

## **Database Table Partitioning**

Database table partitioning feature partitions database table in a number of user-specified chunks. These chunks are then retrieved in parallel to improve the overall throughput for the job. When combined with the highly parallel Astera engine, this feature substantially improves the performance of large data movement jobs. Partitioning is done by selecting a field and defining value ranges for each partition. At runtime, Astera generates and runs multiple queries against the source table and processes the result set in parallel.

## **Steps**

Adding a _Database Table Source_ object allows you to transfer data from a database table. An example of what a database table source object looks like is shown below:

![](https://3768133094-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FH4WCHHphSt0ALtMJlqot%2Fuploads%2FpDYYehENiErNRLscjDDq%2F0.jpeg?alt=media)

To configure the properties of a _Database Table Source_ object after it was added to the dataflow, right-click on it and select _Properties_ from the context menu. The following properties are available:

* _Source Connection screen_ – allows you to enter the connection information for your source, such as server name, database and schema, as well as credentials for connecting to the selected source.
* _Pick Source Table screen_: Select a source table using the _Pick Table_ dropdown.
  * Select _Full Load_ if you want to read the entire table.
  * Select _Incremental Load Based on Audit Fields_ to perform an incremental read starting at a record where the previous read left off.

Incremental load is based around the concept of Change Data Capture (CDC), which is a set of reading and writing patterns designed to optimize large-scale data transfers by minimizing database writing in order to improve performance. CDC is implemented in Astera using Audit Fields pattern. The Audit Fields pattern uses create time or last update time to determine the records that have been inserted or updated since the last transfer and transfers only those records.

Advantages

* Most efficient of CDC patterns. Only records that were modified since the last transfer are retrieved by the query thereby putting little stress on the source database and network bandwidth

Disadvantages

* Requires update date time and/or create date time fields to be present and correctly populated
* Does not capture deletes
* Requires index on the audit field(s) for efficient performance

To use the Audit Fields strategy, select the Audit Field and an optional Alternate Audit Field from the appropriate dropdown menus. Also, specify the path to the file that will store incremental transfer information.

* _WHERE Clause screen:_ You can enter an optional SQL expression serving as a filter for the incoming records. The expression should start with the WHERE word followed by the filter you wish to apply.

For example, _WHERE CreatedDtTm >= ‘2001/01/05’_

* _General Options screen_: The _Comments_ input allows you to enter comments associated with this object.
