Preserving Column Lineage When Using SQL Statements

When using SQL statements involving multiple columns in Coalesce, we recommend using qualified column names, including the node name.

Example

You have a multisource node called MY_NODE and it has columns COL_A,COL_B, and COL_C.

You decide to build a calculated column using a statement like:

CASE WHEN COL_A IS NULL THEN COL_B ELSE COL_C END

This will not display the column lineage because its missing the node name to make it a qualified column name. Instead build your statements like the following:

CASE WHEN MYNODE.COL_A IS NULL THEN MYNODE.COL_B ELSE MYNODE.COL_C END

MY_NODE is added to each column name, which keeps the lineage.

Coalesce app has an autocomplete feature to make it easier to include the node names. Following this best practice will keep your transformations future proof.