CompTIA Data+ DA0-002 · Free study guide

Domain 2 — Data Acquisition and Preparation (22%)

Domain 2 covers where data comes from, the SQL that retrieves it, the pipelines that move it, exploration of its condition, and transformations that make it usable. In practice, analysts often spend substantial time acquiring and preparing data before analysis begins.

Acquiring data

Data arrives from databases, flat files, APIs, web pages, surveys, and machine telemetry, and each source has characteristic failure modes an analyst must recognize.

APIs may paginate responses and enforce rate limits. When the contract does, a reliable extraction follows the documented cursor or page links until the terminal condition and respects throttling and retry guidance. Authentication, schema, versioning, and error handling remain separate requirements.

Web scraping has a governance edge: public visibility is not permission. Before scraping, follow the organization's approval process and review the site's terms, technical controls, privacy obligations, and applicable law. A technically reliable scraper is not automatically authorized.

Flat files fail in two classic ways. Delimiter collision: an unquoted comma inside "Smith, Jones and Partners" splits one field into two — fixed by proper quoting or a different delimiter. Encoding mismatch: accented characters rendering as "é" is the fingerprint of UTF-8 bytes read as a single-byte encoding — fixed by declaring the correct encoding on import. Learn both fingerprints and connect each symptom to its likely cause.

SQL: the retrieval layer

Build reading fluency in SELECT-level SQL. The core patterns are:

Joins. INNER JOIN keeps only matched rows. LEFT OUTER JOIN keeps every row from the left table, filling the right with NULLs when no match exists — the answer whenever the requirement says "including customers who have never ordered." RIGHT is the mirror; FULL keeps both sides; CROSS produces every combination; a self join relates a table to itself.

WHERE versus HAVING. WHERE filters rows before aggregation and cannot see aggregates. HAVING filters groups after GROUP BY and is where "only stores with total sales above $1M" belongs. It is a foundational SQL distinction.

GROUP BY discipline. "Orders per customer" is SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id. Any option mixing an aggregate with a non-grouped column, or putting an aggregate in WHERE, is wrong by construction.

Set operators. UNION combines compatible results and removes duplicates; UNION ALL keeps them; INTERSECT returns rows in both; EXCEPT returns rows in the first but not the second. "Combined list, duplicates removed" is UNION.

The rest of the toolkit. DISTINCT for unique values; ORDER BY with a row limit for top-N ("ten highest-value invoices" = sort descending, limit 10); subqueries where a value depends on another query; aggregate functions SUM, AVG, COUNT, MIN, MAX.

Integration: ETL, ELT, and load strategy

ETL transforms data before it enters the analytical destination; the work may run in several kinds of integration or staging process. ELT loads source-shaped data into an authorized destination and transforms it there. Where transformation occurs defines the sequence; neither acronym guarantees raw retention, performance, security, or quality.

Load strategy balances simplicity, correctness, recovery, and cost. A full load reads the complete selected source and rebuilds or replaces the target scope; it need not use a literal truncate. An incremental (delta) load moves rows added or changed since a checkpoint, identified by stable keys, timestamps, version columns, logs, or change data capture. A very large source with a tiny change rate strongly favors an incremental design, provided deletes, late arrivals, ties, and retries are handled safely.

Know the two ways tables combine: appending (union/stacking) for files with identical columns representing different periods, versus joining (merging) to bring in attributes by a shared key. January-plus-February sales is an append; adding customer region by customer ID is a join.

Profiling before cleaning

Data profiling is the reconnaissance step: distributions, null counts, distinct counts, min/max ranges, and patterns. It reveals characteristics and possible defects before a cleaning decision is made.

Cleaning: the judgment calls

Duplicates. Exact-match deduplication catches identical rows. A harder case is "Jon Smith, 123 Oak St" versus "John Smith, 123 Oak Street." That is fuzzy matching — similarity scoring via edit distance, phonetic codes, or token overlap — which surfaces probable matches for review.

Missing values. Options: delete rows (defensible when few and random), impute a constant, impute a statistic, or model the missing value. When a statistical imputation is justified for a skewed numeric field, the median is less distorted by the tail than the mean. That does not make median imputation automatic: first understand why the value is missing and how the replacement will affect the analysis. A missing salary is not zero; never fabricate a semantic value.

Outliers. Investigate before you act. A flagged value may be a source error, a valid extreme, a different population, a unit mismatch, or an artifact of the screening method. Preserve evidence and choose correction, exclusion, segmentation, or retention from the cause and analytical purpose.

Validation rules stop bad data at the door: reject negative quantities, future order dates, out-of-range ages. Range and plausibility checks at load time are validation — distinct from compression, indexing, or archiving, which manage storage and speed, not correctness.

Transformation and enrichment

Recurring transformations to recognize on sight:

• Type conversion — locale-formatted text such as 07/31/2026 should be parsed with a documented format into a real date before date arithmetic or grouping. Some normalized ISO text sorts chronologically, but a typed date still makes semantics and validation explicit.
• Derived fields — age computed from date of birth; margin from revenue and cost. Calculated during preparation, not stored by the source.
• Binning — collapsing a continuous variable into ranges ("Under 25K", "25K–75K", "Over 75K").
• Scaling — min-max normalization rescales variables to a common 0–1 range so that a distance-based calculation is not dominated by whichever column has the biggest units. Income versus age is the classic pairing.

What to drill

Write SQL against a scratch database until joins, filters, grouping, aggregates, set operators, subqueries, temporary tables, indexes, and parameters are familiar. Then practice source selection, surveys and sampling, ETL/ELT and load recovery, profiling, missingness, duplicates, outliers, business rules, string and numeric transformations, reshaping, augmentation, imputation, deletion, and derived fields. Justify every cleaning action from evidence.