DA0-002 Practice Test 27 — 15 Questions

Question 1

A retail company wants to analyze customer purchase patterns over time. The data is stored in a relational database with tables for Customers, Orders, and Products. Which database concept should be used to ensure that each order references a valid customer?

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

Accepted Answer

Foreign key. A foreign key constraint enforces referential integrity by ensuring that every value in the 'customer_id' column of the Orders table matches a valid primary key value in the Customers table. This prevents orphaned records and guarantees that each order references an existing customer.

Answer

View

Answer

Index

Answer

Primary key

Question 2

Which statistical test should be used to determine if there is a significant association between two categorical variables, such as gender and product preference?

Accepted Answer

Chi-square test. The chi-square test is specifically designed to test for independence between two categorical variables, such as gender (male/female) and product preference (e.g., product A/B/C). It compares observed frequencies in each category combination to the frequencies expected if the variables were independent, using the chi-square statistic. A significant result indicates that the variables are associated, not independent.

Answer

ANOVA

Answer

Pearson correlation

Answer

t-test

Question 3

A data analyst wants to find the top 5 products by total sales amount, but only for products that have been sold more than 50 times. Which SQL query accomplishes this?

Accepted Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5. HAVING filters after aggregation, then ORDER BY and LIMIT give the top 5.

Answer

SELECT product_id, SUM(sales_amount) FROM sales WHERE COUNT(*) > 50 GROUP BY product_id ORDER BY SUM(sales_amount) DESC LIMIT 5

Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id HAVING COUNT(*) > 50 ORDER BY SUM(sales_amount) ASC LIMIT 5

Answer

SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id WHERE COUNT(*) > 50 ORDER BY SUM(sales_amount) DESC LIMIT 5

Question 4

A data team is designing an ETL process to extract data from an operational database daily. The database experiences heavy write loads during business hours. What is the best practice to minimize impact on operations?

Accepted Answer

Use replication or a read replica to extract data. Extracting from a read replica or replicated copy isolates the ETL workload from the primary database that serves production transactions. This prevents the heavy read queries of the ETL process from competing with business-critical writes and locks, preserving performance for users. It is the standard best practice for minimizing operational impact while still obtaining the needed data.

Answer

Extract directly from the primary database with high priority

Answer

Run the extraction during peak hours to ensure data freshness

Answer

Schedule extraction at midnight when load is low

Question 5

An analyst is creating a report that includes multiple charts. To ensure the audience quickly grasps the key insight, which principle of data storytelling should be applied?

Accepted Answer

Use a title that states the main insight. In data storytelling, chart titles should communicate the insight, not just describe the data. A title like 'Q3 revenue dropped 12% in EMEA' tells the audience the takeaway immediately, whereas 'Q3 Revenue by Region' forces them to interpret the chart. This aligns with the principle of reducing cognitive load and leading with the message.

Answer

Avoid using titles to reduce clutter

Answer

Include a legend for every chart

Answer

Place the chart before any explanation

Question 6

A database administrator is designing a normalized database to reduce data redundancy. They have a table with columns: OrderID, ProductID, ProductName, and Quantity. The table is currently in 1NF. To move to 2NF, which issue must be resolved?

Accepted Answer

ProductName depends only on ProductID, causing a partial dependency. To move from 1NF to 2NF, the table must have no partial dependencies. A partial dependency occurs when a non-key attribute depends on only part of a composite primary key. Here, the composite key is (OrderID, ProductID). ProductName depends only on ProductID, not on the full key, so it is a partial dependency. Option A (repeating groups) is a violation of 1NF, not 2NF, and the table is already in 1NF. Option C is incorrect because Quantity depends on both OrderID and ProductID (it is fully functionally dependent on the composite key). Option D is incorrect because a transitive dependency (where a non-key attribute depends on another non-key attribute) is a 3NF issue, not 2NF. Therefore, the correct answer is B.

Answer

The table has repeating groups

Answer

Quantity depends on both OrderID and ProductID

Answer

The table has a transitive dependency

Question 7

A marketing analyst wants to predict whether a customer will churn (yes/no) based on account age and monthly charges. Which regression technique is most appropriate?

Accepted Answer

Logistic regression. Logistic regression is specifically designed for binary classification problems, such as predicting churn (yes/no). It models the probability of the outcome using a logistic function, making it appropriate for this scenario. Simple and multiple linear regression are for continuous outcomes, and K-means is for clustering, not prediction.

Answer

Simple linear regression

Answer

Multiple linear regression

Answer

K-means clustering

Question 8

An analyst uses K-means clustering on customer purchase data. After plotting the within-cluster sum of squares for different values of k, they observe an elbow at k=4. What is the most appropriate number of clusters?

Accepted Answer

4. The elbow method plots within-cluster sum of squares (WCSS) against k and looks for the 'elbow' — the point where adding more clusters yields diminishing reductions in WCSS. An elbow at k=4 means the marginal gain from k=4 to k=5 is small, so k=4 is the recommended number of clusters. Choosing k=4 balances model simplicity with fit quality.

Answer

6

Answer

5

Answer

3

Question 9

Which of the following is a key difference between a Key Performance Indicator (KPI) and a metric?

Accepted Answer

KPIs are tied to strategic objectives, while metrics are broader operational measurements. A KPI is a metric that is explicitly tied to a strategic business objective — it measures progress toward a goal the organization cares about (e.g., customer retention rate). A metric is any quantifiable measurement (e.g., page views, CPU utilization) that may or may not be strategically important. The key distinction is the linkage to strategy, not the data type or leading/lagging nature.

Answer

Metrics are always quantitative, while KPIs can be qualitative

Answer

There is no difference; the terms are interchangeable

Answer

KPIs are always lagging indicators, while metrics are leading indicators

Question 10

A data scientist is building a model to predict customer churn (yes/no). After training a logistic regression model, the coefficient for 'monthly charges' is 0.05 with a p-value of 0.03. Which interpretation is correct at α=0.05?

Accepted Answer

For every unit increase in monthly charges, the odds of churn increase by about 5%.. In logistic regression, coefficients are in log-odds units. A coefficient of 0.05 means that for a one-unit increase in monthly charges, the log-odds of churn increase by 0.05. Exponentiating gives e^0.05 ≈ 1.051, so the odds increase by about 5.1%. The p-value of 0.03 is less than α=0.05, so the effect is statistically significant.

Answer

The model's R-squared is 0.05.

Answer

Monthly charges decrease the probability of churn.

Answer

Monthly charges have no significant effect on churn.

Question 11

An organization is implementing a data warehouse to support business intelligence reporting. The data warehouse must ensure that transactions are processed reliably. Which property guarantees that each transaction is treated as a single, indivisible unit?

Accepted Answer

Atomicity. Atomicity (option C) is the correct property because it ensures that a transaction is treated as a single, indivisible unit of work. In the context of a data warehouse, this means that either all operations within the transaction are committed successfully, or none are applied, preventing partial updates that could corrupt the data. This is a core component of the ACID (Atomicity, Consistency, Isolation, Durability) model, which is fundamental to reliable transaction processing in databases like SQL Server, Oracle, or PostgreSQL.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 12

A data analyst needs to join two tables in a SQL database: Orders and Customers. The analyst wants to include all orders, even if there is no matching customer record. Which type of join should be used?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table (Orders) and the matching rows from the right table (Customers). If there is no match, NULL values are returned for the right table's columns. This satisfies the requirement to include all orders, even those without a matching customer record.

Answer

RIGHT JOIN

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Question 13

A data analyst is troubleshooting a map visualization that shows null values for some regions. Which TWO actions should the analyst take to resolve the issue?

Accepted Answer

Check that latitude and longitude fields are properly geocoded.. Option A is correct because a map visualization plots marks using geographic coordinates, so if latitude and longitude fields are missing, malformed, or not properly geocoded, the tool cannot place those regions and renders null or blank locations. Option B is correct because if the underlying data source lacks the region names (or uses names that don't match the geocoding dictionary), the map cannot resolve those regions to coordinates, again producing null values. Option C is wrong because filtering out nulls merely hides the affected regions rather than fixing the missing geographic data, so the visualization would still be incomplete. Option D is wrong because changing the mark type to Pie abandons the map entirely and does not address the geocoding or data completeness problem. Option E is wrong because removing the color encoding from profit only affects color, not the missing geographic positions that cause the null regions.

Answer

Add a filter to exclude null values.

Answer

Change the mark type from Map to Pie.

Answer

Remove the color encoding from profit.

Question 14

A data scientist builds a logistic regression model to predict customer churn (yes/no). The model outputs a probability of 0.75 for a particular customer. Which of the following best describes this output?

Accepted Answer

There is a 75% chance the customer will churn.. The output of a logistic regression model is a probability between 0 and 1. A value of 0.75 means the model estimates a 75% probability that the customer will churn (the positive class). This is a probabilistic prediction, not a certainty.

Answer

The customer will definitely churn.

Answer

The odds of churning are 0.75 to 1.

Answer

The model is 75% accurate.

Question 15

A logistics company receives GPS tracking data from fleet vehicles at 1-second intervals via a cellular network. The data is used to optimize routes and monitor driver behavior. Recently, the data acquisition system has been missing updates for some vehicles when they pass through tunnels or remote areas. The data team notices gaps during these periods. The company needs a solution to ensure near-real-time data continuity. What should they do?

Accepted Answer

Implement a store-and-forward mechanism that buffers data on the vehicle's onboard unit and uploads when connectivity resumes. A store-and-forward mechanism buffers GPS data locally on the vehicle's onboard unit during connectivity loss (e.g., in tunnels) and automatically uploads the backlog when cellular connectivity resumes. This ensures data continuity without requiring real-time transmission, directly addressing the intermittent connectivity issue while maintaining near-real-time updates.

Answer

Use a hybrid approach that combines cellular and Wi-Fi networks

Answer

Increase the frequency of data transmission to every 0.5 seconds

Answer

Switch to a satellite-based GPS system