Misc Flashcards
How to validate a COPY INTO command before running it?
USE parameter VALIDATION_MODE = RETURN__ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS
Recommended size for Bulk Loading?
10-100mb Compressed
Recommended file size for Snowpipe?
10-100mb Compressed
Cast column employeename to VARCHAR
SELECT employeename::VARCHAR (real casting) SELECT TO_VARCHAR(employeename)
Name the valid context functions?
CURRENT_WORKSHEET()
CURRENT_REGION()
CURRENT_SESSION()
CURRENT_CLIENT()
What is the default compression algorithm Snowflake applies while unloading data?
GZIP
Recommended file size for Parquet loading?
1GB
What transform statements does COPY INTO not support?
FLATTEN
JOIN
GROUP BY
Default Time Travel Retention for Enterprise
1 Day
Change current warehouse of a session?
USE WAREHOUSE
Functions performed by Cloud Services layer?
- Metadata Management
- User Authentication
- Metadata Storage
- Data Security
What type of tech business model is Snowflake?
Cloud Data Platform as a service (SaaS)
What features make micro-partitions immutable within Snowflake?
- Snowflake creates new micro-partitions every time there is a change in data.
- Micro-partitions are editable
Types of stages that do NOT support File Formats
- Internal Table Stage
- Internal User Stage
Where do the compute resources come from for Snowpipe?
Snowflake (managed service)
If Federated Authentication is enabled in your account can you (admins) still maintain user ids and passwords?
Yes, but Snowflake does not recommend this. Instead Snowflake recommends leaving these tasks to your Identity Provider (IdP)
SnowPipe AUTO_INGEST only works with External Stages (True/False)
True, the SnowPipe REST API does work with both Internal/External stages however.
Data Structures Types/Files supported by VARIANT in Snowflake?
- JSON
- Parquet
- XML
- ORC
- Avro
DDL (with examples)
Data Definition Language
Examples:
- CREATE TABLE
- ALTER TABLE
- DROP
- TRUNCATE
Do you need to grant usage on newly created objects within a Share for new objects to be available to Consumers?
Yes
What level of privilege is required to view a Resource Monitor?
MODIFY and or MONITOR
Can tasks be triggered manually?
No
DML (with examples)
Data Manipulation Language
Examples:
- SELECT
- INSERT
- UPDATE
What object is not cloned when its parent schema/database is cloned?
Internal Named Stage; Internal Named Stages cannot be cloned.
Find the credit usage of a warehouse as an ACCOUNTADMIN
Web Interface:
- Interface > Account > Billing & Usage
SQL:
- Query ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
- Query ACCOUNT_USAGE.METERING_HISTORY
ACCOUNT_USAGE.METERING_HISTORY View
A view to return the average credit usage by hour for an account for the last year.
LOAD_UNCERTAIN_FILES param
A parameter within the COPY command, this allows for files whose metadata has expired to be loaded through the copy command.
Be wary, this can lead to duplicates if not managed well.
How often does Snowflake release new features?
Weekly
STRIP_NULL_VALUE function
Replace a “null” value with a SQL NULL value. All else is passed unchanged.
Securable Objects in Snowflake
- Warehouse
- Database
- Table
- File Format
Are permissions granted to Users?
No, only roles.
Ownership or a Role is granted to users, never permissions themselves.
Are Stored Procedures required to return a value?
No
Are User Defined Functions (UDFs) required to return a value?
Yes
Concatenation methods in Snowflake
|| or CONCAT()
How to alter Time Travel retention period?
ALTER TABLE SET DATA_RETENTION_TIME_IN_DAYS = x;
Where are the table Metadata and Cached Results stored in Snowflake
Cloud Services Layer
What type(s) of data are stored in the Snowflake Data Storage layer?
- Data to enable recovery (Time Travel & Fail Safe)
- Persistent data stored in permanent tables (probably transient and temporary as well)
What file formats are supported for UNLOADING data from Snowflake?
- JSON
- Parquet
- CSV/TSV
SQL Comment to list pipes you have access to?
SHOW PIPES()
How far back can you see query history in the History tab?
14 days
Places you can specify a File Format
- Named Stage/Pipe definition
- COPY INTO command
- Table definition
Metadata Retrieval Commands
- SHOW
- LIST
- DESCRIBE
SHOW command
Lists all objects of a certain type within the account or a given database/schema.
Example:
SHOW PROCEDURES;
SHOW USERS;
DESCRIBE command
Retrieves metadata information about a specific object.
Example:
DESCRIBE PROCEDURE SP_UPDATE_LAYER(VARCHAR, VARCHAR, VARCHAR, VARCHAR)
LIST command
Lists files within a Snowflake stage.
LIST @test_stage/snowflake
How to modify Time Travel Retention period:
Have an ACCOUNTADMIN modify the DATA_RETENTION_TIME_IN_DAYS for the default retention period for an account.
Setting DATA_RETENTION_TIME_IN_DAYS to 0 effectively cancels Time Travel on an account/database/etc.
Which objects can be cloned?
- Databases
- Schemas
- Tables
- Stages
- File Formats
- Sequences
- Streams
- Tasks
Are Internal Named Stages clonable?
No, and if you try and clone a table with one, the internal name staged will not be cloned. Likewise, any Snowpipes that are connected to one will also not be cloned.
OLAP meaning
Online Analytical Process
an approach to multi-dimensional analytical queries in computing, useful for business intelligence, reporting, and data mining.
OLTP meaning
Online Transaction Processing
a type of data processing that executes a number of transactions concurrently.
Is Snowflake better suited for OLAP or OLTP?
It is suited extremely well for both.
Is it possible to unload structured data to semi-structured formats (JSON/Parquet)?
Yes
PUT command
Uploads (or stages) data files from a local source on a client machine to an internal snowflake stage.
GET command
Downloads data files from an internal snowflake stage onto a local source machine.
Basically the inverse of the PUT command.
Does PUT support loading files to an EXTERNAL stage?
No, use cloud provider resources for that.
Are staged files in Snowflake compressed and encrypted automatically?
Yes, if the stage is a Snowflake stage (not an external one).
What is the maximum (compressed) row size in Snowflake
16 mb
