Welcome To TechBrothersIT: Snowflake Interview Questions and Answers Part 4

Snowflake Interview Questions and Answers Part 4 - Loading Data With COPY INTO and Snowpipe

Snowflake Interview Q&A · Part 4 of 10

In this post we are going to cover loading data into Snowflake with COPY INTO and Snowpipe: stages and storage integrations, bad rows with ON_ERROR and VALIDATION_MODE, load metadata, file sizes, Snowpipe with auto-ingest and Snowpipe Streaming. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where we design the daily load of fifty source files into raw, staging and reporting tables.

Loading is the first step of almost every Snowflake pipeline. Files land in cloud storage, and something has to get them into tables: a COPY INTO statement for batch loads, Snowpipe for files that arrive all day, or Snowpipe Streaming for rows that must land in seconds. Interviewers ask about it because the details matter on a real project: why a rerun loads nothing, why a few bad rows fail a whole load, and why ten thousand tiny files are slow.

Part 4 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.

On this page

The short answer
Question 1: Files land in S3 or Azure Blob every night. What are stages and storage integrations, and how do you set them up?
Question 2: You run COPY INTO and a few bad rows fail the whole load. How do ON_ERROR and VALIDATION_MODE help?
Question 3: You rerun COPY INTO for yesterday's files and nothing loads. Why, and how does load metadata work?
Question 4: The load of ten thousand tiny files is slow. What file sizes does Snowflake recommend, and why?
Question 5: The business wants files loaded within minutes of landing. How does Snowpipe with auto-ingest work, and how is it billed?
Question 6: A team needs events from Kafka in Snowflake within seconds. When do you use Snowpipe Streaming instead of Snowpipe?
Scenario 7: You are asked to design the daily load of fifty source files into raw, staging and reporting tables. What is your design?

The short answer

Say this first

Files load from stages, and an external stage should connect through a storage integration. COPY INTO skips files already in its load metadata for 64 days, and ON_ERROR and VALIDATION_MODE control bad rows. Snowpipe loads files within about a minute, billed per GB, and Snowpipe Streaming writes rows in seconds.

Keep that in mind. Every question below is one piece of it.

Question 1: Files land in S3 or Azure Blob every night. What are stages and storage integrations, and how do you set them up?

A stage is where Snowflake loads files from, and for nightly files in your own cloud storage I use a named external stage that connects through a storage integration.

Let's start with the types of stages. Internal stages live inside Snowflake: the user stage, the table stage and named internal stages, and you fill them with PUT. External stages point at your own S3, Azure or Google Cloud Storage.

Stage	Reference	Use it for
User	@~	one user's files
Table	@%orders	one table, owner only
Named internal	@my_stage	regular loads, grantable
Named external	@sales_stage	your S3, Azure, GCS

For nightly files, a named external stage is the right choice. It's a database object you can grant to roles, unlike user and table stages.

Now the storage integration. It holds the cloud identity, for S3 an IAM role, and the allowed locations, so no keys sit in the stage. Creating one needs the CREATE INTEGRATION privilege, which only ACCOUNTADMIN has by default.

Then run DESC INTEGRATION. It gives you the Snowflake identity and external ID that go into the trust policy of your IAM role. For Azure, the provider is AZURE, with your AZURE_TENANT_ID.

Here is the whole setup for S3:

stage-setup.sql

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN =
    'arn:aws:iam::123456789012:role/sf-load'
  STORAGE_ALLOWED_LOCATIONS = ('s3://tb-raw/sales/');
-- gives the Snowflake identity for the trust policy
DESC INTEGRATION s3_int;
CREATE STAGE raw.sales_stage
  URL = 's3://tb-raw/sales/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = (FORMAT_NAME = 'raw.csv_ff');

On a real project

Limit the allowed locations to the exact paths you load from, and attach a named file format to the stage.

Question 2: You run COPY INTO and a few bad rows fail the whole load. How do ON_ERROR and VALIDATION_MODE help?

By default, COPY INTO stops the whole statement on the first error. ON_ERROR changes that, and VALIDATION_MODE lets you find the bad rows before you load anything.

First, the defaults. For bulk loads, the default is ABORT_STATEMENT. Snowpipe is different: its default is SKIP_FILE.

Let's look at the options:

ON_ERROR	What happens
ABORT_STATEMENT	default for COPY: first error stops all
CONTINUE	loads good rows, skips bad rows
SKIP_FILE	default for Snowpipe: skips the file
SKIP_FILE_n, _n%	skips a file past a count or percent

CONTINUE loads the good rows and skips the bad ones. SKIP_FILE skips any file with errors, or only past a count or percentage you set. It's slower, because it buffers the whole file.

Then there is VALIDATION_MODE. It's a dry run: it checks the files and returns the errors, or the first rows, without loading anything. One limit: it doesn't work with a COPY that transforms data.

My pattern for a new feed has three steps. First, a dry run with VALIDATION_MODE = RETURN_ERRORS. Then the real load with ON_ERROR = CONTINUE. And right after it, call the VALIDATE function on the last job to save the rejected rows.

safe-load.sql

-- 1) dry run: loads nothing, returns the errors
COPY INTO raw.orders FROM @raw.sales_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  VALIDATION_MODE = RETURN_ERRORS;

-- 2) load the good rows, skip the bad ones
COPY INTO raw.orders FROM @raw.sales_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  ON_ERROR = CONTINUE;

-- 3) the rows that were rejected
SELECT * FROM TABLE(VALIDATE(raw.orders,
  JOB_ID => '_last'));

The mistake I see

CONTINUE with nobody checking the rejects. The load still says it succeeded, so read the per-file results COPY returns and alert on errors.

Question 3: You rerun COPY INTO for yesterday's files and nothing loads. Why, and how does load metadata work?

Nothing loads because those files were already loaded. COPY checks its load metadata and skips them.

Let's see what Snowflake keeps. For each table, the load metadata has:

File name, size and ETag
Row count, last load time and errors
It lasts 64 days, and after that a file's status can be uncertain

COPY checks this list and skips the files it already loaded. That makes COPY idempotent by default. A retry, or a run that picks up the same folder twice, won't duplicate data.

A file's status becomes uncertain when its last modified date and the table's first load are both older than 64 days. Uncertain files are skipped, unless you set LOAD_UNCERTAIN_FILES = TRUE.

So what if you really need to reload, say because you truncated the target? Set FORCE = TRUE. It ignores the load metadata, so it can duplicate data. That's why I delete those files' rows first, using a source file column, and then force the reload:

reload.sql

-- rerun: skipped, already in load metadata
COPY INTO raw.orders
  FROM @raw.sales_stage/orders/2026/10/22/;
-- real reload: remove those rows, then force it
DELETE FROM raw.orders
  WHERE src_file LIKE 'orders/2026/10/22/%';
COPY INTO raw.orders
  FROM @raw.sales_stage/orders/2026/10/22/ FORCE = TRUE;

Careful with Snowpipe

Snowpipe keeps its own load history, for 14 days, and recreating a pipe drops it. That's a classic way to load the same files twice.

Question 4: The load of ten thousand tiny files is slow. What file sizes does Snowflake recommend, and why?

Snowflake recommends data files of roughly 100 to 250 MB compressed, or larger. Very large files, like 100 GB or more, aren't recommended, and a load that runs longer than 24 hours may be aborted.

The reason is parallelism and overhead. Parallel load operations can't exceed the number of files, and every file adds its own processing overhead, however small it is. Ten thousand tiny files is a lot of overhead for very little data.

The opposite also hurts. One huge file on an X-Large warehouse wastes compute, because it can't be spread across the warehouse. So split big files into at least as many as the warehouse loads in parallel.

Here are the three cases side by side:

	Tiny files	100 to 250 MB	Very large file
PARALLEL LOADS	many, tiny work each	enough files for the warehouse	capped by one file
OVERHEAD	adds up per file	low	low
RISK	slow for very little data	none	over 24 hours may abort

So how do we fix tiny files? Upstream:

Batch at the source: have the source write files of 100 to 250 MB.
Date paths: so COPY lists a narrow prefix.
Avoid PATTERN: the docs call it the slowest way to select files.

Scaling up won't help

A bigger warehouse doesn't fix file counts. If small files arrive all day, that's a better fit for Snowpipe.

Question 5: The business wants files loaded within minutes of landing. How does Snowpipe with auto-ingest work, and how is it billed?

Snowpipe is a pipe object that wraps a COPY statement and runs it on serverless compute when new files arrive. It loads them typically within a minute.

Let's follow one file. A new file lands in the S3 bucket. The bucket's event notification sends a message to an SQS queue. The pipe runs its COPY, and the rows are in raw.orders in about a minute.

That's auto-ingest: cloud events tell Snowflake about new files. For S3, point the bucket's event notification at the SQS queue in the notification_channel column of SHOW PIPES. Azure and Google Cloud need a notification integration. Without auto-ingest, your code calls the REST API.

snowpipe.sql

CREATE PIPE raw.orders_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO raw.orders
  FROM @raw.sales_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = 'raw.csv_ff');

-- notification_channel: SQS ARN for the S3 event
SHOW PIPES LIKE 'orders_pipe' IN SCHEMA raw;
SELECT SYSTEM$PIPE_STATUS('raw.orders_pipe');

Billing is simple now: fixed credits per GB loaded, with no per-file charge and no compute charge. You track it in pipe usage history.

Snowpipe billing
CHARGE	fixed credits per GB loaded
PER FILE	no charge
CSV, JSON	uncompressed size
PARQUET	stored size

The pipe's COPY has limits: no FORCE, no PURGE, no VALIDATION_MODE, and ON_ERROR defaults to SKIP_FILE. You can't alter a pipe's COPY, so changing it means CREATE OR REPLACE PIPE.

Don't load twice

Recreating the pipe drops its load history, so after a recreate, refresh with a MODIFIED_AFTER time. When loads look late, check SYSTEM$PIPE_STATUS for pending files.

Question 6: A team needs events from Kafka in Snowflake within seconds. When do you use Snowpipe Streaming instead of Snowpipe?

For Kafka in seconds, it's Snowpipe Streaming, usually through the Kafka connector.

Let's compare the two. Snowpipe loads files from a stage, with about a minute of latency. Snowpipe Streaming writes rows straight into tables, from an SDK, Kafka or a REST API, with no files to stage, in seconds.

Here is what to know about it today:

Kafka connector version 4: generally available, with exactly-once, ordered delivery in about 5 to 10 seconds end to end.
High-performance architecture: generally available on AWS, Azure and Google Cloud.
Pipe object: the entry point, for validation and in-flight transforms, with new Java and Python SDKs.
Billing: per uncompressed GB ingested.

Then the channels. Data flows through channels, and there are two kinds:

Channel	Delivery
Named	ordered, exactly-once, offset tokens
Elastic	at-least-once, no ordering

Named channels keep order and give exactly-once delivery with offset tokens, which fits Kafka partitions and CDC. Elastic channels are at-least-once with no ordering, and they're the recommended start for new apps.

My rule

Files already in cloud storage, and a minute is fine: Snowpipe. Rows or events that must land in seconds: Snowpipe Streaming.

Scenario 7: You are asked to design the daily load of fifty source files into raw, staging and reporting tables. What is your design?

My design is driven by a control table: one stage, one procedure that loads every source, and raw, staging and reporting layers run in order by a task graph. Let's build it one layer at a time.

First, the landing zone. One storage integration and one external stage, with a folder per source and date, like source/yyyy/mm/dd. Each file type gets a named file format.

Next, a control table with one row per source: path, file format, target table and an active flag.

source	stage path	target
orders	orders/	raw.orders
customers	customers/	raw.customers
48 more	...	...

One procedure reads it and runs COPY INTO for each active source. So source fifty-one is a new row, not new code.

Then the raw layer. It loads the files as they are, with lineage columns for the source file name and row number, and ON_ERROR set to CONTINUE, with the rejects logged. Load metadata makes a rerun safe.

load-one-source.sql

COPY INTO raw.orders
  FROM @raw.sales_stage/orders/2026/10/22/
  FILE_FORMAT = (TYPE = CSV PARSE_HEADER = TRUE
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
  INCLUDE_METADATA = (src_file = METADATA$FILENAME,
    src_row = METADATA$FILE_ROW_NUMBER)
  ON_ERROR = CONTINUE;

After raw, the rest of the pipeline:

Staging: types and deduplicates, keeping the latest row per key with QUALIFY ROW_NUMBER().
Reporting: built with MERGE into facts and dimensions.
Task graph: runs the layers in order, with a finalizer task for checks and alerts.
Around it: a dedicated load warehouse, right-sized files and row count checks between layers.

When it changes

If a source starts arriving during the day, move it to Snowpipe.

Key points to remember

Named external stage + storage integration: no keys in the stage
VALIDATION_MODE dry run; choose ON_ERROR on purpose, check rejects
Load metadata: 64 days, skips loaded files; FORCE can duplicate
Files of 100 to 250 MB compressed; parallelism follows file count
Snowpipe: files in about a minute, per GB; Streaming: rows in seconds

Next in this series

Part 5: Streams, Tasks and Dynamic Tables

Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.

Welcome To TechBrothersIT

Label