Loading Files into Snowflake: Snowsight, Stages and COPY INTO

1.Loading files through Snowsight

Snowsight is the point-and-click way to load data. It takes files from your local computer or from a stage that already exists. The formats it accepts are the same ones COPY reads: CSV and TSV for structured data, and JSON, Avro, ORC, Parquet or XML for semi-structured data. There are two hard limits: at most 250 files per upload, and at most 250 MB per file. Above either limit, the documentation sends you to the Snowflake CLI or the SnowSQL client.

You can start from two places. Create » Table » From File builds a new table from the file. Snowsight runs INFER_SCHEMA over the file and shows the inferred file format, column names and data types for you to review before you select Load. Ingestion » Add Data » Load data into a Table loads into a table that already exists. On its Edit Schema page you pick a file format and decide what happens on error. By default no data is loaded from a file that contains an error. You also pick a loading method, either Append (the default) or Replace, and a Match by column names option, which is case insensitive by default. A third route is the Load Data wizard on a table's page under Databases. It asks you for a warehouse, and if that warehouse is suspended you may wait up to 5 minutes for it to resume before the data loads.

There are two exceptions to building a table from a file. You can't create a new table from an XML file, and you can't create a new Apache Iceberg table during a load. In both cases you create an empty table first and then load into the existing table.

Sources1

2.Loading from internal and external stages with COPY INTO

Outside the UI, COPY INTO <table> does the loading. There are three kinds of internal stage: a named internal stage, a table's own stage and your user stage. You put local files into any of them with the PUT command. A named external stage holds the URL and credentials for an Amazon S3, Google Cloud Storage or Azure location. COPY can also read a raw external URL directly. One storage limit to remember: COPY can't read files in archival storage classes that need to be restored first, such as S3 Glacier Flexible Retrieval or Azure Archive Storage. The prefix in the FROM clause tells COPY which kind of location it is reading.

A few details come up often in exam questions. The path is a case-sensitive prefix, so @stage/data/files matches every file whose name starts with that string. If you are loading from the table's own stage, you can leave out the FROM clause altogether. The FROM value must be a literal; a SQL variable won't work. To narrow the file set, use FILES for an explicit list or PATTERN for a regular expression:

COPY INTO mytable FILE_FORMAT = (FORMAT_NAME = myformat) PATTERN='.*sales.*[.]csv';

COPY can also transform data while it loads. Instead of naming the stage directly, you give COPY a SELECT over the staged columns ($1, $2, and so on). That lets you reorder columns, pick only some of them, or pull single elements out of a semi-structured value. In the transformation syntax, the inner FROM takes only internalStage or externalStage. A raw cloud URL isn't accepted there.

COPY INTO [<namespace>.]<table_name> [ ( <col_name> [ , <col_name> ... ] ) ]
     FROM ( SELECT [<alias>.]$<file_col_num>[.<element>] [ , [<alias>.]$<file_col_num>[.<element>] ... ]
            FROM { internalStage | externalStage } )

By default, COPY leaves the source files where they are after loading. Set PURGE = TRUE to remove files that loaded successfully. This needs write access to the bucket or container that holds them.

Sources2

3.Structured, semi-structured and unstructured data

Structured data such as CSV is the simplest case. Every file column lines up with a table column, and a file format (named, or written inline as TYPE = CSV) describes delimiters, headers and NULL handling. Semi-structured data is different because it has no fixed schema and new attributes can appear at any time. A common pattern is to land each JSON document in a single VARIANT column. In the example below, STRIP_OUTER_ARRAY turns a JSON array into one row per element:

CREATE OR REPLACE FILE FORMAT json_format TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE; /* Create an internal stage that references the JSON file format. */ CREATE OR REPLACE STAGE mystage FILE_FORMAT = json_format; /* Stage the JSON file. */ PUT file:///tmp/sales.json @mystage AUTO_COMPRESS=TRUE; /* Create a target table for the JSON data. */ CREATE OR REPLACE TABLE house_sales (src VARIANT); /* Copy the JSON data into the target table. */ COPY INTO house_sales FROM @mystage/sales.json.gz;

If you want typed columns from semi-structured files, MATCH_BY_COLUMN_NAME maps field names in the file to column names in the table. With this option the order of fields in the file doesn't have to match the order of columns in the table. It is the same matching that Snowsight's Match by column names setting offers.

COPY INTO mytable FROM @my_ext_stage/tutorials/dataloading/sales.json.gz FILE_FORMAT = (TYPE = 'JSON') MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE';

Unstructured data, such as images or PDFs, doesn't go through COPY into rows. The files stay on a stage, and you add a directory table to that stage, either in CREATE STAGE or later with ALTER STAGE. Internal and external stages both support directory tables. A directory table isn't a separate database object and has no privileges of its own. It records metadata for each file: its size, when it was last modified, and its Snowflake file URL. You query it to list the files, join it with other tables to build views, or use it as the start of a file-processing pipeline. When files on the stage change, you refresh the directory table's metadata.

Sources324

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Snowsight can create a new table automatically from any supported file, XML included.Why is that wrong?

   Snowsight can't create a new table from an XML file. Create an empty table first, then load the XML into it as an existing table.

   Covered in Loading files through Snowsight

2. 2.Every COPY INTO <table> needs a FROM clause that names the stage.Why is that wrong?

   When the files are in the table's own stage, you can leave out FROM. Snowflake checks that stage automatically.

   Covered in Loading from internal and external stages with COPY INTO