CompTIA Data+ DA0-002 (V2) Practice Question

During the weekly data-load process, a junior data analyst runs a SQL view that casts the column quantity_sold to INT. This week the script fails and returns the runtime error:

Conversion failed when converting the varchar value 'N/A' to data type int.

The schema of the staging and target tables has not changed since the previous successful load. Which action should the analyst take first to troubleshoot the issue and prevent it from happening in future loads?

Increase the database server's memory allocation so the CAST operation can complete in memory.
Enable detailed query-plan logging on the database server to capture the statement's execution plan.
Rewrite the view to use a FULL OUTER JOIN instead of an INNER JOIN to eliminate rows with nulls.
Validate the source file and cleanse any non-numeric values in quantity_sold before loading the staging table.

Report Issue

Answer Description

The error indicates that at least one row in quantity_sold contains a non-numeric string ("N/A"), so SQL Server cannot implicitly convert the value to an integer. According to data-validation best practices, the analyst should verify and cleanse the source data before it is loaded or cast. By validating the incoming extract and filtering or correcting non-numeric values, the analyst removes the root cause of the conversion failure and prevents the error from recurring.

Enabling detailed query logging would show the failing statement but would not fix the data quality problem.
Increasing server memory does not address the data-type mismatch.
Rewriting the view with a different join type does not change the fact that quantity_sold contains invalid characters.

Therefore, validating and cleansing the source data is the most appropriate first step.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.