DDL (Data Definition Language) operations are fundamental SQL commands used to define and manage database structures in Snowflake. The three primary DDL operations are CREATE, ALTER, and DROP.

**CREATE** is used to establish new database objects such as databases, schemas, tables, views, and warehouses. For example, 'CREATE TABLE customers (id INT, name STRING)' generates a new table with specified columns. Snowflake supports various CREATE variations including CREATE OR REPLACE (which overwrites existing objects) and CREATE IF NOT EXISTS (which prevents errors when objects already exist).

**ALTER** modifies existing database objects. This command allows you to add, remove, or modify columns in tables, change object properties, rename objects, or adjust warehouse settings. Common uses include 'ALTER TABLE customers ADD COLUMN email STRING' to add new columns, or 'ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = LARGE' to resize a virtual warehouse. ALTER provides flexibility to evolve your data structures as requirements change.

**DROP** removes database objects permanently. Examples include 'DROP TABLE customers' or 'DROP DATABASE sales_db'. Snowflake's Time Travel feature provides a safety net, allowing recovery of dropped objects within a configurable retention period (up to 90 days for Enterprise edition). The DROP IF EXISTS variation prevents errors when attempting to remove non-existent objects.

Key considerations for DDL operations in Snowflake include:

1. **Transactional DDL**: Snowflake supports transactional DDL, meaning operations can be rolled back if part of a transaction.

2. **Privileges**: Appropriate privileges are required to execute DDL commands on specific objects.

3. **Metadata Operations**: DDL operations are metadata operations that execute quickly regardless of data volume.

4. **Cloning**: Snowflake's unique CREATE CLONE command creates zero-copy clones of objects, enabling efficient data transformation workflows.

Understanding these operations is essential for managing Snowflake environments and implementing effective data transformation strategies.

DML (Data Manipulation Language) operations in Snowflake are essential commands for modifying data within tables. Understanding these operations is crucial for the SnowPro Core Certification.

**INSERT**: This operation adds new rows to a table. Snowflake supports various INSERT methods including single-row inserts, multi-row inserts, and INSERT from SELECT statements. You can insert data from staged files using COPY INTO or INSERT with stage references. Example: INSERT INTO employees VALUES (1, 'John', 'Sales');

**UPDATE**: This command modifies existing records in a table based on specified conditions. Snowflake supports standard UPDATE syntax with WHERE clauses to target specific rows. You can update multiple columns simultaneously and use subqueries for complex transformations. Example: UPDATE employees SET department = 'Marketing' WHERE emp_id = 1;

**DELETE**: This operation removes rows from a table based on conditions specified in the WHERE clause. Using DELETE requires Time Travel storage for the retention period. Be cautious as DELETE operations on large tables can consume significant resources. Example: DELETE FROM employees WHERE status = 'inactive';

**MERGE**: This powerful operation combines INSERT, UPDATE, and DELETE into a single statement. MERGE compares source and target tables, then performs different actions based on whether rows match. It follows the WHEN MATCHED and WHEN NOT MATCHED clauses to determine appropriate actions. This is particularly useful for slowly changing dimensions and incremental data loading scenarios. Example: MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.value = source.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source.id, source.value);

All DML operations in Snowflake are atomic and support transactions. They leverage Snowflake's micro-partition architecture, creating new micro-partitions rather than modifying existing ones, which enables Time Travel and zero-copy cloning capabilities.

Query syntax and clauses form the foundation of data transformations in Snowflake, enabling users to retrieve, manipulate, and analyze data effectively. Understanding these components is essential for the SnowPro Core Certification.

The SELECT clause is the primary component, specifying which columns to retrieve from tables. You can select specific columns, use aliases, or apply functions to transform data. The FROM clause identifies the source tables or views containing your data.

The WHERE clause filters rows based on specified conditions, allowing you to narrow down results using comparison operators, logical operators (AND, OR, NOT), and pattern matching with LIKE. This is crucial for extracting relevant subsets of data.

The GROUP BY clause aggregates rows sharing common values, typically used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX. The HAVING clause then filters these grouped results based on aggregate conditions.

The ORDER BY clause sorts result sets in ascending (ASC) or descending (DESC) order. You can sort by multiple columns and use column positions or aliases.

The LIMIT clause restricts the number of rows returned, useful for sampling data or pagination. Combined with OFFSET, you can skip a specified number of rows.

JOIN clauses combine rows from multiple tables based on related columns. Snowflake supports INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins. Understanding join types is critical for working with normalized databases.

The DISTINCT keyword eliminates duplicate rows from results. UNION, INTERSECT, and EXCEPT operators combine results from multiple queries.

Subqueries can appear in SELECT, FROM, or WHERE clauses, enabling complex nested queries. Common Table Expressions (CTEs) using the WITH clause provide readable alternatives to subqueries.

Window functions like ROW_NUMBER, RANK, and LAG perform calculations across row sets related to the current row, specified using OVER with PARTITION BY and ORDER BY clauses.

Mastering these query components enables efficient data transformation and is fundamental knowledge for Snowflake certification success.

Common Table Expressions (CTEs) and subqueries are essential tools for data transformations in Snowflake, enabling complex query construction and improved readability.

**Subqueries** are queries nested inside another SQL statement. They can appear in SELECT, FROM, WHERE, or HAVING clauses. Subqueries execute first, and their results feed into the outer query. There are three main types: scalar subqueries (returning a single value), row subqueries (returning one row), and table subqueries (returning multiple rows and columns). For example, a subquery in a WHERE clause might filter employees earning above the average salary.

**Common Table Expressions (CTEs)** are temporary named result sets defined using the WITH clause at the beginning of a query. They exist only for the duration of the query execution. CTEs offer several advantages over subqueries: they improve code readability, allow referencing the same result set multiple times within a query, and support recursive operations for hierarchical data processing.

**Key Differences:**
- CTEs are defined before the main query; subqueries are embedded within it
- CTEs can be referenced multiple times; subqueries must be repeated if needed elsewhere
- CTEs support recursion; standard subqueries do not
- CTEs often produce cleaner, more maintainable code

**Snowflake Considerations:**
Snowflake optimizes both constructs efficiently. The query optimizer may treat them similarly in execution plans. However, CTEs provide better organization for complex transformations involving multiple steps. Recursive CTEs are particularly valuable for traversing hierarchical structures like organizational charts or bill of materials.

**Best Practices:**
Use CTEs when you need to reference results multiple times or when building multi-step transformations. Choose subqueries for simple, one-time filtering operations. Both techniques are fundamental for the SnowPro Core exam, particularly in understanding how Snowflake processes complex analytical queries and data transformation pipelines.

Window functions in Snowflake are powerful analytical tools that perform calculations across a set of rows related to the current row, while preserving the individual row identity in the result set. Unlike aggregate functions that collapse multiple rows into a single output, window functions return a value for each row based on a 'window' or subset of data.

The basic syntax includes the OVER() clause, which defines the window specification. This clause can contain PARTITION BY to divide data into groups, and ORDER BY to determine the sequence of rows within each partition.

Key categories of window functions include:

1. **Ranking Functions**: ROW_NUMBER() assigns unique sequential integers, RANK() provides rankings with gaps for ties, DENSE_RANK() gives rankings with no gaps, and NTILE() distributes rows into specified buckets.

2. **Aggregate Window Functions**: Standard aggregates like SUM(), AVG(), COUNT(), MIN(), and MAX() can operate over windows, enabling running totals, moving averages, and cumulative calculations.

3. **Navigation Functions**: LAG() accesses previous row values, LEAD() retrieves subsequent row values, FIRST_VALUE() and LAST_VALUE() return values from the first or last row in the window frame.

Window frames further refine calculations using ROWS or RANGE keywords, specifying boundaries like UNBOUNDED PRECEDING, CURRENT ROW, or specific row offsets.

Practical applications include calculating running totals for financial reporting, determining percentile rankings for performance analysis, computing moving averages for trend analysis, and comparing current values with previous periods.

For the SnowPro Core exam, understand that window functions are essential for complex analytical queries, they operate after WHERE and GROUP BY clauses are processed, and they provide significant performance benefits compared to self-joins or correlated subqueries. Mastering window functions demonstrates proficiency in transforming data efficiently within Snowflake's cloud data platform.

The VARIANT data type in Snowflake is a powerful semi-structured data type designed to store and process JSON, Avro, ORC, Parquet, and XML data. It can hold values of any other data type, including OBJECT and ARRAY, making it extremely flexible for handling diverse data formats.

Key characteristics of VARIANT include its ability to store up to 16 MB of uncompressed data per value. When you load semi-structured data into Snowflake, it automatically parses and stores it in an optimized columnar format, which enables efficient querying and storage compression.

VARIANT columns support dot notation and bracket notation for accessing nested elements. For example, if you have a VARIANT column called 'data' containing JSON, you can access nested values using data:customer:name or data['customer']['name']. This makes extracting specific fields from complex nested structures straightforward.

When working with VARIANT data, Snowflake provides several essential functions. The PARSE_JSON function converts a JSON string into a VARIANT value, while TO_JSON converts VARIANT back to a JSON string. The FLATTEN function is particularly useful for expanding nested arrays into separate rows, enabling you to work with array elements individually.

Type casting is important when extracting values from VARIANT columns. Retrieved values maintain their VARIANT type by default, so you should cast them to specific types using double-colon notation (::) or CAST function for proper comparisons and calculations. For instance, data:price::NUMBER converts the price field to a numeric type.

VARIANT data benefits from Snowflakes automatic metadata collection during loading, which helps optimize query performance. The platform creates statistics about the structure and content of semi-structured data, allowing the query optimizer to make intelligent decisions.

For the SnowPro Core exam, understanding how to load, query, and transform VARIANT data is essential, as semi-structured data handling represents a significant advantage of Snowflakes architecture over traditional data warehouses.

Querying semi-structured data in Snowflake is a powerful capability that allows you to work with formats like JSON, Avro, Parquet, and XML stored in VARIANT columns. Snowflake provides native support for these data types, making it easy to extract and transform nested data structures.

The VARIANT data type is central to handling semi-structured data. When you load JSON or similar formats into Snowflake, the data is stored in VARIANT columns, preserving the original structure while enabling SQL-based querying.

To access nested elements, Snowflake uses bracket notation and dot notation. For example, if you have a VARIANT column called 'data' containing JSON, you can access a field using data:fieldname or data['fieldname']. For nested objects, chain the notation: data:customer:address:city.

The FLATTEN function is essential for working with arrays within semi-structured data. It converts array elements into separate rows, enabling you to join and aggregate nested arrays. The LATERAL keyword often accompanies FLATTEN to correlate the flattened results with other columns in your query.

Key functions for semi-structured data include: GET() and GET_PATH() for retrieving values, PARSE_JSON() for converting strings to VARIANT, OBJECT_CONSTRUCT() for creating JSON objects, ARRAY_AGG() for aggregating values into arrays, and TYPEOF() for determining the data type of elements.

Type casting is important when extracting values. Use the double-colon operator (::) to cast VARIANT values to specific types like ::STRING, ::NUMBER, or ::TIMESTAMP.

Snowflake also offers automatic schema detection through the INFER_SCHEMA function, which examines semi-structured files and suggests column definitions. This simplifies the process of creating structured tables from semi-structured sources.

For performance optimization, Snowflake automatically maintains metadata about semi-structured data, enabling efficient pruning during query execution. Creating materialized views or extracting frequently accessed fields into separate columns can further enhance query performance on semi-structured datasets.

The FLATTEN function in Snowflake is a powerful table function designed to handle semi-structured data by converting nested arrays and objects into a relational format that can be easily queried and analyzed.

When working with JSON, ARRAY, or VARIANT data types, you often encounter hierarchical structures where data is nested within arrays or objects. The FLATTEN function takes these complex nested elements and expands them into separate rows, making the data accessible for standard SQL operations.

The basic syntax is: SELECT * FROM TABLE(FLATTEN(input => <column_name>)). The function accepts several parameters including INPUT (the data to flatten), PATH (specifies which element to flatten), OUTER (includes rows even when the input is empty), RECURSIVE (flattens nested structures at all levels), and MODE (specifies whether to flatten objects, arrays, or both).

When FLATTEN processes data, it returns several useful columns: SEQ (sequence number), KEY (key name for objects), INDEX (array position), VALUE (the actual flattened value), PATH (path to the element), and THIS (the element being flattened).

For example, if you have a JSON column containing an array of order items, FLATTEN will create one row per item in that array. This enables you to join this expanded data with other tables, perform aggregations, or apply filters to individual array elements.

The OUTER parameter is particularly valuable when you need to preserve parent rows even if the nested array is empty or null, similar to a LEFT OUTER JOIN behavior.

The RECURSIVE option allows you to flatten deeply nested structures in a single operation, traversing through multiple levels of hierarchy.

Understanding FLATTEN is essential for the SnowPro Core exam as it demonstrates proficiency in handling modern data formats and transforming semi-structured data into queryable results within Snowflake's architecture.

LATERAL joins in Snowflake represent a powerful feature for data transformations that allows you to reference columns from a preceding table in the FROM clause within a subquery or table function. This capability enables row-by-row processing where each row from the left side of the join can influence the evaluation of the right side.

In traditional joins, the right-hand table expression cannot reference columns from the left-hand table. LATERAL removes this restriction, creating a correlated relationship between tables. For each row processed from the left table, the LATERAL subquery or function executes using values from that specific row.

The syntax follows this pattern: SELECT * FROM table1, LATERAL (subquery referencing table1 columns). Snowflake also supports the alternative LATERAL keyword placement before table functions.

Common use cases include:

1. **FLATTEN operations**: LATERAL is implicitly used with FLATTEN to expand semi-structured data like JSON arrays. Each array element becomes a separate row while maintaining access to parent columns.

2. **Table functions**: When calling table functions that need input from each row of another table, LATERAL enables passing column values as parameters.

3. **Complex transformations**: Breaking down nested structures or performing row-specific calculations that depend on values from the outer query.

For the SnowPro Core exam, understand that LATERAL essentially creates a loop-like behavior at the SQL level. The subquery executes once per row from the preceding table, making it ideal for scenarios requiring element-wise expansion or transformation.

Performance considerations matter when using LATERAL joins since they process data row by row conceptually. However, Snowflake's query optimizer handles these operations efficiently through its parallel processing architecture.

Key points to remember: LATERAL enables column references across table expressions, works seamlessly with FLATTEN for semi-structured data handling, and provides essential functionality for complex data transformation workflows in Snowflake's cloud data platform.

PARSE_JSON and JSON functions are essential tools in Snowflake for working with semi-structured data, particularly JSON formatted content. Understanding these functions is crucial for the SnowPro Core Certification exam.

PARSE_JSON is a function that interprets a string input and converts it into a VARIANT data type containing the parsed JSON structure. This allows you to transform raw JSON text stored as VARCHAR into a queryable format. For example, PARSE_JSON('{"name":"John","age":30}') returns a VARIANT object that you can traverse using dot notation or bracket notation to access nested elements.

The syntax is straightforward: PARSE_JSON(string_expression). If the input string is not valid JSON, the function returns NULL. This makes it useful for processing JSON data received from external sources or stored in string columns.

Conversely, the TO_JSON function performs the opposite operation - it converts a VARIANT value back into a JSON-formatted string. This is helpful when you need to export data or prepare JSON output for downstream applications.

Key use cases include:
1. Loading JSON data from stages into tables with VARIANT columns
2. Transforming string columns containing JSON into queryable structures
3. Extracting specific fields from JSON objects using path notation
4. Flattening nested JSON arrays using the FLATTEN function in combination with parsed JSON

When working with these functions, remember that VARIANT columns can store up to 16MB of data per cell. You can query parsed JSON using notation like column_name:key_name or column_name['key_name'] for accessing nested values.

For the certification exam, understand that PARSE_JSON is the primary method for converting JSON strings into Snowflake's native semi-structured format, enabling efficient querying and manipulation of complex hierarchical data structures within your SQL statements.

User-defined functions (UDFs) in Snowflake are custom functions that allow users to extend the built-in functionality of the platform by creating their own reusable code blocks for data transformations. These functions enable developers to encapsulate complex logic that can be called repeatedly within SQL statements, promoting code reusability and maintainability.

Snowflake supports several types of UDFs. SQL UDFs are written using SQL expressions and are ideal for simple transformations and calculations. JavaScript UDFs leverage JavaScript code for more complex logic, including loops, conditional statements, and string manipulations. Python UDFs allow data scientists and analysts to utilize Python libraries and logic within Snowflake. Java UDFs provide enterprise developers the ability to implement functions using Java programming language.

UDFs can be scalar or tabular. Scalar UDFs return a single value for each input row, making them suitable for column-level transformations. User-defined table functions (UDTFs) return multiple rows for each input, which is useful when you need to generate or expand data.

When creating UDFs, users must specify the input parameters, return type, and the function body containing the logic. UDFs can be designated as secure to protect intellectual property by hiding the function definition from unauthorized users.

Key considerations for UDFs include performance implications, as JavaScript and Python UDFs may have overhead compared to native SQL functions. However, they provide flexibility when built-in functions cannot meet specific business requirements.

UDFs are stored in the Snowflake metadata and can be shared across databases and schemas based on granted privileges. They integrate seamlessly with Snowflake's architecture, benefiting from its automatic scaling and resource management capabilities.

For the SnowPro Core Certification, understanding when to use different UDF types, their syntax, security considerations, and how they fit into data transformation workflows is essential for demonstrating proficiency in Snowflake development.

Stored procedures in Snowflake are powerful database objects that allow you to encapsulate business logic, SQL statements, and procedural code into reusable units. They are essential components for data transformations within the Snowflake ecosystem.

Stored procedures in Snowflake are written using JavaScript, Snowflake Scripting (SQL-based), Java, Python, or Scala. They enable you to perform complex operations that go beyond simple SQL queries, including conditional logic, loops, error handling, and transaction management.

Key characteristics of Snowflake stored procedures include:

1. **Language Support**: You can write procedures in JavaScript (most common), SQL Scripting, Java, Python, or Scala, providing flexibility based on your team's expertise.

2. **Owner's Rights vs Caller's Rights**: Procedures can execute with the privileges of either the owner or the caller, controlling access to underlying objects.

3. **Return Values**: Stored procedures can return scalar values, tables, or status indicators to communicate results back to the calling application.

4. **Transaction Control**: Unlike user-defined functions, stored procedures can contain transaction control statements like BEGIN, COMMIT, and ROLLBACK.

5. **Dynamic SQL**: Procedures support dynamic SQL execution, allowing you to construct and execute SQL statements at runtime.

6. **Error Handling**: Built-in try-catch mechanisms enable robust error handling and logging capabilities.

For data transformations, stored procedures are particularly valuable when you need to orchestrate multiple transformation steps, implement complex business rules, or create ETL/ELT pipelines. They can call other procedures, execute tasks conditionally, and manage data movement between stages and tables.

Best practices include using meaningful naming conventions, implementing proper error handling, documenting parameters and return values, and testing thoroughly before deployment. Stored procedures complement other Snowflake features like streams and tasks to build comprehensive data transformation solutions that maintain data quality and consistency across your data warehouse.

JavaScript and SQL User-Defined Functions (UDFs) in Snowflake are powerful tools for extending the platform's native capabilities and performing custom data transformations.

**SQL UDFs** are functions written in SQL that allow you to encapsulate reusable logic. They accept input parameters and return scalar values or tabular results. SQL UDFs are ideal for simple calculations, string manipulations, and business logic that can be expressed using standard SQL syntax. They execute natively within Snowflake's engine, making them efficient for straightforward operations. SQL UDFs support both scalar functions (returning single values) and table functions (returning result sets).

**JavaScript UDFs** provide more flexibility when complex procedural logic is required that cannot be easily expressed in SQL. These functions run within Snowflake's secure JavaScript execution environment. JavaScript UDFs are particularly useful for advanced string parsing, complex mathematical calculations, JSON manipulation, and implementing algorithms that benefit from procedural programming constructs like loops and conditional statements.

**Key Differences:**
- SQL UDFs typically offer better performance for simple operations since they run natively
- JavaScript UDFs provide greater flexibility for complex logic and procedural operations
- JavaScript UDFs can handle sophisticated data parsing scenarios
- SQL UDFs are easier to write and maintain for SQL-proficient users

**Best Practices:**
- Use SQL UDFs when the logic can be expressed in SQL
- Reserve JavaScript UDFs for scenarios requiring procedural complexity
- Consider performance implications when choosing between the two
- Both types support overloading with different parameter signatures

**Security Considerations:**
JavaScript UDFs execute in a sandboxed environment with restricted access to external resources, ensuring data security. Both UDF types inherit the privileges of the calling user.

For the SnowPro Core exam, understanding when to use each type, their syntax differences, and performance characteristics is essential for data transformation questions.

External functions in Snowflake are a powerful feature that enables users to call external API endpoints from within SQL queries. These functions extend Snowflake's native capabilities by allowing integration with external services, custom business logic, or third-party applications hosted outside the Snowflake environment.

External functions work by sending data from Snowflake to a remote service via HTTPS and receiving processed results back. The architecture involves three main components: the external function definition in Snowflake, an API integration object, and the remote service itself (typically hosted on cloud platforms like AWS Lambda, Azure Functions, or Google Cloud Functions).

To create an external function, you must first establish an API integration, which defines the security and connection parameters for the external service. This integration specifies the allowed API endpoints, authentication methods, and role-based access controls. The external function definition then references this integration and maps input parameters to the remote service.

Key characteristics of external functions include:

1. **Scalar and Tabular Returns**: External functions can return scalar values or tabular data depending on implementation.

2. **Batching**: Snowflake automatically batches rows when calling external functions to optimize performance and reduce API calls.

3. **Security**: All communication occurs over HTTPS, and API integrations support various authentication mechanisms including API keys and OAuth.

4. **Synchronous Processing**: External function calls are synchronous, meaning the query waits for the response before proceeding.

5. **Cost Considerations**: Usage involves both Snowflake compute costs and potential charges from the external service provider.

Common use cases include machine learning model inference, data enrichment from external sources, custom transformations using programming languages not native to Snowflake, and integration with enterprise applications.

For the SnowPro Core exam, understand that external functions require proper privileges, API integrations must be configured by administrators, and they provide flexibility for complex data transformation scenarios that cannot be handled through standard SQL operations.

Streams in Snowflake are powerful objects designed for Change Data Capture (CDC), enabling you to track and process data modifications in tables over time. A stream acts as a change tracking mechanism that records Data Manipulation Language (DML) changes including inserts, updates, and deletes made to a source table.

When you create a stream on a table, Snowflake establishes an offset pointer that marks a specific point in time. As transactions modify the underlying table, the stream captures these changes and makes them available for querying. The stream itself does not store actual data; instead, it maintains metadata about what has changed since the last consumption.

Streams contain special metadata columns: METADATA$ACTION indicates whether a row was inserted or deleted, METADATA$ISUPDATE shows if the change was part of an update operation, and METADATA$ROW_ID provides a unique identifier for each row.

There are three types of streams in Snowflake. Standard streams track all DML changes and are the most comprehensive option. Append-only streams capture only insert operations, making them ideal for scenarios where you only need to process new records. Insert-only streams work specifically with external tables.

A key concept is stream staleness. If the retention period for the source table's change tracking data expires before the stream is consumed, the stream becomes stale and unusable. You must ensure regular consumption of stream data to prevent this issue.

Streams are commonly used in ETL pipelines and data transformation workflows. When combined with tasks, they enable automated incremental data processing. You can query a stream like a regular table, and once changes are consumed within a DML transaction, the stream offset advances, showing only subsequent changes.

Streams provide an efficient, cost-effective method for implementing CDC patterns in Snowflake, eliminating the need for timestamp-based comparisons or full table scans to identify modified records.

Tasks in Snowflake are powerful objects designed to schedule and automate SQL statements or stored procedures. They enable users to execute data transformation workflows on a defined schedule, making them essential for ETL/ELT processes and maintaining data pipelines.

A Task can execute a single SQL statement, call a stored procedure, or leverage Snowflake Scripting. Tasks operate using either a cron-based schedule (using standard cron syntax) or a fixed interval specified in minutes. For example, you can schedule a task to run every hour or at specific times like midnight daily.

Tasks support a tree-like dependency structure through parent-child relationships. A root task initiates the workflow, and child tasks execute after their predecessor completes successfully. This creates Directed Acyclic Graphs (DAGs) allowing complex multi-step transformations. Each DAG can have up to 1000 tasks, with a maximum of 100 levels of dependency.

Key properties of Tasks include:
- WAREHOUSE: Specifies the compute resource for execution (or use serverless tasks)
- SCHEDULE: Defines when the task runs
- ALLOW_OVERLAPPING_EXECUTION: Controls whether concurrent runs are permitted
- SUSPEND_TASK_AFTER_NUM_FAILURES: Automatically suspends after specified consecutive failures

Serverless Tasks represent a convenient option where Snowflake manages the compute resources automatically, eliminating the need to specify or manage a warehouse.

Tasks are created in a suspended state by default and must be explicitly resumed using ALTER TASK RESUME. The TASKADMIN role or appropriate privileges are required to manage tasks. You can monitor task execution through the TASK_HISTORY table function in the Information Schema or Account Usage views.

Best practices include setting appropriate error handling, monitoring task runs regularly, and organizing related tasks into logical DAGs. Tasks integrate seamlessly with Streams for change data capture, enabling efficient incremental data processing and transformation workflows in your Snowflake environment.

Task dependencies and DAGs (Directed Acyclic Graphs) are fundamental concepts in Snowflake for orchestrating complex data transformation workflows.

A Task in Snowflake is a scheduled object that executes a single SQL statement or calls a stored procedure. Tasks can be linked together to create sophisticated data pipelines where the completion of one task triggers the execution of subsequent tasks.

Task Dependencies define the relationships between tasks, establishing which tasks must complete before others can begin. When you create a task with the AFTER clause, you specify its predecessor task. For example: CREATE TASK child_task AFTER parent_task AS SELECT... This creates a parent-child relationship where child_task runs only after parent_task completes successfully.

A DAG (Directed Acyclic Graph) represents the entire network of task dependencies. The term 'directed' means tasks flow in one direction from predecessors to successors. 'Acyclic' means there are no circular dependencies - a task cannot eventually depend on itself. The root task sits at the top of the DAG and has no predecessors, while leaf tasks have no successors.

Key characteristics of Snowflake DAGs include:

1. A single root task that initiates the entire workflow on a defined schedule
2. Up to 1000 tasks per DAG
3. Support for multiple predecessors (up to 100) allowing complex branching and merging patterns
4. All tasks in a DAG share the same owner
5. Only the root task has a schedule; dependent tasks execute based on predecessor completion

To activate a DAG, you must resume the root task using ALTER TASK root_task RESUME. Child tasks must also be resumed for execution. The SYSTEM$TASK_DEPENDENTS_ENABLE function can activate all tasks in a DAG simultaneously.

DAGs enable incremental data processing, error handling through task failure notifications, and efficient resource utilization by executing tasks only when predecessors complete successfully. This orchestration capability is essential for building reliable, maintainable data transformation pipelines in Snowflake.

Sequences in Snowflake are schema-level objects that generate unique, sequential numeric values, commonly used for auto-incrementing columns in tables. They provide a reliable way to create surrogate keys and unique identifiers for your data.

To create a sequence, use the CREATE SEQUENCE statement: CREATE SEQUENCE my_sequence START = 1 INCREMENT = 1. The START parameter defines the initial value, while INCREMENT specifies the step between consecutive values.

Sequences offer several key properties. You can set MINVALUE and MAXVALUE to define boundaries, and the CYCLE option allows the sequence to restart after reaching its maximum value. The ORDER option ensures values are generated in strict order, which is important for certain use cases but may impact performance.

To retrieve the next value from a sequence, use the NEXTVAL function: SELECT my_sequence.NEXTVAL. Each call returns a unique value and advances the sequence. You can also reference sequences in INSERT statements to auto-populate ID columns.

When creating tables, you can associate sequences with columns using DEFAULT: CREATE TABLE employees (id INTEGER DEFAULT my_sequence.NEXTVAL, name STRING). This automatically generates unique IDs when rows are inserted.

Snowflake also supports AUTOINCREMENT and IDENTITY as alternatives to sequences. These are defined at the column level: CREATE TABLE products (product_id INTEGER AUTOINCREMENT, product_name STRING). IDENTITY columns are simpler to implement but offer less flexibility than standalone sequences.

Key differences between sequences and AUTOINCREMENT include: sequences can be shared across multiple tables, they allow gaps in numbering if transactions fail, and they provide more control over value generation parameters.

For the SnowPro Core exam, understand that sequences guarantee uniqueness but not consecutive values. Also note that sequence values are not transactional - once generated, a value is consumed even if the transaction rolls back. This behavior ensures high performance in concurrent environments where multiple sessions need unique values simultaneously.