Skip to main content

📝 Snowflake

Description​

< What is it? >​

Snowflake is a managed cloud data platform used to store, process, and analyze data. A common use is a data warehouse: combine data from operational systems into tables that analysts query with SQL.

Snowflake separates persistent storage from compute resources. Users work with databases, schemas, tables, and compute services while Snowflake manages the underlying infrastructure. See its architecture overview.

  • Example: load sales data from several applications, transform it into reporting tables, and let finance and analytics teams query those tables with separate compute resources.

Key points​

< Storage, compute, and cloud services >​

LayerRole
StoragePersists table data independently of running compute
ComputeExecutes work; a virtual warehouse supplies compute for many SQL queries and data operations
Cloud servicesCoordinates functions such as authentication, metadata management, and query optimization
SQL client → Cloud services → Virtual warehouse → Stored table data

A virtual warehouse is compute capacity. A database and its schemas organize data objects. Multiple warehouses can query the same stored data, and suspending a warehouse does not delete the tables. See the virtual warehouse guide.

< Scaling and workload isolation >​

Separate warehouses can isolate compute used by loading jobs and interactive reports. Warehouse sizing changes available compute resources; multi-cluster warehouses address concurrency by adding clusters. Auto-suspend and auto-resume help match warehouse activity to demand. These settings affect resource use and query behavior; larger compute is not automatically the best choice for every query.

< Micro-partitions and pruning >​

For standard Snowflake tables, data is organized into micro-partitions. Metadata about their contents helps the query engine skip partitions that cannot satisfy a filter. This is called pruning. Query performance depends partly on how much data must be scanned and how well filters align with the data's organization. See micro-partitions and clustering.

Comparison​

< Snowflake and Spark >​

AspectSnowflakeApache Spark
Primary roleManaged platform for storing and processing dataDistributed processing engine that connects to storage
Typical interfaceSQL and platform APIsSQL, PySpark, and other Spark APIs
OperationsService manages infrastructure; users configure access and computeDeployment can be self-managed or provided by a managed service
Common useShared analytical tables, reporting, and data transformationsProgrammable data pipelines, large transformations, and streaming

Both can perform analytical SQL and transformations. They can also be used together: a Spark pipeline may prepare data that is loaded into Snowflake for reporting. Choose based on workload, existing storage, team skills, and operational requirements.

Implementation​

< Aggregate sales with SQL >​

Run this query in a Snowflake SQL worksheet with an available warehouse selected and permission to use it. It creates an inline dataset with VALUES, so no persistent table is needed. See the VALUES reference.

WITH sales AS (
    SELECT column1 AS region, column2 AS amount
    FROM VALUES
        ('East', 120),
        ('West', 90),
        ('East', 60)
)
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

Expected result:

REGIONTOTAL
East180
West90

The aggregation matches the PySpark example. In an application, the input would typically be a stored table or another query rather than three inline rows.

Troubleshoot​

< Common problems >​

SymptomWhat to inspect
A query cannot startSelected warehouse, its state, and the active role's privileges
Queries queue during busy periodsWarehouse load and concurrency settings
A query scans much more data than expectedFilters, micro-partition pruning, and the query profile
Resource use remains high during quiet periodsAuto-suspend settings and jobs that keep compute active

Reference​