CCNA Data Acquisition and Preparation Questions — Page 3 of 3

151

MCQmedium

An analyst is reviewing the above SQL query used to acquire data. What does this query retrieve?

A.Customers who placed more than 5 orders in 2023

B.All customers who placed at least 5 orders in 2023

C.The total number of orders per customer in 2023

D.Customers who placed exactly 5 orders in 2023

AnswerA

The query groups orders by customer, filters transactions to the 2023 calendar year, applies a HAVING clause counting orders per customer, and returns only those exceeding five. This yields the set of customers meeting that order-frequency threshold within the specified period.

Why this answer

The SQL query uses a HAVING clause with COUNT(*) > 5 to filter customers who placed more than 5 orders in 2023. The WHERE clause restricts records to the year 2023, and the GROUP BY customer_id aggregates orders per customer. The condition '> 5' explicitly excludes customers with exactly 5 or fewer orders, making option A correct.

Exam trap

The trap here is confusing the comparison operator '>' with '>=', leading candidates to mistakenly include customers with exactly 5 orders when the query explicitly excludes them.

How to eliminate wrong answers

Option B is wrong because 'at least 5 orders' would require the condition COUNT(*) >= 5, not > 5. Option C is wrong because the query returns customer IDs, not the total number of orders per customer; the COUNT is used only for filtering, not as a selected column. Option D is wrong because 'exactly 5 orders' would require COUNT(*) = 5, not > 5.

Practice this question →

152

MCQmedium

A dataset contains sales transactions with columns 'order_date', 'amount', and 'region'. The analyst wants to calculate the total sales per region for orders placed in 2023, but only include regions where total sales exceed $10,000. Which SQL clause should be used to filter the aggregated results?

A.HAVING

B.WHERE

C.GROUP BY

D.FILTER

AnswerA

HAVING filters groups after aggregation, unlike WHERE, which filters individual rows before grouping. Since the $10,000 threshold applies to SUM(amount) per region, not to individual transactions, HAVING is the only clause that can evaluate the aggregated total and exclude regions failing it.

Why this answer

HAVING is the SQL clause that filters rows after aggregation, so it can reference aggregate functions like SUM(amount) and apply conditions such as SUM(amount) > 10000. WHERE runs before GROUP BY and cannot reference aggregates, so it cannot filter on total sales per region.

Exam trap

DA0-002 often tests the WHERE vs. HAVING distinction — candidates pick WHERE because it 'filters,' forgetting that WHERE cannot reference aggregate functions.

How to eliminate wrong answers

Option B is wrong because WHERE filters individual rows before grouping and aggregation occur, so it cannot reference aggregate results like SUM(amount) — attempting to do so raises an error in standard SQL. Option C is wrong because GROUP BY is the clause that forms the groups (by region) but does not itself filter; it's a prerequisite for HAVING, not a filter. Option D is wrong because FILTER is not a standard SQL clause for post-aggregation filtering — it exists only as a modifier on aggregate functions in some dialects (e.g., PostgreSQL's FILTER (WHERE ...)), not as a standalone clause.

Practice this question →

153

MCQmedium

A data analyst uses a CTE to simplify a complex query. Which keyword is used to define a CTE?

A.DEFINE

B.CTE

C.DECLARE

D.WITH

AnswerD

The WITH keyword introduces a common table expression, defining a named temporary result set that exists only for the duration of a single statement. It satisfies the stem's requirement for simplifying a complex query by letting the analyst break logic into readable, reusable blocks referenced later in the main SELECT.

Why this answer

A Common Table Expression (CTE) is defined using the WITH keyword followed by a name and an AS clause containing the subquery. WITH precedes the main SELECT and can define one or more named CTEs separated by commas.

Exam trap

DA0-002 often tests basic SQL syntax recall — candidates confuse WITH (CTE) with DECLARE (procedural variables) or assume 'CTE' itself is a keyword.

How to eliminate wrong answers

Option A is wrong because DEFINE is not a SQL keyword for CTEs — it appears in some procedural languages (e.g., Snowflake scripting) but is not the standard CTE introducer. Option B is wrong because CTE is the concept's name, not a keyword — you never write 'CTE name AS (...)'. Option C is wrong because DECLARE is used for variables, cursors, and handlers in procedural SQL (T-SQL, PL/pgSQL), not for defining CTEs.

Practice this question →

154

MCQhard

An analyst writes a SQL query that uses a window function: SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary FROM employees. What does the LAG function return for the row with the highest salary?

A.The same salary value

B.NULL

C.The next highest salary

D.Zero

AnswerB

LAG(salary, 1) OVER (ORDER BY salary DESC) fetches the preceding row's salary in descending order. The highest salary occupies the first row, which has no predecessor, so the function returns NULL rather than a wrapped or default value.

Why this answer

LAG(salary, 1) OVER (ORDER BY salary DESC) returns the salary value from the previous row in the window ordering. For the row with the highest salary, there is no preceding row, so LAG returns NULL. This is the defined behavior of LAG when the offset goes beyond the partition boundary.

Exam trap

DA0-002 often tests the boundary behavior of LAG/LEAD — candidates assume the first row returns the current value or zero, when the correct answer is NULL because no preceding row exists.

How to eliminate wrong answers

Option A is wrong because LAG does not return the current row's value — that would be the behavior of a self-reference or FIRST_VALUE, not LAG. Option C is wrong because the next highest salary is the value of the following row, which is what LEAD would return, not LAG. Option D is wrong because LAG returns NULL, not zero, when there is no preceding row; zero would only appear if the underlying column value were zero or if COALESCE were applied.

Practice this question →

155

MCQmedium

An analyst is sampling a large customer database to estimate the average purchase amount. To ensure that the sample proportionally represents different customer segments (e.g., age groups), which sampling method should be used?

A.Systematic sampling

B.Simple random sampling

C.Cluster sampling

D.Stratified sampling

AnswerD

Stratified sampling divides the population into distinct strata — here, age groups — then draws proportionally from each, guaranteeing every segment is represented in the sample. This directly satisfies the stem's requirement for proportional representation across customer segments, unlike simple random sampling, which could under-represent smaller groups.

Why this answer

Stratified sampling divides the population into homogeneous subgroups (strata) based on a characteristic like age group, then samples proportionally from each stratum. This guarantees representation of every segment in the sample, which is exactly what the analyst needs to estimate the average purchase amount accurately across customer segments.

Exam trap

DA0-002 often tests sampling method recognition — candidates confuse stratified (proportional representation of known subgroups) with cluster (sampling whole groups) because both involve dividing the population.

How to eliminate wrong answers

Option A is wrong because systematic sampling selects every k-th element from an ordered list — it doesn't guarantee proportional representation of subgroups and can introduce periodicity bias if the list has a repeating pattern. Option B is wrong because simple random sampling gives every individual an equal chance but can, by chance, under- or over-represent small segments, especially in small samples. Option C is wrong because cluster sampling divides the population into clusters (e.g., stores, cities) and samples entire clusters — it reduces cost but increases variance and does not ensure segment proportionality.

Practice this question →

156

Multi-Selectmedium

A company is acquiring social media data via a public API. Which TWO considerations are important for ensuring ethical and legal compliance?

Select 2 answers

A.Share raw data with third parties for additional insights

B.Use the data for any internal analysis without restrictions

C.Anonymize personal identifiable information (PII) before storage

D.Cache data indefinitely to avoid repeated API calls

E.Comply with the platform's terms of service

AnswersC, E

Social media data routinely contains personal identifiers, so anonymising PII before storage limits re-identification risk and supports privacy regulation compliance. Removing or masking identifiers at ingestion satisfies the ethical and legal requirement to protect data subjects throughout the acquisition pipeline.

Why this answer

Option C is correct because anonymizing personally identifiable information (PII) before storage reduces privacy risk and helps satisfy data-protection regulations such as GDPR and CCPA, which require limiting the processing and retention of identifiable personal data. Option E is correct because a public API is governed by the platform's terms of service, and using the data outside those contractual permissions—such as for prohibited purposes or beyond rate limits—can constitute a legal and ethical violation. Options A and B are not appropriate because sharing raw data with third parties or using it for unrestricted internal analysis can breach privacy obligations and the API's usage terms.

Option D is also incorrect because caching data indefinitely conflicts with data-minimization and retention-limitation principles and may violate the platform's terms.

Exam trap

The trap here is that candidates may confuse 'caching for efficiency' (Option D) with ethical compliance, overlooking that indefinite storage violates data minimization principles and platform terms, while 'internal analysis' (Option B) seems harmless but ignores explicit usage restrictions in the API's terms of service.

Practice this question →

157

Multi-Selectmedium

A data analyst needs to identify duplicate customer records. Which TWO methods are commonly used? (Select two.)

Select 2 answers

A.Fuzzy matching using Levenshtein distance

B.Sorting and comparing adjacent rows

C.Visual inspection of random sample

D.Using a hash function on primary key

E.Exact match on all fields

AnswersA, B

Levenshtein distance measures the minimum single-character edits between strings, so records differing by typos or transpositions still match. This satisfies the need to catch near-duplicates that exact key comparison would miss across customer names and addresses.

Why this answer

Option A (Fuzzy matching using Levenshtein distance) is correct because it measures the minimum number of single-character edits (insertions, deletions, substitutions) needed to transform one string into another, making it ideal for catching near-duplicate customer records that differ slightly due to typos or formatting variations. Option B (Sorting and comparing adjacent rows) is correct because once records are sorted by a key field such as name or email, duplicate or near-duplicate entries naturally cluster together, allowing efficient pairwise comparison of neighboring rows to flag matches. Option C is not a reliable, scalable method since random sampling cannot guarantee detection of all duplicates and is subjective.

Option D does not help because hashing a primary key, which is unique by definition, will never reveal duplicates. Option E is too strict, as exact matching on all fields will miss duplicates that differ in even one attribute, such as a middle initial or apartment number.

Exam trap

The trap here is that candidates often choose 'Exact match on all fields' (Option E) thinking it is a reliable deduplication method, but in practice it fails to catch real-world duplicates that have any minor variation, and the exam expects you to recognize that fuzzy matching and sorted adjacency comparisons are the standard techniques for duplicate detection.

Practice this question →

158

MCQeasy

A data analyst receives the above JSON snippet from a web API. The analyst needs to extract the email addresses for all customers. Which JSONPath expression should be used?

A.$.customers[0].email

B.$..email

C.$.customers[*].email

D.$.customers.email

AnswerC

The wildcard `[*]` iterates every element of the `customers` array, while `.email` selects that key from each object, returning all addresses in one expression. This satisfies the stem's requirement to extract email addresses for all customers, not just a single indexed entry.

Why this answer

The JSONPath expression `$.customers[*].email` uses the wildcard `[*]` to select all elements in the `customers` array and then accesses the `email` property of each element. This matches the requirement to extract email addresses for all customers from the JSON snippet.

Exam trap

The trap here is that candidates often confuse the deep scan operator `..` with the array wildcard `[*]`, thinking `$..email` will neatly extract all customer emails, but it actually retrieves every `email` property at any depth, including from non-customer objects, leading to incorrect data extraction.

How to eliminate wrong answers

Option A is wrong because `$.customers[0].email` only retrieves the email address of the first customer in the array, not all customers. Option B is wrong because `$..email` uses the deep scan operator `..` which recursively searches the entire JSON tree for any property named `email`, potentially returning emails from nested objects or arrays that are not customers (e.g., from an `orders` or `address` object), leading to incorrect or extra results. Option D is wrong because `$.customers.email` attempts to access `email` directly on the `customers` array object, but arrays in JSONPath do not have a property named `email`; this expression would return `null` or an empty result unless the array itself has an `email` property, which it does not.

Practice this question →

159

MCQeasy

A data team needs to extract data from a legacy system that only supports flat file exports. Which data acquisition method is most appropriate?

A.Database replication

B.API call

C.Web scraping

D.File transfer via SFTP

AnswerD

SFTP transfers the flat files the legacy system exports, satisfying the constraint that no direct database or API access exists. It preserves file integrity over an encrypted channel, so scheduled batch extraction works reliably without custom connectors.

Why this answer

The legacy system only supports flat file exports, meaning it cannot provide direct database or API access. SFTP (SSH File Transfer Protocol) is the most appropriate method because it securely transfers flat files over a network, aligning with the system's export capabilities while ensuring data integrity and encryption during transit.

Exam trap

The trap here is that candidates may confuse 'flat file exports' with a need for real-time or API-based methods, overlooking that SFTP is the standard secure file transfer protocol for batch-oriented legacy systems.

How to eliminate wrong answers

Option A is wrong because database replication requires the source system to support a database engine with replication features (e.g., transactional logs or CDC), which a legacy flat-file-only system lacks. Option B is wrong because an API call requires the legacy system to expose a programmatic interface (e.g., REST or SOAP), which is not available if it only supports flat file exports. Option C is wrong because web scraping is used to extract data from web pages via HTTP, not from a legacy system that exports flat files via a file transfer protocol.

Practice this question →

160

MCQeasy

A data analyst is importing a fixed-width text file into a relational database. The file has no header row, and fields are separated by a single space, but some fields contain trailing spaces of varying lengths that shift the apparent column boundaries. The analyst must load the data reliably into the correct columns. Which approach is most appropriate?

A.Split each line on the single space delimiter and assign fields sequentially to columns.

B.Parse the file using fixed character positions derived from a documented layout specification rather than splitting on spaces.

C.Import the entire line into a single column and rely on downstream views to extract fields.

D.Replace all spaces with commas and then load the file as comma-separated values.

AnswerB

A fixed-width file defines each field by character position, so parsing by documented offsets preserves values even when trailing spaces vary. Splitting on spaces would misalign columns because multiple spaces are treated inconsistently. Using the layout specification is the reliable, repeatable way to load this format correctly.

Why this answer

Fixed-width files require position-based parsing because field boundaries are defined by character offsets, not delimiters. Documented offsets remain stable regardless of trailing spaces, whereas splitting on spaces or blindly converting delimiters misaligns columns. Loading the whole line or substituting commas defers or worsens the problem, so position-based parsing is the correct choice.

Exam trap

The trap here is assuming any whitespace-separated text is delimited data, when varying trailing spaces mean the file is actually fixed-width and must be parsed by position.

Practice this question →

161

MCQhard

A data analyst needs to perform stratified sampling on a customer database to ensure proportional representation across three regions: North (40%), South (30%), and West (30%). The total sample size required is 1,000. How many customers should be sampled from the North region?

A.333

B.500

C.300

D.400

AnswerD

Stratified sampling allocates sample counts proportionally to each stratum's share of the population. North represents 40% of customers, so its allocation is 0.40 × 1,000 = 400. This satisfies the stem's proportional-representation constraint directly, giving North exactly its 40% share of the sample.

Why this answer

Stratified sampling allocates sample size proportionally to each stratum's share of the population. North represents 40% of the population, so 40% of the 1,000-customer sample — 400 customers — should be drawn from North.

Exam trap

DA0-002 often tests whether candidates can apply the proportional allocation formula — the trap is confusing the region's percentage with the sample count or mixing up which region gets which share.

How to eliminate wrong answers

Option A is wrong because 333 corresponds to roughly one-third (33.3%), which would be the allocation if the three regions were equal — but North is 40%, not 33%. Option B is wrong because 500 represents 50% of the sample, which would apply only if North were half the population. Option C is wrong because 300 represents 30%, which is the correct allocation for South or West, not North.

Practice this question →

162

MCQhard

A data analyst needs to create a recursive CTE to traverse a hierarchical employee-manager table. Which of the following is a key requirement for a recursive CTE?

A.The CTE must include a WHERE clause in the recursive member

B.The CTE must use the RECURSIVE keyword in the WITH clause

C.The recursive CTE must have at least one anchor member that does not reference the CTE

D.The recursive member must use UNION instead of UNION ALL

AnswerC

A recursive CTE requires an anchor member returning the base rows plus a recursive member referencing the CTE itself, combined by UNION ALL. The anchor must not reference the CTE, otherwise the query cannot establish its initial result set.

Why this answer

A recursive CTE is defined by two parts: an anchor member that produces the base result set without referencing the CTE itself, and a recursive member that references the CTE and is combined with the anchor via UNION ALL (or UNION). The anchor member is mandatory — without it, the recursion has no starting point and the query fails. This is the defining structural requirement of a recursive CTE.

Exam trap

DA0-002 often tests the misconception that the RECURSIVE keyword or a WHERE clause is mandatory; the true structural requirement is the anchor member, which candidates frequently overlook.

How to eliminate wrong answers

Option A is wrong because a WHERE clause is not a structural requirement of the recursive member; filtering is common but optional, and recursion terminates via the join condition or an explicit depth limit, not a mandatory WHERE. Option B is wrong because the RECURSIVE keyword is optional in many engines — PostgreSQL, SQL Server, and Oracle accept WITH RECURSIVE or plain WITH depending on the dialect, and MySQL requires RECURSIVE, so it is not a universal requirement. Option D is wrong because UNION ALL is actually the more common and often required form; UNION (which deduplicates) is allowed but not mandatory, and using UNION ALL is standard practice for performance.

Practice this question →

163

MCQhard

A data analyst discovers that a dataset contains multiple records for the same customer with different spellings (e.g., 'Jon' vs 'John'). Which data preparation step should be applied first?

A.Merge all records into one per customer.

B.Remove duplicates based on exact match.

C.Standardize text fields using a lookup table.

D.Flag records for manual review.

AnswerC

Variant spellings of the same customer must be reconciled before deduplication or matching. A lookup table maps each variant to a canonical value, standardising the text field first so subsequent joins and dedupe logic treat 'Jon' and 'John' as one entity.

Why this answer

The first step when dealing with inconsistent text values (like 'Jon' vs 'John') is to standardize the data using a lookup table or reference mapping. This ensures that all variations are normalized to a canonical form before any merging or deduplication is attempted, preventing data loss and preserving referential integrity.

Exam trap

The trap here is that candidates often jump to 'remove duplicates' (Option B) because they think of exact-match deduplication, but the question specifically tests the understanding that data quality issues like inconsistent spellings must be resolved through standardization before any deduplication logic can be applied.

How to eliminate wrong answers

Option A is wrong because merging records before standardizing spellings would combine data based on non-uniform keys, likely creating erroneous composite records or losing the ability to correctly identify which records belong to the same customer. Option B is wrong because removing duplicates based on exact match would treat 'Jon' and 'John' as different records, failing to identify them as the same customer and leaving the inconsistency unresolved. Option D is wrong because flagging records for manual review is a downstream action that should only be taken after automated standardization has been attempted; skipping standardization first would result in an unnecessarily large and inefficient manual review workload.

Practice this question →

164

MCQeasy

A data analyst is using SQL to extract data. The analyst wants to retrieve all records from a table named 'sales' where the 'amount' column is greater than 100. Which SQL clause should be used?

A.WHERE

B.ORDER BY

C.GROUP BY

D.HAVING

AnswerA

WHERE filters individual rows before any grouping or aggregation, returning only those sales records whose amount exceeds 100. It satisfies the stem's constraint of retrieving all matching rows from the sales table, unlike HAVING, which filters grouped results after aggregation and cannot reference non-aggregated row values in this way.

Why this answer

The WHERE clause in SQL is used to filter records based on a specified condition, such as 'amount > 100'. It is applied directly to the rows in the 'sales' table before any grouping or ordering, making it the correct choice for retrieving only records where the amount exceeds 100.

Exam trap

The trap here is that candidates often confuse HAVING with WHERE, thinking both can filter rows, but HAVING is only valid after GROUP BY and for aggregate conditions, while WHERE filters individual rows before any grouping.

How to eliminate wrong answers

Option B (ORDER BY) is wrong because it is used to sort the result set by one or more columns, not to filter rows based on a condition. Option C (GROUP BY) is wrong because it groups rows that have the same values in specified columns into summary rows, often for use with aggregate functions, and does not filter individual records. Option D (HAVING) is wrong because it is used to filter groups after the GROUP BY clause has been applied, typically with aggregate functions, and cannot be used to filter individual rows before grouping.

Practice this question →

165

MCQeasy

A marketing analyst must combine two datasets: a CRM extract with one row per customer and a transactions extract with many rows per customer. The analyst wants every customer from the CRM to appear in the output, even customers with no matching transactions. Which join type should be used?

A.LEFT JOIN

B.CROSS JOIN

C.FULL OUTER JOIN

D.INNER JOIN

AnswerA

A left join keeps all rows from the left table (the CRM customers) and attaches matching transaction rows where they exist, producing NULLs for customers without transactions. This satisfies the requirement that every customer appears while still enriching those who have purchase activity. It is the standard pattern for preserving a master list during an enrichment join.

Why this answer

A left join anchors the result on the CRM customer list and enriches it with transaction data, retaining customers who never purchased. Unmatched customers receive NULL transaction fields, which is exactly the desired behavior for a master-list enrichment. Inner, full outer, and cross joins either drop customers, add unwanted orphans, or fabricate pairings.

Exam trap

The trap here is defaulting to an inner join out of habit and silently dropping the non-purchasing customers the analyst explicitly needs.

Practice this question →

166

MCQmedium

A data analyst wants to assign a unique sequential integer to each row in a result set, starting at 1, based on the order of the 'sales_amount' column descending. Which window function should be used?

A.DENSE_RANK() OVER (ORDER BY sales_amount DESC)

B.RANK() OVER (ORDER BY sales_amount DESC)

C.NTILE(1) OVER (ORDER BY sales_amount DESC)

D.ROW_NUMBER() OVER (ORDER BY sales_amount DESC)

AnswerD

ROW_NUMBER() assigns a unique sequential integer to every row, starting at 1, with no ties or gaps. Ordering by sales_amount DESC satisfies the stem's ranking constraint, so the highest sale receives 1. RANK() and DENSE RANK() would repeat values for tied amounts, breaking the uniqueness requirement.

Why this answer

ROW_NUMBER() assigns a unique sequential integer to each row starting at 1, with no ties and no gaps, based on the ORDER BY clause. Since the requirement is a unique sequential integer per row ordered by sales_amount DESC, ROW_NUMBER() is the correct function.

Exam trap

DA0-002 often tests the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() — candidates pick RANK() or DENSE_RANK() forgetting that only ROW_NUMBER() guarantees unique sequential integers.

How to eliminate wrong answers

Option A is wrong because DENSE_RANK() assigns the same rank to tied values and leaves no gaps (e.g., 1,1,2), so it does not guarantee a unique integer per row. Option B is wrong because RANK() also assigns the same rank to ties and leaves gaps (e.g., 1,1,3), violating the 'unique sequential integer' requirement. Option C is wrong because NTILE(1) divides the result set into 1 bucket, assigning 1 to every row — it does not produce sequential integers.

Practice this question →

167

Multi-Selecteasy

A data analyst needs to retrieve the top 5 most expensive products from a 'products' table sorted by price descending. Which TWO SQL clauses are required to achieve this? (Select TWO).

Select 2 answers

A.HAVING COUNT(*) > 1

B.WHERE price > 100

C.ORDER BY price DESC

D.GROUP BY price

E.LIMIT 5

AnswersC, E

ORDER BY price DESC sorts the result set by the price column in descending order, placing the most expensive products first. This directly satisfies the stem's requirement to rank products by price from highest to lowest, which the LIMIT clause then truncates to the top 5 rows.

Why this answer

ORDER BY price DESC (C) is required because it sorts the result set by the price column in descending order, placing the most expensive products first. LIMIT 5 (E) is required because it restricts the result set to only the first five rows returned after sorting, giving the top 5 most expensive products. Together, ORDER BY price DESC followed by LIMIT 5 produce exactly the requested output.

The other options do not belong: HAVING COUNT(*) > 1 (A) filters grouped aggregate results, WHERE price > 100 (B) applies a fixed numeric filter rather than selecting the top 5, and GROUP BY price (D) aggregates rows by price instead of simply sorting and limiting them.

Practice this question →

168

MCQhard

A data engineer is ingesting a 40 GB JSON event log into a columnar analytics platform. The file contains deeply nested arrays of user actions, and queries only ever filter on three top-level fields: event_id, event_type, and event_timestamp. The ingestion is currently slow and queries scan excessive data. Which preparation approach is MOST appropriate?

A.Load the JSON as a single string column and rely on the query engine to parse it at read time.

B.Flatten the top-level fields into typed columns, extract the nested arrays into a separate child table, and partition or cluster on event_timestamp.

C.Store the file in a row-oriented relational table with indexes on all nested array paths.

D.Normalize every nested array element into its own row in a fully relational schema with foreign keys.

AnswerB

Extracting the frequently filtered scalars into native typed columns lets the columnar engine read only those columns and apply partition pruning on event_timestamp. Moving the rarely queried nested arrays into a child table keeps the main fact table narrow and fast. This matches the access pattern precisely and reduces both ingestion cost and scan volume.

Why this answer

Matching the storage layout to the access pattern is the decisive factor. Promoting the three filtered scalars to typed columns enables column pruning and partition pruning, while relocating the unused nested arrays keeps the hot table narrow. Parsing strings or fully normalizing both impose costs the workload does not justify.

Exam trap

The trap here is treating JSON flattening as all-or-nothing and either parsing at query time or fully normalizing, instead of selectively promoting the columns the workload actually filters on.

Practice this question →

169

MCQeasy

A retail company wants to analyze customer purchase patterns to identify products frequently bought together. Which data mining technique is most appropriate?

A.Classification

B.Clustering

C.Regression

D.Association rules

AnswerD

Association rules discover co-occurrence relationships between items in transactional data, directly satisfying the requirement to identify products frequently bought together. Algorithms such as Apriori and FP-Growth generate rules like {bread} → {butter}, quantified by support, confidence and lift, which is precisely the market-basket analysis this retail scenario demands.

Why this answer

Association rules are specifically designed to uncover relationships between items in transactional datasets, such as 'customers who buy X also buy Y.' This technique generates rules like {bread, butter} → {milk} with metrics such as support, confidence, and lift, directly answering the question of which products are frequently bought together. Classification, clustering, and regression serve different purposes: they predict labels, group similar instances, or model continuous relationships, respectively. Therefore, association rules are the most appropriate choice for market basket analysis.

Exam trap

The trap here is confusing association rules with clustering because both are unsupervised and used for pattern discovery, but clustering groups similar items while association rules find co-occurrence relationships between items in transactions.

How to eliminate wrong answers

Option A is wrong because classification is a supervised learning technique used to assign predefined labels to records (e.g., spam/not spam), not to discover co-occurrence patterns among items. Option B is wrong because clustering is an unsupervised technique that groups similar data points based on distance metrics, but it does not identify 'if-then' relationships between products in transactions. Option C is wrong because regression is used to predict a continuous numeric value (e.g., sales amount) based on independent variables, not to find frequent itemsets or association rules.

Practice this question →

170

MCQhard

A data analyst is preparing to acquire clickstream data from a web analytics platform via its REST API. The API returns paginated results with a maximum of 500 records per page and issues a short-lived bearer token that expires after one hour. The analyst needs to backfill six months of event data reliably. Which acquisition design is most appropriate?

A.Use the API's default page size and restart the entire backfill from the first page whenever the token expires.

B.Request the maximum page size and loop through pages, refreshing the bearer token before expiry and persisting a cursor or timestamp checkpoint after each successful page.

C.Request one record per page to avoid hitting rate limits, and store the token in the script so it can be reused across runs.

D.Pull all data in a single long-running request and write the response to disk when the connection closes.

AnswerB

Pagination with the maximum page size minimizes request count, token refresh prevents mid-run authentication failures, and a persisted checkpoint allows the job to resume without re-pulling data after an interruption. This design is resilient to the two stated constraints and avoids duplicate or missing records. Checkpointing on a timestamp or cursor also supports incremental runs beyond the initial backfill.

Why this answer

The API imposes two constraints: pagination and short-lived authentication. The sound design respects both by using the largest allowed page, proactively refreshing the token, and checkpointing after each page so progress survives interruptions. This yields efficient, resumable, and duplicate-avoiding acquisition.

Approaches that use tiny pages, single long requests, or full restarts on token expiry either trigger rate limits, cannot complete, or repeatedly re-download data.

Exam trap

The trap here is treating token expiry and pagination as separate concerns, when a robust backfill must handle both together with checkpointing.

Practice this question →

171

MCQeasy

A data analyst needs to retrieve only unique job titles from the 'employees' table. Which SQL keyword should be used in the SELECT clause?

A.TOP

B.DISTINCT

C.UNIQUE

D.FILTER

AnswerB

DISTINCT in the SELECT clause removes duplicate rows from the result set, so repeated job titles collapse to a single occurrence. This satisfies the requirement to retrieve only unique job titles from the employees table.

Why this answer

The DISTINCT keyword in the SELECT clause removes duplicate rows from the result set, so 'SELECT DISTINCT job_title FROM employees' returns each unique job title only once. It is the standard SQL keyword defined in the SQL standard for deduplication of result rows. DISTINCT applies to the entire selected column list, not just one column.

Exam trap

The trap is confusing the UNIQUE constraint (a DDL keyword) with DISTINCT (a DML query keyword), leading candidates to pick UNIQUE for deduplication in a SELECT statement.

How to eliminate wrong answers

Option A (TOP) is wrong because TOP limits the number of rows returned (e.g., TOP 10) and does not remove duplicates. Option C (UNIQUE) is wrong because UNIQUE is a constraint used in table definitions (CREATE TABLE / ALTER TABLE) to enforce uniqueness, not a SELECT clause keyword for deduplication. Option D (FILTER) is wrong because FILTER is used with aggregate functions (e.g., COUNT(*) FILTER (WHERE ...)) to conditionally include rows in an aggregate, not to deduplicate result rows.

Practice this question →

172

MCQmedium

A retail analytics team needs to load a 40 GB CSV file of point-of-sale transactions into a cloud data warehouse nightly. The file is generated as a single object by an upstream system, and the team must minimize load time. Which approach best addresses the load performance bottleneck?

A.Split the file into multiple smaller files and load them in parallel.

B.Convert the CSV to JSON and load it as a semi-structured format.

C.Compress the file using gzip and load the single compressed file.

D.Load the file into a staging table using row-by-row inserts.

AnswerA

Splitting the large CSV into multiple smaller files allows the data warehouse to ingest them concurrently, dramatically reducing overall load time. Parallel ingestion is a standard best practice for bulk loading large datasets, as it leverages distributed compute resources and avoids single-threaded bottlenecks. This directly addresses the performance issue without altering the data content.

Why this answer

The correct approach is to split the large file into multiple smaller files and load them in parallel. This leverages the distributed architecture of modern data warehouses, enabling concurrent ingestion and significantly reducing overall load time. Other options either do not address the parallelism bottleneck or introduce additional processing overhead that worsens performance.

Exam trap

The trap here is assuming that compressing the file alone will solve the load time issue, but compression only reduces transfer size and does not enable parallel processing.

Practice this question →

173

MCQhard

A financial analyst is preparing a dataset of stock transactions for a machine learning model. The 'transaction_amount' column has a highly skewed distribution with a few extremely large values. The analyst decides to apply a logarithmic transformation to this column. Which statement best describes the effect of this transformation?

A.It eliminates all outliers from the dataset.

B.It reduces the impact of outliers by compressing the scale of large values.

C.It normalizes the data to a mean of 0 and standard deviation of 1.

D.It converts the data from continuous to categorical.

AnswerB

A logarithmic transformation compresses the range of large values more than small ones, reducing skewness and the influence of outliers. This makes the distribution more symmetric and can improve the performance of models that assume normality. It is a common technique for handling skewed data.

Why this answer

The logarithmic transformation is used to reduce right skewness by compressing the scale of large values. This lessens the leverage of extreme outliers on statistical models without removing them. The transformation is monotonic, so it preserves the order of data points.

It does not standardize the data or change its type; it simply alters the distribution shape to be more symmetric.

Exam trap

The trap here is confusing logarithmic transformation with standardization or outlier removal, which serve different purposes.

Practice this question →

174

Multi-Selecteasy

Which TWO of the following are valid SQL clauses used to filter and sort data?

Select 2 answers

A.DELETE

B.WHERE

C.ORDER BY

D.UPDATE

E.INSERT

AnswersB, C

WHERE filters rows before grouping or aggregation, applying a predicate to each row and returning only those satisfying the condition. This directly satisfies the stem's requirement for a valid SQL filtering clause, distinct from sorting clauses such as ORDER BY. It cannot sort results, but filtering alone qualifies it as one of the two valid answers.

Why this answer

Option B, WHERE, is correct because it is the SQL clause that filters rows by applying a Boolean predicate to each row before it is returned, as in SELECT ... FROM table WHERE condition. Option C, ORDER BY, is correct because it is the SQL clause that sorts the result set by one or more columns, optionally with ASC or DESC, as in SELECT ...

FROM table ORDER BY column. The remaining options are not filtering or sorting clauses: DELETE (A) is a DML statement that removes rows, UPDATE (D) is a DML statement that modifies existing rows, and INSERT (E) is a DML statement that adds new rows.

Exam trap

CompTIA often tests the distinction between SQL DML statements (DELETE, UPDATE, INSERT) and query clauses (WHERE, ORDER BY), trapping candidates who confuse data manipulation commands with data retrieval or sorting operations.

Practice this question →

175

Multi-Selecthard

A data analyst is using Python pandas to perform exploratory data analysis. Which THREE methods are commonly used to assess data quality and distributions?

Select 3 answers

A.df.transpose()

B.df.describe()

C.df.info()

D.df.sort_values()

E.df.value_counts()

AnswersB, C, E

df.describe() returns count, mean, standard deviation, minimum, quartiles and maximum for numeric columns, exposing outliers, skew and missing values. This single call summarises distribution shape and completeness, making it a standard first step in data-quality assessment.

Why this answer

describe() gives summary statistics, info() shows data types and non-null counts, and value_counts() shows frequency distributions.

Practice this question →

176

MCQmedium

A data analyst is preparing a dataset for a machine learning model. The dataset contains a 'country' column with 150 unique values. To reduce dimensionality, the analyst wants to group less frequent countries into an 'Other' category. Which technique is being applied?

A.One-hot encoding

B.Normalization

C.Binning

D.Imputation

AnswerC

Binning (or bucketing) groups continuous or categorical values into a smaller number of bins. Here, grouping infrequent countries into 'Other' is a form of categorical binning. This reduces the number of unique categories, simplifies the feature, and can improve model performance by limiting noise from rare categories. It is a standard dimensionality reduction technique for high-cardinality categorical variables.

Why this answer

The technique is binning, specifically grouping infrequent categories into an 'Other' bin. This reduces the cardinality of the 'country' feature, which can help prevent overfitting and improve model training efficiency. Binning is a common approach for handling high-cardinality categorical variables by consolidating rare levels into a single category.

Exam trap

The trap here is confusing binning with one-hot encoding, but one-hot encoding expands the feature space while binning reduces it.

Practice this question →

177

MCQmedium

A data analyst at a retail company is profiling a newly acquired customer table. They observe that the 'last_purchase_date' column contains values such as '2023-13-45', '0000-00-00', and '2023-02-30'. Which data quality dimension is primarily violated?

A.Uniqueness

B.Completeness

C.Validity

D.Consistency

AnswerC

Validity checks whether data conforms to defined formats, types, or business rules. Here, the date values are not valid calendar dates (month 13, day 45, day 30 in February), so they violate the validity dimension. The analyst must apply date validation rules to flag or correct these entries before using the data for analysis.

Why this answer

The date values shown are not valid calendar dates, meaning they fail format and range checks. Validity ensures data adheres to defined rules, such as a date being a real date. Completeness, consistency, and uniqueness address different aspects and do not capture the core problem of impossible dates.

Exam trap

The trap here is confusing malformed values with missing values, leading to a completeness answer instead of validity.

Practice this question →

178

Drag & Dropmedium

Drag and drop the steps to perform a data backup using the 3-2-1 rule in the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

The 3-2-1 rule involves multiple copies, different media, offsite storage, and regular testing.

Practice this question →

179

Multi-Selectmedium

A data analyst is profiling a new dataset and needs to assess data quality. Which two metrics are most appropriate for evaluating the completeness and consistency of the data? (Choose two.)

Select 2 answers

A.Data type mismatch count between source and target

B.Frequency of values violating defined business rules

C.Number of distinct values in a column

D.Percentage of missing values per column

E.Average record length in bytes

AnswersB, D

The frequency of values violating business rules measures consistency and validity. Business rules define acceptable ranges or formats, and violations indicate data that does not conform. This metric is crucial for identifying data quality issues that could lead to incorrect analysis or decisions.

Why this answer

Completeness is assessed by the percentage of missing values, which shows how much data is absent. Consistency is assessed by the frequency of business rule violations, which reveals non-conforming data. Together, these metrics provide a clear picture of data quality issues that need addressing before analysis.

Exam trap

The trap here is selecting metrics that seem related to data quality but do not directly measure completeness or consistency, such as distinct value counts or record length.

Practice this question →

180

Multi-Selectmedium

A data analyst wants to retrieve the top 5 highest-paid employees from the 'employees' table. Which SQL clauses could be used to achieve this? (Select TWO.)

Select 2 answers

A.ORDER BY salary DESC

B.HAVING salary

C.ORDER BY salary ASC

D.LIMIT 5

E.GROUP BY salary

AnswersA, D

ORDER BY salary DESC sorts the result set by the salary column in descending order, placing the highest-paid employees first. Combined with a limiting clause such as TOP 5 or FETCH FIRST 5 ROWS ONLY, it satisfies the stem's requirement to retrieve exactly the top five earners.

Why this answer

Option A, ORDER BY salary DESC, is correct because it sorts the result set by the salary column in descending order, placing the highest-paid employees first so the top earners can be identified. Option D, LIMIT 5, is correct because it restricts the result set to only the first 5 rows, which after the descending sort yields exactly the top 5 highest-paid employees. Together, ORDER BY salary DESC LIMIT 5 produces the desired result.

Option C, ORDER BY salary ASC, is wrong because ascending order returns the lowest-paid employees first, the opposite of what is needed. Option B, HAVING salary, is wrong because HAVING filters groups after aggregation and is not used to sort or limit rows. Option E, GROUP BY salary, is wrong because grouping by salary aggregates rows by distinct salary values rather than selecting the top 5 individual employees.

Exam trap

The trap here is confusing the roles of ORDER BY and LIMIT: candidates might think HAVING or GROUP BY can be used to get top N, or they might choose ascending order instead of descending.

Practice this question →

181

MCQmedium

A data analyst is profiling a new dataset containing customer information. When assessing data quality, which metric would be most appropriate to determine if the 'email' column contains valid email addresses?

A.Pattern analysis

B.Null count

C.Cardinality

D.Row count

AnswerA

Pattern analysis examines whether values conform to an expected format, such as the structure of a valid email address. Applying it to the 'email' column reveals entries that deviate from that pattern, directly satisfying the requirement to assess validity of email addresses.

Why this answer

Pattern analysis is the correct metric because it validates data against a defined format or regular expression — exactly what's needed to confirm that values in the 'email' column conform to the structure of a valid email address (e.g., user@domain.tld). Data profiling tools use pattern/format analysis to detect values that deviate from expected structures, making it the appropriate quality dimension for format validation.

Exam trap

The trap here is confusing data quality dimensions — candidates often pick cardinality or null count because they sound like 'profiling' metrics, but the question specifically asks about validating format, which only pattern analysis addresses.

How to eliminate wrong answers

Option B is wrong because null count only measures missing values and says nothing about whether populated values are structurally valid email addresses. Option C is wrong because cardinality measures the number of distinct values in a column, which is useful for detecting duplicates or uniqueness but not format validity. Option D is wrong because row count simply reports the total number of records and provides no information about the correctness or format of any individual value.

Practice this question →

182

MCQhard

A data analyst is using the IQR method to identify outliers in a dataset. The first quartile (Q1) is 25 and the third quartile (Q3) is 45. What is the upper bound for identifying outliers?

A.85

B.65

C.75

D.55

AnswerC

The IQR is Q3 minus Q1, giving 20. The upper outlier bound is Q3 plus 1.5 times the IQR, so 45 + 30 = 75. This satisfies the stem's request for the upper bound using the IQR method.

Why this answer

The IQR method defines outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Here, Q1=25, Q3=45, so IQR = 45-25 = 20. The upper bound is Q3 + 1.5*IQR = 45 + 1.5*20 = 45 + 30 = 75.

Therefore, any value above 75 is considered an outlier.

Exam trap

DA0-002 often tests the IQR calculation. Candidates might forget the 1.5 multiplier or miscalculate the IQR, leading to wrong bounds. The trap is using the wrong formula or arithmetic error.

How to eliminate wrong answers

Option A is wrong because 85 is Q3 + 2*IQR (45+40), which is not the standard IQR method. Option B is wrong because 65 is Q3 + 1*IQR (45+20), which is not the correct multiplier. Option D is wrong because 55 is Q3 + 0.5*IQR (45+10), which is also incorrect.

Practice this question →

183

MCQmedium

A data analyst uses the following query: SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000. What is the purpose of the HAVING clause in this query?

A.To ensure only departments with more than 50000 employees are shown

B.To filter individual employee records before grouping

C.To sort departments by average salary

D.To filter groups (departments) based on the average salary

AnswerD

HAVING filters aggregated results after GROUP BY executes, unlike WHERE which filters rows beforehand. Here it discards department groups whose computed average salary does not exceed 50000, restricting output to qualifying groups based on the aggregate.

Why this answer

The HAVING clause filters groups after aggregation, so in this query it keeps only departments whose average salary exceeds 50000. It operates on the result of GROUP BY, which is why it can reference aggregate functions like AVG(salary) that WHERE cannot.

Exam trap

DA0-002 often tests the WHERE vs HAVING distinction — candidates pick 'filter individual employee records before grouping' because they conflate the two clauses, missing that HAVING operates on aggregated groups and is the only clause that can filter on aggregate results.

How to eliminate wrong answers

Option A is wrong because HAVING AVG(salary) > 50000 compares the average salary value, not a count of employees — to filter by employee count you would write HAVING COUNT(*) > 50000. Option B is wrong because filtering individual rows before grouping is the job of the WHERE clause, which runs before GROUP BY; HAVING runs after aggregation. Option C is wrong because sorting is done with ORDER BY, not HAVING — HAVING only includes or excludes groups, it does not order them.

Practice this question →

184

Multi-Selecthard

After merging two datasets, an analyst finds that the resulting dataset has many null values in some columns. Which TWO steps should the analyst take to address this? (Select two.)

Select 2 answers

A.Ignore nulls and proceed.

B.Impute nulls with the median.

C.Remove all rows with nulls.

D.Replace nulls with a placeholder value like 'Unknown'.

E.Investigate the cause of nulls.

AnswersB, E

Median imputation fills missing numeric values with the column's central value, which is robust to outliers and preserves the row count needed for analysis. It satisfies the requirement to handle nulls introduced by the merge without discarding records, though it reduces variance and should follow cause investigation.

Why this answer

Option B is correct because imputing nulls with the median is a robust statistical technique that fills missing numeric values with the column's central tendency, reducing data loss while limiting the influence of outliers compared to the mean. Option E is correct because investigating the cause of nulls is an essential diagnostic step: nulls introduced by a dataset merge often indicate unmatched join keys, schema mismatches, or missing source records, and understanding the root cause determines whether imputation, key correction, or source remediation is appropriate. Options A, C, and D are not among the correct answers: ignoring nulls (A) can bias analysis and break models, removing all rows with nulls (C) can discard substantial valid data and skew distributions, and replacing nulls with a placeholder like 'Unknown' (D) is only suitable for categorical data and can corrupt numeric columns or mislead downstream processing.

Exam trap

The trap here is that candidates may think 'Ignore nulls and proceed' is acceptable, but the exam tests the understanding that nulls must be actively handled to ensure data quality and model validity, not simply overlooked.

Practice this question →

185

MCQeasy

A data analyst runs the following query: SELECT DISTINCT city FROM customers. What is the primary purpose of using the DISTINCT keyword in this query?

A.To sort the cities alphabetically

B.To count the number of cities

C.To filter cities that start with a specific letter

D.To remove duplicate city names

AnswerD

DISTINCT collapses duplicate rows in the result set, so each city name appears once. Applied to the single selected column, it returns the unique list of cities, eliminating repeated values that would otherwise appear for every matching customer record.

Why this answer

The DISTINCT keyword in a SELECT statement eliminates duplicate rows from the result set. In 'SELECT DISTINCT city FROM customers', if multiple customers live in the same city, that city name appears only once in the output, producing a list of unique city values. DISTINCT operates on the entire row of selected columns, so if multiple columns are selected, the combination must be unique to be retained.

Exam trap

The trap is confusing DISTINCT with ORDER BY or COUNT, causing candidates to select an answer that describes sorting or counting rather than deduplication.

How to eliminate wrong answers

Option A is wrong because sorting is performed by the ORDER BY clause, not DISTINCT — DISTINCT does not guarantee any particular order, and the result set order is undefined without an explicit ORDER BY. Option B is wrong because counting rows requires the COUNT() aggregate function (e.g., 'SELECT COUNT(DISTINCT city) FROM customers'), not the DISTINCT keyword alone. Option C is wrong because filtering rows based on a condition requires the WHERE clause (e.g., 'WHERE city LIKE 'A%''), not DISTINCT, which only removes duplicates after the rows are selected.

Practice this question →

186

MCQmedium

A retail company is integrating sales data from three regional databases into a central data warehouse. The 'product_id' column is defined as an integer in two databases but as a variable-length string in the third. During the ETL process, the analyst must ensure that product_id values are consistent for joining with the product dimension table. Which data transformation should the analyst perform?

A.Apply data normalization to product_id

B.Convert all product_id values to string

C.Convert all product_id values to integer

D.Use data aggregation on product_id

AnswerB

Converting all product_id values to a string data type ensures consistency and avoids loss of leading zeros or non-numeric characters. String representation is the most flexible for joining across sources, as it accommodates numeric and alphanumeric identifiers. This transformation aligns the data types for the join operation.

Why this answer

The product_id column must have a consistent data type across all sources to perform joins. Converting all values to string is the safest choice because it preserves leading zeros and any alphanumeric characters. Integer conversion could fail or lose information if the string column contains non-numeric values.

String conversion ensures compatibility without data loss.

Exam trap

The trap here is assuming that numeric identifiers should always be stored as integers, ignoring the possibility of alphanumeric or leading-zero values.

Practice this question →

187

MCQeasy

A data analyst is tasked with collecting data from multiple spreadsheets provided by different departments. Each spreadsheet has different column names and formats. What is the best first step?

A.Develop a data dictionary and standardize column names

B.Discard any mismatched data

C.Use a machine learning model to clean data

D.Immediately load all data into a database

AnswerA

Differing column names and formats across departmental spreadsheets create schema conflicts that must be resolved before any merging or analysis. A data dictionary documents each source field and its meaning, enabling analysts to map and standardise names consistently, satisfying the stem's requirement for a first step addressing heterogeneous source structures.

Why this answer

Developing a data dictionary and standardizing column names ensures consistency across all data sources before loading, reducing errors and facilitating integration. Immediately loading data can cause inconsistencies. Discarding mismatched data loses potentially valuable information.

Using a machine learning model is an unnecessary and complex first step.

Practice this question →

188

MCQmedium

A data analyst uses a CTE to find employees who earn more than the average salary in their department. Which SQL clause is used to define the CTE?

A.DECLARE

B.WITH

C.DEFINE

D.CTE

AnswerB

The WITH clause introduces a named common table expression, letting the analyst define the per-department average salary subquery once and reference it in the main SELECT. This satisfies the stem's requirement for computing employees earning above their department's average without repeating the aggregate logic.

Why this answer

The WITH clause is the standard SQL syntax that introduces a Common Table Expression (CTE), allowing the analyst to define a named temporary result set (e.g., WITH dept_avg AS (SELECT dept, AVG(salary) ...)) that can then be referenced in the main query. This is defined in the SQL standard and supported by PostgreSQL, SQL Server, Oracle, and MySQL 8+. The CTE exists only for the duration of the single statement that follows it.

Exam trap

The trap here is confusing the conceptual name 'CTE' with the actual SQL keyword — candidates who know the term but not the syntax may select 'CTE' or 'DEFINE' instead of the correct WITH clause.

How to eliminate wrong answers

Option A is wrong because DECLARE is used in T-SQL/PL-SQL to declare variables, cursors, or table variables — not to define a CTE. Option C is wrong because DEFINE is not a SQL keyword for query result sets; it appears in other contexts (e.g., Snowflake's DEFINE or Oracle SQL*Plus) but never introduces a CTE. Option D is wrong because CTE is a conceptual term for 'Common Table Expression,' not an actual SQL keyword — no SQL dialect uses 'CTE' as a clause.

Practice this question →

189

MCQmedium

An e-commerce company is acquiring product data from multiple supplier APIs. The APIs return JSON with inconsistent field naming conventions. Which data acquisition technique should be applied?

A.Data compression

B.Data mapping and transformation

C.Data deduplication

D.Data aggregation

AnswerB

Mapping reconciles differing field names across supplier schemas into one canonical structure, while transformation standardises values. This satisfies the stem's inconsistent naming constraint, since raw ingestion would yield mismatched columns that cannot be joined or queried consistently.

Why this answer

Data mapping and transformation is the correct technique because the JSON responses from different supplier APIs use inconsistent field naming conventions (e.g., 'product_id' vs. 'ProductID'). This technique defines a schema to map source fields to a standardized target format, ensuring data consistency before loading into the company's system. Without transformation, downstream processes like analytics or inventory management would fail due to mismatched field names.

Exam trap

The trap here is that candidates confuse data transformation with data aggregation or deduplication, assuming any processing step can fix schema inconsistencies, but only mapping and transformation directly address field naming and structure mismatches.

How to eliminate wrong answers

Option A is wrong because data compression reduces storage size or transfer bandwidth, but does not address structural inconsistencies in field naming. Option C is wrong because data deduplication removes duplicate records based on content, but does not reconcile different field names or schemas. Option D is wrong because data aggregation summarizes or combines data (e.g., sums, averages), but does not resolve naming conflicts or schema mismatches.

Practice this question →

190

MCQhard

A dataset contains transaction amounts with a few extremely high values. The analyst wants to reduce the impact of these outliers on the average. Which measure of central tendency is most robust?

A.Mean

B.Median

C.Mode

D.Standard deviation

AnswerB

The median is a positional measure, so extreme high transaction values shift it far less than the mean, which sums every value. This robustness to outliers satisfies the stem's requirement to reduce their impact on the average.

Why this answer

Median is not affected by extreme values, while mean is sensitive.

Practice this question →

191

MCQmedium

During exploratory data analysis, you calculate the IQR for a numeric column and find that several data points fall below Q1 - 1.5*IQR. These points are likely:

A.Normal variations within the distribution

B.The mode of the dataset

C.The median of the dataset

D.Outliers

AnswerD

Values below Q1 - 1.5*IQR sit outside Tukey's fence, the standard IQR rule for flagging extreme observations. Since the stem specifies this exact threshold, those points are classified as outliers, though they may still be legitimate values requiring investigation rather than automatic removal.

Why this answer

The 1.5×IQR rule is the standard Tukey fence for identifying outliers in exploratory data analysis. Any value below Q1 − 1.5×IQR (or above Q3 + 1.5×IQR) is flagged as a potential outlier. Since the question states points fall below Q1 − 1.5×IQR, they are by definition outliers.

Exam trap

The trap here is confusing the IQR outlier rule with measures of central tendency — candidates may pick 'mode' or 'median' because those are familiar summary statistics, but the 1.5×IQR fence is specifically the outlier detection rule.

How to eliminate wrong answers

Option A is wrong because normal variations within the distribution fall inside the fences (between Q1 − 1.5×IQR and Q3 + 1.5×IQR); points outside the fence are flagged as unusual, not normal. Option B is wrong because the mode is the most frequently occurring value and has no relationship to the IQR fence calculation. Option C is wrong because the median is the middle value (Q2) and is always inside the IQR; it cannot be identified by falling below Q1 − 1.5×IQR.

Practice this question →

192

MCQhard

A data team is ingesting JSON event data from a mobile application into a columnar warehouse. Each event has a nested array of product objects, and analysts frequently need to report on individual products within those events. The team wants to avoid repeated manual parsing in every query. Which approach best prepares the data for efficient product-level analysis?

A.Convert the entire event to a single delimited text field and split it into columns by position.

B.Keep the nested array as-is and rely on the warehouse's native semi-structured query functions for every report.

C.Flatten the nested product array into a separate table with one row per product and a foreign key back to the event, during the load process.

D.Store the raw JSON as a single string column and parse it at query time using string functions.

AnswerC

Shredding the nested array into a child table normalizes the data so each product becomes a queryable row, which columnar warehouses handle efficiently. Analysts can then join or aggregate without repeating parsing logic. This approach also supports indexing and consistent semantics, directly meeting the requirement for efficient product-level reporting.

Why this answer

The requirement is reusable, efficient product-level analysis from nested JSON. Shredding the array into a child table during load normalizes the structure, lets the columnar warehouse store each product as a row, and removes repeated parsing from reports. Storing raw JSON, querying nested arrays each time, or converting to positional text all defer or damage the preparation work.

Exam trap

The trap here is assuming modern warehouses make nested JSON queries so convenient that no load-time transformation is needed, when repeated parsing still harms performance and consistency.

Practice this question →

193

Multi-Selectmedium

Which TWO are valid data acquisition methods? (Select two.)

Select 2 answers

A.Web scraping

B.Data normalization

C.API calls

D.Data encryption

E.Data profiling

AnswersA, C

Web scraping programmatically extracts data from web pages, making it a recognised acquisition method for gathering external, publicly available datasets. It satisfies the stem's requirement for valid acquisition methods, alongside APIs, by pulling content directly from source sites.

Why this answer

Web scraping (A) is a valid data acquisition method because it programmatically extracts data from web pages, typically via HTTP requests and HTML parsing (e.g., using libraries like BeautifulSoup or Scrapy), making it a recognized way to collect data from sources that lack a formal data feed. API calls (C) are also a valid acquisition method, since they retrieve structured data directly from a service's application programming interface using protocols such as REST/HTTP or SOAP, often returning JSON or XML payloads. In contrast, data normalization (B) is a data preparation/transformation step that scales or restructures values, not a way to obtain data.

Data encryption (D) is a security control that protects data confidentiality, and data profiling (E) is an analysis technique that examines data quality and structure — neither acquires new data.

Exam trap

DA0-002 often tests the confusion between data acquisition (collecting data) and data preparation activities (normalizing, profiling, encrypting), which are downstream steps in the data pipeline.

Practice this question →

194

MCQeasy

You are using pandas in Python to clean a dataset. You notice several rows with missing values in the 'age' column. Which method would you use to remove those rows?

A.df.drop_duplicates()

B.df.dropna()

C.df.fillna(0)

D.df.isna()

AnswerB

`df.dropna()` removes every row containing any null value, directly satisfying the requirement to eliminate rows with missing 'age' entries. Its default `axis=0` and `how='any'` parameters target rows rather than columns, so the cleaned DataFrame retains only complete records without additional filtering logic.

Why this answer

The df.dropna() method is specifically designed to remove rows (or columns) that contain missing values (NaN). By default, it drops any row where at least one NaN is present, which directly addresses the requirement to remove rows with missing 'age' values. This is the standard pandas approach for handling incomplete records when deletion is preferred over imputation.

Exam trap

The trap here is confusing methods for detecting missing values (isna) with those for removing them (dropna), or mistakenly thinking fillna removes rows when it actually replaces values.

How to eliminate wrong answers

Option A is wrong because df.drop_duplicates() removes duplicate rows based on all columns (or a subset), not rows with missing values; it does not consider NaN as a criterion for removal. Option C is wrong because df.fillna(0) replaces missing values with 0 (or a specified value) rather than removing the rows, which would keep the rows but alter the data. Option D is wrong because df.isna() returns a boolean DataFrame indicating which cells are missing, but it does not modify the DataFrame or remove any rows; it is typically used for detection, not cleaning.

Practice this question →

195

MCQhard

An analyst needs to compute a running total of sales for each department, ordered by date. Which window function is most appropriate?

A.ROW_NUMBER() OVER (PARTITION BY department ORDER BY date)

B.SUM(sales) OVER (ORDER BY date)

C.SUM(sales) OVER (PARTITION BY department ORDER BY date)

D.LAG(sales, 1) OVER (PARTITION BY department ORDER BY date)

AnswerC

SUM with PARTITION BY department and ORDER BY date produces a cumulative running total within each department, because the default frame spans from the first row to the current row. Partitioning resets the accumulation per department, while the date ordering guarantees the running sequence the analyst requires.

Why this answer

The running total of sales for each department, ordered by date, requires a window function that sums sales over a partition by department and orders by date. SUM(sales) OVER (PARTITION BY department ORDER BY date) computes a cumulative sum within each department as the date progresses, which is exactly a running total.

Exam trap

DA0-002 often tests the omission of PARTITION BY in window functions, causing candidates to compute a running total across all departments instead of per department.

How to eliminate wrong answers

Option A is wrong because ROW_NUMBER() assigns a sequential integer to each row within the partition, not a running total. Option B is wrong because SUM(sales) OVER (ORDER BY date) computes a running total across the entire result set, not per department; it lacks the PARTITION BY clause. Option D is wrong because LAG(sales, 1) returns the sales value from the previous row, which is not a running total.

Practice this question →

196

MCQmedium

A data analyst is performing data profiling on a customer table. Which metric provides the number of unique values in a column?

A.Row count

B.Cardinality

C.Standard deviation

D.Null count

AnswerB

Cardinality counts the distinct values present in a column, directly satisfying the requirement for the number of unique values during data profiling. Unlike row count, which totals all records including duplicates, cardinality reveals value distribution and repetition, exposing low-variance or high-uniqueness columns that affect indexing and query planning decisions.

Why this answer

Cardinality refers to the number of distinct (unique) values in a column, which is exactly the metric described. It is a fundamental data profiling statistic used to assess column uniqueness, identify candidate keys, and inform indexing or partitioning decisions. High cardinality columns (e.g., primary keys) have many unique values; low cardinality columns (e.g., status flags) have few.

Exam trap

The trap is confusing cardinality with row count or null count — candidates often assume 'unique values' means total rows, but cardinality specifically counts distinct values, which can be far fewer than the row count.

How to eliminate wrong answers

Option A is wrong because row count measures the total number of records in the table, not the number of distinct values in a specific column. Option C is wrong because standard deviation measures the dispersion or spread of numeric values around the mean, which is unrelated to uniqueness. Option D is wrong because null count measures how many rows have missing values in a column, which is a completeness metric, not a uniqueness metric.

Practice this question →

197

Multi-Selecteasy

A data analyst is performing data acquisition from multiple source files. Which TWO data profiling tasks should the analyst complete before loading the data into the target system?

Select 2 answers

A.Create a dashboard for stakeholders

B.Verify data types and formats

C.Build a linear regression model

D.Perform cluster analysis

E.Identify missing values and nulls

AnswersB, E

Verifying data types and formats confirms each source column matches the target schema, catching mismatches such as strings in numeric fields or inconsistent date layouts before load. This satisfies the pre-load profiling requirement, preventing type-conversion failures or silent corruption during ingestion into the target system.

Why this answer

Option B (Verify data types and formats) is correct because data profiling must confirm that each source column's actual data type (e.g., integer, string, date) and format (e.g., YYYY-MM-DD, decimal separators) match what the target system expects, preventing load failures or silent corruption. Option E (Identify missing values and nulls) is correct because profiling must detect NULLs, empty strings, and placeholder values so the analyst can decide on imputation, defaulting, or rejection rules before loading. Option A (Create a dashboard for stakeholders) is a downstream reporting activity, not a pre-load profiling task.

Option C (Build a linear regression model) is predictive analytics performed after data is cleansed and loaded, not profiling. Option D (Perform cluster analysis) is an unsupervised modeling technique also done post-load, so it does not belong in pre-load data profiling.

Practice this question →

198

MCQeasy

A small business wants to acquire customer feedback through a short questionnaire emailed after purchase. Which data acquisition method does this represent?

A.Transaction log

B.Interview

C.Survey

D.Observation

AnswerC

A short questionnaire emailed after purchase is a survey: structured questions distributed to respondents for self-completion. It differs from observation, which records behaviour directly, and from interviews, which are conducted interactively. This satisfies the stem's requirement for acquiring customer feedback at scale.

Why this answer

A survey is a structured data collection method where respondents answer predefined questions, typically via a form or questionnaire. In this scenario, the business is using a short questionnaire emailed after purchase to gather customer feedback, which directly aligns with the definition of a survey as a data acquisition method.

Exam trap

The trap here is that candidates may confuse a survey with a transaction log because both can be automated and delivered electronically, but a transaction log captures system events, not user-provided feedback.

How to eliminate wrong answers

Option A is wrong because a transaction log records system-level events such as database changes, user logins, or API calls, not subjective customer feedback via a questionnaire. Option B is wrong because an interview involves a direct, synchronous conversation between an interviewer and a respondent, often with open-ended questions, whereas the scenario describes an asynchronous, self-administered questionnaire. Option D is wrong because observation involves watching and recording behavior or events without direct interaction, whereas the scenario explicitly involves asking customers for their opinions through a questionnaire.

Practice this question →

199

MCQhard

A data analyst is using a public API to collect historical weather data. The API has a rate limit of 100 requests per minute, but the analyst needs to retrieve 10,000 records as quickly as possible. What strategy should be used?

A.Increase the request rate

B.Use multiple API keys

C.Paginate with appropriate delays

D.Download a precompiled dataset

AnswerC

Pagination splits the 10,000 records into smaller result sets, while delays keep request frequency at or below the API's 100-per-minute rate limit. This satisfies the throughput constraint without triggering throttling errors, retrieving everything as quickly as the limit permits.

Why this answer

Paginating with appropriate delays respects the API's rate limit of 100 requests per minute while maximizing throughput. By splitting the 10,000 records into pages (e.g., 100 records per page) and sending requests at a rate just under the limit (e.g., one request every 0.6 seconds), the analyst can retrieve all data in approximately 100 minutes without triggering HTTP 429 rate-limit errors.

Exam trap

The trap here is that candidates may assume 'as quickly as possible' means sending requests as fast as possible (Option A) or using multiple keys (Option B), overlooking that rate limits are enforced per key or IP and that proper pagination with delays is the only compliant way to maximize throughput.

How to eliminate wrong answers

Option A is wrong because increasing the request rate beyond 100 requests per minute would violate the API's rate limit, resulting in HTTP 429 (Too Many Requests) responses or temporary IP bans. Option B is wrong because using multiple API keys to circumvent rate limits violates the API's terms of service and could lead to account suspension or revocation of access. Option D is wrong because downloading a precompiled dataset may not be available, may not contain the specific historical weather data needed, or may be outdated, and the question explicitly states the analyst is using a public API to collect data.

Practice this question →

200

MCQeasy

A data analyst is preparing a dataset for analysis and notices that the 'age' column has a significant number of missing values. The analyst decides to impute the missing values using the mean age. Which data preparation technique is being applied?

A.Data discretization

B.Data deduplication

C.Data imputation

D.Data normalization

AnswerC

Data imputation is the process of replacing missing values with substituted values. Using the mean age to fill missing entries is a common imputation method. This technique helps maintain dataset size and can reduce bias if the missingness is random, though it may underestimate variability.

Why this answer

Imputing missing values with the mean is a standard data preparation technique known as data imputation. It allows the analyst to retain records that would otherwise be excluded, enabling more complete analysis. This method is simple but should be used with caution as it can affect statistical properties.

Exam trap

The trap here is confusing imputation with normalization or other data preparation steps, but the key is that missing values are being filled with a calculated value.

Practice this question →

201

MCQhard

A data analyst is using a window function to assign a unique rank to each employee within their department based on salary, with ties receiving the same rank and leaving gaps. Which function should be used?

A.DENSE_RANK()

B.RANK()

C.ROW_NUMBER()

D.NTILE()

AnswerB

RANK() assigns identical ranks to tied salary values but skips subsequent numbers, producing gaps such as 1, 2, 2, 4. This matches the stem's requirement for ties sharing a rank while leaving gaps, unlike DENSE_RANK or ROW_NUMBER.

Why this answer

RANK() assigns a rank to each row within a partition based on the ORDER BY clause, giving tied values the same rank and then skipping subsequent ranks (e.g., 1, 2, 2, 4). This matches the requirement of 'ties receiving the same rank and leaving gaps.' DENSE_RANK() would not leave gaps, ROW_NUMBER() would not assign ties, and NTILE() divides rows into buckets rather than ranking them.

Exam trap

The trap here is confusing RANK() with DENSE_RANK(); candidates often forget that RANK() leaves gaps after ties, while DENSE_RANK() does not.

How to eliminate wrong answers

Option A is wrong because DENSE_RANK() assigns consecutive ranks without gaps after ties (e.g., 1, 2, 2, 3), which contradicts the 'leaving gaps' requirement. Option C is wrong because ROW_NUMBER() assigns a unique sequential number to every row regardless of ties, so tied salaries would receive different ranks. Option D is wrong because NTILE() distributes rows into a specified number of roughly equal groups (buckets) and does not produce a ranking with ties and gaps.

Practice this question →

202

MCQmedium

A company is merging two customer databases from different acquisitions. They need to identify duplicate records. Which data profiling technique is most effective?

A.Fuzzy matching on name and address

B.Manually compare all records

C.Exact match on customer names

D.Use primary keys from each database

AnswerA

Fuzzy matching tolerates the typos, abbreviations and formatting variations that plague merged customer records, so near-identical names and addresses still score as matches. Exact matching would miss these, and the technique directly satisfies the requirement to identify duplicate records across the two acquisitions.

Why this answer

Fuzzy matching on name and address is the most effective technique because customer databases from different acquisitions often contain variations in spelling, formatting, and abbreviations (e.g., 'Bob' vs. 'Robert', 'St.' vs. 'Street'). Exact matching would miss these duplicates, while fuzzy matching uses algorithms like Levenshtein distance or Jaro-Winkler to quantify similarity and identify near-matches, ensuring comprehensive deduplication.

Exam trap

The trap here is that candidates assume exact matching or primary keys are sufficient for deduplication, overlooking the real-world data inconsistencies that fuzzy matching is designed to handle.

How to eliminate wrong answers

Option B is wrong because manually comparing all records is impractical and error-prone for large datasets, lacking scalability and consistency. Option C is wrong because exact match on customer names fails to capture duplicates caused by typos, nicknames, or inconsistent formatting (e.g., 'Jon' vs. 'John'). Option D is wrong because primary keys from each database are unique within their own system but cannot identify cross-database duplicates, as the same customer may have different primary keys in each source.

Practice this question →

203

MCQeasy

A data analyst at a healthcare clinic is preparing a patient records dataset for analysis. The analyst discovers that the 'date_of_birth' column contains values stored as text strings in the format 'MM/DD/YYYY', but the analytics tool requires a date data type for age calculations. Which data transformation technique should the analyst apply?

A.Data aggregation

B.Data imputation

C.Data normalization

D.Data type conversion

AnswerD

Converting the text strings to a date data type allows the analytics tool to perform date arithmetic such as calculating age. This transformation changes the representation of the data to match the required format, enabling correct calculations and comparisons. It is the appropriate step when data is stored in an incompatible type.

Why this answer

The dataset stores dates as text, which prevents date arithmetic. Converting the column to a date data type is the correct transformation because it changes the underlying representation to one that supports date functions. This enables accurate age calculations and other temporal operations required by the analytics tool.

Exam trap

The trap here is confusing data type conversion with normalization or aggregation, which address different data preparation needs.

Practice this question →

204

MCQmedium

A data engineer is designing an ETL pipeline to extract sales data from a legacy on-premise database and load it into a cloud data warehouse. The database is slow and queries during business hours affect performance. Which extraction strategy minimizes impact?

A.Query the database with SELECT * every hour

B.Incremental extraction using Change Data Capture (CDC)

C.Full table extraction nightly

D.Use a database log shipping

AnswerB

Incremental extraction using Change Data Capture reads only changed rows from the transaction log, avoiding full-table scans that degrade the legacy database during business hours. This directly satisfies the stem's constraint of minimising performance impact on a slow on-premise source, while reducing data volume transferred to the cloud warehouse.

Why this answer

Incremental extraction using Change Data Capture (CDC) minimizes impact on the legacy on-premise database by reading only the changed rows (inserts, updates, deletes) from transaction logs or change tables, rather than issuing heavy SELECT queries. This avoids full table scans or frequent queries during business hours, preserving database performance for operational workloads.

Exam trap

The trap here is that candidates confuse 'log shipping' (a high-availability technique) with 'Change Data Capture' (an extraction method), or assume that any periodic query (like hourly SELECT *) is acceptable without considering the cumulative performance impact on a slow legacy database.

How to eliminate wrong answers

Option A is wrong because querying the database with SELECT * every hour performs full table scans on the legacy database, which is slow and would degrade performance during business hours, directly contradicting the goal of minimizing impact. Option C is wrong because full table extraction nightly still requires a complete scan of the entire table, which can be resource-intensive and may not complete within a reasonable window if the database is slow, and it does not capture intra-day changes without additional overhead. Option D is wrong because database log shipping is a disaster recovery technique that continuously copies transaction logs to a standby server, not an extraction strategy for ETL; it does not provide a queryable change stream and would require additional processing to parse logs for CDC.

Practice this question →

205

MCQmedium

A data analyst is performing exploratory data analysis on a dataset containing house prices. They want to identify outliers in the 'price' column using the IQR method. The first quartile (Q1) is $200,000, the third quartile (Q3) is $350,000, and the IQR is $150,000. What is the upper bound for identifying outliers?

A.$500,000

B.$575,000

C.$425,000

D.$650,000

AnswerB

The IQR method flags outliers above Q3 + 1.5 × IQR. With Q3 at $350,000 and IQR at $150,000, the calculation gives $350,000 + $225,000 = $575,000, satisfying the stem's requirement for the upper bound. Values exceeding this threshold are treated as outliers.

Why this answer

The IQR method defines the upper bound as Q3 + 1.5 × IQR. With Q3 = $350,000 and IQR = $150,000, the calculation is $350,000 + (1.5 × $150,000) = $350,000 + $225,000 = $575,000. Any price above $575,000 is considered an outlier.

Exam trap

The trap is misremembering the multiplier as 1.0 or 2.0 instead of the standard 1.5, or confusing the upper fence formula with Q3 + IQR.

How to eliminate wrong answers

Option A is wrong because $500,000 results from adding only 1.0 × IQR to Q3, which is not the standard IQR outlier threshold. Option C is wrong because $425,000 is Q3 + 0.5 × IQR, an incorrect multiplier. Option D is wrong because $650,000 is Q3 + 2.0 × IQR, which corresponds to a more extreme threshold not used in the standard Tukey IQR method.

Practice this question →

206

MCQhard

Based on the exhibit, what is the most likely cause of the import failure?

A.The file is empty or contains only headers.

B.The price field includes non-numeric characters that cannot be parsed.

C.The source file is corrupted or in an unsupported format.

D.A data quality issue: the date field contains an invalid date.

AnswerD

An invalid date value in the source field prevents the import from parsing correctly, so the load fails at the data quality stage. The exhibit's error points to a malformed date rather than a schema mismatch or connectivity fault, making the invalid date the specific cause.

Why this answer

The exhibit shows a date field containing '2023-02-30', which is an invalid date (February never has 30 days). This data quality issue causes the import to fail, as the system likely validates date values against calendar rules before inserting them into the target table. The error is not due to file emptiness, non-numeric characters, or corruption, but specifically a semantic data integrity violation.

Exam trap

The trap here is that candidates may overlook semantic data quality issues (like invalid dates) and instead focus on syntactic problems (like file format or non-numeric characters), even though the exhibit clearly shows a date that does not exist in the calendar.

How to eliminate wrong answers

Option A is wrong because the file contains multiple rows of data beyond headers, as evidenced by the visible records in the exhibit. Option B is wrong because the price field shows numeric values (e.g., 19.99, 29.99) without any non-numeric characters that would cause parsing failures. Option C is wrong because the source file is displayed in a standard CSV format with proper delimiters and readable content, indicating it is not corrupted or in an unsupported format.

Practice this question →

207

Multi-Selecteasy

A data analyst is using pandas to clean a DataFrame that contains missing values in the 'age' and 'income' columns. Which THREE pandas methods are appropriate for handling missing data? (Select THREE).

Select 3 answers

A.dropna()

B.pivot_table()

C.merge()

D.apply() with a custom function

E.fillna()

AnswersA, D, E

The `dropna()` method removes rows or columns containing null values, directly satisfying the requirement to handle missing entries in the 'age' and 'income' columns. It suits scenarios where incomplete records should be excluded entirely, though it risks discarding otherwise valid data when nulls are sparse across the DataFrame.

Why this answer

Common pandas methods for missing data include dropna (remove rows with NaN), fillna (replace NaN with a value), and apply with a custom function. Merge is for combining DataFrames; pivot_table is for reshaping.

Practice this question →

208

Multi-Selecthard

An e-commerce company wants to analyze sales performance across product categories. The dataset includes transaction amounts and a column 'category' with values (Electronics, Clothing, Home). The analyst decides to use stratified sampling to ensure proportional representation. Which THREE steps are required to implement this? (Select THREE).

Select 3 answers

A.Calculate the proportion of each category in the population

B.Take a random sample from each stratum with size proportional to its population proportion

C.Divide the dataset into three strata based on category

D.Select every 10th transaction from the entire dataset

E.Combine all categories into a single group and perform simple random sampling

AnswersA, B, C

Stratified sampling requires knowing each stratum's share of the population to allocate sample sizes proportionally. Calculating the proportion of each category establishes those weights, which the subsequent per-stratum sampling step uses to preserve the dataset's category distribution.

Why this answer

Stratified sampling requires first partitioning the population into homogeneous subgroups, so option C is correct: dividing the dataset into three strata based on the 'category' column (Electronics, Clothing, Home) creates the strata. Next, option A is correct because the analyst must calculate each category's proportion of the total population to determine how many samples each stratum should contribute for proportional representation. Option B is also correct because, after determining stratum sizes, a random sample must be drawn from each stratum with size proportional to its population proportion, which is the defining allocation step of proportional stratified sampling.

Option D is incorrect because selecting every 10th transaction describes systematic sampling, not stratified sampling, and it ignores the category strata. Option E is incorrect because merging all categories into one group and performing simple random sampling removes the stratification and does not guarantee proportional representation across categories.

Practice this question →