Data Acquisition and Preparation practice questions

json_normalize()

json_normalize() flattens nested JSON objects and arrays into a flat table, producing the tabular structure the analyst needs. It satisfies the stem's conversion requirement directly, unlike parsing or serialisation functions that merely read or write JSON.

The underlying data source was modified after the last refresh

The log is newer, indicating changes that need a full refresh.

Read objects from the bucket

GetObject allows reading.

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

Change tracking on the EHR avoids full relational scans, while the append-only claims files need no deduplication and the API's date filter limits JSON volume. This satisfies the nightly window and consistency constraints by loading only new or changed records.

Use a left join and treat the CustomerID as a string after conversion.

A left join requires matching keys; converting to string ensures compatibility.

Convert the integer CustomerID to a string with leading zeros to match the format in the second dataset.

This ensures both CustomerID fields have the same data type and format.

Authentication requirements

External APIs almost always require credentials — API keys, OAuth tokens or signed requests — before returning data. Handling key rotation, scopes and expiry adds real acquisition overhead, making authentication a recurring challenge when pulling from third-party endpoints.

Rate limiting

Rate limiting caps how many requests an external API accepts within a time window, so acquisition jobs exceeding that quota receive HTTP 429 responses and must throttle, retry or queue. This directly satisfies the stem's external-acquisition constraint, since the provider controls the limit and your pipeline cannot simply scale past it.

Data volume constraints

External APIs impose rate limits and pagination caps that throttle how much data you can pull per request, directly satisfying the data volume constraint. Unlike internal databases, you cannot simply query everything at once; you must batch, paginate, and handle quota exhaustion, making volume a genuine acquisition challenge.

To check for data freshness

The query inspects metadata such as the latest timestamp or maximum date value in the table, revealing how recently data was loaded. This determines data freshness, confirming whether the acquired dataset reflects current records before the analyst proceeds with analysis.

Access is restricted to a specific IP range

The bucket policy includes a condition on the aws:SourceIp key, which evaluates the requester's IP address against the specified CIDR block. Requests originating outside that range are denied, regardless of IAM permissions. This directly enforces network-level access restriction to the defined IP range.

Modify the ETL mapping for data types

The log shows values failing to load because source fields do not match the target schema's declared types, so correcting the ETL mapping's data type conversions resolves the acquisition failure before any downstream processing occurs.

Investigate the source system for data entry errors.

Negative ages are logically impossible, so the anomaly most likely originates upstream rather than from the mining algorithm. Tracing the source system identifies data entry errors, the root cause, before any cleaning or transformation is applied. Correcting values at origin prevents the same invalid records recurring in future extracts.

HTML pages

HTML pages are static markup fetched over HTTP, so a scraper can parse their DOM to extract unstructured content. This satisfies the stem's requirement for a scrapeable source, since no structured query interface exists and the data must be harvested directly from rendered page elements.

JSON APIs

JSON APIs return structured data over HTTP, which scrapers can call and parse programmatically. This satisfies the stem because the endpoint exposes machine-readable records directly, letting the analyst retrieve fields without parsing presentation markup, unlike unstructured HTML.

PDF documents

PDF documents embed text in a binary container, so scrapers extract content using parsing libraries rather than DOM traversal. This satisfies the stem because the data resides in a downloadable file format, making it harvestable through automated extraction despite lacking HTML structure or a queryable API.

Convert System A amounts to dollars by dividing by 100.

System A stores cents as integers, so dividing by 100 yields dollars matching System B's decimal scale. This equalises the units before merging, satisfying the integration constraint that both sources share one consistent currency representation.

Inconsistent key definitions.

Mismatched key formats cause join failures, reducing matches.

Add the API key in the HTTP header as 'Authorization: Bearer <key>'

Bearer tokens transmit credentials in the HTTP Authorization header, which the API validates per request. This satisfies the stem's header-based API key constraint, since the key travels as a request header rather than a URL query parameter, keeping it out of logs and URLs.

Stream the file directly from SFTP into warehouse using a data pipeline tool

Streaming directly from SFTP into the warehouse avoids staging the full 10 GB file to intermediate storage, cutting latency and disk I/O. The pipeline tool manages retries and resumable transfers, satisfying the daily-drop reliability requirement while handling large files efficiently without manual intervention.

Relational databases

Relational databases store structured records generated within an organisation's own systems, such as sales, inventory or HR tables, so they satisfy the stem's requirement for internal data sources. Unlike external feeds, this data is owned and controlled by the enterprise, making it a canonical example of an internal source.

Flat files

Flat files count as internal data because they reside within the organisation's own systems, such as locally stored CSVs or logs, satisfying the stem's requirement for sources originating inside the enterprise rather than from external providers. They remain a common, directly accessible input for analytics pipelines without third-party licensing.

The signup_date column is in a different date format

Date-format mismatch causes string or implicit comparisons to fail against expected boundaries, so rows that should fall inside the filter are silently excluded. That produces a count far lower than the true population, matching the unexpectedly low figure shown in the exhibit.

The network firewall is blocking the port

A blocked port directly interrupts the TCP handshake the extractor needs to reach the source system, so no data transfer can begin. Since the exhibit shows connectivity timing out before any authentication or query executes, the firewall dropping the required port is the most likely cause of the extraction failure.

Require stores to upload CSV files directly to a cloud-based storage bucket.

Replacing email with direct uploads to cloud storage removes the unreliable mail relay and the manual intern step, so files arrive intact and on schedule. This directly addresses the stem's constraint: missing and delayed records caused by email non-delivery and manual copying, improving both reliability and timeliness of acquisition.

Negotiate with competitors to obtain pricing data via API agreements

Negotiated API agreements provide pricing data through a sanctioned channel, bypassing IP blocking and CAPTCHAs while respecting terms of service. This satisfies the stem's dual requirement of effective acquisition and legal compliance, unlike scraping which breaches both technical controls and contractual terms.