DA0-002 Practice Test 25 — 15 Questions

Question 1

A mid-sized insurance company stores policyholder records in a Microsoft SQL Server database. The compliance team must ensure that any column containing Social Security numbers is masked whenever the data is queried by analysts who are not authorized to view full identifiers. The database administrators want a solution that enforces masking at query time without altering the stored values or requiring application changes. Which SQL Server feature should they implement?

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

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking is the correct choice because it enforces masking at query time based on user permissions, leaving stored data unchanged and requiring no application modifications. It allows authorized users to see full values while unauthorized users see masked results, which precisely matches the compliance requirement for protecting Social Security numbers in the insurance database.

Answer

Row-Level Security

Answer

Always Encrypted

Answer

Transparent Data Encryption

Question 2

A manager in operations needs a real-time dashboard showing production line status, including machine uptime and error counts. Which type of report is most appropriate?

Accepted Answer

Operational report. An operational report is designed to support day-to-day monitoring of ongoing business processes, providing near real-time visibility into metrics like machine uptime and error counts. It is the correct choice for a production line dashboard that operations managers check continuously. Operational reports focus on current status rather than historical trends or deep analysis.

Answer

Analytical report

Answer

Ad hoc report

Answer

Scheduled report

Question 3

A data analyst is preparing a dataset for predictive modeling and must handle missing values in several numeric and categorical columns. The team needs defensible, documented choices rather than ad hoc deletion. Which TWO actions are appropriate for handling missing data in this scenario? (Choose two.)

Accepted Answer

Create an explicit missing-value indicator column alongside an imputed value so the model can learn from the missingness pattern.. Defensible missing-data handling starts with diagnosing the extent and pattern of missingness, then applies a treatment matched to that pattern. Combining an indicator column with an imputed value preserves both usability and the signal contained in absence. Blind mean or mode substitution, blanket row deletion, and zero-filling all introduce bias without documentation.

Answer

Impute numeric missing values with the column mean and categorical missing values with the mode, without further review.

Answer

Replace all missing numeric values with zero so the column contains no nulls and requires no further processing.

Answer

Drop every row that contains any missing value across all columns to guarantee a complete dataset.

Question 4

A retail analytics team is building a monthly dashboard that must show the running total of sales for each store from the first day of the fiscal year through the current transaction date. The sales table contains one row per transaction with columns store_id, transaction_date, and sale_amount. The analyst needs a query that returns every transaction row with an additional column showing the cumulative sales for that store up to and including that transaction. Which SQL approach should the analyst use?

Accepted Answer

Use a SUM(sale_amount) OVER (PARTITION BY store_id ORDER BY transaction_date) window expression.. A window function with PARTITION BY store_id and ORDER BY transaction_date produces a running total per store while keeping every transaction row. The other choices either collapse rows through aggregation, reverse the accumulation logic, or restrict the date range so the total is not year-to-date. The window approach is the standard, efficient way to add a cumulative column without losing detail.

Answer

Use a correlated subquery with SUM(sale_amount) and a WHERE clause that filters transaction_date to the current month only.

Answer

Use GROUP BY store_id with SUM(sale_amount) and a HAVING clause on transaction_date.

Answer

Use a self-join on store_id where the joined transaction_date is greater than or equal to the outer transaction_date, then SUM the inner sale_amount.

Question 5

A data analyst is profiling a new dataset and needs to assess data quality. Which two metrics are most appropriate for evaluating the completeness and consistency of the data? (Choose two.)

Accepted Answer

Frequency of values violating defined business rules. Completeness is assessed by the percentage of missing values, which shows how much data is absent. Consistency is assessed by the frequency of business rule violations, which reveals non-conforming data. Together, these metrics provide a clear picture of data quality issues that need addressing before analysis.

Answer

Data type mismatch count between source and target

Answer

Number of distinct values in a column

Answer

Average record length in bytes

Question 6

A financial services firm is establishing a data governance program for its customer analytics platform. The chief data officer wants to ensure that data quality issues are detected and resolved systematically. Which two of the following practices are most appropriate for maintaining data quality on an ongoing basis? (Choose two.)

Accepted Answer

Establishing a data quality issue log with defined severity levels, owners, and service-level agreements for resolution.. Automated data quality rules during ETL and a formal issue log with severity levels and SLAs together provide a systematic, repeatable approach to detecting and resolving data quality problems. The automated rules catch issues early, while the issue log ensures accountability and timely resolution, which are essential for an ongoing governance program in financial services.

Answer

Encrypting all customer data at rest and in transit to prevent unauthorized access.

Answer

Assigning a data steward to manually review a random sample of records each quarter and document findings in a spreadsheet.

Answer

Relying on business users to report data errors through ad hoc emails to the IT help desk.

Question 7

A marketing team wants to use customer email addresses collected for newsletter subscriptions to send third-party promotional offers. The data governance policy states that data must be used only for the purpose for which it was collected. Which action should the data governance team take?

Accepted Answer

Deny the use because it violates the purpose limitation principle unless new consent is obtained.. Purpose limitation is a core data governance principle: personal data should be collected for specified, explicit, and legitimate purposes and not further processed in a manner incompatible with those purposes. Using newsletter subscription emails for third-party promotions is a new purpose that requires a new lawful basis, typically explicit consent. The governance team should deny the request unless proper consent is obtained and the privacy notice is updated.

Answer

Deny the use because email addresses are considered sensitive personal data under all regulations.

Answer

Allow the use if the third-party offers are related to the newsletter content.

Answer

Allow the use because the email addresses are already in the company's possession.

Question 8

A data engineer is extracting data from a REST API that returns JSON. The API paginates results with a 'next_page' token. The engineer needs to load all pages into a database. Which approach should the engineer use to ensure all data is acquired?

Accepted Answer

Use a while loop that continues to call the API with the 'next_page' token until the token is null, parsing and loading each page.. Following the 'next_page' token in a loop until it is null is the correct way to handle API pagination. This ensures that every page is requested and processed, resulting in a complete dataset. It also respects the API's design and any rate limits.

Answer

Send parallel requests for each page number until an empty response is received.

Answer

Make a single API call with a large 'limit' parameter to retrieve all records at once.

Answer

Use the 'next_page' token as a query parameter in a single request to retrieve all subsequent pages.

Question 9

A data analyst uses linear regression to model the relationship between advertising spend and sales. The residual plot shows a clear U-shaped pattern. What assumption is violated?

Accepted Answer

Linearity. The U-shaped pattern in the residual plot indicates that the relationship between advertising spend and sales is not linear; the model fails to capture the curvature in the data. Linear regression assumes a straight-line relationship between predictors and the response, so a systematic pattern like a U-shape directly violates the linearity assumption. This means the model is misspecified and requires a transformation or a nonlinear modeling approach.

Answer

Independence of residuals

Answer

Homoscedasticity

Answer

Normality of residuals

Question 10

A financial services firm classifies its datasets into public, internal, confidential, and restricted tiers. A data engineer requests access to a restricted customer transaction dataset to build a fraud model. Which combination of controls best enforces the governance policy for this request?

Accepted Answer

Apply role-based access with least privilege, time-bound approval, and query-level audit logging. Restricted data demands defense in depth: authorization tied to a legitimate purpose, permissions scoped to the individual, a defined expiry, and records that attribute every query. Role-based access with least privilege supplies the scoping, time-bound approval removes standing access after the project, and audit logging makes usage reviewable. Controls that only document intent, share broad roles, or open masked copies each leave an enforcement gap for the highest sensitivity tier.

Answer

Grant permanent read access after the engineer signs an acceptable use policy acknowledgment

Answer

Mask all customer identifiers in the dataset and allow self-service access to the masked copy

Answer

Require manager approval, then grant access through a role shared by the entire analytics department

Question 11

An analyst receives a dataset of website sessions where the session_duration_seconds column contains several negative values and a few values exceeding 86,400 seconds. The analyst must prepare this data for analysis of average session length. Which action best addresses this data quality issue while preserving analytical integrity?

Accepted Answer

Flag the out-of-range values, investigate their source, and correct or exclude them using a documented rule before computing the average.. Negative and implausibly large session durations are invalid values that would distort an average. The sound approach is to flag them, investigate the cause, and apply a documented correction or exclusion rule. Transforming them into plausible values or ignoring them hides the defect, while retaining them uncritically treats impossible data as natural variation.

Answer

Replace all out-of-range values with the column mean so the average remains stable.

Answer

Take the absolute value of negative durations and cap all values at 86,400 seconds.

Answer

Leave the values unchanged because outliers are a natural part of real-world data.

Question 12

A data governance committee is reviewing a new analytics project that will combine customer data from a CRM system with clickstream data from a website. The committee must ensure compliance with the organization's data retention policy. Which consideration is most critical when determining the retention period for the combined dataset?

Accepted Answer

The retention period should comply with the most restrictive policy among all source datasets and any new regulatory requirements.. When datasets are combined, the resulting data must adhere to the most restrictive retention and usage policies of all sources. This is because the combination may create new privacy risks or fall under multiple regulations. The strictest policy ensures that no source's requirements are violated. Therefore, the committee should identify the shortest retention period or the most stringent regulatory mandate and apply that to the combined dataset.

Answer

The retention period should be set to match the average retention period of the source systems to simplify management.

Answer

The retention period should be based on the longest retention requirement of any source system to avoid losing data.

Answer

The retention period should be determined by the business owner of the new project without regard to source system policies.

Question 13

A data modeler is designing a dimensional model for a sales analytics system. The fact table contains sales transactions, and the dimension tables include product, customer, and time. To reduce data redundancy, the modeler normalizes the dimension tables into multiple related tables. Which schema is being implemented?

Accepted Answer

Snowflake schema. The snowflake schema is a dimensional model where dimension tables are normalized into multiple related tables to reduce data redundancy. In this scenario, the product, customer, and time dimensions are split into sub-dimensions (e.g., product category, customer geography, time hierarchy), which is the defining characteristic of a snowflake schema. This contrasts with a star schema where dimensions remain denormalized.

Answer

Vault schema

Answer

Star schema

Answer

Galaxy schema

Question 14

A data scientist is building a K-means clustering model for customer segmentation. After plotting the within-cluster sum of squares (WCSS) against the number of clusters (k), she observes that the WCSS decreases sharply until k=5 and then levels off. Which value of k should she choose based on the elbow method?

Accepted Answer

k=5. The elbow method plots WCSS against k and looks for the 'elbow' — the point where the rate of decrease sharply changes from steep to shallow. Since WCSS drops sharply until k=5 and then levels off, k=5 is the optimal choice because additional clusters beyond that yield diminishing returns.

Answer

k=6

Answer

k=4

Answer

k=3

Question 15

A financial services firm maintains a data catalog for its analytics platform. The governance team is defining the roles and responsibilities for a new data stewardship program. The chief data officer wants a single accountable person for each critical data domain, such as customer or transaction data, who approves definitions, resolves quality issues, and enforces policy. Which role should be assigned this accountability?

Accepted Answer

Data owner. Accountability for a data domain belongs to the data owner, a business leader empowered to approve definitions, set policy, and settle quality disputes. Custodians implement controls, stewards perform daily governance tasks, and analysts consume data, but none of those roles carries binding decision rights. The chief data officer needs one accountable owner per domain to make governance enforceable.

Answer

Data steward

Answer

Data analyst

Answer

Data custodian