DA0-002 Data Acquisition and Preparation — 50 Questions

Question 1

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. Based on the correct answer, the JSON policy shown implicitly grants the `s3:GetObject` action, which allows reading objects from the specified S3 bucket. In AWS IAM, reading objects requires explicit `GetObject` permission, making B the only correct choice.

Answer

Change user permissions

Answer

Delete objects from the bucket

Answer

Write objects to the bucket

Question 2

Which THREE are common challenges when acquiring data from external APIs? (Choose three.)

Accepted Answer

Authentication requirements. Authentication requirements are a common challenge because external APIs typically require valid credentials (e.g., API keys, OAuth 2.0 tokens, or JWT) to access protected resources. Without proper authentication, requests are rejected with HTTP 401 Unauthorized or 403 Forbidden errors, and managing token expiration, refresh cycles, and secure storage adds significant complexity to data acquisition pipelines.

Answer

Consistent data schemas

Answer

Guaranteed uptime

Question 3

Refer to the exhibit. A data engineer is setting up data acquisition from a cloud storage bucket with this policy. What does the policy enforce?

Accepted Answer

Access is restricted to a specific IP range. The bucket policy includes a condition using a source IP address with a CIDR block, which restricts access to requests originating from the specified IP range. This enforces network-level access control, allowing only clients within that IP range to perform the allowed actions (like GET requests) on the bucket. Option D correctly identifies this IP-based restriction.

Answer

Only objects with key 'data-bucket' can be accessed

Answer

Only GET requests are allowed

Answer

The storage container is publicly accessible

Question 4

A data analyst needs to count the number of customers who have placed at least one order. Which SQL query should be used?

Accepted Answer

SELECT COUNT(DISTINCT customer_id) FROM orders. COUNT(DISTINCT customer_id) returns the number of unique customers who have placed orders, correctly counting those with at least one order. Option B sums customer IDs, which is not a valid count. Option A uses invalid syntax. Option C counts all orders including duplicates.

Answer

SELECT DISTINCT COUNT(customer_id) FROM orders

Answer

SELECT SUM(customer_id) FROM orders

Answer

SELECT COUNT(customer_id) FROM orders

Question 5

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. 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 6

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. 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 7

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 8

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 9

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. 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 10

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(). `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 11

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 12

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 13

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 14

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 15

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

Accepted Answer

Implementing error handling and logging. 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 16

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 17

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 18

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 19

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 20

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