Posts

Types of Tables in Snowflake

Tables are the database objects which are nothing but a logical collection of rows and columns. Snowflake supports three types of tables: 1. Temporary tables 2. Permanent tables 3. Transient tables Temporary tables: These tables exist only within the session, they will be deleted once the session gets ended. They are not visible to other users or sessions. create temporary table employee (id number, name varchar(50)); Permanent tables: These are the default table type in the Snowflake, they do not need any additional syntax to make them permanent. create table employee (id number, name varchar(50)); Transient tables: Similar to permanent tables except they do not have a fail-safe period. create transient table employee (id number, name varchar(50)); Key Differences: Default time travel period : "1" day for all the tables. Storage Costs : Transient and Temporary Fail Safe Cost : Only Permanent Temporary and Transient : All Editions (0-1) Permanent : Standard Edition (0-1) Perm...
Post a Comment
Read more

Caches in Snowflake

Caching is a process of storing frequently accessed data for faster access and to reduce resource usage.  There are 3 types of Caches. 1. Result Cache | Query Result Cache 2. Metadata Cache 3. Local Disk Cache | Virtual Warehouse Cache Result Cache: Stores the result of the every query executed in the past 24 hours and provides the same data without any compute charges, provided if the underlined data hasn't changed and no syntactical changes. Metadata Cache: Stores the metadata information about the databases,views and the tables. Like the number of rows present in a table. ( Count(*) ) Minimum and maximum value of the columns. The count of distinct values in a column. Local Disk Cache: Result and metadata cache are implemented in cloud service layer, whereas local-disk cache in compute layer.
Post a Comment
Read more

Understanding Snowflake Architecture:

Image
Snowflake is a modern data warehousing platform built on the cloud to overcome the problems that were faced earlier while using the traditional shared-disk and shared-nothing architectures. As I said, in traditional shared-disk architecture, the workload was being shared by the various computers with a single storage disk, causing an issue with the performance of the system. Whereas in shared-nothing architecture, Each computer comes with its own storage working as distributed computing process, leading to high performance but coming with the expensive scalability for immediate heavy workloads. But, Snowflake is built on the combination of shared-disk and shared-nothing architectures called as Multi-Cluster Shared disk architecture. Where the Storage and Compute are independent to scale themselves. Here, we also have a feature called as auto-scaling feature which scales the virtual warehouses based on the workloads giving high performance and easy access to the data. There are 3 layers...
Read more