Snowflake - Tasks

User-defined tasks allow scheduled execution of SQL statements. Tasks run according to a specified execution configuration, using any combination of a set interval and/or a flexible schedule using a subset of familiar cron utility syntax. Tasks currently run in user-specified warehouses but will eventually run in a serverless environment using Snowflake-supplied compute resources. Currently, a task can execute a single SQL statement, including a call to a stored procedure. Tasks can be combined with table streams for continuous ELT workflows to process recently changed table rows. Streams ensure exactly once semantics for new or changed data in a table. Tasks can also be used independently to generate periodic reports by inserting or merging rows into a report table or perform other periodic work.

Task Scheduling
There is no event source that can trigger a task; instead, a task runs on a schedule, which can be defined when creating a task (using CREATE TASK) or later (using ALTER TASK). Snowflake ensures only one instance of a task with a schedule (i.e. a standalone task or the root task in a tree of tasks) is executed at a given time. If a task is still running when the next scheduled execution time occurs, then that scheduled time is skipped.

Task History
Query the TASK_HISTORY table function to verify the task did not run. It is possible that the task ran successfully but the SQL statement in the task definition failed. In particular, note the scheduled and completed times, as well as any error code and message. If the task has a parent task (in a tree of tasks), verify whether the parent task completed successfully.

There is a 60 minute default limit on a single run of a task. This limitation was implemented as a safeguard against non-terminating tasks.

Link: https://docs.snowflake.com/en/user-guide/tasks-intro.html

Snowflake - Stored Procedures

Unlike MS SQL server, Snowflake's stored procedures are written in JavaScript. A stored procedure returns a single value. Although you can run SELECT statements inside a stored procedure, the results must be used within the stored procedure, or be narrowed to a single value to be returned.

Snowflake stored procedures use JavaScript and, in most cases, SQL:

• JavaScript provides the control structures (branching and looping).
• SQL is executed by calling functions in a JavaScript API.

Stored procedures allow:

• Procedural logic (branching and looping), which straight SQL does not support.
• Error handling.
• Dynamically creating a SQL statement and execute it.
• Writing code that executes with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure. This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.

One common use for stored procedures is to automate a task that requires multiple SQL statements and is performed frequently.

Snowflake API consists of JavaScript objects and the methods in those objects.

Link: https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html

Data Warehouse vs Database

Data warehouses and Databases are both relational data systems, but were built to serve different purposes. A data warehouse is built to store large quantities of historical data and enable fast, complex queries across all the data, typically using Online Analytical Processing (OLAP). A database was built to store current transactions and enable fast access to specific transactions for ongoing business processes, known as Online Transaction Processing (OLTP).

Parameter	Database	Data Warehouse
Purpose	Is designed to record	Is designed to analyze
Processing Method	The database uses the Online Transactional Processing (OLTP)	Data warehouse uses Online Analytical Processing (OLAP)
Usage	The database helps to perform fundamental operations for your business	Data warehouse allows you to analyze your business
Tables and Joins	Tables and joins of a database are complex as they are normalized	Table and joins are simple in a data warehouse because they are denormalized
Orientation	Is an application-oriented collection of data	It is a subject-oriented collection of data
Storage limit	Generally limited to a single application	Stores data from any number of applications
Availability	Data is available real-time	Data is refreshed from source systems as and when needed
Usage	ER modeling techniques are used for designing	Data modeling techniques are used for designing
Technique	Capture data	Analyze data
Data Type	Data stored in the Database is up to date	Current and Historical Data is stored in Data Warehouse May not be up to date
Storage of data	Flat Relational Approach method is used for data storage	Data Ware House uses dimensional and normalized approach for the data structure Example: Star and snowflake schema
Query Type	Simple transaction queries are used	Complex queries are used for analysis purpose

Links: 
https://panoply.io/data-warehouse-guide/the-difference-between-a-database-and-a-data-warehouse
https://www.guru99.com/database-vs-data-warehouse.html

Snowflake : Data Encryption

Snowflake provides best-in-class key management, which is entirely transparent to customers.

End-to-end encryption (E2EE) is a form of communication in which no one but end users can read the data. In Snowflake, this means that only a customer and the runtime components can read the data. No third parties, including Snowflake’s cloud computing platform or any ISP, can see data in the clear. E2EE minimizes the attack surface. In the event of a security breach of the cloud platform, the data is protected because it is always encrypted, regardless of whether the breach exposes access credentials indirectly or data files directly, whether by an internal or external attacker.

Client-side encryption provides a secure system for managing data in cloud storage. Client-side encryption means that a user encrypts stored data before loading it into Snowflake. The cloud storage service only stores the encrypted version of the data and never includes data in the clear.

Document Link: https://docs.snowflake.net/manuals/user-guide/security-encryption.html

Sharing Data Securely in Snowflake

Secure Data Sharing enables sharing selected objects (tables, secure views, and secure UDFs) in a database in your account with other Snowflake accounts. The principle participants in any data sharing relationship are the provider and one or more consumers. Snowflake enables the sharing of databases through shares, which are created by data providers and "imported" by data consumers. All database objects shared between accounts are read-only (i.e. the objects cannot be modified or deleted, including adding or modifying table data).
With Secure Data Sharing, no actual data is copied or transferred between accounts. All sharing is accomplished through Snowflake’s unique services layer and metadata store. This is an important concept because it means that shared data does not take up any storage in a consumer account and, therefore, does not contribute to the consumer’s monthly data storage charges. The only charges to consumers are for the compute resources (i.e. virtual warehouses) used to query the shared data.
In addition, because no data is copied or exchanged, Secure Data Sharing setup is quick and easy for providers and access to the shared data is instantaneous for consumers:

• The provider creates a share of a database in their account and grants access to specific objects (i.e. tables, secure views, and secure UDFs) in the database. One or more accounts are then added to the share, which can include your own accounts (if you have multiple Snowflake accounts).
• On the consumer side, a read-only database is created from the share. Access to this database is configurable using the same, standard role-based access control that Snowflake provides for all objects in the system.

Shares are named, first-class Snowflake objects that encapsulate all of the information required to share a database. Each share consists of:

• The privileges that grants access to the database and the schema containing the objects to share.
• The privileges that grant access to the specific objects (tables, secure views, and secure UDFs).
• The consumer accounts with which the database and its objects are shared.

Once a database is created (in a consumer account) from a share, all the shared objects are accessible to users in the consumer account. Shares are secure, configurable, and controlled 100% by the provider account:

• New objects added to a share become immediately available to all consumers, providing real-time access to shared data.
• Access to a share (or any of the objects in a share) can be revoked at any time.

Link: https://docs.snowflake.net/manuals/user-guide/data-sharing-intro.html

Snowflake Architecture

Snowflake’s architecture is a hybrid of traditional shared-disk database architectures and shared-nothing database architectures. Similar to shared-disk architectures, Snowflake uses a central data repository for persisted data that is accessible from all compute nodes in the data warehouse. But similar to shared-nothing architectures, Snowflake processes queries using MPP (massively parallel processing) compute clusters where each node in the cluster stores a portion of the entire data set locally. This approach offers the data management simplicity of a shared-disk architecture, but with the performance and scale-out benefits of a shared-nothing architecture.

Snowflake’s unique architecture consists of three key layers:

• Database Storage
• Query Processing
• Cloud Services

Database Storage When data is loaded into Snowflake, Snowflake reorganizes that data into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage. Snowflake manages all aspects of how this data is stored — the organization, file size, structure, compression, metadata, statistics, and other aspects of data storage are handled by Snowflake. The data objects stored by Snowflake are not directly visible nor accessible by customers; they are only accessible through SQL query operations run using Snowflake.

Query Processing Query execution is performed in the processing layer. Snowflake processes queries using “virtual warehouses”. Each virtual warehouse is an MPP compute cluster composed of multiple compute nodes allocated by Snowflake from a cloud provider. Each virtual warehouse is an independent compute cluster that does not share compute resources with other virtual warehouses. As a result, each virtual warehouse has no impact on the performance of other virtual warehouses.

Cloud Services The cloud services layer is a collection of services that coordinate activities across Snowflake. These services tie together all of the different components of Snowflake in order to process user requests, from login to query dispatch. The cloud services layer also runs on compute instances provisioned by Snowflake from the cloud provider. Among the services in this layer:

• Authentication
• Infrastructure management
• Metadata management
• Query parsing and optimization
• Access control

For more information: https://docs.snowflake.net/manuals/user-guide-intro.html

Snowflake : Cloud Data Warehouse

Snowflake allows corporate users to store and analyze data using cloud-based hardware and software. The data is stored in Amazon S3. It is built for speed, even with the most intense workloads. Its patented architecture separates compute from storage so you can scale up and down on the fly, without delay or disruption. You get the performance you need exactly when you need it.

Snowflake is a fully columnar database with vectorized execution, making it capable of addressing even the most demanding analytic workloads. Snowflake’s adaptive optimization ensures queries automatically get the best performance possible – no indexes, distribution keys or tuning parameters to manage. Snowflake can support unlimited concurrency with its unique multi-cluster, shared data architecture. This allows multiple compute clusters to operate simultaneously on the same data without degrading performance. Snowflake can even scale automatically to handle varying concurrency demands with its multi-cluster virtual warehouse feature, transparently adding compute resources during peak load periods and scaling down when loads subside.

Snowflake’s mission is to enable every organization to be data-driven with instant elasticity, secure data sharing and per-second pricing, across multiple clouds. Snowflake combines the power of data warehousing, the flexibility of big data platforms and the elasticity of the cloud at a fraction of the cost of traditional solutions.