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

Question 1

A retail analytics team loads a nightly CSV export into their warehouse. During validation, the analyst notices that the 'order_date' column, defined as DATE in the target schema, contains values like '2023-13-45' and 'N/A' in several rows. The ETL job currently fails silently on these rows. Which data acquisition and preparation action BEST addresses the root cause while preserving as much data as possible?

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

Accepted Answer

Apply validation rules that flag invalid dates for quarantine while loading conforming rows, and log rejected records.. Validating at ingestion, quarantining malformed dates, and logging rejects keeps the pipeline resilient while surfacing the true defect. It loads every trustworthy row, gives the team evidence to fix the upstream export, and stops the silent-failure pattern. Changing types or imputing values merely relocates or amplifies the error.

Answer

Change the target column type from DATE to VARCHAR so all incoming values load without error.

Answer

Reject the entire nightly file and request a corrected export from the source system.

Answer

Impute today's date for any row where the order_date value cannot be parsed.

Question 2

A data analyst is preparing a dataset for analysis and discovers that the 'customer_id' column has missing values. The analyst decides to remove all rows with missing 'customer_id' because it is a primary key. Which data preparation technique is being applied?

Accepted Answer

Listwise deletion. Listwise deletion is the correct technique because it involves removing records with missing values. Since customer_id is a primary key, missing values cannot be reliably filled, so deleting those rows is a standard data cleaning step. This ensures that each record has a valid unique identifier, which is essential for relational integrity and accurate analysis.

Answer

Discretization

Answer

Normalization

Answer

Imputation

Question 3

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?

Accepted Answer

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

Answer

Uniqueness

Answer

Completeness

Answer

Consistency

Question 4

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?

Accepted Answer

LEFT JOIN. 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.

Answer

CROSS JOIN

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Question 5

A data analyst is working with a dataset that contains a 'salary' column with extreme outliers. Before performing a linear regression analysis, the analyst wants to reduce the impact of these outliers. Which technique should be applied?

Accepted Answer

Winsorizing. Winsorizing is the correct technique because it caps extreme values at a specified percentile, reducing their influence while retaining all data points. For linear regression, outliers can disproportionately affect the slope and intercept, so limiting their impact is crucial. Winsorizing is preferable to deletion when sample size is limited, and it preserves the order of values, making it a robust choice for this scenario.

Answer

One-hot encoding

Answer

Standardization

Answer

Binning

Question 6

A data engineer is ingesting JSON data from an IoT sensor network. The JSON records contain nested arrays and objects. The engineer needs to flatten the structure to load it into a relational table. Which approach is most appropriate?

Accepted Answer

Use a SQL function like OPENJSON or JSON_TABLE to shred the JSON and return a relational rowset.. The most appropriate method is to use a dedicated JSON shredding function like OPENJSON or JSON_TABLE. These functions are built to parse nested JSON and output a relational rowset, handling arrays and objects efficiently. They integrate with SQL queries and allow joining and filtering, making them ideal for loading into relational tables.

Answer

Use a JSON parsing function to extract each field into separate columns, manually specifying the path for each nested element.

Answer

Store the entire JSON as a single string column and parse it during analysis using string functions.

Answer

Convert the JSON to XML first, then use XML parsing functions to extract data.

Question 7

A data analyst is preparing a dataset for analysis and needs to combine data from two tables: 'orders' and 'customers'. The 'orders' table contains a 'customer_id' column, and the 'customers' table contains a 'customer_id' column as the primary key. The analyst wants to include all orders, even those without matching customers. Which type of join should be used?

Accepted Answer

LEFT JOIN. To preserve all rows from the orders table regardless of matching customers, a LEFT JOIN is appropriate. It keeps every order and appends customer details when available. Other join types either exclude unmatched orders or include extra unmatched customers, which is not desired.

Answer

RIGHT JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 8

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?

Accepted Answer

Flatten the top-level fields into typed columns, extract the nested arrays into a separate child table, and partition or cluster on event_timestamp.. 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.

Answer

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

Answer

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

Answer

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

Question 9

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?

Accepted Answer

Split the file into multiple smaller files and load them in parallel.. 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.

Answer

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

Answer

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

Answer

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

Question 10

A data analyst is assessing the quality of a newly acquired dataset from an external source. The analyst needs to evaluate two dimensions of data quality that directly affect the dataset's fitness for use in analysis. Which two dimensions should the analyst prioritize? (Choose two.)

Accepted Answer

Completeness. Completeness and accuracy are fundamental data quality dimensions that directly affect the validity of analysis. Completeness ensures no critical data is missing, while accuracy ensures the data correctly represents reality. These two dimensions are essential when evaluating an external dataset because they determine whether the data can be trusted for decision-making. Other dimensions like accessibility, portability, and scalability are more about operational aspects.

Answer

Accessibility

Answer

Portability

Answer

Scalability

Question 11

A data analyst is integrating data from two source systems into a single customer dataset. Source A uses a customer ID format like 'CUST-12345', while Source B uses '12345'. Additionally, Source A records dates in 'MM/DD/YYYY' format, while Source B uses 'YYYY-MM-DD'. Which two data preparation tasks are essential to ensure the integrated dataset is consistent and usable? (Choose two.)

Accepted Answer

Convert all date values to a single standardized format.. The key challenges are inconsistent customer ID formats and date formats across sources. Standardizing both ensures records can be matched and time-based analysis is accurate. Encryption, missing value removal, and aggregation do not directly solve these format inconsistencies and may introduce other issues.

Answer

Apply encryption to all customer ID fields.

Answer

Remove all records with missing values.

Answer

Aggregate the data by customer to reduce row count.

Question 12

While profiling a customer dataset, an analyst finds that the 'country' column contains values including 'USA', 'United States', 'U.S.A.', and 'US' for the same nation, plus 'usa ' with trailing whitespace. Reports grouped by country show fragmented counts. Which preparation step resolves this issue?

Accepted Answer

Apply standardization by trimming whitespace and mapping all variants to a single canonical country code.. The column is not missing data; it holds the same nation under several spellings and spacing. Standardizing with trimming plus a canonical mapping such as an ISO code unifies those variants so grouped counts consolidate correctly. Deleting rows, widening the column, or imputing a single value either destroys data or leaves the fragmentation untouched.

Answer

Impute the mode of the column for every row to make all values identical.

Answer

Remove all rows containing any country value that is not 'USA' to enforce consistency.

Answer

Increase the column length to accommodate the longest variant string.

Question 13

A data engineer is profiling a dataset of customer orders and notices that the 'order_date' column contains values in multiple formats: 'YYYY-MM-DD', 'MM/DD/YYYY', and 'DD-Mon-YYYY'. The column is currently stored as a string. Which action should the engineer take to ensure consistent date handling for analysis?

Accepted Answer

Parse each format and load the dates into a new column with a consistent DATE data type.. The engineer should parse each date format and load the values into a new column with a consistent DATE data type. This standardizes the data at the storage level, ensuring all analytical queries and tools interpret dates uniformly. It also allows the use of native date functions and avoids repeated conversion logic in every query.

Answer

Use a CASE statement to convert each format to a standard date type during queries.

Answer

Replace all delimiters with hyphens to make the strings look uniform.

Answer

Leave the column as string and rely on the BI tool to interpret the formats.

Question 14

A retail analytics team is acquiring point-of-sale transaction files from 40 different store locations. Each store exports its nightly file with a different column order and slightly different header names (for example, 'TxnID' vs 'Transaction_ID' vs 'TransNo'). The analyst must load all files into a single normalized staging table before transformation. Which data preparation approach best addresses this inconsistency at acquisition time?

Accepted Answer

Create a source-to-target mapping for each store's file layout and apply a schema-on-read extraction that renames and reorders columns into a canonical staging schema.. The core issue is structural inconsistency across many source files, so the fix belongs at the acquisition boundary: define a canonical staging schema and map each source layout into it. Schema-on-read mapping handles renamed and reordered columns without changing upstream systems or delaying ingestion, and it creates documented lineage. Approaches that defer reconciliation to queries or force upstream re-exports either spread complexity or block progress.

Answer

Concatenate all raw files into one text file and parse columns positionally using the order from the largest store.

Answer

Instruct each store to re-export its file using the corporate header standard before the nightly transfer begins.

Answer

Load each file as-is into separate tables and let the BI tool join them by matching column names at query time.

Question 15

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?

Accepted Answer

Data imputation. 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.

Answer

Data discretization

Answer

Data deduplication

Answer

Data normalization