Loading Data to Snowflake: Best Practices

Editor’s note: This article has been updated for accuracy and comprehensiveness in November 2023.

The IDS research predicts 80% of data to be unstructured by 2025. Hence it means that modern businesses need digital data transformation to analyze it correctly and quickly. For now, the Snowflake is one of the real storm makers in this area. It looks like the company is skyrocketing, and more and more businesses are using it. Here we’ll dive deep and figure out its popularity and the capabilities, advantages, and possible pitfalls of data loading to Snowflake.

Table of Contents

1. Pros and Cons of Snowflake
2. Data Ingestion Techniques in Snowflake
3. Data Loading to Snowflake Using Web Interface
4. Data Ingestion using the SnowSQL client
5. Getting Data Into Snowflake Using Snowpipe
6. Loading Data to Snowflake Using Skyvia Cloud Solution
7. Load Data from CSV to Snowflake
8. Load Data from SQL Server to Snowflake
9. Load Data from Amazon S3 to Snowflake
10. Load Data from Oracle to Snowflake
11. Load Data from Microsoft Azure Blob to Snowflake
12. Conclusion

Pros and Cons of Snowflake

Snowflake is a cloud-based data warehousing tool using a three-layered architecture:

• Database storage layer.
• Computer layer.
• Cloud services layer.

It supports many data types (structured and semi-structured), file formats (CSV, JSON, and Parquet), and methods to load data into the system (COPY command, Snowpipe, and bulk loading). 

So, let’s see the platform’s main advantages and pitfalls.

Advantages

• Users can quickly scale resources to match data needs and not lose performance.
• A virtually unlimited workload, providing companies with excellent performance and scalability.
• The easy-to-implement and cost-efficient cloud-first approach: pay for the storage and computing time used.
• Security and compliance cloud storage for any data type, including JSON, CSV, and Parquet, helps to protect your data.
• Data caching improves query performance and saves costs for hardware upgrades.
• With its cloud-agnostic nature, the solution can run in public clouds like Google, Microsoft, and Amazon, which is pretty good for different cloud environments.

Pitfalls

• The load time can be a bit slow.
• The lack of data limits – if your business has unpredictable data growth, you may pay more than expected.
• Lack of unstructured data support.

Data Ingestion Techniques in Snowflake

Depending on the task and data volume, you can upload data to Snowflake in several ways and solutions. Here we’ll review each data ingestion tool and find advantages and pitfalls to help you select the best one for your business:

• Web interface.
• SnowSQL client.
• Snowpipe.
• Skyvia cloud solution.

Data Loading to Snowflake Using Web Interface

The manual data ingestion into Snowflake via the web interface is the simplest one and just needs a few steps to follow:

1. Login to your Snowflake account, and create a new table or select an existing one to load the data into.
2. In the Table details already created, click the Load button to open the Data Wizard for loading the desired file into your table, and select the data warehouse you need.
3.  Select the local files or choose a cloud storage location from Amazon S3, Microsoft Azure, Google Cloud Platform, etc.
4. Open the dropdown list and choose the file format, or create a new named one.
5. At last, set the load options in case of an error, and click the Load button to allow Snowflake to load the data to the table you need via the selected warehouse.

As you can see, the approach is simple and functional enough, but it has at least one pitfall: the number of files to load, and their size is quite limited (just up to 50 MB to support).

Data Ingestion using the SnowSQL client

This approach fits for more advanced users. You may bulk load data using SQL from any delimited text file, including comma-delimited CSV files. Additionally, SnowSQL allows bulk upload of semi-structured data from JSON, AVRO, Parquet, or ORC files.

 With SnowSQL, you can upload data into Snowflake in a few steps:

1. Prepare data for loading. Just ensure that the data is in a compatible format (CSV, JSON, Parque, etc.) for Snowflake and that any necessary transformations or cleaning have been performed.
2. After the successful data preparation, create a stage for loading it. In this case, it means some temporary location to store files before loading them into a table. By default, each user and table is allocated an internal stage to host data files, but users can also create named locations for more granular control over data loading.
3. Use the PUT command to upload data from local storage or the cloud to a stage.
4. When the data is staged successfully, load it into a table using the COPY command.

So, the COPY feature is a benefit when talking about loading data into a Snowflake table. Let’s see why:

• It supports many options for transforming and manipulating data during loading, including reordering and indenting columns. 
•  The COPY INTO command allows downloading data packages from files already available in cloud storage or copying data from a named stage. 

The possible pitfall here is that the method already supports large files format but cannot constantly exchange data.

Note: Snowflake also supports continuous data loading with the STREAM command, allowing you to receive real-time data from external systems.

Getting Data Into Snowflake Using Snowpipe

Ok, imagine that you have some data files available in the stage, and of course, you need to download them to Snowflake. Excellent – just use Snowpipe. The pipe here is the first-class object containing Snowflake’s COPY instruction. If you work with high-frequency or streaming data, that’s your solution.

The steps for setup Snowpipe:

• Setup of the separate database and schema for source data holding.
• Creation of a table for storing the data, followed by creating the file format. 
• The Snowpipe creation and the cloud storage event notifications’ configuration.

 All these steps allow the data to load from files and micro-butches, so your data warehouse will always be up-to-date.

Thus, this method already assumes permanent automated exchange, but there are a few pitfalls:

• Incomplete or incorrect data formatting (if so, use the VALIDATE command after using the COPY INTO to return all errors).  
• Insufficient permissions or credentials (To avoid this, run the CREATE THREAD request with the privileged user OWNERSHIP and ensure that the role is granted the necessary access rights).
• Network connectivity and latency issues while the data loading (if so, check the network connectivity stability and latency according to the acceptable limits).

Loading Data to Snowflake Using Skyvia Cloud Solution

Skyvia is a cloud-based, no-code ETL, ELT, and Reverse ETL non-technical user-friendly platform. The data loading and processing here are automated and highly secured.

Using Skyvia Cloud Data Platform, you can successfully upload data into the Snowflake data warehouse from 180+ ready-made data connectors and vice versa.

Note: Skyvia is a flexible enough solution allowing you to select the best data-loading story you’ve ever needed. The line is from the simplest one, like Import to the business “Lego” as Data Flow is. 

Skyvia provides a lot of widespread data integration to Snowflake scenarios. For instance, you can load data from: 

• CSV.
• SQL Server.
• Amazon S3.
• Oracle.
• Microsoft Azure Blob.

There are just a few, but let’s review each and find its advantages and pitfalls.

Load Data from CSV to Snowflake

How to load data from CSV to Snowflake with the Skyvia solution? Ah, that’s simple; just go step by step:

• First, sign into your account and select the data integration package (lmport or Data Flow).  

Note: if you wish to load data from the file storage, select From storage service option under CSV source type, and choose or create a file storage connection.

• The next step is to set up mapping and transformation. Under the source section, select the data source database or cloud app and choose the connection from the dropdown list.
• After performing this, select the CSV file for loading and the table you want to import data to. Here you can also combine data from multiple sources and load data to various destinations simultaneously.
• Run the data integration process. (That means the data writing into multiple temporary CSV files and uploading them to Amazon S3 or Azure). You can use the scheduling option here to save time and costs. 
• Then, the data will be imported into your Snowflake data warehouse. 

Advantages

• Skyvia is a no-code cloud tool that can automate CSV data import and export to/from Snowflake by schedule.  
• The platform lets you quickly load data from cloud applications or databases to Snowflake and keep your Snowflake up-to-date with fresh data from other sources. 
• Skyvia also offers a free plan to load up to 10k monthly records between data sources directly and 100k records for importing/exporting CSV files.

Pitfalls

• Possible lack of sources (180+).

Load Data from SQL Server to Snowflake 

With Skyvia Cloud Solution, you can also load data from SQL Server to Snowflake in three simple steps:  

1. Connect both the SQL Server and Snowflake accounts to Skyvia. To do it, provide the required parameters such as domain, database, user name, and passwords.
2. Once the accounts are connected, create the data integration package. To do it, click the +New button on the Skyvia dashboard and choose the data integration option (Import, Synchronization, or Data Flow).
3. The last step is mapping configuration and data transfer execution.

Advantages

• You can fast and easily integrate the SQL Server data with Snowflake without coding and ensure that your data remains up-to-date and accurate. 
• With Skyvia cloud ETL tools for cloud data integration, you can import, export, replicate, and synchronize your data. 
• With the Skyvia Data Import solution, you can import data from various Snowflake objects at the same time.  

Pitfalls

•  The transfer speed might be a bit challenging for extensive data volume processing.

Load Data from Amazon S3 to Snowflake

With Skyvia, you can manage and automate your import and export of CSV data between Snowflake and Amazon S3 on a schedule. How to do it? Follow the three steps:

1. Connect Skyvia to Amazon S3 and Snowflake. You can set the connection up manually or use built-in tools to access S3 data.
2. When both systems are successfully connected to Skyvia, select the source, target objects for the data transfer, and scenario (Import, Synchronization, or Data Flow). 
3. Map the source and target fields in the next step after selecting the source and target objects. This step is necessary to ensure accurate data transfer from Amazon S3 to Snowflake with all the required formatting and transformation. The solution also allows the data transferring scheduling to provide automatic data loading into Snowflake regularly.

Advantages

• Serverless, automated data input from S3 to Snowflake is possible by configuring automatic data loading using Snowpipe. So, risks of errors with human impact slow down once the efficiency and accuracy are increasing. 
• Here, your data is constantly up-to-date, which is pretty helpful in making the right decisions.
• To ensure that the data is accurate and usable, you may control all the transformations while the data is being loaded from S3 to Snowflake.

Pitfalls

• The data transferring speed might be a bit slow for large data amounts.

Load Data from Oracle to Snowflake

How to easily upload your data from Oracle to Snowflake with Skyvia Cloud Solution? Just do these three steps:

1. Connect Oracle and Snowflake to Skyvia first. Provide the database server hostname or IP address, the service name, and the port to run on for both services. Then enter the username and password to establish the connection and integration scenario of your choice (Import, Synchronization, or Data Flow). 
2. Configure data mapping and transformation in Skyvia to ensure proper data alignment with the target Snowflake schema. You can load Oracle data directly to/from major cloud and on-premise data sources or synchronize data in both directions. Import packages also allow you to load data to a cloud app or database in one direction.  
3. Finally, schedule and execute the data transfer process. 

Note: Scheduling the data transfer jobs might help ensure the in-time data loading to Snowflake.

Advantages

• Skyvia’s no-code cloud service provides you with abilities of ETL/ELT & Reverse ETL performing, building the data pipelines, sharing data via REST API, etc.
• Skyvia’s mapping and transformation capabilities ensure your data is appropriately transformed and aligned with the target schema.

  Pitfalls

• Handling large data volumes at high speed may be a potential pitfall, but the files’ (100-250 MB or more significant) compressing helps to avoid it.

Load Data from Microsoft Azure Blob to Snowflake

To easily load your data from Microsoft Azure Blob to Snowflake using the Skyvia platform, please, follow the three simple setup steps:

1. Connect Microsoft Azure Blob and Snowflake data sources in the Skyvia interface.
2. Once all the systems are connected successfully, select the source and destination for the data transfer and the integration scenario (Import, Synchronization, or Data Flow).
3. The last step is configuring data transfer mapping and transformation rules. In other words, in this step, you have to specify the data source: destination, mapping, and transformation scenarios to complete it quickly and efficiently.

Advantages

• Skyvia’s automation and scheduling capabilities can save time, money, and effort.

 Pitfalls

• Connectivity and authentication settings errors may be a potential issue, so please configure your credentials correctly to avoid it. 

Conclusion

Let’s summarise it all. As you can see from above, the Skyvia platform offers the simplest three-step solution for saving your time and money, making it a pretty nice choice as a tool for loading data to Snowflake.

Of course, Skyvia offers a set of advantages for the data-loading process, like:

• Automation. 
• Safety & security.
• Accurate data mapping & transformation.
• Scheduling capability.
• Timely data refresh.

But its primary benefit is that Skyvia is the no-code, no-complicated, and user-friendly solution. Want to see Skyvia in action? Just schedule a demo.