When working with cloud data warehouses, two of the most popular platforms are Snowflake and Amazon Redshift. While both are designed for storing and querying large volumes of data efficiently, the terminology used in each platform can differ significantly. Understanding these differences is crucial for data engineers, analysts, and anyone transitioning between the platforms.
In this post, we’ll go through a detailed comparison of Snowflake and Redshift terminology, helping you bridge the knowledge gap and streamline your workflows.
1. Data Warehousing Architecture
Snowflake: Virtual Warehouses
In Snowflake, compute resources are referred to as virtual warehouses. Each virtual warehouse is an independent cluster that provides compute power for queries. Warehouses can be scaled up or down independently and multiple warehouses can operate on the same data simultaneously without contention.
Redshift: Clusters and Nodes
In Redshift, compute is provided by clusters made up of multiple nodes. A cluster is a collection of computing resources, and each node has its own CPU, memory, and storage. Scaling requires adding or removing nodes from the cluster.
Key Difference:
- Snowflake separates compute and storage, allowing elastic scaling of virtual warehouses.
- Redshift combines compute and storage within a cluster, so scaling may require resizing the cluster.
Snowflake Top rated tutorial
@Udemy
2. Storage Terminology
Snowflake: Databases, Schemas, and Tables
Snowflake organizes data into databases, schemas, and tables. The hierarchy is:
- Database – top-level container.
- Schema – logical container within a database.
- Table – structured data storage, can be permanent, temporary, or transient.
Redshift: Databases, Schemas, and Tables
Redshift also uses databases, schemas, and tables, similar to traditional PostgreSQL:
- Database – a top-level container within a Redshift cluster.
- Schema – logical container in a database.
- Table – stores structured data, can be permanent or temporary.
Key Difference: Snowflake’s transient and temporary tables give more flexibility for temporary storage and cost management, whereas Redshift mainly relies on permanent and temporary tables.
3. Compute and Query Processing
Snowflake: Virtual Warehouses and Auto-Suspend
- Virtual Warehouse: Dedicated compute cluster for running queries.
- Auto-Suspend/Auto-Resume: Warehouses can automatically suspend when idle and resume when a query arrives, saving costs.
- Concurrency: Multiple warehouses can query the same data without locking issues.
Redshift: Query Queues and WLM
- Cluster Nodes: Handle query execution.
- Workload Management (WLM): Allows defining queues for queries to manage concurrency and priority.
- Concurrency Limits: Redshift has a fixed concurrency limit per cluster; excessive concurrent queries can queue.
Key Difference: Snowflake handles concurrency with multiple independent warehouses, Redshift relies on WLM and cluster resources.
4. Data Loading and Unloading
Snowflake
- Stages: Locations for temporarily storing data before loading into tables.
- Internal Stages: Managed by Snowflake.
- External Stages: Linked to S3, Azure Blob, or Google Cloud Storage.
- COPY INTO: Command to load data from stages into tables.
- Snowpipe: Continuous data ingestion service.
Redshift
- S3 Buckets: Primary staging area for bulk loading.
- COPY Command: Loads data from S3, DynamoDB, or other sources into Redshift tables.
- Redshift Spectrum: Allows querying external tables in S3 directly.
Key Difference: Snowflake uses stages and Snowpipe for more flexible and continuous ingestion. Redshift uses COPY from S3 and optionally Spectrum for external querying.
5. Indexing and Performance Optimization
Snowflake: Automatic Clustering
- Snowflake does not require manual indexes.
- Clustering Keys: Optional, help optimize large tables by clustering data on certain columns.
- Micro-partitions: Automatic columnar storage segmentation for faster query pruning.
Redshift: Distribution and Sort Keys
- Distribution Key: Determines how rows are distributed across nodes.
- Sort Key: Orders data on disk for faster query performance.
- Vacuum: Reclaims space and sorts data after heavy updates/deletes.
Key Difference: Snowflake handles most optimization automatically, while Redshift requires manual tuning with dist/sort keys and vacuuming.
6. Time Travel and Backup
Snowflake
- Time Travel: Query historical data up to a defined retention period (1–90 days).
- Fail-safe: Additional 7-day period for disaster recovery.
Redshift
- Snapshots: Manual or automated backups of cluster state.
- Point-in-time recovery: Restores from snapshots.
Key Difference: Snowflake offers built-in Time Travel and Fail-safe for querying past versions, Redshift relies on snapshots.
7. Security Terminology
Snowflake
- Roles: Assign permissions to users.
- Grants: Assign object-level access.
- Integration: Supports external OAuth, SSO, key management.
Redshift
- User/Group Roles: Manage access.
- IAM Roles: For S3 and AWS service access.
- Parameter Groups: Configure cluster-level security settings.
Key Difference: Snowflake provides granular role-based access management with easier integration, Redshift uses a combination of IAM roles and PostgreSQL-like user management.
8. Semi-Structured Data Support
Snowflake
- Supports VARIANT, OBJECT, ARRAY types.
- Allows querying JSON, XML, Avro, Parquet directly.
- Functions like
FLATTENhelp in nested data querying.
Redshift
- Supports SUPER data type (Amazon Redshift Spectrum) for semi-structured data.
- Requires
JSON_PARSEand other functions to handle nested structures.
Key Difference: Snowflake’s VARIANT type and native semi-structured querying is more mature and flexible compared to Redshift’s SUPER type.
Conclusion
While Snowflake and Redshift share some common data warehousing concepts (databases, schemas, tables), the terminology and implementation details differ significantly. Snowflake emphasizes elastic compute, automatic optimization, and flexible ingestion, whereas Redshift is cluster-based with manual optimization and tuning.
Understanding these differences is essential for data engineers and analysts moving between platforms, ensuring efficient query performance, cost management, and smooth migration strategies.
Srinimf 