21. What are the Access Control in Snowflake ?
Snowflake provides granular control over access to objects — who can access what objects, what operations can be performed on those objects, and who can create or alter access control policies. The privileges that are available in the Snowflake access control are
All Privileges (Alphabetical)
Global Privileges
User and Role Privileges
Resource Monitor Privileges
Virtual Warehouse Privileges
Integration Privileges
Data Exchange Privileges
Data Exchange Listing Privileges
Database Privileges
Schema Privileges
Table Privileges
External Table Privileges
View Privileges
Stage Privileges
File Format Privileges
Pipe Privileges
Stream Privileges
Task Privileges
Masking Policy Privileges
Sequence Privileges
Stored Procedure Privileges
User-Defined Function (UDF) and External Function Privileges
22. How to Designate Additional Users as Account Administrators ?
By default, each account has one user who has been designated as an account administrator (i.e. user granted the system-defined ACCOUNTADMIN role).
grant role accountadmin, sysadmin to user user2;
alter user user2 set email=’user2@domain.com’, default_role=sysadmin;
23 . What is Bulk Unloading Process in snowflake ?
The process for unloading data into files is the same as the loading process, except in reverse:
Step 1
Use the COPY INTO <location> command to copy the data from the Snowflake database table into one or more files in a Snowflake or external stage.
Step 2
Download the file from the stage:
a. From a Snowflake stage, use the GET command to download the data file(s).
b. From S3, use the interfaces/tools provided by Amazon S3 to get the data file(s).
c. From Azure, use the interfaces/tools provided by Microsoft Azure to get the data file(s).
24. What is a stage in Snowflake ?
A stage specifies where data files are stored (i.e. “staged”) so that the data in the files can be loaded into a table.
25. What is User Stages in snowflake ?
Each user has a Snowflake stage allocated to them by default for storing files. This stage is a convenient option if your files will only be accessed by a single user, but need to be copied into multiple tables.
User stages have the following characteristics and limitations:
User stages are referenced using @~; e.g. use LIST @~ to list the files in a user stage.
Unlike named stages, user stages cannot be altered or dropped.
User stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.
26. What is Table Stages in snowflake ?
Each table has a Snowflake stage allocated to it by default for storing files. This stage is a convenient option if your files need to be accessible to multiple users and only need to be copied into a single table. Stage is not a separate database object; rather, it is an implicit stage tied to the table itself. A table stage has no grantable privileges of its own.
Table stages have the same name as the table.
Unlike named stages, table stages cannot be altered or dropped.
Table stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.
Table stages do not support transforming data while loading it.
27. What is Internal stage and External stage
Internal stage : Stores data files internally within Snowflake. Internal stages can be either permanent or temporary. For more details, see Choosing a Stage for Local Files.
External stage : References data files stored in a location outside of Snowflake. Currently, the following cloud storage services are supported:
Amazon S3 buckets
Google Cloud Storage buckets
Microsoft Azure containers
The storage location can be either private/protected or public.
28. What are the Data security in Snowflake ?
All data automatically encrypted (using AES 256 strong encryption).
All files stored in stages (for data loading/unloading) automatically encrypted (using either AES 128 standard or 256 strong encryption).
Periodic rekeying of encrypted data.
Support for encrypting data using customer-managed keys.
Support for masking column data in tables and views using Column-level Security.
29. What are the benefits of integrating Apache Hive Metastores with Snowflake ?
We have Hive metastore connector for Snowflake to integrate Apache Hivemetastores with Snowflake using external tables. The connector detects metastore events and transmits them to Snowflake to keep the external tables synchronized with the Hive metastore. This allows users to manage their schema in Hive while querying it from Snowflake.The connector supports the following types of Hive tables: External and managed tables and Partitioned and unpartitioned tables.
30. What are User-defined functions in Snwoflake ?
The User-defined functions (UDFs) let you extend the system to perform operations that are not available through the built-in, system-defined functions provided by Snowflake. Snowflake currently supports two types of UDFs, SQL and JavaScript
A SQL UDF evaluates an arbitrary SQL expression and returns either scalar or tabular results.
A JavaScript UDF lets you use the JavaScript programming language to manipulate data and return either scalar or tabular results.
31. What are the 3 main components of Snowflake
Database Storage – The actual underlying file system in Snowflake is backed by S3 in Snowflake’s account, all data is encrypted, compressed, and distributed to optimize performance.
Query Processing – Snowflake provides the ability to create “Virtual Warehouses” which are basically compute clusters in EC2 that are provisioned behind the scenes. Virtual Warehouses can be used to load data or run queries and are capable of doing both of these tasks concurrently.
Cloud Services – Coordinates and handles all other services in Snowflake including sessions, authentication, SQL compilation, encryption, etc.
32. What is virtual warehouse in snowflake?
A virtual warehouse, often referred to simply as a “warehouse”, is a cluster of compute resources in Snowflake. A warehouse provides the required resources, such as CPU, memory, and temporary storage, to perform all DML operations
33. Can I resized the Warehouses of snowflake on runtime ?
Warehouses can be started and stopped at any time. They can also be resized at any time, even while running, to accommodate the need for more or less compute resources, based on the type of operations being performed by the warehouse.
34. Will Snowflake automatically suspends the warehouse ?
A warehouse can be set to automatically resume or suspend, based on activity. By default, auto-suspend is enabled. Snowflake automatically suspends the warehouse if it is inactive for the specified period of time. By default, auto-resume is enabled. Snowflake automatically resumes the warehouse when any statement that requires a warehouse is submitted and the warehouse is the current warehouse for the session.
35. What is a Multi-cluster Warehouse ?
With multi-cluster warehouses, Snowflake supports allocating, either statically or dynamically, a larger pool of resources to each warehouse. A multi-cluster warehouse is defined by specifying the following properties:
Maximum number of server clusters, greater than 1 (up to 10).
Minimum number of server clusters, equal to or less than the maximum (up to 10).
Additionally, multi-cluster warehouses support all the same properties and actions as single-cluster warehouses, including:
Specifying a warehouse size.
Resizing a warehouse at any time.
Auto-suspending a running warehouse due to inactivity; note that this does not apply to individual clusters, but rather the entire warehouse.
Auto-resuming a suspended warehouse when new queries are submitted.
By default, a virtual warehouse consists of a single cluster of servers that determines the total resources available to the warehouse for executing queries.
36. What is Snowsight in Snowflake ?
Snowsight is the SQL Worksheets replacement, is designed to support data analyst activities. Snowsight is available in the new Snowflake web interface.
37. What are the binary formats that is supported by snowflake ?
Snowflake supports three binary formats or encoding schemes: hex, base64, and UTF-8.
The “hex” format refers to the hexadecimal, or base 16, system. In this format, each byte is represented by two characters (digits from 0 to 9 and letters from A to F). When using hex to perform conversion:
The “base64” format encodes binary data (or string data) as printable ASCII characters (letters, digits, and punctuation marks or mathematical operators). (The base64 encoding scheme is defined in RFC 4648.)
The UTF-8 format refers to the UTF-8 character encoding for Unicode. Unlike hex and base64, which are binary-to-text encodings, UTF-8 is a text-to-binary encoding. This means that conversion from string to binary always succeeds, but conversion from binary to string can fail.
38. How to get the DDL of Snowflake Table ?
Query the GET_DDL function to retrieve a DDL statement that could be executed to recreate the specified table. The statement includes the constraints currently set on a table.
select get_ddl(‘table’, ‘mydb.public.salesorders’);
39. How to retrieve list of all table constraints by schema in snowflake ?
You can retrieve a list of all table constraints by schema (or across all schemas in a database) by querying the TABLE_CONSTRAINTS View view in the Information Schema.
For example:
select table_name, constraint_type, constraint_name from mydb.information_schema.table_constraints where constraint_schema = ‘PUBLIC’ order by table_name;
40. When to specify column lengths in snowflake table ?
Snowflake compresses column data effectively; therefore, creating columns larger than necessary has minimal impact on the size of data tables. Likewise, there is no query performance difference between a column with a maximum length declaration (e.g. VARCHAR(16777216)), and a smaller precision. However, when the size of your column data is predictable, we do recommend defining an appropriate column length, for the following reasons:
Data loading operations are more likely to detect issues such as columns loa ed out of order, e.g. a 50-character string loaded erroneously into a VARCHAR(10) column. Such issues produce errors. When the column length is unspecified, some third-party tools may anticipate consuming the maximum size value, which can translate into increased client-side memory usage or unusual behavior.
41. What are the ways to query hierarchical data in snowflake ?
Snowflake provides two ways to query hierarchical data in which the number of levels is not known in advance:
(a) Recursive CTEs (common table expressions).
(b) CONNECT BY clauses.
A recursive CTE allows you to create a WITH clause that can refer to itself. This lets you iterate through each level of your hierarchy and accumulate results.
A CONNECT BY clause allows you to create a type of JOIN operation that processes the hierarchy one level at a time, and allows each level to refer to data in the prior level.
42. Explain the System Functions in snowflake
Complete list of System Functions in snowflake
43. Explain the Table Functions in snowflake
Complete list of Table Functions in snowflake
44. Explain the Aggregate Functions in snowflake
Complete list of Aggregate Functions in snowflake
45. Explain the Scalar Functions in snowflake
Complete list of Scalar Functions in snowflake
