The Grid

The mapping grid is a structured table of your column-level metadata for a specified node. This includes the column names, transformations, data types, source mappings, nullability, default value, and column description. Coalesce uses this metadata when rendering Create and Run templates to produce DDL and DML SQL. The grid provides an easy-to-view format to organize all of the attributes about a node.

Adding Columns

Inherit from Nodes

• Select a node from the Graph or Left Sidebar. Right-click on a node, then select Add Nodes, then the type of node you would like to create.

• Multi-select nodes to be joined from the Graph or Left Sidebar. Right-click on a node, then select Join Nodes, then the type of node you would like to create.

Manually Add Column(s)

• Drag in one or many nodes from the left sidebar node pane.
• Drag in individual columns from the left sidebar column preview pane.
• Manually add a column from the bottom of the grid. From here you will need to assign a column name, data type. Optionally, you can set a transformation, source, nullability, default value, and/or a column-level description.

Transforms

Coalesce supports all transformation functions supported by the underlying database technology — which is currently limited to Snowflake.

📘

What Are Transforms?

Transforms are optional pieces of logic that can be applied to a column to either alter its data, or generate new separate data.

General Transformations

Transformation logic can be authored inline or within the Column Editor. Optionally, Macros may be used to encapsulate logic and to reduce code duplication for frequently used transformations. By default, if no transformation is specified, a column will reference the column specified in the Source column of the Mapping Grid.

🚧

Remember the Data Type!

Set the data type of your column to match the expected output of your transformation!

JSON Parsing

Coalesce makes parsing complex semi-structured JSON easy by providing a helpful JSON parser built-in. To parse a JSON Variant column, right-click the column and select Derive JSON Mappings.

You can read more details about this feature on its main page - JSON Parser.

Bulk Editing Columns

To bulk edit columns, multi-select columns from the mapping grid, then right-click and select Bulk Edit. This will open the Bulk Editor inside the Column Editor. Bulk editing can be performed on a single column attribute, or multiple at once. The sections below describe each of the attributes that are available for bulk editing.

Transform

Description

Any appropriate Snowflake SQL transformations are allowed here.
Jinja, as well as macros, may be used, however, they will be stored in their raw Jinja form. However, the Jinja will be rendered when the node is executed.

Helper Tokens Allowed:

Yes

Data Type

Description
Any appropriate Snowflake SQL data type.
Helper Tokens Allowed:
No

Source

Description

• Operation:
  • Node: Replace column reference with a matching column from the specified node in the Source selector.
  • Node and Column: Replace column reference with the specified node and column in the Source selector.
• Source: Used to specify { Node | Node and Column } to be used in the bulk edit.
  Helper Tokens Allowed:
  Not applicable

Nullable

Description

Helper Tokens Allowed:
Not applicable

Default Value

Description

Helper Tokens Allowed:
Not applicable

Description

Description

Helper Tokens Allowed:
Not applicable

Helper Tokens

These tokens are replaced during a bulk operation to provide column or node context to the operation.

A few notes: The tokens are case-sensitive and space-sensitive. They must be uppercase with no spaces. Also, Helper Tokens are not Jinja variables despite their appearance.

👍

Example using Helper Tokens

Let's say you want to add an IFNULL transformation to several columns.

In this case, you can use {{SRC}} to make this operation quick and easy. First, select the columns from the grid that you wish to modify the transform on. Then, select Transform from the attributes list.

Type transform as such:

NVL( {{SRC}} , 'some other value' )

Finally, click Update All and observe your transformation applied to each column.