DataX: Data Integration and ETL/ELT

Data Integration and ETL/ELT tests whether you can move data between systems without losing, duplicating or silently corrupting it. Expect questions on full versus incremental loads, change data capture, idempotent reruns, and tool configuration - for DataX specifically: job JSON structure, parallelism and error tolerance.

Core rules and concepts

• Extract patterns: full snapshot (simple, expensive), incremental by high-watermark column such as updated_at or a monotonic id (watch late-arriving updates and clock skew), and log-based CDC (captures deletes and intermediate states; depends on log retention).
• Load patterns: ETL transforms before loading a curated store; ELT lands raw data first and transforms in-warehouse, preserving raw history and using warehouse compute.
• Idempotency is the design goal: rerunning a partition must produce the same result. Achieve it with partition overwrite, MERGE/upsert on a key, or dedup by key plus sequence at read time.
• Orchestration encodes dependencies, retries, backfills and alerting. A job that succeeds but writes zero rows needs a volume/freshness check, not just an exit code.
• DataX model: the framework splits a job into tasks using the reader's split logic, runs them in task groups under a channel limit, and applies speed limits by channel, byte and record. errorLimit sets how many dirty records (record) and what share (percentage) may fail before the job aborts. splitPk enables parallel reads on a numeric primary key; querySql mode issues one statement and is not split. Transformers handle light field mapping; heavy business logic belongs downstream.
• Reconciliation: row counts, sum checks, null and distinct profiles on both sides after each run.

Worked example

A 200M-row MySQL orders table must land in Hive nightly. Extract incrementally on updated_at with a one-day lookback to catch late commits; set splitPk to the numeric, non-null order_id so tasks split evenly; cap the channel at 8 and bytes at 10 MB/s to protect the source; set errorLimit record=0 for financial data and alert rather than accept dirty rows. Verify counts and sums on both sides.

Traps

Using splitPk on a string or heavily skewed column; expecting parallelism from querySql; a where clause that excludes boundary rows on rerun; mixing time zones so a day loads twice; treating "job succeeded" as "data correct".