Joining Table and API Data Sources with Python

DA0-002 By islever Editorial Last updated

Apply data transformation Use data acquisition methods Identify data tools

Short answer

B Convert the API and database data to JSON, load each as a pandas DataFrame, then merge the DataFrames to join the sources.

Question

A data analyst needs to join together a table data source and web API data source using Python. Which of the following is the best way to accomplish this task?
  1. Convert the data from the API and database to a varchar format and convert them to pandas DataFrames that are then merged together
  2. Convert the data from the API and database to a JSON format and convert them to pandas DataFrames that are then merged together. Correct Answer
  3. Convert the data from the API and database to a TXT format and convert them to pandas DataFrames that are then merged together.
  4. Convert the data from the API and database to a string format and convert them to pandas DataFrames that are then merged together.

Key Takeaways

The question tests knowledge of intermediate data interchange formats for data acquisition; the trap is picking a flat text format (TXT, varchar, or string) that discards the structure needed to align columns before merging.

This DA0-002 question asks how to best combine a table data source with a web API data source in Python. The correct approach is to normalize both sources into JSON, load them as pandas DataFrames, and merge the resulting DataFrames.

Common mistake: Option C (TXT) is the most tempting wrong pick because plain text feels like a universal format, but TXT flattens nested API and table structures, so column keys are lost and a meaningful pandas merge becomes impossible.

Expert Analysis

Why the Answer Is Correct

JSON is the natural interchange format for this task because REST web APIs almost universally return JSON, and SQL/table results can be serialized to JSON without loss of keys, types, or nesting. Once both sources are expressed as JSON documents, json_normalize or pd.read_json turns each into a pandas DataFrame with named columns, after which df1.merge(df2, on=key) joins them cleanly. This preserves the schema alignment that a merge operation depends on. Amelia233 made the same point, noting that "JSON is the best choice because web APIs commonly return data in JSON format."

Why the Other Options Are Wrong

Option A's varchar format is a database storage type, not an exchange format, and casting API payloads to varchar would erase structure before the DataFrame is built. Option C's TXT format stores unstructured plain text with no column delimiters guaranteed, so pandas cannot reliably reconstruct matching schemas for a merge. Option D's generic string format has the same defect — a raw Python string carries no field names, so there is nothing to join on once it becomes a DataFrame.

Community Comment Notes

The single community comment supports option B and correctly explains that "Python/pandas can easily convert JSON data into DataFrames" before using merge. The commenter's reasoning about web APIs returning JSON matches real API behavior, and no dissenting comments were posted, so the discussion aligns with the technical analysis here.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When a question asks you to join sources of different types, look for the option that preserves structure and column names through the intermediate step — JSON is the standard answer for API-related joins in DA0-002. Eliminate any option naming a storage-only type (varchar) or an unstructured format (TXT, plain string) before comparing the rest.

Frequently Asked Questions

Why is JSON preferred over TXT or a string format when joining API and table data?

JSON keeps field names and types intact, so both sources produce DataFrames with matching columns. TXT and plain strings flatten the data, destroying the keys a merge needs.

Why isn't varchar the right choice for combining an API with a database table?

varchar is a SQL column storage type, not an interchange format for Python. Casting API responses to varchar strips structure before pandas can build a usable DataFrame.

More DA0-002 FAQ →

Discussion 1 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 1

  1. Amelia233 👍 1 Answered B
    JSON is the best choice because web APIs commonly return data in JSON format, and Python/pandas can easily convert JSON data into DataFrames. Once both sources are represented as DataFrames, they can be merged using pandas functions such as merge(). Answer: B

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Which Inconsistency Appears in the January Sales Table?

S-grade

Validating Dashboard Data with Monitoring Alerts

S-grade

Best Communication Approach for Visually Impaired Dashboard Stakeholders

S-grade

Report Type That Varies by User Profile

S-grade

Practice All DA0-002 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DA0-002 Practice Test →

← Back to DA0-002 Study Guide