Connecting to Snowflake (Classic Console)

The Classic Console offers the ability to execute tasks typically carried out using SQL and the command line. These tasks include:

1. Creation and management of users and other account-level objects (requires the appropriate administrator roles).
2. Establishment and utilization of virtual warehouses.
3. Creation and modification of databases and all associated database objects (schemas, tables, views, etc.).
4. Data loading into tables.
5. Submission and monitoring of queries.
6. Password changes and adjustment of user preferences.

For guidance on transitioning from the Classic Console to Snowsight, please refer to the “Upgrading to Snowsight” documentation.

Upgrading to Snowsight

About Snowsight:

Snowsight introduces enhanced and expanded functionality within the user interface, encompassing the following:

1. Granular usage views for effective cost governance.
2. One-click capability to enable database replication to other regions.
3. Comprehensive management of users, accounts, and privileges.
4. Worksheet organization through folders.
5. Autocomplete feature for database objects and SQL functions in worksheets.
6. Dashboards equipped with built-in visualizations.
7. Collaboration and sharing functionalities for worksheets and dashboards.
8. Improved data sharing capabilities, including sharing with other accounts or publicly on the Snowflake Marketplace through listings.

It’s noteworthy that no significant new features have been introduced in the Classic Console since April 2022.

Snowsight is Set as the Default for an Account:

When Snowsight is designated as the default interface for an account, individual users lose the ability to set a default web interface within their user profiles. Despite this, all users maintain access to the Classic Console after logging in to Snowsight.

Please note:

As part of behavior change bundle 2023_08, customers with a Capacity commitment have the option to enable the bundle, setting Snowsight as the web interface for their account.

For additional information regarding the impact of this change, refer to Snowsight: Default interface for all users in Standard Edition accounts (Pending).

To understand more about the behavior change bundle process, consult About Behavior Changes.

You Can Choose the Default Web Interface for Your User Profile:

If Snowsight isn’t the default web interface for all users in your account, you have the option to designate it as the default for your user profile:

1. Log in to Snowsight.

2. Click on your username and choose “Profile.”

3. Under “Default Experience,” opt for Snowsight.

4. In case the option to choose a default experience isn’t visible, it’s likely that your default web interface is set at the account level. Refer to “Snowsight is Set as the Default for an Account.”

5. Click “Save.”

Once you’ve set Snowsight as the default for your user profile, you can still access the Classic Console if necessary by using the Classic Console option in the menu.

Please be aware:

Due to modifications introduced in behavior change bundle 2023_04, every new user added to a Snowflake organization will have Snowsight configured as the default experience for their user profile. Further details can be found in the document Snowsight: Default Interface for New Users.

Getting Started With Snowsight

This section provides an introduction to getting started with Snowsight, the web interface for Snowflake. If you are looking to transition from the Classic Console to Snowsight, please consult the “Upgrading to Snowsight” guide.

Browser Requirements:

Snowsight is compatible with the most recent three major versions of the following web browsers:

Apple Safari for macOS
Google Chrome
Microsoft Edge
Mozilla Firefox

Accessing Snowsight Through a Proxy or Firewall:

To establish access to Snowsight via a proxy or firewall, it may be necessary to include the fully qualified URL and port values in the configuration settings of the proxy servers or firewall.

To identify the fully qualified URL and port for Snowsight, examine the SNOWSIGHT_DEPLOYMENT entry within the return value of the SYSTEM$ALLOWLIST function.

Signing in to Snowsight:

You have the option to connect to Snowsight either via the Internet or by utilizing private connectivity to the Snowflake service.

Using the Internet:

To access Snowsight via the public Internet, follow these steps:

1. Open a supported web browser and go to https://app.snowflake.com.

2. Enter your account identifier or account URL. If you’ve logged into Snowsight before, you may see an account name that you can choose.

3. Sign in using your Snowflake account credentials.

Alternatively, you can access Snowsight from the Classic Console:

Log in to the Classic Console.

In the navigation menu, choose Snowsight.

Snowsight will open in a new tab.

Using Private Connectivity:

Upon completing the setup for private connectivity, follow these steps to access Snowsight:

To sign in directly to Snowsight using private connectivity, especially if you haven’t logged in to the Classic Console before:

Enter one of the following URLs into the browser location bar:

https://app-orgname-account_name.privatelink.snowflakecomputing.com
https://app.cloud_region_id.privatelink.snowflakecomputing.com
(Replace orgname with your Snowflake organization’s name, account_name with your account’s unique name, and cloud_region_id with the cloud region identifier.)

After signing in, you can retrieve these details by hovering over an account in the account selector. Refer to “Format 1 (Preferred): Account Name in Your Organization” for more information.

Note: If you are uncertain about the values to enter, please consult your internal Snowflake administrator before reaching out to Snowflake Support.

Enter your Snowflake credentials.
If you prefer to sign in to Snowsight through the Classic Console using private connectivity to the Snowflake service:

Log in to the Classic Console.

In the upper-right corner of the Classic Console, select “Snowsight.”

Snowsight will open in a new tab or window.

Signing in to a Different Snowflake Account:

Navigate to the bottom of the left navigation bar and utilize the account selector to log in to an alternative account. The selector conveniently displays accounts you have accessed previously.

Using Snowsight:

This section offers a concise introduction to navigating and utilizing Snowsight, including examples and explanations of the following interface components:

1. Left navigation
2. User menu
3. Account selector

Setting User Details and Preferences:

To access your user profile, click on your username and choose “Profile.”

Within your profile, you can view and adjust the following user details:

– Profile photo
– Username (unchangeable)
– First name
– Last name
– Password
– Email

Ensure your user profile includes a first name, last name, and email address, as certain Snowflake features, like accepting the terms of service for the Snowflake Marketplace, may require this information.

You can also configure the following preferences:

1. Default experience:

Choose whether to start in Snowsight or the Classic Console upon signing in. If the option to select a default experience is not visible, your default experience might be set at the account level.

2. Language:

Set the language for Snowsight. Snowflake currently supports English (US) and Japanese (日本語).

3. Notifications:

Indicate whether to receive a browser notification upon the completion of a background query. When setting this preference for the first time, your browser will prompt you to allow notifications from Snowflake.

4. Multi-factor authentication:

Decide whether to enable multi-factor authentication (MFA), which is supported by the Duo Security service.

5. Session Timeout:

Maintain a client session as long as user activity is detected. After a period of inactivity (4 hours), the session will terminate, requiring you to sign in again.

Switching Your Active Role:

When utilizing Snowsight, you have the flexibility to alter the active role within your ongoing session. Your active role dictates the visibility of databases, tables, and other objects, as well as the actions you can undertake.

To change your active role:

1. Navigate to the user menu at the top of the left navigation bar.

2. Hover over your current active role to reveal the role selector.

3. Choose the desired role that you wish to activate.

For additional information on access roles and object privileges, refer to the Overview of Access Control.

Configuring Private Connectivity for Snowsight:

Before establishing private connectivity for Snowsight, it’s essential to first configure private connectivity for your Snowflake account. Refer to the dedicated guide based on the cloud platform hosting your Snowflake account—whether AWS, Azure, or Google Cloud Platform.

To enable private connectivity with Snowsight, follow these steps:

1. Utilizing the ACCOUNTADMIN role, invoke the SYSTEM$GET_PRIVATELINK_CONFIG function within your Snowflake account. Identify the values for the following:

– privatelink-account-url
– snowsight-privatelink-url
– regionless-snowsight-privatelink-url

2. Verify that your DNS settings can resolve these values.

3. Confirm connectivity to Snowsight using each of these URLs from your web browser.

If you wish to use the account name URL (regionless-snowsight-privatelink-url) as your primary access point to Snowsight, contact Snowflake Support. Request that all URL redirects be directed to the specified URL for regionless-snowsight-privatelink-url.

Snowsight: The Snowflake Web Interface

Snowsight simplifies Snowflake’s robust SQL capabilities into a cohesive, user-friendly experience. Employ Snowsight for essential Snowflake operations, including:

1. Crafting and executing queries.
2. Monitoring query performance and copy history.
3. Generating and overseeing users and other account-level entities.
4. Establishing and utilizing virtual warehouses.
5. Establishing and altering databases and all associated database objects.
6. Collaborating on data with other Snowflake accounts.
7. Navigating listings and sharing data publicly on the Snowflake Marketplace.

Utilize Snowsight to administer your account and user profile. This includes tasks such as enabling multi-factor authentication, updating your password, and managing your user preferences.

Connecting with a Snowflake Partner

To set up a trial account with any Snowflake partner currently available in Partner Connect, follow these steps:

1. Log in to either Snowsight or the Classic Console.

2. Activate the ACCOUNTADMIN role in the interface:

– In either interface, click the dropdown menu next to your login name.
– Choose “Switch Role » ACCOUNTADMIN” to switch to the account administrator role.

3. Access the Partner Connect page:

– For Snowsight, select “Admin » Partner Connect.” This opens the Partner Connect page.
– In the Classic Console, click on the “Partner Connect” tab, and the Snowflake Partner Connect page opens.

4. Click on the corresponding tile for the partner you want to connect with.

A dialog will appear, displaying the requirements for connecting to the partner and a list of objects automatically created in Snowflake during the connection process. This includes an empty database, warehouse, default user, and custom role. These objects are utilized by the partner application when interacting with your account.

5. Optionally, specify one or more existing databases in Snowflake to automatically use with the trial. This action creates an additional custom role, making existing data in Snowflake quickly available to the partner application.

If you don’t specify databases during the initial connection, you can do so later, but it will be a manual task.

Important Note: The Classic Console does not currently support specifying shared databases (databases shared from provider accounts to your account) for your Partner Connect trial during the initial connection process. If you choose a shared database, the Classic Console will return an error when you click the Connect button to complete the process.

To use shared databases with a trial:

– Utilize Snowsight to finish the initial connection process.
– Manually specify the shared database after the process completes.

6. Click the Connect button below the partner description to initiate the creation of a trial account with the partner and connect the partner application to Snowflake.

Once the process is complete and the objects are created, the partner tile will be updated with a checkmark.

Connecting to Snowflake (Snowflake Partner Connect)

Partner Connect simplifies the process of generating trial accounts with designated Snowflake business partners and seamlessly integrating these accounts with Snowflake. This functionality offers a convenient avenue for experimenting with different third-party tools and services, allowing you to adopt the ones that align most effectively with your business requirements.

Supported Partners:

Crucial Information:

Snowflake does not establish or dictate the conditions or terms (such as duration, supported features, etc.) for partner trial accounts. These policies are determined independently by each Snowflake partner and may differ among partners.

For specific details regarding a particular trial, kindly reach out to the respective partner directly.

Currently, Partner Connect includes the following partners:

– alation
– alteryx
– census
– coalesce
– dataops
– dbt Labs
– Fivetran
– Hightouch
– Sigma
– zepl

Connecting to Snowflake (Native Programmatic Interfaces)

Snowflake facilitates the development of applications using a variety of popular programming languages and development platforms. By leveraging the native clients (connectors, drivers, etc.) offered by Snowflake, you have the flexibility to develop applications through any of the following programmatic interfaces:

– microsoft.net
– ODBC
– php
– Python
– SQLAcademy

Connecting to Snowflake (SQL Development & Management)

Snowflake offers the following native SQL development and data querying interfaces:

1. Snowsight Worksheets

– Browser-based SQL development and editing.
– Requires no installation or configuration.
– Supports multiple, independent working environments that can be opened, closed, named, and reused across multiple sessions, with all work automatically saved.

2. SnowSQL

– Python-based client for executing all tasks in Snowflake, including querying, executing DDL/DML commands, and bulk loading/unloading of data.
– Download the installer from the SnowSQL Download page.

3. Snowflake SQL Extension for Visual Studio Code:

– Snowflake provides an extension for Visual Studio Code, allowing users to write and execute Snowflake SQL statements directly in VSC.
– Install the extension directly from within Visual Studio Code or indirectly by downloading a specific version.

Moreover, Snowflake seamlessly integrates with various third-party SQL tools for managing the modeling, development, and deployment of SQL code in Snowflake applications. This includes, but is not limited to:

– Dataops (We are partners!)
– aginity
– seekwell
– Statsig

Please be aware:

The provided list does not encompass all SQL management tools compatible with Snowflake; rather, it includes validated tools known to work effectively with Snowflake. While other tools may be employed alongside Snowflake, we cannot assure seamless interoperability of all features and functionalities in these third-party tools with Snowflake.

Connecting to Snowflake (Security, Governance & Observability)

Security and governance tools play a crucial role in safeguarding an organization’s sensitive data, preventing unauthorized access, tampering, and ensuring compliance with regulatory standards. These tools are often utilized alongside observability solutions or services to grant organizations insight into the status, quality, and integrity of their data, helping identify potential issues.

Collectively, these tools facilitate a diverse array of operations, including risk assessment, intrusion detection, monitoring, notification, data masking, data cataloging, data health and quality checks, as well as issue identification, troubleshooting, resolution, and more.

The subsequent list highlights security, governance, and observability tools and technologies acknowledged for their native connectivity to Snowflake:

– Acryl Data
– Alation
– atlan
– Data Dog
– Informatica
– OKERA

Connecting to Snowflake (Machine Learning & Data Science)

Also known as advanced analytics, artificial intelligence (AI), and “Big Data,” machine learning and data science encompass a wide range of vendors, tools, and technologies that offer sophisticated capabilities for statistical and predictive modeling.

While these tools and technologies may share some features and functionality with BI tools, their emphasis is less on analyzing and reporting past data. Instead, they concentrate on scrutinizing extensive datasets to identify patterns and extract valuable business insights that can be utilized to forecast future trends.

The subsequent list highlights machine learning and data science platforms and technologies that are recognized for their native connectivity to Snowflake:

– Alteryx
– databricks
– tellius
– zepl
– HEX

Connecting to Snowflake (Business Intelligence (BI)

BI tools empower executives and managers to analyze, discover, and report on data, facilitating more informed business decision-making. A crucial feature of any BI tool is its capability to present data visually through dashboards, charts, and other graphical outputs.

While business intelligence occasionally intersects with technologies like data integration/transformation and advanced analytics, we have opted to categorize these technologies separately.

The subsequent list highlights BI tools and technologies that are recognized for their native connectivity to Snowflake:

– Adobe
– Tableau
– Sigma
– Oracle
– SAP

Connecting to Snowflake (Data Integration)

Often referred to as ETL, data integration involves the following three core operations:

1. Extract:
Retrieving data from specified data sources.

2. Transform:
Adjusting the source data, as required, through rules, merges, lookup tables, or other conversion methods to align with the target.

3. Load:
Incorporating the resulting transformed data into a target database.

More recently, the term ELT has gained prominence, highlighting that the transformation operation doesn’t necessarily have to occur before loading. This is particularly relevant in systems like Snowflake, which support transformation during or after loading.

Furthermore, the scope of data integration has broadened to encompass a wider array of operations, including:

– Data preparation.
– Data migration, movement, and management.
– Data warehouse automation.

Connect to Snowflake

Snowflake seamlessly integrates with a diverse range of cutting-edge tools and technologies, providing you with extensive access to Snowflake through a robust network of connectors, drivers, programming languages, and utilities. This includes:

1. Certified partners who have created both cloud-based and on-premises solutions specifically designed for connecting to Snowflake.

2. Various third-party tools and technologies that have been verified to be compatible and effective when used in conjunction with Snowflake.

3. Snowflake’s own suite of clients, such as SnowSQL (a command-line interface), connectors tailored for Python and Spark, and drivers supporting Node.js, JDBC, ODBC, and more.

The following sections provide a more in-depth exploration of the solutions. These solutions are presented in alphabetical order and organized based on the categories illustrated in the diagram above.

Tip:

If you do not find a suitable solution here, our broad network of partners is available to assist you in seamlessly integrating with Snowflake. For additional information, refer to Solutions Partners on the Snowflake website.

Key Concepts & Architecture of Snowflake

Snowflake’s Data Cloud operates on a sophisticated data platform offered as a self-managed service. It empowers faster, more user-friendly, and highly flexible data storage, processing, and analytic solutions compared to traditional alternatives.

Unlike other existing database technologies or “big data” software platforms like Hadoop, Snowflake doesn’t rely on pre-existing frameworks. Instead, it integrates a groundbreaking SQL query engine with a cloud-native architecture, specifically designed for efficiency. To end-users, Snowflake offers the complete functionality of an enterprise analytic database, coupled with numerous additional special features and distinctive capabilities.

Data Platform as a Self-managed Service:

Snowflake operates as a fully self-managed service, which implies:

– No hardware (virtual or physical) needs to be selected, installed, configured, or managed.
– Virtually no software requires installation, configuration, or management on the user’s part.
– Continuous maintenance, management, upgrades, and tuning are seamlessly handled by Snowflake.

The entirety of Snowflake’s service operates on cloud infrastructure, with all components—excluding optional command line clients, drivers, and connectors—running within public cloud infrastructures. Snowflake relies on virtual compute instances for computation and a storage service for the persistent storage of data. It is not designed for operation on private cloud infrastructures, whether on-premises or hosted.

Snowflake stands apart from traditional packaged software offerings, as users are not responsible for software installation or updates; Snowflake manages all aspects of these processes.

Snowflake Architecture:

Snowflake’s architecture seamlessly blends elements of traditional shared-disk and shared-nothing database architectures. In alignment with shared-disk architectures, Snowflake employs a central data repository where persisted data is accessible from all compute nodes within the platform. However, akin to shared-nothing architectures, Snowflake executes queries through MPP (massively parallel processing) compute clusters. In this configuration, each node in the cluster locally stores a segment of the complete dataset. This innovative approach provides the data management simplicity characteristic of shared-disk architectures while delivering the performance and scale-out advantages associated with shared-nothing architectures.

Snowflake’s distinctive architecture comprises three fundamental layers:

Database Storage
Query Processing
Cloud Services

Database Storage:

Upon loading data into Snowflake, the platform systematically restructures the data into an internally optimized, compressed, and columnar format. This optimized data is then stored in cloud storage.

Snowflake takes charge of every aspect of data storage, encompassing organization, file size, structure, compression, metadata, statistics, and other pertinent elements. The data objects stored by Snowflake remain discreet and are not directly visible or accessible to customers. Access to this stored data is exclusively facilitated through SQL query operations conducted using Snowflake.

Query Processing:

Queries are processed in the execution layer using “virtual warehouses,” which are MPP compute clusters comprised of multiple nodes allocated by Snowflake. Each virtual warehouse is independent, avoiding any impact on the performance of others. Refer to the documentation on Virtual Warehouses for more details.

Cloud Services:

The cloud services layer orchestrates activities across Snowflake, managing authentication, infrastructure, metadata, query optimization, and access control. These services run on compute instances provisioned by Snowflake from the cloud provider.

Connecting to Snowflake:

Snowflake offers various connection methods:

– Web-based User Interface: Access all aspects of Snowflake management and usage.

– Command Line Clients (e.g., SnowSQL): Comprehensive access to Snowflake management and usage.

– ODBC and JDBC Drivers: Enable other applications (e.g., Tableau) to connect to Snowflake.

– Native Connectors (e.g., Python, Spark): Develop applications connecting to Snowflake.

– Third-party Connectors: Link applications like ETL tools (e.g., Informatica) and BI tools (e.g., ThoughtSpot) to Snowflake.

Getting Started with Snowflake (Summary, clean up, and additional resources)

Congratulations on successfully finishing this introductory tutorial!

Take a moment to review a brief summary and key highlights covered in the tutorial. Additionally, consider tidying up by dropping any objects created during the tutorial. Further insights can be gained by exploring additional topics in the Snowflake Documentation.

Summary and Key Points:

In summary, the data loading process involves two main steps:

Stage the Data Files:

– Data files are staged for loading, and this can be done either internally within Snowflake or in an external location. This tutorial specifically stages files in an internal stage.
Copy Data to Target Table:

– The staged files are copied into an existing target table. A running warehouse is a prerequisite for this step.

– Key considerations for loading CSV files:

– A CSV file comprises one or more records, each containing one or more fields, and sometimes a header record.

Records and fields in each file are separated by delimiters. The default delimiters are:

Records: newline characters
Fields: commas
In simpler terms, Snowflake anticipates each record in a CSV file to be separated by new lines, and the fields within each record (individual values) to be separated by commas. If different characters serve as record and field delimiters, explicit specification is necessary as part of the file format during loading.

There exists a direct relationship between the fields in the files and the columns in the loading target table, with regard to:

The number of fields in the file and columns in the target table.
The positions of the fields and columns within their respective file/table.
Data types (e.g., string, number, or date) for fields and columns.
If the numbers, positions, and data types don’t align with the data, the records won’t be loaded.

Tutorial clean up (Optional):
If the objects you created in this tutorial are no longer needed, you can remove them from the system with DROP statements.

DROP DATABASE IF EXISTS sf_tuts;

DROP WAREHOUSE IF EXISTS sf_tuts_wh;

Exit the connection:

To conclude a connection, employ the !exit command in SnowSQL (or its equivalent, !disconnect).

Executing this command drops the existing connection and terminates SnowSQL if it happens to be the last active connection.

What’s next?:

Deepen your understanding of Snowflake with the following resources:

Explore the Getting Started introductory videos and engage in additional tutorials offered by Snowflake:

– Access Tutorials and Other Resources

Getting Started with Snowflake (Query Loaded Data)

You can query the data within the emp_basic table using standard SQL alongside any supported functions and operators. Additionally, standard Data Manipulation Language (DML) commands allow you to perform operations like updating the loaded data or inserting additional data.

Retrieve all data:

Return all rows and columns from the table:

SELECT * FROM emp_basic;

The following is a partial result:

+————+————–+—————————+—————————–+——————–+————+
| FIRST_NAME | LAST_NAME | EMAIL | STREETADDRESS | CITY | START_DATE |
|————+————–+—————————+—————————–+——————–+————|
| Arlene | Davidovits | adavidovitsk@sf_tuts.com | 7571 New Castle Circle | Meniko | 2017-05-03 |
| Violette | Shermore | vshermorel@sf_tuts.com | 899 Merchant Center | Troitsk | 2017-01-19 |
| Ron | Mattys | rmattysm@sf_tuts.com | 423 Lien Pass | Bayaguana | 2017-11-15 |



| Carson | Bedder | cbedderh@sf_tuts.co.au | 71 Clyde Gallagher Place | Leninskoye | 2017-03-29 |
| Dana | Avory | davoryi@sf_tuts.com | 2 Holy Cross Pass | Wenlin | 2017-05-11 |
| Ronny | Talmadge | rtalmadgej@sf_tuts.co.uk | 588 Chinook Street | Yawata | 2017-06-02 |
+————+————–+—————————+—————————–+——————–+————+

Insert additional data rows:

Beyond loading data from staged files into a table, you can insert rows directly into a table using the INSERT Data Manipulation Language (DML) command.

As an illustration, to insert two additional rows into the table:

INSERT INTO emp_basic VALUES
(‘Clementine’,’Adamou’,’cadamou@sf_tuts.com’,’10510 Sachs Road’,’Klenak’,’2017-9-22′) ,
(‘Marlowe’,’De Anesy’,’madamouc@sf_tuts.co.uk’,’36768 Northfield Plaza’,’Fangshan’,’2017-1-26′);

Query rows based on email address:

Retrieve a list of email addresses containing United Kingdom top-level domains using the LIKE function:

SELECT email FROM emp_basic WHERE email LIKE ‘%.uk’;

The following an example result:

Query rows based on start date:

As an illustration, to determine the potential commencement date for specific employee benefits, add 90 days to the employees’ start dates using the DATEADD function. Narrow down the list to include only those employees whose start date precedes January 1, 2017:

SELECT first_name, last_name, DATEADD(‘day’,90,start_date) FROM emp_basic WHERE start_date <= '2017-01-01';

Getting Started with Snowflake (Copy Data into target tables):

To transfer your staged data into the target table, execute the command COPY INTO .

This COPY INTO command leverages the virtual warehouse created in the “Create Snowflake Objects” step to copy the files.

COPY INTO emp_basic
FROM @%emp_basic
FILE_FORMAT = (type = csv field_optionally_enclosed_by='”‘)
PATTERN = ‘.*employees0[1-5].csv.gz’
ON_ERROR = ‘skip_file’;

In the provided context:

– The FROM clause designates the location containing the data files, which is the internal stage designated for the table.

– The FILE_FORMAT clause defines the file type as CSV and designates the double-quote character (“) for enclosing strings. Snowflake accommodates various file types and options, detailed in the CREATE FILE FORMAT documentation.

– The PATTERN clause specifies that the command should load data from filenames matching a defined regular expression (.*employees0[1-5].csv.gz).

– The ON_ERROR clause outlines the course of action when the COPY command encounters errors in the files. By default, the command halts data loading upon encountering the first error. However, in this example, any file containing an error is skipped, and the command proceeds to load the next file. It’s important to note that none of the files in this tutorial contain errors; this inclusion is for illustrative purposes.

The COPY command offers an option to validate files before loading. Refer to the COPY INTO topic and other data loading tutorials for supplementary instructions on error checking and validation.

Upon execution, the COPY command provides a result displaying the list of copied files along with relevant information:

Getting Started with Snowflake (Stage Data Files)

A Snowflake stage serves as a designated location in cloud storage, facilitating the loading and unloading of data from a table. Snowflake provides support for:

Internal Stages:

Utilized for storing data files internally within Snowflake.
Every user and table in Snowflake is endowed with an internal stage by default, dedicated to staging data files.

External Stages:

Employed for storing data files externally in cloud storage services such as Amazon S3, Google Cloud Storage, or Microsoft Azure.
If your data is already hosted in these cloud storage platforms, external stages can be employed to load data into Snowflake tables.

Within this tutorial, we undertake the process of uploading sample data files (previously downloaded in the prerequisites) to the internal stage associated with the emp_basic table created earlier. The PUT command is employed for this purpose, enabling the upload of the sample data files to the designated internal stage.

Staging sample data files:

Utilize the PUT command in SnowSQL to transfer local data files to the designated table stage associated with the emp_basic table you’ve previously established.

PUT file://[/\]employees0*.csv @sf_tuts.public.%emp_basic;

For example:

– Linux or macOS
PUT file:///tmp/employees0*.csv @sf_tuts.public.%emp_basic;

– Windows
PUT file://C:\temp\employees0*.csv @sf_tuts.public.%emp_basic;

Now, let’s delve into the command:

file://[/\]employees0*.csv specifies the complete directory path and names of the files on your local machine for staging. It’s noteworthy that file system wildcards are permitted, and if multiple files match the pattern, they will all be displayed.

@.% denotes the usage of the stage for the specified table, specifically the emp_basic table in this instance.

By default, the PUT command employs gzip compression, as denoted in the TARGET_COMPRESSION column.

Listing the Staged Files (Optional):

You can list the staged files using the LIST command.

LIST @sf_tuts.public.%emp_basic;

Getting Started with Snowflake (Create Snowflake Objects)

In this phase, you will craft the subsequent Snowflake objects:

Database and Table Creation:

Establish a database (sf_tuts) and a table (emp_basic) for loading sample data.
Develop a virtual warehouse (sf_tuts_wh) with X-Small capacity, facilitating data loading and table querying. This specific warehouse is designed for the tutorial.
Upon tutorial completion, these objects will be removed.

Create a Database:
Use the CREATE DATABASE command to generate the sf_tuts database:

CREATE OR REPLACE DATABASE sf_tuts;

For this tutorial, utilize the default schema (public) available for each database instead of creating a new schema.

Verify the active database and schema for your current session using the context functions:

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

An example result may resemble:

+——————–+——————+
| CURRENT_DATABASE() | CURRENT_SCHEMA() |
|——————–+——————|
| SF_TUTS | PUBLIC |
+——————–+——————+

Create a Table:
Generate a table named emp_basic within sf_tuts.public using the CREATE TABLE command. The table structure corresponds to the fields in the forthcoming CSV data files:

CREATE OR REPLACE TABLE emp_basic (
first_name STRING,
last_name STRING,
email STRING,
streetaddress STRING,
city STRING,
start_date DATE
);

Create a Virtual Warehouse:
Form an X-Small warehouse called sf_tuts_wh using the CREATE WAREHOUSE command. This warehouse is initially suspended but is configured to automatically resume when SQL statements requiring compute resources are executed:

CREATE OR REPLACE WAREHOUSE sf_tuts_wh WITH
WAREHOUSE_SIZE=’X-SMALL’
AUTO_SUSPEND = 180
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED=TRUE;

Verify the active warehouse for your current session:

SELECT CURRENT_WAREHOUSE();

An example result may appear as:

+———————+
| CURRENT_WAREHOUSE() |
|———————|
| SF_TUTS_WH |
+———————+

Getting Started with Snowflake (Prerequisites)

To successfully engage with this tutorial, it is essential to set up a database, table, and virtual warehouse for data loading and querying in Snowflake. The creation of these Snowflake objects necessitates a Snowflake user with a role endowed with the required access control privileges.

Additionally, SnowSQL is indispensable for executing SQL statements throughout the tutorial. Lastly, CSV files containing sample data are required for the data loading process.

While you have the option to use an existing Snowflake warehouse, database, table, and your local data files, it is recommended to utilize the provided Snowflake objects and the accompanying set of data.

Before proceeding, ensure the following setup for Snowflake:

Create a User:

To create the necessary database, table, and virtual warehouse, log in as a Snowflake user with a role granting the requisite privileges for object creation.

If using a 30-day trial account, log in with the user created for the account, equipped with the necessary role for object creation.
If you don’t possess a Snowflake user, you won’t be able to perform the tutorial. Seek assistance from someone with the ACCOUNTADMIN or SECURITYADMIN role to create a user if needed.

Install SnowSQL:

Follow the installation guide for SnowSQL to set it up.
Download Sample Data Files:

Download the provided sample employee data files in CSV format from Snowflake.
Unzip the sample files, preferably into one of the following directories:
Linux/macOS: /tmp
Windows: C:\temp

Each file contains five data records, with fields separated by a comma (,). No extra spaces precede or follow the commas in each record—this conforms to Snowflake’s default expectation when loading CSV data.

By completing these setup steps, you’ll be well-prepared to dive into the tutorial seamlessly.