Top Snowflake Interview Questions (2024)

1. What is Snowflake?

2. What are the features of Snowflake Data Warehouse?

3. What are the different types of tables in Snowflake?

4. What are stages in Snowflake?

5. What is Stream in Snowflake?

6. What is metadata in Snowflake?

7. How to do bulk loading from local system in Snowflake?

8. How to load the data from stage files (AWS S3 bucket) into Snowflake database tables?

9. What is MFA in Snowflakes?

10. How to load Parquet file into Snowflake table?

Q: What is Snowflake?
Ans:

Snowflake is a SaaS (Software-as-a-Service) cloud-based Data Warehouse system that fully supports ANSI SQL. It also features a unique architecture that allows users to simply create tables and begin querying data with very no administrative or DBA involvement.

Q: What are the features of Snowflake Data Warehouse?
Ans:

Below are the features of Snowflake Data Warehouse:

1. Security, Governance, and Data Protection

   • It provides enhanced authentication through Multi-factor authentication (MFA), Federated authentication and single sign-on (SSO), OAuth.
   • TLS protects all interaction between clients and servers.
   • Snowflake Fail-safe (7 days)
   • Data isolation (for loading and unloading) via Amazon S3 policy controls, Azure storage access controls, Google Cloud Storage access permissions.

2. Standard and Extended SQL Support

   Snowflake data warehouse supports the majority of SQL DDL and DML operations. Advanced DML, transactions, lateral views, stored procedures, and other features are also supported.

3. Tools and Interfaces

   It provides Web-based GUI, SnowSQL (Python-based command line client).

4. Connectivity

   Snowflake data warehouse offers a wide range of client connectors and drivers, including Python connectors, Spark connectors, Node.js drivers,.NET drivers, and so on.

5. Data Import and Export

   Allow for bulk loading and unloading data into and out of tables (CSV, TSV, JSON, Avro, ORC, Parquet, and XML formats, cloud storage, file, etc.). Snowpipe is used to load data in micro-batches from internal (Snowflake) or external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stages.

6. Data Sharing

   Allow for sharing data with other Snowflake accounts in a secure manner.

7. Database Replication and Failover

   Database replication and synchronization across several Snowflake accounts in various regions is supported.

Q: What are the different types of tables in Snowflake?
Ans:

Snowflake provides three kinds of tables: temporary, transient, and permanent. Permanent is the default. Temporary tables exist just during the session in which they have been created and are only valid for the duration of that session.

Q: What are stages in Snowflake?
Ans:

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

Q: What is Stream in Snowflake?
Ans:

A stream is a new Snowflake object type that tracks the delta of modifications in a table, both inserts and data manipulation language (DML) modifications, so that action may be made using the changed data.

Q: What is metadata in Snowflake?
Ans:

Snowflake creates metadata (like filename, row numbers of records in files) automatically for files in internal (i.e. Snowflake) or external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stages. This information is stored in virtual columns and can be queried to retrieve the information from the metadata column.

Q: How to do bulk loading from local system in Snowflake?
Ans:

1. Using the PUT command, upload (stage) one or more data files to a Snowflake stage (called internal stage or table/user stage).

   PUT file://
   <path_to_file>/
       <filename> internalStage
           [ PARALLEL =
           <integer> ]
               [ AUTO_COMPRESS = TRUE | FALSE ]
               [ SOURCE_COMPRESSION = AUTO_DETECT | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE |
               RAW_DEFLATE | NONE ]
               [ OVERWRITE = TRUE | FALSE ]

2. To load the contents of the staged file(s) into a Snowflake database table, use the COPY INTO table command.

Uploading files to external stages is not supported by PUT. Use the cloud service's facilities to upload files to external stages.

Q: How to load the data from stage files (AWS S3 bucket) into Snowflake database tables?
Ans:

We can use our existing Amazon Web Services (AWS) S3 buckets which store and manage our data files.

We can use the COPY INTO table command to load the data from stage files into Snowflake database tables. Although we can load directly from the S3 bucket, however Snowflake suggests constructing an external stage that references the bucket and using it rather.

Q: What is MFA in Snowflakes?
Ans:

Snowflake offers Multi-Factor Authentication (MFA) to provide increased login security for users connecting to Snowflake by requiring two or more verification factors to obtain access to a resource.

MFA supports an integrated Snowflake feature, enabled by the Duo Security service and totally controlled by Snowflake.

Q: How to load Parquet file into Snowflake table?
Ans:

1. Use the PUT command to upload the data file to the Snowflake Internal stage.
2. Copy the file from the internal stage to the Snowflake table using COPY INTO table command.