5 min read · Aug 17, 2023
--
It is important to provide clear and concise responses during job interviews with any company.
What is a snowflake?
Snowflake is an analytical data warehouse that operates on the cloud and offers software as a service (SaaS).
Why is Snowflake, not any other warehouse? Or What is the advanced feature of Snowflake?
Below are fetchers available in Snowflake.
(1) Snowflake three-layer architected(cloud layer, query processing layer, storage layer).storage and compute layer is decoupled(2)Auto scaling (3)Time travel (4) Zero copy clone (5)Data sharing (6)Multi-language support(7) Task and strim (8) snow pipe (9)snowpark, etc.
What type of user role is available in Snowflake?
Below are six roles available in Snowflake.
Account admin -The account admin can manage all aspects of the account.
Orgadmin- The organization administrator can manage the organization and account in the organization.
Public -Public role is automatically available to every user in an account.
Securityadmin-Security administrator can manage the security aspects of the account.
Sysadmin-System administrator can create and manage database and warehouse.
User admin-user administrator can create and manage user and role
How to validate a file prior to loading it into a target table in Snowflake?
Before loading your data, you can validate that the data in the uploaded files will load correctly. Execute COPY INTO <table> in validation mode
using the VALIDATION_MODE parameter. The VALIDATION_MODE parameter returns any errors that it encounters in a file.
You can then modify the data in the file to ensure it loads without error.
Type of table support Snowflake?
Snowflake offers three types of tables namely, Temporary, Transient & Permanent. Default is Permanent:
Temporary tables:
Only exist within the session in which they were created and persist only for the remainder of the session.
They are not visible to other users or sessions and do not support some standard features such as cloning.
Once the session ends, data stored in the table is purged completely from the system and, therefore, is not recoverable, either by the user who created the table or Snowflake.
Transient tables:
Persist until explicitly dropped and are available to all users with the appropriate privileges.
Specifically designed for transitory data that needs to be maintained beyond each session (in contrast to temporary tables)
Permanent Tables (DEFAULT):
Similar to transient tables the key difference is that they do have a Fail-safe period, which provides an additional level of data protection and recovery.
What is an External Table in Snowflake?
Snowflake External Tables provide a unique way of accessing the data from files in external locations(i.e. S3, Azure, or GCS) without actually moving them into Snowflake. They enable you to query data stored in files in an external stage as if it were inside a database by storing the file-level metadata.
Type of Snowflake edition?
There are four types of snowflake editions available.
1-Standard Edition
2-Enterprise Edition
3-Business Critical Edition
4-Virtual Private Snowflake (VPS)
What types of stage tables are available in Snowflake?
Snowflake supports two different types of data stages: external stages and internal stages. An external stage is used to move data from external sources, such as (S3, Azure, or GCS), buckets, to internal Snowflake tables. On the other hand, an internal stage is used as an intermediate storage location for data files before they are loaded into a table or after they are unloaded from a table.
internal stage:-(I)User stage
(II) Table Stage
(III) Named Stage
What is the data retention time in Snowflake?
The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts: For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day) at the account and object level (i.e. databases, schemas, and tables).
Can you explain the concept of a snowflake three-layer architecture?
Database Storage:
When data is loaded into Snowflake, Snowflake reorganizes that data into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage. Snowflake manages all aspects of how this data is stored — the organization, file size, structure, compression, metadata, statistics, and other aspects of data storage are handled by Snowflake.
Query Processing:
Query execution is performed in the processing layer. Snowflake processes queries using “virtual warehouses”. Each virtual warehouse is an MPP compute cluster composed of multiple compute nodes allocated by Snowflake from a cloud provider. Each virtual warehouse is an independent compute cluster that does not share compute resources with other virtual warehouses. As a result, each virtual warehouse has no impact on the performance of other virtual warehouses.
Cloud Services:
The cloud services layer is a collection of services that coordinate activities across Snowflake.
Services managed in this layer include:
1-Authentication
2-Infrastructure management
3-Metadata management
4-Query parsing and optimization
5-Access control
What is a clone in Snowflake or what is a zero-copy clone in Snowflake?
The most powerful feature of Zero Copy Cloning is that the cloned and original objects(Table, schema, database) are independent of each other, any changes done on either of the objects do not impact others. Until you make any changes, the cloned object shares the same storage as the original. This can be quite useful for quickly producing backups that don’t cost anything extra until the copied object is changed.
Can you explain what time travel means in Snowflake?
Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period.
Can you explain the concept of fail-safe in Snowflake?
Fail-safe protects historical data in case there is a system failure or any other failure. Fail-safe allows 7 days in which your historical data can be recovered by Snowflake and it begins after the Time Travel retention period ends. Snowflake support team handles this issue.
How to read data from staging table in JSON file.
We are able to read JSON files from the stage layer using the function lateral flatten.FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view (i.e. an inline view that contains correlation referring to other tables that precede it in the FROM clause). FLATTEN can be used to convert semi-structured data to a relational representation
I will update you shortly on your other question………
