Explanation

The benefits of Snowflake’s approach to partitioning table data include:

· In contrast to traditional static partitioning, Snowflake micro-partitions are derived automatically; they don’t need to be explicitly defined up-front or maintained by users.

· As the name suggests, micro-partitions are small in size (50 to 500 MB, before compression), which enables extremely efficient DML and fine-grained pruning for faster queries.

· Micro-partitions can overlap in their range of values, which, combined with their uniformly small size, helps prevent skew.

· Columns are stored independently within micro-partitions, often referred to as columnar storage. This enables efficient scanning of individual columns; only the columns referenced by a query are scanned.

· Columns are also compressed individually within micro-partitions. Snowflake automatically de-termines the most efficient compression algorithm for the columns in each micro-partition.

Explanation

A user with the ACCOUNTADMIN role can also set the MIN_DATA_RETENTION_TIME_IN_DAYS at the account level. This parameter setting enforc-es a minimum data retention period for databases, schemas, and tables. Setting MIN_DATA_RETENTION_TIME_IN_DAYS does not alter or replace the DA-TA_RETENTION_TIME_IN_DAYS parameter value. It may, however, change the effective data retention period for objects. When MIN_DATA_RETENTION_TIME_IN_DAYS is set at the ac-count level, the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).

Explanation

Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake ac-counts hosted on Amazon Web Services (AWS) only.

Rest is correct statements.

Explanation

You cannot use the Snowflake web interface to create users with no passwords or remove pass-words from existing users. You must use CREATE USER or ALTER USER command instead.

Also If you create a user with no password (or alter an existing user and remove their password), this effectively disables Snowflake authentication for the user. Without a password in Snowflake, a user cannot log in using Snowflake authentication and must use federated authentication instead.

Explanation

Regular views do not cache data, and therefore cannot improve performance by caching.

Explanation

SYSTEM$CLUSTERING_DEPTH:

Computes the average depth of the table according to the specified columns (or the clustering key defined for the table). The average depth of a populated table (i.e. a table containing data) is always 1 or more. The smaller the average depth, the better clustered the table is with regards to the speci-fied columns.

Calculate the clustering depth for a table using two columns in the table:

SELECT SYSTEM$CLUSTERING_DEPTH('TPCH_PRODUCT', '(C2, C9)');

SYSTEM$CLUSTERING_INFORMATION:

Returns clustering information, including average clustering depth, for a table based on one or more columns in the table.

SELECT SYSTEM$CLUSTERING_INFORMATION('SAMPLE_TABLE', '(col1, col3)');

Explanation

Permissible Load status for the file:

LOAD_IN_PROGRESS: Part of the file has been loaded into the table, but the load process has not completed yet.

LOADED: The entire file has been loaded successfully into the table.

LOAD_FAILED: The file load failed.

PARTIALLY_LOADED: Some rows from this file were loaded successfully, but others were not loaded due to errors. Processing of this file is completed.

Please not the different Response Codes available with their meaning.

200 — Success. Report returned.

400 — Failure. Invalid request due to an invalid format, or limit exceeded.

404 — Failure. pipeName not recognized.

This error code can also be returned if the role used when calling the endpoint does not have suffi-cient privileges. For more information, see Granting Access Privileges.

429 — Failure. Request rate limit exceeded.

500 — Failure. Internal error occurred.

As you could understand from the questions, there is 200 Success response returned, Status in the response body would be LOADED.

Explanation

Query Profile, available through the classic web interface, provides execution details for a query. For the selected query, it provides a graphical representation of the main components of the pro-cessing plan for the query, with statistics for each component, along with details and statistics for the overall query.

Query Profile is a powerful tool for understanding the mechanics of queries. It can be used whenev-er you want or need to know more about the performance or behavior of a particular query. It is de-signed to help you spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities.

Explanation

Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.

To Know more about Compression of Staged Files, please refer the link:

https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#compression-of-staged-files

Explanation

Column-level Security supports using Context Functions in the conditions of the masking policy body to enforce whether a user has authorization to see data. To determine whether a user can see data in a given SQL statement, it is helpful to consider:

Masking policy conditions using CURRENT_ROLE target the role in use for the current session.

Masking policy conditions using INVOKER_ROLE target the executing role in a SQL statement.

Role hierarchy

Determine if a specified role in a masking policy condition (e.g. ANALYST custom role) is a lower privilege role in the CURRENT_ROLE or INVOKER_ROLE role hierarchy. If so, then the role returned by the CURRENT_ROLE or INVOKER_ROLE functions inherits the privileges of the specified role.

Explanation

When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.

To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.

For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:

SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override');

While the stale pipe was paused, if ownership of the pipe was transferred to another role, then re-suming the pipe requires the additional OWNERSHIP_TRANSFER_CHECK_OVERRIDE argu-ment. For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:

SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override, own-ership_transfer_check_override');

Explanation

Materialized views are first-class Database objects & rest of the understandings are true.

Explanation

You can use the following properties for the ALTER USER command to perform these tasks:

MINS_TO_BYPASS_MFA

Specifies the number of minutes to temporarily disable MFA for the user so that they can log in. Af-ter the time passes, MFA is enforced and the user cannot log in without the temporary token gener-ated by the Duo Mobile application.

Explanation

Snowflake uses file loading metadata to prevent reloading the same files (and duplicating data) in a table. Snowpipe prevents loading files with the same name even if they were later modified (i.e. have a different eTag).

The file loading metadata is associated with the pipe object rather than the table. As a result:

· Staged files with the same name as files that were already loaded are ignored, even if they have been modified, e.g. if new rows were added or errors in the file were corrected.

· Truncating the table using the TRUNCATE TABLE command does not delete the Snowpipe file loading metadata.

Explanation

SYSTEM$STREAM_HAS_DATA

Indicates whether a specified stream contains change data capture (CDC) records.

Explanation

If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.

Explanation

A stage specifies where data files are stored (i.e. “staged”) so that the data in the files can be loaded into a table.

Types of Internal Stages

· User Stages

· Table Stages

· Named Stages

By default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded. In addition, you can create named internal stages.

File staging information is required during both steps in the data loading process:

You must specify an internal stage in the PUT command when uploading files to Snowflake.

You must specify the same stage in the COPY INTO

Explanation

LAST_QUERY_ID() Function

Returns the ID of a specified query in the current session. If no query is specified, the most recently executed query is returned.

RESULT_SCAN() Function

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table.

The following example validates a set of files (SFfile.csv.gz) that contain errors. To facilitate analy-sis of the errors, a COPY INTO statement then unloads the problematic records into a text file so they could be analyzed and fixed in the original data files. The statement queries the RESULT_SCAN table.

1.#copy into Snowtable

2.from @SFstage/SFfile.csv.gz

3.validation_mode=return_all_errors;

4.#set qid=last_query_id();

5.#copy into @SFstage/errors/load_errors.txt from (select rejected_record from ta-ble(result_scan($qid)));

Note: Other options are not valid functions.

Explanation

Apache Kafka software uses a publish and subscribe model to write and read streams of records, similar to a message queue or enterprise messaging system. Kafka allows processes to read and write messages asynchronously. A subscriber does not need to be connected directly to a publisher; a pub-lisher can queue a message in Kafka for the subscriber to receive later.

An application publishes messages to a topic, and an application subscribes to a topic to receive those messages. Kafka can process, as well as transmit, messages; however, that is outside the scope of this document. Topics can be divided into partitions to increase scalability.

Kafka Connect is a framework for connecting Kafka with external systems, including databases. A Kafka Connect cluster is a separate cluster from the Kafka cluster. The Kafka Connect cluster sup-ports running and scaling out connectors (components that support reading and/or writing between external systems).

The Kafka connector is designed to run in a Kafka Connect cluster to read data from Kafka topics and write the data into Snowflake tables.

Every Snowflake table loaded by the Kafka connector has a schema consisting of two VARIANT columns:

RECORD_CONTENT. This contains the Kafka message.

RECORD_METADATA. This contains metadata about the message, for example, the topic from which the message was read.

Explanation

UDFs may be scalar or tabular.

A scalar function returns one output row for each input row. The returned row consists of a single column/value.

A tabular function, also called a table function, returns zero, one, or multiple rows for each input row. A tabular UDF is defined by specifying a return clause that contains the TABLE keyword and specifies the names and data types of the columns in the table results. Tabular UDFs are often called UDTFs (user-defined table functions) or table UDFs.

Explanation

Both Option A & B are correct.

For Refreshing External Table Metadata on a Auto Mode, John can use the AUTO_REFRESH pa-rameter properties of External table.When an external table is created, the AUTO_REFRESH pa-rameter is set to TRUE by default.

When an external table is created, the AUTO_REFRESH parameter is set to TRUE by default.

Snowflake recommend that you accept this default value for external tables that reference data files in either Amazon S3 or Microsoft Azure stages.

However, the automatic refresh option is not available currently for external tables that reference Google Cloud Storage stages.

For these external tables, manually refreshing the metadata on a schedule can be useful.

The following example refreshes the metadata for an external table named snowdb.snowschema.snow_ext_table (using ALTER EXTERNAL TABLE … REFRESH) on a schedule.

-- Create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.

1.CREATE TASK snow_ext_table_refresh_task

2.WAREHOUSE=mywh

3.SCHEDULE='5 minutes'

4.AS

5.ALTER EXTERNAL TABLE snowmydb.snowmyschema.snow_ext_table REFRESH;

This option is the best way to transform relational data into semi-structured data using the least amount of operational overhead. The OBJECT_CONSTRUCT function takes a variable number of key-value pairs as arguments and returns a Snowflake object, which is a variant type that can store JSON data. The function can be used to convert each row of relational data into a JSON object with the column names as keys and the column values as values.

The functions that will compute a ‘fingerprint’ over an entire table, query result, or window to quickly detect changes to table contents or query results are:

• HASH_AGG(*): This function computes a hash value over all columns and rows in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire input if no GROUP BY clause is specified.
• HASH_AGG(, ): This function computes a hash value over two expressions in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire input if no GROUP BY clause is specified. The other functions are not correct because:
• HASH (*): This function computes a hash value over all columns in a single row. The function returns one value per row, not one value per table, query result, or window.
• HASH_AGG_COMPARE (): This function compares two hash values computed by HASH_AGG() over two tables or query results and returns true if they are equal or false if they are different. The function does not compute a hash value itself, but rather compares two existing hash values.
• HASH COMPARE(): This function compares two hash values computed by HASH() over two rows and returns true if they are equal or false if they are different. The function does not compute a hash value itself, but rather compares two existing hash values.

This option is the best way to ensure that Snowpipe is performant while minimizing costs. By splitting the files before loading them, the Data Engineer can reduce the size of each file and increase the parallelism of loading. By setting the SIZE_LIMIT option to 250 MB, the Data Engineer can specify the maximum file size that can be loaded bySnowpipe, which can prevent performance degradation or errors due to large files. The other options are not optimal because:

• Increasing the size of the virtual warehouse used by Snowpipe will increase the performance but also increase the costs, as larger warehouses consume more credits per hour.
• Changing the file compression size and increasing the frequency of the Snowpipe loads will not have much impact on performance or costs, as Snowpipe already supports various compression formats and automatically loads files as soon as they are detected in the stage.
• Decreasing the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose will not affect Snowpipe performance or costs, as Snowpipe does not depend on Kinesis Firehose buffer size but rather on its own SIZE_LIMIT option.

The isolation level of Snowflake streams is repeatable read, which means that each transaction sees a consistent snapshot of data that does not change during its execution. Streams use time travel internally to provide this isolation level and ensure that queries on streams return consistent results regardless of concurrent transactions on their source tables.

The stored procedure is defined with a FLOAT return type and a JavaScript language. The body of the stored procedure contains a SQL statement that inserts a row into the log_table with a value of ‘1’ for col1. The body also contains a return statement that returns 1 as a float value. When the stored procedure is called with any VARCHAR parameter, it will execute successfully and insert one record into the log_table and return 1 as a return value. The other options are not correct because:

• The log_table will not be empty after the stored procedure call, as it will contain one record inserted by the SQL statement.
• The stored procedure will not return NULL as a return value, as it has an explicit return statement that returns 1.

The FORCE=TRUE option in the Snowpipe COPY INTO command allows Snowpipe to load files that have already been loaded before, regardless of the metadata. This is the easiest way to reload the same files without modifying them or recreating the pipe.

The error message “function received the wrong number of rows” is caused by the return message not producing the same number of rows that it received. External functions require that the remote service returns exactly one row for each input row that it receives from Snowflake. If the remote service returns more or fewer rows than expected, Snowflake will raise an error and abort the function execution. The other options are not causes of this error message. Option A is incorrect because external functions do support multiple rows as long as they match the input rows. Option B is incorrect because nested arrays are supported in the JSON response as long as they conform to the return type definition of the external function. Option C is incorrect because the JSON returned by the remote service may be constructed correctly but still produce a different number of rows than expected.

The system role that is recommended for a custom role hierarchy to be ultimately assigned to is SECURITYADMIN. This role has the manage grants privilege on all objects in an account, which allows it to grant access privileges to other roles or revoke them as needed. This role can also create or modify custom roles and assign them to users or other roles. By assigning custom roles to SECURITYADMIN, the role hierarchy can be managed centrally and securely. The other options are not recommended system roles for a custom role hierarchy to be ultimately assigned to. Option A is incorrect because ACCOUNTADMIN is the most powerful role in an account, which has full access to all objects and operations. Assigning custom roles to ACCOUNTADMIN can pose a security risk and should be avoided. Option C is incorrect because SYSTEMADMIN is a role that has full access to all objects in the public schema of the account, but not to other schemas or databases. Assigning custom roles to SYSTEMADMIN can limit the scope and flexibility of the role hierarchy. Option D is incorrect because USERADMIN is a role that can manage users and roles in an account, but not grant access privileges to other objects. Assigning custom roles to USERADMIN can prevent the role hierarchy from controlling access to data and resources.

The result cache is enabled at the session level, which means that repeated queries will return cached results if there is no change in the underlying data or session parameters. However, in this case, the result cache is not relevant because the query uses a specific SEED value for sampling, which makes it deterministic. Therefore, both runs will return the same results regardless of caching.

 The roles that can grant access to the CUSTOMER table are the role that owns the schema, the role that owns the database, and the SECURITYADMIN role. These roles have the ownership or the manage grants privilege on the schema or the database level, which allows them to grant access to any object within them. The other options are incorrect because they do not have the necessary privilege to grant access to the CUSTOMER table. Option C is incorrect because the role that owns the customer table cannot grant access to itself or to other roles. Option D is incorrect because the SYSADMIN role does not have the manage grants privilege by default and cannot grant access to objects that it does not own. Option F is incorrect because the USERADMIN role with the manage grants privilege can only grant access to users and roles, not to tables.

The total_constant_partition_count value indicates the number of micro-partitions where the clustering key column has a constant value across all rows in the micro-partition. However, this does not necessarily mean that the table is clustered well on that column, as there could be other micro-partitions where the range of values in that column overlap with each other. This is the case for the orders table, as the clustering depth is 1, which means that every micro-partition overlaps with every other micro-partition on O_ORDERDATE. This indicates that the table is not clustered well on O_ORDERDATE and could benefit from reclustering.

The system function SYSTEM$ABORT_TRANSACTION can be used to abort a detached transaction that was left in an open state due to a session disconnect or termination. The function takes one argument: the transaction ID of the detached transaction. The function will abort the transaction and release any locks held by it. The other options are incorrect because they do not address the issue of a detached transaction. The system function SYSTEM$CANCEL_TRANSACTION can be used to cancel a running transaction, but not a detached one. The LOCK_TIMEOUT parameter can be used to set a timeout period for acquiring locks on resources, but it does not affect existing locks. The TRANSACTION_ABORT_ON_ERROR parameter can be used to control whether a transaction should abort or continue when an error occurs, but it does not affect detached transactions.

Snowflake’s query results caching capabilities allow users to reuse the results of previously executed queries without re-executing them. For this to happen, the following conditions must be met:

• The results must be reused within 24 hours (not 72 hours), which is the default time-to-live (TTL) for cached results.
• The query must be executed using any virtual warehouse (not necessarily the same one), as long as it is in the same region and account as the original query.
• The USED_CACHED_RESULT parameter does not need to be included in the query, as it is enabled by default at the account level. However, it can be disabled or overridden at the session or statement level.
• The table structure contributing to the query result cannot have changed, such as adding or dropping columns, changing data types, or altering constraints.
• The new query must have the same syntax as the previously executed query, including whitespace and case sensitivity.
• The micro-partitions cannot have changed due to changes to other data in the table, such as inserting, updating, deleting, or merging rows.

The table can be recovered by using the undrop database xyz; command. This command will restore the database that was dropped within the last 14 days, along with all its schemas and tables, including the customer table. The data_retention_time_in_days parameter does not affect this command, as it only applies to time travel queries that reference historical data versions of tables or databases. The other options are not valid ways to recover the table. Option B is incorrect because creating a table as select * from xyz.public.ABC at {offset => -6060248} will not work, as this query will try to access a historical data version of the ABC table that does not exist anymore after dropping the database. Option C is incorrect because creating a table clone xyz.public.ABC at {offset => -360024*3} will not work, as this query will try to clone a historical data version of the ABC table that does not exist anymore after dropping the database. Option D is incorrect because creating a Snowflake Support case to restore the database and table from fail-safe will not work, as fail-safe is only available for disaster recovery scenarios and cannot be accessed by customers.

The Snowflake objects that are created automatically when the Kafka connector starts are tables, pipes, and internal stages. The Kafka connector will create one table, one pipe, and oneinternal stage for each Kafka topic that is configured in the connector properties. The table will store the data from the Kafka topic, the pipe will load the data from the stage to the table using COPY statements, and the internal stage will store the files that are produced by the Kafka connector using PUT commands. The other options are not Snowflake objects that are created automatically when the Kafka connector starts. Option B, tasks, are objects that can execute SQL statements on a schedule without requiring a warehouse. Option E, external stages, are objects that can reference locations outside of Snowflake, such as cloud storage services. Option F, materialized views, are objects that can store the precomputed results of a query and refresh them periodically.