Snowflake Interview Question

What action should be taken by a Snowflake administrator regarding MFA (multi factor authentication) for a user?

We should temporarily disable MFA for that user completely. Disable MFA enrollment for a user, and, if necessary, allow a user to re-enroll in MFA using the ALTER USER.

What data types does Snowflake support?

Supported Snowflake Data Types

  • Character data. ARRAY. TEXT. CHAR, CHARACTER. VARCHAR(n) OBJECT. VARIANT. STRING.
  • Numeric data. BIGINT. FLOAT8. BOOLEAN. INT. DECIMAL. INTEGER. DOUBLE. NUMBER(p,s) DOUBLE PRECISION. NUMERIC. FLOAT. REAL. …
  • Date, time, and timestamp data. DATE. TIMESTAMP_LTZ. DATETIME. TIMESTAMP_NTZ. TIME. TIMESTAMP_TZ. TIMESTAMP.

Is Snowflake all data at rest is always encrypted?

Yes.In Snowflake, all data at rest is always encrypted. As we know that query results can be unloaded into a stage.

Results are encrypted using client-side encryption when unloaded into a customer-managed stage, and are automatically encrypted when unloaded to a Snowflake-provided stage.

How can you unload of data from Snowflake?

  • It requires a running virtual warehouse.
  • Download the file from the stage: From a Snowflake stage, use the GET command to download the data file(s).
  • From S3, use the interfaces/tools provided by Amazon S3 to get the data file(s).
  • From Azure, use the interfaces/tools provided by Microsoft Azure to get the data file(s).

How is data in a Snowflake table is automatically encrypted?

  • Snowflake uses strong AES 256-bit encryption with a hierarchical key model rooted in a hardware security module.
  • Keys are automatically rotated on a regular basis by the Snowflake service, and data can be automatically re-encrypted on a regular basis.

Does Snowflake support querying data in an internal or external stage using Standard SQL?

  • Snowflake supports standard SQL to query data files located in an internal stage or named external (Amazon S3, Google Cloud Storage, or Microsoft Azure) stage.
  • This can be useful for viewing the contents of the staged files, particularly before loading or after unloading data.

What are Snowflake stage types?

  • It describes the values specified for the properties in a stage (file format, copy, and location), as well as the default values for each property.
  • Snowflake supports two types of stages for storing data files used for loading/unloading:
  • Internal stages that store the files internally within Snowflake.
    External stages that store the files in an external location (i.e. S3 bucket) that is referenced by the stage.

What can we do when data is UNLOADED into a Snowflake stage (internal or external)?

  • We can download data (files) from the INTERNAL stage and decrypts the data on machines.
  • If the data is unloaded into an EXTERNAL stage the data is NOT automatically encrypted however we can choose to encrypt as an option
  • If the data is unloaded into an INTERNAL stage the data IS automatically encrypted

What are the ways to improve performance of an external table?

  • Partition the external table (if the logical folder structure permits) – Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e. partitioning the external table).
  • Create materialized views on top of the external table – Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns. 

If we stage un-encrypted files in a Snowflake internal stage then what will happen?

Answer: The files will be encrypted by Snowflake automatically.

The load history for Snowpipe is stored in the metadata for how many days?

14 days.Stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view.

What are supported file formats when UNLOADING data from Snowflake?

Answer :Parquet Delimited Text JSON