Skip to main content

Create Or Alter Package

Overview

The package includes Create or Alter node types

Installation

  • Copy the Package ID  @coalesce/snowflake/create-or-alter-package
  • In Coalesce, open the Workspace where you wish to install the package.
  • Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
  • Paste the Package ID, and proceed with the installation process.

Description

Create or Alter package

Create or Alter package includes:

Create or Alter table

The Create or alter table creates table if it doesn’t exist, or alters it according to the table definition.

Create or Alter Node properties

Create Or Alter has two configuration groups:

Create or Alter Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter Table will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes
are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create table Options

OptionsDescription
Create AsChoose 'table' or 'transient table'
Cluster keyTrue/False toggle for clustering: True: Specify clustering
column and optional expressions False: No clustering
Change TrackingToggle to enable or disable change tracking on the table.
Enable Schema EvolutionToggle to enable or disable schema evolution.
Inline ConstraintsToggle to enable inline constraints:True: Specify column name and constraint specification False: No constraints
Out-Of-Line ConstraintsToggle to enable or disable out-of-line constraints.
When enabled, you can define both primary and foreign keys.
Data Retention TimeSet the number of days for data retention for Time Travel actions.
Default DDL CollationSet the default collation specification for the DDL operations.

Insert data Options

OptionsDescription
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
- INSERT: Individual insert for each source
False: Single source node or multiple sources combined using a join.
Truncate BeforeToggle: True/False
This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE
False: Uses INSERT to append data
Enable testsToggle: True/False
Determines if tests are enabled
DistinctToggle: True/False
True: Group by All is invisible. DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible, data grouped by all columns
False: DISTINCT is visible
Order ByToggle: True/False
True: Sort column and sort order drop down are visible and are required to form order by clause.
False: Sort options invisible

Limitations of Create or Alter Table

  • Collation specifications cannot be altered.
  • Setting or unsetting an inline primary key changes the nullability of the column accordingly
  • New columns can only be added to the end of the column list
  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added.WHen you rename a column in Coalesce mapping grid.Ensure to move the same to the end of column list in mapping grid.

Create Or Alter Table Deployment

Create Or Alter Table Initial Deployment

When deployed for the first time into an environment the Create or Alter table node of materialization type table or transient table will execute the below stage:

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table in the target environment

Create Or Alter Table Redeployment

Altering the Tables and Transient Tables

When redeployed with change in target location or change in node name,results in alter of the table

StageDescription
Rename/Move TableAlter table statement is executed to perform the alter operation

Changing Materialization Type

ChangeStages Executed
Table to transient table or vice versaDrop table/transient table
Create or Alter table/transient table

Recreating the Create Or Alter Table

When the Create or Alter table node is redeployed with any changes in table or config changes result in re-creating table

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table in the target environment

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed

Create Or Alter Tables Undeployment

If a Create or Alter table node of materialization type table/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the table in the target environment will be dropped.

This is executed in below stage:

StageDescription
Drop table/transient tableRemoves the table from the environment

Create or alter view

A create or alter view node creates a new view if it doesn’t already exist, or updates the properties of an existing view to match those defined in the statement.

Create or Alter View Node properties

Create Or Alter has two configuration groups:

Create or Alter View Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter Table will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes
are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create view Options

OptionsDescription
SecureTrue/False toggle for secure view: True:creates a secure view False: Normal view
Change TrackingToggle to enable or disable change tracking on the table.
DistinctToggle: True/False
True: Group by All is invisible. DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible, data grouped by all columns
False: DISTINCT is visible
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
- INSERT: Individual insert for each source
False: Single source node or multiple sources combined using a join.

Limitations of Create or Alter View

  • The CREATE OR ALTER VIEW command doesn’t support changing a view definition once a view is created. In Coalesce,it is supported by dropping and recreating the view during redeployment.

Create Or Alter View Deployment

Create Or Alter View Initial Deployment

When deployed for the first time into an environment the Create or Alter view node will execute the below stage:

StageDescription
Create/Alter viewThis will execute a CREATE OR ALTER statement and create a view in the target environment

Create Or Alter View Redeployment

Altering the View

When redeployed with change in target location or change in node name,results in alter of the view

StageDescription
Rename/Move ViewAlter view statement is executed to perform the alter operation

Recreating the Create Or Alter View

When the Create or Alter view node is redeployed with any changes in secure,change_tracking or multi-source config options,create or alter view is executed again.

StageDescription
Create/Alter ViewThis will execute a CREATE OR ALTER statement and create a view in the target environment

Change in view definition

Change in view definition like change in columns,add or drop columns,change in data type ,adding distinct or group by all results in below stages

StageDescription
Create/Alter ViewThis will execute a CREATE OR ALTER statement and create a view in the target environment

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed

Create Or Alter Tables Undeployment

If a Create or Alter view node are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the table in the target environment will be dropped.

This is executed in below stage:

StageDescription
Drop ViewRemoves the table or view from the environment

Create or Alter Dimension

The Create or alter creates dimension table if it doesn’t exist, or alters it according to the table definition.

Create or Alter Dimension Node

Create Or Alter has two configuration groups:

Create or Alter Dimension Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter Dimension Table will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create Table Options

OptionsDescription
Create AsChoose 'table' or 'transient table'
Cluster keyTrue/False toggle for clustering
True: Specify clustering column and optional expressions
False: No clustering
Change TrackingToggle to enable or disable change tracking on the table.
Enable Schema EvolutionToggle to enable or disable schema evolution.
Inline ConstraintsToggle to enable inline constraints
True: Specify column name and constraint specification
False: No constraints
Out-Of-Line ConstraintsToggle to enable or disable out-of-line constraints
When enabled, you can define both primary and foreign keys.
Data Retention TimeSet the number of days for data retention for Time Travel actions.
Available only for materialization type - table
Maximum Data Extension timeSet the number of days for max data extension for Historical data access.
Available only for materialization type - table
Default DDL CollationSet the default collation specification for the DDL operations.

Insert data Options

OptionsDescription
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
False: Single source node or multiple sources combined using a join.
Truncate BeforeToggle: True/False
This determines whether a table will be overwritten each time a task executes.
True: Uses INSERT OVERWRITE
False: Uses INSERT to append data
Enable testsToggle: True/False
Determines if tests are enabled
Business keyRequired column for both Type 1 and Type 2 Dimensions
Note: Geometry and Geography data type columns are not supported as business key columns.
Change trackingRequired column for Type 2. If not chosen, treated as type 1
DistinctToggle: True/False
True: Group by All is invisible.
DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible
Data grouped by all columns
False: DISTINCT is visible
Order ByToggle: True/False
True: Sort column and sort order drop down are visible and are required to form order by clause.
False: Sort options invisible
Insert Zero Key RecordToggle: True/False
Insert Zero Key Record to Dimension
True: Zero Key Record Options enabled.
False: Zero Key Record not added
Zero Key Record OptionsAdd custom zero key record values for :
-Default Surrogate Key
-Default String Value
-Default Date Value (Date Format DD-MM-YYYY)
-Default Timestamp Value (Timestamp Format YYYY-MM-DD HH24:MI:SS.FF)
-Default Boolean Value
Advanced Zero Key Record OptionsToggle: True/False
True: Select Columns and the default value of the column for zero key record
False: Advanced Zero Key Record Options not enabled

Limitations of Create or Alter Dimension Table

  • Collation specifications cannot be altered.
  • Setting or unsetting an inline primary key changes the nullability of the column accordingly
  • New columns can only be added to the end of the column list
  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added. When you rename a column in Coalesce mapping grid, ensure to move the same to the end of column list in mapping grid.
  • A column's default value can only be set during its initial creation using a CREATE OR ALTER statement; re-running the statement with a different default value will result in an error and will not update/alter/drop the existing default or add a new default.

Create Or Alter Dimension Table Deployment

Create Or Alter Dimension Table Initial Deployment

When deployed for the first time into an environment the Create or Alter table node of materialization type table or transient table will execute the below stage:

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table/transient table in the target environment

Create Or Alter Dimension Table Redeployment

Altering the Tables and Transient Tables

When redeployed with change in target location or change in node name, results in alter of the table

StageDescription
Rename/Move TableAlter table statement is executed to perform the alter operation

Changing Materialization Type

ChangeStages Executed
Table to transient table or vice versaDrop table/transient table
Create or Alter table/transient table

Recreating the Create Or Alter Table

When the Create or Alter table node is redeployed with any changes in table or config changes result in re-creating table

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table in the target environment

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment, then no stages are executed

Create Or Alter Tables Undeployment

If a Create or Alter table node of materialization type table/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the table in the target environment will be dropped.

This is executed in below stage:

StageDescription
Drop table/transient tableRemoves the table from the environment

Create or Alter Fact

The Create or alter creates fact table if it doesn’t exist, or alters it according to the table definition.

Create or Alter Fact Node

Create Or Alter has two configuration groups:

Create or Alter Fact Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter fact Table will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create Table Options

OptionsDescription
Create AsChoose 'table' or 'transient table'
Cluster keyTrue/False toggle for clustering
True: Specify clustering column and optional expressions
False: No clustering
Change TrackingToggle to enable or disable change tracking on the table.
Enable Schema EvolutionToggle to enable or disable schema evolution.
Inline ConstraintsToggle to enable inline constraints
True: Specify column name and constraint specification
False: No constraints
Out-Of-Line ConstraintsToggle to enable or disable out-of-line constraints
When enabled, you can define both primary and foreign keys.
Data Retention TimeSet the number of days for data retention for Time Travel actions.
Available only for materialization type - table
Maximum Data Extension timeSet the number of days for max data extension for Historical data access.
Available only for materialization type - table
Default DDL CollationSet the default collation specification for the DDL operations.

Insert Data Options

OptionsDescription
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
False: Single source node or multiple sources combined using a join.
Truncate BeforeToggle: True/False
This determines whether a table will be overwritten each time a task executes.
True: Uses INSERT OVERWRITE
False: Uses INSERT to append data
Enable testsToggle: True/False
Determines if tests are enabled
Business keyRequired column for Fact table creation.
Note: Geometry and Geography data type columns are not supported as business key columns.
DistinctToggle: True/False
True: Group by All is invisible.
DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible
Data grouped by all columns
False: DISTINCT is visible
Order ByToggle: True/False
True: Sort column and sort order drop down are visible and are required to form order by clause.
False: Sort options invisible

Limitations of Create or Alter Fact Table

  • Collation specifications cannot be altered.
  • Setting or unsetting an inline primary key changes the nullability of the column accordingly
  • New columns can only be added to the end of the column list
  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added. When you rename a column in Coalesce mapping grid, ensure to move the same to the end of column list in mapping grid.
  • A column's default value can only be set during its initial creation using a CREATE OR ALTER statement; re-running the statement with a different default value will result in an error and will not update/alter/drop the existing default or add a new default.

Create Or Alter Fact Table Deployment

Create Or Alter Fact Table Initial Deployment

When deployed for the first time into an environment the Create or Alter table node of materialization type table or transient table will execute the below stage:

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table/transient table in the target environment

Create Or Alter Fact Table Redeployment

Altering the Tables and Transient Tables

When redeployed with change in target location or change in node name, results in alter of the table

StageDescription
Rename/Move TableAlter table statement is executed to perform the alter operation

Changing Materialization Type

ChangeStages Executed
Table to transient table or vice versaDrop table/transient table
Create or Alter table/transient table

Recreating the Create Or Alter Table

When the Create or Alter table node is redeployed with any changes in table or config changes result in re-creating table

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table in the target environment

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment, then no stages are executed

Create Or Alter Tables Undeployment

If a Create or Alter table node of materialization type table/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the table in the target environment will be dropped.

This is executed in below stage:

StageDescription
Drop table/transient tableRemoves the table from the environment

Create or Alter Persistent Stage

The Create or alter creates Persistent Stage table if it doesn’t exist, or alters it according to the table definition.

Create or Alter Persistent Stage Node

Create Or Alter has two configuration groups:

Create or Alter Persistent Stage Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter Persistent Stage Table will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create Table Options

OptionsDescription
Create AsChoose 'table' or 'transient table'
Cluster keyTrue/False toggle for clustering
True: Specify clustering column and optional expressions
False: No clustering
Change TrackingToggle to enable or disable change tracking on the table.
Enable Schema EvolutionToggle to enable or disable schema evolution.
Inline ConstraintsToggle to enable inline constraints
True: Specify column name and constraint specification
False: No constraints
Out-Of-Line ConstraintsToggle to enable or disable out-of-line constraints
When enabled, you can define both primary and foreign keys.
Data Retention TimeSet the number of days for data retention for Time Travel actions.
Available only for materialization type - table
Maximum Data Extension timeSet the number of days for max data extension for Historical data access.
Available only for materialization type - table
Default DDL CollationSet the default collation specification for the DDL operations.

Insert Data Options

OptionsDescription
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
False: Single source node or multiple sources combined using a join.
Truncate BeforeToggle: True/False
This determines whether a table will be overwritten each time a task executes.
True: Uses INSERT OVERWRITE
False: Uses INSERT to append data
Enable testsToggle: True/False
Determines if tests are enabled
Business keyRequired column for both Type 1 and Type 2.
Note: Geometry and Geography data type columns are not supported as business key columns.
Change trackingRequired column for Type 2. If not chosen, treated as type 1
DistinctToggle: True/False
True: Group by All is invisible.
DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible
Data grouped by all columns
False: DISTINCT is visible
Order ByToggle: True/False
True: Sort column and sort order drop down are visible and are required to form order by clause.
False: Sort options invisible

Limitations of Create or Alter Persistent Stage Table

  • Collation specifications cannot be altered.
  • Setting or unsetting an inline primary key changes the nullability of the column accordingly
  • New columns can only be added to the end of the column list
  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added. When you rename a column in Coalesce mapping grid, ensure to move the same to the end of column list in mapping grid.
  • A column's default value can only be set during its initial creation using a CREATE OR ALTER statement; re-running the statement with a different default value will result in an error and will not update/alter/drop the existing default or add a new default.

Create Or Alter Persistent Stage Table Deployment

Create Or Alter Persistent Stage Table Initial Deployment

When deployed for the first time into an environment the Create or Alter table node of materialization type table or transient table will execute the below stage:

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table/transient table in the target environment

Create Or Alter Persistent Stage Table Redeployment

Altering the Tables and Transient Tables

When redeployed with change in target location or change in node name, results in alter of the table

StageDescription
Rename/Move TableAlter table statement is executed to perform the alter operation

Changing Materialization Type

ChangeStages Executed
Table to transient table or vice versaDrop table/transient table
Create or Alter table/transient table

Recreating the Create Or Alter Table

When the Create or Alter table node is redeployed with any changes in table or config changes result in re-creating table

StageDescription
Create/Alter table/transient tableThis will execute a CREATE OR ALTER statement and create a table in the target environment

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment, then no stages are executed

Create Or Alter Tables Undeployment

If a Create or Alter table node of materialization type table/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the table in the target environment will be dropped.

This is executed in below stage:

StageDescription
Drop table/transient tableRemoves the table from the environment

Create Or Alter Task

The Create or Alter Task creates task if it doesn’t exist, or alters it according to the task definition.

Create or Alter Task Node

Create Or Alter Task node has two or three configuration groups depending on config options selected:

Create or Alter Task Node Properties

PropertyDescription
Storage Location(Required) Storage Location where the Create Or Alter Task will be created
Node Type(Required) Name of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed/redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create Table Options

OptionsDescription
Cluster keyTrue/False toggle for clustering
True: Specify clustering column and optional expressions
False: No clustering
Change TrackingToggle to enable or disable change tracking on the table.
Enable Schema EvolutionToggle to enable or disable schema evolution.
Inline ConstraintsToggle to enable inline constraints
True: Specify column name and constraint specification
False: No constraints
Out-Of-Line ConstraintsToggle to enable or disable out-of-line constraints
When enabled, you can define both primary and foreign keys.
Data Retention TimeSet the number of days for data retention for Time Travel actions.
Available only for materialization type - table
Maximum Data Extension timeSet the number of days for max data extension for Historical data access.
Available only for materialization type - table
Default DDL CollationSet the default collation specification for the DDL operations.

Insert Data Options

OptionsDescription
Development ModeTrue / False toggle that determines whether a task will be created or if the SQL to be used in the task will execute as DML as a Run action.
True - A table will be created and SQL will execute as a Run action
False - After testing the SQL as a Run action, setting to false will wrap SQL in a task with specified Scheduling Options
Multi SourceToggle: True/False
Implementation of SQL UNIONs
True: Combine multiple sources in a single node
True Options:
- UNION: Combines with duplicate elimination
- UNION ALL: Combines without duplicate elimination
False: Single source node or multiple sources combined using a join.
Truncate BeforeToggle: True/False
This determines whether a table will be overwritten each time a task executes.
True: Uses INSERT OVERWRITE
False: Uses INSERT to append data
DistinctToggle: True/False
True: Group by All is invisible.
DISTINCT data is chosen for processing.
False: Group by All is visible.
Group by AllToggle: True/False
True: DISTINCT is invisible
Data grouped by all columns
False: DISTINCT is visible
Order ByToggle: True/False
True: Sort column and sort order drop down are visible and are required to form order by clause.
False: Sort options invisible

Task Scheduling Options

OptionDescription
Scheduling ModeChoose compute type:
- Warehouse Task: User managed warehouse executes tasks
- Serverless Task: Uses serverless compute
When Source Stream has Data FlagTrue/False toggle to check for stream data
True - Only run task if source stream has capture change data
False - Run task on schedule regardless of whether the source stream has data. If the source is not a stream should set this to false.
Multiple Stream has Data LogicAND/OR logic when multiple streams (visible if Stream has Data Flag is true)
AND - If there are multiple streams task will run if all streams have data
OR - If there are multiple streams task will run if one or more streams has data
Select WarehouseVisible if Scheduling Mode is set to Warehouse Task. Enter the name of the warehouse you want the task to run on without quotes.
Select initial serverless sizeVisible when Scheduling Mode is set to Serverless Task.
Select the initial compute size on which to run the task. Snowflake will adjust size from there based on target schedule and task run times.
Task ScheduleChoose schedule type:
- Minutes - Specify interval in minutes. Enter a whole number from 1 to 11520 which represents the number of minutes between task runs.
- Cron - Uses Cron expressions. Specifies a cron expression and time zone for periodically running the task. Supports a subset of standard cron utility syntax.
- Predecessor - Specify dependent tasks
Enter predecessor tasks separated by a commaVisible when Task Schedule is set to Predecessor.
One or more task names that precede the task being created in the current node. Task names are case sensitive, should not be quoted and must exist in the same schema in which the current task is being created. If there are multiple predecessor task separate the task names using a comma and no spaces.
Root task nameVisible when Task Schedule is set to Predecessor.
Name of the root task that controls scheduling for the DAG of tasks. Task names are case sensitive, should not be quoted and must exist in the same schema in which the current task is being created. If there are multiple predecessor task separate the task names using a comma and no spaces.
User Task TimeoutSpecifies the time limit on a single run of the task before it times out (in milliseconds)
Values: 0 - 604800000 (7 days). A value of 0 specifies that the maximum timeout value is enforced

Create or Alter Task Deployment

Create or Alter Task Deployment Parameters

The Create or Alter Task includes an environment parameter that allows you to specify a different warehouse used to run a task in different environments.

The parameter name is targetTaskWarehouse and the default value is DEV ENVIRONMENT.

When set to DEV ENVIRONMENT the value entered in the Scheduling Options config Select Warehouse on which to run the task will be used when creating the task.

{
"targetTaskWarehouse": "DEV ENVIRONMENT"
}

When set to any value other than DEV ENVIRONMENT the node will attempt to create the task using a Snowflake warehouse with the specified value.

For example, with the below setting for the parameter in a QA environment, the task will execute using a warehouse named compute_wh.

{
"targetTaskWarehouse": "compute_wh"
}

Create or Alter Task Initial Deployment

When deployed for the first time into an environment the Create or Alter Task node will execute the following stages:

For tasks without predecessors:

StageDescription
Create Target TableCreates table that will be loaded by the task
Create TaskCreates task that will load the target table on schedule
Resume TaskResumes the task so it runs on schedule

For tasks with predecessors:

StageDescription
Create Target TableCreates table that will be loaded by the task
Suspend Root TaskSuspends root task for DAG modification
Create TaskCreates task that will load the target table on schedule

If a task is part of a DAG of tasks, the DAG needs to include a node type called "Task DAG Resume Root." This node will resume the root node once all the dependent tasks have been created as part of a deployment.

Create or Alter Task Redeployment

After initial deployment, changes in task schedule, warehouse, or scheduling options will result in a SUSPEND, CREATE or ALTER TASK, RESUME

For tasks without predecessors:

StageDescription
Suspend TaskSuspend task
Create TaskCreate Or Alter task with new schedule
Resume TaskResumes task with new schedule

For tasks with predecessors:

StageDescription
Suspend Root TaskSuspends root task for DAG modification
Create TaskCreate Or Alter task with new schedule

Create or Alter Task Altering Tables

Subsequent deployments with changes in table like add or drop column and change in data type will result in an ALTER table statement followed by CREATE OR ALTER TASK AND RESUME TASK statements being issued.

StageDescription
Change Column Attributes/Delete Column/Add Column/Change table descriptionCreate or Alter table statement is executed to perform the alter operation.
Suspend TaskSuspend task
Create TaskCreate Or Alter task with new schedule
Resume TaskResumes task with new schedule

Create or Alter Task Undeployment

If a Create or Alter Task node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then all objects created by the node in the target environment will be dropped.

For tasks without predecessors:

StageDescription
Drop TableDrop the table originally created to be loaded by the task.
Drop Current TaskDrop the task

For tasks with predecessors:

StageDescription
Drop TableDrop the table
Suspend Root TaskRoot task needs to be put into a suspended state.
Drop TaskDrops the task

If a task is part of a DAG of tasks the DAG needs to include a node type called Task Dag Resume Root. This node will resume the root node once all the dependent tasks have been created as part of a deployment.

Limitations of Create or Alter Task

  • Collation specifications cannot be altered.
  • Setting or unsetting an inline primary key changes the nullability of the column accordingly
  • New columns can only be added to the end of the column list
  • Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added. When you rename a column in Coalesce mapping grid, ensure to move the same to the end of column list in mapping grid.
  • A column's default value can only be set during its initial creation using a CREATE OR ALTER statement; re-running the statement with a different default value will result in an error and will not update/alter/drop the existing default or add a new default.
  • All Task nodes, predecessor and root should be present in same schema.
  • Renaming a task isn’t supported. Instead it drops old task and recreates the task with new name and schedule specified.

Create Or Alter DAG Root Task

The Coalesce Create Or Alter DAG Root Task UDN is a node that helps to create/alter a standalone root task.

The root task should have a defined schedule that initiates a run of the DAG. Each of the other tasks has at least one defined predecessor to link the tasks in the DAG. A child task runs only after all of its predecessor tasks run successfully to completion.

Tasks can also be used independently to generate periodic reports by inserting or merging rows into a report table or perform other periodic work.

More information about Tasks can be found in Snowflake's Introduction to tasks.

Create Or Alter DAG Root Task Node Configuration

The Create Or Alter DAG Root Task node has two configuration groups:

Create Or Alter DAG Root Task Node Properties

PropertyDescription
Storage LocationStorage Location where the Stream will be created
Node TypeName of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed / redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create Or Alter DAG Root Task Scheduling Options

OptionDescription
Scheduling ModeChoose compute type:
- Warehouse Task - User managed warehouse executes tasks
- Serverless Task - Uses serverless compute
Select WarehouseVisible if Scheduling Mode is set to Warehouse Task.
Name of warehouse to run task on without quotes
Select initial serverless sizeVisible when Scheduling Mode is set to Serverless Task
Initial compute size for serverless tasks. Snowflake will adjust size from there based on target schedule and task run times.
Task ScheduleChoose schedule type:
- Minutes - Specify interval in minutes
- Cron - Use cron expression
Enter root task SQLThe SQL statement to be run when a standalone root task executes
User Task TimeoutSpecifies the time limit on a single run of the task before it times out (in milliseconds)
Values: 0 - 604800000 (7 days). A value of 0 specifies that the maximum timeout value is enforced

Create Or Alter DAG Root Task Deployment

Create Or Alter DAG Root Task Deployment Parameters

The parameter name is targetTaskWarehouse and the default value is DEV ENVIRONMENT.

When set to DEV ENVIRONMENT the value entered in the Scheduling Options config Select Warehouse on which to run task will be used when creating the task.

{
"targetTaskWarehouse": "DEV ENVIRONMENT"
}

When set to any value other than DEV ENVIRONMENT the node will attempt to create the task using a Snowflake warehouse with the specified value.

For example, with the below setting for the parameter in a QA environment, the task will execute using a warehouse named compute_wh.

{
"targetTaskWarehouse": "compute_wh"
}

Create Or Alter DAG Root Task Initial Deployment

When deployed for the first time into an environment, the following stages execute:

StageDescription
Create Root TaskCreates task that will execute Root SQL on schedule

If a task is part of a DAG of tasks, the DAG needs to include a node type called Create DAG Root Resume Task. This node will resume the root node once all the dependent tasks have been created as part of a deployment.

Create Or Alter DAG Root Task Redeployment

After the Task has deployed for the first time into a target environment, subsequent deployments will execute two stages:

StageDescription
Suspend Root TaskSuspends root task
Create Root TaskRecreates root task

Create Or Alter DAG Root Task Undeployment

When a Create Or Alter DAG Root Task node is deleted, two stages are executed:

StageDescription
Suspend Root taskSuspends root task
Drop current taskRemoves the task

Create DAG Root Resume Task

The Coalesce Create DAG Root Resume Task UDN is a node type that helps to resume the root task and its dependents or child tasks. Recursively resumes all dependent tasks tied to a specified root task in a DAG using the root task name specified.

The root task should have a defined schedule that initiates a run of the DAG. Each of the other tasks has at least one defined predecessor to link the tasks in the DAG. A child task runs only after all of its predecessor tasks run successfully to completion.

Tasks can also be used independently to generate periodic reports by inserting or merging rows into a report table or perform other periodic work.

More information about Tasks can be found in Snowflake's Introduction to tasks.

Create DAG Root Resume Task Node Configuration

The Task DAG Resume Root node has two configuration groups:

Create DAG Root Resume Task Node Properties

PropertyDescription
Storage LocationStorage Location where the Stream will be created
Node TypeName of template used to create node objects
DescriptionA description of the node's purpose
Deploy EnabledIf TRUE the node will be deployed / redeployed when changes are detected
If FALSE the node will not be deployed or will be dropped during redeployment

Create DAG Root Resume Task Scheduling Options

OptionDescription
Enter root task nameName of the root task to be resumed - recursively resumes all dependent tasks tied to this specified root task

Create DAG Root Resume Task Deployment

Create DAG Root Resume Task Initial Deployment

When deployed for the first time into an environment the following stage executes:

StageDescription
Try Enable Root TaskResumes root task and all its dependents

Create DAG Root Resume Task Undeployment

StageDescription
Metadata UpdatesMetadata Updates

Code

Create or Alter table Code

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create or Alter view Code

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create or Alter Dimension Code

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create or Alter Fact Code

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create or Alter Persistent Stage Code

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create or Alter Task

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2
Run Templaterun.sql.j2

Create Or Alter DAG Root Task

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2

Create DAG Root Resume Task

ComponentLink
Node definitiondefinition.yml
Create Templatecreate.sql.j2

Versions

Available versions of the package.

Version #Release DateNotes
1.2.0August 11, 2025
 Added 3 new node types - Create or Alter Task, DAG Root and Resume Task 
1.1.0July 30, 2025
 3 New Node types Added 
1.0.1March 04, 2025
 Redeployment fixes for Create package 
1.0.0January 28, 2025
 Create or Alter table, Create or Alter view node types added