Snowflake Features & Architecture Flashcards

1
Q

User Defined Function (UDF)

A

User defined functions (UDF) are schema-level objects that enable users to write their own functions in four different languages: SQL, JavaScript, Python, and Java

-UDFs accept 0 or more parameters
-UDFs can return scalar or tabular results (UDTF)
-UDFs can be called as part of a SQL statement.
-UDFs can be overloaded.

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

JavaScript UDF

A

-JavaScript is specified with the language parameter.
-Enables use of high-level programming language features.
-JavaScript UDFs can refer to themselves recursively.
-Snowflake data types are mapped to JavaScript data types.

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

Java UDF

A

-Snowflake boots up a JVM to execute function written in Java.
-Snowflake currently supports writing UDFs in Java versions 8.x, 9.x, 10.x, and 11.x
-Java UDFs can specify their definition as in-line code or a pre-compiled jar file.
-Java UDFs cannot be designated as secure.

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

Create or Replace External Function

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

Create or Replace API Integration

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

External Function Call Lifecycle

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

External Function Limitations

A

-Slower due to not being to optimize outside process
-Scalar only
-Not shareable
-Less secure
-Egress charges

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

Stored Procedure

A

In Relational Database Management Systems (RDMS) stored procedures are named collections of SQL statements often containing procedural logic.

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

Snowflake Stored Procedure Options

A
  1. JavaScript
  2. Snowflake Scripting - SQL
  3. Snowpark - Python, Java, or Scala
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Stored Procedure: JavaScript

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

Stored Procedures & UDFs

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

Sequences - Inset into a Table

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

Task

A

A task is an object used to schedule the execution of a SQL command or a stored procedure.

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

Task Workflow

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

Tree of Tasks

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

Streams

A

A stream is an object created to view & track DML changes to a source table - inserts, updates, & deletes

17
Q

Streams Code

A
18
Q

Billing Options

A

On-Demand - Pay for usage as you go
Capacity - Pay for usage upfront

19
Q

Billing - Services Billed

A
  • Virtual Warehouse Services - Credits
  • Cloud Services - Credits
  • Serverless Services - Credits
  • Storage - $ Dollar Value
  • Data Transfer -$ Dollar Value
20
Q

Virtual Warehouse Services Billing

A
  • Credit calculated based on size of virtual warehouse
  • Credit calculated on per second basis while a virtual warehouse is in ‘started’ state
  • Credit calculated with a minimum of 60 seconds
21
Q

Cloud Services Billing

A
  • Credits calculated at a rate of 4.4 credits per compute hour
  • Only cloud services that exceeds 10% of the daily usage of the compute resources are billed
  • This is called the Cloud Services Adjustment
22
Q

Serverless Services Billing

A
  • Each serverless feature has it’s own credit rate per compute-hour
  • Serverless features are composed of both compute services and cloud services
  • Cloud Services Adjustment does not apply to cloud services usage when used by serverless features
23
Q

Data Storage Billing

A
  • Data storage is calculated monthly based on the average number of on-disk bytes per day in the following locations:
    • Database Tables
    • Internal Stages
      -Costs calculated based on a flat dollar value rate per terabyte (TB) based on:
    • Capacity or On-demand
    • Cloud provider
    • Region
24
Q

Data Transfer Billing

A
  • Data transfer charges apply when moving data from one region to another or from one cloud platform to another
  • Unloading data from Snowflake using COPY INTO <location> command</location>
  • Replicating data to a Snowflake account in a different region or cloud platform
  • External functions transferring data out of and into Snowflake
25
Q

Connectors and Drivers Available

A
  • Python
  • Go
  • PHP
  • .NET
  • NodeJS
  • Spark
  • Kafka
  • JDBC
  • ODBC
26
Q

Snowflake Partner Tools Categories

A
  • Business Intelligence
  • Data Integration
  • Security & Governance
  • SQL Development & Management
  • Machine Learning & Data Science
27
Q

Snowflake Partner Connect

A

a feature to expedite connectivity with partnered tools

28
Q

Snowflake Scripting

A

-an extension to Snowflake SQL that adds support for procedural logic
- It’s used to write store procedures and procedural code outside of a stored procedure

29
Q

Branching Construct

A
30
Q

Looping Constructs

A
31
Q

CURSOR

A
32
Q

RESULTSET

A
33
Q

Snowpark API Languages

A
  • Java
  • Scala
  • Python
34
Q

Snowpark API Methods

A

.select()
.join()
.group_by()
.distinct()
.drop()
.union()
.sort()

35
Q

Snowpark API Abstraction

A

DateFrame

36
Q

Snowpark

A
  • Snowpark API

-Lazily-evaluated/executed
-Pushdown computation