Loading Data into Snowflake: Stages, File Formats, COPY INTO, Snowpipe, Snowpipe Streaming, Semi-Structured Data, Error Handling, and Production Loading Patterns

Leave a Comment / Data Engineering, Snowflake

Table of Contents

In the previous post, we set up databases, schemas, warehouses, and roles. Now we need to get data into those tables. Snowflake provides three ways to load data: COPY INTO for bulk batch loading, Snowpipe for continuous automated ingestion, and Snowpipe Streaming for real-time row-level inserts. All three rely on stages (file staging areas) and file formats (parsing instructions). This post covers each method in detail with real SQL examples, error handling patterns, and production best practices.

Analogy — A shipping dock. Getting data into Snowflake is like receiving packages at a warehouse dock. The stage is the loading dock where trucks (files) pull up. The file format is the packing slip that tells the dock workers how the contents are organized (CSV with commas, JSON with nesting, Parquet with columns). COPY INTO is the scheduled delivery — a truck arrives at a set time, workers unload everything in bulk. Snowpipe is the automated conveyor belt — packages (files) appear on the dock and are automatically sorted onto shelves (tables) within minutes, no scheduling needed. Snowpipe Streaming is the express window — individual items (rows) are handed directly through a window and placed on shelves in seconds.

How Data Gets Into Snowflake

Three loading methods:

  1. COPY INTO (Bulk Loading)
     - You run a SQL command manually or on a schedule
     - Uses YOUR virtual warehouse (you pay for compute)
     - Best for: large batch loads, nightly ETL, initial migration
     - Latency: minutes to hours (depends on schedule)

  2. Snowpipe (Continuous Loading)
     - Files trigger automatic loading via cloud event notifications
     - Uses SERVERLESS compute (Snowflake manages and bills per-file)
     - Best for: frequent small files, near-real-time availability
     - Latency: typically 1-3 minutes after file arrives

  3. Snowpipe Streaming (Real-Time)
     - Application sends rows directly via SDK (no files needed)
     - Uses SERVERLESS compute
     - Best for: IoT, clickstream, log data, sub-minute latency
     - Latency: seconds

  Common to all three:
     - Data lands in a Snowflake table
     - Snowflake handles compression, micro-partitioning, and metadata
     - Deduplication is built in (same file will not be loaded twice)

Stages — Where Files Live Before Loading

A stage is a pointer to a location where data files are stored. Snowflake has three types of stages: internal (files stored inside Snowflake), external (files in S3, Azure Blob, or GCS), and table stages (auto-created per table).

Analogy — Airport terminals. An internal stage is the domestic terminal — your files are already inside Snowflake’s territory. An external stage is the international terminal — your files are in a foreign country (S3, Azure Blob, GCS), and Snowflake needs a passport (credentials) to access them. A table stage is the curbside pickup — a quick, temporary spot tied to one specific table.

Internal Stages

-- Create a named internal stage
CREATE STAGE raw_stage
  COMMENT = 'Internal stage for CSV and JSON uploads';

-- Upload files using Snowsight UI:
-- Data > Add Data > Stage > select raw_stage > drag and drop files

-- Upload files using SnowSQL (command-line client):
-- PUT file:///tmp/orders.csv @raw_stage;

-- List files in a stage
LIST @raw_stage;
-- Returns: name, size, md5, last_modified

-- Remove files from a stage after loading
REMOVE @raw_stage/orders.csv;

External Stages (S3, Azure, GCS)

-- Create an external stage pointing to an S3 bucket
CREATE STAGE s3_vendor_stage
  URL = 's3://my-data-bucket/vendor-a/'
  STORAGE_INTEGRATION = my_s3_integration
  COMMENT = 'Vendor A daily files in S3';

-- Create an external stage pointing to Azure Blob Storage
CREATE STAGE azure_vendor_stage
  URL = 'azure://myaccount.blob.core.windows.net/vendor-data/'
  STORAGE_INTEGRATION = my_azure_integration
  COMMENT = 'Vendor data in Azure Blob';

-- Create an external stage pointing to GCS
CREATE STAGE gcs_vendor_stage
  URL = 'gcs://my-gcs-bucket/vendor-data/'
  STORAGE_INTEGRATION = my_gcs_integration
  COMMENT = 'Vendor data in Google Cloud Storage';

-- List files in an external stage
LIST @s3_vendor_stage;

Storage Integrations — Secure Cloud Access

-- Create a storage integration (one-time setup by ACCOUNTADMIN)
-- This avoids storing credentials directly in stage definitions
CREATE STORAGE INTEGRATION my_s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-access'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-data-bucket/');

-- Describe the integration to get the AWS IAM user and external ID
-- (needed to configure the trust policy in AWS)
DESC INTEGRATION my_s3_integration;

Table Stages

-- Every table automatically has a table stage (@%table_name)
-- No creation needed -- it exists as soon as the table exists

-- Upload a file to a table stage
-- PUT file:///tmp/orders.csv @%orders;

-- List files in a table stage
LIST @%orders;

-- Table stages are convenient for quick, one-off loads
-- For production, use named internal or external stages

File Formats — Telling Snowflake How to Parse Your Files

A file format defines how Snowflake should read a file: delimiter, quoting, header row, compression, null values, and more. You can define file formats inline (in the COPY command) or as reusable named objects.

-- Named CSV file format
CREATE FILE FORMAT csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1                    -- Skip the first row (column headers)
  FIELD_OPTIONALLY_ENCLOSED_BY = '"' -- Handle quoted fields
  NULL_IF = ('NULL', 'null', '')     -- Treat these strings as NULL
  EMPTY_FIELD_AS_NULL = TRUE
  TRIM_SPACE = TRUE
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE  -- Allow rows with fewer/more columns
  COMMENT = 'Standard CSV with header row';

-- Named JSON file format
CREATE FILE FORMAT json_format
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE    -- If JSON file is an array of objects
  STRIP_NULL_VALUES = FALSE   -- Keep null fields
  COMMENT = 'JSON array of objects';

-- Named Parquet file format
CREATE FILE FORMAT parquet_format
  TYPE = 'PARQUET'
  SNAPPY_COMPRESSION = TRUE
  COMMENT = 'Standard Parquet with Snappy compression';

-- Named TSV (tab-separated)
CREATE FILE FORMAT tsv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = '\t'
  SKIP_HEADER = 1
  COMMENT = 'Tab-separated values';

-- List all file formats
SHOW FILE FORMATS;

COPY INTO — The Bulk Loading Workhorse

COPY INTO is Snowflake’s primary command for loading data from staged files into tables. It reads files, parses them using a file format, and writes rows into the target table. It uses your virtual warehouse for compute.

Analogy — Unloading a delivery truck. COPY INTO is the dock foreman telling the workers: “Open the truck (stage), read the packing slip (file format), and put everything on shelf 3 (target table).” The foreman (COPY command) coordinates the work. The workers (warehouse compute nodes) do the heavy lifting. More workers (bigger warehouse) means faster unloading.

Basic COPY INTO

-- Load from an internal stage with a named file format
COPY INTO RAW_DB.VENDOR_A.ORDERS
  FROM @raw_stage/orders/
  FILE_FORMAT = csv_format;

-- Load from an external stage (S3)
COPY INTO RAW_DB.VENDOR_A.ORDERS
  FROM @s3_vendor_stage/2026/07/
  FILE_FORMAT = csv_format;

-- Load specific files
COPY INTO RAW_DB.VENDOR_A.ORDERS
  FROM @s3_vendor_stage
  FILES = ('orders_20260719.csv', 'orders_20260720.csv')
  FILE_FORMAT = csv_format;

-- Load files matching a pattern
COPY INTO RAW_DB.VENDOR_A.ORDERS
  FROM @s3_vendor_stage
  PATTERN = '.*orders_202607.*\.csv'
  FILE_FORMAT = csv_format;

COPY INTO with Inline File Format

-- No need to create a named file format for one-off loads
COPY INTO RAW_DB.VENDOR_A.ORDERS
  FROM @raw_stage/orders.csv
  FILE_FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  );

Key COPY INTO Options

-- ON_ERROR: what to do when a row fails
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  ON_ERROR = 'CONTINUE';          -- Skip bad rows, load the rest
  -- ON_ERROR = 'SKIP_FILE';      -- Skip entire file if any row fails
  -- ON_ERROR = 'SKIP_FILE_10';   -- Skip file if > 10 errors
  -- ON_ERROR = 'ABORT_STATEMENT'; -- Stop loading on first error (default)

-- FORCE: reload files even if they were loaded before
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  FORCE = TRUE;                   -- Bypass deduplication check

-- PURGE: delete files from stage after successful load
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  PURGE = TRUE;                   -- Clean up stage after load

-- VALIDATION_MODE: check data without loading
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  VALIDATION_MODE = 'RETURN_ERRORS';     -- Show only error rows
  -- VALIDATION_MODE = 'RETURN_ALL_ERRORS'; -- Show all errors
  -- VALIDATION_MODE = 'RETURN_5_ROWS';    -- Preview first 5 rows

Deduplication — How Snowflake Prevents Double Loading

Snowflake tracks which files have been loaded via metadata:

  - For COPY INTO: metadata stored for 64 days per table
  - For Snowpipe: metadata stored for 14 days per pipe

  If you run the same COPY INTO twice on the same file:
    First run: loads the file, stores metadata (filename + checksum)
    Second run: checks metadata, sees file was already loaded, SKIPS it

  This means:
    - Safe to rerun COPY INTO without duplicating data
    - If file changes (same name, new content), Snowflake detects the new checksum
    - After 64 days, the metadata expires and the file could be loaded again
    - Use FORCE = TRUE to bypass this check and reload intentionally

Transforming Data During Load

COPY INTO supports simple transformations using a SELECT statement. This means you can rename columns, cast types, add constants, and filter rows — all during the load, without a separate transformation step.

-- Load with column mapping and transformation
COPY INTO RAW_DB.VENDOR_A.ORDERS (order_id, customer_name, amount, order_date, load_timestamp)
  FROM (
    SELECT
      $1::INTEGER,                           -- Column 1 -> order_id
      UPPER(TRIM($2)),                       -- Column 2 -> customer_name (cleaned)
      $3::DECIMAL(10,2),                     -- Column 3 -> amount (typed)
      TO_DATE($4, 'MM/DD/YYYY'),             -- Column 4 -> order_date (parsed)
      CURRENT_TIMESTAMP()                    -- Add load timestamp
    FROM @raw_stage/orders.csv
  )
  FILE_FORMAT = csv_format;

-- Load only specific columns from a file
COPY INTO customers (id, name, email)
  FROM (
    SELECT $1, $2, $5     -- Pick columns 1, 2, and 5 from the file
    FROM @raw_stage/customers.csv
  )
  FILE_FORMAT = csv_format;

-- Filter rows during load
COPY INTO orders (order_id, region, amount)
  FROM (
    SELECT $1, $2, $3
    FROM @raw_stage/orders.csv
    WHERE $2 = 'Ontario'  -- Only load Ontario orders
  )
  FILE_FORMAT = csv_format;

Loading Semi-Structured Data (JSON, Parquet, Avro)

Snowflake handles semi-structured data natively using the VARIANT data type. You can load JSON, Parquet, Avro, and ORC files directly without flattening them first.

Loading JSON

-- Create a table with a VARIANT column for raw JSON
CREATE TABLE RAW_DB.VENDOR_A.EVENTS_RAW (
  raw_data VARIANT,
  loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Load JSON into the VARIANT column
COPY INTO RAW_DB.VENDOR_A.EVENTS_RAW (raw_data)
  FROM @s3_vendor_stage/events/
  FILE_FORMAT = json_format;

-- Query the JSON data using dot notation and bracket notation
SELECT
  raw_data:event_id::STRING AS event_id,
  raw_data:user.name::STRING AS user_name,
  raw_data:user.email::STRING AS user_email,
  raw_data:timestamp::TIMESTAMP AS event_time,
  raw_data:properties.page_url::STRING AS page_url
FROM RAW_DB.VENDOR_A.EVENTS_RAW
WHERE raw_data:event_type = 'page_view';

-- Flatten nested JSON arrays
SELECT
  raw_data:order_id::STRING AS order_id,
  item.value:product_name::STRING AS product,
  item.value:quantity::INTEGER AS qty,
  item.value:price::DECIMAL(10,2) AS price
FROM RAW_DB.VENDOR_A.EVENTS_RAW,
  LATERAL FLATTEN(input => raw_data:items) AS item;

Loading Parquet

-- Parquet files have embedded schema -- Snowflake reads it automatically
-- Option 1: Load into VARIANT (raw, fastest)
CREATE TABLE orders_parquet_raw (raw_data VARIANT);

COPY INTO orders_parquet_raw
  FROM @s3_vendor_stage/parquet/
  FILE_FORMAT = parquet_format;

-- Option 2: Load into typed columns (using column mapping)
COPY INTO orders (order_id, customer_name, amount, order_date)
  FROM (
    SELECT
      $1:order_id::INTEGER,
      $1:customer_name::STRING,
      $1:amount::DECIMAL(10,2),
      $1:order_date::DATE
    FROM @s3_vendor_stage/parquet/
  )
  FILE_FORMAT = parquet_format;

-- Option 3: Auto-detect schema and create table (Snowflake 2024+)
CREATE TABLE orders_from_parquet
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(
      LOCATION => '@s3_vendor_stage/parquet/',
      FILE_FORMAT => 'parquet_format'
    ))
  );

Snowpipe — Continuous Automated Ingestion

Snowpipe automates the COPY INTO process. Instead of running COPY INTO on a schedule, Snowpipe listens for cloud event notifications (S3 SNS, Azure Event Grid, GCS Pub/Sub) and loads files within minutes of their arrival. It uses serverless compute — you do not manage a warehouse.

Analogy — An automatic mailroom. COPY INTO is like checking your mailbox once a day and carrying everything inside (scheduled, manual). Snowpipe is like having an automatic mailroom that sorts and delivers mail the moment it drops through the slot (event-driven, continuous). You do not hire a mail carrier (no warehouse needed) — the building provides one (serverless compute).

-- Step 1: Create the target table
CREATE TABLE RAW_DB.VENDOR_A.ORDERS_STREAM (
  order_id INTEGER,
  customer_name STRING,
  amount DECIMAL(10,2),
  order_date DATE,
  loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Step 2: Create the pipe
CREATE PIPE RAW_DB.VENDOR_A.ORDERS_PIPE
  AUTO_INGEST = TRUE
  COMMENT = 'Auto-ingest orders from S3'
  AS
  COPY INTO RAW_DB.VENDOR_A.ORDERS_STREAM
    FROM @s3_vendor_stage/orders/
    FILE_FORMAT = csv_format;

-- Step 3: Get the SQS queue ARN for S3 event configuration
SHOW PIPES;
-- Look for the "notification_channel" column -- this is the SQS ARN
-- Configure this in your S3 bucket's event notifications
-- (S3 > Bucket > Properties > Event Notifications > Add notification > SQS)

-- Step 4: Monitor pipe status
SELECT SYSTEM$PIPE_STATUS('RAW_DB.VENDOR_A.ORDERS_PIPE');

-- Step 5: Check load history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ORDERS_STREAM',
  START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
))
ORDER BY LAST_LOAD_TIME DESC;

-- Pause and resume a pipe
ALTER PIPE RAW_DB.VENDOR_A.ORDERS_PIPE SET PIPE_EXECUTION_PAUSED = TRUE;
ALTER PIPE RAW_DB.VENDOR_A.ORDERS_PIPE SET PIPE_EXECUTION_PAUSED = FALSE;

Snowpipe Pricing

Snowpipe billing:

  Compute: billed per-second of serverless compute used for loading
    - Typically 0.06 credits per 1000 files for small files
    - No warehouse needed (you do not pay warehouse credits)

  Overhead: a small per-file overhead for event processing

  Cost comparison:
    Loading 1000 small CSV files per day:
      COPY INTO (Small warehouse, 5-min run): ~0.17 credits
      Snowpipe: ~0.06 credits

    Loading 10 large files per day:
      COPY INTO (Medium warehouse, 2-min run): ~0.13 credits
      Snowpipe: ~0.01 credits

  Rule of thumb:
    - Many small files (IoT, logs): Snowpipe is cheaper
    - Few large files (nightly batch): COPY INTO may be cheaper
    - Snowpipe's real value is latency (minutes vs hours), not cost

Snowpipe Streaming — Real-Time Row-Level Ingestion

Snowpipe Streaming (introduced in 2023, mature by 2026) allows applications to insert rows directly into Snowflake tables via a Java or Python SDK — no files, no stages. Data is available in seconds.

Snowpipe Streaming flow:

  Application (Java/Python SDK)
    |
    | -- Opens a "channel" to a target table
    | -- Sends rows (insertRows API call)
    | -- Rows buffered client-side, flushed every second
    |
  Snowflake
    |
    | -- Receives micro-batch of rows
    | -- Writes to micro-partitions
    | -- Data queryable within seconds
    |
  Result: sub-second ingestion latency, no files or stages involved

  Use cases:
    - IoT sensor data (thousands of readings per second)
    - Clickstream data (user events in real time)
    - Application logs (structured log entries)
    - Financial ticks (stock prices, trades)

COPY INTO vs Snowpipe vs Snowpipe Streaming

FeatureCOPY INTOSnowpipeSnowpipe Streaming
TriggerManual or scheduledCloud event (file arrival)Application SDK call
ComputeYour warehouseServerless (Snowflake)Serverless (Snowflake)
InputFiles in a stageFiles in a stageRows from application
LatencyMinutes to hours1-3 minutesSeconds
Dedup metadata64 days14 daysOffset-based (client tracks)
Best forLarge batch loads, nightly ETLFrequent small files, near-real-timeIoT, clickstream, logs, real-time
Cost modelWarehouse creditsPer-file serverless creditsPer-row serverless credits
TransformationSELECT in COPY INTOSELECT in pipe definitionNone (raw insert only)

Error Handling and Validation

Validating Before Loading

-- Preview what COPY INTO will load (without actually loading)
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  VALIDATION_MODE = 'RETURN_5_ROWS';

-- Show all errors without loading
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  VALIDATION_MODE = 'RETURN_ERRORS';

-- Show all errors across all files
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  VALIDATION_MODE = 'RETURN_ALL_ERRORS';

Handling Errors During Load

-- Load with error handling: skip bad rows, log them
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  ON_ERROR = 'CONTINUE';

-- After loading, check which rows were rejected
SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));
-- Returns: rejected rows with error details (line number, column, error message)

Production Error Pattern

-- Step 1: Load with CONTINUE to capture errors
COPY INTO orders
  FROM @raw_stage
  FILE_FORMAT = csv_format
  ON_ERROR = 'CONTINUE';

-- Step 2: Save rejected rows to an error table
CREATE TABLE IF NOT EXISTS orders_errors AS
SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));

-- Step 3: Alert if errors exceed threshold
SET error_count = (SELECT COUNT(*) FROM TABLE(VALIDATE(orders, JOB_ID => '_last')));
SELECT IFF($error_count > 100, 'ALERT: Too many load errors', 'Load OK') AS status;

-- Step 4: Check COPY history for overall load status
SELECT *
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ORDERS',
  START_TIME => DATEADD('hour', -1, CURRENT_TIMESTAMP())
));

Data Loading Best Practices

File sizing — Aim for files between 100 MB and 250 MB (compressed). Too small (1 MB) means thousands of files and high overhead. Too large (5 GB) means one file failure rejects a lot of data. Split large exports into multiple files at the source.

Compression — Always compress files before staging. Snowflake supports GZIP, BZIP2, ZSTD, BROTLI, and Snappy. GZIP is the most common. Compressed files load faster because less data is transferred. Snowflake auto-detects compression in most cases.

File organization — Organize files in stages by date paths: @stage/orders/2026/07/19/orders_001.csv.gz. This makes it easy to load specific dates using paths or patterns, and to troubleshoot when loads fail.

Dedicated warehouses — Use a separate warehouse for data loading (WH_ETL). Loading is I/O intensive and can slow down analytics queries if they share a warehouse. Size the loading warehouse based on file volume: Small for < 1 GB, Medium for 1-50 GB, Large for 50+ GB.

Use named file formats — Create reusable file format objects instead of inline definitions. This ensures consistency across all COPY commands and makes it easy to update parsing rules in one place.

Monitor load history — Query INFORMATION_SCHEMA.COPY_HISTORY regularly. Track success/failure rates, row counts, and load duration. Set up alerts for failed loads.

Unloading Data — COPY INTO Location

Snowflake also supports unloading data from tables to stages using COPY INTO location syntax:

-- Unload to an internal stage as CSV
COPY INTO @raw_stage/exports/orders_export
  FROM (SELECT * FROM ANALYTICS_DB.CORE.ORDERS WHERE order_date = '2026-07-19')
  FILE_FORMAT = (TYPE = 'CSV' HEADER = TRUE COMPRESSION = 'GZIP')
  OVERWRITE = TRUE
  SINGLE = FALSE             -- Split into multiple files
  MAX_FILE_SIZE = 268435456;  -- 256 MB per file

-- Unload to S3 as Parquet
COPY INTO @s3_vendor_stage/exports/orders/
  FROM ANALYTICS_DB.CORE.ORDERS
  FILE_FORMAT = (TYPE = 'PARQUET')
  HEADER = TRUE;

Common Mistakes

  1. Not creating named file formats. Using inline file format definitions in every COPY command leads to inconsistency — one COPY has SKIP_HEADER=1, another does not. Create a named file format per source type and reference it everywhere.

  2. Loading files that are too small. Loading 10,000 files of 100 KB each is dramatically slower than loading 10 files of 100 MB each. Snowflake processes files in parallel, but per-file overhead is significant. Ask source systems to generate larger files or aggregate small files before staging.

  3. Not using VALIDATION_MODE before production loads. Running COPY INTO VALIDATION_MODE = ‘RETURN_ERRORS’ on a sample of files before the first production load catches schema mismatches, encoding issues, and delimiter problems before they cause failures at scale.

  4. Using COPY INTO for near-real-time workloads. If you need data available within minutes of creation, use Snowpipe. Running COPY INTO every 5 minutes on a schedule wastes warehouse credits (5 minutes of startup + suspend overhead) and still has higher latency than Snowpipe.

  5. Forgetting to clean up stages. Files in internal stages consume storage that you pay for. After successful loading, use PURGE = TRUE in the COPY command or run REMOVE @stage/path/ manually. External stage files in S3/Blob/GCS have their own storage costs from the cloud provider.

  6. Not organizing files by date in stages. Dumping all files into the root of a stage makes it impossible to load specific dates, troubleshoot failures, or manage retention. Use paths like /orders/2026/07/19/ consistently.

  7. Loading with ON_ERROR = ABORT_STATEMENT for large batch loads. One bad row in a 10 GB file causes the entire load to fail. Use ON_ERROR = ‘CONTINUE’ or ‘SKIP_FILE_10’ for large loads, then inspect rejected rows with VALIDATE() after the load completes.

  8. Not monitoring Snowpipe. Snowpipe is fire-and-forget, which means failures are silent. Query COPY_HISTORY and SYSTEM$PIPE_STATUS regularly. Set up alerts for pipes that stop loading or accumulate errors.

Interview Questions

Q: What is the difference between COPY INTO and Snowpipe? A: COPY INTO is a user-initiated bulk load command that runs on a virtual warehouse you specify and control. You run it manually or on a schedule. Snowpipe is an automated, continuous ingestion service that uses serverless compute managed by Snowflake. It is triggered by cloud event notifications when new files arrive in a stage. COPY INTO is best for large, scheduled batch loads. Snowpipe is best for frequent small files where near-real-time availability (1-3 minutes) is needed.

Q: What are the three types of stages in Snowflake? A: Internal stages store files within Snowflake’s managed storage. External stages point to cloud storage locations (S3, Azure Blob, GCS) and require storage integrations for secure access. Table stages are auto-created per table and provide a quick staging area for that specific table. For production, named internal or external stages are recommended because they are reusable across tables and easier to manage.

Q: How does Snowflake prevent duplicate data loading? A: Snowflake tracks loaded files using metadata that stores the filename and checksum. For COPY INTO, this metadata is stored for 64 days per table. For Snowpipe, it is stored for 14 days per pipe. If you run COPY INTO again on the same file, Snowflake checks the metadata and skips the file. You can bypass this check with FORCE = TRUE to intentionally reload a file. After the metadata retention period expires, the file could be loaded again if it still exists in the stage.

Q: How do you load JSON data into Snowflake? A: Load JSON files into a VARIANT column using COPY INTO with a JSON file format. Use STRIP_OUTER_ARRAY = TRUE if the JSON file is an array of objects. Once loaded, query the VARIANT data using colon notation (raw_data:field_name) and cast to specific types (::STRING, ::INTEGER). For nested arrays, use LATERAL FLATTEN to unnest the array into individual rows. This approach keeps the raw JSON intact in bronze and allows flexible schema-on-read querying without predefined column structures.

Q: What is VALIDATION_MODE and when would you use it? A: VALIDATION_MODE is a COPY INTO option that validates data without actually loading it. RETURN_5_ROWS previews the first 5 rows as they would be loaded. RETURN_ERRORS shows only rows that would fail. RETURN_ALL_ERRORS shows all errors across all files. Use it before the first production load of a new data source to catch schema mismatches, encoding issues, and delimiter problems without risking bad data in your tables.

Q: What are the best practices for file sizing in Snowflake data loading? A: Aim for compressed files between 100 MB and 250 MB. Files that are too small (under 10 MB) create per-file processing overhead and slow down loading. Files that are too large (over 5 GB) mean that one bad row can reject a large amount of data, and parallel processing across files is less effective. Always compress files using GZIP, ZSTD, or Snappy before staging. Organize files in date-based directory structures for easy selective loading and troubleshooting.

Q: How would you set up a production data loading pipeline in Snowflake? A: For batch loading, create a dedicated loading warehouse (WH_ETL), named external stages pointing to your cloud storage, and named file formats per source type. Schedule COPY INTO commands via Snowflake Tasks or an external orchestrator (Airflow, dbt). Use ON_ERROR = CONTINUE with post-load validation using VALIDATE() and COPY_HISTORY. For continuous loading, set up Snowpipe with AUTO_INGEST = TRUE and configure cloud event notifications on your storage bucket. Monitor both approaches using COPY_HISTORY queries and set up resource monitors to control costs.

Wrapping Up

Data loading in Snowflake centers on three methods: COPY INTO for scheduled bulk loads, Snowpipe for continuous file-based ingestion, and Snowpipe Streaming for real-time row-level inserts. Stages hold your files. File formats tell Snowflake how to parse them. COPY INTO’s deduplication prevents double-loading. Snowpipe automates the entire process. And the VARIANT data type makes semi-structured data (JSON, Parquet, Avro) a first-class citizen.

In the next post, we will move from loading raw data to transforming it: Streams for change data capture, Tasks for scheduled SQL, Dynamic Tables for declarative pipelines, and stored procedures for complex logic.

Related posts: – Snowflake Overview & Architecture – Snowflake Account Setup & RBAC – How Real Companies Receive Data – Parquet vs CSV vs JSON

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top