CompTIA Data+ DA0-002 · Free study guide

Objective 2.1 — Use data acquisition methods

Acquisition turns a business requirement into a complete, repeatable flow of data. It includes more than connecting to a source. The analyst must collect the right population, combine records at the right grain, limit stress on operational systems, and preserve enough evidence to rerun or audit the result.

Query and integration operations

A join combines columns from related data sets using a key. An inner join keeps matched records only. A left join keeps every record from the left side and supplies nulls where the right side has no match. If a report must include customers with no orders, customers must be on the preserved side of an outer join.

Concatenation combines values or collections in sequence. String concatenation might build a display name from separate fields. Row concatenation, often called appending, stacks compatible monthly extracts whose columns have compatible meanings.

A filter limits rows according to a condition. In SQL, WHERE filters individual rows before aggregation. Grouping collects rows that share values, and an aggregate such as COUNT, SUM, AVG, MIN, or MAX produces a result for each group. HAVING filters groups after aggregation. “Orders above $100 per region, keeping regions with more than 50 such orders” uses WHERE for the amount and HAVING for the grouped count.

Union combines compatible query results vertically. UNION removes duplicate result rows, while UNION ALL preserves them. Removing duplicates has a cost and may erase legitimate repeated events, so choose it only when the requirement calls for set semantics.

A nested query, or subquery, places one query inside another. It can calculate a comparison value, test whether a related record exists, or define a filtered input. A common table expression named with WITH can make the same logic easier to read within one statement.

Temporary tables and useful subsets

A temporary table holds intermediate results for a session or limited scope. It is useful when later statements reuse an expensive result, when an analyst needs to inspect a stage, or when indexing the intermediate data improves the remaining work.

A subset contains only the rows and columns needed for the task. Selecting a date range, relevant regions, and required fields near the source reduces transfer and processing. A useful subset must still preserve the required population. A random sample is not a valid shortcut when the output must contain every transaction.

Basic query optimization

An index creates a structure that helps a database locate or order records without scanning every row. It is most useful for selective filters, joins, and common sort patterns. Indexes consume storage and add maintenance to inserts and updates. An index on a field with only two values may not help a query that returns half the table.

Parameterization supplies values separately from query text. A parameterized query avoids unsafe string construction, supports controlled reuse, and can help the database reuse execution plans. A parameter is a value placeholder, not a way to substitute arbitrary table names or SQL fragments.

ETL and ELT

Extract, transform, load, or ETL, extracts source data, transforms it in a processing layer, and then loads the prepared result. ETL fits when sensitive fields must be removed before the destination, when the target has limited transformation capacity, or when strong validation must gate what enters it.

Extract, load, transform, or ELT, lands source data first and transforms it inside the destination platform. ELT fits scalable analytical platforms that can retain a controlled raw layer and use their own compute for transformation. It supports reprocessing when business logic changes.

The distinction is the location and order of transformation, not whether transformation exists. Neither pattern removes the need for validation, access control, lineage, or testing.

Incremental acquisition collects new or changed records after a stored checkpoint, such as a modification timestamp or change sequence. A full acquisition reloads the complete source. A trustworthy incremental process must handle late-arriving changes, deleted records, time-zone boundaries, and reruns without duplication.

Surveys and sampling

When the organization creates data through a survey, question wording, response options, delivery channel, and target population shape the result. A large response count cannot repair a sample that excludes quiet customers or attracts only people with strong opinions.

Simple random sampling gives every population member an equal chance of selection; for a fixed sample size, each possible sample is equally likely. Stratified sampling divides the population into meaningful groups and samples within each, which helps ensure that small but important groups are represented. Convenience and voluntary-response samples are easier to collect but often biased.

Sampling reduces cost and time when measuring a population. It is not appropriate when a complete transaction ledger is required. Distinguish collecting representative evidence from extracting every operational record.

APIs, files, and safe extraction

An API collector must handle authentication, pagination, rate limits, retries, and response changes. It should record checkpoints and avoid treating a partial successful response as a complete data set. Retries must not create duplicate target records.

File acquisition requires checks for expected filename, size, row count, delimiter, quoting, encoding, schema, and delivery completion. A comma inside a quoted company name is data, not a separator. Corrupted text such as é often signals a character-encoding mismatch.

Direct database extraction should use least-privilege, read-only credentials and, when available, a read replica or controlled low-traffic window. A consistent snapshot prevents a long extract from mixing states while source transactions continue.

Scenario: building a regional service report

A team must report every customer, total completed service value this quarter, and satisfaction by region. Orders live in a production database, customer attributes arrive through a paginated API, and satisfaction comes from a survey. The quarterly order table contains hundreds of millions of rows.

The acquisition selects the required period and columns, filters completed orders, groups by customer, and aggregates value. An index supporting the date, status, or customer access pattern may reduce scanning, but the plan must confirm it. A left join from customers preserves customers with no completed order. The API loop follows every page and respects rate limits. A stratified survey sample can preserve representation across small regions. Parameterized dates make the query reusable and safer. A temporary table is justified if the quarterly aggregation feeds several statements. ETL or ELT depends on where transformation is approved and efficient, not on the fact that the destination is called a warehouse.

Exam traps

• Using an inner join when the word “including” requires unmatched left-side records.
• Confusing WHERE with HAVING.
• Choosing UNION when legitimate duplicate events must remain.
• Assuming every nested query needs a temporary table.
• Adding an index without considering selectivity, write cost, or the query plan.
• Treating parameterization as ordinary string concatenation.
• Calling load-then-transform ETL.
• Using a voluntary survey response as if it were a representative sample.
• Ignoring API pagination after one successful response.
• Running a large extract with an administrative account against peak production.

Readiness checklist

• I can choose inner, left, and other joins from inclusion requirements.
• I can distinguish concatenation, union, filtering, grouping, and aggregation.
• I can explain when WHERE, HAVING, nested queries, and temporary tables fit.
• I can describe how indexes, parameters, and subsets improve a query safely.
• I can distinguish ETL from ELT by transformation order and location.
• I can choose full or incremental acquisition and name incremental edge cases.
• I can compare simple random, stratified, convenience, and voluntary sampling.
• I can plan safe API, file, and production-database extraction.
• I can verify that an acquired data set is complete before analysis begins.