Snowflake Parquet Data Transfer: 2 Easy Steps

on Data Storage, Data Warehouses, Snowflake • September 23rd, 2021 • Write for Hevo

Snowflake is a Software-as-a-Service (SaaS) platform that assists companies in building data warehouses. It runs in the Cloud and gives users the option of storing their data there as well. Snowflake’s infrastructure is incredibly flexible, and it’s compute and storage resources scale well to meet your evolving storage requirements.

You will be required to execute a variety of setups when utilizing Snowflake. For example, the size of your data warehouse must be configured, as this influences how quickly the warehouse can handle queries. At the same time, you’ll need to keep an eye on several aspects of your Snowflake data warehouse. Similarly, you will have to look after data transfer format. Apache’s Parquet data format is one of the most efficient data transfer formats. This is where Snowflake Parquet data transfer comes into the picture.

In this in-depth article, you will get to know about Snowflake, Apache Parquet along with the steps needed to carry out the Snowflake Parquet data transfer process.

Table of Contents

Introduction to Snowflake

Snowflake Parquet - Snowflake Logo
Image Source

With the increase in digitization across all facets of the business world, more and more data is being generated and stored. As the reliance of enterprises surges on Data-Driven Decision Making, Data Warehousing and subsequent processing have taken the center stage as an enabler of business growth. Snowflake is a Data Warehousing platform laying its foundations on Cloud Computing; established in 2012, Snowflake is primarily software as a service (SaaS), offering Cloud-Based Data Analytics and storage services. More specifically, it is “data warehouse as a service”, letting the corporate clients extract meaningful insights from tons of data to steer the organization based upon Data-Driven optimal decisions. 

What makes Snowflake stand out from other data warehouse solutions is that there is no hardware or software involvement (virtual or physical) to select, configure, install and manage. More so, the organization takes complete responsibility for maintenance, management, upgrades, and tunning of the platform to give the best performance results. Serving a variety of clients from almost all the business sectors, Snowflake empowers organizations to deliver a value-added user experience to their customers, thus facilitating customer acquisition and retention. 

Key Features of Snowflake

Snowflake provides a modern data architecture, having a host of innovative features and functionalities, which are discussed as follows:

1) Cloud Power Agnostic

Snowflake is a Cloud-agnostic solution i.e., can easily be integrated with all the Cloud service providers: GCP, AWS, and Azure. The best part is that Snowflake retains user experience while switching from one cloud platform to another. It is sort of a plug-and-play solution that can be deployed with current Cloud Architectures in all or selected domains. 

Snowflake Parquet - cloud power
Image Source

2) Scalability

Being a multi-cluster shared framework, Snowflake segregates the resources for Computation and Storage. It provides users the flexibility to scale up or down the resources as and when required without any interruption in the services. For ensuring minimal administration, Snowflake has been equipped with Auto-Scaling and Auto-Suspension features. The former provides the functionality of automatically starting or stopping the clusters in case of resource-intensive operations. The latter one can put a brake on the virtual warehouse whenever clusters have been idle for a pre-defined time duration. These features certainly enable Scalability, Flexibility, Efficiency, Cost Management, and Performance Optimization for the overall data warehousing solution.

Snowflake Parquet - scalability
Image Source

3) Concurrency & Workload Separability

While the traditional data warehousing schemes are characterized by the users and processes competing for resources, which generally result in concurrency issues, Snowflake’s multi-cluster architecture segregates the workloads to be administered by individual computer clusters, called Virtual Warehouses. Essentially, requests from one virtual warehouse would never impact the queries from another, hence allowing the running of data analysis operations, reports, and ETL/ELT processing without the competition for resources. 

4) Near-Zero Administration

As Snowflake is Data Warehouse as a service, it requires very little effort from the client’s database administrators or IT teams in setting up and managing the platform. Without the need for hardware or software configuration, there is no need for indexing the tables and tuning the databases. Software updates are managed by Snowflake’s team and are deployed without any downtime. 

5) Security

Snowflake offers a host of security features that guarantee the confidentiality, privacy, and integrity of the user’s data. Network policies can be administered by whitelisting IPs while multi-factor authentication and SSO support further complement the security architecture. Data access is controlled via a hybrid model of discretionary access control and role-based controls, while subsequent encryption provides a good level of control and flexibility.

For further information on Snowflake, check out the official website here.

Introduction to Apache Parquet

Snowflake Parquet - parquet logo
Image Source

As rapid advancements characterize modern Data Analytics and associated architectures, time to market has become the key Metric to gauge business success. In the domain of Analytics, data architecture plays a key role so that data sets can be arranged for achieving efficiency in the query results. 

Parquet, an open-source file format in the realm of Hadoop ecology, provides users the flexibility to arrange the datasets in Column-Oriented storage to achieve optimized data compression rates and encoding structures. Parquet can enhance the relevant platform performance to handle the data in bulk as Column-Based data can skip the non-relevant headers very quickly. Technically, Apache Parquet is based upon record shredding architecture and assembly algorithm framework which are far better in terms of performance in comparison with meek flattening of nested namespaces. 

Key Features of Apache Parquet

Key features of Apache Parquet are outlined as follows:

  • Apache Parquet is an open-source platform with free-of-cost access and is seamlessly compatible with the majority of the Hadoop data processing architectures.
  • Parquet provides users the functionality to compress the data files to minimize the processing load as well as storage requirements. As the compression function is performed column by column, different sorts of encoding can be used for string and integer data types. Data can be compressed by three frameworks in parquet: dictionary encoding, bit packing, and run-length encoding
  • Unlike the CSV files where data is arranged in rows format, Parquet files consist of row groups, headers, footers while row groups contain respective columns of data and metadata; this arrangement makes Parquet a self-describing format that is well-optimized for quick query fetching and high-performance benchmarks. 
Snowflake Parquet - feature
Image Source
  • As Parquet is developed from the ground up, it can process complex nested structures when dealing with large data volumes (typically in gigabytes) for individual files. 

Simplify ETL and Data Integration using Hevo’s No-code Data Pipeline

Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up data integration from Salesforce (one of the 30+ Free Data Sources) and 100+ Data Sources and will let you directly load data to a Data Warehouse or the destination of your choice. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.

Get Started with Hevo for free

Let’s look at some of the salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
  • Scalable Infrastructure: Hevo has in-built integrations for 100’s of sources that can help you scale your data infrastructure as required.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-day Free Trial!

Steps for Snowflake Parquet Data Transfer

To achieve the desired efficiency and optimization in querying the database, Snowflake Parquet data transfer can be done. It’ll entail the two steps:

Step 1: Import Data to Snowflake Internal Storage using PUT Command 

The first step in Snowflake Parquet data transfer is to use the PUT command. Via PUT command, one can upload the Parquet file onto the Snowflake internal storage. The destination can be a name, a table, an external stage, or a user stage:

@~<username> - is used to upload the Snowflake user stage. 
@%<tablename> - is used to upload to the Snowflake table stage.
@<name> - is used to upload to the name stage.

The following example uploads the emp.csv file to the internal table EMP stage. 

Syntax

PUT file:///tmp/data1_0_0_0.snappy.parquet @%EMP;
data1_0_0_0.snappy.parquet(0.00MB): [##########] 100.00% Done (0.241s, 0.01MB/s).

Output

Sourcetargetsource_sizetarget_sizeSource_compressiontarget_compressionstatusmessage
data1_0_0_0.snappy.parquetdata1_0_0_0.snappy.parquet14451445PARQUETPARQUETUPLOADED
Source
1 Row(s) produced. Time Elapsed: 2.068s

A use-case for name internal stage is given by:

Syntax

PUT file:///apps/sparkbyexamples/data1_0_0_0.snappy.parquet @~;

Another use-case with the name internal stage coupled with the path is given by:

Syntax

PUT file:///apps/sparkbyexamples/data1_0_0_0.snappy.parquet @~/tmp;

Step 2: Transferring Snowflake Parquet Data Tables using COPY INTO Command

The next step in Snowflake Parquet data transfer is to use the COPY INTO command. Once the data is successfully in the internal stage, now the user can easily transport it into the Snowflake tables via the COPY INTO command.

As an example, one can make a table EMP alongside one column of type Variant for Snowflake Parquet data transfer. 

Syntax

CREATE OR REPLACE TABLE EMP(PARQUET_RAW VARIANT)
COPY INTO EMP from (select $1 from @%EMP/data1_0_0_0.snappy.parquet)
 file_format = (type=PARQUET COMPRESSION=SNAPPY);
SourcestatusRows_parsedRows_loadederror_limiterrors_seenfirst_errorfirst_error_characterfirst_error_color_name
data1_0_0_0.snappy. parquetLOADED    5    5    1    0NULLNULLNULL
Source
1 Row(s) produced. Time Elapsed: 1.472s

The same statement can also be coded as:

COPY INTO EMP from @%EMP/data1_0_0_0.snappy.parquet
file_format = (type=PARQUET COMPRESSION=SNAPPY);

Users can then run the select and visualize the data accordingly.

Syntax

SELECT * FROM EMP;  

Output

+------------------------------------+
| PARQUET_RAW                        |
|------------------------------------|
| {                                  |
|   "_COL_0": "James",               |
|   "_COL_1": "Smith",               |
|   "_COL_2": 1.000000000000000e+04, |
|   "_COL_3": 10,                    |
|   "_COL_4": "M"                    |
| }                                  |
| {                                  |
|   "_COL_0": "Michael",             |
|   "_COL_1": "Jones",               |
|   "_COL_2": 2.000000000000000e+04, |
|   "_COL_3": 10,                    |
|   "_COL_4": "M"                    |
| }                                  |
| {                                  |
|   "_COL_0": "Robert",              |
|   "_COL_1": "Williams",            |
|   "_COL_2": 3.000400000000000e+03, |
|   "_COL_3": 10,                    |
|   "_COL_4": "M"                    |
| }                                  |
| {                                  |
|   "_COL_0": "Maria",               |
|   "_COL_1": "Jones",               |
|   "_COL_2": 2.347650000000000e+04, |
|   "_COL_3": 20,                    |
|   "_COL_4": "F"                    |
| }                                  |
| {                                  |
|   "_COL_0": "Jen",                 |
|   "_COL_1": "Mary",                |
|   "_COL_2": 1.234450000000000e+05, |
|   "_COL_3": 20,                    |
|   "_COL_4": "F"                    |
| }                                  |
+------------------------------------+
5 Row(s) produced. Time Elapsed: 0.667s

You have successfully established the Snowflake Parquet data transfer process.

Conclusion

The use of Snowflake Parquet data transfer certainly enhances the applicability, performance, and functionality of the data warehousing platform. Achieving these benchmarks becomes even more crucial when dealing in the realm of Cloud-Based architectures; the subject arrangement not only saves cost but also delivers optimal value as far as Data-Driven Modeling is concerned. Carrying out the Snowflake Parquet data transfer process manually can be challenging. This is where Hevo saves the day. 

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouse, Database, or a destination of your choice like Snowflake. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about Snowflake Parquet Data Transfer! Let us know in the comments section below!

No-Code Data Pipeline for Snowflake