Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Snowflake Cert Studying > Misc > Flashcards

Misc Flashcards

1
Q

How to validate a COPY INTO command before running it?

A

USE parameter VALIDATION_MODE = RETURN__ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Recommended size for Bulk Loading?

A

10-100mb Compressed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Recommended file size for Snowpipe?

A

10-100mb Compressed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Cast column employeename to VARCHAR

A 
SELECT employeename::VARCHAR (real casting)
SELECT TO_VARCHAR(employeename)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Name the valid context functions?

A

CURRENT_WORKSHEET()
CURRENT_REGION()
CURRENT_SESSION()
CURRENT_CLIENT()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the default compression algorithm Snowflake applies while unloading data?

A

GZIP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Recommended file size for Parquet loading?

A

1GB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What transform statements does COPY INTO not support?

A

FLATTEN
JOIN
GROUP BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Default Time Travel Retention for Enterprise

A

1 Day

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Change current warehouse of a session?

A

USE WAREHOUSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Functions performed by Cloud Services layer?

A
  • Metadata Management
  • User Authentication
  • Metadata Storage
  • Data Security
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What type of tech business model is Snowflake?

A

Cloud Data Platform as a service (SaaS)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What features make micro-partitions immutable within Snowflake?

A
  • Snowflake creates new micro-partitions every time there is a change in data.
  • Micro-partitions are editable
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Types of stages that do NOT support File Formats

A
  • Internal Table Stage

- Internal User Stage

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Where do the compute resources come from for Snowpipe?

A

Snowflake (managed service)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

If Federated Authentication is enabled in your account can you (admins) still maintain user ids and passwords?

A

Yes, but Snowflake does not recommend this. Instead Snowflake recommends leaving these tasks to your Identity Provider (IdP)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

SnowPipe AUTO_INGEST only works with External Stages (True/False)

A

True, the SnowPipe REST API does work with both Internal/External stages however.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Data Structures Types/Files supported by VARIANT in Snowflake?

A
  • JSON
  • Parquet
  • XML
  • ORC
  • Avro
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

DDL (with examples)

A

Data Definition Language

Examples:

  • CREATE TABLE
  • ALTER TABLE
  • DROP
  • TRUNCATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Do you need to grant usage on newly created objects within a Share for new objects to be available to Consumers?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What level of privilege is required to view a Resource Monitor?

A

MODIFY and or MONITOR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Can tasks be triggered manually?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

DML (with examples)

A

Data Manipulation Language

Examples:

  • SELECT
  • INSERT
  • UPDATE
24
Q

What object is not cloned when its parent schema/database is cloned?

A

Internal Named Stage; Internal Named Stages cannot be cloned.

25
Q

Find the credit usage of a warehouse as an ACCOUNTADMIN

A

Web Interface:
- Interface > Account > Billing & Usage

SQL:

  • Query ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  • Query ACCOUNT_USAGE.METERING_HISTORY
26
Q

ACCOUNT_USAGE.METERING_HISTORY View

A

A view to return the average credit usage by hour for an account for the last year.

27
Q

LOAD_UNCERTAIN_FILES param

A

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.

28
Q

How often does Snowflake release new features?

A

Weekly

29
Q

STRIP_NULL_VALUE function

A

Replace a “null” value with a SQL NULL value. All else is passed unchanged.

30
Q

Securable Objects in Snowflake

A
  • Warehouse
  • Database
  • Table
  • File Format
31
Q

Are permissions granted to Users?

A

No, only roles.

Ownership or a Role is granted to users, never permissions themselves.

32
Q

Are Stored Procedures required to return a value?

A

No

33
Q

Are User Defined Functions (UDFs) required to return a value?

A

Yes

34
Q

Concatenation methods in Snowflake

A

|| or CONCAT()

35
Q

How to alter Time Travel retention period?

A

ALTER TABLE SET DATA_RETENTION_TIME_IN_DAYS = x;

36
Q

Where are the table Metadata and Cached Results stored in Snowflake

A

Cloud Services Layer

37
Q

What type(s) of data are stored in the Snowflake Data Storage layer?

A
  • Data to enable recovery (Time Travel & Fail Safe)

- Persistent data stored in permanent tables (probably transient and temporary as well)

38
Q

What file formats are supported for UNLOADING data from Snowflake?

A
  • JSON
  • Parquet
  • CSV/TSV
39
Q

SQL Comment to list pipes you have access to?

A

SHOW PIPES()

40
Q

How far back can you see query history in the History tab?

A

14 days

41
Q

Places you can specify a File Format

A
  • Named Stage/Pipe definition
  • COPY INTO command
  • Table definition
42
Q

Metadata Retrieval Commands

A
  • SHOW
  • LIST
  • DESCRIBE
43
Q

SHOW command

A

Lists all objects of a certain type within the account or a given database/schema.

Example:
SHOW PROCEDURES;
SHOW USERS;

44
Q

DESCRIBE command

A

Retrieves metadata information about a specific object.

Example:
DESCRIBE PROCEDURE SP_UPDATE_LAYER(VARCHAR, VARCHAR, VARCHAR, VARCHAR)

45
Q

LIST command

A

Lists files within a Snowflake stage.

LIST @test_stage/snowflake

46
Q

How to modify Time Travel Retention period:

A

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.

47
Q

Which objects can be cloned?

A
  • Databases
  • Schemas
  • Tables
  • Stages
  • File Formats
  • Sequences
  • Streams
  • Tasks
48
Q

Are Internal Named Stages clonable?

A

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.

49
Q

OLAP meaning

A

Online Analytical Process

an approach to multi-dimensional analytical queries in computing, useful for business intelligence, reporting, and data mining.

50
Q

OLTP meaning

A

Online Transaction Processing

a type of data processing that executes a number of transactions concurrently.

51
Q

Is Snowflake better suited for OLAP or OLTP?

A

It is suited extremely well for both.

52
Q

Is it possible to unload structured data to semi-structured formats (JSON/Parquet)?

A

Yes

53
Q

PUT command

A

Uploads (or stages) data files from a local source on a client machine to an internal snowflake stage.

54
Q

GET command

A

Downloads data files from an internal snowflake stage onto a local source machine.

Basically the inverse of the PUT command.

55
Q

Does PUT support loading files to an EXTERNAL stage?

A

No, use cloud provider resources for that.

56
Q

Are staged files in Snowflake compressed and encrypted automatically?

A

Yes, if the stage is a Snowflake stage (not an external one).

57
Q

What is the maximum (compressed) row size in Snowflake

A

16 mb

Snowflake Cert Studying (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions