Explore topic-wise InterviewSolutions in .

In the CREATE TABLE DDL, SPECIFY the TEMPORARY keyword (or the TEMP abbreviation) to create a temporary table. The following syntax must be used to create temporary tables:

Among the leading cloud data warehouse solutions is Snowflake due to its innovative features such as separating computing and storage, facilitating data sharing and cleaning, and supporting popular programming languages such as Java, Go, .Net, Python, etc. Several technology giants, including Adobe Systems, Amazon Web Services, Informatica, Logitech, and Looker, are building data-intensive applications using the Snowflake platform. Snowflake PROFESSIONALS are therefore always in demand.  

Have trouble preparing for a Snowflake job INTERVIEW? Don't worry, we have compiled a list of top 30+ Snowflake interview QUESTIONS and answers that will help you.

Useful Interview Resources:

• Data Warehouse Tools
• Big Data Interview Questions
• Cloud Computing Interview Questions 

For instance, an ARRAY of strings arr is GIVEN. The task is to remove all strings that are anagrams of an earlier string, then print the remaining array in sorted order. 

• Examples:  
  Input: arr[] = { “Scaler”, “Lacers”, “Accdemy”, “Academy” }, N = 4 
  
  Output: [“Scaler”, “Academy”,]  
  
  Explanation: “Listen” and “Silent” are anagrams, so we remove “Silent”. Similarly, “Scaler” and “Lacers” are anagrams, so we remove “Lacers”. 
   
• Code Implementation

• Output:

Data sharing via SNOWFLAKE allows organizations to share data quickly and securely between Snowflake accounts. Database OBJECTS that are shared between snowflake accounts are only readable and can't be changed or MODIFIED. The three types of sharing are as follows: 

• Data sharing between management units 
• Data sharing between FUNCTIONAL units 
• Sharing data between geographically dispersed areas.

Zero-copy cloning is one of the great features of Snowflake. It basically allows you to duplicate the source object without making a physical copy of it or adding additional storage costs to it. A snapshot of the data in a source object is taken when a clone (cloned object) is created, and it is made available to the cloned object. Cloned objects are independent of the source object and are therefore writable, and any changes made to either object are not reflected in the other. The keyword CLONE allows you to copy tables, SCHEMAS, databases without actually copying any data. 

Zero copy cloning syntax in Snowflake

• In ORDER to clone an entire production DATABASE for development purposes:

• In order to clone a schema

• In order to clone a SINGLE table:

Snowflake offers MULTIPLE editions to MEET your organization's specific needs. In every subsequent EDITION, either new features are introduced or a higher level of service is provided. It's easy to switch editions as your organization's needs change. 

The following are some of the Snowflake Editions: 

• Standard Edition: This is Snowflake's entry-level offering, which allows full access to all of Snowflake's standard features. This edition strikes a good balance between features, level of support, and price.
• Enterprise Edition: With Enterprise Edition, you get all the features and services of Standard Edition, but with some extra features designed to meet the needs of large-scale enterprises.
• Business-critical Edition: Previously known as Enterprise for Sensitive Data (ESD), Business Critical Edition offers even more advanced levels of data security for organizations that deal with highly sensitive data. This edition includes all the features and services of the Enterprise Edition, plus enhanced security and data protection.
• Virtual Private Snowflake (VPS): With this edition, organizations with strict security REQUIREMENTS, like financial institutions and companies that collect, analyze, and share highly sensitive data, can get the highest level of security.

CONSIDER an example where a query takes 15 minutes to RUN or execute. Now, if you were to repeat the same query with the same frequently used data, later on, you would be doing the same work and wasting resources.
Alternatively, Snowflake caches (stores) the results of each query you run, so whenever a new query is submitted, it checks if a matching query ALREADY exists, and if it did, it uses the cached results rather than running the new query again. DUE to Snowflake's ability to FETCH the results directly from the cache, query times are greatly reduced. 

Types of Caching in Snowflake 

• Query Results Caching: It stores the results of all queries executed in the past 24 hours.
• Local Disk Caching: It is used to store data used or required for performing SQL queries.  It is often referred to as
• Remote Disk Cache: It holds results for long-term use.

The following diagram visualizes the levels at which Snowflake caches data and results for subsequent use. 

An important aspect of data compression is the encoding, restructuring, or other modifications necessary to minimize its size. As soon as we input data into Snowflake, it is systematically compacted (COMPRESSED). Compressing and storing the data in Snowflake is achieved through modern data compression ALGORITHMS. What makes snowflake so great is that it CHARGES customers by the size of their data after compression, not by the exact data. 

Snowflake Compression has the following advantages: 

• Compression lowers storage costs compared with original cloud storage.
• On-disk caches do not incur storage costs.
• In general, data sharing and CLONING involve no storage expenses.

YES, you can connect the Snowflake to AWS glue. AWS glue fits seamlessly into Snowflake as a data WAREHOUSE service and presents a comprehensive managed ENVIRONMENT. Combining these two SOLUTIONS makes data ingestion and transformation EASIER and more flexible.

Cloud-based DATA warehouse platforms like Snowflake and Amazon Redshift provide excellent performance, scalability, and business intelligence tools. In terms of CORE FUNCTIONALITY, both platforms provide similar capabilities, such as RELATIONAL management, security, scalability, cost efficiency, etc. There are, however, several differences between them, such as pricing, user experience and deployment options. 

• There is no maintenance required with Snowflake as it is a complete SaaS (Software as a Service) offering. In contrast, AWS Redshift clusters require manual maintenance.
• The Snowflake security model uses always-on encryption to enforce strict security checks, while Redshift uses a flexible, customizable approach.
• Storage and computation in Snowflake are completely independent, meaning the storage costs are approximately the same as those in S3. In contrast, AWS bypasses this problem with a Red Shift spectrum and lets you QUERY data that is directly available in S3. Despite this, it is not flawless like Snowflake.

SNOWFLAKE offers a default 7-day PERIOD during which HISTORICAL data can be retrieved as a fail-safe feature. Following the expiration of the Time Travel data retention period, the fail-safe default period begins. Data recovery through fail-safe is performed under best-effort conditions, and only after all other recovery options have been exhausted. Snowflake may use it to recover data that has been lost or DAMAGED due to extreme operational failures. It may take several HOURS to several days for Fail-safe to complete data recovery. 

The DATA retention period is a critical component of SNOWFLAKE Time TRAVEL. When data in a table is modified, such as when data is deleted or objects containing data are removed, Snowflake preserves the state of that data before it was updated. Data retention specifies how many days historical data will be preserved, enabling Time Travel operations (SELECT, CREATE, CLONE, UNDROP, etc.) to be performed on it.  

All Snowflake ACCOUNTS have a default retention period of 1 day (24 hours). By default, the data retention period for standard objectives is 1 day, while for enterprise editions and higher accounts, it is 0 to 90 days.  

Time travel is a Snowflake feature that gives you access to historical data present in the Snowflake data warehouse. For example, SUPPOSE you accidentally DELETE a table named Employee. Using time travel, it is possible to go BACK five minutes in time to retrieve the data you lost. Data that has been altered or deleted can be accessed via Snowflake Time Travel at any point within a defined period. It is capable of performing the following tasks within a specific/defined period of time: 

• Analyzing data manipulations and usage over a specified period of time.
• Restoring data-related OBJECTS (tables, schemas, and databases) that are accidentally lost (dropped)/
• BACKUP and duplication of data (clones) at or before specific points in the past.

As soon as the defined/specific period of time (data retention period) expires, the data moves into Snowflake Fail-safe and these actions/tasks cannot be performed. 

Schemas LIKE Star and Snowflake serve as a logical description of the entire database, or how data is ORGANIZED in a database. 

• Star Schema: A star schema typically consists of one fact table and SEVERAL associated dimension tables. The star schema is so named because the structure has the appearance of a star. Dimensions of the star schema have been denormalized. When the same values are repeated within a table, it is considered denormalization.

• Snowflake Schema: In a snowflake schema, the center has a fact table, which is associated with a number of dimension tables. Those dimension tables are in turn associated with other dimension tables. Snowflake schemas provide fully normalized data structures. Separate dimensional tables are used for the various levels of hierarchy (CITY > country > region).

The Snowflake Schema describes how data is organized in Snowflake. Schemas are basically a logical grouping of database objects (such as tables, views, etc.). Snowflake schemas consist of one fact table linked to many dimension tables, which link to other dimension tables via many-to-one relationships. A fact table (stores quantitative data for analysis) is surrounded by its associated dimensions, which are related to other dimensions, FORMING a snowflake PATTERN. Measurements and facts of a business process are contained in a Fact Table, which is a key to a Dimension Table, while attributes of measurements are stored in a Dimension Table. Snowflake offers a complete set of DDL (Data Definition Language) commands for CREATING and maintaining DATABASES and schemas. 

As shown in the above diagram, the snowflake schema has one fact table and two-dimension tables, each with THREE levels. Snowflake schemas can have an unlimited number of dimensions, and each dimension can have an infinite number of levels. 

After data is loaded into Snowflake, it automatically reorganizes the data into a compressed, optimized, columnar format (micro-partitions). The optimized data is then stored in the cloud storage. Snowflake manages all aspects of storing these data, including file structure, size, statistics, compression, metadata, etc. Snowflake data objects aren't visible to CUSTOMERS or users. Users can only access data by performing SQL queries on Snowflake. Snowflake uses a columnar format to optimize and store data within the storage layer. With it, data is stored in columns instead of rows, allowing for an ANALYTICAL querying method and improving database performance. With Columnar databases, business intelligence will be easier and more ACCURATE. COMPARED to row-level operations, column-level operations are faster and use FEWER resources than row-level operations. 

Above is an example of a table with 24 rows divided into four micro-partitions, arranged and sorted by column. As the data is divided into micro-partitions, Snowflake can first remove those micro-partitions not relevant to the query, followed by pruning the remaining micro-partitions by column. The result is fewer records traversed, resulting in significantly faster response times.