Is Snowflake available on-premise? True False.
When you sign up for a SnowFlake trial account, what are the three SnowFlake editions offered? Standard Enterprise Premier Enterprise for Business Intelligence Business Critical .
Which SnowFlake edition supports private communication between Snowflake and your other VPCs through AWS PrivateLink? Standard Enterprise Business Critical .
Which cloud provider is not supported by SnowFlake? AWS Azure IBM Google Cloud Platform .
Does Snowflake automatically stores data in encrypted form in all editions? True False .
In which SnowFlake edition is Tri-Secret Secure option available? Standard or higher. Enterprise or higher. Business Critical or higher. .
Can we use Multi-factor Authentication to connect to Snowflake via the Snowflake JDBC driver? True False.
A client has ODBC or JDBC available in their systems, but they do not have the SnowFlake drivers. Is the client able to connect to SnowFlake? True False.
What is the name of Snowflake’s Command Line Interface tool? SnowCLI SnowSQL SnowTerminal SnowCMD .
The three main layers of SnowFlake are… Extraction, Ingestion, Load Database Storage, Compute & Cloud Services Database, Virtual Warehouse, Data Experience .
Select the term that is associated with the compute layer: Query optimization Query planning Query processing .
Which of the following services are provided by the Cloud Services Layer? Metadata Management Authentication Storage Infrastructure Management Query Execution .
What statement is true about SnowFlake’s unique architecture? One Node Shared Data Multi-Cluster Shared Data One Node Private Data .
What is the storage hierarchy in SnowFlake? Account → DB → Schemas → Objects Account → Schemas → DB → Objects Account → DB → Objects → Schemas .
Can two Virtual Warehouses access the same data simultaneously without any contention issues? True False.
The interactions with data are initialized through the services layer? True False.
In which layer of SnowFlake architecture is stored all security-related information? Storage Compute Cloud Services All of the above. .
Can the table functions in INFORMATION_SCHEMA be used to return account-level usage and historical information for storage, warehouses, user logins, and queries? True False.
What influences SnowFlake pricing? (SELECT ONLY ONE) Amount of data queried from SnowFlake. Amount of data scanned from SnowFlake. SnowFlake pricing is based on usage & storage. .
Compute cost in SnowFlake depends on… The query execution time. The query execution time and the waiting query time. The WareHouse size and how long the WareHouse runs. .
What are the two major cost categories in Snow Flake? (SELECT TWO) Storage. Compute. Cloud Service. Data Transfers. .
How is the data storage cost computed for SnowFlake? Based on the average daily amount of uncompressed data stored. Based on the average daily amount of compressed data stored. Based on the amount of uncompressed data stored on the last day of the month. Based on the amount of compressed data stored on the last day of the month. .
Which type of data incur in SnowFlake storage cost? Data Stored in permanent tables. Data Stored in temporal tables. Cache results. Data retained for Fail-Safe & Time-Travel. .
Tables with Fail-Safe turned on incurs additional storage costs compared to tables where Fail-Safe is turned off? True False.
Does data stored in a temporary table not contribute to SnowFlake’s storage cost? True False.
Which factors influence the unit cost of snowflake credits and data storage? SnowFlake Edition. Region of the SnowFlake account. On-Demand or Pre-Paid account. Users on SnowFlake. .
What technique does SnowFlake use to limit the number of micro-partitions retrieved as part of a query? Pruning Clustering Indexing Computing .
Which statements are correct about micro-partitions in SnowFlake? Contiguous units of storage Non-contiguous units of storage 50 and 500MB of compressed data 50 and 500MB of uncompressed data Organized in a columnar way .
Which options are correct regarding the data that is stored in micro-partition metadata? The range of values for each of the columns in the micro-partition. The number of distinct values. Additional properties are used for both optimization and efficient query processing. .
What techniques would you consider to improve the performance of a query that takes a lot of time to return any result? Define partition keys Create cluster keys & turn auto clustering on the table Create an index on the search result .
Which of the following clustering metadata for the micro-partitions is maintained by SnowFlake in a table? The number of micro-partitions that comprise the table. The number of micro-partitions containing values that overlap with each other. The depth of the overlapping micro-partitions. None of the above. .
Which of the below will you consider while choosing a cluster key Columns that are typically used in the selective filters. Columns are frequently used in join predicates. Columns with extremely high cardinality. Columns with extremely low cardinality. .
Does re-clustering in SnowFlake require manual configuration? True False.
Is re-clustering in SnowFlake only triggered if the table would benefit from the operation? True False.
What can you easily check to see if a large table will benefit from explicitly defining a clustering key? Clustering depth Clustering ratio Values in a table .
Which system functions are available in SnowFlake to view/monitor the clustering metadata for a table? SYSTEM$CLUSTERING_DEPTH SYSTEM$CLUSTERING_INFORMATION SYSTEM$CLUSTERING_METADATA .
Is clustering generally most cost-effective for tables that are queried frequently and do not change often? True False.
Which of the below columns are usually a good choice for clustering keys? UUID column from a Customer in a 10TB table. Gender male/female in a 20TB table. Timestamp in a 10TB table. Store_id in a 2TB table. .
What types of tables are available in SnowFlake? Permanent Temporary Transient External Internal .
What types of views are available in SnowFlake? Regular Secure View Table View Materialized View External View .
We need to temporarily store intermediate data, which will only be used by an ETL process. We don’t need the data outside the ETL process. If you want to optimize storage cost, what type of table will you create to store this data? Permanent Temporary Transient External .
Can you use streams to track changes in materialized view? True False.
In which of the below scenarios will you use an external table? You have data on the cloud providers, but the data cannot be copied or moved to any other location due to compliance regulations. You have a high volume of data on the cloud providers, but we only need a part of the data in SnowFlake. You have data on the cloud providers which needs to be updated by SnowFlake. You have XML data on the cloud provider. .
Select two limitations with materialized views: Time Travel is not supported. We cannot define Cluster Keys. Streams cannot track changes. .
Can temporary tables be created with a clustering key defined? True False.
What are the usual data loading steps in Snowflake? Source → Snowflake Stage → Snowflake table Source → Snowflake Table → Snowflake Stage Snowflake Table → Source → Snowflake Stage .
What key concepts will need to be considered while loading data into Snowflake? Stage File Format Transformation File Size Error validation .
Using COPY INTO <location> command, you can unload data from a table into which locations? Named internal stage (or table/user stage). Named external stage that references an external location (Amazon S3, Google Cloud Storage, or Microsoft Azure). An external location like Amazon S3 or Azure. Local Drive .
After how many days does the load history of the COPY command expire? 1 day 14 days 64 days 180 days .
While loading data through the COPY command, you can transform the data. Which of the below transformations are allowed? Truncate columns Omit columns Filters Reorder columns Cast Aggregate .
What option will you specify to delete the stage files after a successful load into a Snowflake table with the COPY INTO command? DELETE = TRUE REMOVE = TRUE PURGE = TRUE TRUNCATE = TRUE .
In which of the below scenarios is SnowPipe recommended to load data? We have a small volume of frequent data We have a huge volume of data generated as part of a batch schedule In both of the previous scenarios .
Is SnowPipe Serverless? True False.
After how many days does the load history of SnowPipe expire? 1 day 14 days 90 days 180 days .
Can SnowPipe load a file with the same name if it has been modified later? True False.
Does SnowPipe guarantee that files are loaded in the same order they are staged? True False.
Which of the below APIs are SnowPipe REST APIs? insertFiles insertReport insertHistoryScan loadFiles loadHistoryScan .
Which data-loading method requires a user-specified warehouse to execute COPY statements? Bulk Data Load SnowPipe Both .
Which of the following commands cannot be executed from the SnowFlake UI? SHOW LIST <stages> GET COPY INTO PUT .
Which command will we use to download the files from the stage/location loaded through the COPY INTO <LOCATION> command? GET PUT UNLOAD INSERT INTO .
Does GET support downloading files from external stages? True False.
Can you load data using the PUT command through worksheets in the SnowFlake UI? True False.
When data is staged to a Snowflake internal staging area using the PUT command, is the data encrypted on the client’s machine? True False.
Can Resource Monitors be replicated? True False.
Can a Warehouse be assigned to more than one Resource Monitor? True False.
For which activities does Snowflake have administration settings to help for resource consumption? Help control costs associated with unexpected warehouse credit usage Manage access to SnowFlake for specific users Manage the availability of the product .
What can the resource monitor associated with a Warehouse do when it reaches (or about to) hit the limit? Suspend the WareHouse Send notification alert Kill the query that is running Delete the SnowFlake account .
What is the name of the property from the Resource Monitors that let you specify whether you want to control the credit usage of your entire account or a specific set of warehouses? Credit Quota Monitor Level Schedule Notification .
What actions can a Resource Monitor do when it hits the limit? Notify & Suspend Notify & Suspend Immediately Notify Notify & Increase the limit .
Which properties of a Resource Monitor can be modified? Credit Quota Monitor Level Schedule Actions .
What are the different caching mechanisms available in SnowFlake? Metadata cache Query result cache Index cache Table cache Warehouse cache .
A query executed a couple of hours ago, which spent more than 5 minutes to run, is executed again, and it returned the results in less than a second. What might have happened? SnowFlake used the persisted query results from the metadata cache SnowFlake used the persisted query results from the warehouse cache A new SnowFlake version has been released in the last two hours, improving the speed of the service .
Are Snowflake caches automatically invalidated if the underlying data changes? True False.
What command will you execute if you want to disable the query cache? ALTER SESSION SET USE_CACHED_RESULT = TRUE ALTER SESSION SET USE_CACHED_RESULT = FALSE ALTER SESSION SET USE_CACHED_RESULT = ON ALTER SESSION SET USE_CACHED_RESULT = OFF.
Which type of data incur in SnowFlake storage cost? Data Stored in permanent tables. Data Stored in temporal tables. Cache results. Data retained for Fail-Safe & Time-Travel. .
Which cache runs for 24 hours? Metadata cache Results cache Warehouse cache .
May the warehouse cache be reset if a running warehouse is suspended and resumes? True False.
Does the warehouse cache size change with the warehouse-size? True False.
To improve the performance, which of the below techniques can be used in SnowFlake? Cluster Keys Multi-Warehouses Maximize the cache use Increasing the Warehouse Size Dedicated Warehouses.
What type of data incur in SnowFlake Storage costs? Data stored in permanent tables
Fail-Safe data Zero-copy cloning data Time travel data Data stored in cloud storage like AWS Data stored in temporary tables .
What should be the first option to restore data into a table? Time-Travel Fail-Safe Zero-Copy Cloning .
Which SnowFlake edition allows only one day of Time Travel? Standard Enterprise Business Critical .
Which SnowFlake edition (and above) allows until 90 days of Time Travel? Standard Enterprise Business Critical .
Do you need to contact Snowflake Support to retrieve data from Fail-Safe? True False.
Is Fail-Safe period for temporary and transient tables 0? True False.
Select the statements that are true about Fail-Safe: There is no difference between Time-Travel and Fail-Safe The data retention period for a permanent table with 30 days of Time-Travel is 37 days Fail-Safe provides a non-configurable 7-day period Fail-Safe provides a non-configurable 90-day period Fail-Safe ensures that historical data is protected in the event of a system failure or other catastrophic events .
A point-in-time snapshot of data that can be updated by users is called… Time-Travel Fail-Safe Zero-Copy Cloning .
Which service does SnowFlake use to provide the Zero-Copy cloning functionality? SSD Cache of the Virtual Warehouses Cache Metadata from the service layer.
What would happen if we executed the following command?
CREATE OR REPLACE TABLE newTable CLONE table1; “newTable” is created with all the data from “table1” “newTable” is created, and SnowFlake internally executes a batch job to copy all the data from “table1” “newTable” is created, and SnowFlake internally executes a pipe to copy all the data from “table1” SnowFlake creates a new entry in the metadata store to keep track of the new clone. The existing micro-partitions of “table1” are mapped to the new table. .
Which answers are true about roles in SnowFlake? SnowFlake users have a limit on the number of roles that they can assume SnowFlake users can have one or more roles Only a role can be active for a particular session Privileges can be directly assigned to users .
Which of the following object types are stored within a schema? Tables Views File Formats Roles .
Which of the following roles are the default ones in SnowFlake? ACCOUNTADMIN SECURITYADMIN
VIEWER USERADMIN SYSADMIN NETWORKADMIN.
Which command will you run to list all users and roles to which a role has been granted? SHOW GRANTS TO ROLE <ROLE> SHOW GRANTS OF ROLE <ROLE> SHOW GRANTS IN ROLE <ROLE> .
Which roles can create, alter or drop network policies? ACCOUNTADMIN SECURITYADMIN SYSADMIN USERADMIN .
Which roles can create shares and resource monitors? ACCOUNTADMIN SECURITYADMIN SYSADMIN USERADMIN .
Can worksheets of the SnowFlake UI have a different role, warehouse, and database? True False.
What is the minimum SnowFlake edition that you need for the Data Sharing capability? Standard Enterprise Business Critical .
Which database objects can be shared using SnowFlake Secure Data Sharing? Tables External tables Secure views Secure materialized views Secure UDFs Users .
How can a producer share a table with a consumer located in a different region? This is not a problem; producers and consumers can be in different regions Replicate your account to another region and create a share from that region. Create a script to replicate your data in the consumer account. .
Which SnowFlake role can set up a SnowFlake Share? SECURITYADMIN SYSADMIN DATASHARINGADMIN ACCOUNTADMIN PUBLIC .
What are the two types of data consumer accounts available in SnowFlake? Shared Account Reader Account Public Account Full Account .
Is shared data always immediately available to Consumer Accounts? True False.
Which SnowFlake object returns a set of rows instead of a single, scalar value, and can be accessed in the FROM clause of a query? UDF UDTF Stored procedure .
Do UDFs support both SQL & JavaScript in SnowFlake? True Falsde.
Are UDFs, UDTFs, and Stored Procedures account or schema level objects in SnowFlake? Schema level Account-level .
What object will you use to schedule a merge statement in SnowFlake so that it runs every hour? Task Stream Pipe Table .
Select the true statements about SnowFlake tasks: A task can execute a single SQL Statement A task can execute multiple SQL Statements
A task can execute a call to a Stored Procedure A task can execute a function .
A task is still being executed before the next scheduled task. What is going to happen with the new scheduled task? SnowFlake will abort it SnowFlake will skip it SnowFlake will wait for the previous task to complete
.
Which roles or privileges are required to view TASK_HISTORY? AccountAdmin MONITOR EXECUTION privilege SysAdmin SecurityAdmin Task owner (OWNERSHIP privilege) .
What will happen to the child task if you remove its predecessor? The child task is removed from the system The child task may become the root task The child task may become a standalone task .
A task went into a loop. How long will the task run before SnowFlake finishes it? 15 minutes
30 minutes 60 minutes 4 hours .
The owner of a task (the one who has the OWNERSHIP privilege) is deleted. What will happen to the task? The task will belong to the role that dropped the owner’s role The task is deleted
The task is suspended.
If a transaction disconnects and goes into a detached state, which cannot be committed or rolled back, how long will SnowFlake take to abort the transaction? 15 minutes 60 minutes 4 hours 12 hours .
Does SnowFlake support Nested Transactions? True False.
Which object in SnowFlake stores DML change made to tables and metadata about each change? Tables Pipes Table Streams Account Streams.
What statements are true about streams? A Stream itself does NOT contain any table data A stream only stores the offset for the source table The hidden columns used by a stream consume storage .
Which are the additional columns that the streams create? METADATA$ACTION METADATA$ISREAD METADATA$ISUPDATE METADATA$ROW_ID METADATA$COLUMN_ID.
What different types of streams exist in SnowFlake? Standard Append-only Update-only Insert-only.
Do streams ensure exactly-once semantics for new or changed data in a table? True False.
Are Insert-only streams only supported on external tables? True False.
A stream has been created on a table. A row is inserted in the table, and it’s updated later. Will the stream capture both events? True False.
Can we use streams in materialized views? True False.
Can multiple streams be created for the same table? True False.
Does SnowFlake allow only the load of structure data? True False.
Which of the following file formats are supported by SnowFlake? CSV XML TXT AVRO.
How will you store JSON data in SnowFlake? Using a column of the JSON type Using a column of the VARCHAR type Using a column of the VARIANT type Using a column of the NULL type .
Which of the following object types are stored within a schema? Tables Views File Formats UDFs Roles Users Sequences .
You have the following data in a variant column from the table “myTable”. How can you query the favorite technology that Gonzalo uses?
{
"name": "Gonzalo",
"country": "Spain",
"favouriteTechnology": SnowFlake,
"hobbies": [
{
"name": "soccer",
"since": "2000",
},{
"name": "music",
"since": "2005",
},{
"name": "technology",
"since": "1996",
}
]
} SELECT favouriteTechnology FROM myTable; SELECT src:favouriteTechnology FROM myTable; SELECT src:$favouriteTechnology FROM myTable; SELECT CONVERT_JSON(src:favouriteTechnology) FROM myTable; .
Which table function allows you to convert semi-structured data to a relational representation? FLATTEN CHECK_JSON PARSE_JSON .
What file format provides the fastest load performance? Parquet JSON Avro CSV .
Which certifications are compliant with SnowFlake? HIPAA PCI-DSS FedRAMP .
Can Snowflake carry out transformations after loading files staged by partner software (ELT)? True False.
Column level security in Snowflake allows the application of a masking policy to a column within a table or view. Which two features are related to column-level security? Dynamic Data Masking Lock Databases External Tokenization .
What is common between Fivetran, Informatica, Stitch, and Talend? They are SnowFlake programming interfaces partners They are SnowFlake data integration partners They are SnowFlake security partners .
What statements are true about streams? A Stream itself does NOT contain any table data A stream only stores the offset for the source table The hidden columns used by a stream consume storage .
|
