ARA-C01 Question 269
Single answerOLTP/RDBMS sourcesA retail company is migrating reporting workloads from an on-premises PostgreSQL OLTP system to Snowflake. The PostgreSQL database supports a customer-facing order-entry application and experiences frequent inserts and updates throughout the day. Business stakeholders require Snowflake to reflect source changes within 5 minutes, while minimizing impact on the OLTP system. Historical changes must also be preserved for downstream analytics. Which approach should the architect recommend?
- A
Run full table extracts from PostgreSQL every 5 minutes and use CREATE OR REPLACE TABLE in Snowflake to keep the target current.
- B
Use a CDC-based ingestion pattern from PostgreSQL transaction logs into Snowflake, then apply MERGE logic or an append-plus-history design to preserve changes.
- C
Query PostgreSQL directly from Snowflake using external tables so that Snowflake always reads the latest transactional rows without loading data.
- D
Schedule nightly batch exports from PostgreSQL, then use Snowflake Time Travel to reconstruct intraday history when needed.
Show answer and explanation
Correct answer: B
Explanation
For OLTP/RDBMS sources, Snowflake architecture best practice is to avoid frequent full extracts from transactional systems when low-latency replication is required. Instead, architects should use CDC-based ingestion to capture inserts, updates, and deletes from source database logs with minimal impact on the OLTP workload. Once changes arrive in Snowflake, they can be applied to target tables using MERGE for current-state reporting or retained in append-only/history tables to support auditing and downstream analytics. This pattern is consistent with common Snowflake migration guidance for operational relational sources: protect the transactional system, capture incremental changes efficiently, and model both current and historical state in Snowflake as needed.
- A. Incorrect.
This approach is not appropriate for a busy OLTP source with a 5-minute freshness requirement. Repeated full extracts create unnecessary read pressure on PostgreSQL, increase network and processing costs, and do not scale well as tables grow. Using CREATE OR REPLACE TABLE also discards table state each cycle and is a poor fit when historical change preservation is required.
- B. Correct.
This is the best choice. For OLTP/RDBMS sources with frequent inserts and updates, change data capture (CDC) based on database logs is the standard design to minimize impact on the source while achieving near-real-time replication. In Snowflake, the architect can land change events and then use MERGE for current-state tables, or maintain an append-only history pattern for audit and analytics. This satisfies both low-latency and historical tracking requirements.
- C. Incorrect.
This is incorrect because Snowflake external tables are designed for files in cloud storage, not for directly querying a live PostgreSQL database. This option reflects a common misconception that external tables can virtualize relational databases. To bring PostgreSQL data into Snowflake, the architect would typically use replication/CDC tooling, staged file loads, or connector-based ingestion patterns.
- D. Incorrect.
Nightly batch exports cannot meet the 5-minute latency requirement. In addition, Snowflake Time Travel preserves historical versions of data already stored in Snowflake; it does not reconstruct changes that were never loaded from the source during the day. This option confuses Snowflake's internal data retention features with source-system change capture.