Extract, Transform, and Load (ETL) is technique 3.10 of the IIBA Guide to Business Data Analytics: a way to source and curate data from many systems into one target repository that serves as a "single source of truth" [1]. For the CBDA exam it belongs to Source Data, 15% of the questions [2]. The guide does not teach SQL. It describes what the analyst checks at each of the three steps, and that is what I would learn.
ETL at a glance
ETL is the plumbing between the systems that run the business and the place where analysis happens. The facts:
| Item | Value |
|---|---|
| Guide section | 3.10, chapter 3 (Techniques) [1] |
| Purpose | Make data from a variety of sources available in a target repository, a "single standardized source of data" [1] |
| Listed in | Section 2.2.5, Select Techniques for Source Data, as "ETL and Data Management Techniques" (no BABOK reference) (Guide, 2.2.5) |
| Elements | Extract Data, Transform Data, Load Data [1] |
| Analyst's role | Suggest sources, verify or prescribe transformations, check that business rules are not contradicted, oversee ELT loads [1] |
| Exam | 75 multiple-choice, scenario-based questions in 2 hours; Source Data is 15% [2] |
Last verified: November 2026 (exam facts on iiba.org; guide text checked section by section).
What the guide says ETL is
The guide defines ETL as "a data sourcing and curation approach" for application development, business intelligence and analytics [1]. The generic definition is shorter: a three-phase process where data is extracted, transformed (including cleaning) and loaded into an output container [3].
The guide puts ETL on three core principles [1]:
- Identify high-quality data from a variety of sources: data warehouses, data marts, data lakes, transactional databases, external websites, mobile data.
- Move it to a target repository that creates a "single source of truth", applying data governance guidelines and verifying business and data rules.
- Give easy access to this repository for analytics work.
The guide then answers a common objection. Big data and streaming data often follow different architectures, which "can create a perception that ETL mechanics are irrelevant" [1]. The guide disagrees: the governing principles of ETL "remain the same regardless of the tools used" [1]. With big data the order of transform and load may change, Lambda and Kappa architectures exist for unstructured and distributed data, but the finer points are "outside the scope of this guide" [1].
One more detail matters for the exam. Most analytics solutions pass through a proof of concept stage, where traditional ETL is more relevant, and that is where analysts usually conceptualize and interpret the solution [1]. I read this as a hint: the CBDA candidate should think at the proof of concept level, not at the level of a streaming platform.
Where ETL is used in the CBDA domains
ETL sits in Source Data. Section 3.10 has no domain-by-domain subsection, so the links come from chapter 2 and from neighbouring techniques:
| Domain, task or technique | What the guide says |
|---|---|
| 2.2, Source Data | The tasks need people with "strong technical skills" to extract data, and they "require the most amount of effort" (Guide, 2.2) |
| 2.2.3, Collect Data | Analysts understand where data comes from, what transformations are performed and where it is stored: data lineage. Numeric and alpha gender codes from two sources must be reconciled (Guide, 2.2.3) |
| 2.2.5, Select Techniques for Source Data | ETL is used "to extract and curate required data without compromising or changing data" needed for ongoing operations (Guide, 2.2.5) |
| 2.3.2, Prepare Data | Joins, normalizing, standardization, scaling and converting data; the analyst adds business context to statistical choices (Guide, 2.3.2) |
| 3.13, Interface Analysis | Findings documented through data mapping are "leveraged during extract, load, and then transform (ELT) activities" (Guide, 3.13) |
For the whole domain, see Source Data, Domain 2. The 2.2.5 row is the one I would remember: ETL takes data out without breaking the systems the business runs on.
The three elements of ETL
The guide describes each element as a set of steps or tasks where the analyst has a clear job [1].
Extract Data
Extraction is the first stage: identify and aggregate data across multiple sources [1]. Three steps:
| Step | What the guide says [1] |
|---|---|
| 1. Identify data sources and types | Analysts may suggest sources based on the business problem. Classification goes top-down, from coarse to fine granularity |
| 2. Create a universal classification of data | Sources have different conventions and data dictionaries; definitions, descriptions and formats are reconciled into one scheme |
| 3. Verify data integrity | ETL tools check sizes, formats, redundancy and transmission losses through a universal schema; analysts can check minimum and maximum values, identifiers, valid and expected values, or use sampling |
The guide's example for step 1 is a marketing campaign: CRM, sales and channel data, drilled down to customer data, engagement, in-store and online sales, marketing automation, loyalty programs and web analytics [1].
Transform Data
Transformation is "the translation of extracted data to a usable and accurate format" that follows sound business logic [1]. Analysts may verify or prescribe ten common types [1]:
| Transformation | Guide example [1] |
|---|---|
| Reconcile encoded values | Date formats, lists of values |
| Derive calculated values | Sales = price × units |
| Standardize values | Rescaling |
| Drop redundant attributes | — |
| Mask attributes | Social security or credit card numbers |
| Translate text to word vectors | For NLP applications |
| Bin data | Age to age groups |
| Split strings | USIL12@# to Country, State, ID |
| Impute missing data | — |
| Join, merge, pivot, roll up | — |
The key sentence for the analyst: analysts "play an important role" in verifying that relevant business rules are not contradicted while data is transformed [1].
Load Data
Loading moves transformed data to a target repository: a database, a data warehouse or a data lake [1]. The guide contrasts two load types [1]:
- Incremental load checks the delta and adds only changed data. It allows faster generation of BI reports.
- Full extraction may mean complete replacement of the data on the target. The guide links it to better predictive and prescriptive analytics.
Typical load tasks: review the target format and load type against the business need, load from the staging area (a "logical placeholder" for easy transformations), generate audit trails, and standardize the workflow so it can repeat. The guide's example is a quarterly sales report that is refreshed each quarter once the metrics and transformation rules are final [1].
ETL vs ELT in the guide
ELT is the same three steps in a different order: extract, load, then transform. The guide says many big data technologies use ELT so that transformation can happen later, on data stored in a distributed file system, and this lets teams "experiment quickly" [1]. The analyst's role does not disappear. Analysts "may provide oversight" in checking whether data loaded this way is reliable for business use [1].
| ETL | ELT | |
|---|---|---|
| Order | Extract, transform, load [1] | Extract, load, transform [1] |
| Typical context in the guide | Traditional sources, SQL, proof of concept [1] | Big data, distributed storage [1] |
| Where business rules are checked | Before loading, in the transform step [1] | Transformation happens after loading; analysts may oversee whether loaded data is reliable [1] |
A worked example: one campaign, three source groups
This example is mine and the numbers are invented. A retailer asks whether a spring email campaign lifted sales. The analytics team needs one data set from the three source groups the guide lists for a campaign: CRM, sales and channel data [1].
- Identify sources. CRM (customers), sales (online orders and in-store sales), channel data (the email platform). Start coarse, then drill into the fields the research question needs.
- Universal classification. The online shop stores dates as 2026-04-03, the stores as 03/04/2026. The CRM uses a customer ID, the stores use a loyalty card number. Write one scheme for all of them. This is where a data mapping pays off.
- Verify integrity. Row counts match: 48,210 online orders in the source and in the extract. The minimum price check finds 312 orders at $0.00 (0.65%). These are free samples sent by the campaign. A business rule decides: exclude them from revenue, keep them for reach.
- Transform. Derive sales = price × units (2 × $24.50 = $49.00). Mask email addresses before they reach the analytics area. Bin age into groups. Join orders to the CRM: 1,930 online orders (4.0%) have no match, because they are guest checkouts. Keep them in a separate segment instead of dropping them with an inner join.
- Check business rules. The first transformation spec sums gross sales. The finance rule says returns reduce revenue. The analyst flags it before the dashboard goes live.
- Load. Daily incremental loads feed the campaign dashboard. At the end of the campaign, a full reload builds the data set for the lift model. Every replaced row goes to an audit trail.
Nothing in this example needed code from the analyst. Every decision needed business knowledge: what a free sample is, who a guest buyer is, how finance counts revenue.
Strengths and limitations
The guide lists four strengths and three limitations [1]:
| Strengths [1] | Limitations [1] |
|---|---|
| Well-established process steps, "primarily for descriptive analytics problems" | Heterogeneous and streaming data are hard for traditional ETL tools |
| Graphical tools and connections to enterprise sources make sourcing and self-service analytics easy | High-volume data movement needs a lot of planning to keep data consistent |
| Easy maintenance through automatic traces to sources and audit trails | Not suitable for "near real-time interventions through algorithmic decision-making" |
| A robust pre-analysis that summarizes the nature of the data before modelling |
My take on the last limitation: it is the most testable line in the whole section. A fraud score that must react in milliseconds is not an ETL problem.
What practitioners and data say about ETL
The data says pipelines take a big share of the work and break more often than people think. A 2005 industry blog post quoting Ralph Kimball notes that ETL "is often estimated to consume 70 percent" of the time and effort of building a data warehouse [4]. Recent surveys point the same way:
| Survey | Sample | Finding |
|---|---|---|
| Data observability vendor survey, 2023 [5] | 200 data professionals | Incidents rose from 59 to 67 per month; 15 hours to resolve one on average; 74% say business stakeholders find issues first all or most of the time |
| Analytics engineering vendor survey, 2025 [6] | 459 practitioners | Poor data quality is the top challenge (over 56%); 57% spend most of their workday maintaining or organizing data sets |
| Data integration vendor benchmark, 2026 [7] | 500 senior data leaders | 53% of engineering time goes to pipeline maintenance; 4.7 pipeline breaks per month on average |
| Data science platform survey, 2020 [8] | About 2,400 people | About 45% of time on data preparation, including 19% on data loading |
All four come from tool vendors, so I read them as direction, not as exact numbers.
Practitioners describe failures that the guide's integrity checks are built to catch:
- Deleted, but still active. An associate solutions architect describes a customer deleted from the CRM for privacy reasons who stayed active in a sales dashboard for weeks. In the same post, an incremental load skipped late records, and a filter on "test" dropped a customer named "contest_winner" [9].
- Green pipeline, wrong data. An engineer with five years on a cloud ETL service lists IDs that suddenly arrived with decimals and broke joins, and invoice dates in 1900 [10].
- The checkmark lies. A data engineer with 15 years of projects lists a skipped day of data, duplicates and an inner join that silently dropped rows: "A green checkmark means nothing if the data is wrong." [11]
- Silent failures. A data engineering consultant and newsletter author names five: schema drift, partial loads, stale data, late-arriving dimensions and hard-coded logic that goes stale [12].
- The BA's map. A business analysis trainer describes a data map that shows how each source field maps to the target, with translation rules and clean-up needs, which the BA starts from the business side and developers extend [13].
- Clarity over code. A data modeler argues that mappings should live as readable metadata, because "Business leaders need clarity — not code." [14]
From the exam side, a data analyst with more than ten years of experience warns about reading options carefully, because many questions "had two confusing answers" [22]. ETL questions look like that: two plausible steps, one right owner.
Experts disagree on ETL
Practitioners agree that data must be moved and cleaned, but not on where the transformation should happen or who should own it.
1. ETL is dead, transform later. A data platform evangelist wrote in 2017 that "ETL is hard. And it's limiting." and argued for transforming data in the warehouse after loading, with just-in-time transformation as the long-term ideal [15]. Another author answered the same year: "ETL is not dead." [16] The guide stays in the middle: ELT is a valid order for big data, and the principles do not change [1].
2. Zero-ETL removes the work. A cloud provider's CEO presented zero-ETL integrations in 2022 as a fix for "one of the greatest ETL pain points" [17]. An ETL tool vendor objects that zero-ETL covers only "the extract and the load" [18]. Both sides sell something, so I take this debate with salt. The guide's answer is the transform step: business rules still have to be checked somewhere [1].
3. Who writes the transformations. A data platform leader at a retail company argued in 2016: "Engineers should not write ETL." Data scientists should own their pipelines end to end [19]. An engineer at a cloud data company replied that engineers should write ELT and be proud of it [20]. A third group created a new role, the analytics engineer, who spends time "transforming, testing, deploying, and documenting data" [21].
My view is close to the guide. The analyst does not have to own the code. The analyst has to own the rules: what each field means, what a valid value is, and which business rule a transformation must not break.
How ETL differs from similar techniques
ETL is easy to confuse with its neighbours in the Source Data list:
| Compared with | Difference in the guide |
|---|---|
| Data Mapping (3.6) | Mapping documents traceability between data elements and sources, including transformations and extraction methods; ETL executes the movement (Guide, 2.2.5) |
| Data Dictionary (3.4) | A dictionary of terminology for data labels; ETL uses it to build a universal classification (Guide, 2.2.5; [1]) |
| Data Flow Diagrams (3.5) | A conceptual or logical view of data collected and stored in sources (Guide, 2.2.5) |
| Interface Analysis (3.13) | Shows how data is captured and stored; its mapping feeds ELT work (Guide, 2.2.5; Guide, 3.13) |
| Exploratory Data Analysis (3.11) | Explores the data set to find patterns and quality issues before formal analysis (Guide, 3.11) |
| Prepare Data (2.3.2) | A task in Analyze Data that links, standardizes and cleans data for the model (Guide, 2.3.2) |
The neighbour I would review next is Exploratory Data Analysis, because EDA is where loading mistakes usually surface.
Exam traps for ETL
| Scenario | Tempting answer | Guide reading |
|---|---|---|
| Which domain lists ETL? | Analyze Data | Source Data, section 2.2.5 (Guide, 2.2.5) |
| Sourcing for analytics must not disturb operations | Copy and fix data in the source system | Extract and curate "without compromising or changing data" needed for operations (Guide, 2.2.5) |
| Two sources code the same field differently | Fix it in the report | Create a universal classification during extraction [1] |
| Credit card numbers in the extract | Load them, restrict access later | Mask them during transformation [1] |
| Quarterly BI report must refresh fast | Full reload every time | Incremental load gives faster BI reports [1] |
| Big data project | ETL principles no longer apply | Principles remain the same; the order may become ELT [1] |
| Near real-time algorithmic decision | Build an ETL job | ETL is not suitable for near real-time algorithmic interventions [1] |
| Who checks business rules during transformation? | Only the ETL developer | Analysts play an important role in verifying them [1] |
For more scenario practice, use the CBDA Source Data questions.
Check yourself: CBDA practice questions
1. Two source systems store the same customer attribute with different codes and formats. During extraction, what should the analyst help create? A) A business visualization. B) A universal classification of data. C) A full load of the target. D) A decision table.
Answer: B. Step 2 of extraction reconciles definitions, descriptions and formats into a uniform scheme [1].
2. A team must refresh a weekly sales dashboard as fast as possible. The warehouse supports loading only the changed data. Which statement matches the guide? A) Full extraction is always faster. B) Incremental load allows faster generation of BI reports. C) Incremental load is better for prescriptive models. D) The load type does not matter.
Answer: B. The guide links incremental load to faster BI reports, and full extraction to better predictive and prescriptive tasks [1].
3. The business wants to block suspicious card payments within seconds, using an algorithm. What does the guide say about ETL here? A) ETL is the best fit. B) ETL is not suitable for near real-time algorithmic interventions. C) Use ETL with a full load. D) Use a staging area only.
Answer: B. This is one of the three limitations in 3.10.4 [1].
Conclusion: the gap between the guide's ETL and real pipelines
The guide's three steps match practice, but real pipelines fail more quietly than the guide suggests, and surveys put numbers on the effort the guide only names.
| Area | Guide | Reality | Delta |
|---|---|---|---|
| Integrity checks | Min, max, identifiers, expected values, sampling [1] | Pipelines report success while data is wrong [10, 11, 12] | Under-used |
| Effort | Source Data tasks "require the most amount of effort"; high-volume ETL needs a lot of planning and effort (Guide, 2.2; [1]) | 57% spend most of the workday maintaining data sets; pipelines break several times a month [6, 7] | No gap |
| Order of steps | ETL, with ELT for big data [1] | Transform after load is widely argued for [15, 21] | No gap |
| Who owns transformations | Analysts verify or prescribe [1] | Analytics engineers or data scientists own the code [19, 21] | Added by employers |
| Business rules | Must not be contradicted [1] | BA-written data maps with translation rules [13] | No gap |
So, for the exam, learn the three elements and who does what. For real work, add one habit: never trust a green pipeline until a person who knows the business has looked at the numbers.
Related topics
- CBDA exam simulator with full mock exams.
FAQ
What is ETL in the CBDA guide?
ETL is technique 3.10 of the IIBA Guide to Business Data Analytics. It is a data sourcing and curation approach that extracts data from many sources, transforms it into a usable and accurate format, and loads it into a target repository that acts as a single source of truth for analysis.
What does a business analyst do in ETL?
The analyst suggests data sources based on the business problem, helps reconcile definitions across sources, verifies or prescribes transformations such as derived values or masking, and checks that business rules are not contradicted. For ELT, the analyst oversees whether loaded data is reliable for business use.
Which CBDA domain covers ETL?
The guide lists ETL and data management techniques in section 2.2.5, Select Techniques for Source Data. Source Data is Domain 2 and makes up 15% of the CBDA exam.
What is the difference between ETL and ELT?
ETL transforms data before loading it into the target. ELT loads raw data first, usually into distributed storage, and transforms it later. The guide says many big data technologies use ELT, while the governing principles of ETL stay the same.
When is ETL not a good fit?
The guide names three limitations: heterogeneous and streaming data are hard for traditional ETL tools, high-volume movement needs a lot of planning, and ETL is not suitable for near real-time algorithmic decision-making.
Sources
- IIBA: Guide to Business Data Analytics, 3.10 Extract, Transform, and Load (ETL)
- IIBA: Certification in Business Data Analytics (CBDA)
- Wikipedia: Extract, transform, load
- Rittman Mead: Ralph Kimball on the 38 subsystems of ETL (2005)
- BusinessWire: Data downtime nearly doubled year over year, survey (2023)
- dbt Labs: State of Analytics Engineering 2025
- Fivetran: Enterprise Data Infrastructure Benchmark Report 2026
- BigDATAwire: Data prep still dominates data scientists' time (2020)
- Behind the scenes of data ingestion: how small issues cause big headaches (2025)
- Why your ADF pipeline shows success but your data is wrong (2026)
- My 10 biggest fails as a data engineer (2025)
- The 5 silent failures in data pipelines (2026)
- Bridging the Gap: Data migration projects and the business analyst
- Roadmaps, not pipelines: business-friendly mapping from sources to targets (2025)
- InfoWorld: ETL is dead (2017)
- CIO: Is ETL dead in the age of AI? Not quite (2017)
- The Stack: AWS touts a zero-ETL future (2022)
- Matillion: Mythbuster, would life be better without ETL? (2025)
- Stitch Fix: Engineers shouldn't write ETL (2016)
- Engineers should write ELT (2021)
- dbt Labs: What is analytics engineering? (2019)
- Techcanvass: CBDA certification journey of Priya Telang (2024)