OpenEDG PCAD: Data Acquisition and Integration

What this domain tests

Data acquisition and integration covers getting data from relational databases, HTTP APIs, and web or CSV sources, then combining it into one trustworthy dataset. The exam expects you to know the mechanics of each source and the failure modes that break integration.

Core rules

• Databases: connect with a driver (sqlite3, psycopg2) or SQLAlchemy; use parameterized queries; always close connections and cursors. pandas.read_sql is a shortcut but still needs a connection.
• APIs: send requests.get with auth headers and query params; check status_code; parse response.json(). Pagination is usually via page, offset, or a next cursor, so loop until exhausted.
• Web content: use BeautifulSoup for HTML, select by CSS class or id; respect robots.txt and rate limits; prefer official APIs over scraping.
• Integration: align schemas, cast join keys to the same dtype, deduplicate by a stable natural key, and log row counts before and after each merge.

Worked example

A weather API returns {'data': [...], 'next': 'https://.../page=2'}. You initialize records = [], request page 1, extend with payload['data'], then set url = payload['next'] and repeat while url is not None. After collection, build a DataFrame, drop duplicates on station_id plus timestamp, and assert the row count equals the sum of per-page counts minus duplicates. The reasoning: the next field, not a fixed page count, defines termination; deduplication protects against overlapping pages.

Traps that cost marks

• Assuming the JSON root is always a list; APIs often wrap data in an object.
• Stopping after page 1 when a next link exists.
• Joining on an integer ID in one table and a string ID in another, producing zero matches.
• Leaving connections open, or using f-strings to build SQL, which creates injection risk.
• Silently overwriting duplicate keys during a merge instead of deciding a rule.