What is Snowflake and what does it do?

There are many reasons why organizations are using Snowflake. Here’s some of the key benefits and advantages:

• Fast data retrieval: Snowflake works efficiently with massive data volumes with faster data retrieval.
• Vertical scalability: Its unique architecture separates the storage and compute resources. Therefore, users can scale storage or compute independently as per workload requirements. Its vertical scaling allows an increase in the compute resources for handling large workloads.
• Horizontal scalability: Snowflake’s horizontal scaling allows adding compute clusters for distributing workloads to handle concurrent workloads.
• Auto-scaling: Snowflake supports auto-scaling that dynamically configures the compute resources. For example, adding more clusters if the workload increases and scaling down clusters if the workload decreases. This auto-scaling ensures high performance with cost optimization.
• Concurrency: Snowflake is designed to handle highly concurrent workloads without impacting performance. It ensures multiple users can access the environments with high performance and reliability.
• Advanced security: Snowflake has industry-leading security features for protecting data. These features include federated authentication and single sign-on (SSO), key-pair authentication, multi-factor authentication, network policies and rules, private endpoints, access controls, encryption, and an OAuth security interface.
• Extensive data integration support: Snowflake supports various data integration tools and technologies. Examples are Ascend, Azure Data Factory, Coalesce, Census, Datameer, Data Virtuality, Diyotta, Etleap, and etlworks.
• Business Intelligence support: Various BI tools and technologies provide native support to Snowflake. For example, Adobe Analytics, Amazon QuickSight, Astrato, Google Looker Studio, IBM Cognos Analytics, Microsoft Power BI, Oracle Analytics, and Pentaho Business Analytics.
• Flexible billing methods: It has on-demand month-to-month and pre-purchased resources. Its monthly billing is per the data storage (compressed terabytes per month) and compute resources.
• SQL queries: It uses the SQL (SnowSQL) command line client tool to execute the SQL queries and perform DLL\DML operations.
• Data sharing: Snowflake's unique feature includes data sharing among partners without duplicating it. Users only pay for data processing since no storage data exchange is involved.
• Seamless integration: Snowflake supports seamless integration with various data integration tools, allowing organizations to ingest and transform data from different sources into their Snowflake data warehouse.

Snowflake is a cloud-native data warehouse solution. Snowflake has three layers of unique architecture. These layers are database storage, query processing, and cloud services.

1. Database storage: Snowflake stores data in cloud storage, such as Amazon S3, Google Cloud Storage, and Azure Blob Storage. It stores data in optimized, compressed, and columnar formats. Data is not directly accessible by the users. Users can access it through SQL queries in Snowflake.
2. Query processing: Snowflake query execution occurs in the processing layer. It uses a concept known as a virtual warehouse or warehouse, which is a cluster of computing resources. Each warehouse comprises multiple compute nodes (MPP compute cluster) allocated by Snowflake. Each virtual warehouse is an independent compute cluster. It provides all the required resources for running queries in a Snowflake session. These queries include DML queries such as updating, loading, and unloading data.
3. Cloud services: The cloud service layer comprises various services for coordinating multiple activities in Snowflake. It includes authentication, infrastructure management, metadata management, query parsing, optimization, and access control.

Yes, Snowflake is a database warehouse solution built for cloud-native architecture. It can be considered a cloud-based relational database or SaaS data warehouse. Snowflake includes the features of the traditional database with cloud capabilities. A few characteristics are as follows:

• Snowflake stores data in compressed, optimized, and multiple micro partitions in the columnar format.
• Snowflake supports the standard SQL statements for querying data. These statements include SELECT, TOP, FROM, JOIN, PIVOT, UNPIVOT, GROUP BY, HAVING, ORDER BY, LIMIT, FETCH.
• Like a traditional database, it includes security features such as encryption, access controls, and multiple authentication mechanisms.

It provides mechanisms to quickly scale up or down your Snowflake resources to meet workload-changing needs.

Snowflake is a cloud database warehouse. However, its features and functionalities extended its capabilities beyond a traditional data warehouse solution. A few useful features are as follows:

• Management and Development Capabilities
• It has a collection of tools for working with the data warehouse solution.
• SnowSight is for account management, resource monitoring and system usage, and querying data.
• SnowSQL: It is a Python-based command line client tool.
• Snowflake extension for Visual Studio code
• Extensibility and Integration Capabilities
  • Snowflake's integrations with various data science, machine learning, and business intelligence tools enable it to expand its capability without depending on a specific tool for Extract-Transform-Load (ETL) operations.
  • Snowflake handles structured and semi-structured data such as JSON, Parquet, and Avro, which makes it suitable for data lakes.
  • It has an extensive set of client connectors and drivers. For example, Python connector, Spark Connector, Node.JS driver, Go Snowflake driver, JDFC client drive, and ODBC client driver.
• Security and Compliance Capabilities
  • It supports business critical and sensitive data in compliance with the HIPAA and HITRUST CSF regulations.
  • It provides a granular object access control mechanism.
  • It provides isolation of data for loading and unloading using Amazon S3 policy controls, Azure Storage access controls, and Google Cloud storage access permissions.
  • It has built-in support for authentication methods: multi-factor authentication, federated authentication and single sign-on (SSO), Snowflake OAuth, and external OAuth.
  • Federated authentication and single sign-on (SSO).
  • It allows organizations to choose the geographical region for storing the data as required.
  • Choose the location of your data stored based on your region.
  • Snowflake enables users to recover their historical data for disaster recovery.

Snowflake does not have a direct relationship with SQL or MySQL.

• SQL is a language that works with relational database systems
• MySQL is an open-source relational database system.

At a high level, the differences between Snowflake and MySQL are as follows.