Loading Structured and Semi-Structured Data

1. Loading Structured and Semi-Structured Data

In this video, we'll cover the main methods for getting data into Snowflake — when to use each one, how stages work, and how to handle both structured and semi-structured data.

2. The Loading Workflow

Before we get into the detail, let's walkthrough the typical workflow for loading data into Snowflake. Your files — CSV, JSON, or any other format - go into a stage first. That stage is either internal, managed by Snowflake inside your account, or external, pointing to cloud storage you already manage like an S3 bucket, GCS, or Azure Blob. Either way, nothing goes directly into a table. Files land in the stage, and then you execute `COPY INTO` to load them. A file format object (defined separately) tells the `COPY INTO` command how to parse what it is reading. We'll dive deeper into each step in the rest of the video.

3. What is a Stage?

Before you can use COPY INTO, you need a stage. A stage is a temporary storage location where your files sit before they're loaded into a table. There are two types as we just mentioned: Internal and External. To create an internal stage, you execute `CREATE STAGE` followed by the stage name. To create an external stage, you also execute `CREATE STAGE`, then add a `URL` to your cloud storage and a storage integration with related parameters so Snowflake can access that location securely. Either way, the pattern is the same: files land in the stage first, then COPY INTO moves them into the table.

4. Loading data into Snowflake

There are three main ways to get data into Snowflake. COPY INTO is the workhorse — designed for bulk loading from a stage, handles structured and semi-structured formats, and scales well. This is the method you'll use most. INSERT works for adding a small number of rows directly without staging anything first. Fine for one-off additions but not for large datasets. The Snowsight UI loading wizard is the right choice for non-technical users or quick one-off loads where writing SQL isn't necessary.

5. Inspecting a Stage with LIST

Once your stage is created, inspect its contents with LIST. The @ symbol before the stage name tells Snowflake you're referencing a stage, not a table. You'll see that pattern throughout loading and unloading operations. LIST returns every file in the stage: name, size, and last modified timestamp. It's a simple check before running COPY INTO — confirming the right files are there before you commit to loading them.

6. Loading Structured Data with COPY INTO

COPY INTO takes three things: the destination table, the source file in the stage, and a file format definition. The file format is where most loading errors come from — wrong delimiter, missing header skip, or mishandled quoted fields. The output tells you whether the file loaded successfully and how many rows were processed.

7. Loading and Querying Semi-Structured Data

Semi-structured data like JSON loads into a special Snowflake column type called VARIANT. VARIANT can hold any JSON structure without needing to define the schema upfront, instead Snowflake stores it as-is. Loading works the same way as CSV, just with a different file format type. Querying it is where the syntax changes, you use colon notation to navigate the JSON structure, then cast the value to the type you need. It looks unfamiliar at first but it's consistent — colon to navigate, double colon to cast.

8. Loading with the UI

For one-off loads or situations where SQL isn't practical, Snowsight has a built-in loading wizard. You'll find it under the quick actions menu on the home screen: Upload local files. Select your table, upload your file, configure the format options — no SQL required. It's not the right tool for production pipelines, but it's genuinely useful for getting data in quickly during exploration, or when a non-technical stakeholder needs to load a file themselves.

9. Let's practice!

Time to put your knowledge to the test!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.