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

Question 1

In a dataset of employee salaries, the analyst notices one value that is significantly higher than the rest. Using the IQR method, which values are typically considered outliers?

Accepted Answer

Values beyond Q1 - 1.5*IQR or Q3 + 1.5*IQR. Outliers are values less than Q1 - 1.5*IQR or greater than Q3 + 1.5*IQR.

Answer

Values beyond Q1 - 3*IQR or Q3 + 3*IQR

Answer

Values beyond mean ± 2 standard deviations

Answer

Values beyond min and max

Question 2

A data analyst wants to combine first_name and last_name columns into a single full_name column in a SQL query. Which string function should be used?

Accepted Answer

CONCAT(). CONCAT() joins two or more strings together.

Answer

UPPER()

Answer

LENGTH()

Answer

SUBSTRING()

Question 3

An analyst is performing EDA and wants to measure the strength and direction of linear relationship between two continuous variables. Which statistical measure should they compute?

Accepted Answer

Correlation. Correlation (typically Pearson's r for linear relationships) is the standardized measure of both the strength and direction of a linear relationship between two continuous variables, ranging from -1 to +1. A positive value indicates a positive linear association, a negative value a negative one, and the magnitude indicates strength. Standard deviation, mean, and mode are univariate descriptive statistics and say nothing about the relationship between two variables.

Answer

Standard deviation

Answer

Mean

Answer

Mode

Question 4

A data analyst is validating referential integrity between orders and customers tables. Which TWO of the following checks should the analyst perform?

Accepted Answer

Check that no customer is deleted while having orders. Referential integrity ensures that foreign key values in a child table always match a primary key value in the parent table, so option C is correct: verifying that every customer_id in orders exists in customers confirms each order references a valid customer and detects orphaned rows. Option B is also correct because preventing deletion of a customer who still has orders (or enforcing ON DELETE RESTRICT/NO ACTION or cascading appropriately) preserves the parent-child relationship and avoids orphaned orders. Option A is wrong because a non-null order_id is a primary key/entity integrity check, not referential integrity. Option D is wrong because uniqueness of customer names is a data-quality/business rule unrelated to foreign key relationships. Option E is wrong because positive order amounts are a domain or business-rule validation, not a referential integrity check.

Answer

Check that every order has a non-null order_id

Answer

Check that customer names are unique

Answer

Check that order amounts are positive

Question 5

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

Accepted Answer

Calculate the proportion of each category in the population. 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.

Answer

Select every 10th transaction from the entire dataset

Answer

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

Question 6

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

Accepted Answer

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

Answer

pivot_table()

Answer

merge()

Question 7

An analyst needs to identify outliers in a numeric column 'transaction_amount' using the interquartile range (IQR) method. Which TWO steps are part of this process? (Select TWO).

Accepted Answer

Subtract 1.5 times the IQR from Q1 and add 1.5 times the IQR to Q3 to define bounds. The IQR method requires first calculating the first quartile (Q1) and third quartile (Q3) of the numeric column, since the IQR is defined as Q3 minus Q1 — this makes option C correct. Once Q1, Q3, and the IQR are known, the outlier bounds are established by subtracting 1.5 × IQR from Q1 (lower fence) and adding 1.5 × IQR to Q3 (upper fence), so option A is correct. Values falling outside these fences are flagged as outliers. Option B is not required because the median is not used in the IQR fence calculation. Option D describes trimming extremes by percentile, which is a different technique and not part of the IQR method. Option E describes a z-score approach using mean and standard deviation, which is an alternative outlier-detection method, not the IQR method.

Answer

Calculate the median of the column

Answer

Sort the data and remove the top and bottom 5%

Answer

Compute the mean and standard deviation of the column

Question 8

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

Accepted Answer

ORDER BY price DESC. 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.

Answer

HAVING COUNT(*) > 1

Answer

WHERE price > 100

Answer

GROUP BY price

Question 9

An analyst wants to use Python (pandas) to compute the average sales amount per region from a DataFrame 'df' with columns 'region' and 'sales'. Which TWO pandas operations are needed? (Select TWO).

Accepted Answer

df.pivot_table(index='region', values='sales', aggfunc='mean'). Option B, df.pivot_table(index='region', values='sales', aggfunc='mean'), is correct because pivot_table with index='region' groups rows by region, selects the 'sales' column via values='sales', and applies the mean aggregation through aggfunc='mean', directly producing the average sales per region. Option E, df.groupby('region')['sales'].mean(), is correct because groupby('region') splits the DataFrame by region, ['sales'] selects the sales column, and .mean() computes the arithmetic average of sales within each group, yielding the same per-region averages. The other options do not compute grouped averages: A (df.fillna(0)) only replaces missing values with zero, C (df['sales'].apply(np.sqrt)) applies a square-root transformation element-wise, and D (df.merge(df2, on='region')) joins two DataFrames on the region key without any aggregation.

Answer

df.fillna(0)

Answer

df['sales'].apply(np.sqrt)

Answer

df.merge(df2, on='region')

Question 10

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?

Accepted Answer

Data type conversion. 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.

Answer

Data aggregation

Answer

Data imputation

Answer

Data normalization

Question 11

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?

Accepted Answer

Convert all product_id values to string. 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.

Answer

Apply data normalization to product_id

Answer

Convert all product_id values to integer

Answer

Use data aggregation on product_id

Question 12

A data analyst at a healthcare provider is reconciling patient records from two source systems. System A stores dates in 'MM/DD/YYYY' format, while System B stores dates in 'DD/MM/YYYY' format. During integration, the analyst notices that some records from System B have been incorrectly parsed, resulting in invalid dates. Which data preparation technique should the analyst apply to ensure consistent date interpretation?

Accepted Answer

Apply a regular expression to extract the day, month, and year components, then reassemble them in a standard 'YYYY-MM-DD' format.. The core issue is ambiguous date formats across sources. Explicitly parsing and reconstructing dates based on known source formats eliminates ambiguity. This method ensures each component is correctly identified before standardization, preventing misinterpretation that occurs when relying on default settings or automated detection.

Answer

Use a CAST function to convert the string dates to a DATE data type, relying on the database's default date format.

Answer

Convert all dates to Unix timestamps using a built-in function that automatically detects the format.

Answer

Sort the dates as strings and then manually correct any that appear out of order.

Question 13

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?

Accepted Answer

It reduces the impact of outliers by compressing the scale of large values.. 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.

Answer

It eliminates all outliers from the dataset.

Answer

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

Answer

It converts the data from continuous to categorical.

Question 14

A data analyst at a retail chain is importing a CSV file into a database. The file contains a 'transaction_date' column with values like '2023-13-01' and '2023-02-30'. The target column is defined as DATE. The analyst needs to ensure that invalid dates are flagged and not loaded. Which approach best handles this data quality issue during acquisition?

Accepted Answer

Load the data into a staging table with the column as VARCHAR, then use a function like TRY_CAST or TO_DATE with error handling to identify invalid dates.. The correct approach is to stage the data as strings and then use database functions that attempt conversion with error handling. This allows invalid dates to be identified without failing the entire load, and only valid dates are moved to the final DATE column. This method is robust, scalable, and integrates with SQL-based ETL processes.

Answer

Use a Python script with the pandas library to read the CSV and apply the to_datetime function with errors='coerce' before loading into the database.

Answer

Set the database column to accept NULL values and load all rows, allowing the database to automatically convert invalid dates to NULL.

Answer

Use a regular expression to validate the date format and reject rows that do not match the pattern.

Question 15

A data analyst is profiling a dataset of customer orders. The 'order_date' column contains dates in various formats, including 'YYYY-MM-DD', 'DD/MM/YYYY', and 'MM-DD-YYYY'. The analyst needs to standardize these dates into a single format for time-series analysis. Which approach should the analyst take?

Accepted Answer

Use a regular expression to extract year, month, and day, then reconstruct the date in a standard format.. The dates are stored in multiple formats, so a one-size-fits-all parsing function will fail. Using regular expressions to extract the year, month, and day components allows the analyst to handle each pattern systematically and then reconstruct a standardized date. This method is robust and does not depend on locale settings.

Answer

Sort the dates lexicographically and then apply a standard format.

Answer

Apply a single date parsing function with a fixed format string.

Answer

Convert the dates to Unix timestamps using a generic conversion function.