Convert Join to Aggregation
convert_join_to_aggregationTransform a join node into an aggregated fact table with GROUP BY. Automatically generates JOIN clauses, infers datatypes, and sets column attributes.
Instructions
Convert an existing join node into an aggregated fact table with GROUP BY. This is the REQUIRED follow-up after creating a multi-predecessor node — it completes the join setup.
This tool automatically:
Generates JOIN ON clauses from common columns between predecessors
Writes the complete FROM/JOIN/ON/GROUP BY clause to the node's joinCondition (no separate update needed)
Replaces columns with GROUP BY dimensions + aggregate measures
Infers datatypes from transform functions (COUNT → NUMBER, SUM → NUMBER(38,4), etc.)
Sets column-level attributes (isBusinessKey on GROUP BY columns, isChangeTracking on aggregates)
Validates that all non-aggregate columns are in GROUP BY
Runs intelligent config completion
Use this to transform a simple join (row-level) into an aggregated fact table (summary-level).
Example: Convert order detail join to customer metrics: { workspaceID: "1", nodeID: "fact-node-id", groupByColumns: ['"STG_ORDER_HEADER"."CUSTOMER_ID"'], aggregates: [ { name: "TOTAL_ORDERS", function: "COUNT", expression: 'DISTINCT "STG_ORDER_HEADER"."ORDER_ID"' }, { name: "LIFETIME_VALUE", function: "SUM", expression: '"STG_ORDER_HEADER"."ORDER_TOTAL"' }, { name: "AVG_ORDER_VALUE", function: "AVG", expression: '"STG_ORDER_HEADER"."ORDER_TOTAL"' } ], joinType: "INNER JOIN" }
Args:
workspaceID (string, required): The workspace ID
nodeID (string, required): The node ID of the join to convert
groupByColumns (string[], required): Columns to group by (dimensions)
aggregates (array, required): Aggregate columns with functions and expressions
joinType (string, optional): JOIN type (default: INNER JOIN)
maintainJoins (boolean, optional): Generate JOINs (default: true)
repoPath (string, optional): Local repo path for config completion
Returns: Updated node with new columns, joinCondition, GROUP BY analysis, and config completion results.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| nodeID | Yes | The node ID of the join to convert | |
| joinType | No | Type of JOIN to use. Defaults to INNER JOIN. | |
| repoPath | No | Optional path to local Coalesce repository for intelligent config completion | |
| aggregates | Yes | Aggregate columns with their functions and expressions | |
| workspaceID | Yes | The workspace ID | |
| maintainJoins | No | If true (default), analyzes predecessors, generates JOIN SQL, and writes the joinCondition to the node. If false, only replaces columns with aggregates without generating joins. | |
| groupByColumns | Yes | Columns to group by (dimensions). Use fully-qualified names like '"TABLE"."COLUMN"'. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| nodeID | No | ||
| created | No | ||
| warning | No | ||
| nextSteps | No | ||
| validation | No | ||
| joinSuggestions | No | ||
| configCompletion | No | ||
| nodeTypeValidation | No | ||
| configCompletionSkipped | No |