ARA-C01 Question 180
Single answerMigrationA company is migrating a 120 TB on-premises data warehouse to Snowflake. The source system contains several years of historical data stored as compressed files in object storage, plus ongoing daily incremental extracts. The migration team must minimize time-to-load for the historical backfill, avoid reloading files if jobs are retried, and keep the target tables available for downstream validation throughout the migration. Which approach best meets these requirements?
- A
Load all historical and incremental files with repeated INSERT ... SELECT statements from external tables, because Snowflake does not track file load history for staged data.
- B
Use Snowpipe Streaming for the historical backfill and daily increments, because it is optimized for bulk loading large backlogs and automatically preserves exactly-once semantics for all migration scenarios.
- C
Stage the files in cloud storage, use COPY INTO to bulk-load the historical data in parallel, and then continue with COPY INTO for daily incremental files while relying on Snowflake's load metadata to prevent accidental reloading of the same files.
- D
Create materialized views over external tables for the historical data, and migrate to native Snowflake tables only after all downstream validation is complete, because this avoids any physical data loading during migration.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to use staged files with COPY INTO for both the historical backfill and subsequent incremental loads. In Snowflake migration projects, COPY INTO is the standard bulk loading mechanism for large volumes of existing files because it scales well, supports parallelism, and maintains load history metadata for staged files to help prevent duplicate ingestion during retries. This is especially important during migrations, where restartability and idempotent behavior are operational priorities. Snowflake documentation and best practices consistently position bulk loading via stages and COPY INTO as the preferred approach for large file-based ingestion, while Snowpipe and Snowpipe Streaming are intended for more continuous, lower-latency ingestion patterns rather than massive one-time backfills. External tables can assist with transitional access patterns, but they are not the primary mechanism for high-performance migration into native Snowflake storage.
- A. Incorrect.
Incorrect. INSERT ... SELECT from external tables is generally not the best choice for a large historical backfill when the goal is fast bulk ingestion and retry-safe file handling. Snowflake does track load metadata for files loaded with COPY INTO from stages, which helps prevent duplicate loads of the same staged files unless forced. Using repeated INSERT statements from external tables would also typically be less operationally efficient for a large migration workload.
- B. Incorrect.
Incorrect. Snowpipe Streaming is designed for low-latency row-based ingestion from applications, not as the preferred mechanism for loading a very large historical backlog of files. While it can support exactly-once semantics in supported ingestion patterns, it is not the recommended bulk backfill tool for 120 TB of existing files in object storage. A common misconception is treating all ingestion technologies as interchangeable; in practice, bulk file loading with COPY INTO is the standard approach for large-scale migrations.
- C. Correct.
Correct. For a large migration, the recommended pattern is to place the source files in a Snowflake-accessible stage and use COPY INTO to load them in parallel. COPY INTO is optimized for bulk ingestion and records file load metadata, which helps avoid reloading already loaded files during retries or reruns unless the load is explicitly forced. Continuing to use staged incremental files with COPY INTO also supports a controlled migration process while keeping target tables available for validation and downstream testing.
- D. Incorrect.
Incorrect. External tables can be useful for querying data in place, but they are not a substitute for a high-performance migration target when the objective is to complete a bulk historical load into native Snowflake tables. Materialized views over external tables introduce additional considerations and do not eliminate the need to physically load data if the end state is Snowflake-managed tables. This option also does not best address the requirement for minimizing historical load time.