DA0-002 Data Acquisition and Preparation Practice Test 17 — 15 Questions

Question 1

A data analyst is merging two datasets: one containing customer demographics with a 'customer_id' column, and another containing transaction records with a 'cust_id' column. The analyst needs to combine these datasets to analyze purchasing behavior by demographic. Which SQL join condition should be used?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

ON demographics.customer_id = transactions.cust_id. The correct join condition matches the customer identifier from the demographics table with the corresponding identifier in the transactions table, despite the different column names. This allows the analyst to combine the datasets accurately. Using the wrong column names or adding non-existent conditions would result in SQL errors and prevent the merge, so it is essential to map the keys correctly.

Answer

ON demographics.customer_id = transactions.cust_id AND demographics.customer_id = transactions.customer_id

Answer

ON demographics.cust_id = transactions.customer_id

Answer

ON demographics.customer_id = transactions.customer_id

Question 2

A data analyst is merging two datasets: one containing customer demographics with a 'customer_id' column, and another containing transaction records with a 'cust_id' column. The analyst needs to combine these datasets to analyze purchasing behavior by demographic. Which SQL join condition should be used?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The employee_id data types are different, causing implicit conversion issues.. The join fails because the employee_id values are stored differently: one as integer, the other as string with leading zeros. Even if the numeric values are the same, the string representation differs, so equality comparison fails. Converting both to a consistent type and format resolves the issue.

Answer

The employee_id column has NULL values in both tables.

Answer

The join condition is missing a necessary filter on department.

Answer

The employee_id column contains duplicate values in one of the tables.

Question 3

A data analyst is preparing a dataset for predictive modeling and must handle missing values in several numeric and categorical columns. The team needs defensible, documented choices rather than ad hoc deletion. Which TWO actions are appropriate for handling missing data in this scenario? (Choose two.)

Accepted Answer

Create an explicit missing-value indicator column alongside an imputed value so the model can learn from the missingness pattern.. Defensible missing-data handling starts with diagnosing the extent and pattern of missingness, then applies a treatment matched to that pattern. Combining an indicator column with an imputed value preserves both usability and the signal contained in absence. Blind mean or mode substitution, blanket row deletion, and zero-filling all introduce bias without documentation.

Answer

Impute numeric missing values with the column mean and categorical missing values with the mode, without further review.

Answer

Replace all missing numeric values with zero so the column contains no nulls and requires no further processing.

Answer

Drop every row that contains any missing value across all columns to guarantee a complete dataset.

Question 4

A marketing analyst is reviewing a dataset of campaign responses. The 'response' column contains values 'Yes', 'No', 'Y', 'N', 'yes', and 'no'. Before analysis, the analyst needs to count how many customers responded positively. Which data preparation step is most appropriate?

Accepted Answer

Standardize the values to 'Yes' and 'No' using case normalization and mapping.. The analyst should standardize the values to 'Yes' and 'No' by normalizing case and mapping abbreviations. This ensures all positive responses are consistently represented, enabling accurate counting and analysis. Without standardization, variations like 'Y' and 'yes' would be treated as separate categories, leading to incorrect insights.

Answer

Convert the column to uppercase to make all values consistent.

Answer

Filter out rows with 'Y' and 'N' because they are abbreviations.

Answer

Create a new binary column where 'Yes' and 'Y' are 1, and all else 0.

Question 5

A retail analytics team is building a monthly dashboard that must show the running total of sales for each store from the first day of the fiscal year through the current transaction date. The sales table contains one row per transaction with columns store_id, transaction_date, and sale_amount. The analyst needs a query that returns every transaction row with an additional column showing the cumulative sales for that store up to and including that transaction. Which SQL approach should the analyst use?

Accepted Answer

Use a SUM(sale_amount) OVER (PARTITION BY store_id ORDER BY transaction_date) window expression.. A window function with PARTITION BY store_id and ORDER BY transaction_date produces a running total per store while keeping every transaction row. The other choices either collapse rows through aggregation, reverse the accumulation logic, or restrict the date range so the total is not year-to-date. The window approach is the standard, efficient way to add a cumulative column without losing detail.

Answer

Use a correlated subquery with SUM(sale_amount) and a WHERE clause that filters transaction_date to the current month only.

Answer

Use GROUP BY store_id with SUM(sale_amount) and a HAVING clause on transaction_date.

Answer

Use a self-join on store_id where the joined transaction_date is greater than or equal to the outer transaction_date, then SUM the inner sale_amount.

Question 6

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.)

Accepted Answer

Frequency of values violating defined business rules. 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.

Answer

Data type mismatch count between source and target

Answer

Number of distinct values in a column

Answer

Average record length in bytes

Question 7

A data engineer is acquiring semi-structured JSON event logs from a mobile application. Each event contains nested objects and arrays, and some fields are missing depending on event type. Before loading into a relational warehouse, the engineer must flatten and validate the data. Which TWO acquisition practices are most appropriate for this semi-structured source? (Choose two.)

Accepted Answer

Validate required fields, data types, and allowed enumerations during ingestion, and quarantine records that fail validation for later review.. Semi-structured acquisition works best when the pipeline imposes a governed target schema and validates records at ingestion. Mapping nested paths to flat, typed columns with explicit null handling makes missing fields intentional, while validation with quarantine protects the warehouse from malformed data and schema drift. Deferring parsing to reports, discarding optional fields, or auto-inferring schemas all trade short-term convenience for long-term inconsistency and lost information.

Answer

Store each raw JSON document as a single text column and defer all parsing to report-level SQL using string functions.

Answer

Allow the ingestion job to infer types from each batch and create new columns automatically whenever an unfamiliar key appears.

Answer

Load only the fields that are present in every event type and discard optional fields to guarantee a uniform structure.

Question 8

A data analyst at a retail bank is preparing a daily transaction file for loading into the analytics warehouse. The source system exports dates in the format 'MM/DD/YYYY', but the target warehouse requires ISO 8601 format 'YYYY-MM-DD'. The analyst needs to transform the date column without changing the underlying date value. Which transformation should the analyst perform?

Accepted Answer

Use a date parsing and formatting function to interpret MM/DD/YYYY and output YYYY-MM-DD.. Converting a date representation while preserving the value is a standard data transformation task. Using a native parse-and-format function with an explicit input format ensures the calendar date is interpreted correctly and emitted as ISO 8601. Regex reordering, source-system changes, or untyped strings either risk invalid dates, affect unrelated systems, or defer correctness to query time, none of which meets the warehouse loading requirement cleanly.

Answer

Apply a regular expression to reorder the month, day, and year components into YYYY-MM-DD.

Answer

Change the source system's regional date setting so exports use ISO 8601.

Answer

Store the date column as a string and let the warehouse interpret the format at query time.

Question 9

A data analyst is profiling a dataset and notices that the 'age' column contains negative values and values exceeding 120. The analyst needs to address these anomalies. Which data preparation technique is most appropriate?

Accepted Answer

Outlier detection and treatment. The most appropriate technique is outlier detection and treatment because negative ages and ages over 120 are statistically implausible and likely errors. This method identifies and corrects or removes such values, ensuring the dataset's integrity. Other techniques like imputation or normalization do not address the underlying invalidity of the data.

Answer

Data aggregation

Answer

Imputation

Answer

Normalization

Question 10

A data engineer is profiling a newly acquired customer table before loading it into a warehouse. The table has a customer_id column that should be unique, a signup_date column stored as text in 'YYYY-MM-DD' format, and a country column with values such as 'US', 'USA', 'United States', and 'U.S.'. The engineer must document which data quality dimensions are violated and plan remediation. Which TWO actions best address the identified data quality issues? (Choose two.)

Accepted Answer

Standardize the country values to a single reference list using a mapping table or CASE expression.. The profile reveals a consistency problem in the country column and a type-conformance problem in the signup_date column. Standardizing country values and converting the date text to a native date type both directly remediate identified defects. Deleting duplicate IDs, ignoring inconsistent labels, or fabricating missing dates either destroys valid data or hides problems instead of resolving them.

Answer

Impute missing signup_date values with the current date to avoid nulls in the column.

Answer

Delete all rows where the customer_id appears more than once to enforce uniqueness.

Answer

Leave the country values as-is because they all refer to the same country and analysts can interpret them.

Question 11

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?

Accepted Answer

Binning. 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.

Answer

One-hot encoding

Answer

Normalization

Answer

Imputation

Question 12

A data engineer is extracting data from a REST API that returns JSON. The API paginates results with a 'next_page' token. The engineer needs to load all pages into a database. Which approach should the engineer use to ensure all data is acquired?

Accepted Answer

Use a while loop that continues to call the API with the 'next_page' token until the token is null, parsing and loading each page.. Following the 'next_page' token in a loop until it is null is the correct way to handle API pagination. This ensures that every page is requested and processed, resulting in a complete dataset. It also respects the API's design and any rate limits.

Answer

Send parallel requests for each page number until an empty response is received.

Answer

Make a single API call with a large 'limit' parameter to retrieve all records at once.

Answer

Use the 'next_page' token as a query parameter in a single request to retrieve all subsequent pages.

Question 13

An analyst is profiling a newly acquired customer table and wants a quick summary of the central tendency and spread of the 'annual_income' column, which contains a few extreme outliers from data entry errors. Which combination of descriptive statistics is most appropriate to report the typical income while limiting the influence of those outliers?

Accepted Answer

Median and interquartile range. For skewed data with known outliers, robust statistics are preferred. The median resists extreme values and represents the middle of the distribution, while the interquartile range measures spread using the middle 50 percent of records. Mean and standard deviation, mode and range, and sum and count either amplify outlier influence or fail to describe central tendency and dispersion together.

Answer

Sum and count

Answer

Mean and standard deviation

Answer

Mode and range

Question 14

A healthcare analytics team is ingesting a nightly CSV extract of patient encounters. The extract occasionally contains rows where the 'discharge_date' is earlier than the 'admission_date'. The team wants the pipeline to flag these rows for review rather than silently load them. Which data preparation action best addresses this requirement?

Accepted Answer

Apply a validation rule that compares admission_date and discharge_date and routes failing rows to a quarantine table with a reason code.. The requirement is to identify and isolate suspect records for human review. A validation rule comparing the two dates and routing failures to a quarantine table with a reason code achieves this without deleting data or contaminating the trusted layer. Dropping, passing through, or auto-correcting records all fail because they either lose information, hide the problem, or make unverified changes to clinical data.

Answer

Swap the values in admission_date and discharge_date whenever discharge_date is earlier.

Answer

Load the rows as-is and rely on downstream report filters to exclude invalid dates.

Answer

Drop all rows where discharge_date is earlier than admission_date before loading.

Question 15

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?

Accepted Answer

Parse the file using fixed character positions derived from a documented layout specification rather than splitting on spaces.. 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.

Answer

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

Answer

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

Answer

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