Cloud Data Platforms: Introduction to Snowflake

Course Objectives and Analytical Foundations

• Learning Objectives for Week 1:     * Explain Traditional OLAP Limitations: Articulate the architectural reasons why traditional Online Analytical Processing (OLAP) databases fail to address modern data scaling needs.     * Understand Separation of Storage and Compute: Describe this fundamental concept, which is identified as the single most important concept in the unit.     * Identify Snowflake’s Layered Architecture: Outline the three distinct layers—Storage, Compute (Virtual Warehouses), and Cloud Services—and the specific functions of each.     * Distinguish Snowflake’s Role: Properly identify what Snowflake is (a cloud data platform) and what it is not (a general-purpose transactional database or a BI tool).

The Problem: OLTP vs. OLAP and Traditional Limitations

• Two Fundamentally Different Workloads:     * OLTP (Online Transaction Processing):         * Purpose: Recording individual events such as orders, logins, and payments.         * Operations: Characterized by many small reads and writes per second.         * Optimization: Focused on row-level access using a normalized schema (to avoid redundancy).         * Primary Users: Applications, rather than analysts.         * Examples: MySQL, PostgreSQL, Oracle.     * OLAP (Online Analytical Processing):         * Purpose: Answering complex business questions.         * Operations: Characterized by a few large queries that scan millions of rows.         * Optimization: Focused on columnar, aggregated reads using denormalized schemas to maximize query speed.         * Primary Users: Analysts, Data Scientists, and Business Intelligence (BI) tools.         * Examples: Redshift, BigQuery, Snowflake.

• Structural Limits of Traditional Data Warehouses (On-premises systems like Teradata, Netezza, and early Redshift):     * Coupled Storage and Compute: In traditional architectures, hardware is bought in fixed blocks. To increase storage, one must also purchase more compute, even if not needed, and vice versa. There is no way to scale them independently.     * Fragile Concurrency Scaling: When multiple users (e.g., $50$ analysts) run reports simultaneously, they compete for the same fixed resources. This leads to slowed queries or the need for complex, inefficient queuing logic.     * Slow/Expensive Provisioning: Increasing capacity requires manual hardware acquisition, configuration, and long wait times. Once purchased, costs are incurred regardless of utilization.     * Rigid Schema-on-Write: Data must have a strictly defined structure before it is loaded. This makes it difficult to incorporate semi-structured data (JSON, logs, API responses) without heavy pre-transformation.

Snowflake’s Three-Layer Architecture

• 1. Storage Layer (Data at Rest):     * Foundation: Centralized cloud object storage (AWS S3, Azure Blob, or Google Cloud Storage).     * Data Format: Data is stored in a compressed, columnar format.     * Micro-partitions: Data is divided into small files ranging from $50\,MB$ to $500\,MB$ of compressed data.     * Columnar Storage: Files store columns rather than rows, which enables "column pruning" (only reading the specific columns needed for a query).     * Automatic Clustering: Snowflake automatically tracks minimum and maximum values per column per partition. This allows queries to perform "partition pruning," skipping irrelevant files based on filter conditions.     * Management: Users do not need to build indexes or manually define partitions; Snowflake handles this automatically.

• 2. Compute Layer (Virtual Warehouses):     * Definition: A Virtual Warehouse (VW) is a cluster of compute nodes used to execute queries; it is not a storage location.     * Warehouse Sizes & Credits:         * XS: $1\,\text{node}$, costing $1\,\text{credit/hr}$ (Used for Dev/light queries).         * S: $2\,\text{nodes}$, costing $2\,\text{credits/hr}$ (Used for Analyst queries).         * M: $4\,\text{nodes}$, costing $4\,\text{credits/hr}$ (Used for Heavy reporting).         * L: $8\,\text{nodes}$, costing $8\,\text{credits/hr}$ (Used for Data engineering).         * XL–4XL: $16-128\,\text{nodes}$, costing $16-128\,\text{credits/hr}$ (Used for Large-scale ELT).     * Key Behaviors:         * Instant Start/Stop: Billing is based only on active time.         * Auto-suspend: Transitions to idle (no cost) after a period of inactivity.         * Resource Isolation: Different teams (Finance, ETL, Analytics) can have their own dedicated warehouses to avoid resource contention.         * Physical Separation: Deleting or suspending a warehouse does not affect the data in the storage layer.

• 3. Cloud Services Layer (The Brain):     * Responsibilities: Operates as the management layer that coordinates the entire system.     * Query Optimization: Rewrites SQL and determines the most efficient execution plan (join order, partition reading).     * Authentication & Security: Manages user access, Multi-Factor Authentication (MFA), and Role-Based Access Control (RBAC).     * Metadata Management: Tracks statistics on partitions and query history, powering the "Query Profile" and caching.     * Infrastructure Management: Handles regional failover, replication, and availability without user configuration.     * Usage & Billing: Tracks consumption through schemas like ACCOUNT_USAGE.

The Impact of Separation of Storage and Compute

• Scaling Flexibility: Scaling storage does not require more compute; scaling compute does not require more storage.

• Concurrency Resolution: High concurrency issues are solved by spinning up additional Virtual Warehouses rather than slowing down existing ones.

• Cost Efficiency: Users can suspend compute when idle. In traditional systems, users pay for idle capacity $24/7$.

• Immediate Resizing: Resizing a Virtual Warehouse takes seconds and requires no data movement or migration downtime.

The Cloud Data Platform Landscape

• Snowflake: Multi-cloud (AWS, Azure, GCP), credit-based compute, strong semi-structured data support.

• Google BigQuery: GCP-native, serverless model with no VW management. Users pay per TB scanned or via flat-rate slots.

• Amazon Redshift: AWS-native, uses provisioned clusters or a newer serverless mode. Deep integration with the AWS ecosystem.

• Databricks (Lakehouse): Multi-cloud, focuses on combining Data Warehousing with Data Engineering and ML using Python/Spark.

Boundary Definitions: What Snowflake is Not

• Not an OLTP Database: Do not use it for live application transactions (use PostgreSQL or MySQL).

• Not a BI/Visualization Tool: While it has the Snowsight UI, it is primarily a storage and query engine. Tableau, Power BI, and Looker sit on top of it.

• Not a Data Pipeline Tool: It is the destination for data. Tools like Fivetran, dbt, or Airbyte are required to move and transform data into Snowflake.

• Not a File System: While it handles JSON/Parquet, for raw object storage or key-value needs, one should use S3, DynamoDB, or MongoDB.

Advanced Capabilities

• Semi-structured Data: Native support for JSON, Avro, and Parquet using dot notation for nested objects without ETL.

• Time Travel: Query historical data snapshots from $0-90$ days ago to undo accidental deletions.

• Secure Data Sharing: Share live data across accounts via the Snowflake Marketplace without data movement.

• Snowflake Cortex: Built-in AI/ML functions including SENTIMENT(), SUMMARIZE(), TRANSLATE(), and COMPLETE().

• Snowpark & Notebooks: Execution of Python, Java, or Scala code inside Snowflake compute, including Jupyter-style notebooks.

The Credit Model and Pricing Logic

• Compute Credits: Charged per-second when a VW is running. Cost is variable based on warehouse size and uptime.

• Storage: Charged at a flat rate per TB per month. This is a constant cost regardless of query volume.

• Cloud Services: Typically charged at $10\%$ of daily compute credits, often covered by a free tier threshold.

• Strategic Implication: The model encourages users to query freely (due to flat storage costs) but to be disciplined with warehouse sizing and auto-suspension (due to variable compute costs).

Snowsight: The User Interface

• Workspaces: The SQL editor for writing/running queries, featuring an AI Copilot.

• Monitoring: The UI provides access to the Query History, Query Profile, and ACCOUNT_USAGE views to track consumption and performance.

• Snowflake Copilot: An AI assistant capable of generating SQL from natural language (requires human verification).

Course Roadmap

• Week 1 (Current): Snowflake Objects & Navigation (Snowsight, VWs, Databases, Schemas).

• Week 2: Visualizations in Snowsight (Charts, Dashboards, Sharing).

• Week 3: Project Work (Business Scenario, Schema Design, Final Dashboard).