DA0-002 Data Acquisition and Preparation Practice Test 5 — 15 Questions

Question 1

A company is merging two databases from different departments. In Database A, customer IDs are integers. In Database B, customer IDs are alphanumeric strings. To merge, the data analyst must reconcile these differences. Which step should be taken first?

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

Accepted Answer

Perform data profiling to understand the ID formats and relationships. Data profiling is the essential first step before any transformation or mapping. It allows the analyst to examine the actual formats, patterns, and relationships in both ID columns (e.g., whether Database B's alphanumeric IDs contain embedded numeric sequences or consistent prefixes). Without profiling, any conversion or mapping would be based on assumptions that could lead to data loss or incorrect merges.

Answer

Drop the ID column and use a surrogate key

Answer

Convert all IDs to integers using CAST

Answer

Create a mapping table based on the first character

Question 2

A data analyst is building a dataset from multiple sources and needs to ensure data quality. During the data acquisition phase, which activity is most important to perform?

Accepted Answer

Data profiling. Data profiling is the most important activity during the data acquisition phase because it involves examining source data to understand its structure, content, and quality issues before integration. This step identifies missing values, data types, duplicates, and inconsistencies early, preventing downstream errors in analysis. Without profiling, subsequent cleaning and modeling may be based on flawed assumptions about the data.

Answer

Data visualization

Answer

Data cleaning

Answer

Data modeling

Question 3

An organization needs to acquire data from a third-party vendor. The data will be used for regulatory reporting. Which of the following should be the primary consideration before acquiring the data?

Accepted Answer

Legal and compliance requirements. When acquiring data for regulatory reporting, legal and compliance requirements must be the primary consideration because the data must adhere to specific laws (e.g., GDPR, HIPAA, SOX) and industry regulations. Failing to ensure compliance can result in legal penalties, fines, or rejection of the report by regulatory bodies. This overrides technical or cost concerns, as non-compliant data is unusable for its intended purpose.

Answer

Volume of data

Answer

Data format

Answer

Cost of the data

Question 4

A data analyst is using SQL to extract data. The analyst wants to retrieve all records from a table named 'sales' where the 'amount' column is greater than 100. Which SQL clause should be used?

Accepted Answer

WHERE. The WHERE clause in SQL is used to filter records based on a specified condition, such as 'amount > 100'. It is applied directly to the rows in the 'sales' table before any grouping or ordering, making it the correct choice for retrieving only records where the amount exceeds 100.

Answer

ORDER BY

Answer

GROUP BY

Answer

HAVING

Question 5

Which TWO of the following are common methods for acquiring data from external sources?

Accepted Answer

Public APIs. Public APIs (C) are a standard method for acquiring external data, since they expose structured endpoints (typically REST/HTTP or GraphQL) that applications can query programmatically to pull data from third-party services. Web scraping (D) is also a common external data acquisition method, using tools such as HTTP clients and HTML parsers to extract data from public web pages when no API is available. Both are specifically designed to bring in data from outside sources, which matches the scenario. Data warehousing (A) is a storage and analytics architecture, not an acquisition method. Manual data entry (B) is a way to input data, usually from internal or human sources, not a typical external acquisition technique. Direct database connection to an internal server (E) targets internal systems, so it is not an external data source method.

Answer

Data warehousing

Answer

Manual data entry

Answer

Direct database connection to an internal server

Question 6

Which THREE of the following are best practices when performing data extraction for a data pipeline?

Accepted Answer

Implementing error handling and logging. Option B is correct because robust error handling and logging are essential in a data pipeline: they capture failures (e.g., connection timeouts, schema mismatches, partial loads) and provide an audit trail for troubleshooting and reprocessing without silently losing data. Option C is correct because documenting the extraction process records source systems, connection details, extraction frequency, filters, and transformation assumptions, which enables maintainability, reproducibility, and knowledge transfer across teams. Option E is correct because incremental extraction (e.g., using high-water marks, change data capture, or timestamp/ID deltas) reduces load on source systems and shortens pipeline runtime compared with repeatedly pulling entire datasets. Option A is not a best practice because a full refresh every time is inefficient, increases source-system load, and lengthens processing windows when only changed data is needed. Option D is not a best practice because ignoring data quality issues during extraction allows bad, missing, or malformed records to propagate downstream, corrupting analytics and requiring costly rework.

Answer

Performing a full refresh every time

Answer

Ignoring data quality issues during extraction

Question 7

Which TWO of the following are valid SQL clauses used to filter and sort data?

Accepted Answer

WHERE. Option B, WHERE, is correct because it is the SQL clause that filters rows by applying a Boolean predicate to each row before it is returned, as in SELECT ... FROM table WHERE condition. Option C, ORDER BY, is correct because it is the SQL clause that sorts the result set by one or more columns, optionally with ASC or DESC, as in SELECT ... FROM table ORDER BY column. The remaining options are not filtering or sorting clauses: DELETE (A) is a DML statement that removes rows, UPDATE (D) is a DML statement that modifies existing rows, and INSERT (E) is a DML statement that adds new rows.

Answer

DELETE

Answer

UPDATE

Answer

INSERT

Question 8

What is the primary purpose of the HAVING clause in the query shown?

Accepted Answer

Filter groups after aggregation. The HAVING clause is used to filter groups after the GROUP BY clause has aggregated the data. In SQL, WHERE filters individual rows before aggregation, while HAVING applies conditions to the results of aggregate functions like SUM, COUNT, or AVG. Option D is correct because the query uses HAVING to restrict which grouped results appear in the final output.

Answer

Sort the results in descending order

Answer

Join two tables

Answer

Filter rows before grouping

Question 9

A retail company is migrating its on-premises data warehouse to a cloud data warehouse. The current ETL process extracts data from a transactional database (SQL Server) and a web analytics system (JSON logs). The ETL runs nightly and takes 6 hours. The business requires that the new cloud warehouse support real-time reporting with data latency of less than 15 minutes. The data engineer proposes using change data capture (CDC) from the SQL Server database and streaming the JSON logs via a message queue. However, management is concerned about cost and complexity. The engineer must design a solution that meets the latency requirement while minimizing operational overhead. Which approach should the engineer recommend?

Accepted Answer

Implement CDC for the SQL Server database and stream the JSON logs via a message queue to the cloud warehouse. CDC captures only changed rows from SQL Server, minimizing data volume and enabling near-real-time ingestion, while streaming JSON logs via a message queue (e.g., Apache Kafka or Amazon Kinesis) provides sub-15-minute latency. This combination meets the latency requirement without the overhead of full batch exports or complex virtualization, addressing management's cost and complexity concerns.

Answer

Export the SQL Server data to flat files every 15 minutes and use a cloud storage trigger to load

Answer

Continue with nightly batch loads but increase the frequency to every hour

Answer

Use a data virtualization tool to query the source systems directly without moving data

Question 10

Based on the exhibit, what is the most likely cause of the import failure?

Accepted Answer

A data quality issue: the date field contains an invalid date.. The exhibit shows a date field containing '2023-02-30', which is an invalid date (February never has 30 days). This data quality issue causes the import to fail, as the system likely validates date values against calendar rules before inserting them into the target table. The error is not due to file emptiness, non-numeric characters, or corruption, but specifically a semantic data integrity violation.

Answer

The file is empty or contains only headers.

Answer

The price field includes non-numeric characters that cannot be parsed.

Answer

The source file is corrupted or in an unsupported format.

Question 11

A marketing company is building a customer segmentation model. The data team has access to two sources: a CRM database with customer demographics and purchase history, and a third-party data provider that offers social media activity scores. The CRM data is updated daily, while the third-party data is refreshed weekly on Sundays. The analyst needs to create a unified dataset for the model training scheduled for Wednesday morning. The analyst runs a SQL query to join the two tables on CustomerID, but the resulting dataset has far fewer rows than expected. Upon investigation, the analyst finds that many customers in the CRM do not have matching records in the third-party data. Additionally, some customers in the third-party data have multiple entries due to unresolved duplicates. The analyst must produce the most complete dataset possible while maintaining data quality. Which course of action should the analyst take?

Accepted Answer

First deduplicate the third-party data by keeping the most recent record per CustomerID, then perform a LEFT JOIN from CRM to the deduplicated third-party data.. It first resolves the duplicate issue in the third-party data by keeping the most recent record per CustomerID, ensuring each customer has a single, current social media score. Then, a LEFT JOIN from CRM to the deduplicated third-party data preserves all CRM customers, maximizing completeness while maintaining data quality. This approach aligns with the goal of producing the most complete dataset for model training, as the CRM is the primary source with daily updates.

Answer

Perform an INNER JOIN on CustomerID and then remove duplicates from the result.

Answer

Use only the third-party data because it provides the social media scores needed for segmentation.

Answer

Perform a LEFT JOIN from the third-party data to CRM, then aggregate duplicates by averaging scores.

Question 12

Drag and drop the steps to perform a data backup using the 3-2-1 rule in the correct order.

Accepted Answer

Create at least three copies of your data, then store the copies on at least two different types of media, then ensure at least one copy is stored offsite, then regularly test your backups.. The 3-2-1 rule involves multiple copies, different media, offsite storage, and regular testing.

Answer

Store the copies on at least two different types of media, then ensure at least one copy is stored offsite, then create at least three copies of your data, then regularly test your backups.

Answer

Create at least three copies of your data, then ensure at least one copy is stored offsite, then store the copies on at least two different types of media, then regularly test your backups.

Answer

Regularly test your backups, then create at least three copies of your data, then store the copies on at least two different types of media, then ensure at least one copy is stored offsite.

Question 13

Drag and drop the steps to perform a data audit in the correct order.

Accepted Answer

1. Inventory, 2. Quality Assessment, 3. Compliance Check, 4. Documentation, 5. Recommendations. Data audit begins with inventory, quality assessment, compliance check, documentation, and recommendations.

Answer

1. Quality Assessment, 2. Inventory, 3. Compliance Check, 4. Documentation, 5. Recommendations

Answer

1. Inventory, 2. Compliance Check, 3. Quality Assessment, 4. Documentation, 5. Recommendations

Answer

1. Inventory, 2. Quality Assessment, 3. Documentation, 4. Compliance Check, 5. Recommendations

Question 14

Match each data analysis technique to its primary purpose.

Accepted Answer

Regression: Predicting a continuous outcome variable based on one or more predictor variables. The correct matches are: Regression with predicting continuous outcomes, Clustering with grouping similar data, Classification with assigning categories, and PCA with reducing dimensionality. Common confusions include swapping regression and clustering definitions.

Answer

Regression: Grouping similar data points together

Answer

Clustering: Predicting a continuous outcome variable

Question 15

Match each database concept to its definition.

Accepted Answer

Primary Key: Uniquely identifies each record in a table.. Primary keys uniquely identify records, foreign keys link tables, indexes speed retrieval, and normalization reduces redundancy. Common confusions include swapping primary and foreign key definitions.

Answer

Primary Key: A field that links to the primary key of another table.

Answer

Foreign Key: Uniquely identifies each record in a table.