Change Data Capture
Capture, Compare, Report, Update, Refresh, Store
Challenges
Operational data is subject to constant changes, additions, and deletions compared to historical data. Data Warehouse Maintenance and enterprise groups must compare tables and files, as well as identify, isolate, and analyze changes to data.
- complex or cumbersome
- vulnerable and overloadable
- limited to a single database
- dependent on log data and cannot easily use changed data in meaningful reports.
Furthermore, regularly reading and updating the entire database with increasing data volume is an impractical alternative to warehouse updating. In other words, the feasibility of in-database operations for deltas decreases with increasing table size.
Solutions
Capture and reference MS SQL, MySQL, Oracle, and PostgreSQL targets in real-time as new rows are inserted, updated, or deleted by IRI Ripcurrent use to monitor these protocols for changes to the source data
OR you report the deltas (changes) offline in faster, portable, and more functional Sort Control Language(SortCLJobs in IRI Voracity or IRI CoSort. SortCL's approach to Change Data Capture (CDC) is data-centric rather than log-based:
- enables multiple source change analyses (not just from one or a few RDBs, but from all, as well as from flat files)
- supports the segmentation of inserts, deletions, and updates
- can be cumulative or incremental (update CDC)
- enables meaningful BI (reporting) generation based on update values
- excludes protocol sniffers, DB-specific triggers, or other complex designs
Compare massive tables, files, and other connected Sources with consolidated sorting, join, and condition logic that identifies the deltas, while simultaneously:
- Transformation of data (cleaning, calculation, aggregation, etc.)
- Convert of data types, field positions, and target formats
- Protection through field-level encryption, data masking, etc.
- Report in custom detail and overview layouts
- Updating data warehouse tables with real-time updates
- Bulk Load pre-sorted data using DB Load Utilities
- Output in flat files for archiving, replication, or distribution
Regardless of the goals, this approach removes a larger workload from the DBMS (which relies on triggers to update CDC tables). It also allows for concurrent Business Intelligence from the change details.
In a scenario with a large data change, you might want to use a SELECT query in the compatible IRI FACT (Fast Extract) Tool for Oracle, DB2, etc. beginning to transfer rows generated after a specific timestamp to a flat file. Unqualified dumps are also very fast with FACT and allow for the analysis of entire transaction sets in a subsequent step.
At the same time, you can count the number of new, changed, or missing records in your reports or view SortCL's runtime statistics (which count inner and outer matches for each join). Label, log, and analyze changes in transaction data to spot red flags and forensically assess trends.
