CASE STUDIES LIST/ HOME
← Case #20#21 / 25Case #22 →
CASE STUDY #21Phase 5: Cloud & OrchestrationCLASSIFICATION: ACTUAL

From Data Lake to Cloud Warehouse: Loading and Querying Snowflake

High-Throughput Data Ingestion and Semi-Structured JSON Parsing in Snowflake

SnowflakeAzure ADLS Gen2SQLSnowpipe

1. The Problem

Loading 10M records into Snowflake line-by-line using Python `INSERT` statements took 3.2 hours and exhausted Snowflake warehouse credit limits.

2. What I Initially Thought

"I thought standard SQL `INSERT INTO table VALUES (...)` statements would execute fast in a cloud data warehouse."

3. What I Learned

Snowflake is optimized for bulk loading from cloud stages using `COPY INTO`. Storing semi-structured JSON in `VARIANT` columns enables high-speed querying via `LATERAL FLATTEN`.

Snowflake External StagesCOPY INTO Bulk LoadingVARIANT Data TypeLATERAL FLATTEN Parsing

4. What I Built

Snowflake data warehouse loading pipeline using ADLS Gen2 External Storage Stages, file format specs, and automated `COPY INTO` commands.

-- Create External Stage pointing to ADLS Gen2 Silver Parquet files
CREATE STAGE IF NOT EXISTS adls_silver_stage
  URL='azure://datapulse.dfs.core.windows.net/silver/'
  STORAGE_INTEGRATION = adls_azure_int
  FILE_FORMAT = (TYPE = PARQUET);

-- High-Speed Bulk Load into Snowflake!
COPY INTO gold_tasks_analytics
FROM @adls_silver_stage/tasks/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

5. The Experiment

BEFORE

Ingesting 10M records using Python INSERT loop. Duration: 3.2 hours. Snowflake credits consumed: 14 credits.

CHANGE APPLIED

Migrated ingestion to Snowflake `COPY INTO` bulk loading from ADLS Gen2 External Stage.

AFTER RESULT

Ingestion time for 10M records dropped from 3.2 hours to 42 seconds; credit consumption reduced by 98%.

6. What Went Wrong

Attempted to load un-compressed single large 50 GB JSON file, preventing Snowflake from parallelizing file ingestion across warehouse nodes.

7. Engineering Decision & Trade-offs

Standardized on loading multiple 100 MB Parquet files into Snowflake to enable parallel multi-threaded worker scans.

8. What I Would Do Differently in Production

Set up Snowflake Snowpipe for continuous real-time auto-ingestion triggered automatically by Azure Event Grid storage notifications.

Questions I Can Now Answer Confidently in an Interview:

  • How does Snowflake's COPY INTO command achieve high-throughput bulk data ingestion?
  • How does Snowflake store and query semi-structured JSON data using the VARIANT type?
  • Why is splitting data into multiple 100 MB files recommended when loading into Snowflake?

Expected / Verified Evidence

•Snowflake SQL DDL and COPY script (sql/snowflake/load_gold.sql)
•Snowflake Query History execution duration comparison
•Warehouse credit consumption metrics chart
BACK TO ALL CASE STUDIESNEXT: CASE #22 (The Data Didn't Break the Pipeline. The Data Broke the Business.)