Overview
Data Acquisition and Preparation covers three steps that sit between a raw source and a trustworthy analysis. Objective 2.1 is getting the data: integrating sources, writing queries with joins, unions, filters, grouping, aggregates and nested queries, basic query optimization through indexing, parameterization, subsets and temporary tables, choosing ETL or ELT, and collecting data through surveys and sampling. Objective 2.2 is exploring it for missing values, duplication, redundancy, outliers, completeness and validation failures. Objective 2.3 is transforming and cleansing it with techniques from string manipulation and parsing to imputation, scaling, exploding and derived variables.
The domain exists because analysis is only as good as its input, and most real analyst time goes here. It draws on the data types and structures from Domain 1, and everything in Domain 3 and Domain 4 inherits whatever errors survive this stage.
How it's tested
Questions here are almost all 'given a scenario' items. You see a data sample, a query, a row count or a business requirement and choose the acquisition method, the inconsistency present or the cleansing technique that fixes it without damaging the data. Some items show output and ask what the query did. Think in rows: what is added, removed or duplicated.
-
Pick the query operation
Two tables or sources are described with a reporting need, and you choose the join type, union, grouping or nested query that produces exactly the required rows.
-
Name the data problem
A sample shows blanks, repeated customers, an extreme value or a malformed date, and you identify the inconsistency category such as missing values, duplication, outlier or failed validation.
-
Choose the fix
A specific defect is described and you choose the transformation, such as imputation, parsing, standardization, exploding or deletion, that corrects it with the least distortion.
A stem says a customer table has 5,000 rows and an orders table has 12,000; the analyst needs every customer, including those with no orders, with order totals. Options list inner join, left join from customers, union and cross join. Only a left join from customers keeps all 5,000 customers and returns nulls for those without orders; an inner join would drop the non-buyers the requirement explicitly includes.
Objectives 3
Official objectives from the CompTIA outline, in outline order.
2.1 Given a scenario, use data acquisition methods.Integration, querying, basic query optimization, ETL/ELT and data collection.
- What it means
- This covers combining data from several sources, querying with join, concatenate, filter, union, grouping, aggregate and nested queries, speeding queries through indexing, parameterization, subsets and temporary tables, choosing extract, transform, load (ETL) or extract, load, transform (ELT), and collecting primary data through surveys and sampling.
- What to understand
- Joins combine columns from related tables by key, while unions stack rows from queries with matching columns (UNION removes duplicates, UNION ALL keeps them), and WHERE filters rows before grouping whereas HAVING filters groups after aggregation. ETL transforms before loading, suiting strict target schemas; ELT loads raw data into a powerful target such as a cloud warehouse and transforms there. Sampling must be random or stratified to represent the population.
- How it shows up
- A requirement such as 'all customers even without orders', 'combine this year's and last year's extracts' or 'the report query takes ten minutes' points to a left join, a union or an optimization technique.
- Common confusion
- Using a join where a union is needed, or filtering an aggregate with WHERE instead of HAVING.
2.2 Given a scenario, perform data exploration to identify possible inconsistencies with a data set.Missing values, duplication, redundancy, outliers, completeness and validation.
- What it means
- Before analysis you profile the data to find missing values, duplicate records, redundant fields or stored copies, outliers, incomplete records or coverage gaps, and values that fail validation rules such as type, range or format checks.
- What to understand
- Duplication is the same record appearing more than once; redundancy is the same information stored in more than one place, such as a city field repeated in two tables or a column derivable from others. An outlier may be an error or a real extreme, so investigate before removing it. Completeness asks whether required fields and expected records are all present, for example every store reporting every day.
- How it shows up
- A sample or summary statistic reveals a problem, such as a count higher than the number of customers, a max age of 430 or blank postal codes, and you name it.
- Common confusion
- Treating every outlier as an error, or calling redundancy and duplication the same thing.
2.3 Given a scenario, perform appropriate data transformation and cleansing techniques.String manipulation through deletion: the cleansing toolkit.
- What it means
- The listed techniques are string manipulation, conversion, clustering, augmentation, exploding, scaling, standardization, imputation, parsing, merging, appending, derived variables and deletion, each fixing a different kind of defect or preparing data for analysis.
- What to understand
- Imputation fills missing values with a mean, median, mode or estimate, while deletion removes records when filling would mislead. Parsing splits one field into several columns, exploding turns a list in one cell into one row per item, and clustering groups near-identical values such as spelling variants so they can be standardized. Merging joins data sets side by side on a key, appending stacks rows with the same columns, and derived variables compute new fields such as age from birth date.
- How it shows up
- A specific defect or modeling need is described and you choose the single technique that addresses it, such as exploding a comma-separated tags column before counting tags.
- Common confusion
- Mixing up merging and appending, or using mean imputation on skewed data where the median would distort less.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Inner vs. left vs. full outer join
An inner join keeps only rows with matching keys in both tables; a left join keeps every row from the left table and fills unmatched right-side columns with nulls; a full outer join keeps all rows from both sides.
- Remember
- Phrases like 'all customers, including those without' mean a left (outer) join from that table.
- Tested as
- Join choice is the most common query scenario and can appear in performance-based items.
- Trap
- Defaulting to an inner join, which silently drops unmatched records the requirement wanted.
-
UNION vs. UNION ALL
Both stack the rows of two queries with the same number and compatible types of columns; UNION removes duplicate rows, while UNION ALL keeps every row and runs faster.
- Remember
- Join adds columns; union adds rows.
- Tested as
- Combining monthly or regional extracts that share a layout.
- Trap
- Using UNION when legitimate identical rows exist, such as two identical purchases, and losing real data.
-
WHERE vs. HAVING
WHERE filters individual rows before GROUP BY runs, while HAVING filters the groups after aggregate functions such as SUM or COUNT are computed.
- Remember
- Conditions on an aggregate, such as total sales over 10,000, go in HAVING.
- Tested as
- Reading or fixing a grouped query.
- Trap
- Placing an aggregate condition in WHERE, which errors or filters the wrong level.
-
Nested query (subquery)
A query inside another query supplies a value or set to the outer query, for example customers whose spend exceeds the average spend computed in a subquery.
- Remember
- Use it when a filter depends on a value you must calculate first.
- Tested as
- Scenarios comparing records to a computed benchmark.
- Trap
- Hard-coding a benchmark value that goes stale as the data changes.
-
Basic query optimization
Indexing speeds lookups on filtered or joined columns; parameterization reuses execution plans and blocks injection; subsets limit rows and columns to what is needed; temporary tables store intermediate results for reuse.
- Remember
- Select only needed columns and rows first, then index the join and filter keys.
- Tested as
- Slow-query scenarios ask which change helps most.
- Trap
- Adding indexes to every column, which slows inserts and updates without helping the query.
-
ETL vs. ELT
ETL transforms data in a staging layer before loading it into the target; ELT loads raw data first and transforms it inside a scalable target such as a cloud warehouse.
- Remember
- ELT preserves the raw data and leans on target compute; ETL fits strict schemas and sensitive fields that must be cleaned before they land.
- Tested as
- Pipeline design scenarios name the target platform and constraints.
- Trap
- Choosing ELT when regulated fields must be masked before they ever reach the target store.
-
Sampling
Sampling selects a subset of a population for collection or analysis; random sampling gives each member an equal chance, while stratified sampling draws from defined subgroups in proportion.
- Remember
- A convenience sample is cheap but biased.
- Tested as
- Data collection scenarios ask which method gives representative results.
- Trap
- Surveying only customers who contacted support and generalizing to all customers.
-
Duplication vs. redundancy
Duplication is the same record appearing more than once in a data set; redundancy is the same fact stored in several places or columns, which risks the copies disagreeing.
- Remember
- Duplicates inflate counts; redundancy causes conflicting values.
- Tested as
- Exploration items ask you to name the inconsistency.
- Trap
- Deduplicating rows when the actual problem is two columns holding conflicting versions of the same attribute.
-
Outlier
An outlier is a value far from the rest of the distribution, often flagged with the interquartile range rule (beyond 1.5 times the IQR from the quartiles) or a large z-score.
- Remember
- Investigate first: an outlier can be a data-entry error or a real, important event.
- Tested as
- Exploration scenarios show summary statistics with an implausible minimum or maximum.
- Trap
- Deleting every outlier automatically, which can remove the fraud or failure the analysis was meant to find.
-
Imputation
Imputation replaces missing values with an estimate such as the mean, median, mode, a fixed value or a model prediction, so records can stay in the analysis.
- Remember
- Median is safer than mean for skewed numeric data; mode suits categorical fields.
- Tested as
- Cleansing items describe the distribution and the gap and ask for the best fill.
- Trap
- Imputing when values are missing for a systematic reason, which hides a data collection problem.
-
Parsing vs. exploding
Parsing splits one field into several columns, such as a full name into first and last; exploding turns a list in one cell into multiple rows, one per element.
- Remember
- Parsing widens the table; exploding lengthens it.
- Tested as
- Transformation scenarios with delimited strings or JSON arrays.
- Trap
- Parsing a variable-length tag list into columns, which creates sparse, uncountable fields.
-
Merging vs. appending
Merging combines two data sets side by side using a shared key; appending stacks data sets with the same columns on top of each other.
- Remember
- Merging is the join idea; appending is the union idea.
- Tested as
- Cleansing objective items name the goal and ask for the technique.
- Trap
- Appending two tables with different column orders or definitions and creating misaligned data.
-
Scaling vs. standardization
Scaling rescales numeric values to a common range, such as 0 to 1 with min-max scaling; standardization either converts values to z-scores (mean 0, standard deviation 1) or makes formats consistent, such as one date format or state code style.
- Remember
- Read the stem to see whether it is about numeric ranges or consistent formats.
- Tested as
- Preparing features measured in different units, or cleaning inconsistent entries.
- Trap
- Treating standardization only as a statistics term and missing the format-consistency meaning.
-
Derived variable
A derived variable is a new field calculated from existing fields, such as order margin from price minus cost, or tenure in months from a start date.
- Remember
- Derive rather than store when the value can be recomputed, to avoid redundancy.
- Tested as
- Scenarios where the needed metric is not in the source.
- Trap
- Hard-coding a derived value that goes stale when its inputs change.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Join vs. union vs. merge vs. append
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Join (SQL) | Combines columns from tables that share a key | Row count depends on join type and key matches | Duplicate keys on one side multiply rows |
| Union (SQL) | Stacks rows from queries with compatible columns | UNION removes duplicates; UNION ALL keeps them | Losing real identical rows with UNION |
| Merging (cleansing) | Combines data sets side by side on a key | The tool-level equivalent of a join | Merging on a non-unique key and inflating totals |
| Appending (cleansing) | Adds rows from a data set with the same structure | The tool-level equivalent of a union | Appending sets whose columns do not line up |
ETL vs. ELT
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| ETL | Extract, transform in staging, then load | Only cleaned, conformed data reaches the target | Assuming it keeps raw history in the target; it usually does not |
| ELT | Extract, load raw, transform inside the target | Uses scalable target compute and keeps raw data for reprocessing | Loading sensitive raw fields that policy says must be masked first |
Imputation vs. deletion for missing values
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Mean imputation | Fill gaps with the average | Fine for roughly symmetric numeric data | Pulled by outliers in skewed data |
| Median imputation | Fill gaps with the middle value | Robust to skew and extreme values | Still shrinks variance if many values are missing |
| Mode imputation | Fill gaps with the most frequent value | Works for categorical fields | Over-represents the most common category |
| Deletion | Remove incomplete records or fields | Avoids invented values | Biases results if missingness is not random |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Inner join by default
- What candidates pick
- An inner join between customers and orders.
- Why it looks right
- It is the most common join and returns clean, fully populated rows.
- Why it's wrong
- Customers with no orders disappear, which violates a requirement to report every customer.
Clue to watch for Words like 'all', 'including those without' or 'even if no activity'.
-
Totals inflated after a join
- What candidates pick
- Accept the summed revenue after joining orders to a promotions table.
- Why it looks right
- The query ran without errors and the join key looks right.
- Why it's wrong
- If the promotions table has several rows per order, each order repeats and revenue is multiplied; aggregate or deduplicate first.
Clue to watch for Row count after the join exceeds the order count, or totals jump unexpectedly.
-
UNION drops real records
- What candidates pick
- UNION to combine two months of transaction extracts.
- Why it looks right
- It sounds safer because it removes duplicates.
- Why it's wrong
- Two genuine identical transactions collapse into one; UNION ALL keeps every row when the sets do not overlap.
Clue to watch for Non-overlapping periods or sources, and a need for exact row counts.
-
Mean imputation on skewed data
- What candidates pick
- Fill missing household incomes with the mean.
- Why it looks right
- The mean is the default measure of center.
- Why it's wrong
- A few very high incomes pull the mean up, so the filled values overstate typical income; the median is more representative.
Clue to watch for The stem mentions skew, a long tail or extreme values.
-
Removing every outlier
- What candidates pick
- Delete all transactions beyond three standard deviations.
- Why it looks right
- It makes charts and averages look tidy.
- Why it's wrong
- If the analysis is about fraud, failures or high-value customers, the outliers are the signal; investigate and only remove confirmed errors.
Clue to watch for The business goal involves rare or extreme events.
-
Parsing a list into columns
- What candidates pick
- Split a semicolon-separated product-tags field into tag1, tag2, tag3 columns.
- Why it looks right
- Splitting by delimiter is a familiar step.
- Why it's wrong
- The number of tags varies, leaving sparse columns that are hard to count; exploding into one row per tag supports grouping and counting.
Clue to watch for The goal is to count or filter by individual list items.
-
ELT with unmasked sensitive data
- What candidates pick
- Load raw records into the cloud warehouse and mask later.
- Why it looks right
- ELT is the modern pattern for cloud platforms.
- Why it's wrong
- Policy that forbids storing raw identifiers in the target means the data must be transformed or masked before loading, which is ETL.
Clue to watch for A requirement that sensitive fields never land unmasked in the destination.
Answer framework
Use this sequence for any Domain 2 scenario about getting, checking or cleaning data.
- Restate the required output in rows and columns: which records must be present, at what grain, with which fields.
- Choose the acquisition operation that produces exactly those rows: join type for added columns, union for added rows, grouping for summaries, subquery for computed filters.
- Profile the result for row-count changes, missing values, duplicates, redundant fields, outliers and failed validation rules.
- Pick the least distorting fix for each defect, such as imputation, parsing, exploding, standardization or deletion, and justify it from the data's distribution or structure.
- Recheck counts and totals against the source before handing the data to analysis, and record what you changed.
Practice question
Want more like this? The CompTIA Data+ practice test has questions for every domain.
Checklist
0 of 14
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Begin with querying in objective 2.1, because join and grouping logic shows up again in troubleshooting and report validation. Use a free database such as SQLite or PostgreSQL with two small tables, and run every join type, UNION and UNION ALL, and a grouped query with HAVING. Write down the row count before and after each step until you can predict it.
Next, take a messy public data set and profile it for objective 2.2: count nulls per column, count distinct keys versus rows, sort to find extremes, and test format rules. Name each problem using the outline's terms.
Then work through objective 2.3 as a list of thirteen techniques, giving each one a before-and-after example in pandas, R or Power Query. Candidates usually underestimate exploding, augmentation and clustering because they appear less often in daily spreadsheet work, and they mix up scaling with standardization.
Finally, practice scenario questions under time pressure and expect performance-based items that show a table or query. After each miss, ask what happened to the rows. Then move to Domain 3, which assumes your data is now clean.
FAQ
How much is Data Acquisition and Preparation worth on DA0-002?
CompTIA weights it at 22%, the second-largest domain after Data Analysis. Per-domain question counts are not published.
How much SQL do I need for Data+ V2?
You should read and reason about joins, unions, filters, grouping, aggregates and nested queries, and know basic optimization ideas such as indexing and temporary tables. The objectives do not call for advanced database administration.
Are there performance-based questions in this domain?
CompTIA states the exam includes multiple-choice and performance-based questions but does not say which domains they cover. The 'given a scenario' wording of all three objectives makes hands-on practice with data samples and queries worthwhile.
What is the difference between ETL and ELT?
ETL transforms data before loading it into the target; ELT loads raw data first and transforms it inside the target platform. Constraints such as masking rules or target compute decide which fits.
Should I always remove outliers?
No. Investigate whether an outlier is an error or a real event, and keep real extremes when the analysis is about them, such as fraud or equipment failures.
When is median imputation better than mean imputation?
When the numeric field is skewed or has extreme values, because the median is not pulled toward the tail. For categorical fields, the mode is the usual simple choice.
How does this domain relate to Data Governance?
Domain 2 finds and fixes quality problems in a specific data set, while Domain 5 sets up the ongoing practices, such as profiling, health checks and automated monitoring, that catch them systematically.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 2 knowledge
You have just covered joins, unions, exploration and cleansing techniques. Practice choosing the right one when the scenario shows a data sample rather than a definition.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the CompTIA Data+ AI tutor Get any concept explained your way