Skip to main content

Understanding View Nodes in Coalesce

View Nodes in Coalesce create generic SQL views in your data warehouse. While many Node types can be materialized as views, the View Node type exists specifically to improve DAG readability by making views visually distinct from other Node types.

When View Nodes Are Created​

Views are created and modified exclusively during deployment operations, not during refresh jobs.

Initial Deployment​

When you deploy a View Node for the first time, Coalesce executes a "Create View" stage that runs a CREATE OR REPLACE statement in your target environment.

Redeployment Triggers​

Views are recreated during deployment when you make changes to:

  • View definition or SQL logic
  • Table descriptions
  • Secure options (converting to or from secure views)
  • View names
  • Column definitions

No Execution During Refresh**​

Views are excluded from refresh jobs entirely. They have no run stages, and no stages are rendered during a refresh operation. This exclusion happens because views are configured using a materialization type of "view."

Why Views Don't Refresh​

Views don't store data—they compute results dynamically at query time. When other Nodes reference a view during a refresh job, the database engine runs the view's SQL to generate results on demand, automatically providing the latest data from underlying tables without needing to refresh the view itself.

Dynamic Data Updates​

Views automatically reflect current data from their source tables every time they're queried. You never need to manually refresh a view. The database engine executes the view's SQL dynamically, ensuring query results always reflect the most recent state of the underlying data.

View Node Configuration Options​

View Nodes offer several configuration options to customize their behavior:

  • Distinct: Toggle for SELECT DISTINCT functionality
  • Multi-source: Toggle for combining multiple sources
  • Override Create SQL: Toggle for custom SQL
  • Multi Source Strategy: Choose between UNION versus UNION ALL when combining sources

Override Create SQL Feature​

The Override Create SQL option allows you to write completely custom SQL for the view definition.

View Nodes Versus Stage Nodes​

While Stage Nodes can be materialized as views, dedicated View Nodes have distinct characteristics:

  • Only allow view materialization (no table option).
  • Have view-specific configuration options like Distinct.

The Run View Alternative​

If you need a view to be recreated during refresh jobs—for example, to utilize dynamic parameters for source selection—you can use the Run View Node type from the Incremental Loading Package.

Unlike standard View Nodes, Run View Nodes include view creation logic in the Run tab. When a job executes using Run View Nodes, the views are recreated as part of the job execution and utilize whatever parameter values are passed in at runtime.

Delete View Nodes​

If you delete a View Node from a Workspace and deploy that change, the view will be dropped from the target environment.

Enabling View Nodes​

View Nodes are disabled by default in Coalesce. You need to manually enable them by navigating to Build Settings > Node Types and toggling the View Node type option. This is because Stage Nodes can also be materialized as views and provide the same functionality with more flexibility.