Snowflake Interview Questions

01 June 2023

12 min read

1. What is Snowflake and what are its key features?

Snowflake is a cloud-based data warehousing platform that allows organizations to store, manage, and analyse large volumes of structured and semi-structured data. Its key features include instant scalability, separation of storage and compute, automatic performance optimization, support for various data types, and native support for SQL. It is truly a Saas offering.

The architecture of Snowflake is made up of three layers:

Database Storage
Query Processing
Cloud Services

2. What is the purpose of the Storage Layer in Snowflake?

In Snowflake, the storage layer stores the various data, tables, and query results. The Storage Layer is built on cloud blob storage that is scalable (uses the storage system of AWS, GCP, or Azure). The storage layer is designed to scale completely independently of computing resources, ensuring maximum scalability, elasticity, and performance capacity for data warehousing and analytics.

3. What is the purpose of the Compute Layer in Snowflake?

Virtual warehouses, which are one or more clusters of computing resources, perform all data processing tasks in Snowflake. Virtual warehouses retrieve the bare minimum of data from the storage layer to fulfill query requests.

4. What programming languages are supported in Snowflake?

Snowflake supports various programming languages such as Go, C, . NET, Java, Python, Node.js, etc.

5. What does Snowflake's Cloud Services Layer do?

Snowflake's brain is the services layer. Snowflake's services layer authenticates user sessions, manages them, enforces security functions, compiles and optimizes queries, and coordinates all transactions.

6. What is the difference between a Snowflake database and a schema?

In Snowflake, a database is a container for organizing schemas, tables, and other database objects. It provides an isolated environment for managing data. On the other hand, a schema is a container within a database that logically groups related database objects, such as tables, views, and functions. Multiple schemas can exist within a single database.

7. What are the advantages of a columnar database?

Instead of the traditional row level, columnar databases organize data at the column level. Compared to a row-level relational database, all column-level operations will be much faster and consume fewer resources.

8. How does Snowflake ensure data security?

Snowflake prioritizes data security and provides several measures to protect data. It encrypts data both in transit and at rest using industry-standard encryption algorithms. Snowflake also supports granular access controls, allowing fine-grained permissions to be assigned to users and roles. It offers integration with Identity and Access Management (IAM) systems and supports Multi-Factor Authentication (MFA) for user authentication.

9. What are Snowflake's data loading options?

Snowflake provides multiple data loading options. These include:

Bulk loading: Using Snowflake's COPY command to load data in bulk from files stored in cloud storage platforms like Amazon S3 or Azure Blob Storage.
Streaming: Snowflake supports continuous ingestion of data streams through its Snowpipe feature, which allows near real-time data loading.
External tables: Snowflake can create virtual tables that reference data stored externally in cloud storage, enabling query access without loading the data into Snowflake.

Unlock the Power of Data with Snowflake Training at traininghub.io

10. What are the different stages involved in a typical data load process in Snowflake?

The typical data load process in Snowflake involves the following stages:

a. Extract: Extract data from various sources, such as databases, files, or streaming platforms.

b. Transform: Apply necessary transformations or cleansing operations to prepare the data for loading into Snowflake.

c. Load: Load the transformed data into Snowflake using one of the data loading options, such as bulk loading or streaming.

d. Validate: Perform data quality checks and ensure the loaded data is accurate and complete.

e. Index and Optimize: Create appropriate indexes and optimize the table structure to improve query performance.

11. What are the cloud platforms currently supported by Snowflake?

Snowflake supports multiple cloud platforms.

Amazon Web Services (AWS): Snowflake has a strong integration with AWS, leveraging services like Amazon S3 for storage, Amazon EC2 for compute resources, and Amazon VPC for network connectivity.
Microsoft Azure: Snowflake is available on the Azure cloud platform, utilizing Azure Blob Storage for storage, Azure Virtual Machines for compute resources, and Azure Virtual Network for networking.
Google Cloud Platform (GCP): Snowflake can be deployed on GCP, making use of Google Cloud Storage for storage, Google Compute Engine for compute resources, and Google Virtual Private Cloud (VPC) for network connectivity.

12. Is Snowflake an ETL tool?

Yes, Snowflake is an ETL tool. It’s a three-step process, which includes:

Extracts data from the source and creates data files. Data files support multiple data formats like JSON, CSV, XML, and more.
Loads data to an internal or external stage. Data can be staged in an internal, Microsoft Azure blob, Amazon S3 bucket, or Snowflake managed location.
Data is copied into a Snowflake database table using the COPY INTO command.

13. How is data stored in Snowflake?

Snowflakes store the data in multiple micro partitions which are internally optimized and compressed. The data is stored in a columnar format in the cloud storage of Snowflake. The data objects stored by Snowflake cannot be accessed or visible to the users. By running SQL query operations on Snowflake, you can access them.

14. How is Snowflake distinct from AWS?

Snowflake offers storage and computation independently, and storage cost is similar to data storage. AWS handles this aspect by inserting Redshift Spectrum, which enables data querying instantly on S3, yet not as continuous as Snowflake.

15. What type of database is Snowflake?

Snowflake is built entirely on a SQL database. It’s a columnar-stored relational database that works well with Excel, Tableau, and many other tools. Snowflake contains its query tool, supports multi-statement transactions, role-based security, etc., which are expected in a SQL database.

16. What ETL tools do you use with Snowflake?

Snowflake integrates well with various popular ETL tools, allowing you to extract data from different sources, transform it, and load it into Snowflake for analysis. Some commonly used ETL tools with Snowflake include:

Informatica: Informatica PowerCenter and Informatica Cloud provide robust ETL capabilities and have native connectors to Snowflake.
Talend: Talend Data Integration is a widely used ETL tool that supports Snowflake as a target data warehouse. It offers a drag-and-drop interface for designing data integration workflows.
Matillion: Matillion ETL is a purpose-built ETL tool for cloud data warehouses, including Snowflake. It offers a range of pre-built connectors and transformations for easy data integration.

17. What kind of SQL does Snowflake use?

Snowflake supports the most common standardized version of SQL, i.e., ANSI for powerful relational database querying.

18. Explain Virtual warehouse in snowflake

In Snowflake, a virtual warehouse is a compute resource that is used to process queries and perform operations on data stored in Snowflake. It is the unit of computational power in Snowflake's architecture. Virtual warehouses are designed to scale dynamically based on workload demands, providing instant elasticity and concurrency.

19. Is Snowflake OLTP or OLAP?

Snowflake is primarily designed as an OLAP (Online Analytical Processing) platform rather than an OLTP (Online Transaction Processing) system. OLAP systems are optimized for complex analytical queries and aggregations on large volumes of data, while OLTP systems are focused on handling high-volume transactional workloads with low-latency requirements.

20. How does Snowflake handle data compression?

Snowflake compresses all of the data by default. Snowflake selects the best compression algorithms and does not allow end-users to customize them. The best part is that Snowflake charges customers based on the final data size after compression.

21. What is Schema?

Schemas and databases used for the arrangement of the stored data. It is a logical grouping of database objects like views, tables, etc. The advantages of Snowflake diagrams are that they provide structured data and use limited disk space.

22. Explain Snowpipe

The Snowpipe continuous data ingestion service loads files minutes after being added to a stage and submitted for ingestion. Instead of manually running COPY statements on a schedule to load large batches, you can load data from files in micro-batches (organizing data into small groups/matches), allowing users to access the data within minutes (speedy response time). Snowpipe divides data analysis into micro-batches, making it more manageable. Snowpipe uses a combination of filenames and file checksums to ensure that only new data is processed.

The benefits of Snowpipe are as follows:

Snowpipe makes real-time analytics possible by removing obstacles.
It's a good deal.
It's easy to operate.
Management isn't necessary.
It offers a variety of benefits, including adaptability and resilience.

>> What is Zero-copy cloning?

It is an implementation that enables us to create a copy of tables, schemas and databases without actually copying the data. For performing zero-copy in Snowflake, we must use a keyword named CLONE. This option allows you to obtain real-time data from production and carry out several actions.

23. What is Snowflake Caching, and why should you care?

When you submit a new query, Snowflake checks for previously executed queries, and if one exists and the results are still cached, it uses the cached result set instead of running the query. The results of the Snowflake Cache are global and can be used by anyone.

24) In Snowflake, what are the various types of caching?

Various types of caching in snowflake are:

Caching of Search Results
Local Disk Caching for Virtual Warehouses
Cache for Metadata

25. How do I stop Snowflake from caching query results?

Run the query below to disable the Snowflake Results cache. It should turn off the query for the duration of the session.

alter session set use_cached_result =false;

26. What are the advantages of Snowflake Query Caching?

The default setting for the Results Cache is Automatic. You do not need to do anything special to use this feature.
For 24 hours, all results are cached.
There are no restrictions on space. Snowflake Cache (AWS/GCP/Azure) has infinite storage.
The cache is global and accessible to all WH and users.
As a result of caching, your BI dashboards will load faster.
As a result of caching, the compute cost is reduced.

27. What is SnowSQL used for?

SnowSQL is the command-line client used to connect to Snowflake and conduct SQL queries as well as complete all DDL and DML actions such as loading and unloading data from database tables.

SnowSQL (snowsql executable) can be operated as an interactive shell or in batch mode via stdin or with the -f option.

28. What is the use of Snowflake Connectors?

Snowflake connectors are software components that facilitate seamless integration between Snowflake and other systems, tools, and platforms. They provide standardized and optimized connectivity options, allowing data to be easily exchanged between Snowflake and external data sources or applications.

Snowflake connectors enhance the versatility and connectivity of Snowflake, enabling seamless data integration, extraction, synchronization, and application integration. They play a crucial role in integrating Snowflake into the broader data ecosystem and leveraging its capabilities to drive data-driven insights and decision-making.

29. What are Snowflake views?

Views are useful for displaying certain rows and columns in one or more tables. A view makes it possible to obtain the result of a query as if it were a table. The CREATE VIEW statement defines the query. Snowflake supports two different types of views:

Non-materialized views (often referred to as "views") - The results of a non-materialized view are obtained by executing the query at the moment the view is referenced in a query. When compared to materialised views, performance is slower.
Materialized views - Although named as a type of view, a materialised view behaves more like a table in many aspects. The results of a materialised view are saved in a similar way to that of a table. This allows for faster access, but it necessitates storage space and active maintenance, both of which incur extra expenses.

30. Explain Snowflake Clustering.

Snowflake clustering refers to the organization and arrangement of data within a table based on one or more columns. It is a technique used in Snowflake to improve query performance by reducing the amount of data that needs to be scanned or processed during query execution. When data is clustered in Snowflake, it is physically stored in a specific order based on the clustering keys defined for the table.

It's important to note that the effectiveness of clustering depends on the data distribution and query patterns specific to your use case. Choosing appropriate clustering keys based on the data and workload characteristics is crucial for achieving optimal performance gains.
Overall, Snowflake clustering is a valuable feature that optimizes data organization within tables, improves query performance, and enables cost-effective data processing in Snowflake's cloud-based data warehousing environment.

31. How can we execute Snowflake Procedure?

Stored procedures enable us to create modular code that includes complex business logic by adding different SQL statements with procedure logic. Following are the steps to execute the Snowflake procedure:

Execute the SQL statement
Retrieve the results of the query.
Retrieve the result set metadata.

32. Why do we use Fail-safe?

To reduce the risk factor, DBA typically performs complete and incremental data backups on a regular basis. This process consumes more storage space and can sometimes be double or triple. In addition, the process of recovering data is expensive, takes time, requires a company downtime, and even more.

33. Explain Data Shares

Snowflake Data sharing allows organizations to securely and immediately share their data. Secure data sharing enables sharing of the data between the accounts through Snowflake secure views, database tables.

34. Does Snowflake use Indexes?

No, Snowflake does not use indexes. This is one of the aspects that set the Snowflake scale so good for the queries.

35. Where do we store data in Snowflake?

Snowflake systematically creates metadata for the files in the external or internal stages. We store metadata in the virtual columns, and we can query through the standard “SELECT” statement.

36. What is “Stage” in Snowflake?

In Snowflake, stages are data storage locations. If the data to be imported into Snowflake is stored in another cloud area, such as AWS S3, Azure, or GCP, these are referred to as External stages; if the data is stored within Snowflake, they are referred to as Internal stages.

Internal Stages are further divided as below: