1. Introduction to Snowflake

What is Snowflake?

Snowflake is a cloud-based data warehousing platform that allows organizations to store, process, and analyze large amounts of data. Unlike traditional on-premises databases, Snowflake is built entirely on the cloud, offering scalability, high performance, and simplified management.

Key points:

• Fully-managed service → no infrastructure to maintain

• Supports structured and semi-structured data (JSON, Parquet, Avro, etc.)

• SQL-based interface → easy for data engineers, analysts, and BI teams

Why Snowflake is Popular Among Data Engineers

Snowflake has become a favorite for data engineers for several reasons:

1. Scalability: Compute and storage are separate, allowing warehouses to scale up or down based on demand.

2. Elasticity: Virtual warehouses can automatically pause when not in use and resume instantly.

3. Concurrency: Multiple users and workloads can run simultaneously without performance degradation.

4. Simplified ETL / ELT: Supports batch and streaming data ingestion with minimal setup.

5. Cost-efficient: Pay only for what you use; storage and compute billed separately.

Cloud Data Warehousing vs Traditional Data Warehousing

Snowflake’s Multi-Cloud Support (AWS, Azure, GCP)

Snowflake runs on major cloud providers, giving flexibility to businesses:

• AWS: Original cloud platform for Snowflake, integrates with S3, Redshift, and Glue.

• Azure: Integrates with Blob Storage, Azure Data Lake, and Power BI.

• GCP: Integrates with Google Cloud Storage, BigQuery, and Looker.

Benefits:

• Avoid cloud lock-in → choose the provider that fits your business

• Unified experience across cloud platforms

• Supports cross-region and cross-cloud data sharing

---

2. Snowflake Architecture

Overview of Snowflake Architecture

Snowflake is built on a unique cloud-native architecture that separates storage, compute, and services. This allows it to handle large-scale data workloads efficiently, supporting concurrent queries, real-time analytics, and both structured and semi-structured data.

Unlike traditional data warehouses, Snowflake’s architecture is designed for elasticity, scalability, and high performance.

Three-Layer Architecture

Snowflake’s architecture is divided into three main layers:

1. Database Storage Layer

• All your data is stored in a centralized, cloud-based storage.

• Data is automatically compressed, encrypted, and stored in columnar format.

• Snowflake handles micro-partitioning, which splits large tables into small, manageable units.

• Key feature: Storage is separate from compute, so you can scale storage independently.

2. Query Processing / Virtual Warehouses

• Query processing happens in virtual warehouses (compute clusters).

• Each warehouse can scale up or down in size (X-Small, Small, Medium, Large…).

• Warehouses process queries independently, so multiple workloads can run concurrently without interfering.

• Supports auto-suspend and auto-resume to save costs.

Example:

• Marketing team runs a large analytics query on a Medium warehouse.

• Sales team runs a small ETL job on a separate Small warehouse.

• Both queries run simultaneously without slowing each other down.

3. Cloud Services Layer

• Handles all metadata, query optimization, security, and infrastructure management.

• Responsible for:

  • Authentication & access control

  • Query parsing and optimization

  • Transaction management

  • Infrastructure management (scheduling, scaling, failover)

• Provides a fully-managed experience → no need for DBAs to tune hardware.

Separation of Compute and Storage

• Storage and compute are completely independent in Snowflake.

• You can store terabytes or petabytes of data while running multiple small compute clusters for different workloads.

• Benefits:

  • Scale storage without touching compute

  • Scale compute without touching storage

  • Run multiple queries in parallel without contention

Benefits of Snowflake Architecture

---

3. Snowflake Editions & Pricing

Overview of Snowflake Editions

Snowflake offers multiple editions to suit different business requirements, each with varying features and capabilities:

💡 Tips: Most teams start with the Standard or Enterprise edition, then upgrade based on security or compliance needs.

---

On-Demand vs Pre-Purchased Credits

Snowflake uses a credit-based pricing model for compute:

• On-Demand Credits

  • Pay for compute usage per second.

  • Ideal for teams with variable workloads.

  • No upfront commitment; you only pay for what you use.

• Pre-Purchased Credits

  • Buy a set amount of credits upfront at a discounted rate.

  • Ideal for predictable workloads with consistent usage.

  • Helps with budgeting and cost control.

Storage Cost:

• Billed separately from compute.

• Charged based on amount of data stored and duration of storage.

---

Auto-Suspend and Auto-Resume

Snowflake allows you to automatically manage virtual warehouse usage:

• Auto-Suspend:

  • Pauses the virtual warehouse after a period of inactivity (default 10 minutes).

  • Reduces compute costs while no queries are running.

• Auto-Resume:

  • Automatically resumes the warehouse when a query is submitted.

  • Ensures workloads start immediately without manual intervention.

Example:

-- Create a warehouse with auto-suspend after 5 minutes
CREATE WAREHOUSE my_warehouse
  WITH WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

🔹 This ensures you only pay for compute when actively running queries, helping save costs significantly.

---

4. Snowflake Objects

Snowflake provides various objects to organize, store, and process your data efficiently. These are the building blocks of any Snowflake data pipeline.

1. Databases

• A database is the highest-level container for storing data in Snowflake.

• It can contain schemas, tables, views, and other objects.

• Example:

CREATE DATABASE SalesDB;
USE DATABASE SalesDB;

2. Schemas

• A schema is a logical grouping of database objects like tables, views, stages, etc.

• Helps organize objects for different teams or projects.

CREATE SCHEMA Marketing;
USE SCHEMA Marketing;

3. Tables

• Tables store structured data in rows and columns.

• Types of tables in Snowflake:

Permanent Tables

• Default table type.

• Data is persistent, durable, and supports Time Travel and Fail-safe.

CREATE TABLE Customers (
    ID INT,
    Name STRING,
    Age INT
);

Temporary Tables

• Exists only for the session in which it was created.

• Automatically dropped at session end.

CREATE TEMPORARY TABLE TempOrders (
    OrderID INT,
    Amount FLOAT
);

Transient Tables

• Persistent but no Fail-safe.

• Lower storage costs for intermediate or staging data.

CREATE TRANSIENT TABLE StagingOrders (
    OrderID INT,
    Amount FLOAT
);

4. External Tables

• Reference data stored outside Snowflake, e.g., S3, Azure Blob, or GCS.

• Query external data without loading it into Snowflake.

CREATE EXTERNAL TABLE ExtOrders (
    OrderID INT,
    Amount FLOAT
)
WITH LOCATION=@my_s3_stage
FILE_FORMAT = (TYPE = CSV);

5. Views

• Virtual tables defined by a SQL query.

• Do not store data physically, only the query definition.

CREATE VIEW ActiveCustomers AS
SELECT * FROM Customers WHERE Status='Active';

6. Stages

• Stages are storage locations for loading/unloading data.

• Internal Stages: Managed by Snowflake.

• External Stages: S3, Azure Blob, or GCS.

-- Internal stage
CREATE STAGE my_stage;

-- External stage (AWS S3)
CREATE STAGE s3_stage
URL='s3://mybucket/data'
CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');

7. File Formats

• Define format of files for loading/unloading data.

• Example formats: CSV, JSON, PARQUET, AVRO.

CREATE FILE FORMAT my_csv_format
TYPE = CSV
FIELD_OPTIONALLY_ENCLOSED_BY='"'
SKIP_HEADER = 1;

8. Sequences

• Generate unique numeric values, commonly used for primary keys.

CREATE SEQUENCE OrderSeq START = 1000 INCREMENT = 1;
INSERT INTO Orders(ID, Amount) VALUES (NEXTVAL(OrderSeq), 250);

9. Streams

• Track data changes (insert/update/delete) on a table.

• Useful for Change Data Capture (CDC).

• For detailed Study Refer - https://abhi1213.hashnode.dev/streams-in-snowflake

CREATE STREAM CustomerStream ON TABLE Customers;

10. Tasks

• Schedule SQL statements or ETL pipelines.

• Can run periodically or on a schedule.

• Detailed Study - TBDL

CREATE TASK DailyTask
WAREHOUSE = my_warehouse
SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
INSERT INTO DailySummary SELECT * FROM Orders WHERE OrderDate=CURRENT_DATE;

11. Materialized Views

• Store precomputed results for faster query performance.

• Automatically refreshes when underlying tables change.

CREATE MATERIALIZED VIEW MV_Sales AS
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID;

---

5. Snowflake Data Types

In Snowflake, choosing the right data type is crucial for performance, storage efficiency, and query accuracy. Snowflake supports structured and semi-structured data types.

1. Numeric Data Types

Used to store numbers, including integers and decimals.

Example:

CREATE TABLE Sales (
    ID INT,
    Amount NUMBER(10,2),
    Discount FLOAT
);

2. String Data Types

Used to store textual data.

Example:

CREATE TABLE Customers (
    ID INT,
    Name VARCHAR(100),
    Status CHAR(1)
);

3. Date & Time Data Types

Used to store dates and times.

Example:

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    OrderTime TIME,
    CreatedAt TIMESTAMP_NTZ
);

4. Boolean

• Stores TRUE or FALSE values.

CREATE TABLE Flags (
    ID INT,
    IsActive BOOLEAN
);

5. Semi-Structured Data Types

Snowflake can store JSON, Avro, Parquet, ORC, XML using VARIANT, OBJECT, and ARRAY types.

Example:

CREATE TABLE EventLogs (
    ID INT,
    EventData VARIANT
);

-- Insert JSON data
INSERT INTO EventLogs VALUES (1, PARSE_JSON('{"event":"login","user":"Alice"}'));

-- Query JSON data
SELECT EventData:event, EventData:user FROM EventLogs;

---

6. Snowflake SQL Basics

SQL is the primary language for interacting with Snowflake. As a data engineer, mastering Snowflake SQL is essential for creating, querying, and managing data efficiently.

1. Connecting to Snowflake

• Snowflake provides multiple ways to connect:

  • Web Interface (Snowflake UI)

  • SnowSQL CLI

  • Python / Snowpark

  • BI tools (Tableau, Power BI)

Example using SnowSQL CLI:

snowsql -a <account_name> -u <username> -r <role>

2. Databases and Schemas

• Organize your data into databases and schemas.

-- Create a database
CREATE DATABASE SalesDB;

-- Use the database
USE DATABASE SalesDB;

-- Create a schema
CREATE SCHEMA Marketing;

-- Use the schema
USE SCHEMA Marketing;

3. Creating Tables and Inserting Data

-- Create table
CREATE TABLE Customers (
    ID INT,
    Name STRING,
    Age INT,
    Status STRING
);

-- Insert data
INSERT INTO Customers (ID, Name, Age, Status) VALUES
(1, 'Alice', 25, 'Active'),
(2, 'Bob', 30, 'Inactive'),
(3, 'Charlie', 28, 'Active');

4. Querying Tables

SELECT

-- Select all columns
SELECT * FROM Customers;

-- Select specific columns
SELECT Name, Age FROM Customers;

WHERE

-- Filter data
SELECT * FROM Customers WHERE Age > 25;

GROUP BY

-- Aggregate example
SELECT Status, COUNT(*) AS Count
FROM Customers
GROUP BY Status;

ORDER BY

-- Sort data
SELECT * FROM Customers ORDER BY Age DESC;

5. Joins and Subqueries

JOIN

CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    Amount FLOAT
);

-- Inner join
SELECT c.Name, o.Amount
FROM Customers c
JOIN Orders o
ON c.ID = o.CustomerID;

Subquery

-- Get customers with orders > 200
SELECT Name
FROM Customers
WHERE ID IN (
    SELECT CustomerID
    FROM Orders
    WHERE Amount > 200
);

6. Views

• Virtual tables that store SQL queries.

-- Create a view
CREATE VIEW ActiveCustomers AS
SELECT * FROM Customers WHERE Status='Active';

-- Query the view
SELECT * FROM ActiveCustomers;

7. Using Semi-Structured Data

CREATE TABLE EventLogs (
    ID INT,
    EventData VARIANT
);

INSERT INTO EventLogs VALUES 
(1, PARSE_JSON('{"event":"login","user":"Alice"}'));

-- Query JSON fields
SELECT EventData:event AS Event, EventData:user AS User
FROM EventLogs;

---

7. Semi-Structured Data in Snowflake

Snowflake allows data engineers to store and query semi-structured data (JSON, AVRO, Parquet, ORC, XML) alongside structured data without transforming it beforehand. This is one of Snowflake’s most powerful features for modern data pipelines.

1. Handling JSON, AVRO, Parquet, ORC, XML

Snowflake can ingest and query data in multiple semi-structured formats:

Example: Loading a JSON file into Snowflake: For more detailed Study refer - https://hashnode.com/post/cme5z2ayz000k02le5sq14i4t

-- Create stage for JSON files
CREATE STAGE my_json_stage URL='s3://mybucket/json/' 
CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');

-- Load JSON into a table with VARIANT column
CREATE TABLE EventLogs (
    ID INT,
    EventData VARIANT
);

COPY INTO EventLogs
FROM @my_json_stage
FILE_FORMAT = (TYPE = JSON);

2. VARIANT Data Type

• VARIANT is a special Snowflake type that can store any semi-structured data.

• Can store JSON objects, arrays, nested structures, or mixed types.

• Enables querying semi-structured data using dot notation.

Example:

INSERT INTO EventLogs VALUES 
(1, PARSE_JSON('{"event":"login","user":"Alice","details":{"ip":"192.168.1.1"}}'));

SELECT EventData:event AS Event,
       EventData:user AS User
FROM EventLogs;

Output:

3. Flattening Semi-Structured Data (TBDL)

• Semi-structured data often contains nested arrays or objects.

• Use the FLATTEN function to expand arrays or nested objects into multiple rows.

Example: JSON array flattening

INSERT INTO EventLogs VALUES
(2, PARSE_JSON('{"event":"purchase","items":[{"id":1,"price":100},{"id":2,"price":200}]}'));

SELECT EventData:event AS Event,
       f.value:id AS ItemID,
       f.value:price AS Price
FROM EventLogs,
     LATERAL FLATTEN(input => EventData:items) f
WHERE EventData:event='purchase';

Output:

4. Using LATERAL FLATTEN to Query Nested Structures (TBDL)

• LATERAL FLATTEN works like a table function.

• It explodes arrays or nested objects into rows, making them easier to query with SQL.

Key Points:

• input → the VARIANT column or nested array

• Returns columns like:

  • value → element value

  • index → array index

  • path → path within JSON object

• Can be combined with JOINs, WHERE clauses, and aggregations

---

8. Snowflake Virtual Warehouses (Compute)

In Snowflake, a virtual warehouse is the compute engine responsible for query processing, data loading, and transformation. Understanding how virtual warehouses work is essential for efficient resource management and cost optimization.

1. What is a Virtual Warehouse?

• A virtual warehouse is a cluster of compute resources in Snowflake.

• It handles SQL queries, data transformations, and loading/unloading operations.

• Key characteristics:

  • Independent of storage (compute and storage are separate)

  • Can scale up or down based on workload

  • Multiple warehouses can run concurrently without blocking each other

Example: You might have:

• A warehouse for ETL pipelines

• Another warehouse for BI reports

• Both can run simultaneously without conflict

2. Size Options

Snowflake provides multiple warehouse sizes to handle different workloads:

Tip: Start small and scale up if needed. Snowflake allows instant resizing without downtime.

3. Scaling: Auto-Scale & Multi-Cluster Warehouses

Auto-Scale

• Automatically adds or removes compute nodes based on current query load.

• Prevents slow queries when concurrency increases.

-- Enable multi-cluster auto-scaling
ALTER WAREHOUSE my_warehouse
SET WAREHOUSE_SIZE = 'MEDIUM'
SET MIN_CLUSTER_COUNT = 1
SET MAX_CLUSTER_COUNT = 3
SET AUTO_SUSPEND = 300
SET AUTO_RESUME = TRUE;

Multi-Cluster Warehouses

• Multiple clusters can handle high concurrent workloads.

• Snowflake automatically distributes queries among clusters to avoid queuing.

4. Auto-Suspend & Auto-Resume

• Auto-Suspend: Pauses the warehouse after a defined period of inactivity (e.g., 5 minutes) to save costs.

• Auto-Resume: Automatically resumes the warehouse when a query is submitted, ensuring seamless execution.

Example:

CREATE WAREHOUSE my_warehouse
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 300       -- 5 minutes
  AUTO_RESUME = TRUE;

5. Concurrency Handling

• Multiple users or queries can run simultaneously without slowing each other down by using separate warehouses or multi-cluster warehouses.

• If one warehouse is busy, Snowflake can route queries to other clusters in a multi-cluster warehouse.

• Ensures consistent performance even under heavy workloads.

---

9. Snowflake Storage

Snowflake’s storage layer is designed for high performance, scalability, and data protection. Understanding how Snowflake stores data helps data engineers optimize queries, manage historical data, and ensure data reliability.

1. Micro-Partitions

• Snowflake automatically splits tables into micro-partitions, which are small, contiguous units of storage (16MB–512MB uncompressed).

• Each micro-partition stores columnar data, metadata (min/max values, distinct values), and statistics for query optimization.

• Benefits:

  • Efficient query pruning → only relevant micro-partitions are scanned

  • Improved compression → reduced storage costs

  • Faster analytics on large tables

Example:
If a table has 100 million rows, Snowflake might divide it into thousands of micro-partitions automatically.

2. Columnar Storage

• Snowflake stores data in columnar format, unlike traditional row-based storage.

• Advantages:

  • Only the columns used in a query are read → faster queries

  • Better compression for repetitive data

  • Optimized for analytics and aggregation operations

Example:

SELECT SUM(Salary) FROM Employees WHERE Department='HR';

• Only the Salary and Department columns are scanned, not the entire row.

3. Time Travel in Snowflake

Time Travel allows you to access historical data or previous states of tables, schemas, or databases. Snowflake automatically retains data changes for a defined period, which is extremely useful for undoing mistakes, auditing, or comparing data over time.

Key Points

• Default retention: 1 day (24 hours) for Standard edition

• Enterprise edition: up to 90 days

• Applies to: tables, schemas, databases, and transient tables

• Works for: SELECT, CLONE, UNDROP, RESTORE operations

3.1 Query Historical Data

You can query historical data using:

• AT clause (specific timestamp)

• BEFORE clause (specific statement)

Example 1: Query table 1 hour ago

SELECT * 
FROM Employees
AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '1 HOUR');

Example 2: Query table before last update

SELECT * 
FROM Employees
BEFORE (STATEMENT => 'last_update_statement_id');

3.2 Restore Deleted or Updated Data

• Undrop a table if accidentally dropped:

UNDROP TABLE Employees;

• Restore a table to a previous state:

CREATE TABLE Employees_Restore AS
SELECT * 
FROM Employees
AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '1 DAY');

• Restore individual rows using Time Travel:

INSERT INTO Employees
SELECT * 
FROM Employees
AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '1 HOUR')
WHERE ID=101;

3.3 Cloning for Time Travel

• Zero-copy cloning allows you to create a snapshot of a table, schema, or database at a previous point in time:

CREATE TABLE Employees_Clone CLONE Employees AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '1 DAY');

• No additional storage is used initially because Snowflake only stores changes made after the clone.

3.4 Best Practices for Time Travel

• Use Time Travel for:

  • Undoing accidental deletes or updates

  • Auditing changes

  • Creating historical snapshots for reporting

• Avoid storing large amounts of transient data beyond retention period

• Combine with staging tables for ETL processes

4. Fail-Safe in Snowflake

Fail-Safe is Snowflake’s last line of defense for data recovery. Unlike Time Travel, it is managed entirely by Snowflake, cannot be accessed by users directly, and ensures critical recovery after Time Travel retention ends.

Key Points

• Duration: 7 days after Time Travel retention ends

• Only accessible by Snowflake Support

• Intended for emergency recovery, e.g., accidental deletions beyond Time Travel

• Applies to permanent tables only, not transient tables

Flow:

1. Data changes occur → Time Travel retention active

2. Time Travel expires → data enters Fail-Safe

3. Snowflake support can restore data for 7 days

4. After 7 days → data permanently deleted

4.1 Visual Overview

Data Change --> Time Travel (1-90 days) --> Fail-Safe (7 days) --> Permanent deletion

• Time Travel: User-accessible recovery

• Fail-Safe: Admin-level, emergency recovery only

4.2 Use Cases

• Recover from user errors within Time Travel period

• Recover from catastrophic events beyond Time Travel (Fail-Safe)

• Audit historical data for compliance

• Zero-copy cloning for testing without affecting original data

4.3 Best Practices

• Prefer Time Travel for day-to-day recovery

• Use Fail-Safe only for critical emergencies

• Combine with clones to minimize risk when experimenting with large tables

• Monitor retention settings according to edition (1 day default, 7–90 days for higher editions)

---

10. Loading Data into Snowflake

• Using Snowflake Web UI / SnowSQL / Python

• Loading from local files

• Loading from cloud storage: S3, Azure Blob, GCS

• COPY INTO command

• Staging: internal vs external stages

  Refer : - https://abhi1213.hashnode.dev/ways-of-loading-data-in-snowflake

---

11. Snowflake Security

Snowflake’s security architecture is built on a zero-trust model — everything is secured by default.
It offers end-to-end encryption, role-based access control (RBAC), data masking, and multi-factor authentication, making it one of the safest data platforms in the cloud.

1. Roles and Privileges

Snowflake uses Role-Based Access Control (RBAC) to manage user permissions.
Roles determine what operations a user can perform and on which objects (databases, schemas, tables, etc.).

Key System-Defined Roles:

Example:

-- Create a custom role
CREATE ROLE data_engineer;

-- Grant access to a schema
GRANT USAGE ON DATABASE analytics TO ROLE data_engineer;
GRANT USAGE ON SCHEMA analytics.sales TO ROLE data_engineer;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.sales TO ROLE data_engineer;

Assign role to a user:

GRANT ROLE data_engineer TO USER abhishek;

2. Users and Groups

Each user in Snowflake has:

• A login name

• Default role

• Default warehouse

• Password or key-based authentication

Example:

CREATE USER abhishek
  PASSWORD = 'Strong#Pass123'
  DEFAULT_ROLE = data_engineer
  DEFAULT_WAREHOUSE = compute_wh
  MUST_CHANGE_PASSWORD = TRUE;

Groups are implemented through roles, not a separate entity.
You can assign multiple users to the same role to manage access collectively.

3. Role Hierarchy

Snowflake roles form a hierarchical tree structure where roles can inherit privileges from others.

Example:

ACCOUNTADMIN
   └── SECURITYADMIN
        └── SYSADMIN
             └── data_engineer

Granting one role to another:

GRANT ROLE data_engineer TO ROLE SYSADMIN;

This means anyone with the SYSADMIN role also inherits all privileges of data_engineer.

4. Secure Views

Secure Views prevent unauthorized users from viewing underlying data directly.
They are similar to normal views but data access is strictly controlled and query results are never cached in plain text.

Example:

CREATE SECURE VIEW employee_public_view AS
SELECT Name, Department
FROM employee
WHERE IsSensitive = FALSE;

Benefits:

• Protects sensitive columns (like salaries or SSNs)

• Prevents cached data exposure

• Used widely in multi-tenant environments

5. Masking Policies

→ https://abhi1213.hashnode.dev/snowflake-data-masking-and-column-level-security-a-complete-guide-with-examples

Dynamic Data Masking allows sensitive data (like PII) to be hidden or masked dynamically based on user roles.

Step 1: Create a masking policy

CREATE MASKING POLICY mask_ssn AS (val STRING) 
RETURNS STRING ->
CASE
  WHEN CURRENT_ROLE() IN ('HR_ROLE', 'ACCOUNTADMIN') THEN val
  ELSE 'XXX-XX-XXXX'
END;

Step 2: Apply it to a column

ALTER TABLE employees 
MODIFY COLUMN ssn 
SET MASKING POLICY mask_ssn;

Result:

• HR_ROLE users see the full SSN.

• Others see masked values (XXX-XX-XXXX).

6. Multi-Factor Authentication (MFA)

Snowflake supports MFA for enhanced login security.
Users must provide:

• Password

• Second factor (e.g., code from an authenticator app)

Setup via:

• Snowflake Web UI → Preferences → Enable MFA

• Supported through Okta, Duo, or Google Authenticator

Benefits:

• Prevents unauthorized account access

• Mandatory for administrative roles in most enterprises

7. Encryption (Always-On)

Snowflake provides end-to-end encryption by default — no manual setup required.

Always-on encryption ensures that no unencrypted data ever touches Snowflake’s infrastructure.

8. Summary

---

12. Streams and Tasks

    Refer :- https://abhi1213.hashnode.dev/streams-in-snowflake

    1. What is a Stream in Snowflake?

    A Stream in Snowflake is a change-tracking object that records insert, update, and delete changes made to a table.
    It doesn’t store the full data — only delta changes since the last time the stream was consumed.

    Think of it as a CDC (Change Data Capture) mechanism built into Snowflake.

    Key Features of Streams

    • Tracks DML changes (INSERT, UPDATE, DELETE) on a base table.

    • Maintains an offset — the point up to which changes have been read.

    • Automatically resets once consumed in a transaction.

    • Useful for incremental data loading, auditing, and data replication.

2. What is a Task in Snowflake?

A Task in Snowflake lets you schedule SQL statements or pipelines — like a built-in job scheduler.
You can automate repetitive processes such as:

• Loading data periodically

• Refreshing materialized views

• Consuming streams for ETL

Key Features of Tasks

• Executes SQL or stored procedures automatically.

• Can be time-based or dependency-based (chained to other tasks).

• Runs inside Snowflake’s serverless compute layer — no need to manage clusters.

• Supports auto-resume and auto-suspend for efficiency.

---

13. Snowflake Cloning: A Beginner-Friendly Guide for Data Engineers

    Snowflake’s zero-copy cloning is a powerful feature that allows you to create instant, independent copies of databases, schemas, or tables without physically duplicating the data. This is extremely useful for development, testing, backup, and experimentation.

    1. What is Cloning in Snowflake?

    Cloning creates a logical copy of an object:

    • Can clone databases, schemas, or tables.

    • Zero-copy: Initially, the clone shares the same micro-partitions as the source — no extra storage is used.

    • After cloning, both the source and clone are independent objects — changes to one do not affect the other.

Benefits:

• Instant creation, regardless of size.

• Minimal storage usage initially.

• Safe environment for development or testing without touching production.

• Works seamlessly with Time Travel.

2. How to Create a Clone

(a) Clone a Table

    CREATE TABLE orders_clone CLONE orders;

(b) Clone a Schema

    CREATE SCHEMA sales_clone CLONE sales;

(c) Clone a Database

    CREATE DATABASE analytics_clone CLONE analytics;

3. Time Travel and Cloning

Cloning works with historical snapshots using Time Travel:

    -- Clone table as it existed 24 hours ago
    CREATE TABLE orders_clone_24h CLONE orders AT (OFFSET => -24*60*60);

Useful for recovering historical data or testing ETL pipelines on previous snapshots.

4. Behavior of Clones and Source Objects

Understanding the interaction between source objects and clones is crucial for data engineers.

(a) What Happens if the Source Object is Deleted?

• The clone remains accessible.

• Storage is preserved for micro-partitions that the clone references.

• Changes to the clone continue independently.

    DROP TABLE orders;  -- Delete the source table
    SELECT * FROM orders_clone;  -- Clone still works

Key Takeaway: Clones are resilient and survive deletion of the source.

(b) What Happens if the Clone is Deleted?

• Deleting the clone does not affect the source object.

• Only the storage used by the clone (after copy-on-write changes) is freed.

    DROP TABLE orders_clone;  -- Clone deleted
    SELECT * FROM orders;     -- Source table still exists

Key Takeaway: Clones are independent objects; deleting them does not impact the original.

(c) What Happens if Both Are Modified?

• Snowflake uses copy-on-write storage:

  • When you modify the clone, only the changed micro-partitions consume extra storage.

  • The source remains unchanged.

• Similarly, changes to the source after cloning do not affect the clone.

5. Storage Considerations

• Initial creation: Zero additional storage.

• After modification: Only modified partitions consume extra storage.

• Cloning large datasets is fast and cost-efficient compared to full duplication.

6. Practical Use Cases for Cloning

7. Best Practices for Data Engineers

• Always combine cloning with Time Travel to have historical snapshots.

• Use clones for ETL testing before applying changes to production.

• Monitor storage usage for clones that diverge significantly from the source.

• Combine clones with streams and tasks to build automated pipelines on safe, isolated data.

---

14. Data Sharing

• Secure data sharing

• Reader accounts

• Sharing across regions and cloud providers

• Use cases for data sharing

---

15. Performance Optimization

• Clustering keys

• Caching: query cache, result cache, metadata cache

• Partition pruning

• Using multi-cluster warehouses for concurrency

---

16. Snowflake Integration

• Connecting with Python (Snowflake Connector, Snowpark)

• Using Snowflake with Spark

• Integration with ETL tools: Matillion, Fivetran, Airflow, Talend

• BI tools: Tableau, Power BI, Looker

---

17. Snowflake Best Practices for Data Engineers

• Separate storage and compute for cost optimization

• Use transient tables for intermediate processing

• Minimize data movement

• Optimize micro-partitions with clustering

• Use Streams and Tasks for incremental data loading

• Use zero-copy cloning for environment management

---

18. Snowflake Use Cases

• Data lake + Data warehouse hybrid

• Real-time analytics with streams

• ETL / ELT pipelines

• Machine Learning pipelines

• Multi-cloud and data sharing scenarios

---

19. Resources & Next Steps

• Official Snowflake documentation

• Snowflake hands-on labs

• Snowflake community and blogs

• Practice exercises for ETL and analytics