DA0-002 Practice Exam 3 — 20 Questions

Question 1

A data analyst is using a recursive CTE to traverse an organizational hierarchy. What is the purpose of the anchor member in the recursive CTE?

Accepted Answer

It provides the initial seed or starting rows for the recursion.. The anchor member initializes the recursion with the base result set.

Answer

It filters the final output of the recursive CTE.

Answer

It specifies how to join the CTE with itself recursively.

Answer

It defines the termination condition for the recursion.

Question 2

A company uses an OLTP system for processing customer transactions. Which characteristic is most important for this system to ensure that each transaction is processed reliably, even if multiple users access the system simultaneously?

Accepted Answer

It follows ACID properties. ACID properties (Atomicity, Consistency, Isolation, Durability) are the foundational guarantees that ensure each transaction in an OLTP system is processed reliably, even when multiple users access the system concurrently. Atomicity ensures all-or-nothing execution, Consistency preserves database invariants, Isolation prevents concurrent transactions from interfering with each other, and Durability guarantees committed transactions survive failures. These properties directly address the requirement for reliable transaction processing under simultaneous access.

Answer

It uses a columnar storage format

Answer

It stores data in a denormalized schema

Answer

It supports complex analytical queries

Question 3

Which database index type is most commonly used for exact-match lookups and range queries in a B-tree structure?

Accepted Answer

B-tree index. A B-tree index is the correct answer because it maintains sorted data in a balanced tree structure, enabling both exact-match lookups (via equality searches) and efficient range queries (via ordered traversal of leaf nodes). This dual capability makes it the standard index type in relational databases like MySQL, PostgreSQL, and Oracle for general-purpose querying.

Answer

Hash index

Answer

Clustered index

Answer

Bitmap index

Question 4

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 5

A healthcare analytics team is building a classification model to predict patient readmission within 30 days. The dataset contains 10,000 records with 30 features, including demographics, vital signs, lab results, and medication history. The target variable is imbalanced: 85% no readmission, 15% readmission. The team used logistic regression with default settings and achieved an accuracy of 85%, but the model predicted 'no readmission' for all patients. The lead analyst suspects the model is not learning due to class imbalance. The team has time to implement one corrective action before the next model review. Which action should the team take?

Accepted Answer

Apply SMOTE to oversample the readmission class. SMOTE (Synthetic Minority Oversampling Technique) directly addresses the class imbalance by generating synthetic samples for the minority class (readmission). This forces the logistic regression model to learn decision boundaries that separate the two classes, rather than defaulting to the majority class prediction. With 85% majority and 15% minority, accuracy alone is misleading, and SMOTE is a proven technique to improve recall for the minority class.

Answer

Remove features with low variance to reduce noise

Answer

Use accuracy as the evaluation metric to monitor improvement

Answer

Switch to a random forest model with default settings

Question 6

A data analyst needs to merge two customer tables from different sources. One table uses 'CUST_ID' as the primary key, the other uses 'CustomerID'. To ensure accurate merging, the analyst should first:

Accepted Answer

Normalize the key column names to a common format. Normalizing key column names to a common format (Option B) is the correct first step because the merge operation requires a consistent join key. Without aligning 'CUST_ID' and 'CustomerID' to a single name and data type, the database or ETL tool will treat them as different columns, resulting in a cross join or an error. This step ensures referential integrity and enables an accurate inner or outer join based on the primary key.

Answer

Perform a fuzzy match on names

Answer

Remove duplicate rows from both tables

Answer

Aggregate data by region

Question 7

A data analyst needs to combine data from two tables: one containing customer information and another containing order details. The analyst wants to include all customers, even those who have not placed any orders. Which type of join should be used?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table (customers) and matching rows from the right table (orders). If a customer has no orders, the order columns will contain NULLs. This satisfies the requirement to include all customers, even those without orders.

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Question 8

A hospital's analytics team is designing a new repository for electronic health records. The records include patient demographics, lab results, and physician notes, and the schema must remain flexible because new lab test types are added frequently. The team needs to enforce relationships between patients, encounters, and lab orders while keeping the ability to evolve the schema. Which data model best fits these requirements?

Accepted Answer

Relational model. The requirement to enforce relationships between patients, encounters, and lab orders points to a model with primary and foreign keys, which is the relational model. It also supports schema evolution through controlled migrations when new lab test types appear. Key-value, graph, and document models offer flexibility or traversal but do not natively enforce the structured relationships clinical records demand.

Answer

Document model

Answer

Key-value model

Answer

Graph model

Question 9

Drag and drop the steps to normalize a database table from 1NF to 3NF in the correct order.

Accepted Answer

Step 1: Achieve 1NF by removing repeating groups and ensuring atomic values. Step 2: Achieve 2NF by removing partial dependencies. Step 3: Achieve 3NF by removing transitive dependencies.. Normalization proceeds from 1NF to 2NF to 3NF, then table creation and foreign keys.

Answer

Step 1: Achieve 3NF by removing transitive dependencies. Step 2: Achieve 2NF by removing partial dependencies. Step 3: Achieve 1NF by removing repeating groups.

Answer

Step 1: Achieve 2NF by removing partial dependencies. Step 2: Achieve 1NF by removing repeating groups. Step 3: Achieve 3NF by removing transitive dependencies.

Answer

Step 1: Achieve 1NF by removing repeating groups. Step 2: Achieve 3NF by removing transitive dependencies. Step 3: Achieve 2NF by removing partial dependencies.

Question 10

A data analyst wants to segment customers into groups based on their purchasing behavior. The dataset includes numerical features such as annual income and purchase frequency. Which algorithm is most appropriate for this task?

Accepted Answer

K-means clustering. K-means clustering is an unsupervised learning algorithm that partitions data points into k groups based on similarity of numerical features, which is exactly what customer segmentation requires. Since the task is to discover natural groupings in unlabeled data using features like annual income and purchase frequency, K-means is the standard and most appropriate choice.

Answer

Linear regression

Answer

Logistic regression

Answer

Chi-square test

Question 11

A dashboard designer wants to maximize the data-ink ratio. Which action should they take?

Accepted Answer

Remove redundant gridlines. The data-ink ratio, a concept from Edward Tufte, is maximized by removing non-data ink — elements that do not convey information. Redundant gridlines add visual clutter without adding data value, so removing them directly increases the ratio. The other options all add non-data ink (3D effects, logos, colorful backgrounds) that reduce the ratio.

Answer

Add a 3D effect to bars

Answer

Include a company logo in the chart area

Answer

Use a colorful background

Question 12

A data engineer is designing a data pipeline to ingest streaming data from IoT sensors. The sensors send data every second, and the pipeline must handle bursts of up to 10,000 messages per second. Which approach is most appropriate for capturing this data before processing?

Accepted Answer

Use a message queue to buffer the incoming data. A message queue (e.g., Apache Kafka, Amazon Kinesis, or RabbitMQ) provides an asynchronous buffer that decouples the high-velocity ingestion (up to 10,000 messages/second) from downstream processing. This allows the pipeline to absorb burst traffic without overwhelming the processing layer, ensures data durability, and supports replayability in case of failures.

Answer

Directly write each message to a relational database

Answer

Load directly into a data warehouse

Answer

Store data in flat files and process in nightly batches

Question 13

A data analyst receives a dataset with a column 'salary' that contains values like '45,000', '55,000', and '65,000'. The analyst notices that the values are stored as text. Which data concept should be applied to convert the salary column from text to numeric format for analysis?

Accepted Answer

Data type conversion. Data type conversion is the correct concept because the salary values are stored as text (string) but need to be converted to a numeric type (e.g., integer or float) for mathematical operations like aggregation or averaging. In tools like Python (pandas `astype(float)`), SQL (`CAST(salary AS INTEGER)`), or Excel (`VALUE()` function), this explicit conversion ensures the data is treated as numbers, not strings. Without conversion, operations like `SUM` or `AVG` would fail or produce incorrect results.

Answer

Data imputation

Answer

Data validation

Answer

Data normalization

Question 14

A data analyst needs to collect customer sentiment data from social media platforms. Which data acquisition method is most appropriate?

Accepted Answer

Use web scraping. Web scraping is the most appropriate method because it allows the data analyst to programmatically extract unstructured customer sentiment data (e.g., posts, comments, reviews) directly from social media platforms using HTTP requests and HTML parsing. Unlike surveys or focus groups, scraping can collect large volumes of real-time, publicly available data without relying on self-reported or curated responses.

Answer

Conduct a survey

Answer

Organize focus groups

Answer

Query the internal CRM

Question 15

A data analyst is extracting data from a relational database using SQL. Which clause is essential for limiting the rows retrieved to only those needed?

Accepted Answer

WHERE. WHERE. The WHERE clause is used to filter rows from a table based on specified conditions, limiting the result set to only the rows that meet those conditions. Option A (GROUP BY) is incorrect because it groups rows with same values into summary rows, not for filtering. Option B (ORDER BY) is incorrect because it sorts the result set, not filters. Option D (HAVING) is incorrect because it filters groups after aggregation, not individual rows. Therefore, WHERE is essential for limiting rows retrieved.

Answer

GROUP BY

Answer

ORDER BY

Answer

HAVING

Question 16

Refer to the exhibit. A data analyst is trying to understand access permissions for the company data folder. Which statement accurately describes the effective permissions?

Accepted Answer

DataAnalyst can read objects in the production folder except those in the sensitive subfolder.. The exhibit shows an access control policy that grants the DataAnalyst user read permission on the production folder, but includes an explicit deny rule for the sensitive subfolder specified via a path condition. In most access control systems, explicit deny rules take precedence over allow rules, so the deny on the sensitive subfolder overrides the allow on the production folder, effectively blocking read access to objects in the sensitive subfolder while permitting reads elsewhere in the production folder.

Answer

DataAnalyst can read all objects in the production folder, including the sensitive subfolder.

Answer

No one can read from the production folder except DataAnalyst.

Answer

Only DataAnalyst is allowed to read from the entire production folder.

Question 17

A data analyst is performing a multiple linear regression with three predictors. The model output shows an R-squared of 0.85 and an adjusted R-squared of 0.80. Which of the following is the best interpretation of the difference between these two values?

Accepted Answer

One or more predictors may not be contributing meaningfully. R-squared (0.85) measures how much variance the model explains, while adjusted R-squared (0.80) penalizes for the number of predictors and only increases if a new predictor improves the model more than chance would predict. A notable drop between the two (0.05) suggests that at least one predictor is not contributing meaningfully and may be adding noise rather than explanatory power.

Answer

The model is overfitted, so all predictors should be removed

Answer

The model has high multicollinearity

Answer

The residuals are not normally distributed

Question 18

A company is ingesting data from multiple sources into a cloud data warehouse. They decide to load the data raw and then perform transformations within the warehouse. Which approach does this describe?

Accepted Answer

ELT. ELT (Extract, Load, Transform) loads raw data first, then transforms it inside the data warehouse, as opposed to ETL which transforms before loading.

Answer

Data lake ingestion

Answer

ETL

Answer

Stream processing

Question 19

A financial institution wants to analyze transaction networks to detect fraud rings. Which database type is best suited for this analysis?

Accepted Answer

Graph database. A graph database is designed to store and traverse relationships between entities, making it ideal for analyzing transaction networks where connections between accounts, merchants, and transactions reveal fraud rings. Its native graph model (nodes and edges) allows efficient pattern matching and pathfinding queries, such as detecting circular transactions or shared attributes, which are common in fraud detection.

Answer

Wide-column store

Answer

Key-value store

Answer

Document store

Question 20

A data analyst needs to perform a stratified random sample of a customer database. Which TWO steps are essential for this sampling method? (Select two.)

Accepted Answer

Randomly select a proportional number from each stratum. Option D is correct because stratified random sampling begins by partitioning the population into homogeneous subgroups called strata, typically based on a shared characteristic such as age, region, or customer tier, so that each subgroup is internally similar. Option C is correct because, after the strata are formed, the analyst must draw a random sample from each stratum, usually in a number proportional to that stratum's size in the population, ensuring the sample reflects the population's structure. Option A is incorrect because simple random sampling on the whole population ignores the strata and is a different sampling method. Option B is incorrect because randomly selecting entire clusters describes cluster sampling, not stratified sampling. Option E is incorrect because selecting every nth customer is systematic sampling, which does not require dividing the population into strata.

Answer

Use simple random sampling on the whole population

Answer

Randomly select entire clusters of customers

Answer

Select every nth customer from a list