Hydrated_Metadata | Coalesce Documentation

columns object[]

Columns map to the columns displayed in the Mapping Grid. Add a source or a Node to populate the columns. There can be multiple columns. A column array can contain multiple column objects.

Array [

idstring

An auto-generated Coalesce ID.

Example: 4c1e4719-b72b-41fe-82f6-7b6311475fdd

namestring

The name of the column.

Example: L_PARTKEY

dataTypestring

This is the data type for the column and by default maps to the source of the Node.

Example: NUMBER(38,0)

descriptionstring

If there is a comment or description or it can be added here.

Example: Column description

nullableboolean

If a column can have null values

defaultValuestring

Default value of a column.

Example: Coalesce

tests object[]

Column level tests for null and unique values. Each test is in it's own object. If there are no tests, shows as an empty array. tests: []

Array [

name

Name of the test. Indicates if the test is Unique or "Null"

Possible values: [Unique, "Null"]

Default value: Null

Example: "Null"

descriptionstring

Description of the test.

Null testing - checks for any null values
Unique value check if value is unique

Example: checks for any null values

continueOnFailureboolean

Specifies if to continue running the pipeline after the related test fails.

runOrderstring

Column level tests run After.

Default value: After

Example: After

templateStringstring

The SQL template for the test that will be executed. These are examples.

Unique

        templateString: |2-

                      SELECT * FROM {{ ref(node.location.name, node.name)}} 
                      WHERE "L_ORDERKEY" IS NULL

Null

        templateString: |2-

                      SELECT "L_ORDERKEY", COUNT(*)
                      FROM {{ ref(node.location.name, node.name)}} 
                      GROUP BY "L_ORDERKEY"
                      HAVING COUNT(*) > 1

Example: \n SELECT * FROM {{ ref(node.location.name, node.name)}} \n WHERE \"L_ORDERKEY\" IS NULL

]

<system-column-name>boolean

System Columns only appear as a column on Node initalization. These are considered Dynamic Column Fields. They are given custom attributes which show in the metadata and are set to true to indicate it's a system column.

Default value: true

isChangeTrackingboolean

If the column is change tracking. isChangeTracking will only appear in the metadata if the value is true. These are considered Dynamic Column Fields.

isBusinessKeyboolean

If the column is a business key. isBusinessKey will only appear in the metadata if the value is true. These are considered Dynamic Column Fields

isSurrogateKeyboolean

This is a system column. If you copy a Node, this will be in the Hydrated Metadata.

isSystemCreateDateboolean

This is a system column. If you copy a Node, this will be in the Hydrated Metadata.

isSystemUpdateDateboolean

This is a system column. If you copy a Node, this will be in the Hydrated Metadata.

isSystemRecordSourceboolean

This is a system column. If you copy a Node, this will be in the Hydrated Metadata.

]

storageLocations object[]

All Workspace Storage Locations. There can be multiple locations. Review the Node metadata to get the location.

Array [

databasestring

Name of database storage location is mapped to.

Example: DOCS_HYDRATED_METADATA

schemastring

Name of schema the Storage Location is mapped to.

Example: DEV

namestring

Name of the Storage Location.

Example: DEV

]

config object

Any of the Generic UI elements and Customizable Core UI Element created will appear here. Review the example Node to understand how the generic elements can appear.

truncateBeforeboolean

This is a toggle button that determines if the data will be truncated before running this Node's INSERT script.

testsEnabledboolean

This toggle determines if user created tests will be run as part of the Node's script. Users can run column and Node level tests.

postSQLstring

The SQL executed after DML stages executed by the Run command.

Example: SELECT * FROM COALESCE

preSQLstring

The SQL executed before DML stages executed by the Run command.

Example: SELECT * FROM COALESCE

insertStrategy

This is a dropdown selector that provides a choice of 3 strategies for combining multiple data sources. This is used when multisourceToggle is enabled.

Possible values: [INSERT, UNIQUE, UNIQUE ALL]

<generic-dropdown>string

A generic [dropdown(/docs/build-your-pipeline/user-defined-nodes/node-config-options#dropdown-selector)] selector. Give it a custom attribute name to appear in the metadata.

<generic-toggle-button>boolean

Toggle between true and false. A generic toggle button. Give it a custom attribute name to appear in the metadata.

<generic-tabular> object

A tabular structure for collecting metadata. Each column in the component can be one of several types including:

dropdownSelector
toggleButton
columnDropdownSelector
textBox
columnSelector Review the metadata example and Node Definition Reference. Give it a custom attribute name to appear in the metadata.

itemsobject[]

Array of generic items added to the tabular object.

For example:

  myTabularConfig:
    items:
      - myToggleButton2: false

<generic-textbox>string

Allows users to enter any combination of characters including SQL statements that can include keyword highlighting. Give it a custom attribute name to appear in the metadata.

Textbox

<generic-dropdown-selector>string

Let uses select from a list of objects. Give it a custom attribute name to appear in the metadata. Drop down selector

<generic-column-dropdown>object

Column dropdown selector. This populates the column information based on the user selection and any custom logic. Review the column object for a full list of available values.

sources object[]

Sources map your columns to their immediate parent nodes. Each source shows:

Which Node the data comes from.
Which column in that Node maps to your column.
Sources only tracks the direct predecessor and not the full Node lineage.

Array [

namestring

Name of the source. If there are multiple sources, each name will represent a different source object.

- name: SRC1
- name: SRC2
- name: SRC3

columns object[]

Node columns

Array [

idstring

An auto-generated Coalesce ID.

Example: 4c1e4719-b72b-41fe-82f6-7b6311475fdd

namestring

The name of the column.

Example: L_PARTKEY

dataTypestring

This is the data type for the column and by default maps to the source of the Node.

Example: STRING

descriptionstring

If there is a comment or description or it can be added here.

Example: Column description

nullableboolean

If a column can have null values

defaultValuestring

Default value of a column.

Example: Coalesce

tests object[]

Column level tests for null and unique values. Each test is in it's own object. If there are no tests, shows as an empty array. tests: []

Array [

name

Name of the test. Indicates if the test is Unique or "Null"

Possible values: [Unique, "Null"]

Default value: Null

Example: "Null"

descriptionstring

Description of the test.

Null testing - checks for any null values
Unique value check if value is unique

Example: checks for any null values

continueOnFailureboolean

Specifies if to continue running the pipeline after the related test fails.

runOrderstring

Column level tests run After.

Default value: After

Example: After

templateStringstring

The SQL template for the test that will be executed. These are examples.

Unique

        templateString: |2-

                      SELECT * FROM {{ ref(node.location.name, node.name)}} 
                      WHERE "L_ORDERKEY" IS NULL

Null

        templateString: |2-

                      SELECT "L_ORDERKEY", COUNT(*)
                      FROM {{ ref(node.location.name, node.name)}} 
                      GROUP BY "L_ORDERKEY"
                      HAVING COUNT(*) > 1

Example: \n SELECT * FROM {{ ref(node.location.name, node.name)}} \n WHERE \"L_ORDERKEY\" IS NULL

]

<system-column-name>boolean

Default value: true

transformstring

Any column transformations ad column transformations.

Example: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)

sourceColumns object[]

The node and column information for the source.

Array [

node object

The Node source of the column. It only shows direct lineage.

idstring

An autogenerated Node ID.

namestring

The Node name.

nodeTypestring

location object

Where the Node is located.

namestring

The name of the Node Storage Location

Example: WORK

descriptionstring

The Node description.

materializationType

The materialization of the Node. Table or View.

Possible values: [table, view]

isMultisourceboolean

If the Node is Multi Source

override object

If the materializationType is set to view, users can override Create SQL allows a user to override the DDL of a view, enabling greater flexibility in view creation. Override Create SQL Toggle.

create object

enabledboolean

If the user has toggled override to true or false.

scriptstring

The custom script written in the Create SQL box.

tests object[]

Any Node level tests.

Array [

namestring

Name of the test.

Example: Test

descriptionstring

Description of the test.

continueOnFailurestring

If the pipeline should keep running, even if the Node test fails.

runOrder

If the test should run before or after the DML operation.

Possible values: [Before, After]

templateStringstring

Any custom SQL that was added here.

Example: SELECT * FROM COALESCE

]

columns object[]

The columns in the source node that it maps to.

Array [

idstring

An auto-generated Coalesce ID.

Example: 4c1e4719-b72b-41fe-82f6-7b6311475fdd

namestring

The name of the column.

Example: L_PARTKEY

dataTypestring

This is the data type for the column and by default maps to the source of the Node.

Example: NUMBER(38,0)

descriptionstring

If there is a comment or description or it can be added here.

Example: Column description

nullableboolean

If a column can have null values

defaultValuestring

Default value of a column.

Example: Coalesce

tests object[]

Column level tests for null and unique values. Each test is in it's own object. If there are no tests, shows as an empty array. tests: []

Array [

name

Name of the test. Indicates if the test is Unique or "Null"

Possible values: [Unique, "Null"]

Default value: Null

Example: "Null"

descriptionstring

Description of the test.

Null testing - checks for any null values
Unique value check if value is unique

Example: checks for any null values

continueOnFailureboolean

Specifies if to continue running the pipeline after the related test fails.

runOrderstring

Column level tests run After.

Default value: After

Example: After

templateStringstring

The SQL template for the test that will be executed. These are examples.

Unique

        templateString: |2-

                      SELECT * FROM {{ ref(node.location.name, node.name)}} 
                      WHERE "L_ORDERKEY" IS NULL

Null

        templateString: |2-

                      SELECT "L_ORDERKEY", COUNT(*)
                      FROM {{ ref(node.location.name, node.name)}} 
                      GROUP BY "L_ORDERKEY"
                      HAVING COUNT(*) > 1

Example: \n SELECT * FROM {{ ref(node.location.name, node.name)}} \n WHERE \"L_ORDERKEY\" IS NULL

]

hashDetails object

Hash details only appear on columns that are hashed columns.

columns object[]

Array [

idstring

namestring

dataTypestring

descriptionstring

nullableboolean

defaultValuestring

testsobject[]

transformstring

sourceColumns object[]

Array [

node object

idstring

namestring

nodeTypestring

location object

namestring

descriptionstring

materializationTypestring

isMultisourceboolean

override object

create object

enabledboolean

scriptstring

testsobject[]

column object

idstring

namestring

dataTypestring

descriptionstring

nullableboolean

defaultValuestring

testsobject[]

]

algorithm

The type of alogorithm used.

Possible values: [SHA1, SHA256, MD5]

]

joinstring

The Join statement for the source.

Example: FROM {{ ref('WORK', 'STG_LINEITEM') }} "STG_LINEITEM"

dependencies object[]

The Nodes here are from the JOIN condition and sources for the columns.

Array [

node object

The Node source of the column. It only shows direct lineage.

idstring

An autogenerated Node ID.

namestring

The Node name.

nodeTypestring

location object

Where the Node is located.

namestring

The name of the Node Storage Location

Example: WORK

descriptionstring

The Node description.

materializationType

The materialization of the Node. Table or View.

Possible values: [table, view]

isMultisourceboolean

If the Node is Multi Source

override object

If the materializationType is set to view, users can override Create SQL allows a user to override the DDL of a view, enabling greater flexibility in view creation. Override Create SQL Toggle.

create object

enabledboolean

If the user has toggled override to true or false.

scriptstring

The custom script written in the Create SQL box.

tests object[]

Any Node level tests.

Array [

namestring

Name of the test.

Example: Test

descriptionstring

Description of the test.

continueOnFailurestring

If the pipeline should keep running, even if the Node test fails.

runOrder

If the test should run before or after the DML operation.

Possible values: [Before, After]

templateStringstring

Any custom SQL that was added here.

Example: SELECT * FROM COALESCE

]

columns object[]

The columns in the source node that it maps to.

Array [

idstring

An auto-generated Coalesce ID.

Example: 4c1e4719-b72b-41fe-82f6-7b6311475fdd

namestring

The name of the column.

Example: L_PARTKEY

dataTypestring

This is the data type for the column and by default maps to the source of the Node.

Example: NUMBER(38,0)

descriptionstring

If there is a comment or description or it can be added here.

Example: Column description

nullableboolean

If a column can have null values

defaultValuestring

Default value of a column.

Example: Coalesce

tests object[]

Column level tests for null and unique values. Each test is in it's own object. If there are no tests, shows as an empty array. tests: []

Array [

name

Name of the test. Indicates if the test is Unique or "Null"

Possible values: [Unique, "Null"]

Default value: Null

Example: "Null"

descriptionstring

Description of the test.

Null testing - checks for any null values
Unique value check if value is unique

Example: checks for any null values

continueOnFailureboolean

Specifies if to continue running the pipeline after the related test fails.

runOrderstring

Column level tests run After.

Default value: After

Example: After

templateStringstring

The SQL template for the test that will be executed. These are examples.

Unique

        templateString: |2-

                      SELECT * FROM {{ ref(node.location.name, node.name)}} 
                      WHERE "L_ORDERKEY" IS NULL

Null

        templateString: |2-

                      SELECT "L_ORDERKEY", COUNT(*)
                      FROM {{ ref(node.location.name, node.name)}} 
                      GROUP BY "L_ORDERKEY"
                      HAVING COUNT(*) > 1

Example: \n SELECT * FROM {{ ref(node.location.name, node.name)}} \n WHERE \"L_ORDERKEY\" IS NULL

]

customSQLstring

When a Node is created as a view, Override Create SQL lets a user enter custom SQL. The SQL entered will appear here.

Example:

{{ stage('Override Create SQL') }}       	CREATE OR REPLACE VIEW {{ ref('WORK', 'STG_SUPPLIER')}} AS (       		SELECT         "S_SUPPKEY" AS "S_SUPPKEY",        "S_NAME" AS "S_NAME",        "S_ADDRESS" AS "S_ADDRESS",        "S_NATIONKEY" AS "S_NATIONKEY",        "S_PHONE" AS "S_PHONE",        "S_ACCTBAL" AS "S_ACCTBAL",        "S_COMMENT" AS "S_COMMENT"        FROM {{ ref('SAMPLE', 'SUPPLIER') }}       	)

]

node object

Contains information about the Node

idstring

An autogenerated Node ID.

namestring

The Node name.

nodeTypestring

location object

Where the Node is located.

namestring

The name of the Node Storage Location

Example: WORK

descriptionstring

The Node description.

materializationType

The materialization of the Node. Table or View.

Possible values: [table, view]

isMultisourceboolean

If the Node is Multi Source

override object

If the materializationType is set to view, users can override Create SQL allows a user to override the DDL of a view, enabling greater flexibility in view creation. Override Create SQL Toggle.

create object

enabledboolean

If the user has toggled override to true or false.

scriptstring

The custom script written in the Create SQL box.

tests object[]

Any Node level tests.

Array [

namestring

Name of the test.

Example: Test

descriptionstring

Description of the test.

continueOnFailurestring

If the pipeline should keep running, even if the Node test fails.

runOrder

If the test should run before or after the DML operation.

Possible values: [Before, After]

templateStringstring

Any custom SQL that was added here.

Example: SELECT * FROM COALESCE

]

thisstring

This refers to the Node. this refers to a templating reference that provides a shorthand way to reference the fully qualified name of the Node being worked on.

Example: "{{ ref_no_link(node.location.name, node.name) }}"

parameters object

Parameters in the metadata represent runtime parameters that can be passed to templates.

<some-parameter>string

<another-parameter>string

Hydrated_Metadata
{
  "columns": [
    {
      "id": "4c1e4719-b72b-41fe-82f6-7b6311475fdd",
      "name": "L_PARTKEY",
      "dataType": "NUMBER(38,0)",
      "description": "Column description",
      "nullable": true,
      "defaultValue": "Coalesce",
      "tests": [
        {
          "name": "\"Null\"",
          "description": "checks for any null values",
          "continueOnFailure": true,
          "runOrder": "After",
          "templateString": "\n            SELECT * FROM {{ ref(node.location.name, node.name)}} \n            WHERE \"L_ORDERKEY\" IS NULL"
        }
      ],
      "<system-column-name>": true,
      "isChangeTracking": true,
      "isBusinessKey": true,
      "isSurrogateKey": true,
      "isSystemCreateDate": true,
      "isSystemUpdateDate": true,
      "isSystemRecordSource": true
    }
  ],
  "storageLocations": [
    {
      "database": "DOCS_HYDRATED_METADATA",
      "schema": "DEV",
      "name": "DEV"
    }
  ],
  "config": {
    "truncateBefore": true,
    "testsEnabled": true,
    "postSQL": "SELECT * FROM COALESCE",
    "preSQL": "SELECT * FROM COALESCE",
    "<generic-dropdown>": "string",
    "<generic-toggle-button>": true,
    "<generic-tabular>": {
      "items": [
        {}
      ]
    },
    "<generic-textbox>": "string",
    "<generic-dropdown-selector>": "string",
    "<generic-column-dropdown>": {}
  },
  "sources": [
    {
      "name": "string",
      "columns": [
        {
          "id": "4c1e4719-b72b-41fe-82f6-7b6311475fdd",
          "name": "L_PARTKEY",
          "dataType": "STRING",
          "description": "Column description",
          "nullable": true,
          "defaultValue": "Coalesce",
          "tests": [
            {
              "name": "\"Null\"",
              "description": "checks for any null values",
              "continueOnFailure": true,
              "runOrder": "After",
              "templateString": "\n            SELECT * FROM {{ ref(node.location.name, node.name)}} \n            WHERE \"L_ORDERKEY\" IS NULL"
            }
          ],
          "<system-column-name>": true,
          "transform": "CAST(CURRENT_TIMESTAMP AS TIMESTAMP)",
          "sourceColumns": [
            {
              "node": {
                "id": "string",
                "name": "string",
                "nodeType": "string",
                "location": {
                  "name": "WORK"
                },
                "description": "string",
                "isMultisource": true,
                "override": {
                  "create": {
                    "enabled": true,
                    "script": "string"
                  }
                },
                "tests": [
                  {
                    "name": "Test",
                    "description": "string",
                    "continueOnFailure": "string",
                    "templateString": "SELECT * FROM COALESCE"
                  }
                ]
              },
              "columns": [
                {
                  "id": "4c1e4719-b72b-41fe-82f6-7b6311475fdd",
                  "name": "L_PARTKEY",
                  "dataType": "NUMBER(38,0)",
                  "description": "Column description",
                  "nullable": true,
                  "defaultValue": "Coalesce",
                  "tests": [
                    {
                      "name": "\"Null\"",
                      "description": "checks for any null values",
                      "continueOnFailure": true,
                      "runOrder": "After",
                      "templateString": "\n            SELECT * FROM {{ ref(node.location.name, node.name)}} \n            WHERE \"L_ORDERKEY\" IS NULL"
                    }
                  ]
                }
              ]
            }
          ],
          "hashDetails": {
            "columns": [
              {
                "id": "string",
                "name": "string",
                "dataType": "string",
                "description": "string",
                "nullable": true,
                "defaultValue": "string",
                "tests": [
                  {}
                ],
                "transform": "string",
                "sourceColumns": [
                  {
                    "node": {
                      "id": "string",
                      "name": "string",
                      "nodeType": "string",
                      "location": {
                        "name": "string"
                      },
                      "description": "string",
                      "materializationType": "string",
                      "isMultisource": true,
                      "override": {
                        "create": {
                          "enabled": true,
                          "script": "string"
                        }
                      },
                      "tests": [
                        {}
                      ]
                    },
                    "column": {
                      "id": "string",
                      "name": "string",
                      "dataType": "string",
                      "description": "string",
                      "nullable": true,
                      "defaultValue": "string",
                      "tests": [
                        {}
                      ]
                    }
                  }
                ]
              }
            ]
          }
        }
      ],
      "join": "FROM {{ ref('WORK', 'STG_LINEITEM') }} \"STG_LINEITEM\"",
      "dependencies": [
        {
          "node": {
            "id": "string",
            "name": "string",
            "nodeType": "string",
            "location": {
              "name": "WORK"
            },
            "description": "string",
            "isMultisource": true,
            "override": {
              "create": {
                "enabled": true,
                "script": "string"
              }
            },
            "tests": [
              {
                "name": "Test",
                "description": "string",
                "continueOnFailure": "string",
                "templateString": "SELECT * FROM COALESCE"
              }
            ]
          },
          "columns": [
            {
              "id": "4c1e4719-b72b-41fe-82f6-7b6311475fdd",
              "name": "L_PARTKEY",
              "dataType": "NUMBER(38,0)",
              "description": "Column description",
              "nullable": true,
              "defaultValue": "Coalesce",
              "tests": [
                {
                  "name": "\"Null\"",
                  "description": "checks for any null values",
                  "continueOnFailure": true,
                  "runOrder": "After",
                  "templateString": "\n            SELECT * FROM {{ ref(node.location.name, node.name)}} \n            WHERE \"L_ORDERKEY\" IS NULL"
                }
              ]
            }
          ]
        }
      ],
      "customSQL": "{{ stage('Override Create SQL') }}       \tCREATE OR REPLACE VIEW {{ ref('WORK', 'STG_SUPPLIER')}} AS (       \t\tSELECT         \"S_SUPPKEY\" AS \"S_SUPPKEY\",        \"S_NAME\" AS \"S_NAME\",        \"S_ADDRESS\" AS \"S_ADDRESS\",        \"S_NATIONKEY\" AS \"S_NATIONKEY\",        \"S_PHONE\" AS \"S_PHONE\",        \"S_ACCTBAL\" AS \"S_ACCTBAL\",        \"S_COMMENT\" AS \"S_COMMENT\"        FROM {{ ref('SAMPLE', 'SUPPLIER') }}       \t)"
    }
  ],
  "node": {
    "id": "string",
    "name": "string",
    "nodeType": "string",
    "location": {
      "name": "WORK"
    },
    "description": "string",
    "isMultisource": true,
    "override": {
      "create": {
        "enabled": true,
        "script": "string"
      }
    },
    "tests": [
      {
        "name": "Test",
        "description": "string",
        "continueOnFailure": "string",
        "templateString": "SELECT * FROM COALESCE"
      }
    ]
  },
  "this": "\"{{ ref_no_link(node.location.name, node.name) }}\"",
  "parameters": {
    "<some-parameter>": "string",
    "<another-parameter>": "string"
  }
}