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

Question 1

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

Accepted Answer

WHERE. The WHERE clause is used to filter rows based on specified conditions, while the ORDER BY clause sorts the result set in ascending or descending order. Both are standard SQL clauses for data filtering and sorting, respectively.

Answer

DELETE

Answer

UPDATE

Answer

INSERT

Question 2

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 3

A data analyst sees this error in the ETL logs. What is the most likely cause?

Accepted Answer

The underlying data source was modified after the last refresh. The error indicates that the materialized view's underlying data has changed since its last refresh. Materialized views rely on change tracking (e.g., logs or timestamps) to perform incremental refreshes. If the source data was modified after the last refresh, the view cannot be incrementally refreshed—a full refresh is required instead. This mismatch between the view's snapshot and the source data is a common cause of refresh failures in ETL processes.

Answer

The source table was dropped

Answer

The analyst does not have permission to refresh the view

Answer

There is a network connection timeout

Question 4

A healthcare organization is building a data warehouse to support population health analytics. The data sources include: (1) an electronic health record (EHR) system with a relational database containing patient demographics, diagnoses, and medications; (2) a claims system that generates CSV files daily; (3) patient-generated health data from mobile apps via a REST API returning JSON. The data engineer needs to design a data acquisition process that runs nightly. The EHR system has a change tracking mechanism that logs changes with timestamps. The claims CSV files are appended daily. The API supports filtering by date. The data warehouse uses a star schema with fact and dimension tables. The engineer must ensure data consistency and minimize load times. Which approach should the engineer take?

Accepted Answer

Extract only new and changed EHR data using change tracking, extract the full claims CSV (since it's append-only), and extract API data filtered by the last extraction date. It uses incremental extraction for the EHR system (via change tracking) and the API (via date filtering), while performing a full extraction of the claims CSV since it is append-only and small enough to reload nightly. This minimizes load times by avoiding full re-extraction of large, slowly changing datasets, and ensures data consistency by capturing only new or modified records. The star schema in the data warehouse is then populated efficiently from these targeted extracts.

Answer

Perform a full extraction of all data from all sources every night and load directly into the data warehouse

Answer

Use a staging area to land all raw data first, then transform and load

Answer

Extract the EHR data using change tracking, extract the full claims CSV, and extract the API data using a full dump

Question 5

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 6

A data analyst is merging two datasets from different departments. The analyst notices that the 'CustomerID' field in the first dataset is stored as an integer, while in the second dataset it is stored as a string with leading zeros. Which TWO steps should the analyst take to ensure successful data integration?

Accepted Answer

Use a left join and treat the CustomerID as a string after conversion.. Converting the integer CustomerID to a string ensures both datasets have a compatible data type for the join. This approach preserves the leading zeros in the second dataset, which are semantically significant (e.g., '00123' vs. 123). A left join is appropriate to retain all records from the primary dataset while matching on the converted key.

Answer

Perform the merge directly without transformation since databases handle type conversions automatically.

Answer

Strip all non-numeric characters from the string CustomerID before joining.

Answer

Convert the string CustomerID to an integer by removing leading zeros.

Question 7

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 8

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 9

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 10

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 11

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 12

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.

Question 13

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 14

A company is merging two customer databases from different acquisitions. They need to identify duplicate records. Which data profiling technique is most effective?

Accepted Answer

Fuzzy matching on name and address. Fuzzy matching on name and address is the most effective technique because customer databases from different acquisitions often contain variations in spelling, formatting, and abbreviations (e.g., 'Bob' vs. 'Robert', 'St.' vs. 'Street'). Exact matching would miss these duplicates, while fuzzy matching uses algorithms like Levenshtein distance or Jaro-Winkler to quantify similarity and identify near-matches, ensuring comprehensive deduplication.

Answer

Manually compare all records

Answer

Exact match on customer names

Answer

Use primary keys from each database

Question 15

A data architect is designing an ETL pipeline to ingest streaming data from IoT sensors. The data must be available for real-time analytics. Which acquisition method is best?

Accepted Answer

Real-time streaming via API. Real-time streaming via API is the best method because IoT sensors generate continuous data that must be ingested with sub-second latency for real-time analytics. APIs (e.g., REST, WebSocket, or MQTT) enable event-driven ingestion, allowing the ETL pipeline to process each sensor reading as it arrives, which is essential for time-sensitive use cases like anomaly detection or live monitoring.

Answer

Poll sensors every hour

Answer

Manually upload sensor logs

Answer

Batch load daily CSV files