Skip to main content

Reordering Columns in Coalesce

You can reorder columns in Coalesce Nodes using the Mapping Grid. However, there are important differences in how column ordering behaves depending on whether you're working with new Nodes or existing Nodes, and between development Workspaces and deployed Environments.

  • New Nodes: Column order is preserved during initial deployment.
  • Existing Nodes: Adding columns in the middle may result in different column order due to your data platforms limitations.

Why Coalesce Doesn't Require Specific Column Order​

Coalesce pipelines are designed so that column order doesn't impact functionality. We fully qualify all statements, ensuring they run correctly regardless of the underlying column sequence.

New and Existing Node Behavior​

New Nodes (Initial Deployment)​

When you deploy a new Node for the first time column order is preserved as defined in your Coalesce Node.

  • Development Workspaces: Executes CREATE OR REPLACE. Column order matches Coalesce.
  • Environments: Executes CREATE. Column order matches Coalesce.

Existing Nodes (Subsequent Deployments)​

When you modify an existing Node and redeploy:

Development Workspaces​

  • Always executes CREATE OR REPLACE.
  • Column order always matches what you see in Coalesce.

Environments​

  • Uses imperative deployment by default.
  • Adding new columns results in CLONE, ALTER, SWAP, DROP operations.
  • New columns to the end of existing tables during ALTER operations, regardless of where you positioned them in Coalesce.

Why Column Order Changes During Deployment​

When you modify an existing Node by:

  1. Adding a new column in a specific position within the Mapping Grid.
  2. Deploying to an Environment.

The deployment process will add the new column to the end of the existing table, regardless of where you positioned it in the Coalesce Mapping Grid. This only affects existing Nodes. New Nodes will have their column order preserved during initial deployment.

This can cause issues if you have:

  • Pre-SQL scripts that insert data based on column position.
  • Dependencies on specific column ordinal positions.

Pre-SQL executes before the Node's primary DML/DDL operations. If your Pre-SQL contains INSERT statements that rely on column position rather than column names, these statements will fail or insert data into wrong columns when the actual table structure differs from what you defined in Coalesce.

Approaches for Changing Column Order​

When modifying existing Nodes and encountering column order issues there are a couple of options to take.

Reorder in the Mapping Grid​

To reorder columns in a Node's Mapping Grid:

  1. Drag and drop the columns into your desired position within the Mapping Grid
  2. Click Create to generate the corresponding CREATE TABLE statement

This is recommended for:

  • New Nodes: Column order will be preserved during initial deployment to Environments.
  • Development work: Column order will always match what you see in Coalesce.
  • When Environment column order isn't critical: If you only need correct order in development.

When modifying existing Nodes and encountering column order issues:

  1. Add new columns to the end of your existing Coalesce Node
  2. Update any Pre-SQL scripts to account for the new column position
  3. This ensures the Coalesce Node matches the data platform table structure in both Workspaces and Environments

Benefits:

  • Simplest approach.
  • Maintains consistency between Coalesce and your data platform.
  • No data reload required.

Drawback:

  • Column might not be in your desired ordinal position.

Use Transient Deployment Strategy​

For ongoing automated column ordering, but it requires creating custom Nodes.

Create a custom Node with the transient strategy, which will CREATE OR REPLACE the table when deploying to an Environment.

Benefits:

  • Maintains your desired column order.

Drawbacks:

  • Any data loaded into the Node prior to the change will need to be reloaded.
  • Requires creation of a custom Node.

Manual Column Reordering Workaround (One-Time Fix)​

To fix the column order in a table that’s already deployed to an Environment as a one-time operation, you must work outside of Coalesce and execute SQL commands directly in your data platform.

  1. Rename the existing table, for example TABLE_NAME_BACKUP.
  2. Redeploy the Node from Coalesce, which will create the table with your desired column order.
  3. Run an INSERT statement to copy data from the backup table into the new table.
Requires Dropping and Recreating

This process requires dropping and recreating the table, which means all data must be preserved through the backup and restore process.