Practice Test 1 Flashcards by Annie Weinmann

Which term is used to describe Snowflake’s architecture?

Multi-cluster, shared data warehouse

How well did you know this?

Not at all

Perfectly

What appears on the top bar of the Snowflake UI?

Databases

Warehouses

Tables

Worksheets

Account (for admins)

History

Partner connect, help, notifications (top right side)

Drop down menu for user (top right side)

Databases

Warehouses

Worksheets

Account (for admins)

History

Partner connect, help, notifications (top right side)

Drop down menu for user (top right side)

How well did you know this?

Not at all

Perfectly

What are the Snowflake editions?

Standard, Premier, Enterprise, Enterprise for Sensitive Data (ESD), and Virtual Private Snowflake (VPS)

How well did you know this?

Not at all

Perfectly

What’s the factor when you increase the t-shirt size of warehouse? 2, 4, 6, or 8?

How well did you know this?

Not at all

Perfectly

Snowflake is designed for which type of workloads? (select all that apply)

OLAP (analytics) workloads

OLTP (transactional) workloads

Concurrent workloads

On-premises workloads

OLAP

Concurrent

How well did you know this?

Not at all

Perfectly

Which of the following are Cloud-native solutions? Select all that apply.

Google Big Query

Snowflake

Both

both

How well did you know this?

Not at all

Perfectly

What are the three things customers want most from their enterprise data warehouse solution?

Concurrency

Query Performance

Simplicity

How well did you know this?

Not at all

Perfectly

A Snowflake deployment runs in which environment?

Region

How well did you know this?

Not at all

Perfectly

Does AWS Private Link allow customers to connect their on prem VPC ?

YES

How well did you know this?

Not at all

Perfectly

Which of the following can you clone?

User

Table

Schema

Databases

Views

Table
Schema
Database

How well did you know this?

Not at all

Perfectly

True or false: The Query Profiler view is only available for completed queries.

False

How well did you know this?

Not at all

Perfectly

What are some of the items the services layer in Snowflake manages? Select all that apply

User authentication

Metadata

Query Compilation and Optimization

Data Security

All

How well did you know this?

Not at all

Perfectly

What types of data does Snowflake support natively? Select all that apply.

Structured, relational data

Semi-structured data such as JSON and AVRO

All

How well did you know this?

Not at all

Perfectly

Where is metadata stored?

Global services layer

Virtual warehouse layer

Data storage layer

Customer’s local place

Global services layer

How well did you know this?

Not at all

Perfectly

True or False: A micropartition will be updated as updates are made to the rows of the data it contains

False

How well did you know this?

Not at all

Perfectly

True/False: When a clone of a table is created, the original table’s data is physically copied?

False

How well did you know this?

Not at all

Perfectly

True/False: Fail safe is configurable.

False

How well did you know this?

Not at all

Perfectly

Do all tables have fail safe?

How well did you know this?

Not at all

Perfectly

Which features are direct results of Snowflake’s storage management approach? Select all that apply.

Time Travel

Zero-copy cloning

Fail safe

Time Travel

Zero Copy Cloning

How well did you know this?

Not at all

Perfectly

In order to view the storage usage of all tables in a db, you can use: (Select all that apply)

SHOW TABLES;

SHOW STORAGE BY TABLE;

INFORMATION_SCHEMA.TABLE_STORAGE_METRICS

INFORMATION_SCHEMA.TABLE_HISTORY

show tables;

NFORMATION_SCHEMA.TABLE_STORAGE_METRICS

How well did you know this?

Not at all

Perfectly

True/False: A single schema can exist in more than one database in SF

False

How well did you know this?

Not at all

Perfectly

What’s the maximum row size?

8 MB

16 MB

50 MB

4000 GB

16MB

How well did you know this?

Not at all

Perfectly

True or False: The database storage in Snowflake is split across the nodes like other Shared nothing databases.

True

How well did you know this?

Not at all

Perfectly

True/False: Zero-copy cloning is an additional feature of Snowflake available for Enterprise edition and up

False

How well did you know this?

Not at all

Perfectly

What kind of data format is JSON? Semi-structured Unstructured Structured

semi structured

What methods can be used to create and/or manage Virtual Warehouses?

Snowflake Web Interface SQL commands

When does a warehouse come out of suspended state (2 ways)

When it is auto-resumed When a query is run

What is the default time for auto-suspend?

10 min

Warehouses size up between t-shirt sizes by a factor of what?

Which of the following does SF not support? insert upsert merge

upsert

How many threads does a single Virtual Machine within a Compute Cluster contain?

True/False: The user has to specify which cluster a query will run on in a multi-cluster warehouse.

False

When scaling up virtual warehouses by increasing virtual warehouse t-shirt size, you are primary scaling for improved: Performance Concurrency Simplicity On-Prem accessibility

performance

True/False: The minimum time that a warehouse is charged is 5 mins.

False

True or false: You can resize a Virtual Warehouse while queries are running.

True

True or false: It is a best practice in Snowflake to break large tables into partitions to improve query performance.

False

Why would you scale a Virtual Warehouse UP? Complex queries Large datasets More workloads

Complex Queries | Large Datasets

Why would you scale a Virtual Warehouse OUT? Handle concurrency Handle complex query

Concurrency

Which of the following commands could run without a warehouse running, assuming no previous queries have been run? Check all that apply: sum(<>) min(<>) Select * from table copy avg(<>)

min(col name)

Whats the command for creating Virtual Warehouse CREATE VIRTUAL_WAREHOUSE CREATE WAREHOUSE

create warehouse

True or False: It is possible to query data stored in Snowflake without a Virtual Warehouse.

True

True or false: You need an active warehouse to run a basic insert statement.

True

Name the 3 serverless functions in SF (no need for virtual warehouse)

auto-clustering (pay only when it wakes up ) snowpipe (pay for the compute used to execute COPY INTO) materialized views

Select all the editions In which you can use multi-cluster warehouses? Standard Premium Enterprise Enterprise for Sensitive Data (EDS)/Business Critical VPS

Enterprise and higher.

What is the optimal size for loading files into Snowflake?

10-100mb compressed

You need to specify the file format when loading data into Snowflake

False

What is the maximum row size (compressed) in Snowflake?

16mb

How is Snowpipe charged?

per second/per core granularity

What command do you use to put files from a local file system into an internal stage?

PUT

True/False: You can use the PUT command in the UI

False

Snowflake supports which of the following file formats for data unloading? CSV/TSV Json Parquet

all

Which of the following are supported by the COPY statement? Joins Aggregates Concatenate Ordering Filtering

concatenate | ordering

Which edition(s) of Snowflake provide up to 90 days of time travel?

Enterprise, ESD, VPS

How many days of time travel do you get for Standard and Premier editions?

1 day

Snowflake supports multi-factor authentication (MFA) to provide increased login security for users connecting to Snowflake. Which statements are true about MFA security? MFA is automatically enabled for your account and available for all users to self-enroll MFA is an integrated feature powered by the Duo Security Service MFA can be used for connecting to Snowflake via the Snowflake JDBC driver

MFA is an integrated feature powered by the Duo Security Service

Which connectors support multi-factor authentication?

Pretty much all of them: UI, JDBC, ODBC, SnowSQL

true or false: Snowflake charges a premium for storing semi-structured data?

false

What column type is used to store semi-structured data?

variant

What type of data would you use the FLATTEN function for? Structured data Semi-structured data Both of the above None of the above

semi structured

T/F STRIP_NULL_VALUE converts a JSON “NULL” value to a SQL Null value?

True

What are the two ways to create and manage shares in Snowflake

Use SQL commands Via Snowflake Web UI

How many shares can a data provider provide?

unlimited

Who pays for the storage of the data being shared?

data provider

Who pays the compute cost for queries run on a data share?

Data consumer, data provider pays compute cost for reader accounts

Data sharing is available starting in which edition?

standard

True/False: You can clone a data share in Snowflake.

false

What happens when data providers revoke privileges on a share on an object?

it's unavailable

What are the four default roles in Snowflake?

ACCOUNTADMIN, SYSADMIN, SECURITYADMIN, PUBLIC

True/False: A single database can exist in more than one Snowflake account.

False

Which of the following contributes to an account’s compute cost? Warehouse size The number of clusters The amount of data queried

Warehouse size The number of clusters

What can monitor account-level credit and storage usage

Resource Monitor

Identify concerns about legacy data sharing. Date volume Data security Data governance

All

Which role should be in charge of managing database objects? SYSADMIN ACCOUNT ADMIN SECURITY ADMIN PUBLIC

Sysadmin

True/False: Roles are a subset of users. Users own objects which allows the role to access those objects.

False

What does snowflake recommend for best practices for the accountadmin/sysadmin roles? Check all that apply (not exact answers, but something along these lines) Make default role sysadmin (if a user has both) Create all objects using the accountadmin role (definitely don’t check this one) Use the sysadmin role for general administration of tasks Only enter the accountadmin role to perform tasks for this role, then leave the role once you’ve finished those tasks.

Make default role sysadmin (if a user has both) Only enter the accountadmin role to perform tasks for this role, then leave the role once you’ve finished those tasks.

Select all that apply: Which of the following are blocking operators? MERGE UPDATE INSERT COPY

merge | update

True/False: When a clone of a table is created, the original table’s data is physically copied?

false

Would scaling up (increasing Warehouse size) or out (adding more Cluster to the Warehouse) help a complex query perform better?

What technique does Snowflake use to limit the number of micro-partitions scanned by each query?

clustering

True/False It’s best practice to have a clustering key in every table?

false

True/False: All tables in SF are clustered.

false

You can use Snowflake on the following cloud services (select all that apply): Amazon Web Services Microsoft Azure Google Cloud Provider (in preview)

all

What can you download through UI? SnowSQL ODBC Driver

all

Snowflake's query language is based on:

sql

Which of the following objects can not be cloned Table User Share Stage View File format

user share view

The fact that micro-partition is immutable support which of the following feature Zero-copy cloning Time travel Fail safe Clustering

zero copy cloning | time travel

How much uncompressed data is in a micro-partition?

50-500mb

What is the most used connector?

jdbc

What is the best place to find information on connectors and tool integration?

snowflake docs

What is the name of the feature that allows you to analyze a visual representation of the query execution?

query profiler

Shared databases are read-only in Snowflake?

true

True/False: Query the files in an external stages directly without loading?

false

Which Snowflake functions can be run in Snowsql but not the Web UI? GET PUT COPY

get | put

Which Snowflake layer has caching? Global Service Virtual Warehouse Storage

all

True or False: Snowflake can be deployed in a hybrid cloud or private cloud environment?

false

Which Snowflake role is strongly recommended to enable MFA? SYSADMIN ACCTADMIN SECURITYADMIN Both SYSADMIN and ACCTADMIN

acctadmin

We can load file through Snowpipe without using a stage

false