Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

My SQL Cards > SnowFlake University - Snowflake Intro > Flashcards

SnowFlake University - Snowflake Intro Flashcards

1
Q

Databases with tables made up of rows and columns are called

A

RDBMS’s

Relational
Database
Management
Systems

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

Tables are situated within

A

Schemas

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

What are schemas?

A

Schemas are logical and organizational buckets/objects; a schema can have multiple tables and views.

Schemas are listed in parentheses after the table name.

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

Common RDBMS objects:

A

Tables

Views

Schemas

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

What are the 3 parts of Snowflake Worksheet?

A
  1. Navigational Tree Area (half of it turns to a properties panel when a table is chosen)
  2. SQL Entry Pane
  3. Result/Preview Pane
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Warehouse provide…

A

the processing power to execute a command

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

To set a worksheet’s context using code, type

A

USE WAREHOUSE [warehouse name];

into the SQL Pane

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

Turning on/off the Code Highlight shortcut is….

and what does Code Highlight do?

A

ctrl + shift + k

It highlights the statement you click on, and only executes it.

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

Shortcut to running the whole SQL code is

A

ctrl + shift + Enter

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

Which editions of Snowflake has multi-cluster available?

A

Snowflake’s Enterprise Edition product (or above)

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

Sometimes a data file has values that contain quotes, apostrophes and/or commas.
These can cause problems when ingesting data. For that reason, some files will be…

A

formated so that fields that have string values are enclosed.

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

When creating a file format, where can you indicate what encloses some of the values that contain quotes, apostrophes and/or commas?

A

The option is called:

Field optionally enclosed by…

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

When creating a file format, and some of the values contain quotes, apostrophes and/or commas: what are the options to enclosing them?

A
  1. None
  2. Double Quote
  3. Single Quote

Snowflake doesn’t support other control (enclosing) character, so you must deal with them differently.

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

What is an ELT Process

A

Extract, Load, Transformation

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

What is considered part of the final step in the ELT Process?

A

Updating/changing a table( i.e. Transforming it)

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

What is an ETL Process

A

Extract, Transform, and Load

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

What are the names given to the two tables in an ETL process?

A 
1st table (Extracted): Source
2nd table (Loaded): Target
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What command to use in order LOAD a table?

A

INSERT INTO

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

Cleaning the data up where it already sits without moving it is called:

A

UPDATE in PLACE

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

What command to use in order EXTRACT a table?

A

FROM

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

What command to use in order TRANSFORM a table?

A

REPLACE

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

What does SQL stand for?

A

Structured Query Language

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

What SQL version Snowflake uses?

A

Standard SQL: ANSI 1999 and SQL: 2003 extensions

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

What does DML stand for and what does it allow us to do?

A

Data Management Language

- let users retrieve and edit data in databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What does DDL stand for and what does it allow us to do?
Data Definition Language | - let users creat and edi database objects such as tables, indexes, and uses
26
Typical clauses for DML
SELECT, UPDATE, INSERT, DELETE
27
Typical clauses for DDL
CREATE, ALTER, DROP
28
What are the three layers of Snowflake architecture?
Cloud Services Query Processing Database Storage
29
What does Snowflake utilize to offer on-demand computing power?
MPP (Massively Parallel Processing) compute clusters where each node in the cluster store a portion of the entire data locally
30
Billing is based on
Credits (servers/cluster) consumed per second
31
Data loading efficiency does/doesn't scale w/ WH size, while query efficiency does/doesn't?
doesn't | does
32
3 Layers in which data exist:
1. Organizing/removing Data (DDL) 2. Storing/working with Data (DML) 3. Querying Data
33
What is a subquery?
A query within another query - could be used in statements such as SELECT, FROM, AND WHERE
34
Types of subqueries?
1. Correlated 2. Uncorrelated 3. Scalar 4. Non-Scalar
35
What does correlated subquery mean?
Refers to one or more columns from the outer query
36
What does uncorrelated subquery mean?
Has no reference to the outer query
37
What does scalar subquery mean?
returns a single value, it is not a correlated subquery, because it can be executed independently
38
What does non-scalar subquery mean?
Return 0 or more values (multiple rows and columns)
39
In the warehouse analogy what are the following called and what are they called in Snowflake? Workers Goods
Workers - Servers - Compute | Goods - Data - Storage
40
What problems are caused by separating data into data marts?
Getting integrated data (from another department or data mart) is inefficient
41
What is the temporary solution used to solve data marts issue?
Keeping copies of subsets of data in the various data mart-- but that leads to numbers that does not match across departments
42
Snowflake section off and silos data and thus there is a need to replicate the data?
NO
43
How is Snowflake warehouse not the same as data marts?
Every wawrhouse has access to all you data, all the time
44
How many clusters of servers are there in a Snowflake warehouse?
1
45
When changing warehouse sizes from small to medium you are...
adding servers, not clusters
46
Scaling up/down means
Changing the size of a warehouse
47
Scaling out is sometimes called
Elastic data warehousing
48
Why is calling out sometimes called elastic data warehousing?
because the warehouse will stretch out to take extra work and then snap back to its original size once the work is done
49
A warehouse can be stretched to up to
10 clusters during peak workloads
50
Elastic warehouses are also called
Multi-cluster warehouses
51
What are the three things warehouse power is used for:
loading data uploading data querying data
52
What does the warehouse not do:
contain or store data
53
What is a Stage or Staging Area?
A place to put things temporarily before moving them to a more stable location
54
What are stages in Old School Data Warehouses?
A middle stop bw transactions (OLTP) and Reporting (OLAP)
55
What are stages in Snowflake?
Cloud folders or directories where you place files so that Snowflake services can pick them up and pull them up in to a database.
56
What is FTP?
File Transfer Protocol
57
What are the two types of stages in Snowflake?
Internal stages | external stages
58
What are inside stages in Snowflake?
they act like directories inside a Snowflake account's local storage
59
What are external stages in Snowflake?
Act like a secure gateway between cloud storage services and Snowflake services
60
What are the big three cloud services?
Amazon S3 Google Cloud Platform (GCP) Microsoft Azure BLOB Storage
61
What are the three needed components of an external stage?
1. Cloud storage location 2. Cloud storage access credentials (ex. IAM User & Policy for S3) 3. Stage Definition (a stage object that contains references to those two things)
62
What does pre-stage data movement:
where data is coming from sources external to Snowflake (or cloud service)
63
What are the 4 applications provided by Amazon for pre-stage data movement?
1. FTP tool called Amazon Transfer for SFTP 2. Command-line Interface: Amazon CLI 3. Website Interface 4. Could also load data pragmatically (ex. Python)
64
T/F: Snowflake tracks whether a file has been loaded and doesn't let you load it twice by accident.
True
65
What code to use if one wants to force a file to reload, which was already loaded?
FORCE = TURE at the end of the COPY INTO statement But this will result in having double the number of rows
66
How to query a data that doesn't have column name?
We select them using a dollar sign and their sequence number
67
List data type trend levels from smaller to larger data quantity supported + examples
Structured Data - Tables Semi-Structured Data Quasi-Structured Data - Clickstream Unstructured Data - Images, PDFs, Videos
68
Entity Relationship Diagrams (ERD)
They show relationship between different entities/tables, such as "authors" and "books" (logical infrastructure)
69
What are the following match to in a database: Entity Attribute Value
Table Column Rows
70
Normalizing the data means (3 things):
1. Different entities in separate tables 2. Same info not repeated unnecessarily 3. Unique IDs for each row
71
Creating a sequence to
act as a counter that give unique ID to each record
72
How to create a sequence
Through Wizard Web UI: | Go to Databases and click on Sequences in ribbon
73
What code line would show you the next value in a sequence?
SELECT sequence_name.nextval; Every time you refer to the Sequence's Next Value in a select statement, it will increment. And thus, it is recommended that you reset the counter before you use the value in a table
74
the 5 semi-structured data supported by Snowflake:
1. JSON - JavaScript Object Notation 2. XML 3. Parquet 4. Avro 5. ORC
75
Name the data type used when setting up a table column that will be home to semi-structured data?
Variant
76
What is an Object Model?
It is the nesting pattern of any dataset
77
In JASON, attributes are called:
Keys
78
Key-Value Pair in JASON syntax is
A term used when referring to a key and its value
79
In JASON, every entity is surrounded by...
curly braces
80
The key and the value in a key-value pair are separated by...
a colon :
81
Between each key-value pairs there is a...
a comma
82
JASON syntax to retrieve a nested value
main_table:nested_table[sequence number of key].key Sequence of keys start by 0, aka the first key in a nested table has the value of 0

My SQL Cards (3 decks)

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