DA0-002 Data Acquisition and Preparation Practice Question
A data analyst at a retail chain is importing a CSV file into a database. The file contains a 'transaction_date' column with values like '2023-13-01' and '2023-02-30'. The target column is defined as DATE. The analyst needs to ensure that invalid dates are flagged and not loaded. Which approach best handles this data quality issue during acquisition?
⚠ Common exam trap
The trap here is assuming that format validation (like regex) is sufficient to catch invalid dates, but it cannot detect semantic errors such as month 13 or February 30.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Load the data into a staging table with the column as VARCHAR, then use a function like TRY_CAST or TO_DATE with error handling to identify invalid dates.
The correct approach is to stage the data as strings and then use database functions that attempt conversion with error handling. This allows invalid dates to be identified without failing the entire load, and only valid dates are moved to the final DATE column. This method is robust, scalable, and integrates with SQL-based ETL processes.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a Python script with the pandas library to read the CSV and apply the to_datetime function with errors='coerce' before loading into the database.
Why it's wrong here
While pandas with errors='coerce' can convert invalid dates to NaT, this approach requires an external script and does not integrate directly into the database load process. It also assumes the analyst has Python access and may not be part of the standard ETL pipeline, making it less efficient and maintainable than an in-database solution.
- ✗
Set the database column to accept NULL values and load all rows, allowing the database to automatically convert invalid dates to NULL.
Why it's wrong here
Most databases do not automatically convert invalid date strings to NULL; they typically raise an error or load a default value, which could corrupt data. Relying on automatic conversion is unreliable and may cause the entire load to fail or silently insert incorrect dates, compromising data integrity.
- ✓
Load the data into a staging table with the column as VARCHAR, then use a function like TRY_CAST or TO_DATE with error handling to identify invalid dates.
Why this is correct
Loading into a staging table with a string column allows the data to be ingested without conversion errors. Then, using a function like TRY_CAST (SQL Server) or TO_DATE with error handling (Oracle) can attempt conversion and return NULL or an error for invalid dates. This isolates invalid rows for review, ensuring only valid dates move to the final DATE column.
- ✗
Use a regular expression to validate the date format and reject rows that do not match the pattern.
Why it's wrong here
A regular expression can verify the format (e.g., YYYY-MM-DD) but cannot validate semantic correctness such as month 13 or day 30 in February. It would accept '2023-13-01' as it matches the pattern, failing to catch the actual data quality issue. Therefore, this approach does not reliably flag invalid dates.
Go deeper
Related to this question
About these practice questions
This DA0-002 question is part of Courseiva's 1,004-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official CompTIA exam blueprint
This DA0-002 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DA0-002 exam.