Correction: Correction Regarding “Data Extraction Methods in Big Data ETL”
Follow-up: Local Hash Comparison in Big Data ETL to Speed Up Comparison
You can’t talk about big data warehousing without talking about ETL. The term is most common in warehouses, but its scope isn’t limited to them. It’s an extremely important step, so here’s a quick introduction to how ETL extracts and compares data.
What Is ETL
ETL stands for Extract-Transform-Load. It describes the process of taking data from the source through extraction, transformation, and loading into the destination. Data from business systems is extracted, cleansed, transformed, and then loaded into the warehouse, with the goal of consolidating scattered, messy, non-standardized data across the enterprise into one place to serve as the basis for decision-making analysis.
Full Extraction
Full extraction is a complete copy. It’s simple and there isn’t much to say about it. Most of the time you’re doing incremental sync instead.
Incremental Extraction
Incremental extraction means capturing the inserts, updates, and deletes that happened in the database since the last extraction. Incremental extraction generally isn’t allowed to destabilize the business system, so you can’t lock tables or run large-scale queries against it.
Triggers
Create insert, update, and delete triggers in the business system, and append a row to a temp table whenever data changes. That gives you the delta directly. The downside is that it intrudes on the source system and hurts its performance.
Timestamps
An incremental capture approach based on comparing increasing values: add a timestamp column to the source table, update it whenever the row is modified, and at extraction time compare the current system time against the timestamp column to decide which rows to pull. This approach can’t detect deleted rows, and it’s also intrusive — you have to add the column and make sure every write updates it.
Hash Comparison
This is the approach I want to focus on. Take a look at the diagram I drew first, then I’ll walk through what each node does.
First, determine the comparison time window and the columns to compare. It’s essential that both sides are compared within the same window, otherwise the comparison is meaningless. The most common choice is a unified window on the time dimension — for example, comparing one day’s worth of data.
Then send a SQL query to each of the source and target databases, pulling back the primary key (say id) and the hash of the columns being compared. Now you have both sides’ primary keys and hash pseudo-columns within one unified data window.
Compute the complement of each of the two sets: CuB is B’s complement, CuA is A’s complement. Those are the changes on each side. The intersection is unchanged data and can be discarded.
Once you have both complements, CuB and CuA, use the primary keys to fetch the real rows from each data source. Note that the comparison operations below work on real data, not the hash pseudo-columns.
Using the business-dimension primary key, compute the complements and intersection of the two sets again. Note that this time it’s the business-dimension unique key (a national ID number, for example), not the database id from the previous step. This distinction matters. The CuB you get here is the data to be inserted into source B; CuA is the data to be handed to A for insertion. There’s also an intersection, which is the conflicting data — the two sides disagree, and depending on user settings or an explicit user decision, one side wins and overwrites the other.
So how do you identify deleted rows? Before deploying an ETL tool you need to establish data standards — data governance — which mandate delete markers and update markers.
Logs
Analyze the database’s own logs to determine what changed. Oracle’s Changed Data Capture (CDC) is the representative technology here. CDC was introduced in Oracle9i and helps you identify data that changed since the last extraction. With CDC, data is captured as inserts, updates, and deletes happen against the source table, and the changed data is saved in database change tables. You can then capture the changes and expose them to the target system in a controlled way through database views.
When There Is No Data Governance
All three approaches above require changes on the source side — adding triggers, adding timestamps, adding delete marker columns. But in reality, many data sources won’t let you modify their schema, or won’t cooperate with your adaptation.
In that case, to detect inserts, updates, and deletes, you need to build a mirroring mechanism inside the ETL tool itself: take a snapshot of the source, compare it against the previous snapshot, and find what changed. This approach is almost entirely non-intrusive, but the trade-off is a drop in execution efficiency — you’re moving large volumes of data, which affects the stability of the original system, and you also have to store and manage the mirror snapshots. It’s the option of last resort, when you have no choice but to sacrifice efficiency.
Design diagram download: ETL.pdf