Snowflake Key Concepts

 



Snowflake is a cloud-based data warehouse that offers scalable and secure storage and processing of structured and semi-structured data. Snowflake uses a shared-nothing architecture for parallel processing and offers native support for SQL as well as other programming languages such as Python, R, and Java

 

Snowflake’s unique architecture consists of three key layers:

  • -          Database Storage
  • -          Query Processing
  • -          Cloud Services

In Snowflake, they have done the decoupling of storage and compute.

 

Virtual Warehouse:

  • -          A compute resource in Snowflake that processes queries and performs data loading and unloading.
  • -          It can be independently scaled up or down based on demand.
  • -          You can resume and suspend very easily.

 

Micro-Partition:

  • -          A storage unit in Snowflake that contains a subset of the data in a table.
  • -          Micro-partitions are automatically optimized for efficient querying.
  • -          Within each micro-partition, data is stored in a columnar data structure, allowing better compression and efficient access only to those columns required by a query.

           

Time Travel:

  • -          A feature in Snowflake that allows users to query historical data at specific points in time or within a specific time range.

           

Data Sharing:

  • -          Snowflake’s Secure Data Sharing feature allows you to share objects (such as tables) from a database in your account with another Snowflake account without having to duplicate the data and without the need to copy or transfer the data.

 

Restoring:

  • -          It provides you the facility to restore with simple SQL Commands like UNDROP TABLE.

 

Multi-cluster:

  • -          Concurrency is no longer a problem for Snowflake, unlike traditional data warehouses with concurrency issues where users and processes must compete for resources. Because of Snowflake’s multi-cluster architecture, concurrency is not an issue anymore.

 

Caching Results:

  • -          To help speed up your queries and reduce costs, the Snowflake architecture includes caching at various levels. When a query is run, for example, Snowflake keeps the results of the query for 24 hours. So, if the same query is run again by the same user or another account user, the results are already available to be returned, assuming the underlying data hasn’t changed. This is especially useful for analysis work, as it eliminates the need to rerun complex queries to access previous data or compare the results of complex queries before and after a change.

           

Multi-table INSERT:

  • -          Snowflake allows Multi-table INSERT and threads are executed in parallel.

 

QUALIFY:        

  • -          In a SELECT statement, the QUALIFY clause filters the results of window functions. QUALIFY does with window functions what HAVING does with aggregate functions and GROUP BY clauses.

           

Pricing:

  • -          You can pay for actual consumption only.

Yogesh Shinde

LinkedInProfile

Next >>>

Comments

Post a Comment

Popular posts from this blog

Snowflake Vs MS-SQL - Part 36 - SPLIT_TO_TABLE

Image
       SPLIT_TO_TABLE Sometimes we get a string with commas separated and we need to convert it into rows as below: Suppose you receive a string in this format ' 1,2,3,4,5 ' And you want to return like this. MS-SQL Server   You can achieve this by using multiple ways.  In the recent version of MS SQL Server, they came up with a new String Function  STRING_SPLIT . In  Snowflake ,  we can use a similar function  SPLIT_TO_TABLE  as  STRING_SPLIT.   For more details, you can refer to the following  SPLIT_TO_TABLE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>
Read more

Snowflake Vs MS-SQL - Part 63 - ON Clause & Cross Join

Image
          ON Clause & Cross Join We use different joins to pull the records from multiple tables. We need to use the ON clause along with JOIN. What will happen if we don't use the ON clause? MS-SQL Server   If we don't mention the ON clause, it throws an error. However, if we use the same column from the same table on both sides, it works as a cross-join.  Also, if you mention condition as 1=1 in the ON clause, it treats as CROSS JOIN. However, in  Snowflake,  both above queries will work.  Apart from this, if you don't mention the ON clause, it treats it as CROSS JOIN: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   
Read more

Snowflake Vs MS-SQL - Part 33 - INITCAP

Image
      INITCAP Sometimes we need to provide strings/lines in a specific format as per the client's request. Suppose you want to capitalize the first letter of each word in the string and all other letters appear in lowercase.  It needs special treatment, known as  InitCap (Initial Capitalisation). MS-SQL Server   You can only achieve this differently. You need to write a function or couple of lines of code as below:  In  Snowflake ,  we don't need to write so many lines of code . We can use the inbuilt function  INITCAP. Also, this function gives you the flexibility to provide a delimiter  that INITCAP uses as a separators for words in the input expression.  In the below example, 'q' is used as the delimiter. For more details, you can refer to the following  INITCAP .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>  
Read more