DA0-002 Data Acquisition and Preparation — 40 Questions

Question 1

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?

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

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 2

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 3

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

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

Accepted Answer

Authentication requirements. Authentication requirements (A) are a common challenge because external APIs typically enforce OAuth 2.0 tokens, API keys, or signed requests, and credential handling, token refresh, and scope management must be implemented correctly before any data can be pulled. Rate limiting (C) is also a standard obstacle, since providers cap requests per second/minute/day (often returning HTTP 429 with Retry-After headers), forcing throttling, backoff, and batching strategies. Data volume constraints (E) are a real challenge too, as APIs impose pagination limits, maximum page sizes, result caps, and payload/timeout limits that require incremental extraction and pagination handling. Consistent data schemas (B) is not marked correct because schema variability is more typically associated with internal source systems or file-based feeds than with the defining acquisition challenges of external APIs. Guaranteed uptime (D) is not marked correct because no external API offers guaranteed uptime, and availability concerns, while real, are not one of the three challenges identified here.

Answer

Consistent data schemas

Answer

Guaranteed uptime

Question 7

Refer to the exhibit. An analyst runs this query before acquiring data from a PostgreSQL database. What is the primary purpose of this query?

Accepted Answer

To check for data freshness. The query shown in the exhibit retrieves the `last_analyzed` column from PostgreSQL's system catalog, which records when table statistics were last updated. Analyzing this timestamp helps the analyst determine if the table's data is fresh enough for the intended analysis, thus checking data freshness before acquisition.

Answer

To verify data types

Answer

To find primary keys

Answer

To identify duplicate tables

Question 8

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 9

Refer to the exhibit. An analyst sees this log during data acquisition. What action should be taken first?

Accepted Answer

Modify the ETL mapping for data types. The log shows a data type mismatch during ETL (Extract, Transform, Load) processing, where a date field is being read as a string. The correct first action is to modify the ETL mapping for data types to ensure the date string is properly cast or converted to the target date format, preventing data loss or corruption. This aligns with standard data acquisition best practices: adjust the transformation layer to handle source data anomalies before rejecting or altering the source.

Answer

Reject the entire dataset

Answer

Ignore warnings and continue

Answer

Correct the date string in the source

Question 10

During a data mining project, an analyst discovers that a significant number of records have a negative value for the age field. What is the most appropriate first step?

Accepted Answer

Investigate the source system for data entry errors.. The first step in handling anomalous data like negative ages is to investigate the source system for data entry errors. This aligns with the data mining process, where understanding the root cause of data quality issues is critical before applying any imputation or removal techniques. Without investigation, you risk masking systemic problems that could affect all records.

Answer

Impute using regression.

Answer

Replace negative age with the mean age.

Answer

Remove all records with negative age.

Question 11

Which THREE data sources are suitable for web scraping? (Select three.)

Accepted Answer

HTML pages. HTML pages (A) are the canonical web-scraping target: an HTTP client fetches the markup and a parser (e.g., BeautifulSoup, lxml, or a headless browser) extracts elements via CSS selectors or XPath. JSON APIs (B) are also suitable because scrapers can issue HTTP requests to REST endpoints and deserialize the JSON payload directly, often yielding cleaner structured data than HTML. PDF documents (E) are scrapable too, since tools like pdfplumber, PyPDF2, or OCR pipelines (Tesseract) can extract text and tables from them when no API or HTML source exists. CSV files (C) are not a web-scraping source; they are flat files typically downloaded or read locally with pandas/csv, not parsed from web page structure. Database connections (D) are direct data-access channels using drivers and SQL, not web scraping, which by definition retrieves data over HTTP from web resources.

Answer

CSV files

Answer

Database connections

Question 12

A financial institution is merging transaction data from two different systems. System A stores currency amounts as integers in cents, and System B stores as decimals in dollars. What is the best way to integrate the data?

Accepted Answer

Convert System A amounts to dollars by dividing by 100.. Converting System A's integer cents to dollars by dividing by 100 ensures both datasets share a consistent unit (dollars) and numeric data type (decimal). This direct transformation eliminates ambiguity in aggregation and reporting, as financial calculations require uniform precision and scale. Using a transformation layer or storing as strings would introduce unnecessary complexity or risk of rounding errors.

Answer

Keep both as is and use a transformation layer.

Answer

Store all amounts as strings to preserve precision.

Answer

Convert System B amounts to cents by multiplying by 100.

Question 13

A data team is integrating customer data from three sources. After joining, they find that the count of unique customers is lower than expected. What is the most likely cause?

Accepted Answer

Inconsistent key definitions.. When joining customer data from multiple sources, inconsistent key definitions (e.g., one source uses integer IDs while another uses string IDs, or different formats like 'CUST-001' vs '1001') cause the join to fail to match records that actually represent the same customer. This results in fewer unique customers than expected because the join treats mismatched keys as different entities, effectively dropping or misaligning records. The data team likely used an inner join or a left join that only retains matches based on exact key equality, so any key inconsistency reduces the count of matched unique customers.

Answer

Missing values in join keys.

Answer

Data truncation during transfer.

Answer

Duplicate entries across sources.

Question 14

A data analyst is tasked with collecting data from a web API that returns JSON. The API requires an API key in the header. Which method should be used to authenticate?

Accepted Answer

Add the API key in the HTTP header as 'Authorization: Bearer '. The correct method is to include the API key in the HTTP header using the 'Authorization: Bearer ' format. This is the standard approach for token-based authentication in REST APIs, as defined by RFC 6750. It keeps the credential out of URLs and logs, and is the expected mechanism for API key authentication in modern web APIs.

Answer

Use a session cookie

Answer

Store the API key in the database and reference it

Answer

Include the API key in the URL query string

Question 15

A data engineer is tasked with acquiring data from a third-party vendor that provides daily file drops via SFTP. The files are large (10 GB each). The pipeline must load data into a data warehouse. Which approach optimizes for speed and reliability?

Accepted Answer

Stream the file directly from SFTP into warehouse using a data pipeline tool. Streaming the file directly from SFTP into the warehouse using a data pipeline tool (e.g., Apache NiFi, Airbyte, or Fivetran) eliminates the intermediate staging step, reducing disk I/O and latency. This approach leverages incremental processing and parallel streams to handle large 10 GB files efficiently, while built-in retry and checkpoint mechanisms ensure reliability against network interruptions.

Answer

Download the file to a staging server, then bulk insert into warehouse

Answer

Have the vendor push data via API instead of SFTP

Answer

Split the file into smaller chunks and load concurrently

Question 16

Which TWO are examples of internal data sources? (Select exactly 2)

Accepted Answer

Relational databases. Relational databases (B) are a classic internal data source because they store an organization's own structured operational data, such as customer records, transactions, and inventory, held within its systems. Flat files (E) are also internal sources, since files like CSV, TSV, or log files are typically generated and stored inside the organization's own infrastructure. APIs (A) are not inherently internal — they are interfaces that often expose external or third-party data, so they are not an example of an internal source. Sensor readings (C) usually come from IoT or edge devices that are external to the core enterprise systems, and social media comments (D) are clearly external data generated by third parties on public platforms.

Answer

APIs

Answer

Sensor readings

Answer

Social media comments

Question 17

Refer to the exhibit. What is the most likely issue causing the unexpectedly low count?

Accepted Answer

The signup_date column is in a different date format. The most likely cause of an unexpectedly low count is that the signup_date column is stored in a different date format than the one used in the query filter, so the comparison silently excludes rows that should match. When date formats differ (for example, 'YYYY-MM-DD' versus 'MM/DD/YYYY'), the filter predicate fails to match valid records, producing a lower-than-expected count.

Answer

The customers table is indexed incorrectly

Answer

The query is missing a GROUP BY clause

Answer

The database was not refreshed

Question 18

Refer to the exhibit. What is the most likely cause of the extraction failure?

Accepted Answer

The network firewall is blocking the port. The exhibit likely shows a connection timeout or 'unable to connect' error when attempting to extract data from a remote database. Such errors typically occur when the network port used by the database (e.g., 3306 for MySQL, 5432 for PostgreSQL, 1433 for SQL Server) is blocked by a firewall. Since the extraction process fails to establish a connection, the most likely cause is that the firewall is preventing communication on that port. Other options like a locked table or complex query would produce different errors (e.g., lock wait timeout or query execution time exceeded), and a down database server would typically result in a connection refused error rather than a timeout.

Answer

The source table is locked

Answer

The extraction query is too complex

Answer

The database server is down

Question 19

A retail company is acquiring sales data from 150 stores worldwide. Each store sends daily CSV files via email to a central email address. The data acquisition process is manual: an intern downloads each attachment and copies it into a shared folder. The shared folder is then accessed by an ETL tool that loads data into a data warehouse. Recently, the data warehouse has been missing records for several stores. The intern reports that some emails are not being received or are delayed. The company needs to improve the reliability and timeliness of data acquisition. Which course of action should be taken first?

Accepted Answer

Require stores to upload CSV files directly to a cloud-based storage bucket.. It directly addresses the root cause: the unreliable email delivery mechanism. By having stores upload CSV files directly to a cloud-based storage bucket, the data acquisition process becomes deterministic and avoids email delays, spam filtering, and manual intervention. This also provides a centralized, auditable location for the ETL tool to pull from, improving both reliability and timeliness. The other options either mitigate symptoms (A, C) or introduce unnecessary complexity (B) without solving the fundamental issue of email unreliability.

Answer

Train the intern to check email more frequently and manually verify all attachments.

Answer

Replace the email method with a web-based API that stores push data in real-time.

Answer

Implement an automated email parser that downloads attachments and moves them to the shared folder.

Question 20

An e-commerce company wants to integrate product pricing data from competitor websites to adjust its own prices dynamically. They plan to scrape pricing pages every hour. However, the competitors' websites have anti-scraping measures such as IP blocking and CAPTCHAs. The company's legal team also advises caution regarding terms of service. Which data acquisition strategy is both effective and compliant?

Accepted Answer

Negotiate with competitors to obtain pricing data via API agreements. Negotiating API agreements with competitors is the only strategy that is both effective (reliable, structured, real-time data) and compliant (explicit permission, no ToS violation, no anti-scraping circumvention). It also avoids legal exposure under CFAA and contract law.

Answer

Use a public data aggregator that already provides competitor pricing with permission

Answer

Use a rotating proxy service and human-like browser automation to bypass blocks

Answer

Instruct staff to manually record prices once a week