ARA-C01 Question 271
Single answerAPI sourcesA retail company needs to ingest order events from a third-party SaaS platform that exposes only a REST API. The API returns JSON, enforces rate limits, and requires OAuth token refresh. New data must be available in Snowflake within 5 minutes, and the architecture team wants to minimize custom infrastructure while keeping ingestion reliable and maintainable. Which approach best meets these requirements?
- A
Use Snowpipe Streaming to call the SaaS REST API directly from Snowflake and load the JSON payloads into a target table.
- B
Build an external process or managed integration service to call the REST API, handle OAuth and rate limiting, land the JSON into cloud storage, and use Snowpipe or Snowpipe Streaming to ingest into Snowflake.
- C
Create an external table over the SaaS REST API endpoint and use AUTO_REFRESH to detect new JSON documents as they are published.
- D
Use a Snowflake task with a SQL statement scheduled every 5 minutes to invoke the REST API endpoint and insert the returned JSON into a VARIANT column.
Show answer and explanation
Correct answer: B
Explanation
For API sources, Snowflake best practice is typically to separate extraction from loading. Snowflake excels at ingesting data once it is produced by an upstream process, but it is not a native general-purpose connector framework for polling arbitrary third-party REST APIs. In a real-world architecture, an external application, integration platform, or ETL tool should call the API, handle operational concerns such as OAuth token management, retries, pagination, and rate limiting, and then push data into Snowflake either through staged files plus Snowpipe or through Snowpipe Streaming for lower latency. This pattern aligns with Snowflake documentation on ingestion services: Snowpipe and Snowpipe Streaming ingest data provided to them; they do not replace source-specific API extraction logic. External tables are for files in external stages, not REST endpoints, and tasks are orchestration features within Snowflake rather than a substitute for robust API client infrastructure.
- A. Incorrect.
Incorrect. Snowpipe Streaming is designed to ingest rows into Snowflake through a client SDK from an application or service that already has the data. It does not natively connect to arbitrary third-party REST APIs, manage OAuth token refresh, or handle external API rate limiting on behalf of the user. A candidate might choose this because Snowpipe Streaming supports low-latency ingestion, but it still requires a producer application outside Snowflake.
- B. Correct.
Correct. This is the recommended architectural pattern for API sources. Snowflake does not function as a general-purpose REST API polling engine for third-party SaaS systems. A separate ingestion component, such as a custom service, cloud-native integration service, or ETL/ELT tool, should call the API, manage authentication and throttling, and then deliver the data to Snowflake. Landing data in cloud storage and using Snowpipe is a common near-real-time design; alternatively, the external process can use Snowpipe Streaming if sub-file latency is required. This approach minimizes custom infrastructure compared to fully bespoke pipelines while remaining operationally sound.
- C. Incorrect.
Incorrect. External tables are created over files in supported cloud object storage locations, not over live REST API endpoints. AUTO_REFRESH applies to metadata refresh for new files arriving in stages backed by supported cloud storage integrations. Someone might pick this option by confusing API-delivered JSON with staged JSON files.
- D. Incorrect.
Incorrect. Standard Snowflake SQL tasks schedule SQL statements or stored procedure execution inside Snowflake, but Snowflake is not intended to directly poll arbitrary REST APIs as a native ingestion pattern. Even with procedural logic, handling OAuth flows, API retries, and rate limits is better implemented in an external service purpose-built for outbound API communication. This option reflects a common misconception that tasks can replace external integration components for API extraction.