Explore topic-wise InterviewSolutions in .

In SNOWFLAKE, clustering is a type of DATA partitioning, where unique cluster keys are SPECIFIED for each table. Cluster keys are subsets of a table's columns that are used to co-locate data within the table. These keys are APPROPRIATE for comprehensive TABLES. The process of managing clustered data in a table is known as re-clustering. 

All Snowflake's FEATURES are built on top of SQL (Structured Query Language) databases. The data in this relational database SYSTEM is stored in columns and it is compatible with other tools, including Excel and Tableau. As a SQL database, Snowflake contains a query TOOL, supports multi-statement transactions, PROVIDES role-based security, etc. 

Snowflake is developed as an OLAP (Online Analytical Processing) database system, not as an OLTP (Online TRANSACTION Processing) database system. In OLTP (Online Transaction Processing), data is collected, stored, and processed from real-time transactions, but in OLAP (Online Analytical Processing), complex queries are used to evaluate aggregated historical data from OLTP systems. Snowflake is not designed to handle much updating and inserting of SMALL amounts of data like a transactional database would. Snowflake, for INSTANCE, cannot handle referential integrity because, even though it supports integrity and other constraints, they are not ENFORCED except the NOT NULL CONSTRAINT that is always enforced. Other constraints than NOT NULL are created as disabled constraints. However, depending on the use, we may also use it for online transaction processing (OLTP). 

• Horizontal Scaling: Horizontal scaling increases concurrency by scaling horizontally. As your customer base GROWS, you can use auto-scaling to increase the number of virtual warehouses, enabling you to respond instantly to additional queries.
• Vertical Scaling: Vertical scaling involves increasing the processing power (e.g. CPU, RAM) of an existing machine. It generally involves scaling that can reduce processing time. Consider CHOOSING a larger virtual warehouse-size if you want to OPTIMIZE your WORKLOAD and make it run faster.

SNOWFLAKE is compatible with the following ETL TOOLS:  

• Matillion
• Blendo
• Hevo Data
• StreamSets
• Etleap
• Apache Airflow, ETC.

Yes, Snowflake is an ETL (Extract, TRANSFORM, and Load) tool, since it is performed in three steps, including:   

• The data is extracted from the source and saved in data files in a variety of formats including JSON, CSV, XML, and more.
• Loads data into a stage, either internal (Snowflake managed location) or external (Microsoft Azure, Amazon S3 bucket, GOOGLE Cloud).
• The COPY INTO command is used to copy data into the Snowflake DATABASE.

Every organization considers DATA security to be one of its TOP priorities. The Snowflake platform adheres to the best security standards in the industry to encrypt and safeguard customer data. The platform provides the best key management features at no additional charge. To protect client data, Snowflake EMPLOYS the FOLLOWING security measures: 

• Snowflake automatically encrypts the data that it contains using a managed key.
• Snowflake is based on Transport Layer Security (TLS) to ensure data security between CUSTOMERS and servers.
• Depending on your cloud region, you can select a geographic location to store your data.

SNOWFLAKE currently supports the following cloud platforms: 

• Amazon WEB SERVICES (AWS)
• Google Cloud Platform (GCP)
• MICROSOFT AZURE (Azure).

The TERM SNOWFLAKE computing refers to Snowflake's ability to provide instant, secure, and governed access to all data networks, ALONG with its core architecture that enables multiple types of data workloads and offers a unified platform for modern data applications.  In contrast to other data warehouses, Snowflake does not use a database or "big data" software platform such as Hadoop. Snowflake, however, combines an entirely new SQL QUERY engine with a natively cloud-based architecture.  

In simple terms, Snowpipe is a continuous data ingestion service provided by Snowflake that LOADS files within minutes as soon as they are added to a stage and submitted for ingestion. Therefore, you can load data from files in micro-BATCHES (organizing data into small groups/matches), allowing users to access the data within minutes (very less response time), rather than MANUALLY running COPY statements on a schedule to load large batches. By loading the data into micro-batches, Snowpipe makes it EASIER to analyze it. Snowpipe uses a combination of filenames and file checksums to ensure that only new data is PROCESSED.  

Advantages of Snowpipe -

• By eliminating roadblocks, Snowpipe facilitates real-time analytics.
• It is cost-effective.
• It is simple to use.
• There is no management required.
• It provides flexibility, resilience, and so on.

STAGES are locations in Snowflake where data is stored, and staging is the process of uploading data into a stage. Data that needs to be loaded or stored within Snowflake is stored either elsewhere in the other cloud regions LIKE in AWS (Amazon Web Service) S3, GCP (Google Cloud Platform), or AZURE, or is stored internally within Snowflake. When data is stored in another cloud region, this is known as an EXTERNAL stage; when it is stored inside a snowflake, it is known as an internal stage. Internal stages can be further categorized as follows: 

• User stages: Each of these stages pertains to a specific user, so they'll be assigned to every user by default for storing files.
• Table stages: Each of these stages pertains to a specific database table, so they'll be assigned to every table by default.
• Internal named stages: Compared to the user or table stages, these stages offer a greater degree of flexibility. As these are some of the Snowflake objects, all operations that can be performed on objects can also be performed on internally named stages. These stages must be created manually and we can specify file FORMATS when creating these stages.

Cloud-based data warehouses are becoming increasingly popular, with Redshift and Snowflake being two of the biggest players. These are large data analytics databases capable of analyzing and READING vast amounts of data. 

Snowflake vs Redshift - 

Snowflake's data warehouse can be accessed using the following ways:  

• ODBC Drivers (a driver for CONNECTING to Snowflake).
• JDBC Drivers (a driver enabling a Java application to interact with a database).
• PYTHON LIBRARIES (for creating Python applications that connect to Snowflake and perform standard operations).
• Web USER Interface (can be used for almost any task you can accomplish with SQL and the command line, such as: Creating and managing users and other account-level objects).
• SnowSQL Command-line Client (Python-based command-line interface to connect to Snowflake from Windows, Linux, and MacOS).

A VIRTUAL warehouse is basically a collection of computing resources (like CPU, memory, Solid state drive, etc.) that customers can access to run queries, load data, and perform other DML (Data Manipulation Language) and SQL (Structured Query Language) OPERATIONS. 

For example, it provides memory, temporary storage, and CPU resources that can be used for DML operations and SQL execution. You can use this independent compute cluster at any time and then TURN it off when not needed. You are charged (paid) for each virtual warehouse you run, their size, and how long they run. Virtual warehouses do not SHARE their compute resources with each other, and therefore, their performance is independent of each other.   

As shown in the FOLLOWING diagram, different groups of users can be assigned separate and dedicated virtual warehouses. Therefore, ETL processes can continuously load and execute complex transformation procedures on separate warehouses, ensuring no impact on data scientists or finance reports. 

The Snowflake architecture is a hybrid of shared-disk (A common disk or storage device is shared by all computing nodes) and shared-nothing (Each computing node has a private MEMORY and storage space) database architecture in order to combine the best of both. Snowflake utilizes a central data repository for persistent data, which is available to all compute nodes similar to a shared-disk architecture. But, equally, as with shared-nothing architectures, Snowflake uses massively parallel computing (MPP) clusters for query processing, in which each node stores part of the whole data set locally.

The Snowflake architecture is divided into three key layers as shown below:   

• Database Storage Layer: Once data has been loaded into Snowflake, this layer REORGANIZES that data into a specific format like columnar, compressed, and optimized format. The optimized data is stored in cloud storage.
• Query Processing Layer: In the processing layer, queries are executed USING virtual warehouses. Virtual warehouses are independent MPP (Massively Parallel Processing) compute clusters comprised of multiple compute nodes that Snowflake ALLOCATES from cloud providers. Due to the fact that virtual warehouses do not SHARE their compute resources with each other, their performance is independent of each other.
• Cloud Services Layer: It provides services to administer and manage a Snowflake data cloud, such as access control, authentication, metadata management, infrastructure management, query parsing, optimization, and many more.

Snowflake has the following key FEATURES:

• With Snowflake, you can interact with the data cloud through a web interface. Users can navigate the web GUI to control their accounts, monitor resources, and monitor resources and system usage queries data, etc.
• Users can connect to Snowflake's data cloud using a wide RANGE of client connectors and drivers. Among these connectors are Python Connector (an interface for writing Python applications to connect to Snowflake), Spark connector, NodeJS driver, .NET driver, JBDC driver for Java development, ODBC driver for C or C++ programming, etc.
• The core architecture of Snowflake enables it to operate on the public cloud, where it uses virtualized computing instances and efficient storage buckets for processing huge amounts of big data cost-effectively and scalable.
• Snowflake integrates with a number of big data tools, including business intelligence, machine learning, data integration, security, and governance tools.
• With advanced features such as simplicity, increased performance, high concurrency, and profitability, Snowflake is incomparable to other traditional data warehouse solutions.
• Snowflake supports the storage of both structured and semi-structured data (such as JSON, AVRO, ORC, Parquet, and XML data).
• Snowflake automates cloud data management, security, governance, availability, and data resilience, resulting in reduced costs, no downtime, and BETTER operational efficiency.
• With it, users can rapidly query data from a database, without having an impact on the underlying dataset. This allows them to receive data closer to real-time.
• Most DDL (Data Definition Language) and DML (Data Manipulation Language) commands in SQL are supported by the Snowflake data warehouse. Additionally, advanced DML, lateral views, transactions, stored procedures, etc., are also supported.