 Data that does not follow the tabular structure of

relational data.

 Semi-structured data does not require a prior definition of

a schema and can constantly evolve (i.e. new attributes
can be added at any time).

 Elements of semi-structured data can include:

 Key-Value Pairs This is the most basic form of semi-
structured data and is essentially a simple data structure
where each item is made up of a key and a corresponding
value.
 Hierarchical Structures Unlike structured data, which
represents data as a flat table, semi-structured data can
contain N-level hierarchies of nested information.
 Multi-Valued Fields Semi-structured data can contain
fields that have multiple values, commonly known as arrays.
 Snowflake natively supports the semi-structured data formats below:

JSON (JavaScript Object Notation): JSON data is a hierarchical collection of name/value pairs grouped
into objects and arrays

 Colons : separate names and values in name/value pairs.

 Curly braces {} denote objects.
 Square brackets [] denote arrays.
 Commas , separate entities in objects and arrays.
Example:
{"firstName":"John", "empid":45611}

{"employees":[
{"firstName":"John", "lastName":"Doe"},
{"firstName":"Anna", "lastName":"Smith"},
{"firstName":"Peter", "lastName":"Jones"}
]
}
AVRO:

 Data serialization system developed within the Apache Hadoop project

 An Avro schema consists of a JSON string, object, or array that defines the type of schema and the data
attributes (field names, data types, etc.) for the schema type

 Example:
{
"type": "record",
"name": "person",
"namespace": "[Link]",
"fields": [
{"name": "fullName", "type": "string"},
{"name": "age", "type": ["int", "null"]},
{"name": "gender", "type": ["string", "null"]}
]
}
ORC(Optimized Row Columnar):

 Columnar storage format primarily used

in the Hadoop ecosystem for high-
performance storage and retrieval of large
datasets

 ORC was designed for efficient

compression and improved performance
for reading, writing, and processing data

 An ORC file contains groups of row data

called stripes, along with auxiliary
information in a file footer.

 At the end of the file a postscript holds

compression parameters and the size of
the compressed footer
Parquet:

 Parquet is a compressed, efficient

columnar data representation
designed for projects in the Hadoop
ecosystem

 One of the main advantages of

Parquet is its ability to handle very
large datasets efficiently

 Parquet is column-oriented – meaning

the values of each table column are
stored next to each other, rather than
those of each record
XML(eXtensible Markup Language):

 Is a markup language that defines a

set of rules for encoding documents

 Designed to store and transport data in

a structured, human-readable, and
machine-readable way

 An XML document consists primarily of

the following constructs:

 Tags (identified by angle brackets, <

and >)
 Elements consist of a “start” tag and
matching “end” tag, with the text
between the tags constituting the
content of the element
 VARIANT
 A VARIANT value can have a maximum size of up to 128 MB of uncompressed data
 Can store a value of any other type, including OBJECT and ARRAY values
 Data from APIs or third-party tools is easier to dump into a VARIANT column

 ARRAY
 An array contains 0 or more pieces of data. Each element is accessed by specifying its position in the array
 Can directly contain a VARIANT value, and thus indirectly contain a value of any other data type, including
itself
 The theoretical maximum combined size of all values in an array is 128 MB.
 ARRAY data is typically used when There is a collection of data, and each piece in the collection is structured
the same

 OBJECT
 Snowflake supports using constants to specify OBJECT values
 OBJECT constants are delimited with curly braces ({ and })
 You have multiple pieces of data that are identified by strings. For example, if you want to look up information
by province name, you might want to use an OBJECT value.
 To retrieve data from a top-level key, we can use the colon notation, like src:person
 If you need to delve deeper into the object structure, you have two options
 Use dot notation with a statement like src:[Link]
 Use bracket notation with a statement such as src['person']['address']['city’]

 Syntax:
 Dot notation: <column>:<level1_element>.<level2_element>.<level3_element>
 Bracket notation: <column>['<level1_element>']['<level2_element>']['<level3_element>']
Snowflake gives various functions for working with JSON data. Below are the most commonly
used ones:
 PARSE_JSON: takes a string input as a JSON document and outputs a VARIANT value
 TRY_PARSE_JSON: this works in the exact same way as PARSE_JSON, but produces a null value if there is
an error during parsing
 LATERAL FLATTEN: used to flatten an array into multiple rows - one per object in the array.
 INFER_SCHEMA: Automatically detects the file metadata schema in a set of staged data files that
contain semi-structured data and retrieves the column definitions.
 CHECK_JSON: Checks the validity of a JSON document. If the input string is a valid JSON document or a
NULL, the output is NULL (i.e. no error). If the input cannot be translated to a valid JSON value, the output
string contains the error message.
 STRIP_NULL_VALUE: Converts a JSON null value to a SQL NULL value. All other variant values are passed
unchanged.
 Snowflake Snowpipe is a powerful,
serverless continuous data ingestion
service that enables you to load data from
files in micro-batches as soon as they're
available in a stage

 Data available to users within minutes,

providing near real-time analytics.

 A pipe is a named, first-class Snowflake

object that contains a COPY statement
used by Snowpipe

 Purpose: load data from files in micro-

batches, making it available to users within
minutes, rather than manually executing
COPY statements on a schedule to load
larger batches.
 Snowpipe uses a combination of filename and a file
checksum to ensure only “new” data is processed.
 Snowpipe uses serverless architecture and uses Snowflake-
supplied compute resources and you are billed accordingly.
 Snowpipe loads data in response to new file notification
events. These notification events are usually configured in
cloud platforms.
 We can also trigger a Snowpipe manually from Snowflake or
through custom programs calling the REST APIs.
 Create Storage Account on Azure Portal
 Create Container inside Storage Account
 Create Folder inside the Container
 Upload files inside the folder

 Create Storage Integration in Snowflake

 Describe Storage Integration and grant consent permissions to URL in value for parameter AZURE_CONSENT_URL
 Note down the property_value of AZURE_MULTI_TENANT_APP_NAME that is before underscore (_)

 Assign Permissions to SPN created above for Storage Account in Azure

 In Azure Storage account Access Control (IAM), add role assignment and select Storage Blob Data Contributor role.

 Create Storage Queue inside Storage Account in Azure

 Create a storage queue inside your storage account by navigating to StorageAccount >> Data Storage >> Queues

 Create Event Subscription inside Storage Account in Azure

 Create an Event Subscription inside your storage account by navigating to StorageAccount >> Events >> Event
Subscription
 Select the Event Type as Blob Created
 Select the Endpoint Type as Storage Queues and select the queue created in earlier step

 Create Notification Integration in Snowflake

 Describe Notification Integration and grant consent permissions to URL in value for parameter
AZURE_CONSENT_URL
 Note down the property_value of AZURE_MULTI_TENANT_APP_NAME that is before underscore (_)

 Assign Permissions to SPN created above for Storage Account in Azure to access Queue
 In Azure Storage account Access Control (IAM), add role assignment and select Storage Queue Data Contributor
role.

 Create file format in Snowflake

 Create Stage in Snowflake

 Mention the name of Storage Integration created in previous steps
 Mention the URL of the file uploaded in container inside storage account

 Create Pipe in Snowflake

 Mention the name of Notification Integration created in previous steps
 Change Data Capture (CDC) refers to the process of tracking and capturing data changes in a table,
enabling you to identify and process new data, updates, or deletions efficiently

 This is essential for keeping data synchronized across different systems and ensuring data accuracy.

 Snowflake implements CDC using Streams, which continuously capture DML (Data Manipulation
Language) changes like inserts, updates, and deletes as well as metadata about each change, so
that actions can be taken using the changed data

 These changes are then available for processing using Tasks. A task object runs a SQL statement,
which can include calls to stored procedures

 Tasks can run on a schedule or based on a trigger that you define, such as the arrival of data
 A stream stores an offset for the source
object and not any actual table columns or
data. When queried, a stream accesses and
returns the historic data in the same shape
as the source object (i.e. the same column
names and ordering) with the following
additional columns:

 METADATA$ACTION: Indicates the DML

operation (INSERT, DELETE) recorded
 METADATA$ISUPDATE: Updates to rows in
the source object are represented as a pair
of DELETE and INSERT records in the stream
with a metadata column
METADATA$ISUPDATE values set to TRUE.
 METADATA$ROW_ID: Specifies a unique,
immutable row ID for tracking changes over
time
Standard:
 A standard (i.e. delta) stream tracks all DML changes to the source object, including inserts, updates,
and deletes (including table truncates)
 Supported for streams on standard tables, dynamic tables, Snowflake-managed Apache Iceberg
tables, directory tables, or views

Append-only
 An append-only stream exclusively tracks row inserts. Update, delete, and truncate operations are not
captured by append-only streams
 Supported for streams on standard tables, dynamic tables, Snowflake-managed Apache Iceberg
tables, or views

 Insert-only
 An insert-only stream tracks row inserts only; they do not record delete operations that remove rows
from an inserted set
 Supported for streams on Apache Iceberg or external tables
 A stream becomes stale when its offset falls outside of the data retention period for its source table (or
underlying tables for a source view)
 In a stale state, historical data and any unconsumed change records for the source table are no longer
accessible
 To continue tracking new change records, you must recreate the stream using the CREATE
STREAM command
 To prevent a stream from becoming stale, consume the stream records within a DML statement during
the table’s retention period and regularly consume its change data before its STALE_AFTER timestamp
 If the data retention period for a table is less than 14 days and a stream hasn’t been consumed,
Snowflake temporarily extends this period to prevent the stream from going stale
 The retention period is extended to the stream’s offset, up to a maximum of 14 days by default,
regardless of your Snowflake edition
 The maximum number of days for which Snowflake can extend the data retention period is determined
by the MAX_DATA_EXTENSION_TIME_IN_DAYS parameter value
 A Snowflake Task allows scheduled execution of SQL statements including calling a stored procedure or
Procedural logic using Snowflake Scripting.

 To create a task, you need to be defining the following optional parameters using CREATE TASK along
with the SQL code:
 The compute resources using which the SQL code executes using a WAREHOUSE parameter.
 The schedule details when the code needs to be executed using a SCHEDULE parameter.

 Tasks require compute resources to execute SQL code. Either of the following compute models can be
chosen for individual tasks:
 User-managed (i.e. Virtual warehouse)
 Snowflake-managed (i.e. Serverless compute model)

 Once task is created it will be in suspended state. You need to be manually resume the task using ALTER
TASK.

 Only one SQL statement is allowed to be executed through a task. If you need to execute multiple
statements, build a procedure
 Snowflake Tasks are not event based, instead a task runs on a schedule.

 The Snowflake task engine has a CRON and NONCRON variant scheduling mechanisms
 Serverless
Task

Standard Task
 In Snowflake, you can manage multiple tasks with a task graph, also known as a directed acyclic
graph, or DAG.

 A task graph is composed of a root task and dependent child tasks. The dependencies must run in a
start-to-finish direction, with no loops

 An optional final task (finalizer) can perform cleanup operations after all other tasks are complete.
 A task graph is limited to a maximum of 1000 tasks

 A single task can have a maximum of 100 parent tasks and 100 child tasks

 All tasks in a task graph must have the same task owner and be stored in the same database and
schema

 A finalizer task is always associated with a root task. Each root task can have only one finalizer task,
and a finalizer task can be associated with only one root task

 For serverless tasks, Snowflake automatically scales resources to make sure tasks complete within a
target completion interval, including queueing time

 For user-managed tasks, longer queueing periods are common when tasks are scheduled to run on a
shared or busy warehouse