Snowflake Features & Architecture

Question 1

User Defined Function (UDF)

Answer 1

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.

Question 2

JavaScript UDF

Answer 2

-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.

Question 3

Java UDF

Answer 3

-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.

Question 4

Create or Replace External Function

Answer 4

Question 5

Create or Replace API Integration

Answer 5

Question 6

External Function Call Lifecycle

Answer 6

Question 7

External Function Limitations

Answer 7

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

Question 8

Stored Procedure

Answer 8

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

Question 9

Snowflake Stored Procedure Options

Answer 9

1. JavaScript
2. Snowflake Scripting - SQL
3. Snowpark - Python, Java, or Scala

Question 10

Stored Procedure: JavaScript

Answer 10

Question 11

Stored Procedures & UDFs

Answer 11

Question 12

Sequences - Inset into a Table

Answer 12

Question 13

Task

Answer 13

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

Question 14

Task Workflow

Answer 14

Question 15

Tree of Tasks

Answer 15

Question 16

Streams

Answer 16

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

Question 17

Streams Code

Answer 17

Question 18

Billing Options

Answer 18

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

Question 19

Billing - Services Billed

Answer 19

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

Question 20

Virtual Warehouse Services Billing

Answer 20

• 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

Question 21

Cloud Services Billing

Answer 21

• 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

Question 22

Serverless Services Billing

Answer 22

• 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

Question 23

Data Storage Billing

Answer 23

• 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

Question 24

Data Transfer Billing

Answer 24

• 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

Question 25

Connectors and Drivers Available

Answer 25

- Python - Go - PHP - .NET - NodeJS - Spark - Kafka - JDBC - ODBC

Question 26

Snowflake Partner Tools Categories

Answer 26

- Business Intelligence - Data Integration - Security & Governance - SQL Development & Management - Machine Learning & Data Science

Question 27

Snowflake Partner Connect

Answer 27

a feature to expedite connectivity with partnered tools

Question 28

Snowflake Scripting

Answer 28

-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

Question 29

Branching Construct

Answer 29

Question 30

Looping Constructs

Answer 30

Question 31

CURSOR

Answer 31

Question 32

RESULTSET

Answer 32

Question 33

Snowpark API Languages

Answer 33

- Java - Scala - Python

Question 34

Snowpark API Methods

Answer 34

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

Question 35

Snowpark API Abstraction

Answer 35

DateFrame

Question 36

Snowpark

Answer 36

- Snowpark API -Lazily-evaluated/executed -Pushdown computation