Snowflake Decoded - Master the Fundamental Concepts

Snowflake's storage layer automatically creates immutable micro partitions (roughly 50–500 MB, compressed), tracks min/max and distinct counts in metadata, and uses a columnar format to optimize queries on column subsets.

Explore virtual warehouses as multi-node compute clusters that provide cpu, memory, and temporary storage to process queries, with scalable sizes, suspend/resume behavior, and per-warehouse caching for faster repeats.

Understand Snowflake pricing, including storage costs after compression, per-second virtual warehouse credits with a one-minute minimum, serverless compute charges, data transfer out costs, and cloud service usage—10% free baseline.

Discover how to connect to a snowflake instance via the classic and modern web interfaces and snow sql, plus drivers and connectors for Python, NodeJS, PHP, and Go.

Install Snow SQL on Windows and connect to your Snowflake instance. Run a sample query against the hello world database and public schema.

Learn how Snowflake loads data with the copy command for bulk loads and Snowpipe for streaming, updating micro partitions, stages, and metadata in the cloud services layer.

Explore snowflake stages, comparing external stages that reference cloud storage with internal stages that store data, and learn about named, table, and user stages plus uploading data before copy command.

Explore internal stages in Snowflake, including user, table, and named internal stages, and learn how named internal stages customize file formats, enable reuse, and control access while reducing storage costs.

Load on-premises data into Snowflake by creating a target table and a named internal stage, then upload with Snow SQL and copy into the airport table.

Explore the external stage concept: Snowflake connects to cloud storage like S3 via storage integration, with credentials, encryption keys, and CSV files under a comma delimiter; cloud provider fees apply.

Load flight data from an S3 bucket into a Snowflake table using an external stage. Create the table and external stage, then run a copy into the table from CSV.

Understand table stages and user stages in Snowflake, including automatic creation, lifetime, and access rules, plus loading limitations that prevent in-stage data transformations and file format changes.

Learn how the copy command performs transformations during loading—reordering columns, omitting columns, casting types, and truncating values—while joins, filters, and aggregations are not supported and can be performed after loading.

Explore how external tables let you query data stored in cloud object storage while Snowflake metadata remains in Snowflake; understand read-only access, cost implications, partitioning, and materialized views.

Explore unloading data from Snowflake with the copy command in reverse, exporting from tables or views to internal/external stages in csv, json, or parquet formats with compression and encryption.

Explore how Snowflake protects data with encryption at rest and in transit, strong authentication and network policies, plus time travel, failsafe, storage and drop features, including transient and temporary tables.

Explore Snowflake time travel, a data protection feature that lets you query and recover historical data from tables after human error, and clone past states for testing.

Explore how Snowflake stores data with immutable micro partitions and uses time travel to read historical data, including how updates create new partitions and deletes mark old ones.

Master Snowflake time travel syntax to query historical data with at and before clauses, the add clause (timestamp, offset, or statement ID), and recover data before accidental changes.

Demonstrate time travel with Snowflake's at and before SQL extension, using before, at, and statement id to recover data before updates and deletes, then apply a time offset.

Explore how ondrop leverages time travel in Snowflake to recover a dropped table or an entire database, demonstrated through creating, dropping, and restoring objects in a lab.

Explore how fail-safe storage adds a seven-day protection layer after time travel, recoverable only by Snowflake support, with time travel up to 90 days and zero failsafe for transient tables.

Discover how Snowflake bills time travel and fail-safe storage every 24 hours as immutable micro partitions generate backup partitions and costs vary by table type and time travel configuration.

Explore Snowflake's table types: temporary, transient, and permanent, covering time travel, failsafe, session scope, and cost implications for practical use.

Learn to create temporary and transient tables in Snowflake and apply time travel to recover data; observe that temporary tables drop at session end, while transient tables persist across sessions.

Learn how Snowflake's zero-copy cloning creates copies of tables, schemas, and databases without physically duplicating data. The clone command captures a snapshot, yielding independent clones with no extra storage.

Learn how Snowflake uses micro partitions and metadata to enable zero copy cloning, with time travel considerations and update-driven new partitions that keep sources and clones independent.

Learn how to clone a table in Snowflake to create an instant, independent copy that doesn’t physically duplicate data, and compare its speed to a standard create-table operation.

Demonstrate cloning complete schemas and entire databases in Snowflake using the clone keyword, including child objects and multiple schemas, with hands-on SQL in the Snowflake web UI.

Master the technique of cloning with time travel to recreate a table's state at a chosen point in time, enabling production-like test environments for debugging and testing.

Discover Snowflake's secure data sharing, a metadata operation that shares in place with no data movement, so updates to the source automatically reflect for consumers while preserving security.

Snowflake data sharing leverages micro partitions and metadata to enable providers to share tables with consumers without data movement or extra storage, with changes reflected instantly.

Explore Snowflake offerings for data sharing—direct sharing, Snowflake Marketplace, and data exchange. Learn how metadata-based sharing works between provider and consumer accounts, including reader accounts for non-Snowflake partners.

Learn how to securely share data in Snowflake by creating a share object, granting usage and select access to schemas, databases, and tables, enabling consumer read-only databases to query data.

Share a table between two Snowflake accounts (provider and consumer) by creating a share, granting usage and select, and let the consumer create a read-only database from the share.

Share a Snowflake table between provider and consumer accounts using the Snowflake web UI. Create a direct share as account admin and set up a read-only database for the consumer.

Learn to share a table with a non snowflake customer by creating a reader account, configuring private sharing, and managing access from provider to consumer.

Explore how Snowflake Marketplace enables many-to-many data sharing of third-party datasets, with free and paid listings, ready-to-query access, and personalized buy requests.

Explore the Snowflake marketplace hands-on by using search, filters, and category options to find datasets. Import a ready-to-query, free travel dataset into your Snowflake account with account admin privileges.

Create and manage a Snowflake data exchange, a private hub for invite-only data sharing where owners invite members, publish listings, and control access for providers and consumers.

Explore Snowflake's automatic performance features, including metadata, cache, query result caching, and partition pruning, along with manual options like virtual warehouse scaling and clustering keys to boost query performance.

Understand how Snowflake executes queries, from cloud services layer query compilation and optimization to virtual warehouse processing, including micro partition pruning, in-memory processing, caching, and result caching.

Identify and interpret Snowflake query profiles to measure and improve query performance. Access profiles via query id, history, or user interface to view the graphical processing plan and component statistics.

Snowflake speeds queries by using metadata and query result caches, returning results when unchanged, and otherwise processes via a virtual warehouse with its own local micro partition cache.

Snowflake's query result cache persists results to avoid re-executing when data hasn't changed, boosting performance and reducing costs, with 24-hour validity extendable to 31 days and default enabled.

Demonstrate query result cache in Snowflake by running a query, observing cache reuse on a second run, and disabling the cache with alter session set use cached result false.

Explore how Snowflake tracks micro partitions by recording min and max values and distinct counts in the metadata cache, enabling selective partition reads and faster queries.

Explore how the Snowflake metadata cache speeds count star and min/max queries via metadata for date columns, while min/max on character columns requires a full table scan.

Cache data locally in a virtual warehouse to speed up queries. Reuse cached data rather than cloud reads, and weigh suspending the warehouse for cost benefits.

Demonstrate building and utilizing a virtual warehouse cache across multiple queries, then show how suspending the warehouse purges the cache, affecting subsequent query profiles.

Learn how Snowflake uses micro partitions and partition pruning to boost query performance, and how clustering keys and automatic clustering redistribute data for optimal access.

We refresh and expand the course to reflect Snowflake's evolution, delivering clearer explanations, better visuals, updated demonstrations, and new real-world examples with each new material release.

Learn how to scale up or scale down virtual warehouses to match the query workload, including multi-cluster scaling for concurrency, with practical syntax to adjust warehouse size.

Explore how multi-cluster virtual warehouses scale out to handle concurrent queries by dynamically spawning and decommissioning additional warehouses, which requires Snowflake enterprise edition.

Materialized views precompute and store query results to accelerate complex, frequently run queries, with Snowflake's background refresh keeping data in sync and routing to the base table when needed.

Enable search optimization to speed point lookups and predicate-rich queries using a persistent search access path, managed in the background by Snowflake, requiring enterprise edition or above.

Explore Snowflake's multi-layer security architecture, from data at rest encryption and key rotation to MFA, RBAC, dynamic data masking, OAuth2, and private connectivity, with cloud-provider security layered on.

Snowflake encrypts data with 256-bit encryption, manages keys by default, and rotates them every 30 days; try secret secure combines a snowflake and customer key into a composite master key.

Snowflake enables multi-factor authentication by default through the duo security service. Users log in with a password and complete a second factor via duo mobile app, passcodes, SMS, or calls.

Explore Snowflake authentication options, including key pair login with public/private keys and SAML 2.0 federated IdPs, plus password standards enforcing at least eight characters with a digit, uppercase and lowercase.

Explore Snowflake authorization through RBAC and DAC, with role-based privileges, owner rights, and features like column-level security, OAuth 2.0, and secure views at the view and function level.

Explore Snowflake’s authorization beyond RBAC, including column and row level security with masking policies, dynamic data masking, external tokenization, and secure views and UDFs.

Explore Snowflake built-in roles and the role hierarchy, including sysadmin, user admin, security admin, and account admin, plus the public role and custom role guidance.

Master network layer security in Snowflake by applying TLS 1.2 encryption, configuring network policies for IP whitelisting and blacklisting, and enabling private connectivity via AWS, Azure, and Google private links.

Extend Snowflake with user-defined functions, stored procedures, Snowflake scripting, and Snowpark. Explore scalar and table functions, external functions, and languages like SQL, Java, JavaScript, and Python.

Secure UDFs protect data by preventing access to underlying tables and code, weigh security against performance, and decide when views should be secure.

Explore how Snowflake stored procedures enable procedural sql with if-else logic, looping, and dynamic sql, and control execution under caller's or owner's rights for recurring administrative tasks.

Explore Snowpark, a library that lets you write data-processing code in Java, Scala, or Python, translated to sql and executed in Snowflake with lazy and parallel execution.

Discover how Snowflake scripting extends SQL with procedural logic, including variables, if and else, case, loops, blocks, and exception handling for stored procedures and procedural code.