Change Data Capture

 Change data capture (CDC) is the process of capturing changes made at the data source and applying them throughout the enterprise. CDC minimizes the resources required for ETL ( extract, transform, load ) processes because it only deals with data changes. The goal of CDC is to ensure data synchronicity.
There are four methods to handle Change Data Capture(CDC):

1. Timestamp-based CDC
                           Timestamp column in the source table is used to capture the date and time of the last change, whether it’s a new entry or an update to an existing row

* Simple Method
* Cannot identify deleted records


2. Trigger-based CDC
                           Database triggers are added to the source tables so all changes (inserts, updates, deletes) are replicated in a second set of tables specifically used for the CDC process. Only the “changed” records that are captured in the CDC tables are used to update the data warehouse during the ETL process

 * Complex
 * Can identify new, updated, and/or deleted records
 * Suitable for low-latency (near real-time) data warehouse updates

3. Snapshot Merge
                           This is a simple technique where regularly scheduled table exports (“snapshots”) or staging tables are used to identify changed records. By calculating the difference between the current and previous snapshots, all new, updated or deleted records can be captured and loaded into the data warehouse

* Can identify new, updated, and/or deleted records
* Has database design and performance implications
* Not practical for very large tables unless using efficient snapshot systems
* Not suitable for low-latency data warehouse updates


4. Log Scraping
                          Database applications can be configured to track all activity in log files. For CDC purposes, those application log files can be scanned and parsed (“scraped”) to identify when changes occur and capture those records

 * Low impact on database
* No impact on source application
* Can deliver “real-time” (very low-latency) updates to data warehouse
* Complex log formats make parsing difficult
* Log formats are different between database applications and often change

Comments

Post a Comment

Popular posts from this blog

Increase Java Memory For Pentaho Data Integration

Pentaho runs inside a Java virtual machine, and hence is bound by the properties of that VM. These optimisations can apply to just about any Java application, including the Pentaho BI Server and GUI tools. Method to increase memory allocation: Open the file spoon.sh or spoon.bat in a text editor Look for a section that looks like this: # ****************************************************************** # ** Set java runtime options                                     ** # ** Change 256m to higher values in case you run out of memory.  ** # ****************************************************************** OPT="-Xmx256m -cp $CLASSPATH -Djava.library.path=$LIBPATH -DKETTLE_HOME=$KETTLE_HOME -DKETTLE_REPOSITORY=$KETTLE_REPOSITORY -DKETTLE_USER=$KETTLE_USER -DKETTLE_PASSWORD=$KETTLE_PASSWORD -DKETTLE_PLUGIN_PACKAGES=$KETTLE_PLUGIN_PACKAGES" Change the -Xmx parameter to alter the maximum heap size, e.g. -Xmx1024m
Read more

Different Match Models in tMap with example

Unique Match:     (Also called Last match) If several matches are found in the Inner Join, i.e. the last row matching the explicit join as well as the filter will be added to the output flow. First Match: If several matches are found in the Inner Join, i.e. the first row matching the explicit join as well as the filter will be added to the output flow. All Match: If several matches are found in the Inner Join, i.e. rows matching the explicit join as well as the filter, all of them will be added to the output flow Example: deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 Outputs: 1. Unique match deptid   sales 1           1000 2           5000 3           6000 2. All matches deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 3. First match deptid   sales 1           1000 2           2000 3           3000
Read more

Simple Transformation from a csv to Excel Output

Image
Lets start with a basic transformation to convert your comma seperated values file input to excel output Step 1: Launch kettle 4.1 Step 2: select file>new>transformation Step 3: drag input>csv input & output>csv output on the workspace Step 4: Arrange them one after another & create a hop/connection between them by holding the shift key & dragging the arrow from center of csv input step to excel output step Step 5 : double click the csv input step & configure as shown in the figure above. Choose the filenae location & select get fields button to auto retrieve fields. Step 6: double click the excel output step & give the desired path location of the new excel file which is to be created, click the fields tab & select the get fields button to retrieve all the fields currently present in the stream. Step 7: Click the run button above the workspace else Select --> Action>Run from the menu. Your file is automatically crea
Read more