2025 Data Ingestion Flashcards by Christine Carroll

What two options are provided for loading?

Bulk and Continuous

How well did you know this?

Not at all

Perfectly

What basic transformations are supported while loading data using the COPY command?

Reordering columns
Omitting columns
Type casting
Data truncation

How well did you know this?

Not at all

Perfectly

What are the three type of internal stages?

User Stage
Table Stage
Named Stage

How well did you know this?

Not at all

Perfectly

How do you reference a user stage

How well did you know this?

Not at all

Perfectly

How are table stages referenced?

How well did you know this?

Not at all

Perfectly

How are named stages referenced

How well did you know this?

Not at all

Perfectly

What are the acceptble file formats when creating a stage?

CSV, JSON, AVRO, ORC, PARQUET, and XML

How well did you know this?

Not at all

Perfectly

If the file format is specified in multiple locations, what is the order of precedence?

Copy into table statement
The Stage definition
The table definition

How well did you know this?

Not at all

Perfectly

What is the difference between the DESC and LIST commands on a stage

Desc is metadata on stage, list is the list of files staged successfully.

How well did you know this?

Not at all

Perfectly

How is Snowpipe costs calculated

Per Second/per-cpu core and then converts this into credits

How well did you know this?

Not at all

Perfectly

To load BROTLI compressed files, what should COMPRESSION be set to on the COPY INTO statement?

BROTLI, not AUTO

How well did you know this?

Not at all

Perfectly

What are the options for COMPRESSION on a COPY INTO statement

AUTO
GZIP
BZ2
BROTLI
ZSTD
DEFLATE
RAW_DEFLATE
NONE

How well did you know this?

Not at all

Perfectly

On the COPY INTO statement, what happens if a file is listed in FILES but does not exist

Snowflake follows the behavior set ON_ERROR, default is ABORT

How well did you know this?

Not at all

Perfectly

What is the upper limit for the number of file names you can list on FILES in a COPY INTO statement

1000

How well did you know this?

Not at all

Perfectly

What type of files can be set on TYPE on a COPY INTO statement

CSV
JSON
AVRO
ORC
PARQUET
XML

How well did you know this?

Not at all

Perfectly

What options are available on VALIDATION_MODE on a COPY INTO statement

RETURN_n_ROWS
RETURN_ERRORS
RETURN_ALL_ERRORS

How well did you know this?

Not at all

Perfectly

What is the difference between RETURN_ALL_ERRORS and RETURN_ERRORS on the VALIDATION_MODE in a COPY INTO statement?

They both return all errors across all the files in the COPY command, but ALL also returns files that were partially loaded during an earlier load.

How well did you know this?

Not at all

Perfectly

Where can you get history and see issues that occurred during a load

LOAD_HISTORY in the information schema

How well did you know this?

Not at all

Perfectly

In a COPY INTO statement, you might see
SKIP_HEADER = <n>
What is n?</n>

n refers to the number of lines to skip at the beginning of the file. The default value is 0.

How well did you know this?

Not at all

Perfectly

What are the options for ON_ERROR on a COPY INTO statement

CONTINUE
SKIP_FILE
ABORT_STATEMENT

How well did you know this?

Not at all

Perfectly

What is the default for ON_ERROR for COPY INTO

ABORT_STATEMENT

How well did you know this?

Not at all

Perfectly

What is the default for ON_ERROR for Snowpipe

SKIP_FILE

How well did you know this?

Not at all

Perfectly

What is the slowest of all the ON_ERROR options

SKIP_FILE
The action buffers the entire file whether errors are found or not. When loading large numbers of records from files with no logical delineation, consider CONTINUE instead

How well did you know this?

Not at all

Perfectly

With ON_ERROR set to ABORT_STATEMENT how are files explicitly set on FILES treated vs patterns?

Explicitly requested files are treated as requirements, while pattern-matched files are treated as “load what you can find.”

How well did you know this?

Not at all

Perfectly

If ON_ERROR is set to ABORT_STATEMENT, where do you troubleshoot issues

QUERY_HISTORY Since the data was not ingested, it will not be found in COPY_HISTORY

On a COPY INTO statement, what is num on SIZE_LIMIT =

Max size in bytes of data to be loaded, after which the COPY stops loading files. At least one file is loaded regardless of the value specified.

On a COPY INTO statement, how do you specify to get rid of a file from the stage once the load is successful

PURGE = TRUE

If a purge operation fails, is an error returned

How do you force a COPY INTO statement to load a file that has already been loaded?

FORCE = TRUE

What is the time window a duplicate file will not be loaded by default

64 days

When tracking a file's loaded state, what is LAST_MODIFIED

The timestamp of when the file was initially staged or when it was last modified, whichever is later

On a COPY INTO statement, what happens if text exceed the column length and TRUNCATECOLUMNS = FALSE

It will produce an error

What is the difference between TRUNCATECOLUMNS and ENFORCE_LENGTH on a COPY INTO statement

Truncate will truncate if set to true Enforce will truncate if set to false

When using Snowpipe, how do you get rid of a file after loading?

PURGE is not an option. To remove files, periodically execute REMOVE command

What two options are available to detect a file is available for Snowpipe

Cloud Messaging REST API

If a pipe is paused, how long are event notifications maintained

14 days. Older than that are processed on a best effort basis

The REST API for Snowpipe requires what for authentiction

JWT signed using RSA keys

How are files loaded in Snowpipe

FIFO is attempted but not guaranteed

What stages are supported for Snowpipe

Internal, External, and Table

Once data is staged, what endpoint should be used for data loading

insertFiles

For the REST endpoint insertFiles, what is the max number of files?

5000

For the REST endpoint insertFiles, what is the max file path for each file

1,024 bytes

For the REST endpoint insertFiles, what does a response 200 mean?

Success, files are added to the queue.

For the REST endpoint insertFiles, what does a response 400 mean?

Invalid format/limit exceeded

For the REST endpoint insertFiles, what does a response 404 mean?

Pipe name not recognized

For the REST endpoint insertFiles, what does a response 429 mean?

Request rate limit exceeded failure

For the REST endpoint insertFiles, what does a response 500 mean?

Internal error failure

What doe the REST endpoint insertReport do?

Returns a report of files recently ingested into Snowlake. Only shows files for a max of the last 10 minutes, and the 10,000 most recent

For the REST endpoint insertReport, what does a response 200 mean?

Success, list of files returned

For the REST endpoint insertReport, what does a response 400 mean?

Invalid file format/limit exceeded

For the REST endpoint insertReport, what does a response 404 mean?

Pipe name not recognized

For the REST endpoint insertReport, what does a response 429 mean?

Request rate limit exceeded failure

For the REST endpoint insertReport, what does a response 500 mean?

Internal error occured

What does the REST endpoint loadHistoryScan do?

Returns a report of files recently ingested into table and the view history between two points in time. Returns a max of 10,000 items, but multiple calls can be made.

For the REST endpoint loadHistoryScan, what does a response 200 mean?

Success, returns load history scan results

For Snowpipe all COPY INTO commands are support except for

FILES = (''...) ON_ERROR = ABORT_STATEMENT SIZE_LIMIT PURGE FORCE RETURN_FAILED_ONLY VALIDATION_MODE

In an Internal Stage, what encryption is used

AWS 256

When calling the Snowpipe API what is the difference between loadHistoryScan and insertReport

loadHistoryScan fetches a report about ingested files between two points in time

If you are trying to avoid the rate limit error code, 429, which should you use insertReport or loadHistoryScan

insertReport, calling every 8 minutes to get the last 10 minutes of history

For insertFiles, how long are events retained for

A maximum of 10 minutes

Schema on Read is good for what type of data

Semi-structure data like JSON

Schema on Write is good for what type of data

Structured and relational data

If an AWS Administrator accidentally deletes a SQS subscription to the SNS topic for a snowpipe, what will happen to the pipe which references the topic to receive event messages from Amazon S3?

The pipe will no longer be able to receive the messages. To restore the system immediately, the user needs to manually create a new SNS topic with a different name and then recreate the pipe by specifying the new SNS topic name in the pipe definition. Without the rename, you have to wait 72 hours and still recreate the pipe.

What happens in the COPY INTO statement if data is transformed and the validation_mode is set to return_all_errors

SQL compilation error: invalid parameter 'validation_mode' The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.

When is the parameter INTEGRATION from Snowpipe required?

When configuring AUTO_INGEST for Google Cloud Storage or Microsoft Azure stages.

Does Snowpipe have the USAGE privilege