Overview
Data Ingestion & Transformation is a quarter of the ABDE exam. It covers building ETL and ELT pipelines, cleansing and normalizing records, choosing between batch and real-time acquisition, coping with schema changes in source systems, and connecting to relational databases, REST APIs and file drops. DASCA names Talend Open Studio and dbt as reference tools, which signals the two styles you must understand: a visual integration job that transforms in flight, and SQL models that transform inside the warehouse after loading.
The domain exists because every downstream consumer inherits the quality of intake. A duplicated order, a silently dropped column or a timezone shift introduced at ingestion surfaces later as a wrong dashboard or a skewed model. Storage design (Domain 2) decides where landed data lives, orchestration (Domain 3) runs these jobs on schedule, and governance (Domain 4) validates what they produce.
How it's tested
Expect short scenarios describing a source system, a freshness requirement and a constraint such as source load, cost or changing columns. Questions ask for the BEST or MOST appropriate loading pattern, transformation location or fix. The right answer usually matches the latency the business actually needs while protecting the source and keeping reruns safe.
-
Choose the loading pattern
A scenario gives data volume, change rate and freshness needs, and you pick full load, incremental load by watermark, or change data capture.
-
Place the transformation
You decide whether logic belongs in flight (ETL) or inside the warehouse after loading (ELT), based on where compute is cheap and whether raw data must be kept.
-
Diagnose a broken intake
A pipeline produces duplicates, nulls or a failed load after a source change, and you identify the cause, such as a non-idempotent insert or an unhandled new column.
A retailer loads a 400-million-row orders table every night with a full reload, and the source database slows down for customers. Rows carry a reliable last_updated timestamp, and analysts only need yesterday's changes by 6 a.m. The fix is an incremental extract filtered on last_updated above the stored high-water mark, merged into the target by order key. Streaming would exceed the stated latency need and add cost.
Practical applications 5
Official practical applications from the DASCA outline, in outline order.
1.1 Implementing ETL/ELT pipelinesWhere transformation happens and how pipeline stages fit together.
- What it means
- ETL transforms data in a separate engine before loading it into the target, while ELT loads raw data first and transforms it inside the warehouse or lakehouse using its own compute, often with SQL models such as dbt.
- What to understand
- ELT keeps an untouched raw layer you can replay when logic changes, and it suits elastic cloud warehouses; ETL still fits when data must be masked or reduced before it lands, or when the target has little compute. Both need idempotent loads, meaning a rerun produces the same result instead of duplicate rows, usually by merging (upserting) on a business key or overwriting a partition.
- How it shows up
- A scenario says business rules change often and analysts want to rebuild history, which points to ELT with a retained raw layer; another says personal data must never reach the warehouse unmasked, which points to transformation before load.
- Common confusion
- Candidates treat ELT as simply ETL with the letters swapped, missing that the real difference is where compute runs and whether raw data is preserved for reprocessing.
1.2 Data cleansing and normalizationMaking records consistent, deduplicated and typed correctly.
- What it means
- Cleansing fixes or removes bad records (trimming whitespace, standardizing date and currency formats, handling nulls, deduplicating), and normalization makes values consistent, such as one country code standard or one timezone like UTC.
- What to understand
- Deduplication needs a defined key and a rule for which record survives, typically the latest by update timestamp. Bad rows are better routed to a quarantine (dead-letter) table with the reason than silently dropped, so data loss stays visible and recoverable. In ingestion, normalization means standardizing values, which is different from database normalization into third normal form.
- How it shows up
- A question shows customer counts inflated after a retry, or revenue off because some sources send cents and others dollars, and asks for the cleansing step that fixes it at the right layer.
- Common confusion
- Mixing value normalization (consistent formats and units) with schema normalization (splitting tables to remove redundancy), or assuming that deleting invalid rows is an acceptable default.
1.3 Handling batch and real-time acquisitionMatching acquisition frequency to the business latency need.
- What it means
- Batch acquisition collects data at intervals (hourly, nightly) and processes it as a bounded set, while real-time acquisition captures events continuously as they occur, often through change data capture (CDC) or an event stream.
- What to understand
- Choose the cheapest approach that meets the stated latency: a dashboard reviewed each morning does not justify streaming. Incremental batch uses a high-water mark such as a max timestamp or ID, and log-based CDC reads the database transaction log, catching updates and deletes without querying source tables. Micro-batching (small batches every few seconds or minutes) sits between the two.
- How it shows up
- The scenario states a freshness requirement in minutes or hours and a source constraint, and the correct answer is the least complex pattern that satisfies both.
- Common confusion
- Assuming real-time is always better, or assuming a timestamp-based incremental load captures hard deletes, which it cannot because deleted rows no longer exist to be queried.
1.4 Managing schema evolutionKeeping pipelines working when source structures change.
- What it means
- Schema evolution is the handling of source changes such as added columns, renamed fields, type changes or dropped columns without breaking loads or corrupting history.
- What to understand
- Additive changes (a new nullable column) are usually backward compatible and can be absorbed automatically, while renames, type narrowing and removals are breaking changes that need versioning, mapping or a coordinated migration. Self-describing formats such as Avro and Parquet carry their schema with the data, and a schema registry can enforce compatibility rules for event data.
- How it shows up
- A load fails or silently fills a column with nulls after a source release, and you choose between schema-on-read tolerance, explicit mapping and a compatibility check before deploy.
- Common confusion
- Treating every schema change as equally safe; a renamed column looks like one dropped column plus one new column, which can silently null out history.
1.5 Establishing connectivity with diverse source systemsReliable extraction from databases, APIs and files.
- What it means
- This covers connecting to relational databases through JDBC or ODBC, to REST APIs with authentication and pagination, and to file sources such as CSV, JSON or Parquet landing in object storage or SFTP folders.
- What to understand
- Each source type fails differently: databases can be overloaded by heavy queries, so read replicas or CDC protect production; APIs enforce rate limits and need pagination, retries with exponential backoff and token refresh; files arrive late, partially written or twice, so pipelines check for completion markers and track processed file names. Credentials belong in a secrets manager, never in job code.
- How it shows up
- A scenario describes intermittent API failures (HTTP 429 too many requests) or half-loaded files, and asks for the change that makes intake reliable.
- Common confusion
- Retrying immediately in a tight loop on rate-limit errors, which makes throttling worse, instead of backing off and honoring the API's retry guidance.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
ETL vs. ELT
ETL transforms data in a separate engine before loading, while ELT lands raw data in the target first and transforms it there with the target's own compute.
- Remember
- ELT preserves raw data so you can rebuild outputs when business logic changes, which is why it dominates on scalable cloud warehouses and lakehouses.
- Tested as
- Questions ask where a transformation should run given data sensitivity, target compute and the need to reprocess history.
- Trap
- Choosing ETL by habit when the scenario stresses frequent logic changes and replaying history, both of which favor keeping a raw layer.
-
Idempotent load
A load is idempotent when running it twice for the same input leaves the target in the same state as running it once.
- Remember
- Achieve it with MERGE or upsert on a business key, or by overwriting a whole date partition, never with blind INSERT on retry.
- Tested as
- Duplicate-row scenarios after a retry or backfill are almost always testing idempotency.
- Trap
- Adding a DISTINCT in a report to hide duplicates instead of fixing the non-idempotent write that created them.
-
High-water mark
A high-water mark is the stored maximum value (timestamp or increasing ID) reached by the last successful extract, used to pull only newer rows next time.
- Remember
- Update the mark only after the load commits, and allow a small overlap window so late-committed rows are not skipped, relying on idempotent merge to absorb repeats.
- Tested as
- Incremental batch questions test whether you save the mark at the right moment and pick a reliable column.
- Trap
- Advancing the mark before the load finishes, which permanently skips rows when the job fails midway.
-
Change data capture (CDC)
CDC captures inserts, updates and deletes from a source, ideally by reading the database transaction log (log-based CDC, as tools like Debezium do).
- Remember
- Log-based CDC captures deletes and adds little query load on the source, while timestamp polling misses hard deletes and depends on a trustworthy update column.
- Tested as
- The clue is usually 'deletes must be reflected' or 'the source cannot handle heavy queries'.
- Trap
- Selecting a timestamp-based incremental query when the scenario requires deleted records to disappear downstream.
-
Full vs. incremental load
A full load re-extracts the whole dataset each run, and an incremental load extracts only rows added or changed since the previous run.
- Remember
- Full loads are simple and self-correcting for small tables, while incremental loads are needed once volume or source load makes full extraction too slow or costly.
- Tested as
- Scenarios with growing tables and slow nightly jobs test whether you switch to incremental extraction.
- Trap
- Assuming incremental is always better, even for a small reference table where a full refresh is simpler and avoids drift.
-
Dead-letter or quarantine table
A quarantine table stores records that failed validation or parsing, together with the error reason and source details, instead of dropping them.
- Remember
- Quarantining keeps the main pipeline flowing while making bad data visible, countable and replayable after a fix.
- Tested as
- Questions about malformed records ask for the approach that neither halts the whole load nor loses data silently.
- Trap
- Choosing to fail the entire batch for a handful of malformed rows when the requirement is continuous delivery with traceability.
-
Schema-on-write vs. schema-on-read
Schema-on-write enforces structure when data is loaded (typical of warehouses), while schema-on-read stores raw data and applies structure when it is queried (typical of data lakes and Hive external tables).
- Remember
- Schema-on-read tolerates new or messy fields at intake but pushes errors to query time, so it needs validation later.
- Tested as
- Used in questions about absorbing frequently changing source formats.
- Trap
- Believing schema-on-read removes the need for data quality checks.
-
Backward-compatible schema change
A backward-compatible change lets consumers using the new schema still read data written with the old one, such as adding an optional field with a default.
- Remember
- Adding nullable or defaulted fields is usually safe; renaming, removing a required field or narrowing a type usually breaks consumers.
- Tested as
- Schema evolution questions ask which source change can be absorbed automatically and which needs coordination.
- Trap
- Treating a rename as harmless, when to most readers it is a dropped column plus a new empty one.
-
dbt model
A dbt model is a SELECT statement in a version-controlled file that dbt compiles and materializes in the warehouse as a view, table or incremental table, with dependencies declared through ref().
- Remember
- dbt does the T in ELT inside the warehouse; it does not extract or load data from sources.
- Tested as
- Tool-fit questions test whether you know dbt transforms already-loaded data, while Talend-style jobs handle extraction and in-flight transformation.
- Trap
- Picking dbt to pull data from a REST API, a job it is not designed to do.
-
Exponential backoff
Exponential backoff retries a failed call after progressively longer waits (for example 1, 2, 4, 8 seconds), often with random jitter.
- Remember
- It is the standard response to transient errors and HTTP 429 rate limiting, ideally honoring a Retry-After header when the API sends one.
- Tested as
- API ingestion scenarios with intermittent failures test this pattern.
- Trap
- Retrying instantly in a loop, which deepens throttling and can get the client blocked.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
ETL vs. ELT
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| ETL | Extract, transform in a separate engine, then load the finished result | Transformation runs before the data reaches the target, so raw data may never land | Forgetting that changed logic cannot be replayed if raw data was not kept |
| ELT | Extract, load raw data, then transform inside the warehouse or lakehouse | Uses target compute and preserves a raw layer for reprocessing | Loading sensitive raw fields into a shared warehouse when policy requires masking first |
Full load vs. timestamp incremental vs. log-based CDC
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Full load | Re-extract the entire table every run | Simplest and self-healing, but cost and source load grow with table size | Keeping it for very large tables whose nightly window is overrun |
| Timestamp incremental | Extract rows whose update column exceeds the last high-water mark | Cheap and easy, but misses hard deletes and relies on a reliable update column | Using it when deletes must propagate downstream |
| Log-based CDC | Read inserts, updates and deletes from the database transaction log | Captures every change including deletes with little query load on the source | Choosing it for a nightly report that needs no more than a daily snapshot |
Batch vs. micro-batch vs. streaming acquisition
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Batch | Bounded sets processed on a schedule such as hourly or nightly | Latency of hours, lowest cost and simplest recovery | Rejecting it for a once-a-day report because streaming sounds more modern |
| Micro-batch | Small batches every few seconds to minutes | Near-real-time latency while keeping batch-style processing semantics | Calling it true event-at-a-time streaming |
| Streaming | Each event processed continuously as it arrives | Lowest latency, but needs state, ordering and late-data handling | Choosing it when the stated requirement is measured in hours |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Real-time by default
- What candidates pick
- A Kafka-based streaming pipeline for a sales report reviewed each morning.
- Why it looks right
- Streaming sounds modern and more capable, so it feels like the senior engineer's answer.
- Why it's wrong
- It adds cost, state management and operational burden without meeting any stated need; a nightly or hourly incremental batch satisfies the requirement more simply.
Clue to watch for The scenario gives a freshness need in hours or names a daily consumer such as a morning dashboard.
-
Retry with INSERT
- What candidates pick
- Simply rerunning the failed job to recover a partially loaded batch.
- Why it looks right
- Rerunning is the natural first instinct and usually works in tutorials.
- Why it's wrong
- If the job appends rows, the portion that loaded before the failure is inserted again, creating duplicates; recovery must rely on idempotent MERGE or partition overwrite.
Clue to watch for Phrases like 'the job failed halfway' or 'counts doubled after the retry'.
-
Timestamp polling for deletes
- What candidates pick
- An incremental query on last_updated to keep the target in sync, including removed records.
- Why it looks right
- It already works for inserts and updates, so extending it seems natural.
- Why it's wrong
- A hard-deleted row no longer exists in the source, so no query can return it; log-based CDC or soft-delete flags are needed.
Clue to watch for The requirement says cancelled or deleted records must also disappear from the target.
-
dbt as an extractor
- What candidates pick
- dbt to pull data from a SaaS API into the warehouse.
- Why it looks right
- dbt is named by DASCA as an ingestion-and-transformation tool, so it seems to cover the whole pipeline.
- Why it's wrong
- dbt runs SQL transformations on data already in the warehouse; extraction and loading need an integration tool such as Talend or a connector service.
Clue to watch for The scenario's task is moving data out of a source system rather than reshaping data already loaded.
-
Fail the whole batch
- What candidates pick
- Stopping the entire nightly load because 30 of 5 million rows have invalid dates.
- Why it looks right
- Strict failure feels like the safest way to protect data quality.
- Why it's wrong
- It blocks all consumers over a tiny defect; routing bad rows to a quarantine table with alerts keeps delivery on time while preserving the evidence for correction.
Clue to watch for The scenario stresses on-time delivery and a small, identifiable set of bad records.
-
Ignoring source load
- What candidates pick
- Running heavy extraction queries directly against the production OLTP database during business hours.
- Why it looks right
- It is the most direct path to the freshest data.
- Why it's wrong
- Large scans compete with customer transactions; a read replica, off-peak window or log-based CDC protects the source.
Clue to watch for Mentions that the application slows down or the database team limits query load.
Answer framework
Use these steps whenever an ABDE question asks how to bring data from a source into the platform.
- Identify the real freshness requirement and pick the simplest acquisition mode, batch, micro-batch or streaming, that meets it.
- Check what must be captured: inserts only, updates, or deletes as well, and choose full load, high-water-mark incremental or log-based CDC accordingly.
- Protect the source by using replicas, off-peak windows, pagination and backoff, and keep credentials in a secrets manager.
- Decide where transformation runs: before load when data must be masked or reduced, after load (ELT) when raw history must be replayable.
- Make every write idempotent and quarantine invalid records with reasons, so retries, backfills and schema changes never duplicate or silently lose data.
Practice question
Want more like this? The ABDE practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Start with the ETL versus ELT distinction and idempotency, because nearly every other ingestion question depends on them. Build one small pipeline yourself: land a CSV and a JSON API response in a raw schema, then write two or three dbt models that clean and join them, and rerun everything twice to confirm the row counts do not change.
Next, work through load patterns. Practice deciding between full reloads, high-water-mark incremental loads and log-based CDC, and write down for each what it captures, what it costs the source and how it recovers from a failure. Candidates most often underestimate deletes, the timing of watermark updates, and the difference between value normalization and database normalization.
Then cover schema evolution and source connectivity: classify sample schema changes as safe or breaking, and sketch how an API extractor handles pagination, HTTP 429 responses and expired tokens.
Afterwards, do timed practice questions on this domain and review every miss by naming the scenario clue you overlooked, such as a latency requirement, a delete requirement or a source-load warning. Then move on to Domain 2, since storage layout decisions build directly on how data lands.
FAQ
How much of the ABDE exam is Data Ingestion & Transformation?
DASCA weights this domain at 25%, the largest share of the eight domains. On a 75-question exam that suggests roughly 19 questions, though DASCA does not publish per-domain question counts.
Do I need hands-on experience with Talend and dbt?
DASCA names Talend Open Studio and dbt as tools for this domain, so you should know what each is for and where it fits. Questions focus on choosing the right approach, so understanding extraction versus in-warehouse transformation matters more than memorizing menus.
When is ELT better than ETL?
ELT fits when the target has scalable compute and you need to keep raw data so outputs can be rebuilt when business logic changes. ETL still fits when data must be masked, filtered or reduced before it is allowed to land.
Why does an incremental load miss deleted records?
A timestamp-filtered query only returns rows that still exist in the source. A hard-deleted row is gone, so it never appears; log-based CDC or soft-delete flags are needed to propagate deletions.
What kind of questions does this domain use?
Mostly short scenarios that state a source, a freshness requirement and a constraint, then ask for the BEST pattern or fix. A smaller number check tool fit or the meaning of terms such as idempotent or CDC.
How should I handle schema changes in a source?
Absorb additive, nullable column changes automatically, and treat renames, removals and type narrowing as breaking changes that need mapping, versioning or a coordinated release. Self-describing formats and a schema registry make compatibility checkable.
How does this domain connect to the rest of the exam?
Ingestion decides what lands and in what shape, which drives storage layout in Domain 2, scheduling and retries in Domain 3, and data quality and lineage checks in Domain 4.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 1 knowledge
You have worked through ETL versus ELT, incremental loads and CDC, cleansing, schema evolution and source connectivity. Check whether you can pick the right intake pattern under exam timing.