Snowflake Interview Questions and Answers

The snowflake data warehouse is a cloud-based Analytical data warehouse provided as Software-as-a-service (SaaS). It runs on cloud infrastructures like AWS, Microsoft Azure, and GCP (Google Cloud Provider). It cannot be run on a private cloud or hosted infrastructure.

Snowflake is available on AWS, Azure, and GCP. Snowflake supports loading data from files staged in any of the following locations, regardless of the cloud platform for your Snowflake account:

• Internal (i.e., Snowflake) stages
• Amazon S3
• Microsoft Azure blob storage
• Google Cloud Storage

ETL stands for Extract, Transform, Load — three critical words when moving data to Snowflake. Here’s how it works:

Extract: ETL extracts data from all kinds of data sources. Think relational databases, flat files, legacy systems, SaaS sources, CRMs, ERPs, etc.

Transform: It transforms the data from an unusable format to a usable format. (Otherwise, the data is useless.)

Load: It loads the data into a warehouse like Snowflake.

Following are the some ETL tools for Snowflake used:

• Matillion
• Blendo
• Apache Airflow
• StreamSets
• Stitch
• Etleap
• io
• Hevo Data
• Informatica

• Data Computing
• Data warehouse
• Data lake
• Data engineering
• Data science
• Data applications
• Data sharing
• Data Management & integration
• Cloud Data Analytics
• Data Security

We can access the Snowflake data warehouse through:

• JDBC Drivers
• ODBC Drivers
• Web User Interface
• Python Libraries
• SnowSQL Command-line Client

Snowflake comes with the following features

• Data Protection
• Standard and Extended SQL Support
• Tools and Interfaces
• Concurrency and Accessibility
• Data Import and Export
• Data Sharing
• Security
• Result Caching
• XML Support
• Search Optimization Service
• Database Replication and Failover
• Compute Layer

Data Warehouse also known as an enterprise data warehouse (EDW). It is a central information repository of the current and historical information that enables Data Analytics and Business Intelligence (BI) activities. Data warehouses can store vast amounts of data from numerous sources in a single location, run queries and perform analyses to help businesses optimize their operations. Its analytical skills enable companies to gain significant insights from their data and make better decisions.

Snowflake Fail-safe is used to Disaster recovery of historical data (by Snowflake). 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.   

Snowflake Snowpipe is essentially a COPY command that sits on top of a cloud storage location. It is a serverless, scalable, and optimized data ingestion utility provided by Snowflake for continuously loading data into Snowflake tables.

Zero Copy Cloning is a feature which provides a quick and easy way to create a copy of any table, schema, or an entire database without incurring any additional costs as the derived copy shares the underlying storage with the original object.

Stream is a feature where you can track the changes happening in the data for a table. You can capture all inserts, updates, and deletes as well. You can capture changes from the time when the stream is enabled but you will not be able to capture the change before that. It supports 3 types of streams:

Standard: It supports tracking of all inserts, updates, and deletes.

Append only: It supports tracking of all inserts on the table.

Insert Only: It is supported only on external tables.

Using the time travel feature of the Snowflake, we can select and retrieve the data from the table, present at the specified time in the table.

By using AT clause along with select query as follows:

-- select the data as of specified date time in snowflake using the time travel

select * from Snowflake_Task_Demo  at (TIMESTAMP=>'2022-08-4 00:57:35.967'::timestamp)

Using the history command along with the show command will show all the delete ordropped schemas. However, only the schemas which are still in the retention period will be shown.

Example:

-- Show deleted schemas

show SCHEMAS history;

— Check for dropped_on column. For deleted schemas it would be not null.

If by mistake a database got deleted or dropped then you might want it to restore or undrop again. In Snow flake, By Using under DATA RETENTION period is possible to restore the deleted database only

SQL to restore the database as follows:

-- Undrop database

undrop database database_name;

-- This will restore the database

In snow flake, The UNDROP command can be used to restore a dropped table, schema, or database that has not yet been purged from the system (i.e. not yet put into Snowflake Fail-safe). UNDROP returns the item to its most recent state, which was before it was dropped.

Following syntax enables to create of a temporary table in the Snowflake

Syntax: create temporary table mytable (id number, creation_date date);

Metadata is stored in virtual columns that can be: Queried using a standard SELECT statement.

Snowflake uses OLAP as a foundational part of its database schema.

The commonly used pattern matching operators.

LIKE and ILIKE

RLIKE

LIKE ANY and LIKE ALL

CONTAINS

Query Syntax:

[ … ]

SELECT [ { ALL | DISTINCT } ]

    {

          [{<object_name>|<alias>}.]*

        | [{<object_name>|<alias>}.]<col_name>

        | [{<object_name>|<alias>}.]$<col_position>

        | <expr>

        [ [ AS ] <col_alias> ]

    }

    [ , … ]

[ … ]

By using the COPY INTO <table> command.

A clustering key is a subset of columns in a table or an expression that are explicitly used to co-locate the data in the table in the same micro-partition.

Syntax: CREATE TABLE <name> ... CLUSTER BY ( <expr1> [ , <expr2> ... ] )

You can use the session variables in conjunction with your query. Also, use the SET command to set session variable.

The Table stages are referenced using @%tableName.

SnowSQL command is used to upload files to table stage area.

Syntax: DECODE( <expr> , <search1> , <result1> [ , <search2> , <result2> ... ] [ , <default> ] )

Snowflake supports TO_JSON and PARSE_JSON functions to convert and validate json values.

TO_JSONSyntax:.

TO_JSON( <expr>)

PARSE_JSON syntax:

parse_json( <expr>)

When you are executing SQL queries interactively at the SnowSQL prompt either by using -q or -f options, the command returns one of the following exit codes:

0: Everything ran smoothly.

1: Something went wrong with the client.

2: Something went wrong with the command line arguments.

3: SnowSQL could not contact the server.

4: SnowSQL could not communicate properly with the server.

5: The exit_on_error configuration option was set and SnowSQL exited because of an error.

Extensive set of client connectors and drivers provided by Snowflake:

• Spark connector
• Python connector
• JDBC client driver
• ODBC client driver
• js driver
• PHP PDO driver
• .NET driver
• Go Snowflake driver

Yes, some major competitors of Snowflake include:

• Microsoft Azure Synapse Analytics (formerly SQL Data Warehouse)
• Google Cloud BigQuery
• Amazon Redshift
• SAP Data Warehouse Cloud
• Oracle Autonomous Warehouse
• IBM Db2 Database
• Marklogic
• Datastax
• Cloudera