DA0-002 Practice Test 35 — 15 Questions

Question 1

A data analyst needs to identify duplicate customer records based on email and phone number. Which SQL techniques can be used to find duplicates? (Select TWO).

Accepted Answer

Use a CTE to assign ROW_NUMBER() and then select rows where rn > 1. Option E is correct because grouping by email and phone with GROUP BY and filtering with HAVING COUNT(*) > 1 returns exactly those email/phone combinations that appear more than once, which is the standard way to detect duplicate records. Option D is correct because a CTE can compute ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) and then the outer query filters WHERE rn > 1, reliably identifying all rows beyond the first occurrence of each duplicate key. Option A is not correct because ORDER BY only sorts rows and does not detect or filter duplicates. Option B is not correct because SELECT DISTINCT removes duplicates and returns only unique combinations, the opposite of finding them. Option C is not correct because a window function cannot be referenced in the WHERE clause of the same query level, so WHERE rn > 1 would fail; the ROW_NUMBER() must be wrapped in a subquery or CTE as in option D.

Answer

SELECT email, phone FROM customers ORDER BY email, phone

Answer

SELECT DISTINCT email, phone FROM customers

Answer

SELECT email, phone, ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) AS rn FROM customers WHERE rn > 1

Question 2

A retail company wants to predict sales based on advertising spend and season. Which data modeling technique should the analyst use?

Accepted Answer

Multiple linear regression. Multiple linear regression is the correct technique because the analyst needs to model a continuous outcome (sales) based on two or more predictor variables: advertising spend (continuous) and season (categorical, typically encoded as dummy variables). This allows the model to capture the independent effect of each predictor on sales, which simple linear regression cannot do because it only handles one predictor.

Answer

Simple linear regression

Answer

Logistic regression

Answer

K-means clustering

Question 3

A business analyst creates a one-time report to analyze customer churn for a special project. Which report type is being used?

Accepted Answer

Ad hoc report. An ad hoc report is a one-time, on-demand report created to answer a specific question or support a particular project, exactly as described. It is not scheduled, not part of a recurring cycle, and is typically built quickly by a user or analyst. The phrase 'one-time report for a special project' is the textbook definition of ad hoc reporting.

Answer

Scheduled report

Answer

Self-service report

Answer

Analytical report

Question 4

A marketing manager wants to visualize the conversion funnel from website visits to final purchase. Which chart type is most appropriate?

Accepted Answer

Funnel chart. Funnel charts are specifically designed to show stages in a pipeline or conversion process, with decreasing values as prospects move through the funnel.

Answer

Treemap

Answer

Waterfall chart

Answer

Scatter plot

Question 5

A healthcare provider needs to integrate patient data from multiple clinics into a single data warehouse. Which process is used to extract, transform, and load the data?

Accepted Answer

ETL. ETL (Extract, Transform, Load) is the correct process because the healthcare provider must first extract data from multiple source clinics, then transform it (e.g., standardize formats, clean duplicates, apply business rules) before loading it into the target data warehouse. This ensures data quality and consistency, which is critical for clinical analytics and reporting.

Answer

ELT

Answer

OLAP

Answer

OLTP

Question 6

A data analyst is documenting a report for external stakeholders. Which THREE elements should be included to ensure report quality and transparency?

Accepted Answer

Data freshness (e.g., last updated timestamp). Option A (Data freshness, e.g., last updated timestamp) is correct because documenting when the data was last refreshed tells external stakeholders how current the figures are, which is essential for judging whether the report is fit for their decision-making. Option D (Limitations and assumptions) is correct because transparently stating what the analysis does not cover and what conditions it relies on prevents stakeholders from over-interpreting results and misusing them. Option E (Methodology notes) is correct because explaining how the data was collected, transformed, and calculated lets external readers verify the approach and reproduce or trust the results. Option B (Employee names who created the report) is not required for report quality and transparency; authorship metadata is optional and can even conflict with privacy or anonymity requirements. Option C (Row-level security settings) is an access-control implementation detail, not a transparency element for external stakeholders, and exposing it could reveal sensitive security configuration.

Answer

Employee names who created the report

Answer

Row-level security settings

Question 7

A data analyst needs to visualize the distribution of customer ages in a dataset. The analyst wants to see the frequency of ages grouped into bins. Which chart type should be used?

Accepted Answer

Histogram. A histogram is the correct choice because it is purpose-built for showing the frequency distribution of a continuous numeric variable by grouping values into bins (e.g., ages 18–25, 26–33). Each bar's height represents the count of observations falling into that bin, directly answering the analyst's need to see frequency by age group. Unlike a bar chart, the bars are adjacent with no gaps, reflecting the continuous nature of the underlying variable.

Answer

Pie chart

Answer

Box plot

Answer

Bar chart

Question 8

A data engineer is designing a data pipeline where raw data is loaded into a cloud data warehouse (Snowflake) and then transformed using SQL. This approach is called:

Accepted Answer

ELT. ELT (Extract, Load, Transform) loads raw data into the target warehouse first and then performs transformations using the warehouse's compute engine — exactly the pattern described with Snowflake. This leverages Snowflake's elastic compute and SQL capabilities to transform data in place, avoiding a separate transformation tier. ETL, by contrast, transforms data before loading it into the warehouse.

Answer

ETL

Answer

Data migration

Answer

Data wrangling

Question 9

An analyst needs to present quarterly sales data to the board. The CEO wants to see both overall trend and breakdown by region. Which dashboard layout is most effective?

Accepted Answer

A combination of a line chart for total and a stacked area chart for regional breakdown. It simultaneously satisfies the CEO's dual requirement: a line chart clearly shows the overall quarterly sales trend, while a stacked area chart breaks down total sales by region, allowing the board to see both the aggregate performance and the contribution of each region over time. This combination leverages the strengths of each chart type—line for trend clarity and stacked area for part-to-whole relationships—without overloading the viewer with data.

Answer

A single line chart with all regions

Answer

A KPI card with total sales

Answer

A table with all quarterly figures

Question 10

A data engineer is comparing data warehouses and data lakes. Which statement accurately describes a data warehouse?

Accepted Answer

Optimized for complex queries on structured data. A data warehouse is optimized for complex queries on structured data because it uses a schema-on-write approach, where data is cleaned, transformed, and organized into relational tables (e.g., star or snowflake schemas) before loading. This pre-processing enables efficient execution of aggregations, joins, and reporting queries using SQL, making it ideal for business intelligence and analytics. In contrast, data lakes store raw data in native formats and rely on schema-on-read, which is less performant for structured query patterns.

Answer

Typically stores data in object storage

Answer

Stores raw, unprocessed data

Answer

Uses schema-on-read

Question 11

A data analyst wants to visualize the distribution of customer ages, including quartiles and potential outliers, for a dataset with 10,000 records. Which TWO chart types are appropriate? (Choose two.)

Accepted Answer

Box plot. A box plot (A) is correct because it directly displays the five-number summary — minimum, first quartile (Q1), median, third quartile (Q3), and maximum — along with potential outliers plotted beyond the 1.5×IQR fences, which is exactly what the analyst needs for quartiles and outliers. A histogram (E) is also correct because it bins the 10,000 age values into intervals and shows the frequency distribution, revealing shape, skew, and modality of the ages. A pie chart (B) is unsuitable because it shows parts-of-a-whole proportions for categorical data, not a numeric distribution. A line chart (C) is inappropriate since it implies a continuous sequence or trend over an ordered variable such as time, not a frequency distribution. A treemap (D) displays hierarchical part-to-whole relationships using nested rectangles, which does not convey quartiles or outliers.

Answer

Pie chart

Answer

Line chart

Answer

Treemap

Question 12

Which TWO of the following are characteristics of a data lake?

Accepted Answer

Retains raw data in native format. A data lake retains raw data in its native format, meaning data is ingested without transformation or schema enforcement. This allows storage of structured, semi-structured, and unstructured data as-is, preserving fidelity for future analytics. Unlike a data warehouse, a data lake does not require upfront schema definition, enabling flexible exploration and machine learning workloads.

Answer

Optimized for OLTP

Answer

Stores only structured data

Answer

Enforces ACID transactions

Question 13

A dataset contains outliers in a feature that will be used for linear regression. Which two outlier treatment methods are appropriate? (Choose TWO)

Accepted Answer

Cap the outliers at a percentile (e.g., 99th percentile). Option A (Cap the outliers at a percentile, e.g., 99th percentile) is correct because winsorizing/capping replaces extreme values with a boundary value at a chosen percentile, which preserves the data points while reducing the leverage that extreme values exert on the least-squares fit, thereby limiting their disproportionate influence on the regression coefficients. Option D (Remove the outlier rows) is correct because deleting the offending observations eliminates their influence entirely, which is a standard and valid treatment when outliers are identified as erroneous or unrepresentative and the remaining sample is still adequate for fitting the model. Option B (Use min-max normalization) is not appropriate because rescaling features to a [0,1] range is a transformation that preserves the relative positions and leverage of outliers, so it does nothing to mitigate their effect on the regression. Option C (Increase the sample size) does not treat the outliers themselves; adding more data does not reduce the influence of existing extreme points and may even introduce more outliers. Option E (Replace outliers with the mean) is generally inappropriate because substituting the mean distorts the distribution, artificially reduces variance, and biases the regression estimates rather than properly handling the extreme values.

Answer

Use min-max normalization

Answer

Increase the sample size

Answer

Replace outliers with the mean

Question 14

In logistic regression, the output is a probability between 0 and 1. If the predicted probability for a customer churning is 0.7 and the decision threshold is 0.5, what is the predicted class?

Accepted Answer

Churn (class 1). In logistic regression, the predicted class is determined by comparing the predicted probability to the decision threshold. Here the probability is 0.7 and the threshold is 0.5; since 0.7 ≥ 0.5, the model predicts the positive class, which is churn (class 1).

Answer

Not churn (class 0)

Answer

Both classes equally likely

Answer

Uncertain, need more data

Question 15

Which measure best describes the spread of the middle 50% of a dataset?

Accepted Answer

IQR. Interquartile range (IQR) is the range between Q1 and Q3, covering the middle 50%.

Answer

Range

Answer

Standard deviation

Answer

Variance