Getting Started with Snowflake (Snowflake in 20 minutes)

Introduction:

This guide utilizes the Snowflake command line client, SnowSQL, to introduce fundamental concepts and tasks, including:

1. Creating Snowflake Objects: You'll create a database and a table for storing data.

2. Loading Data: Sample CSV data files are provided for you to load into the table.

3. Querying: Finally, you'll explore sample queries.

Note:
To execute data loading and queries, Snowflake requires a virtual warehouse. Keep in mind that a running virtual warehouse consumes Snowflake credits. However, the credit consumption in this tutorial will be minimal as the entire process can be completed in under 30 minutes. Additionally, Snowflake incurs a minimal charge for on-disk storage used by the sample data in this tutorial. Nevertheless, steps are provided to drop the table and minimize storage costs.

If you are using a 30-day trial account, it's worth noting that the account comes with free credits, and you won't incur any costs during the tutorial. For detailed information on warehouse sizes and costs, refer to the Warehouse Size section.

What You Will Learn

Throughout this tutorial, you will gain proficiency in the following tasks:

Creating Snowflake Objects: Establish a database and a table dedicated to storing data.

Installing SnowSQL: Learn to install and utilize SnowSQL, the command line query tool for Snowflake.

Users employing Visual Studio Code may find the Snowflake Extension for Visual Studio Code to be an alternative to SnowSQL.
Loading CSV Data Files: Employ diverse mechanisms to load data from CSV files into tables.

Writing and Executing Sample Queries: Develop the ability to write and execute a variety of queries against recently loaded data.

Getting Started with Snowflake (Continuation)

Trial Accounts:

A trial account with Snowflake provides the opportunity to assess and test the platform's innovative and robust features at no cost or contractual commitments. Signing up for a trial account is simple – just provide a valid email address. No payment or additional qualifying information is necessary.

Signing Up for a Trial Account:

Begin your free trial by completing the self-service form on the Snowflake website.

Upon signing up, you'll make essential selections, including your preferred cloud platform, region, and Snowflake Edition. Keep in mind that certain features in the Enterprise Edition may consume additional credits, impacting the rate at which you utilize your free usage balance.

Your free usage balance decreases as you consume credits for utilizing compute resources and incurring storage-related costs.

The trial period spans 30 days from the sign-up date or until your free usage balance is depleted, whichever comes first. At any point during the trial, you have the flexibility to cancel or convert the account to a paid subscription.

Once the trial concludes, the account enters a suspended state. Although you can still log in, you won't have access to features like running a virtual warehouse, loading data, or performing queries.

To reactivate a suspended trial account, you'll need to provide credit card information, transitioning it into a paid subscription.

Using Compute Resources:

Virtual warehouses play a crucial role in providing the computational power needed for data loading and query execution within Snowflake. These warehouses operate by consuming credits, consequently diminishing your free usage balance. To initiate this process, simply start a warehouse, and any credits utilized will be deducted from your balance. If your credit consumption exhausts your free usage balance entirely, adding a credit card to your account becomes necessary to continue utilizing Snowflake.

It's important to note that free credits are solely consumed by the virtual warehouses you create in your account, and this consumption occurs only when these warehouses are actively running.

Tip:
To avoid unintentional usage of your free credits, consider the following:

Verify Warehouse Size: Before starting or resuming virtual warehouses, check their sizes. Larger warehouses consume more credits while running. In many instances, Small or Medium-sized warehouses are adequate for evaluating Snowflake's loading and querying capabilities.

Auto-Suspend Setting: When creating a warehouse, refrain from disabling auto-suspend. Opting for a short auto-suspend time period, such as 5 minutes or less, can effectively reduce credit consumption.

Using Storage:

When you upload data to your trial account, the associated storage cost is deducted from your free usage balance, calculated according to the standard On-Demand cost of a terabyte (TB) in your cloud platform and region. Beyond storage expenses, the act of loading data also utilizes credits as it engages the compute resources of a warehouse.

Converting to a Paid Account:

To transition your trial account to a paid status, follow these steps:

Log in to Snowsight.
Navigate to Admin » Billing & Terms.
Select Payment Methods.
Click on + Credit Card.
Provide the required information and click Add Card.
Upon entering your credit card details, Snowflake will verify the card's validity by initiating a $1 (USD) charge. No other charges are applied at this stage.

Note that you can also update the credit card associated with a trial account using the same interface. Each time you add a new credit card, a $1 (USD) charge is processed for verification.

It's essential to be aware that adding a credit card to a trial account converts it into a paid account without prematurely ending the trial period. Throughout the remaining trial duration, you can continue utilizing your free credits and storage until the balance is depleted. Afterward, any additional credit consumption and storage costs will be billed.

Unused balances expire at the conclusion of the trial period. At this point, costs for credit consumption and data storage are charged to the credit card on file at the end of each billing cycle, typically on a monthly basis.

For detailed pricing information, please refer to the pricing page on the Snowflake website.

Canceling a Trial Account:

Cancellation of a trial account can be initiated by reaching out to Snowflake Support and requesting the cancellation. It's important to note that, presently, trial accounts cannot be canceled directly through the web interface. To proceed with the cancellation, you must contact Snowflake Support for assistance.

Getting Started with Snowflake is Easy (Simple Guide)

Here's a simple guide to help you get started with Snowflake, including watching a live demo, trying it for free, and participating in a virtual hands-on lab:

1. Watch a Live Demo:

- Visit the Snowflake website: https://www.snowflake.com/.
- Look for the "Resources" or "Learn" section.
- Check for upcoming webinars or live demos. These sessions are usually conducted by Snowflake experts who showcase the platform's features and capabilities.
- Register for a live demo and attend the scheduled session.

2. Try Snowflake for Free:

- Navigate to the Snowflake website.
- Find the "Free Trial" or "Sign Up for Free" button.
- Follow the registration process to create a Snowflake account.
- You may need to provide some basic information and set up a username and password.
- Once registered, log in to the Snowflake platform using your credentials.

3. Explore the Snowflake Interface:

- After logging in, take some time to explore the Snowflake interface.
- Familiarize yourself with the main components, such as the Worksheets for querying data, the Object Browser for managing databases and tables, and the Warehouses for managing compute resources.
- Snowflake has a user-friendly interface, and you can find detailed documentation on their website to help you navigate and understand each feature.

4. Load Sample Data:

- To get hands-on experience, consider loading sample data into Snowflake.
- Snowflake provides sample datasets that you can use for testing and exploration. Look for these datasets in the documentation or within the Snowflake platform.

5. Participate in a Virtual Hands-On Lab:

- Check if Snowflake offers virtual hands-on labs or workshops.
- These labs typically provide a guided, interactive experience where you can work on exercises to understand key concepts and functionalities.
- Look for announcements on the Snowflake website, community forums, or through any communication channels they provide.

6. Join the Snowflake Community:

- Connect with other Snowflake users and experts through the Snowflake Community.
- The community is a valuable resource for asking questions, sharing experiences, and learning from others' use cases.
- Participate in forums, webinars, and discussions to enhance your understanding of Snowflake.

7. Refer to Documentation and Tutorials:

- Snowflake provides extensive documentation and tutorials to help users understand and use the platform effectively.
- Refer to the official documentation for detailed information on specific features, best practices, and troubleshooting.

By following these steps, you'll be well on your way to getting started with Snowflake and gaining hands-on experience with the platform.

Submitting a Listing for Approval

Before you can publish a listing on the Snowflake Marketplace, it's necessary to submit the listing for approval by Snowflake.

If the option to "Submit for Approval" is disabled and you wish to submit your listing, please check the following:

1. Ensure that you have completed the steps to configure the listing.
2. Verify that you are the ACCOUNTADMIN or have the OWNERSHIP privilege for the data product attached to the listing.
3. Confirm that all sample SQL queries attached to the listing pass validation.
To submit a listing for approval, follow these steps:

1. Log in to Snowsight.
2. In the left navigation bar, go to Data » Provider Studio.
3. Select the Listings tab, then choose the draft listing you intend to submit for approval.
4. Click on "Submit for Approval."

After Snowflake reviews the listing, the state will change to either Approved or Denied.

If the listing is denied, update it based on the feedback provided in comments and resubmit it for approval.

Upon approval or denial of the listing, an email notification is sent to both the Business Contact and Technical Contact email addresses specified in the provider profile associated with the listing.

Publishing a Listing for an Application Package:

To make an approved listing accessible on the Snowflake Marketplace, follow these steps:

1. Log in to Snowsight.

2. In the left navigation bar, go to Data » Provider Studio.

3. Select the Listings tab, then choose the listing you wish to publish.

4. Click on "Publish."

After publishing your Snowflake Marketplace listing, you have the option to create a referral link, allowing you to share a direct link to your listing with consumers.

Creating a Listing for an Application Package for the Snowflake Marketplace

To showcase your application package on the Snowflake Marketplace, follow these steps to create a listing:

1. Log in to Snowsight.

2. Navigate to Data » Provider Studio in the left navigation bar.

3. Click on + Listing, opening the Create Listing window.

4. Provide a name for your listing.

5. In the "Who can discover the listing" section, select "Anyone on the Marketplace" to publish the listing on the Snowflake Marketplace.

6. In the "How will consumers access the data product?" section, choose "Free" or "Paid."

7. Click on "Next." A draft listing is generated.

Before publishing your draft listing, you must configure additional required and optional capabilities.

Configuring a Marketplace Listing for an Application Package:

Once you've created a listing for the Snowflake Marketplace, it's essential to configure additional details for your listing to facilitate submission for approval and subsequent publication.

To configure a listing, follow these steps:

Log in to Snowsight.

In the left navigation bar, go to Data » Provider Studio.

Select the Listings tab, then choose the draft listing you wish to configure.

Click on "Add" next to each section displayed on the page, providing the necessary information.

While inputting details for each section, consult Configuring Listings for insights into each field. The specific properties available for editing depend on the type of listing created.

If you intend to monetize your Snowflake Native App, include a pricing plan to receive compensation for your Snowflake Native App.

Creating a Listing for an Application Package

To share your application with consumers, create a listing for your application package.

Creating a Private Listing for an Application Package

To present your application package exclusively to specific consumers, follow these steps to create a listing:

Log in to Snowsight.

Navigate to Data » Provider Studio in the left navigation bar.

Click on + Listing, opening the Create Listing window.

Provide a name for your listing.

In the "Who can discover the listing" section, choose "Only specified consumers" to privately share the listing with specific accounts.

Click on + Select to choose the application package to include in the listing.

Add a description for your listing.

(Optional) If you have multiple provider profiles, select the one to use for publishing this listing.

In the "Add consumer accounts" section, input the account identifiers for the consumers you wish to share the listing with.

If the consumer accounts are in a different region, set up auto-fulfillment:

Review the refresh frequency configured at the account level. Adjust if needed; refer to Set Account-Level Refresh Frequency.
Optionally, choose a warehouse for auto-fulfillment.
Click on "Publish" to publish the listing to the selected consumers, or choose "Save Draft" to save it as a draft.

To monetize your application, incorporate a pricing plan to receive payment for your application.

Sharing an Application with Consumers

Accessible to accounts in all AWS regions outside of government instances. For information on support for other cloud platforms, please reach out to your Snowflake representative.

Following the development and testing of the application package containing your application, you can make the application available to consumers through listings.

As a provider, you incorporate an application package as the data content of a listing. Subsequently, consumers install the application in their account directly from the listing.

Set Up Roles and Privileges:

When initiating the creation of a listing, it must be done from the account containing the data or application package. The role responsible for attaching a data product to a listing and publishing the listing should align with the role that initially created and owns the application package or share. The transfer of the OWNERSHIP privilege for a share is not supported.

In cases where a different role is used to create and manage the listing, it is advisable to grant the MODIFY privilege on the listing to the role that possesses ownership of the application package or share. For instance:

Role of the share or application package owner:

OWNERSHIP privilege on the share or application package.
MODIFY privilege on the listing.

Prerequisites for Publishing a Listing for an Application Package

Prior to generating a listing for an application package, make sure to accomplish the following:

Develop and thoroughly test your application package.
Before publishing, verify the correct functionality of your application package and ensure that roles and privileges are appropriately configured.
To streamline the process of sharing applications from your account to other Snowflake accounts, it is recommended to become a provider of listings in Snowflake. Refer to the guide on Becoming a Provider of Listings for details.

Note: Creating a provider profile is not mandatory for private listings.

Adding Application Logic to an Application Package

This section details the process of incorporating application logic into the setup script of an application package. Additionally, it provides guidance on integrating external code files into an application package.

For information specifically related to including a Streamlit app in an application package, refer to the documentation on Adding a Streamlit App to an Application Package.

Considerations for Using Stored Procedures and Functions:

Within the Snowflake Native App Framework, you have the capability to incorporate stored procedures, user-defined functions (UDFs), and external functions into an application package. These components can be authored in any of the languages supported by Snowflake.

Adding Application Code Securely:

All stored procedures and user-defined functions (UDFs) within a Snowflake Native App operate under the context of the application, possessing access to all objects within the installed Snowflake Native App. This scenario poses a risk of SQL injection attacks.

When creating procedures and functions intended for use within a Snowflake Native App, it is advised to follow best practices by utilizing bound parameters for all SQL commands that involve user input. This precaution applies to input received through procedure arguments.

For further details, refer to the documentation on Creating a Stored Procedure.

Removing an Application Package from Your Account

Users possessing the OWNERSHIP privilege for an application package can detach it from an account. However, it's important to note that removing an application package associated with a listing is not permitted.

Once an application package is removed, it becomes inaccessible within the provider account.

Note:
Following the removal of both a listing and the associated application package, consumers can view but cannot access the Snowflake Native App generated from that application package. Attempting to access the Snowflake Native App results in an error indicating the removal of the application package.

Removing an application package can be done either through Snowsight or by utilizing SQL commands.

Removing an Application Package Using Snowsight:
To remove an application package using Snowsight, adhere to the following steps:

Sign in to Snowsight.
In the left navigation bar, navigate to Apps, and then select Packages.
Choose the ellipsis (...) next to the desired application package, then select Drop.

Removing an Application Package Using SQL Commands:
To remove an application package using SQL, execute the DROP APPLICATION PACKAGE command, as illustrated in the following example:

DROP APPLICATION PACKAGE HelloSnowflakePackage;

Transferring Ownership of an Application Package

After creating an application package, you have the option to transfer ownership of the application package to another account-level role.

Transferring Ownership Using Snowsight:
To transfer ownership of an application package via Snowsight, follow these steps:

1. Sign in to Snowsight.
2. In the left navigation bar, navigate to Apps and then select Packages.
3. Choose the ellipsis (...) next to the application package you wish to transfer, then select Transfer Ownership.
4. Under Transfer to, choose the new account-level role.
5. Click on Transfer.

Transferring Ownership Using SQL Commands:
To transfer ownership of an application package to a different account-level role using SQL, utilize the GRANT OWNERSHIP command, as illustrated in the following example:
GRANT OWNERSHIP ON APPLICATION HelloSnowflakePackage TO ROLE native_app_dev;

Creating an Application Package Using SQL Commands

To generate an application package using SQL, employ the CREATE APPLICATION PACKAGE command, exemplified in the following instance:

CREATE APPLICATION PACKAGE HelloSnowflakePackage;

After creating an application package, use the SHOW APPLICATION PACKAGES command to view the list of available application packages.

Granting Privileges on an Application Package:

Certain tasks associated with the development of an application package necessitate specific privileges assigned to the application package. The following table outlines the privileges required for performing these tasks:

Privilege Task
ATTACH LISTING Add an application package to a listing.
DEVELOP Create an APPLICATION object in development mode from the application package.
INSTALL Create an APPLICATION object based on the application package.
MANAGE RELEASES Specify a release directive, view the version and patch level.
MANAGE VERSIONS Add a version and patch level to an application package.
OWNERSHIP Perform all tasks mentioned above.

Granting Privileges on an Application Package Using Snowsight:

To configure privileges for an application package using Snowsight, follow these steps:

1. Log in to Snowsight.

2. In the left navigation bar, go to Apps and then select Packages.

3. Choose the desired application package, and click on the Settings tab.

4. Within the Privileges section, click the edit icon next to the privilege you wish to grant.

5. Click on Add Role, and then select the role to which you want to grant the privilege.

6. Save your changes.

The assigned role will now be displayed next to the granted privilege.

Granting privileges on an application package to a role using SQL commands:
It involves using the GRANT command, illustrated in the following example:

GRANT MANAGE RELEASES ON APPLICATION PACKAGE HelloSnowflakePackage TO ROLE app_release_mgr;

This command bestows the MANAGE RELEASES privilege upon the app_release_mgr role. You can employ the identical command to confer other available privileges on an application package.

Creating an Application Package Using Snowsight

To generate an application package using Snowsight, follow these steps:

1. Log in to Snowsight.

2. Navigate to the left-hand menu, choose 'Apps,' and then click on 'Packages.'

3. Click on 'Create' followed by 'App Package.'

4. Provide a name for your application package.

5. Choose the target audience for the application package:

- Select 'Consumers outside of your organization' to make it accessible externally. This choice triggers an automated security scan for each version and patch.

- Choose 'Consumers within your organization' to limit availability within your organization. No automated security scan is initiated for this option.

6. Optionally, add comments for the application package. Note that these comments won't be visible to consumers.

7. Click 'Create.'

Creating an Application Package Part II

The Snowflake Native App Framework provides the flexibility to create an application package either through Snowsight or by executing SQL commands.

Privileges Required for Application Package Creation:
To create an application package, your role must be granted the global privilege of CREATE APPLICATION PACKAGE.

Uploading the Manifest File and Setup Script to a Named Stage:
Before crafting an application package, it is essential to generate the manifest file and setup script, and subsequently, upload them to a named stage accessible to your account.

There are two methods for file upload to a named stage:

Using Snowsight: For detailed instructions, refer to the guide on Staging Files Using Snowsight.

Using the PUT Command.

Creating an Application Package

This topic describes how to create and manage an application package with the Snowflake Native App Framework.

About Application Packages:
An application package serves as a comprehensive container, encompassing the data content, application logic, metadata, and setup script essential for an application's functionality. Additionally, it includes details about the defined versions and patch levels associated with the application.

Manifest File Example

The following example shows a typical manifest file with values specified for all supported properties:

manifest_version: 1 # required
version:
name: hello_snowflake
label: "v1.0"
comment: "The first version of a Snowflake Native App"

artifacts:
readme: readme.md
setup_script: scripts/setup.sql
default_streamlit: streamlit/ux_schema.homepage_streamlit
extension_code: true

configuration:
log_level: debug
trace_level: always

privileges:
- EXECUTE TASK:
description: "Run ingestion tasks for replicating Redshift data"
- EXECUTE MANAGED TASK:
description: "To run serverless ingestion tasks for replicating Redshift data"
- CREATE WAREHOUSE:
description: "To create warehouses for executing tasks"
- MANAGE WAREHOUSES:
description: "To manage warehouses for optimizing the efficiency of your accounts"
- CREATE DATABASE:
description: "To create sink databases for replicating Redshift data"
- IMPORTED PRIVILEGES ON SNOWFLAKE DB:
description: "To access account_usage views"

references:
- enrichment_table:
label: "Enrichment Table"
description: "Select table with audience data to enrich with Ramp IDs"
privileges:
- SELECT
- INSERT
- UPDATE
object_type: Table
multi_valued: true
register_callback: config.register_reference

Manifest File Reference

The manifest.yml file can contain the following valid properties:

manifest_version:

Specifies the version of the manifest file.
Required
version:

Defines a block containing parameters related to a version.

Optional

name:
Specifies the logical name of the version.
label:
Displays a name for the version to consumers.
comment:
Provides a comment visible to providers using the SHOW VERSIONS command.
artifacts:

Defines a block related to resources distributed from this version of the package.
Required
readme:

Specifies the path to a markdown readme file, offering an overview of the Snowflake Native App.
Required
setup_script:

Specifies the path and filename of the SQL script run during installation.
Optional
default_streamlit:

Specifies the schema and name of the default Streamlit app for Snowflake Native Apps that include Streamlit.
Required if Streamlit is included; Optional otherwise.
extension_code:

Specifies if the Snowflake Native App can run UDFs and stored procedures.
Optional
configuration:

Defines a block containing configuration properties for the Snowflake Native App.

Optional

log_level:
Specifies the logging level (Default: Off).
trace_level:
Specifies the trace event level (Default: Off).
privileges:

Defines the privileges that the consumer must grant during installation.

Optional; Default: Empty list

:

Specifies the name of the privilege.
description:
Describes the privilege for consumer understanding.
references:

Defines a block containing references defined by the provider.

Optional

- :

Specifies the name of the reference.
label:
Describes the reference for consumer viewing.
privileges:
Specifies the required privileges for the reference.
object_type:
Specifies the type of associated object.
multi_valued:
Specifies if more than one object is associated (Optional; Default: false).
register_callback:
Specifies the callback function used for the reference.
Required if references is specified.

Creating the Manifest File

About the Manifest File:

The Snowflake Native App Framework mandates the inclusion of a manifest file in each application package. This file delineates essential properties for the application package, such as the setup script's location and version definitions.

Key requirements for the manifest file are as follows:

The manifest file must be named manifest.yml.
It should be uploaded to a specifically named stage, ensuring accessibility during the creation of an application package or Snowflake Native App.
The manifest file must be positioned at the root of the directory structure within the designated stage.

Application Roles and Versions

Application roles lack versioning. Consequently, removing an application role or withdrawing a permission from an object situated outside a versioned schema can potentially affect the existing version of an application or the version undergoing an upgrade. It is advisable to drop application roles only when all versions of the application utilizing those roles have been successfully removed.

Using Application Roles in the Setup Script:

Application roles established within the setup script are automatically conferred upon the role that possesses the application instance. Upon installation, the role employed to install the application assumes the ownership of that application. Nevertheless, the application owner retains the authority to extend privileges to other account roles within the consumer account.

These application roles serve as a means to impart privileges on objects within the application, thereby enabling the conferment of privileges to the consumer.

For example:
CREATE APPLICATION ROLE admin;
CREATE APPLICATION ROLE user;
GRANT APPLICATION ROLE user TO APPLICATION ROLE admin;

CREATE OR ALTER VERSIONED SCHEMA app_code;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE admin;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE user;
CREATE OR REPLACE PROCEDURE app_code.config_app(...)
GRANT USAGE ON PROCEDURE app_code.config_app(..)
TO APPLICATION ROLE admin;

CREATE OR REPLACE FUNCTION app_code.add(x INT, y INT)
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
TO APPLICATION ROLE admin;
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
TO APPLICATION ROLE user;

In this illustration, the setup script initiates the creation of application roles, specifically 'admin' and 'user.' Subsequently, both application roles are endowed with access to the schema housing the application code. Additionally, access to the 'add' function within the schema is granted to both roles. Furthermore, exclusive access to the 'config_app' procedure is extended to the 'admin' role.

About Application Roles

Snowflake recommends adhering to the following best practices when crafting the setup script for an application:

When employing the CREATE command to generate objects within the setup script, it is advisable to use the CREATE OR REPLACE or CREATE IF NOT EXISTS variations of the commands. The setup script might be executed multiple times during installation, upgrades, or error recovery scenarios, such as in cases where a versioned schema may not be empty.
sql
Copy code
-- Example of creating a schema within the setup script
CREATE SCHEMA IF NOT EXISTS app_config;
CREATE TABLE IF NOT EXISTS app_config.params(...);
As the CREATE SCHEMA command doesn't alter the session context, objects must be qualified with the target schema when created. Considerations should be given to potential failures during the setup script execution. Since the script is designed to be idempotent, it automatically reruns if the initial execution encounters an error.

Take into account the implications when granting permissions to application roles within a schema. Utilize the CREATE OR REPLACE statement carefully, especially with procedures, as it replaces the procedure, implicitly removing prior grants. Although grants are restored later in the script, a script failure during execution might temporarily revoke consumer access to the procedure.

sql
Copy code
-- Example of creating or replacing a procedure and granting usage to an application role
CREATE OR REPLACE PROCEDURE app_state.proc()...;
-- Additional CREATE statements
GRANT USAGE ON PROCEDURE app_state.proc()
TO APPLICATION ROLE app_user;
Views on shared content should always be defined in a versioned schema. This ensures that any code accessing the view during an upgrade maintains a consistent view, even if new columns or other attributes are added or removed.

If the setup script necessitates prolonged operations, such as upgrading large state tables, ensure that these updates are compatible with existing running code from the previous version.

These best practices help optimize the robustness and reliability of the setup script throughout the application's lifecycle.