ARA-C01 Question 272
Single answerAPI sourcesA retail company needs to ingest order updates from a third-party SaaS platform that exposes only a REST API. The API enforces rate limits, uses OAuth 2.0 tokens that expire every hour, and occasionally returns nested JSON payloads with schema changes. The company wants a solution centered on Snowflake that minimizes custom infrastructure, preserves raw responses for replay, and supports downstream transformation in SQL. Which approach is the MOST appropriate?
- A
Use a Snowflake external function to call the SaaS API directly from SQL on a schedule, and write the results into target tables.
- B
Build a Snowpark Python stored procedure in Snowflake that calls the external API directly, handles OAuth refresh logic internally, and runs continuously as the ingestion service.
- C
Use an external orchestration/integration service to call the SaaS REST API, land raw JSON files in cloud object storage, and configure Snowpipe to load the files into a VARIANT-based landing table for downstream processing.
- D
Create a materialized view over a stage that points to the SaaS API endpoint so Snowflake can automatically fetch and flatten new JSON documents as they appear.
Show answer and explanation
Correct answer: C
Explanation
For third-party API sources, Snowflake is typically the destination and processing platform rather than the component responsible for directly polling operational REST endpoints. Best practice is to use an external ingestion or orchestration service to extract data from the API, especially when the source requires OAuth token handling, pagination, retry logic, and rate-limit compliance. Persisting raw API responses in cloud object storage before loading them into Snowflake provides replayability, auditability, and resilience. Snowpipe is designed to continuously load files from cloud storage into Snowflake tables and works well with semi-structured JSON loaded into VARIANT columns. This pattern also accommodates schema drift because transformations can be applied later using SQL on VARIANT data. Relevant Snowflake guidance includes using stages and Snowpipe for file-based continuous ingestion, and using VARIANT for semi-structured JSON data. External functions and stored procedures can be useful in specific cases, but they are not the preferred primary architecture for robust SaaS API ingestion pipelines.
- A. Incorrect.
Incorrect. External functions are intended for invoking remote services from Snowflake through API Gateway-style integrations, typically to enrich query processing, not to serve as a primary scheduled ingestion framework for pulling rate-limited SaaS API data. They also do not natively solve durable raw payload capture, replayability, or robust API pagination/token lifecycle management for operational ingestion.
- B. Incorrect.
Incorrect. While Snowpark Python stored procedures can execute Python logic, using a stored procedure as a continuously running ingestion service for external API polling is not the best architectural choice. This approach places operational concerns such as scheduling, retry behavior, OAuth token refresh, rate-limit handling, and long-running API extraction logic inside Snowflake, which is generally better handled by purpose-built orchestration or integration tooling. It also does not inherently provide the most resilient raw-file landing pattern.
- C. Correct.
Correct. This is the most appropriate design for API-source ingestion into Snowflake. A dedicated integration/orchestration layer can handle OAuth token refresh, retries, pagination, rate limiting, and incremental extraction from the REST API. Landing raw JSON into cloud storage preserves source responses for replay and audit. Snowpipe can then auto-ingest files into a landing table using VARIANT, which is well suited for semi-structured and evolving JSON schemas. Downstream transformations can be done in SQL or Snowpark after ingestion.
- D. Incorrect.
Incorrect. Snowflake stages reference files in supported cloud storage locations, not arbitrary REST API endpoints. Materialized views also do not fetch data from an API source. This option reflects a common misconception that stages can directly connect to operational APIs; they cannot.