Bigquery Base Node Types

‹ View all packages

Package ID: @coalesce/bigquery/bigquery-base-node-types

Supported Platform:

Latest Version: 1.0.0 (January 28, 2026)

dimensionfactpersistent stagework

Overview

Coalesce base Node Types to construct facts and dimensions.

Installation

1. Copy the Package ID: @coalesce/bigquery/bigquery-base-node-types
2. In Coalesce, open the Workspace where you wish to install the package.
3. Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
4. Paste the Package ID, and proceed with the installation process.

Description

Coalesce-BigQuery - Base Node Types

Brief Summary

These Node Types transforms raw data into a Single Source of Truth, driving strategic value through four specialized architectural layers:

• Data Preparation & History (Work & Persistent Stage): All data begins in the Work and Persistent Stage areas. Think of this as our quality control and storage hub. Here, raw data is cleaned and organized.

• Business Context & Events (Dimension): To make sense of numbers, we need context. Dimension nodes provide the "who, what, where, and why" (e.g., Customer Details, Product Types, Store Locations). More importantly, we use these stages to keep a "historical memory" of the business, tracking how information—like a customer’s address or a product’s category—changes over time, ensuring we never lose sight of our past performance.

• Performance Metrics (Fact): The Fact nodes are the heartbeat of our reporting. These store the quantitative "how much" of the business—such as total revenue, costs, and profit margins. By combining these facts with our Dimensions, leadership can see exactly how specific regions, products, or time periods are performing.

• Simplified Access (View): Finally, Views act as a user-friendly window into this complex system. Instead of navigating technical tables, business users interact with Views that have been tailored for specific needs—providing secure, easy-to-read, and high-speed access to the exact data required for day-to-day decision-making.

---

The Coalesce Base Node Types Package includes:

• Work
• Persistent Stage
• Dimension
• Fact
• View
• Code

---

Work

The Coalesce work node is a versatile node that allows you to develop and deploy a Work table/view in Google BigQuery.

A Work node serves as an intermediary object and is commonly employed to store raw data before undergoing the crucial phases of transformation and loading into the main tables of the data warehouse.

This pivotal step ensures that the raw data is processed and structured effectively.

Work Node Configuration

The Work node type has two configuration groups:

• Node Properties

• Options

  

  Work Node Properties

Property	Description
Storage Location	Storage Location where the WORK will be created
Node Type	Name of template used to create node objects
Deploy Enabled	If 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

Work Options

You can create the node as:

• Table
• View

Work Options Table

Property	Description
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination -False: Single source node or multiple sources combined using a join
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True: Truncate table stage gets executed False: Table is appended with data load
Enable tests	Toggle: True/False Determines if column/node data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Work Options View

Setting	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible

Work Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

📘 Specify Group By Clauses

You should specify group by clause in this space if you are not opting for the group by all provided in OPTIONS config.

Work Deployment

Work Initial Deployment

When deployed for the first time into an environment the Work node of materialization type table will execute the below stage:

Stage	Description
Create Work Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment
Create Work View	This will execute a CREATE OR REPLACE statement and create a view in the target environment

Work Redeployment

After the WORK node with materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the WORK Table or recreating the WORK table.

Altering the Work Tables

A few types of column or table changes will result in an ALTER statement to modify the Work Table in the target environment, whether these changes are made individually or all together:

• Changing table names
• Dropping existing columns
• Altering column data types
• Adding new columns

The following stages are executed:

Stage	Description
Clone Table	Creates an internal table
Rename Table| Alter Column | Delete Column | Add Column | Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Metadata Update for the Work Tables

If any of the following change are detected, metadata update stage executes.

• Join clause
• Adding transformation
• Changes in configuration like adding distinct or group by

One of the following stages are executed:

Stage	Description
Making metadata updates	Refreshes metadata

Recreating the Work Views

The subsequent deployment of Work node of materialization type view with changes in view definition, adding table description or renaming view results in deleting the existing view and recreating the view.

The following stages are executed:

Stage	Description
Delete View	Dropping existing view
Create View	Creates new view with updated definition

Work Undeployment

If a Work Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the WorkTable in the target environment will be dropped.

This is executed in below stage:

Stage	Description
Delete Table	Drops the existing Work Table from target environment

If a Work Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the WorkView in the target environment will be dropped.

The stage executed:

Stage	Description
Delete View	Drops the existing Work view from target environment

---

Persistent Stage

The Coalesce Persistent Stage Nodes element, serving as an intermediary object, is frequently utilized to maintain data persistence across multiple execution cycles.

It plays a crucial role in tracking the historical changes of columns linked to business keys.

This functionality is particularly beneficial when the objective is to retain raw data for prolonged durations.

Persistent Stage Node Configuration

The Persistent node type has two configuration groups:

• Node Properties

• Options

  

Persistent Stage Node Properties

Property	Description
Storage Location	Storage Location where the PStage will be created
Node Type	Name of template used to create node objects
Deploy Enabled	If 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

Persistent Stage Options

Option	Description
Create As	Table is the only option at this time
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Business key	Required column for both Type 1 and Type 2.
Change tracking	Required column for Type 2
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is appended with data load
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Persistent Stage Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

📘 Specify Group By Clause

You should specify group by clause in this space if you are not opting for the group by all provided in OPTIONS config.

Persistent Stage Deployment

Persistent Stage Initial Deployment

When deployed for the first time into an environment the Persistent node will execute the below stage:

Stage	Description
Create Persistent Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment

Persistent Stage Redeployment

After the Persistent node has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Persistent Table or recreating the Persistent table.

Altering the Persistent Tables

A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:

• Changing table names
• Dropping existing columns
• Altering column data types
• Adding new columns

The following stages are executed:

Stage	Description
Clone Table	Creates an internal table
Rename Table| Alter Column | Delete Column | Add Column | Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Metadata Update for the Persistent Tables

If any of the following change are detected, metadata update stage executes.

• Join clause
• Adding transformation
• Changes in configuration like adding distinct or group by

One of the following stages are executed:

Stage	Description
Making metadata updates	Refreshes metadata

Persistent Stage Undeployment

If a Persistent Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Persistent Table in the target environment will be dropped.

This is executed in below stage:

Stage	Description
Delete Table	Drops table
Drop View	Drops view

---

Dimension

The Coalesce Dimension UDN is a versatile node that allows you to develop and deploy a Dimension table in Google BigQuery.

A dimension table or dimension entity is a table or entity in a star, snowflake, or starflake schema that stores details about the facts. Dimension tables describe the different aspects of a business process.

Dimension Node Configuration

The Dimension node type has two configuration groups:

• Node Properties

• Options

  

Dimension Node Properties

Property	Description
Storage Location	Storage Location where the Dimension will be created
Node Type	Name of template used to create node objects
Deploy Enabled	If 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

Dimension Options

Dimension Table Options

Options	Description
Create As	Table or View
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Business key	Required column for both Type 1 and Type 2 Dimensions
Change tracking	Required column for Type 2 Dimension
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is appended with data load
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Dimension View Options

Options	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Business key	Required column for both Type 1 and Type 2 Dimensions
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible

Dimension Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

📘 Specify Group By Clause

You should specify group by clause in this space if you are not opting for the group by all provided in OPTIONS config.

Dimension Deployment

Dimension Initial Deployment

When deployed for the first time into an environment the Dimension node of materialization type table will execute the Create Dimension Table stage.

Stage	Description
Create Dimension Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment
Create Dimension View	This will execute a CREATE OR REPLACE statement and create a view in the target environment

Dimension Redeployment

After the Dimension node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Dimension Table or recreating the Dimension table.

Altering the Dimension Tables

A few types of column or table changes will result in an ALTER statement to modify the Work Table in the target environment, whether these changes are made individually or all together:

• Changing table names
• Dropping existing columns
• Altering column data types
• Adding new columns

The following stages are executed:

Stage	Description
Clone Table	Creates an internal table
Rename Table| Alter Column | Delete Column | Add Column | Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Metadata Update for the Dimension Tables

If any of the following change are detected, metadata update stage executes.

• Join clause
• Adding transformation
• Changes in configuration like adding distinct or group by

One of the following stages are executed:

Stage	Description
Making metadata updates	Refreshes metadata

Recreating the Dimension Views

Any of the following changes to views will result in deleting and recreating the Dimension view.

• View defintion
• Adding table description
• Renaming view results

Dimension Undeployment

If a Dimension Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Dimension Table in the target environment will be dropped.

This is executed in below stage:

Stage	Description
Delete Table	Target table in Google BigQuery is dropped

If a Dimension Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Dimension View in the target environment will be dropped.

Stage	Description
Delete View	Drops the existing Dimension view from target environment.

---

Fact

The Coalesce Fact UDN is a versatile node that allows you to develop and deploy a Fact table in Google BigQuery.

A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit. Fact tables and entities aggregate measures, or the numerical data of a business.

Fact Node Configuration

The Fact node has two configuration groups:

• Node Properties
• Options

Fact Node Properties

Properties	Description
Storage Location	Storage Location where the Fact will be created
Node Type	Name of template used to create node objects
Deploy Enabled	If 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

Fact Options

Options	Description
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Business key	Required column for Fact table creation. Note: Geometry and Geography data type columns are not supported as business key columns.
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is appended with data load
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Fact View

Setting	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Enable tests	Toggle: True/False Determines if node/columns data quality tests are enabled
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible

Fact Joins

Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the UI.

📘 Specify Group By Clause

You should specify group by clause in this space if you are not opting for the group by all provided in OPTIONS config.

Fact Deployment

Fact Initial Deployment

When deployed for the first time into an environment the Fact node of materialization type table will execute the Create Fact Table stage.

Stage	Description
Create Fact Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment
Create Fact View	This will execute a CREATE OR REPLACE statement and create a view in the target environment

Fact Redeployment

After the Fact node has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Fact Table or recreating the Fact table.

Altering the Fact Tables

A few types of column or table changes will result in an ALTER statement to modify the Work Table in the target environment, whether these changes are made individually or all together:

• Changing table names
• Dropping existing columns
• Altering column data types
• Adding new columns

The following stages are executed:

Stage	Description
Clone Table	Creates an internal table
Rename Table| Alter Column | Delete Column | Add Column | Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Metadata Update for the Fact Tables

If any of the following change are detected, metadata update stage executes.

• Join clause
• Adding transformation
• Changes in configuration like adding distinct or group by

One of the following stages are executed:

Stage	Description
Making metadata updates	Refreshes metadata

Recreating the Fact Views

The subsequent deployment of Work node of materialization type view with changes in view definition, adding table description or renaming view results in deleting the existing view and recreating the view.

The following stages are executed:

Stage	Description
Delete View	Removes existing view
Create View	Creates new view with updated definition

Fact Undeployment

If a Fact Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Fact Table in the target environment will be dropped.

This is executed in below stage:

Stage	Description
Delete Table	Target table in Google BigQuery is dropped

---

View

The Coalesce View UDN is a versatile node that allows you to develop and deploy a View in Google BigQuery.

A view allows the result of a query to be accessed as if it were a table. Views serve a variety of purposes, including combining, segregating, and protecting data.

View Node Configuration

The View node type has two configuration groups:

• Node Properties
• Options

View Node Properties

Properties	Description
Storage Location	Storage Location where the View will be created
Node Type	Name of template used to create node objects
Deploy Enabled	If 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

View Options

Options	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible False: Create view SQL based on options chosen are framed and executed
Multi Source	Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION DISTINCT: Combines sources with duplicate elimination - UNION ALL: Combines sources without duplicate elimination False: Single source node or multiple sources combined using a join
Distinct	Toggle: True/False True: Group By All is invisible. DISTINCT data is chosen for processing False: Group By All is visible
Group By All	Toggle: True/False True: DISTINCT is invisible. Data is grouped by all columns for processing False: DISTINCT is visible

View Joins

Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the Coalesce app.

📘 Specify Group By Clauses

Best Practice is to specify group by clauses in this space if you are not opting for the group by all provided in OPTIONS config.

View Deployment

View Initial Deployment

When deployed for the first time into an environment the View node will execute the Create View stage.

Stage	Description
Create View	This will execute a CREATE OR REPLACE statement and create a View in the target environment

View Redeployment

The subsequent deployment of View node with changes in view definition, adding table description, adding secure option or renaming view results in deleting the existing view and recreating the view.

The following stages are executed:

Stage	Description
Delete View	Removes existing view
Create View	Creates new view with updated definition

View Undeployment

If a View Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the View in the target environment will be dropped.

This is executed in the below stage:

Stage	Description
Delete View	Removes the view from the environment

Code

Work Code

• Node definition
• Create Template
• Run Template

Persistent Stage Code

• Node definition
• Create Template
• Run Template

Dimension Code

• Node definition
• Create Template
• Run Template

Fact Code

• Node definition
• Create Template
• Run Template

View Code

• Node definition
• Create Template
• Run Template

Macros

Versions

Available versions of the package.

Version #	Release Date	Notes
1.0.0	January 28, 2026	BigQuery Base node types - Initial Release