ARA-C01 Question 270
Single answerOLTP/RDBMS sourcesA retail company is migrating analytics workloads from an on-premises PostgreSQL OLTP system to Snowflake. The PostgreSQL database supports a customer-facing application and experiences frequent inserts and updates throughout the day. The architecture team must design an ingestion pattern that minimizes impact on the source OLTP system while ensuring Snowflake receives near-real-time changes, including updates and deletes, for downstream reporting. Which approach is the MOST appropriate?
- A
Run full table extracts from PostgreSQL every 5 minutes and load them into Snowflake with COPY INTO, then rebuild target tables after each load.
- B
Use PostgreSQL change data capture (CDC) to capture inserts, updates, and deletes from the transaction log and apply those changes incrementally into Snowflake.
- C
Query PostgreSQL system tables every minute to identify changed rows, then truncate and reload affected Snowflake tables.
- D
Use Snowpipe to connect directly to PostgreSQL and continuously ingest changed rows into Snowflake without an intermediate staging layer.
Show answer and explanation
Correct answer: B
Explanation
For OLTP/RDBMS sources, Snowflake architectural best practice is to avoid frequent full extraction patterns when near-real-time data movement is required, especially for systems with ongoing transactional workloads. Log-based CDC is generally the preferred design because it reduces impact on the source database, captures inserts/updates/deletes accurately, and supports incremental application of changes in Snowflake using patterns such as MERGE. This aligns with common Snowflake guidance for ingesting data from operational systems: use batch loads for periodic bulk movement, but use CDC-oriented tools and patterns for low-latency replication from transactional databases. Snowpipe is valuable for continuous file-based ingestion, but it does not natively query or subscribe directly to PostgreSQL. In practice, architects often combine CDC tooling from the source database with staged files, streaming connectors, or managed replication/integration services before loading into Snowflake.
- A. Incorrect.
This approach is not appropriate for a high-transaction OLTP source that requires near-real-time synchronization. Frequent full extracts create unnecessary read pressure on the PostgreSQL system, increase network and compute overhead, and make delete handling inefficient. While technically possible, it is not a best-practice architecture for minimizing source impact or supporting low-latency change propagation.
- B. Correct.
This is the best answer. CDC from the PostgreSQL transaction log is specifically suited for OLTP/RDBMS sources with frequent inserts, updates, and deletes. It minimizes load on the operational database compared with repeated full extracts because it reads changes from logs rather than re-querying entire tables. It also preserves change semantics needed for incremental MERGE-based processing in Snowflake. This is the common architectural pattern for near-real-time ingestion from transactional systems.
- C. Incorrect.
This is not the most appropriate solution. Polling system tables or using ad hoc queries to infer changes is less reliable and less efficient than log-based CDC. It may miss deletes or require custom logic, and truncating/reloading affected Snowflake tables is disruptive and inefficient. This reflects a common misconception that metadata polling is equivalent to enterprise-grade CDC.
- D. Incorrect.
This is incorrect because Snowpipe does not connect directly to PostgreSQL or other OLTP databases. Snowpipe is used to automatically load files from supported cloud staging locations such as Amazon S3, Google Cloud Storage, or Azure Blob Storage. In a database-ingestion pattern, an external tool or service would typically capture CDC events and land them in a supported stage or otherwise load them into Snowflake.