Part 1: Introduction to Snowflake and its Architecture

1. Introduction to Snowflake

Snowflake is a cloud-based data warehousing platform that provides a large number of capabilities in terms of data storage, processing, and analysis. It offers secure and fast access to data with a SQL-based approach, which makes it an excellent choice for both small and large-scale data operations.

2. Snowflake Architecture

Snowflake's architecture is divided into three key layers:

a. Database Storage

Snowflake automatically arranges the data into multiple micro-partitions and compresses them for storage. The data is stored in a columnar format which enables efficient computation and analytics.

-- Create a simple database
CREATE DATABASE IF NOT EXISTS my_database;

b. Query Processing

Snowflake decouples the compute from the storage layer, enabling them to scale independently. You can adjust your compute resources (known as "warehouses") up or down based on your processing needs.

-- Create a warehouse
CREATE WAREHOUSE IF NOT EXISTS my_warehouse
WAREHOUSE_SIZE = XSMALL 
WAREHOUSE_TYPE = STANDARD 
AUTO_SUSPEND = 180 
AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
INITIALLY_SUSPENDED = TRUE;

c. Cloud Services

The cloud services layer is essentially the brain of the Snowflake operations. It coordinates and handles all transactions, metadata management, optimization, and security.

-- Assign resource monitor to warehouse
ALTER WAREHOUSE my_warehouse SET RESOURCE_MONITOR = my_resourcemonitor ;

3. Set Up

To get started with Snowflake, you will need an account on the platform.

Steps:

1. Open the Snowflake web interface and follow the sign-up process. You will receive a 30-day free trial.

2. After creating an account, you can log in to your Snowflake account.

3. To start querying data, you need to create a database and a warehouse as shown in the SQL samples above.

4. Connecting to Snowflake

In order to connect with Snowflake, you can use either the Snowflake web interface for SQL commands or a Snowflake connector for Python.

For Python:

# Import the snowflake connector
import snowflake.connector

# Set up a connection
con = snowflake.connector.connect(
  user='',
  password='',
  account='',
  warehouse='my_warehouse',
  database='my_database',
  schema='public'
)

With the Python connector, you can run your SQL commands from Python and fetch the result data into a pandas DataFrame for further processing or analysis.

Summary

We have introduced Snowflake and its architecture along with the necessary setup and connection processes. In the coming sections, we will delve deeper into querying, data loading, and manipulation. In particular, we will explore working with both structured and semi-structured data types.

Exploring Snowflake Data Warehousing - Practical Implementation

In this guide, we will explore the practical applications of Snowflake, a cloud-built data warehousing tool.

Section 1: Loading Data into Snowflake

Before we can start our data exploration, we must first load our data into Snowflake. To achieve this, we can utilize Snowflake's COPY INTO <table> command.

COPY INTO my_table 
FROM 's3://my-bucket/path/to/my/data.csv'
CREDENTIALS = (AWS_KEY_ID = 'my_aws_access_key_id' AWS_SECRET_KEY = 'my_aws_secret_access_key')
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'CONTINUE';

Replace my_table, my-bucket/path/to/my/data.csv, my_aws_access_key_id, and my_aws_secret_access_key with your actual table name, S3 data location, and AWS credentials respectively.

Section 2: Querying Snowflake Tables

Next, let's work on querying our data from Snowflake.

Here's a simple SQL query that selects all records from my_table:

SELECT * 
FROM my_table;

For a more advanced query, this SQL query gathers the total count of users in my_table grouped by the user's country:

SELECT country, COUNT(*)
FROM my_table
GROUP BY country;

Section 3: Sharing Data Across Databases

In Snowflake, data can be shared across databases even within the same account. We will illustrate this by creating a clone of my_table in a different database.

CREATE DATABASE new_database_name;
USE DATABASE new_database_name;
CREATE TABLE new_table_name CLONE current_database.my_table;

Replace new_database_name, new_table_name, and current_database with your actual new database name, new table name and current database name respectively.

Section 4: Securing Data in Snowflake

In Snowflake, data is automatically encrypted. Let's add a further layer of security by adding a row-level security policy.

CREATE ROW ACCESS POLICY sales_rep_rap
AS (SELECT CASE
             WHEN CURRENT_ROLE() IN ('SALES_REP') THEN TRUE
             ELSE FALSE
            END)
FOR TABLE my_table;

ALTER TABLE my_table
SET ROW ACCESS POLICY sales_rep_rap;

The above commands create a Row Access Policy that is applicable only to the role 'SALES_REP'. The policy is then applied to my_table.

Section 5: Integrating with External Tools

To fetch data from Snowflake for data visualization or reporting purposes, we will use Python's snowflake-connector-python library.

Step 1: Import necessary libraries.

import snowflake.connector

Step 2: Connect to Snowflake.

con = snowflake.connector.connect(
  user='',
  password='',
  account='',
  warehouse='',
  database='',
  schema=''
)

Step 3: Query data from Snowflake.

cur = con.cursor()
cur.execute("SELECT * FROM my_table")
for (column1, column2) in cur:
    print(f"{column1}, {column2}")

Remember to replace everything inside < > as per your Snowflake configuration.

Implementing these scenarios will give you a good grip on the most common operations in Snowflake. This can serve as a basis for more advanced operations, such as modifying and optimizing your Snowflake environment.

Mastering Data Management in Snowflake

In this guide, we delve into the practical implementation of key data management aspects in Snowflake. We will guide you through Snowflake data loading, data unloading, and data protection.

Please note that this guide assumes that you're already familiar with the basics of Snowflake and its Architecture, and you already have an existing Snowflake account.

1. Data Loading in Snowflake

1.1 Using COPY INTO command

In Snowflake, data loading is implemented typically by using the COPY INTO command. This operation reads files from a specified stage, which can be a local file system or an external location like Amazon S3, Google Cloud Storage, or Microsoft Azure storage. The data is loaded into a new or existing table.

COPY INTO my_table
FROM (
  SELECT $1::string as c1, $2::integer as c2
  FROM '@~/staged_file.csv.gz' 
  FILE_FORMAT = ( 
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1 
  )
);

In this example, '@~/staged_file.csv.gz' represent the path of the file you want to load, my_table is the target table into which the data will be loaded, and the SELECT statement casts each column in the file to the appropriate data type.

---

2. Data Unloading in Snowflake

2.1 Using the COPY INTO LOCATION command

To unload (or export) data from Snowflake tables, you can use the COPY INTO <location> command as shown in the example below:

COPY INTO '@my_external_stage/myfileprefix_'
FROM my_table
FILE_FORMAT = ( FORMAT_NAME = my_csv_format )
HEADER = TRUE;

In this example, @my_external_stage/myfileprefix_ is the external storage location where the data will be written, my_table is the source table from which the data will be unloaded, and my_csv_format is a previously-defined file format object that specifies the format of the output files.

---

3. Data Protection in Snowflake

Noteworthy data protection features in Snowflake include data encryption and data retention.

3.1 Data Encryption

Snowflake automatically encrypts all data using AES-256 strong encryption. Encryption is intrinsic and does not require user intervention.

3.2 Data Retention

Snowflake automatically retains historical data for a period that is configurable per database. To configure data retention, adjust the NUMBER OF DAYS of the DATA_RETENTION_TIME_IN_DAYS parameter as shown below:

ALTER DATABASE my_database SET DATA_RETENTION_TIME_IN_DAYS = 90;

In the above example, my_database is the name of your database and 90 is the number of days for which the historical data should be retained.

---

By mastering these aspects of Data Management in Snowflake, you'll be well-equipped to handle your business data more efficiently and securely.

Snowflake SQL - Concepts and Best Practices

Here, we will delve into key Snowflake SQL concepts, and showcase the best practices using practical SQL queries and operations.

Section 1: Snowflake SQL

Snowflake SQL is an ANSI compliant SQL that includes support for powerful features such as Object Creation, Data Manipulation, Transactions, Session and Account Management, and Security Management.

Example 1: CREATE TABLE

Let's create a new table named "employees", with columns for "ID", "FirstName", "LastName", and "Department".

  CREATE TABLE employees (
      ID NUMBER,
      FirstName STRING,
      LastName STRING,
      Department STRING
  );

Example 2: INSERT INTO

Now, let's insert data into the "employees" table.

  INSERT INTO employees values(1, 'Peter', 'Parker', 'IT'),
                              (2, 'Clark', 'Kent', 'HR'),
                              (3, 'Tony', 'Stark', 'Engineering');

Example 3: SELECT FROM

Let's retrieve data from our table. This particular query will select all the rows from the "employees" table where "Department" is "IT".

  SELECT * FROM employees WHERE Department = 'IT';

Section 2: Advanced Snowflake SQL Concepts

While basic SQL functionality like SELECT, INSERT and CREATE TABLE is useful, Snowflake supports additional powerful features that can enhance your data manipulation and analysis capabilities.

Example 4: User-Defined Functions (UDFs)

UDFs allow you to create a function to encapsulate complex expressions or operations. Here's how to create and use a UDF.

  -- Creating a UDF to concatenate first name and last name
  CREATE FUNCTION full_name(FIRST_NAME STRING, LAST_NAME STRING)
  RETURNS STRING
  AS
    $$ FIRST_NAME || ' ' || LAST_NAME$$;

  -- Using the UDF
  SELECT ID, full_name(FirstName, LastName) AS FullName, Department FROM employees;

Example 5: JOIN Operations

Snowflake supports JOIN operations to extract data from multiple tables.

  -- Assuming a second table 'departments' with columns 'Department' and 'Head'
  SELECT e.FirstName, e.LastName, d.Head
  FROM employees e 
  INNER JOIN departments d
  ON e.Department = d.Department;

Section 3: Best Practices

1. Use Semi-Structured Data Types: Snowflake supports semi-structured data types such as VARIANT, OBJECT, and ARRAY enabling you to handle more complex, nested data.

   -- Create table with semi-structured data
   CREATE TABLE public.events (
     EVENT_ID NUMBER,
     ATTRIBUTES VARIANT);

2. Use COPY commands for Bulk Data Ingestion: Snowflake has the COPY command that enables faster loading of large data files.

   -- Loading JSON file from a stage area
   COPY INTO public.events
   FROM @mystage/mypath/file.json
   FILE_FORMAT = (TYPE = 'JSON');

3. Take advantage of Zero-Copy Cloning: Snowflake provides zero-copy cloning that allows you to create copies of your database, schema, or tables without incurring storage costs.

   -- Create a clone of employee table
   CREATE TABLE employees_clone CLONE employees;

These are some of the concrete examples of Snowflake SQL concepts and best practices. As a powerful, cloud-based data platform, Snowflake provides vast capabilities for data-processing tasks, aimed at enabling data-driven decision-making.

Advanced Snowflake - Optimal Performance Techniques

The points discussed here focus on the specific implementation details that can result in optimal performance when working with Snowflake.

Table Clustering

Table clustering in Snowflake organizes the data in a table based on one or more specified columns. Clustering improves the performance of Snowflake operations that filter data using the clustering keys. Here's how you can implement it:

-- Assuming you have a table named 'orders'
-- Cluster the table by orderId and customerId

ALTER TABLE orders CLUSTER BY (orderId, customerId);

Materialized Views

Use Materialized Views to store the result of a query physically and update them periodically. If you often execute a complex query, a Materialized View holding the result of the query can speed up your operations.

-- Assuming you still work with 'orders' table
-- Creating materialized view 

CREATE MATERIALIZED VIEW order_summary 
AS SELECT customerId, COUNT(orderId), SUM(orderAmount)
FROM orders GROUP BY customerId;

Query Optimization

Snowflake optimizes the execution of queries by caching the result set in memory. To use this feature, you should ensure the same SQL text is used for repeated queries.

-- For instance, instead of 
SELECT * FROM orders WHERE orderId = 1234;

-- Use
SELECT * FROM orders WHERE orderId = ?;
-- Execute it with the parameters {1234}

Caching

Snowflake automatically caches some data storage and computation results to improve performance. Understanding how these caches work can help you structure your operations in ways that leverage these caches.

Results of SELECT statements get cached in the sessions in which they are executed. If the same SELECT statement gets executed again in the same session, Snowflake fetches the result from the cache.

Data Loading

To load data efficiently, you should stage your data files. Here's how you can load a CSV file:

-- Create a named file format
CREATE FILE FORMAT csv_format TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"';
-- Create a named stage object
CREATE STAGE my_stage FILE_FORMAT = csv_format;
-- Copy the data
COPY INTO orders FROM @my_stage;

Take into consideration that files that are 100 MB to a few GB in size can be loaded more efficiently.

Following these best practices, you can improve your Snowflake performance to reach your data warehousing goals.

Snowflake Security - Data Protection and Compliance

Snowflake security ensures data protection and compliance by integrating a framework that includes encryption, access control, and auditing. The following sections detail the practical implementation of these practices.

1. Data Encryption

Snowflake provides encryption automatically at all stages (at rest and in transit), thus you rarely need to intervene. However, organizations may manage their keys for better control.

Managing your encryption keys

Snowflake uses Hierarchical Key Model to encrypt and decrypt data. You can manage the master encryption keys by employing Snowflake's BYOK (Bring Your Own Key) feature.

--To Create a stage for master key
CREATE STAGE my_keystage
URL = 'azure://mykeyvault.blob.core.windows.net/mykeystage'
CREDENTIALS = (AZURE_SAS_TOKEN='?sv=2019-02-02&ss=bfqt&srt=sco&sp=rwdlacup&se=2022-06-05T21:00:31Z&st=2022-06-05T13:00:31Z&spr=https,http&sig=xxxxxxxx')
;
--Rotating encryption key with the key on the stage
ALTER ACCOUNT
   SET ENCRYPTION KEY
   WITH (STAGE = ‘my_keystage’, STAGE_FILE_FORMAT = (TYPE = ‘JWK’), BINARY_AS_TEXT = TRUE)
   TO ‘azure/wk-2019-02-21T15-30.jwk’;

2. User and Role-Based Access Control

In Snowflake access to databases is controlled by User and Role-based access controls.

Creating Users

CREATE USER john
PASSWORD = '5tr0ngPa55worD'
DEFAULT_ROLE = FULL_ACCESS
MUST_CHANGE_PASSWORD = TRUE
;

Creating and Assigning Roles

--To Create Role
CREATE ROLE FULL_ACCESS
;

--To Grant Privileges
GRANT USAGE ON DATABASE my_database TO ROLE FULL_ACCESS
;

--To Assign Role to User
GRANT ROLE FULL_ACCESS TO USER john
;

3. Auditing and Compliance

Snowflake allows performing audit using system defined function and views such as QUERY_HISTORY, LOGIN_HISTORY.

Sample audit

--Audit unsuccessful login attempts
SELECT * FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY()) WHERE LOGIN_STATUS = 'NO';

--Audit data queries ran by a specific user
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION()) WHERE EXECUTION_STATUS = 'SUCCESS' AND USER_NAME = 'john';

This guide should help you implement real-life solutions related to Snowflake's security, data protection, and compliance features. It provides actionable SQL code to encrypt, control access, and audit activity on your Snowflake environment. Remember that Snowflake’s features may vary based on the level of your account.

Snowflake Data Sharing Techniques

Data sharing within Snowflake uses the cloud services layer and is a crucial component of Snowflake’s Data Cloud. It allows you to expose or share your table view to other Snowflake accounts. Let's dive into the practical implementation.

Creating a Database Share

We'll start by creating a compute warehouse and a database which will contain the shared data. This section assumes you have the necessary privileges to perform these actions and that you're already logged into your Snowflake account.

-- Create a new warehouse to process share
CREATE WAREHOUSE SHARE_WAREHOUSE
    WAREHOUSE_SIZE = 'X-Small'
    AUTO_SUSPEND = 600
    INITIALLY_SUSPENDED = TRUE;

-- Create a new database for shared data
CREATE DATABASE SHARE_DB;

Populating the Database

Next, we'll populate the database with data that we'll share. In real life, you'd typically use your organization's data. Here's how we'll create a sample table and populate it with mock data.

-- Use the share database and create table
USE DATABASE SHARE_DB;
CREATE TABLE SHARE_TABLE (ID NUMBER, NAME STRING);
-- Insert some records into the table
INSERT INTO SHARE_TABLE (ID, NAME) 
  VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');

Creating the Share

Once we have our data ready, we can proceed to create the Share object. This object contains metadata about what objects are being shared and the accounts having access to them.

-- Create a data share
CREATE SHARE SHARE_OBJECT;

Next, we'll grant privileges to this share object for the specific database and table we'd like to share.

-- Grant privileges to the database and table
GRANT USAGE ON DATABASE SHARE_DB TO SHARE SHARE_OBJECT;
GRANT SELECT ON SHARE_DB.SHARE_TABLE TO SHARE SHARE_OBJECT;

Sharing with Consumer Accounts

Finally, we'll share the data with specific Snowflake accounts (consumer accounts). Every Snowflake account has a unique name which we use for this purpose.

-- Alter the share to add accounts
ALTER SHARE SHARE_OBJECT ADD ACCOUNTS = ('CONSUMER_ACCOUNT1', 'CONSUMER_ACCOUNT2');

The consumer accounts should now be able to see the share object from their accounts, and can create a database from it.

-- On the consumer account
CREATE DATABASE CONSUMER_DB FROM SHARE PRODUCER_ACCOUNT.SHARE_OBJECT;

The consumer can now query the shared data from their newly created database.

-- Query shared data
SELECT * FROM CONSUMER_DB.SHARE_DB.SHARE_TABLE;

Conclusion

In this tutorial, we covered how to prepare and share a database with other Snowflake accounts. Note that you can also revoke the share permissions at any time:

-- Revoke share privileges
REVOKE USAGE ON SHARE SHARE_OBJECT FROM ACCOUNT 'CONSUMER_ACCOUNT1';
ALTER SHARE SHARE_OBJECT REMOVE ACCOUNTS = ('CONSUMER_ACCOUNT1');

These steps demonstrate how Snowflake data sharing works, but remember that in a real-world scenario, you'll need to adapt these steps to your specific needs and consider data protection and compliance practices.

Data Migration and Integration Tools in Snowflake

Data migration is an essential aspect when working with the Snowflake tool. It involves transferring data from various sources into the Snowflake data warehouse. This involves the use of several data integration tools.

In this section, we will be implementing the use of some of these tools including Snowpipe for data loading as well as Snowflake's Python connector for basic data manipulation.

1. Data Loading with Snowpipe

Snowpipe is an automated data loading service provided by Snowflake which allows loading data seamlessly from cloud storage into Snowflake in real-time.

Let's assume we have data in our cloud storage that we want to migrate into the Snowflake database.

Firstly, we need to make sure that our data is staged on the Cloud Storage.

Setup Snowpipe

We can use the below SQL commands to setup the Snowpipe:

USE ROLE sysadmin;
CREATE PIPE mydatabase.myschema.mypipe AS COPY INTO mydatabase.myschema.mytable FROM '@mydatabase.myschema.mystage';

Load Data using Snowpipe

Once we've created the pipe, we can use it to load data into Snowflake:

INSERT INTO mydatabase.myschema.mypipe (filename, metadata)
  SELECT metadata$filename, metadata
  FROM TABLE(STAGE_NAME)
  WHERE STAGE_NAME IS NOT NULL;

2. Basic Data Manipulation with Snowflake's Python connector

Snowflake provides several connectors for popular programming languages, one of which is Python.

Python Setup

To use the Snowflake Connector for Python, we need to first install it using pip:

pip install --upgrade snowflake-connector-python

Establish a Connection

Firstly, import the module, setup your connection details, and establish a connection as follows:

import snowflake.connector

# Creating Connection
con = snowflake.connector.connect(
  user='USERNAME',
  password='PASSWORD',
  account='ACCOUNTURL',
  warehouse='WAREHOUSE',
  database='DATABASE',
  schema='SCHEMA'
)

Execute SQL Statement

Once the connection is established, we can use it to execute SQL statements

# Creating a cursor object
cur = con.cursor()

# Executing SQL Statement
cur.execute("SELECT * FROM your_snowflake_table")
 
# Fetch the results
rows = cur.fetchall()

# Closing the cursor and connection
cur.close()
con.close()

Conclusion

Snowflake provides a variety of tools for data migration and integration, making it a flexible and powerful platform for managing your organisation's data. Using Snowpipe for automatic data loading and Python connector for interacting with the database programmatically, you can build complex and flexible data integration pipelines.

Working with Semi-Structured Data in Snowflake

Snowflake supports loading and managing semi-structured data, including JSON, Avro, XML, ORC and Parquet formats. In this guide, we'll primarily focus on JSON data.

Contents

1. Loading JSON Data
2. Querying JSON Data
3. Working with Flattening functions

1. Loading JSON Data

Assuming that you have your JSON data already in a stage, you can copy the data into your Snowflake table using the COPY INTO command:

COPY INTO my_table
FROM (SELECT $1, $1:userId::integer, $1:id::integer, $1:title, $1:completed::boolean
      FROM @my_stage/my_file.json.gz)
FILE_FORMAT = (TYPE = 'JSON');

In this example, $1 refers to the entire JSON object. $1:userId is the userId field of the JSON object.

2. Querying JSON Data

To query JSON data, Snowflake allows you to use some specific SQL functions and operators. For instance, you can check whether a particular field exists in the JSON object, or you can extract the specific value of a field in the JSON object.

Check field exists:

SELECT *
FROM my_table
WHERE jsonData:"userId" IS NOT NULL;

To extract field value:

SELECT jsonData:"userId"::integer as userId
FROM my_table;

3. Working with Flattening Functions

Snowflake provides two main functions to flatten JSON objects: LATERAL FLATTEN() and FLATTEN(). They are used to transform JSON objects to become more readable and to be used with other SQL operations.

SELECT value:id::integer AS id,
       value:userId::integer AS userId,
       value:title AS title,
       value:completed::boolean AS completed
FROM my_table, lateral flatten(input => jsonData);

Here, we're using the LATERAL FLATTEN() function with the jsonData column as input. This function returns one row for each sub-object within the top-level JSON object.

In cases where your JSON data has nested structures, you will use the FLATTEN function multiple times to fully flatten your JSON data.

SELECT value:userId::integer AS userId,
       value.product.name AS product_name,
       value.product.price::float AS product_price,
FROM my_table,
LATERAL FLATTEN(input => jsonData.items),
LATERAL FLATTEN(input => value.product);

That's a basic introduction to working with semi-structured data in Snowflake, primarily JSON data. Thankfully, the Snowflake engine allows us to deal with JSON data just like standard SQL data, greatly simplifying operations.

Implementing Real-World Projects with Snowflake

In this section, we will continue to learn about Snowflake by practically implementing it on real-world use cases. Here we are going to deal with two different types of project use cases - a Data Analytics scenario and a Data Modeling scenario.

Project Use Case 1: Data Analytics Scenario

In data analytics, Snowflake is utilized for gleaning insights from large datasets. This scenario involves creating a near real-time sales analytics dashboard based on an e-commerce sales data.

Step 1: Load the Data into Snowflake

• After obtaining your data from your source, clean and preprocess it according to your requirements.
• Create your table structure in Snowflake that matches the format of your data.
• Use the 'PUT' command to stage your files, and ‘COPY INTO’ command to load data into the Snowflake table.

create or replace table ecommerce_sales(
  order_id string, 
  product_id string, 
  customer_id string, 
  sales float, 
  order_date date
); 

put file://path_to_your_file/ecommerce_sales.csv @~ auto_compress=true;

copy into ecommerce_sales
  from @~
  file_format = (type = 'CSV' field_optionally_enclosed_by='"')
  pattern='.*\.csv\.gz';

Step 2: Perform Data Analytics Query

You can now perform your sales analytics upon the data using SQL in Snowflake. An example analytics query might be:

SELECT product_id, COUNT(order_id) AS order_count, SUM(sales) AS total_sales
FROM ecommerce_sales
GROUP BY product_id
ORDER BY total_sales DESC;

Project Use Case 2: Data Modeling Scenario

In a data modeling scenario, Snowflake is used to streamline internal database structures for better usability, performance, and data governance.

Step 1: Create a Data Model

Let's consider a retail organization's sales database that needs to be normalized and organized in Snowflake. We'll start by creating the necessary tables.

CREATE TABLE CUSTOMERS (
  CUSTOMER_ID STRING PRIMARY KEY,
  NAME STRING,
  EMAIL STRING,
  ADDRESS STRING
);

CREATE TABLE PRODUCTS (
  PRODUCT_ID STRING PRIMARY KEY,
  NAME STRING,
  PRICE FLOAT
);

CREATE TABLE ORDERS (
  ORDER_ID STRING PRIMARY KEY,
  CUSTOMER_ID STRING,
  PRODUCT_ID STRING,
  QUANTITY INT,
  SALE_DATE DATE,
  FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID),
  FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

Step 2: Insert Sample Data

Next, we populate these tables with some data.

INSERT INTO CUSTOMERS VALUES ('CUST123', 'John Doe', 'john.doe@example.com', '123 Main St');
INSERT INTO PRODUCTS VALUES ('PROD456', 'Widget', 19.99);
INSERT INTO ORDERS VALUES ('ORD789', 'CUST123', 'PROD456', 1, '2022-01-01');

Step 3: Query the Data Model

Now that the database is structured and populated, querying is straightforward. For instance, to find out what products a particular customer bought:

SELECT p.NAME
FROM ORDERS o
JOIN PRODUCTS p ON o.PRODUCT_ID = p.PRODUCT_ID
WHERE o.CUSTOMER_ID = 'CUST123';

To implement Snowflake in real-world projects, it is essential not only to understand its features but also to know how these features can be combined together to solve complex use cases. These real-world project scenarios should give you practical exposure to how Snowflake can be implemented in everyday business situations.