In databases, change data capture is a set of software design patterns used to
determine the data that has changed so that action can be taken using the
changed data. Also, Change data capture is an approach to data integration that
is based on the identification, capture and delivery of the changes made to
enterprise data sources. CDC solutions occur most often in
data-warehouse environments since capturing and preserving the state of
data across time is one of the core functions of a data warehouse, but CDC
can be utilized in any database or data repository system.
Methodology System developers can set up CDC
mechanisms in a number of ways and in any one or a combination of system
layers from application logic down to physical storage.
In a simplified CDC context, one computer system has data believed to
have changed from a previous point in time, and a second computer system needs
to take action based on that changed data. The former is the source, the
latter is the target. It is possible that the source and target are the same
system physically, but that would not change the design pattern logically.
Not uncommonly, multiple CDC solutions can exist in a single system.
= Timestamps on rows = Tables whose changes must be captured
may have a column that represents the time of last change. Names such as
LAST_UPDATE, etc. are common. Any row in any table that has a timestamp in that
column that is more recent than the last time data was captured is considered to
have changed. = Version Numbers on rows =
Database designers give tables whose changes must be captured a column that
contains a version number. Names such as VERSION_NUMBER, etc. are common. When
data in a row changes, its version number is updated to the current
version. A supporting construct such as a reference table with the current
version in it is needed. When a change capture occurs, all data with the latest
version number is considered to have changed. When the change capture is
complete, the reference table is updated with a new version number.
Three or four major techniques exist for doing CDC with version numbers, the
above paragraph is just one. Use in Optimistic Locking
Version numbers can be useful with optimistic locking in ACID transactional
or relational database management systems. For an example in
read-then-update scenarios for CRUD applications in relational database
management systems, a row is first read along with the state of its version
number; in a separate transaction, an SQL UPDATE statement is executed along
with an additional WHERE clause that includes the version number found from
the initial read. If no record was updated, it usually means that the
version numbers didn't match because some other action/transaction had
already updated the row and consequently its version number. Several object
relational mapping tools use this method to detect for optimistic locking
scenarios. = Status indicators on rows =
This technique can either supplement or complement timestamps and versioning. It
can configure an alternative if, for example, a status column is set up on a
table row indicating that the row has changed. Otherwise, it can act as a
complement to the previous methods, indicating that a row, despite having a
new version number or an earlier date, still shouldn't be updated on the
target. = TimeStatus on rows =
This approach combines the three previously discussed methods. As noted,
it is not uncommon to see multiple CDC solutions at work in a single system,
however, the combination of time, version, and status provides a
particularly powerful mechanism and programmers should utilize them as a
trio where possible. The three elements are not redundant or superfluous. Using
them together allows for such logic as, "Capture all data for version 2.1 that
changed between 62005 12:00 a.m. and 72005 12:00 a.m. where the status code
indicates it is ready for production." = Triggers on tables =
May include a publish/subscribe pattern to communicate the changed data to
multiple targets. In this approach, triggers log events that happen to the
transactional table into another queue table that can later be "played back".
For example, imagine an Accounts table, when transactions are taken against this
table, triggers would fire that would then store a history of the event or
even the deltas into a separate queue table. The queue table might have schema
with the following fields: Id, TableName, RowId, TimeStamp, Operation.
The data inserted for our Account sample might be: 1, Accounts, 76, 112008
12:15am, Update. More complicated designs might log the actual data that
changed. This queue table could then be "played back" to replicate the data from
the source system to a target. [More discussion needed]
An example of this technique is the pattern known as the log trigger.
= Event Programming = Coding a change into an application at
appropriate points is another method that can give intelligent discernment
that data changed. Although this method involves programming vs. more easily
implemented "dumb" triggers, it may provide more accurate and desirable CDC,
such as only after a COMMIT, or only after certain columns changed to certain
values - just what the target system is looking for.
= Log scanners on databases = Most database management systems manage
a transaction log that records changes made to the database contents and to
metadata. By scanning and interpreting the contents of the database transaction
log one can capture the changes made to the database in a non-intrusive manner.
Using transaction logs for change data capture offers a challenge in that the
structure, contents and use of a transaction log is specific to a
database management system. Unlike data access, no standard exists for
transaction logs. Most database management systems do not document the
internal format of their transaction logs, although some provide programmatic
interfaces to their transaction logs. Other challenges in using transaction
logs for change data capture include: Coordinating the reading of the
transaction logs and the archiving of log files.
Translation between physical storage formats that are recorded in the
transaction logs and the logical formats typically expected by database users.
Dealing with changes to the format of the transaction logs between versions of
the database management system. Eliminating uncommitted changes that the
database wrote to the transaction log and later rolled back.
Dealing with changes to the metadata of tables in the database.
CDC solutions based on transaction log files have distinct advantages that
include: minimal impact on the database.
no need for programmatic changes to the applications that use the database.
low latency in acquiring changes. transactional integrity: log scanning
can produce a change stream that replays the original transactions in the order
they were committed. Such a change stream include changes made to all
tables participating in the captured transaction.
no need to change the database schema Confounding factors
As often occurs in complex domains, the final solution to a CDC problem may have
to balance many competing concerns. = Unsuitable source systems =
Change data capture both increases in complexity and reduces in value if the
source system saves metadata changes when the data itself is not modified.
For example, some Data models track the user who last looked at but did not
change the data in the same structure as the data. This results in noise in the
Change Data Capture. = Tracking the capture =
Actually tracking the changes depends on the data source. If the data is being
persisted in a modern database then Change Data Capture is a simple matter
of permissions. Two techniques are in common use:
Tracking changes using Database Triggers Reading the transaction log as, or
shortly after, it is written. If the data is not in a modern database,
Change Data Capture becomes a programming challenge.
= Push versus pull = Push: the source process creates a
snapshot of changes within its own process and delivers rows downstream.
The downstream process uses the snapshot, creates its own subset and
delivers them to the next process. Pull: the target that is immediately
downstream from the source, prepares a request for data from the source. The
downstream target delivers the snapshot to the next target, as in the push
model. See also
Slowly Changing Dimension Referential integrity
External links LinkedIn Databus
Data Replication as a Service Best Practices
Attunity Change Data Capture IBM Infosphere CDC
Tutorial on setting up CDC in Oracle 9i Tutorial on setting up SQL Azure Change
Data Capture Details of the CDC facility included in
Microsoft Sql Server 2008 Feb '08 CTP SymmetricDS - Heterogeneous, Cross
Platform CDC Gamma-Soft CDC Technology
Talend References