Data is the lifeline for many companies. Be it is an advertising company or decision making company, data plays an important role in each and every step. To understand and utilize all this data, data warehouses have become an essential part of modern business. There are many data warehouse appliance such as Teradata, Vertica, Greenplum, etc are available in the market. But, nowadays, organizations are more inclined towards cloud data warehouses such as Snowflake, Redshift, Azure SQL Synapse, Google cloud Spanner, etc. In this article, we will check key differences between Redshift and Snowflake – Redshift vs Snowflake.
Redshift vs Snowflake – Key Differences
Both are powerful relational DBMS database models, and both offer some really powerful options in terms of managing data.
Following are some of key differences between Redshift and Snowflake.
AWS Integration
Redshift
If you’re working with an Amazon AWS, Redshift should be your first choice. Amazon Redshift integrates with a various AWS services such as Kinesis, SageMaker, EMR, Glue, DynamoDB, Athena, Database Migration Service (DMS), Schema Conversion Tools (SCT), etc.
Snowflake
On other hand, Snowflake can easily integrate with AWS simple storage service (S3), Simple Queue Services (SQS), etc. However, it is difficult for customers to use tools like Kinesis, Glue, Athena, etc.
Architecture
Redshift
Amazon Redshift architecture is an MPP (massively parallel processing). Amazon Redshift is based on industry-standard PostgreSQL (8.x), so most existing SQL client applications will work with only minimal changes.
Snowflake
On the other hand, Snowflake’s architecture is a hybrid of traditional shared-disk database architectures and shared-nothing database architectures. Similar to shared-disk architectures, Snowflake uses a central data repository for persisted data that is accessible from all compute nodes in the data warehouse. But, similar to shared-nothing architectures, Snowflake processes queries using MPP (massively parallel processing) compute clusters where each node in the cluster stores a portion of the entire data set locally.
Snowflake does not use PostgreSQL, instead it is built from scratch for the cloud.
Connection
Redshift
Amazon Redshift communicates with client applications by using industry-standard JDBC and ODBC drivers for PostgreSQL.
You can also connect to Redshift using the psql command line interface.
Snowflake
Snowflake on the other hand, provides wide range of connectors.
Following are the connectors that you can use to connect Snowflake
- Snowflake Connector for Python
- SnowSQL – Command line interface
- Snowflake Connector for Spark
- Snowflake Connector for Kafka
- Node.js Driver
- Go Snowflake Driver
- .NET Driver
- JDBC Driver
- ODBC Driver
Connection
Both Redshift and Snowflake take security very seriously. They provide many security features to secure data and user account.
Redshift
Amazon Redshift database security is distinct from other types of databases. In addition to database security, Amazon Redshift provides following features to manage security
- Sign-in credentials – You should have an AWS account to access Redshift management console. In other words, access to Redshift console is managed by AWS account privileges.
- Access management – AWS Identity and Access Management (IAM) accounts are used to manage access.
- Cluster encryption — This security method will encrypt the data in all your user-created tables. There is an option to enable cluster encryption when you launch the Redshift cluster.
- Cluster security groups – Security groups are associated with cluster to grant other users inbound access to an Amazon Redshift cluster.
- VPC – You can create your cluster on a virtual private cloud (VPC).
- Load data encryption – Amazon Redshift supports both server-side encryption and client-side encryption.
- Data in transit – Amazon Redshift uses hardware accelerated SSL to communicate with Amazon S3 or while using any other operation such as COPY, UNLOAD, backup, and restore operations.
- MFA – You can set up multi-factor authentication to access Redshift management console.
Snowflake
Similarly, Snowflake supports industry standard security features to secure user accounts and data.
Following are key security features provided by Snowflake
- Network/site access – Site access controlled through IP whitelisting and blacklisting, managed through network policies. Snowflake also supports private communication between Snowflake and your other VPCs through the AWS PrivateLink.
- Account/user authentication – MFA (multi-factor authentication) for increased security for account access by users. Also support for user SSO (single sign-on) through federated authentication and Oauth.
- Object security – Snowflake supports controlled access to all objects in an account (users, warehouses, databases, tables, etc) through a hybrid model of DAC (discretionary access control) and RBAC (role-based access control).
- Data security – All data automatically encrypted (using AES 256 strong encryption). All files stored in stages (for data loading/unloading) automatically encrypted (using either AES 128 standard or 256 strong encryption). Periodic rekeying of encrypted data. Support for encrypting data using customer-managed keys.
- Security Validations – Soc 1 and Soc 2 Type II compliance. Support for HIPAA compliance. PCI DSS compliance.
Semi-Structured Data Support
Redshift
Redshift does not support semi-structured data such as JSON, Parquet, etc. You have to store semi-structured data on S3 and the integrate it with Redshift.
The Redshift spectrum allows you to query semi-structured data stored in Amazon S3.
Related Articles,
Snowflake
Snowflake supports Semi-Structured data types – Variant, Object, and Array, You can load data without worrying about the schema. In some use cases, this is very useful.
Data Lake
Redshift
Amazon Redshift supports query, export data to and from your data lake created on S3. You can query open file formats such as Parquet, ORC, JSON, Avro, CSV, and more directly in S3 using familiar ANSI SQL.
Related Article,
Snowflake
Snowflake allows you to store the semi-structured data into its table using a special data type called variant. You can use Snowflake to create data lake.
However, the Snowflake can easily integrate with S3. You can create external table and query semi-structure file similar to Redshift.
Maintenance
Redshift
Scale up / down – Redshift does not easily scale up and down. The cluster resize operation takes a lot of time.
Table Clustering – Redshift does not support table clustering.
Snowflake
Scale up / down – However, with Snowflake, switching compute data warehouse or resize is a matter of seconds. Since compute and storage is separate, there is no need to copy any data to scale up and down.
Table Clustering – Snowflake automatically create clusters when you define clustering column.
Hope this article helps you to provide key differences between Redshift and Snowflake. Please comment if any of the feature is missing.