DA0-001 Mining and Acquiring Data — Timed 40 Questions

Question 1

A data analyst is pulling data from a production database for a report. The database contains customer orders with a column 'order_date'. The analyst notices that some orders have dates in the future. Which data quality issue does this represent?

Accepted Answer

Violation of business rules. Option D is correct because future order dates violate a business rule that order_date must be in the past or present. This is a classic data integrity issue where the data does not conform to domain-specific constraints, such as 'order_date <= CURRENT_DATE'. The analyst should flag this as a violation of business rules, not a data type or consistency problem.

Answer

Invalid data type

Answer

Inconsistent data

Answer

Missing data

Question 2

A data engineer is designing a data pipeline to ingest streaming data from IoT sensors. The sensors send data every second, and the pipeline must handle bursts of up to 10,000 messages per second. Which approach is most appropriate for capturing this data before processing?

Accepted Answer

Use a message queue to buffer the incoming data. Option C is correct because a message queue (e.g., Apache Kafka, Amazon Kinesis, or RabbitMQ) provides an asynchronous buffer that decouples the high-velocity ingestion (up to 10,000 messages/second) from downstream processing. This allows the pipeline to absorb burst traffic without overwhelming the processing layer, ensures data durability, and supports replayability in case of failures.

Answer

Directly write each message to a relational database

Answer

Load directly into a data warehouse

Answer

Store data in flat files and process in nightly batches

Question 3

A data analyst needs to combine two datasets: one contains customer information (customer_id, name, address) and the other contains order information (order_id, customer_id, order_date). The analyst wants to include all customers, even those who have not placed orders. Which type of join should be used?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table (customers) and the matching rows from the right table (orders). If a customer has no orders, the order columns will contain NULLs. This satisfies the requirement to include all customers, even those without orders.

Answer

FULL OUTER JOIN

Answer

INNER JOIN

Answer

RIGHT JOIN

Question 4

A data analyst is tasked with extracting data from a legacy system that outputs fixed-width text files. The analyst needs to parse these files into a structured format. Which tool or method is most appropriate for this task?

Accepted Answer

A scripting language such as Python. Python is the most appropriate choice because fixed-width text files require precise column slicing based on character positions, which Python's string slicing and libraries like `struct` or `pandas.read_fwf` handle natively. Unlike graphical ETL tools or spreadsheets, Python provides programmatic control to define exact field widths, handle edge cases like missing delimiters, and process large files efficiently without manual intervention.

Answer

A spreadsheet application

Answer

An ETL tool with a graphical interface

Answer

SQL

Question 5

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?

Accepted Answer

Perform data profiling to understand the ID formats and relationships. Option C is correct because 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 6

A data analyst needs to extract data from an API that returns JSON. The analyst wants to convert the JSON output into a tabular format for analysis. Which function in a scripting language is commonly used for this purpose?

Accepted Answer

json_normalize(). Option D is correct because `json_normalize()` is a function in the pandas library specifically designed to flatten semi-structured JSON data (including nested lists and dictionaries) into a tabular DataFrame. This makes it the ideal tool for converting API responses with complex nesting into rows and columns for analysis, unlike simpler JSON parsing functions.

Answer

json.loads()

Answer

to_csv()

Answer

read_json()

Question 7

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 8

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 9

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 10

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

Accepted Answer

Public APIs. Public APIs (C) are a common method for acquiring data from external sources because they provide a standardized, programmatic interface (often RESTful over HTTP/HTTPS) for requesting and receiving structured data, such as JSON or XML, from third-party services like social media platforms or weather services. Web scraping (D) is another common method that involves programmatically extracting data from web pages by parsing HTML or DOM structures, often using tools like BeautifulSoup or Selenium, when no API is available.

Answer

Data warehousing

Answer

Manual data entry

Answer

Direct database connection to an internal server

Question 11

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 implementing error handling and logging is a critical best practice in data pipeline extraction. It ensures that failures (e.g., network timeouts, authentication errors, or schema mismatches) are captured and can be diagnosed without data loss or silent corruption, which is essential for maintaining pipeline reliability and auditability.

Answer

Performing a full refresh every time

Answer

Ignoring data quality issues during extraction

Question 12

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 13

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 14

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

Accepted Answer

The materialized view log was updated after the last refresh. The error indicates that the materialized view's underlying data has changed since its last refresh, specifically because the materialized view log was updated. Materialized views rely on logs to track changes for fast refreshes; if the log is updated after the last refresh, the view's snapshot becomes stale and cannot be incrementally refreshed without a complete refresh. This is a common cause of refresh failures in Oracle databases.

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 15

A data engineer is configuring access to a data lake in Amazon S3. What does the JSON policy shown allow?

Accepted Answer

Read objects from the bucket. The JSON policy shown grants the `s3:GetObject` action, which allows reading objects from the specified S3 bucket. This is a standard AWS IAM policy that explicitly permits the `GetObject` API call, enabling the data engineer to retrieve objects from the data lake.

Answer

Change user permissions

Answer

Delete objects from the bucket

Answer

Write objects to the bucket

Question 16

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. Option B is correct because 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 17

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. Option C is correct because 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 18

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.. Option C is correct because 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 19

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.. Option D is correct because 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 20

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.. Option A is correct because 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.