Skip to Content

Snowflake SnowPro Advanced Architect: Comparing Options to Efficiently Ingesting Small JSON Files into Snowflake

By: Author Alex Lim

Posted on

Categories Exam

Learn the most cost-effective solution for loading numerous small JSON files from IoT devices into Snowflake tables. Explore external tables, pipes, streams, and COPY commands to optimize your data ingestion process.

Table of Contents

Question

A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider.

What is the MOST cost-effective way to bring this data into a Snowflake table?

A. An external table
B. A pipe
C. A stream
D. A copy command at regular intervals

Answer

D. A copy command at regular intervals

Explanation

The most cost-effective way to bring the large number of small JSON files into a Snowflake table is:

D. A copy command at regular intervals

Using a COPY command at regular intervals is the most efficient approach for this scenario. Here’s why:

  1. COPY commands can directly load data from cloud storage (e.g., Amazon S3, Azure Blob Storage) into Snowflake tables.
  2. Snowflake automatically scales resources during the COPY operation, ensuring optimal performance.
  3. COPY commands can handle large volumes of small files efficiently by leveraging Snowflake’s parallel processing capabilities.
  4. Regular intervals allow for batching the files, reducing the overhead of individual file processing.

The other options have limitations:

  • External tables provide a metadata layer over external data but don’t physically load the data into Snowflake.
  • Pipes and streams are used for continuous data loading and change data capture, respectively, but are not designed for bulk loading small files.

Therefore, using COPY commands at regular intervals strikes the right balance between cost-effectiveness and performance when ingesting a large number of small JSON files into Snowflake.

Snowflake SnowPro Advanced Architect certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Snowflake SnowPro Advanced Architect exam and earn Snowflake SnowPro Advanced Architect certification.