SNOWFLAKE Classes

Difference between Database and Data warehouse

The terms "database" and "data warehouse" both refer to systems for storing and
managing data, but they serve different purposes and have distinct characteristics
Database: Data Warehouse:
 Purpose: Primarily used to store  Purpose: Designed for analytical
and manage transactional data. It's querying and reporting. It's used to
designed for operations like store large volumes of historical
inserting, updating, deleting, and data aggregated from multiple
querying data in real-time. sources for analysis.
 Data Structure: Usually consists of  Data Structure: Organized for
structured data organized into querying and analysis, often in a
tables with rows and columns. denormalized format (e.g., star or
Supports normalization to reduce snowflake schema) to optimize
redundancy. complex queries.
 Examples: Relational databases  Examples: Snowflake, Amazon
(e.g., MySQL, PostgreSQL, Oracle), Redshift, Google BigQuery,
NoSQL databases (e.g., MongoDB, Microsoft Azure Synapse.
Cassandra).
 Operations: Focuses on complex
 Operations: Supports CRUD queries and aggregations, often
(Create, Read, Update, Delete) using SQL. Supports ETL (Extract,
operations, often through SQL Transform, Load) processes to
(Structured Query Language). import and transform data from
various sources.
 Performance: Optimized for fast
reads and writes of individual  Performance: Optimized for
records, suitable for OLTP (Online reading large datasets and
Transaction Processing) systems. performing complex queries,
suitable for OLAP (Online
 Data Volume: Typically manages
Analytical Processing) systems.
smaller volumes of data compared
 to data warehouses, focusing on  Data Volume: Can handle large
current data. volumes of data, often spanning
many years, allowing for historical
 Users: Often used by applications,
analysis and trend identification.
end-users, and operational staff
who need to access and modify  Users: Primarily used by data
data in real-time. analysts, business intelligence
professionals, and decision-makers
who need to analyze large datasets
and generate reports.

Key Differences:
 Focus: Databases focus on transaction processing, while data warehouses
focus on analysis.
 Data: Databases handle current, detailed data, data warehouses handle
historical, aggregated data.
 Design: Databases use normalization; data warehouses often
use denormalization for query performance.
 Performance: Databases are optimized for transactional speed; data
warehouses are optimized for analytical queries.
In summary a database is for day-to-day operations and transaction processing,
while a data warehouse is for analyzing large datasets to inform business
decisions.
Snowflake_Introduction:

Snowflake Architecture

Snowflake’s architecture is a hybrid of traditional shared-disk and shared-nothing

database architectures.
Similar to shared-disk architectures, Snowflake uses a central data repository for
persisted data that is accessible from all compute nodes in the platform.
But similar to shared-nothing architectures, Snowflake processes queries using
MPP (massively parallel processing) compute clusters where each node in the
cluster stores a portion of the entire data set locally.
This approach offers the data management simplicity of a shared-disk
architecture, but with the performance and scale-out benefits of a shared-
nothing architecture.
Snowflake’s unique architecture consists of three key layers:
 Database Storage
 Query Processing
 Cloud Services
Database Storage
When data is loaded into Snowflake, Snowflake reorganizes that data into its
internal optimized, compressed, columnar format. Snowflake stores this optimized
data in cloud storage.
Snowflake manages all aspects of how this data is stored — the organization, file
size, structure, compression, metadata, statistics, and other aspects of data
storage are handled by Snowflake. The data objects stored by Snowflake are not
directly visible nor accessible by customers; they are only accessible through SQL
query operations run using Snowflake
We have two types of storage here
1. ON-Demand Storage.
2. Pre-Computed Storage.
Query Processing
Query execution is performed in the processing layer. Snowflake processes
queries using “virtual warehouses”. Each virtual warehouse is an MPP compute
cluster composed of multiple compute nodes allocated by Snowflake from a
cloud provider.
Each virtual warehouse is an independent compute cluster that does not share
compute resources with other virtual warehouses. As a result, each virtual
warehouse has no impact on the performance of other virtual warehouses.

Cloud Services
The cloud services layer is a collection of services that coordinate activities
across Snowflake. These services tie together all of the different components of
Snowflake in order to process user requests, from login to query dispatch. The
cloud services layer also runs on compute instances provisioned by Snowflake
from the cloud provider.
Services managed in this layer include:
 Authentication
 Infrastructure management
 Metadata management
 Query parsing and optimization
 Access control

MICRO PARTITIONS

In Snowflake, micro-partitions are a fundamental aspect of how data is stored and

managed within the platform. They play a critical role in optimizing query
performance and data management. Here's an overview of micro-partitions in
Snowflake:
What are Micro-Partitions?
 Definition: Micro-partitions are Snowflake's internal method of organizing
and storing data. When you load data into a Snowflake table, the data is
automatically divided into small, contiguous units of storage called micro-
partitions.
 Size: Each micro-partition typically contains between 50 MB to 500 MB of
compressed data. The exact size can vary based on the amount of data and
how it's compressed.
 Columnar Storage: Data within micro-partitions is stored in a columnar
format, which means each column of data is stored separately. This format
is highly efficient for analytical queries that often target specific columns
rather than entire rows.
  Immutable: Once created, micro-partitions are immutable, meaning they
cannot be altered. Any modifications to the data (such as updates or
deletes) result in new micro-partitions being created, while the old ones
remain unchanged until they're no longer needed and are eventually
purged.
Key Features of Micro-Partitions:
 Automatic Partitioning: Snowflake automatically manages the creation
and maintenance of micro-partitions. Users don’t need to manually define
partitioning keys or strategies, as the platform handles this automatically
based on how data is loaded.
 Data Clustering: Micro-partitions are automatically clustered based on the
order in which data is inserted. Snowflake uses the concept of natural
clustering, meaning that the order of data loading influences how it's
physically stored.
 Metadata Storage: Snowflake stores metadata about each micro-partition,
including the range of values for each column (min/max), number of
distinct values, and other statistical information. This metadata is used to
optimize queries by allowing the query engine to skip irrelevant micro-
partitions, reducing the amount of data scanned.
 Automatic Pruning: During query execution, Snowflake’s query optimizer
uses the metadata associated with micro-partitions to perform automatic
pruning. This means only the necessary micro-partitions that contain
relevant data are scanned, significantly improving query performance.
Benefits of Micro-Partitions:
 Performance Optimization: By storing data in micro-partitions and utilizing
metadata for pruning, Snowflake can execute queries more efficiently,
especially when dealing with large datasets.
 Scalability: Micro-partitions allow Snowflake to scale effectively as data
volume grows. Data is distributed across micro-partitions, which can be
processed in parallel, enabling fast query execution even on large datasets.
  Cost Efficiency: Since micro-partitions allow for selective data scanning, they
help reduce the amount of data processed during queries, which can lower
the cost of running queries in Snowflake.
Best Practices:
 Clustering Keys: While Snowflake handles clustering automatically, in some
cases, defining a clustering key can improve query performance. A
clustering key is a set of columns that influences how data is clustered in
micro-partitions, which can optimize query performance for specific types
of queries.
 Avoid Over-Clustering: Be cautious with clustering too frequently, as it can
lead to unnecessary costs and complexity. Snowflake’s automatic clustering
often suffices for many use cases.
Micro-partitions are central to how Snowflake delivers high performance,
scalability, and efficiency in handling large datasets. They abstract away much of
the complexity associated with data storage and partitioning, allowing users to
focus on querying and analyzing their data.
 WAREHOUSES
In Snowflake, a warehouse is a virtual compute resource that provides the
necessary power to execute SQL queries, load data, and perform other
operations. Warehouses are central to how Snowflake manages compute
resources, as they determine the processing power allocated to tasks.
Key Concepts of Warehouses in Snowflake
1. Purpose of Warehouses
 Processing Power: Warehouses are responsible for running queries, loading
data, and performing other compute-intensive tasks.
 Independent of Storage: Warehouses handle only the compute aspect,
while Snowflake's storage layer manages data storage. Compute and
storage are decoupled, allowing flexibility in scaling.
 On-Demand Scaling: You can resize or pause warehouses as needed,
allowing you to control costs and performance.
2. Warehouse Sizes
Snowflake offers different warehouse sizes to suit various workloads. These sizes
determine the amount of compute power available and range from X-Small to 4X-
Large and even larger for specialized needs. The size of the warehouse affects:
 Concurrency: How many queries can run simultaneously.
 Query Performance: Larger warehouses offer faster query performance due
to more compute resources.
 Cost: Larger warehouses consume more credits (Snowflake's billing unit).
Increasing and decreasing the size of warehouse is known as SCALEIN &
SCALEOUT
Available Warehouse Sizes:
 X-Small (1 node)
 Small (2 nodes)
 Medium (4 nodes)
  Large (8 nodes)
 X-Large (16 nodes)
 2X-Large (32 nodes)
 3X-Large (64 nodes)
 4X-Large (128 nodes)
 5X-Large and 6X-Large will be created by snowflake
Example:
sql
CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'LARGE';
3. Warehouse States
 Running: The warehouse is active and ready to execute queries.
 Paused: The warehouse is not running and therefore not consuming credits.
You can manually pause a warehouse when it's not in use to save costs.
 Resumed: A warehouse can be resumed from a paused state when you
need it to process queries again.
Example:
sql
ALTER WAREHOUSE my_warehouse SUSPEND; -- Pause the warehouse
ALTER WAREHOUSE my_warehouse RESUME; -- Resume the warehouse
4. Scaling Policy
Snowflake offers two ways for warehouses to handle varying workloads:
 Auto-Scaling: Allows the warehouse to automatically add or reduce
compute resources (in terms of additional clusters) based on query
demand.
o Max Clusters: Defines the maximum number of clusters the
warehouse can scale to.
 o Min Clusters: Defines the minimum number of clusters.
o Scaling helps optimize performance during peak demand and saves
costs during low activity.
Increasing or decreasing of no of clusters(cluster count) is know as SCALEUP and
SCALEDOWN
Example:
sql
CREATE WAREHOUSE my_warehouse
WITH WAREHOUSE_SIZE = 'LARGE'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 5;
5. Auto-Suspend and Auto-Resume
Snowflake provides the ability to auto-suspend and auto-resume warehouses:
 Auto-Suspend: If the warehouse is inactive for a specified amount of time,
it automatically suspends to save on costs.
 Auto-Resume: When a query is issued, Snowflake automatically resumes
the warehouse, ensuring that it's available when needed.
Example:
sql
ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300; -- Suspend after 5
minutes of inactivity
ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE; -- Automatically
resume on query execution
6. Concurrency Scaling
  When a warehouse becomes overloaded with multiple queries, Snowflake
can automatically scale it by adding additional concurrency scaling clusters.
These clusters are temporary and do not affect the primary warehouse’s
configuration.
 Concurrency scaling allows more queries to run in parallel without the need
for users to manually adjust warehouse settings.
Concurrency scaling is free of charge for most Snowflake editions.
7. Warehouse Monitoring and Management
You can monitor the performance of your warehouse through Snowflake’s
interfaces (web UI, SQL queries, or API):
 Query History: Allows you to check the performance of specific queries.
 Warehouse Load and Usage: Monitor how the warehouse resources are
being utilized to adjust size and cost-effectiveness.
 Suspended or Running State: View whether a warehouse is actively
consuming credits or paused.
Example for checking warehouse usage:
sql
SHOW WAREHOUSES;
8. Cost Management
 Snowflake Warehouses are billed based on compute credits: The larger the
warehouse size, the more credits you consume.
 A key part of cost management is adjusting warehouse sizes and using
features like auto-suspend to ensure you're not running the warehouse
unnecessarily.
 Warehouses can be resized to balance between performance and cost.

Key Commands for Warehouses in Snowflake

1. Creating a Warehouse:
sql
Copy code
CREATE WAREHOUSE my_warehouse
WITH WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE;
2. Altering a Warehouse (e.g., changing size or settings):
sql
Copy code
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
3. Pausing and Resuming a Warehouse:
sql

ALTER WAREHOUSE my_warehouse SUSPEND; -- Pauses the warehouse

ALTER WAREHOUSE my_warehouse RESUME; -- Resumes the warehouse
4. Dropping a Warehouse:
sql

DROP WAREHOUSE my_warehouse;

5. Showing Warehouse Details:
sql

SHOW WAREHOUSES;

Summary
  Snowflake warehouses provide compute resources needed for query
execution and data operations.
 They are highly scalable, can be resized, and have features like auto-scaling,
auto-suspend, and auto-resume to optimize performance and cost-
efficiency.
 Different warehouse sizes are available to suit the workload, and features
like concurrency scaling allow handling high query loads without manual
intervention.
 Efficient warehouse management is crucial for balancing performance and
controlling costs in Snowflake.
Understanding how warehouses work and how to optimize them is essential to
managing workloads effectively in Snowflake.
Note:
Two Ways to choose warehouse size like increase in clusters count and increasing
the size of ware house

STORAGE INTEGRATION
To create a storage integration in Snowflake with AWS (Amazon S3), you'll
configure a secure connection that allows Snowflake to interact with data stored
in an S3 bucket. The process involves setting up an IAM role in AWS and creating a
storage integration in Snowflake. This will allow Snowflake to read from and write
to the S3 bucket without needing to expose AWS credentials directly.
Steps to Set Up Storage Integration in Snowflake with AWS S3
Step 1: Set Up an IAM Role in AWS
1. Create an IAM Role in AWS
o Go to the IAM (Identity and Access Management) console in AWS.
o Create a new role with the "Another AWS Account" option.
o Specify Snowflake's AWS Account ID based on your Snowflake region
(e.g., for us-west-2, it's 123456789012, the actual ID depends on your
Snowflake region).
o Check the box to allow "Require external ID" and set the External ID
as a unique identifier generated by Snowflake (this will be provided
when creating the integration in Snowflake).
2. Attach a Policy to the IAM Role
o Add the following permissions policy to the role, allowing Snowflake
to read from the S3 bucket.
Example policy:
Json
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "",
"Effect": "Allow",
 "Principal": {
"AWS": "arn:aws:iam::565393053492:user/elus0000-s"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId":
"TO09223_SFCRole=5_3AUZcdb56be8B31KybOfL/q7Blw="
}
}
}
]
}
o Replace my-bucket with the actual bucket name and folder path
where the data is stored or where you intend to unload data.
3. Copy the Role ARN
o After creating the IAM role, copy its Amazon Resource Name (ARN)
(e.g., arn:aws:iam::123456789012:role/snowflake-access-role).
Step 2: Create a Storage Integration in Snowflake
1. Create the Storage Integration
o In Snowflake, create a new storage integration that links to the S3
bucket via the IAM role.
Example:
sql
Copy code
CREATE STORAGE INTEGRATION my_s3_integration
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-access-
role'
STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/my-folder/')
STORAGE_AWS_EXTERNAL_ID = '<generated_external_id>'; -- This ID will be auto-
generated by Snowflake
Key Components:
o STORAGE_AWS_ROLE_ARN: The ARN of the IAM role you created in
AWS.
o STORAGE_ALLOWED_LOCATIONS: The S3 bucket(s) or folder(s) that
Snowflake can access via this integration.
o STORAGE_AWS_EXTERNAL_ID: A unique ID generated by Snowflake
to ensure secure access to the IAM role. You’ll need this ID when
setting up the IAM role in AWS.
2. Retrieve the External ID from Snowflake
o After running the above SQL command, you can retrieve the
generated External ID by running the following command:
sql
Copy code
DESC INTEGRATION my_s3_integration;
o The STORAGE_AWS_EXTERNAL_ID will appear in the result. You’ll
need to update the IAM role in AWS with this value under the
External ID section (if you haven't done so already).
Step 3: Grant Permissions in Snowflake
1. Grant Access to Users or Roles:
 o Once the integration is set up, you can grant access to this integration
to specific users or roles in Snowflake.
Example:
sql
Copy code
GRANT USAGE ON INTEGRATION my_s3_integration TO ROLE my_role;
Step 4: Create an External Stage Using the Storage Integration
Once the storage integration is created and linked to the S3 bucket, you can create
an external stage in Snowflake to load or unload data.
1. Create an External Stage:
o The external stage references the S3 bucket and uses the storage
integration for secure access.
Example:
sql
Copy code
CREATE STAGE my_s3_stage
URL = 's3://my-bucket/my-folder/'
STORAGE_INTEGRATION = my_s3_integration;
2. Loading Data from S3:
o You can now use this stage to load data from the S3 bucket into a
Snowflake table.
Example:
sql
Copy code
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV');
3. Unloading Data to S3:
o Similarly, you can use the stage to unload data from Snowflake to S3.
Example:
sql

COPY INTO @my_s3_stage

FROM my_table
FILE_FORMAT = (TYPE = 'CSV');

Summary of Storage Integration with AWS S3

1. Create an IAM Role in AWS that allows access to the S3 bucket. Assign the
necessary permissions to read from or write to the bucket.
2. Create a Storage Integration in Snowflake using the AWS role ARN and
allow Snowflake to securely access the S3 bucket.
3. Link the Storage Integration to an External Stage to load/unload data
between Snowflake and S3.
4. Manage Permissions: Assign Snowflake roles access to the storage
integration and stage.
 LOADING & UNLOADING THE DATA

Loading and unloading data in Snowflake involves transferring data between

Snowflake and external sources such as cloud storage (Amazon S3, Azure Blob
Storage, Google Cloud Storage) or local files. Below are the detailed steps for
loading data into Snowflake from external files, and unloading data from
Snowflake to an external location.
1. Loading Data into Snowflake
There are several ways to load data into Snowflake, including:
 Bulk Loading with COPY INTO (recommended for large data sets).
 Using the Web Interface.
 Using Snowpipe (for continuous data ingestion).
The COPY INTO command is the most common and efficient for bulk loading data.
Prerequisites for Loading Data:
1. Create a Table: The table in Snowflake where data will be loaded.
2. Create an External Stage or Internal Stage:
o External Stage: Use this if the data resides in cloud storage (e.g., S3,
Azure Blob, GCS).
o Internal Stage: Snowflake’s internal storage for temporarily holding
files.

Example 1: Loading Data from an S3 Bucket to Snowflake

Step 1: Create a Stage (if external storage is used)
 Use a storage integration or credentials for secure access to external cloud
storage.
sql
Copy code
CREATE STAGE my_s3_stage
URL = 's3://my-bucket/my-folder/'
STORAGE_INTEGRATION = my_s3_integration; -- Ensure you have a storage
integration set up
Step 2: Create a Table for Loading Data
 Assume you are loading a CSV file with data about products.
sql
Copy code
CREATE OR REPLACE TABLE products (
product_id INTEGER,
product_name STRING,
category STRING,
price FLOAT
);
Step 3: Load Data Using COPY INTO
 Use the COPY INTO command to load data from the stage (S3) into the
Snowflake table.
sql
Copy code
COPY INTO products
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
PATTERN = '.*products_data_.*.csv'; -- This is optional to match specific files
Step 4: Verify the Data Load
 After loading, check the data in the target table to verify the load.
sql
Copy code
SELECT * FROM products;

Example 2: Loading Data from a Local File to Snowflake

Step 1: Use PUT Command to Upload Files to an Internal Stage
 If the file is on your local machine, upload it to Snowflake's internal stage
first.
sql
Copy code
PUT [Link] @%products;
Step 2: Load Data from the Internal Stage to the Table
 After uploading, load the data into your table using the COPY INTO
command.
sql
Copy code
COPY INTO products
FROM @%products
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"');

2. Unloading Data from Snowflake

Unloading data means extracting data from Snowflake and storing it in an external
location, such as a cloud storage service (S3, Azure Blob, GCS). Snowflake uses the
COPY INTO command to export data to these storage systems.
Example: Unloading Data from Snowflake to S3
Step 1: Create an External Stage (if not already done)
  You can use an external stage to define the S3 location where the data will
be unloaded.
sql
Copy code
CREATE STAGE my_s3_stage
URL = 's3://my-bucket/my-folder/'
STORAGE_INTEGRATION = my_s3_integration;
Step 2: Unload Data from a Table into an S3 Bucket
 Use the COPY INTO command to export data from Snowflake into an
external storage system.
sql
Copy code
COPY INTO @my_s3_stage
FROM products
FILE_FORMAT = (TYPE = 'CSV')
HEADER = TRUE; -- Include column headers in the CSV file
This command will unload the data from the products table to the specified S3
bucket in CSV format.
Step 3: Verify the Data Unload
 After the process is complete, the files will be available in the S3 bucket.

3. File Formats for Loading/Unloading Data

When loading or unloading data, Snowflake supports multiple file formats,
including:
 CSV: The most commonly used file format for both loading and unloading.
 JSON: Supports semi-structured data.
  Parquet: Optimized for columnar storage.
 Avro: Another format for semi-structured data.
 ORC: An optimized format for big data systems.
You define file formats when loading or unloading data using the FILE_FORMAT
parameter.
Example of Creating a File Format:
sql
Copy code
CREATE OR REPLACE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
SKIP_HEADER = 1;
You can reference this format when loading or unloading data:
sql
Copy code
COPY INTO products
FROM @my_s3_stage
FILE_FORMAT = my_csv_format;

4. Loading Data Continuously with Snowpipe

If you want to load data continuously as new files arrive in the external storage
, you can use Snowpipe. Snowpipe is a Snowflake service designed for continuous
data ingestion, automatically loading new data into Snowflake as soon as it lands
in cloud storage (e.g., S3, Azure Blob, or GCS).
Here’s a summary of how Snowpipe works for continuous data loading:
1. Set Up Snowpipe for Continuous Loading
Step 1: Create a Stage for Your Cloud Storage
Define an external stage where new files will arrive. For example, an S3 bucket:
sql
Copy code
CREATE STAGE my_snowpipe_stage
URL = 's3://my-bucket/my-folder/'
STORAGE_INTEGRATION = my_s3_integration;
Step 2: Create a Pipe for Continuous Data Loading
Create a pipe that uses Snowpipe to automatically load new data from the stage
into a Snowflake table:
sql
Copy code
CREATE OR REPLACE PIPE my_snowpipe
AS
COPY INTO products
FROM @my_snowpipe_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'CONTINUE'; -- Handles bad records without stopping the load
Step 3: Automate Snowpipe with Event Notifications
Set up event notifications (such as AWS SNS/SQS for S3, Azure Event Grid, or
Google Pub/Sub) to trigger Snowpipe automatically whenever new data lands in
the external storage.
In AWS, you would configure an S3 bucket notification to send a message to an
SNS topic or SQS queue whenever new objects are uploaded. Snowflake will
monitor these notifications and initiate the data load into the defined table
automatically.

Key Benefits of Snowpipe:

 Continuous Data Loading: Automates the data load process, making it real-
time or near-real-time.
 Event-Driven: Uses cloud provider events to trigger data ingestion.
 Granular Cost Control: Charges are based on the data processed, providing
a more cost-effective solution for smaller, continuous loads.

Summary of Loading and Unloading Data in Snowflake:

 Loading Data:
o Use the COPY INTO command for bulk loads.
o Load data from external (S3, Azure Blob, GCS) or local sources (using
internal stages).
o Supports multiple file formats such as CSV, JSON, Parquet, Avro, and
ORC.
 Unloading Data:
o Use the COPY INTO command to export data from Snowflake to cloud
storage.
o Unload data in various formats (CSV, Parquet, etc.).
 Snowpipe:
o Automates continuous data loading for real-time data ingestion.
o Triggered by cloud storage events like file uploads.
This provides flexibility to handle both large batch data loads and continuous real-
time data ingestion efficiently in Snowflake.
 DATA TRANSFORMATION OF SEMISTRUCTURED
JSON DATA

Data transformation of semi-structured data, such as JSON, in Snowflake involves

parsing, querying, and transforming the semi-structured data into structured
formats like tables. Snowflake provides built-in functions and features to handle
and transform JSON data.
Steps for Transforming Semi-Structured JSON Data
1. Loading JSON Data into Snowflake
First, load the raw JSON data into Snowflake, typically into a VARIANT column,
which is Snowflake’s data type for semi-structured data. You can load JSON data
using the COPY INTO command as described earlier.
Example: Create a Table with a VARIANT Column
sql
Copy code
CREATE OR REPLACE TABLE raw_json_data (
json_content VARIANT
);
Loading JSON Data
If the JSON data is stored in a file (e.g., [Link] in an S3 bucket), use COPY INTO
to load the data into the json_content column:
sql
Copy code
COPY INTO raw_json_data
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'JSON');
The JSON data will now be stored in the json_content column as semi-structured
data (in the VARIANT type).

2. Querying and Parsing JSON Data

Snowflake allows you to query JSON data using dot notation and the :field
notation for nested fields. These notations let you access and extract specific
fields from the JSON structure.
Example JSON Data:
json
Copy code
{
"id": 1,
"name": "Product A",
"category": "Electronics",
"price": 99.99,
"attributes": {
"color": "red",
"weight": 1.2
}
}
Querying JSON Fields in Snowflake
sql
Copy code
SELECT
json_content:id::INT AS product_id,
 json_content:name::STRING AS product_name,
json_content:category::STRING AS category,
json_content:price::FLOAT AS price,
json_content:[Link]::STRING AS color,
json_content:[Link]::FLOAT AS weight
FROM raw_json_data;
This query will extract specific fields from the JSON data and return them as
structured columns.
 :field notation: Extracts the field from the JSON object.
 ::datatype: Casts the extracted field into a specific data type (e.g., STRING,
FLOAT, etc.).

3. Flattening Nested JSON Arrays

If the JSON contains arrays (e.g., lists of objects), you can flatten the arrays using
the FLATTEN function. This is useful for dealing with complex JSON structures that
contain nested arrays.
Example JSON with an Array:
json
Copy code
{
"id": 2,
"name": "Product B",
"category": "Electronics",
"price": 199.99,
"reviews": [
{"user": "Alice", "rating": 5, "comment": "Excellent"},
 {"user": "Bob", "rating": 4, "comment": "Good"}
]
}
Flattening the JSON Array
sql
Copy code
SELECT
json_content:id::INT AS product_id,
json_content:name::STRING AS product_name,
json_content:category::STRING AS category,
json_content:price::FLOAT AS price,
[Link]:user::STRING AS reviewer,
[Link]:rating::INT AS rating,
[Link]:comment::STRING AS comment
FROM raw_json_data,
LATERAL FLATTEN(input => json_content:reviews) AS review;
 LATERAL FLATTEN: Unpacks each element in the JSON array (reviews),
turning them into individual rows.
 [Link]:field: Extracts values from the elements in the array.
This query will return each review in a separate row, linked to the corresponding
product.

4. Creating a Structured Table from JSON Data

After extracting the relevant fields from the JSON, you can insert the transformed
data into a structured table.
Step 1: Create the Target Table
sql
Copy code
CREATE OR REPLACE TABLE structured_products (
product_id INT,
product_name STRING,
category STRING,
price FLOAT,
reviewer STRING,
rating INT,
comment STRING
);
Step 2: Insert Transformed Data into the Table
sql
Copy code
INSERT INTO structured_products
SELECT
json_content:id::INT AS product_id,
json_content:name::STRING AS product_name,
json_content:category::STRING AS category,
json_content:price::FLOAT AS price,
[Link]:user::STRING AS reviewer,
[Link]:rating::INT AS rating,
[Link]:comment::STRING AS comment
FROM raw_json_data,
LATERAL FLATTEN(input => json_content:reviews) AS review;
This query inserts the flattened, structured data into the structured_products
table.

5. Working with JSON Data in Snowflake

Snowflake offers several functions and operators to manipulate JSON data
efficiently:
 PARSE_JSON(): Converts a string into a JSON object (VARIANT type).
 TO_VARIANT(): Casts a value to the VARIANT type.
 OBJECT_INSERT(): Inserts a key-value pair into a JSON object.
 OBJECT_DELETE(): Removes a key from a JSON object.
 ARRAY_SIZE(): Returns the size of a JSON array.
 ARRAY_AGG(): Aggregates multiple rows into a JSON array.
Example: Insert a New Key-Value Pair into JSON
sql
Copy code
SELECT
OBJECT_INSERT(json_content, 'discount', 10) AS updated_json
FROM raw_json_data;

Summary of JSON Data Transformation in Snowflake:

1. Load JSON data into a VARIANT column in a Snowflake table.
2. Query JSON fields using dot notation or : notation to extract specific fields.
3. Flatten nested JSON arrays using the FLATTEN function.
 4. Insert transformed data into a structured table for easier querying and
reporting.
5. Use Snowflake's JSON functions for advanced manipulation of JSON data.
Snowflake’s ability to handle and query semi-structured data like JSON allows you
to efficiently transform it into structured formats suitable for analysis.
 SNOWFLAKE TASKS

Snowflake Tasks are a key feature that allow you to automate the execution of
SQL statements (typically INSERT, UPDATE, or DELETE statements) in a scheduled
or event-driven manner. They are especially useful for managing ETL (Extract,
Transform, Load) workflows, data refreshes, or data transformation pipelines.
Tasks can be used to create a workflow where one task triggers another, enabling
sequential or dependent task execution.
Key Features of Snowflake Tasks:
1. Automated SQL Execution: Schedule SQL queries or set up event-driven
task execution.
2. Task Scheduling: Define tasks to run at specific intervals.
3. Task Dependencies: Chain tasks together so that one task automatically
triggers another.
4. Event-Driven Tasks: Use tasks triggered by data arrival in a stage (e.g.,
Snowpipe).
5. Versioning & History: Keep track of execution logs and task versioning.
Types of Snowflake Tasks:
1. Standalone Tasks: Execute independently based on a schedule.
2. Tree of Tasks: Create dependent tasks where one task triggers another.

1. Creating a Snowflake Task

To create a task, you use the CREATE TASK statement, defining the schedule, the
SQL command to be executed, and any optional dependencies.
Basic Structure:
sql
Copy code
CREATE OR REPLACE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = 'USING CRON 0 0 * * *' -- Daily at midnight
AS
INSERT INTO my_table
SELECT * FROM staging_table
WHERE processed = FALSE;
 WAREHOUSE: The virtual warehouse that will execute the task.
 SCHEDULE: When to execute the task. This can be done using a CRON
expression or a simple interval.
 SQL Statement: The query that the task will run (in this case, copying data
from a staging table).
Example: Simple Task
This task runs every hour and moves data from staging_table to final_table.
sql
Copy code
CREATE OR REPLACE TASK hourly_task
WAREHOUSE = my_warehouse
SCHEDULE = '1 HOUR'
AS
INSERT INTO final_table
SELECT * FROM staging_table
WHERE loaded = FALSE;
2. Scheduling Options
Snowflake tasks can be scheduled using either a CRON expression or a simple
time interval.
2.1. Using CRON for Scheduling
A CRON expression allows for highly flexible scheduling.
CRON Expression Example:
 0 0 * * * → Runs daily at midnight.
 */15 * * * * → Runs every 15 minutes.
 0 0 1 * * → Runs on the first day of every month at midnight.
sql
Copy code
CREATE OR REPLACE TASK daily_task
WAREHOUSE = my_warehouse
SCHEDULE = 'USING CRON 0 0 * * *'
AS
DELETE FROM user_sessions WHERE last_active < CURRENT_DATE - 30;
2.2. Using Simple Intervals
You can also schedule tasks to run at a fixed interval, such as every hour, minute,
or day.
Simple Interval Example:
sql
Copy code
CREATE OR REPLACE TASK hourly_refresh_task
WAREHOUSE = my_warehouse
 SCHEDULE = '1 HOUR'
AS
CALL refresh_table();

3. Task Dependencies (Chaining Tasks)

You can create dependent tasks, where one task triggers another after it finishes.
This is useful for building workflows or pipelines with multiple stages.
Example: Dependent Tasks
You have a multi-stage ETL pipeline where:
 Task 1: Loads data into a staging table.
 Task 2: Cleans and transforms the data.
 Task 3: Inserts the cleaned data into the final table.
Task 1 (Load Data):
sql
Copy code
CREATE OR REPLACE TASK load_data_task
WAREHOUSE = my_warehouse
SCHEDULE = '1 HOUR'
AS
COPY INTO staging_table
FROM @my_s3_stage;
Task 2 (Transform Data, Dependent on Task 1):
sql
Copy code
CREATE OR REPLACE TASK transform_data_task
 WAREHOUSE = my_warehouse
AFTER load_data_task -- Dependency on load_data_task
AS
INSERT INTO transformed_table
SELECT * FROM staging_table
WHERE is_valid = TRUE;
Task 3 (Insert Data, Dependent on Task 2):
sql
Copy code
CREATE OR REPLACE TASK load_final_table_task
WAREHOUSE = my_warehouse
AFTER transform_data_task -- Dependency on transform_data_task
AS
INSERT INTO final_table
SELECT * FROM transformed_table;
In this example, the tasks form a chain:
1. Task 1 runs every hour to load data.
2. When Task 1 completes, it triggers Task 2 to clean the data.
3. When Task 2 completes, it triggers Task 3 to insert the cleaned data into the
final table.

4. Event-Driven Tasks
Snowflake tasks can be triggered by external events, such as the arrival of new
files in cloud storage. Event-driven tasks are commonly used with Snowpipe to
automate data pipelines.
Example: Task Triggered by Data Arrival
In this example, Snowpipe loads data into a table, and a task is triggered once new
data is ingested.
sql
Copy code
CREATE OR REPLACE TASK process_new_data_task
WAREHOUSE = my_warehouse
AFTER SNOWPIPE(my_snowpipe) -- Task runs after the Snowpipe completes
AS
CALL process_newly_loaded_data();
This task automatically runs whenever new data is ingested by the Snowpipe
process, which handles file ingestion from cloud storage.

5. Managing and Monitoring Tasks

 Start/Stop Tasks: You can manually start or stop tasks.
Start a task:
sql
Copy code
ALTER TASK my_task RESUME;
Stop a task:
sql
Copy code
ALTER TASK my_task SUSPEND;
 Viewing Task Status: You can monitor task execution using the SHOW TASKS
command or by querying the INFORMATION_SCHEMA.TASK_HISTORY table
to review task execution history.
sql
Copy code
SHOW TASKS;
Query task history:
sql
Copy code
SELECT *
FROM INFORMATION_SCHEMA.TASK_HISTORY
WHERE NAME = 'my_task';

6. Error Handling and Retrying

 ON_ERROR: You can define how Snowflake should handle errors during task
execution.
Example:
sql
Copy code
CREATE OR REPLACE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = '1 HOUR'
ON_ERROR = CONTINUE -- Ignore errors and continue execution
AS
CALL process_data();

Summary of Snowflake Tasks:

  Automated Workflows: Tasks allow you to automate SQL workflows, such
as ETL jobs or data refreshes.
 Scheduling: You can schedule tasks to run at fixed intervals or using CRON
expressions.
 Task Dependencies: Chain tasks together for multi-step processes where
one task triggers another.
 Event-Driven Execution: Tasks can be triggered by external events, such as
file arrivals in cloud storage.
 Management and Monitoring: You can start, stop, and monitor tasks using
SQL commands and system views like TASK_HISTORY.
This functionality enables you to build efficient, automated data pipelines within
Snowflake, ensuring your data is always up-to-date.
 STREAMS

Streams in Snowflake are a powerful feature designed to track changes (inserts,

updates, and deletes) made to a table over time. Streams enable you to build
incremental data pipelines by providing a way to capture changes in tables for
downstream processing, without manually tracking which rows were modified.
They are commonly used for change data capture (CDC) in ETL processes.
Key Concepts of Snowflake Streams:
1. Change Data Capture (CDC): A stream captures changes (inserts, updates,
and deletes) to a table since the last time the stream was consumed.
2. Incremental Data Processing: You can process only the changed data,
avoiding the need to reprocess entire tables.
3. Non-Persistent: A stream does not store actual data; it tracks metadata
(change records).
4. Streams on Views: Snowflake also supports streams on views, enabling
changes to be tracked in the result set of a query.

Types of Streams in Snowflake:

1. Table Streams: Tracks changes to a base table.
2. Append-Only Streams: Tracks only inserts into the table.
3. Change-Tracking Streams: Tracks inserts, updates, and deletes (also called
Delta Streams).

How Streams Work in Snowflake:

A stream keeps track of changes made to the source table since the last time the
stream was queried. The changes can include:
  Inserts: New rows added to the table.
 Updates: Existing rows that have been modified.
 Deletes: Rows that have been deleted.
Each stream is associated with a table or view, and when you query the stream, it
returns a view of the changes. You can consume the changes in downstream
operations (e.g., for ETL, reporting, or audit purposes).

1. Creating a Stream on a Table

To create a stream, you use the CREATE STREAM statement, specifying the table
that the stream will track.
Example: Create a Stream
sql
Copy code
CREATE OR REPLACE STREAM my_stream
ON TABLE my_table
SHOW_INITIAL_ROWS = TRUE;
 ON TABLE: Specifies the table to track.
 SHOW_INITIAL_ROWS: Optionally includes the current contents of the
table when the stream is first created. By default, streams only track
changes after they are created.

2. Querying a Stream
You query a stream just like a table. The stream will return the changes that
occurred in the source table since the last time the stream was queried.
Example: Querying Changes
sql
Copy code
SELECT *
FROM my_stream;
The query returns a set of rows with metadata indicating how each row changed:
 METADATA$ACTION: Indicates the type of change (INSERT, UPDATE,
DELETE).
 METADATA$IS_UPDATE: Indicates whether the row was part of an update.
 Other columns: The columns from the original table showing the data that
changed.

3. Using Streams for Incremental Processing

Streams are commonly used in incremental data processing or ETL pipelines to
process only the rows that have changed in the source table.
Example: Using Stream to Update a Target Table
You can combine a stream with a task (automated schedule) to move changed
data to another table.
Step 1: Create a Stream on the Source Table
sql
Copy code
CREATE OR REPLACE STREAM my_stream
ON TABLE source_table;
Step 2: Create a Task to Process Changes
This task runs every hour and processes the rows tracked by the stream, updating
the target table:
sql
Copy code
CREATE OR REPLACE TASK process_changes_task
WAREHOUSE = my_warehouse
SCHEDULE = '1 HOUR'
AS
MERGE INTO target_table t
USING my_stream s
ON [Link] = [Link]
WHEN MATCHED AND [Link]$ACTION = 'DELETE' THEN DELETE
WHEN MATCHED AND [Link]$ACTION = 'UPDATE' THEN UPDATE SET t.col1
= s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN INSERT (id, col1, col2) VALUES ([Link], s.col1, s.col2);
 MERGE: The MERGE statement integrates the changes from the stream into
the target table.
o DELETE: If the action is a delete, remove the corresponding row from
the target table.
o UPDATE: If the action is an update, modify the existing rows in the
target table.
o INSERT: If the action is an insert, add new rows to the target table.
Step 3: Query the Target Table
After the task runs, the target table will reflect all the changes captured by the
stream in an incremental fashion.

4. Managing Streams
Checking Stream Information:
You can view stream details using the SHOW STREAMS command:
sql
Copy code
SHOW STREAMS;
Resetting a Stream:
A stream can be reset, which clears its change tracking. You might want to do this
after a large one-time load.
sql
Copy code
ALTER STREAM my_stream SET RESET = TRUE;

5. Types of Actions Captured by Streams

A stream captures the following actions in the table:
 INSERT: When a new row is inserted into the table.
 UPDATE: When an existing row is updated.
 DELETE: When a row is deleted from the table.
You can identify the type of action using the METADATA$ACTION column, which
contains values like INSERT, UPDATE, or DELETE.
Example: Filtering Specific Actions
To retrieve only INSERT operations, you can filter the stream results:
sql
Copy code
SELECT *
FROM my_stream
WHERE METADATA$ACTION = 'INSERT';

6. Streams on Views
Snowflake allows you to create streams on views, meaning you can track changes
in the result set of a query (rather than a physical table).
Example: Create a Stream on a View
sql
Copy code
CREATE OR REPLACE VIEW my_view AS
SELECT id, col1, col2 FROM source_table WHERE condition = 'active';

CREATE OR REPLACE STREAM my_view_stream

ON VIEW my_view;
This allows you to track changes in the view, such as when rows appear or
disappear based on the underlying query.

7. Use Cases for Streams in Snowflake:

 Change Data Capture (CDC): Track incremental changes in tables for ETL or
real-time reporting purposes.
 ETL Pipelines: Use streams to process only new or changed data instead of
reprocessing the entire dataset.
 Audit and Compliance: Track data changes for auditing purposes.
 Data Synchronization: Synchronize changes between different tables or
between Snowflake and external systems.
 Event-Driven Architecture: Use streams to trigger downstream processes in
response to changes in your data.

Summary of Snowflake Streams:

 Streams capture changes (inserts, updates, deletes) in a table or view.
  Metadata (e.g., METADATA$ACTION) helps identify the type of change
(insert, update, delete).
 You can query a stream to capture incremental changes and apply them to
downstream processing (e.g., through tasks or manual queries).
 Streams on views allow for tracking changes in query results.
 Streams are commonly used for CDC (Change Data Capture) and
incremental ETL processing.
Streams, in combination with tasks, provide a flexible and efficient way to build
incremental data pipelines and event-driven processes in Snowflake.
 SNOWPIPE

Snowpipe is a feature in Snowflake that enables continuous data ingestion. It

automates the loading of data from files into Snowflake tables as soon as they are
available in a cloud storage location (like AWS S3, Google Cloud Storage, or Azure
Blob Storage). Unlike traditional batch loading, Snowpipe enables real-time or
near-real-time data loading, which is essential for modern data pipelines.
Key Features of Snowpipe:
1. Continuous Data Loading: Automatically loads data into Snowflake as soon
as files are uploaded to cloud storage.
2. Serverless: Snowpipe is fully managed by Snowflake, and it automatically
scales without requiring manual infrastructure management.
3. Pay-Per-Use: You only pay for the compute resources Snowpipe uses to load
data, based on the amount of data processed.
4. Event-Driven or Manual Triggering: Can be triggered either manually via a
REST API or automatically using cloud provider notifications.
5. Data Validation and Transformation: Provides support for validating and
transforming the data during the load process.

How Snowpipe Works:

1. Data Files Uploaded to Cloud Storage: Data files are staged (uploaded) to a
cloud storage location like S3, Azure Blob, or Google Cloud Storage.
2. Cloud Storage Notifications (optional): Snowpipe can be triggered by event
notifications from the cloud provider, indicating that new data is available
to load.
3. Data Ingestion: Snowpipe continuously monitors the staging location and
loads the data into Snowflake tables.
 4. Transformation: If specified, transformations (like parsing, filtering, etc.)
can be applied during the loading process.

Steps to Set Up Snowpipe

1. Stage Your Data in Cloud Storage
Before Snowpipe can load data into Snowflake, the data must first be uploaded to
a stage (a cloud storage location such as an S3 bucket).
For example, create an external stage for an S3 bucket:
sql
Copy code
CREATE OR REPLACE STAGE my_s3_stage
URL = 's3://my-bucket/data/'
CREDENTIALS = (AWS_KEY_ID = 'your_key' AWS_SECRET_KEY = 'your_secret');
2. Define a Snowpipe
Next, define a Snowpipe to automatically load data from the stage into a target
table.
sql
Copy code
CREATE OR REPLACE PIPE my_pipe
AUTO_INGEST = TRUE
AS
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV');
 AUTO_INGEST = TRUE: Enables automatic triggering of Snowpipe using
cloud notifications.
  COPY INTO: Specifies the SQL command that Snowpipe will use to load data
from the stage into the target table.
 FILE_FORMAT: Specifies the format of the data files (e.g., CSV, JSON,
Parquet, etc.).
3. Cloud Storage Event Notifications (Optional but Recommended)
To automate the loading process, you can configure event notifications from your
cloud provider (AWS, Azure, GCP) to notify Snowpipe when new files are uploaded
to the stage.
AWS S3 Example: Set up an S3 event notification that triggers Snowpipe
whenever new files are uploaded to your S3 bucket.
 You configure an S3 event notification to invoke an SNS (Simple Notification
Service) topic or an SQS (Simple Queue Service) queue, which Snowflake
listens to for incoming files.
4. Loading Data
Once Snowpipe is set up, it will automatically load data as new files are staged.
You can monitor Snowpipe activity to ensure files are being loaded.
Monitor the load history:
sql
Copy code
SELECT *
FROM table(information_schema.copy_history(table_name => 'MY_TABLE'));

Manually Triggering Snowpipe

Snowpipe can also be triggered manually if you don't want to set up cloud event
notifications.
To trigger Snowpipe manually, you can use the Snowflake REST API to notify
Snowpipe when new files are staged.
REST API Example:
bash
Copy code
POST /v1/data/pipes/{pipe_name}/insertFiles
In the request, you specify the list of files that need to be ingested.

Example Workflow with Snowpipe and S3 (AWS)

1. Upload Data to S3
Files (e.g., [Link], [Link]) are uploaded to an S3 bucket.
2. Snowpipe Triggers Data Load
Based on S3 event notifications, Snowpipe detects the new files in the bucket and
automatically loads the data into the specified Snowflake table.
3. Query the Loaded Data
Once the data is loaded, it is immediately available for querying.
sql
Copy code
SELECT * FROM my_table;

Monitoring Snowpipe
You can monitor Snowpipe's performance and track the status of files being
ingested using several system views:
 COPY History: View detailed history of the files that have been loaded using
Snowpipe.
sql
Copy code
SELECT *
FROM TABLE(information_schema.copy_history(table_name => 'my_table'));
  Pipe Notifications: View the status of notifications from the cloud storage.
sql
Copy code
SELECT *
FROM information_schema.notifications
WHERE pipe_name = 'my_pipe';
 Task Activity: Snowpipe tasks, like loading data, are tracked in the task
history.
sql
Copy code
SHOW PIPES;

Use Cases for Snowpipe

1. Real-Time Data Loading: Ideal for applications that need to ingest and
process streaming data, such as IoT sensors, application logs, or financial
transactions.
2. Event-Driven Pipelines: Automatically load data as soon as it's available in
cloud storage, triggering downstream processes like analytics or reporting.
3. Incremental ETL Processes: Continuously update your data warehouse by
loading new or updated files as they arrive.
4. Data Lake Integration: Easily integrate with cloud-based data lakes (S3,
Azure Blob, GCS) for hybrid data management.

Benefits of Snowpipe
 Automated Ingestion: Snowpipe simplifies data pipelines by automating
the process of loading data into Snowflake tables.
  Scalability: Snowpipe handles variable data volumes and automatically
scales to accommodate changes in workload.
 Cost-Efficient: Snowpipe is a pay-per-use service, charging based on the
amount of data processed rather than maintaining a long-running compute
resource.
 Near Real-Time Data: Snowpipe ensures data is ingested in near real-time,
supporting modern, low-latency data-driven applications.

Summary of Snowpipe:
 Snowpipe provides a serverless, automated solution for loading data into
Snowflake.
 It supports continuous ingestion, ensuring new data is available as soon as
it's staged in cloud storage.
 You can configure event notifications in cloud storage (S3, Azure, or GCS) to
trigger Snowpipe or use the REST API for manual invocation.
 Pay-per-use pricing model makes Snowpipe cost-effective for handling
variable workloads.
 Ideal for real-time analytics, event-driven ETL pipelines, and incremental
data ingestion.
This makes Snowpipe an excellent choice for any organization looking to
streamline their data pipelines with minimal manual effort.
 TIMETRAVEL AND FAILSAFE

Time Travel and Failsafe are two important data recovery features in Snowflake
that allow users to recover historical data, providing protection against accidental
changes or deletions.
1. Time Travel in Snowflake
Time Travel enables you to access historical data (tables, schemas, or databases)
at any point within a retention period. This feature is crucial for:
 Recovering deleted or modified data.
 Performing audits to view historical data versions.
 Analyzing changes made to the data over time.
Key Concepts of Time Travel:
 Data Retention Period: Time Travel allows you to query, clone, or restore
data to a previous state within a specified retention period. This period is
adjustable and can be up to 90 days, depending on your Snowflake edition.
 Historical Access: You can query previous versions of a table, restore
dropped tables, or clone databases/schemas/tables from the past.
 Actions Covered: Time Travel applies to:
o Dropped tables, schemas, or databases.
o Updated or deleted rows in a table.
Time Travel Retention Periods:
 Standard Edition: Up to 1 day.
 Enterprise Edition: Up to 90 days.
 Virtual Private Snowflake (VPS): Also up to 90 days, with additional security
measures.

How to Use Time Travel:

1. Query Historical Data:
You can query historical data at a specific point in time using the AT clause for
exact timestamps or the BEFORE clause to see the data just before a specific
timestamp.
sql
Copy code
-- Query data at a specific point in time
SELECT *
FROM my_table
AT (TIMESTAMP => '2024-09-10 12:00:00');

-- Query data before a certain change occurred

SELECT *
FROM my_table
BEFORE (STATEMENT => 'statement_id');
2. Restore Dropped Tables or Schemas:
Even after a table is dropped, it can be recovered within the retention period
using Time Travel.
sql
Copy code
-- Restore a dropped table
UNDROP TABLE my_table;

-- Restore a dropped schema

UNDROP SCHEMA my_schema;
3. Clone Historical Data:
You can create a clone of your table, schema, or database from a previous point in
time. This allows you to analyze historical data without affecting the current data.
sql
Copy code
-- Clone a table as it was 2 days ago
CREATE TABLE my_table_clone CLONE my_table
AT (OFFSET => -2 DAYS);

Limitations of Time Travel:

 After the retention period ends, the historical data is purged and can no
longer be accessed via Time Travel.
 Time Travel incurs additional storage costs because Snowflake maintains
historical versions of the data.

2. Failsafe in Snowflake
Failsafe is a last resort data recovery mechanism in Snowflake that extends
beyond Time Travel. It provides Snowflake with a way to recover data after the
Time Travel retention period has expired. Failsafe is intended for use in extreme
cases, such as when all other recovery mechanisms (e.g., Time Travel) have failed.
Key Concepts of Failsafe:
 Failsafe Period: Failsafe is available for an additional 7 days after the Time
Travel period ends. It allows Snowflake to recover data but is not accessible
by the user directly.
 Manual Recovery by Snowflake Support: Failsafe is a manual recovery
process performed by Snowflake’s support team in the event of an
emergency, such as inadvertent deletion after the Time Travel period has
expired.
  Read-Only: Failsafe is read-only and does not allow modifications during
the retention period.
Purpose of Failsafe:
 Emergency Recovery: Failsafe ensures that data can be recovered in the
event of catastrophic failure or major human error. It is not meant to be
used as part of regular operational workflows.
 Data Protection: Snowflake uses Failsafe to provide extra protection against
data loss.
Important Notes on Failsafe:
 Failsafe cannot be bypassed or turned off.
 It incurs no additional storage costs because it is designed solely for data
recovery purposes and not for general use.
 Data in Failsafe is not accessible to users; only Snowflake can access it
during recovery.

Time Travel vs. Failsafe

Feature Time Travel Failsafe
Last-resort recovery for
Access and recover historical data within
Purpose data after Time Travel
the retention period.
expires.
7 days after the Time Travel
Retention Up to 90 days (depends on edition).
period ends.
User Users can access, query, and restore Users cannot directly
Access historical data. access data in Failsafe.
Storage costs apply during the retention No additional cost for
Cost
period. Failsafe.
Use Cases Recovering data after accidental Emergency data recovery
modifications or deletions, cloning after Time Travel has
Feature Time Travel Failsafe
historical data for auditing. expired.
Requires intervention from
Trigger Automatically available for users to utilize.
Snowflake support.

Example Scenarios:
1. Accidentally Dropped Table:
You accidentally drop a table but realize it shortly after.
 Solution: Use the UNDROP command during the Time Travel retention
period to recover the table.
sql
Copy code
UNDROP TABLE my_table;
2. Query Historical Data:
You need to audit how data in a table looked two weeks ago for compliance
purposes.
 Solution: Query the table using the AT or BEFORE clause to retrieve data as
it was two weeks ago.
sql
Copy code
SELECT * FROM my_table
AT (OFFSET => -14 DAYS);
3. Time Travel Period Expires:
You realize a week after the Time Travel period expired that you need the dropped
data.
  Solution: Contact Snowflake Support to initiate the Failsafe recovery
process, as the data is no longer available in Time Travel but still within the
Failsafe window.

Summary
 Time Travel provides a way to access and recover historical data up to 90
days, depending on your Snowflake edition, for use cases like data recovery,
audits, or cloning.
 Failsafe is an additional 7-day period after Time Travel that allows
Snowflake support to recover data in emergency situations when the Time
Travel window has expired.
 Time Travel is meant for regular operational recovery, while Failsafe is a last
resort for catastrophic data loss.