Skip to main content

Using Conditionals in Coalesce

Learn how to implement if-else logic in Coalesce using CASE statements, conditional functions, and SQL updates to conditionally transform your data.

What Are Conditionals?​

Logic that executes different actions based on whether a condition is true or false. In Coalesce, you can implement conditionals through Transforms, Pre-SQL, or Post-SQL.

Using CASE Statements in Transforms​

Basic Syntax​

CASE 
  WHEN condition THEN result
  ELSE default_result
END

Single Condition Example​

To update O_ORDERSTATUS to XXX when O_CUSTKEY equals 14784007:

CASE 
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784007 THEN 'XXX'
  ELSE {{SRC}}
END

Multiple Conditions​

CASE 
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784007 THEN 'XXX'
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784008 THEN 'YYY'
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784009 THEN 'ZZZ'
  ELSE {{SRC}}
END

Alternative Conditional Functions​

Beyond CASE statements, Coalesce supports other conditional functions that can simplify transformations for specific use cases.

IF/IFF Functions​

Snowflake uses IFF:

IFF({{SRC_NODE}}."O_CUSTKEY" = 14784007, 'XXX', {{SRC}})

Databricks uses IF or IFF :

IF({{SRC_NODE}}."O_CUSTKEY" = 14784007, 'XXX', {{SRC}})

DECODE Function​

DECODE({{SRC_NODE}}."O_CUSTKEY", 14784007, 'XXX', {{SRC}})

Using Pre-SQL and Post-SQL for Conditional Updates​

Post-SQL executes after data loads, allowing bulk updates based on conditions.

Basic UPDATE Statement​

In the Post-SQL box:

UPDATE {{ ref_no_link('WORK', 'STG_ORDERS_TRANSFORM') }}
SET O_ORDERSTATUS = 'XXX'
WHERE O_CUSTKEY = 14784007;

Multiple Updates Using Stages​

Coalesce lets you run multiple SQL statements in Pre-SQL/Post-SQL either by inserting {{stage('…')}} or wrapping them in a BEGIN … END block.

UPDATE {{ ref_no_link('WORK', 'STG_ORDERS_TRANSFORM') }}
SET O_ORDERSTATUS = 'XXX'
WHERE O_CUSTKEY = 14784007;

{{stage('Update Second Condition')}}

UPDATE {{ ref_no_link('WORK', 'STG_ORDERS_TRANSFORM') }}
SET O_ORDERSTATUS = 'YYY'
WHERE O_CUSTKEY = 14784008;

Creating Conditional Audit Columns​

Track data changes by adding new columns with conditional logic. Add a new column, STATUS_CHANGE_FLAG, then add the SQL to in Transform.

CASE 
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784007 THEN 'MODIFIED'
  WHEN {{SRC_NODE}}."O_CUSTKEY" = 14784008 THEN 'REVIEWED'
  ELSE NULL
END
Adding Columns

You must create and run the new column before applying Transformations.

Understanding Pre-SQL, Transforms, and Post-SQL​

  • Pre-SQL: Executes before any data movement begins. Use this for setup tasks like creating backup tables, temporary tables, or logging.
  • Post-SQL: Executes after the data has been loaded into the target table. These are separate SQL statements (UPDATE, MERGE, etc.) that modify data already in place.
  • Transforms: Execute as part of the data loading process. When Coalesce builds the SQL, your transform logic becomes part of the SELECT statement that loads data from source to target. All transformations happen during this single data movement operation.

Choosing the Right Method​

Use Transform when you need:

  • Complex conditional logic
  • Row-by-row processing
  • Creating derived columns
  • Logic integrated into data transformation pipeline

Use Post-SQL when you need:

  • Simple bulk updates
  • Updates based on joined data
  • Updates after validation steps
  • Temporary data fixes

Use Pre-SQL when you need:

  • Backup tables before processing
  • Logging or audit table setup
  • Temporary staging table creation
  • Initial state capture before transformations

Use CASE when you have:

  • Multiple conditions to evaluate
  • Range checks or inequalities
  • Complex logic with AND/OR operators
  • 3+ different scenarios requiring different actions
CASE 
  WHEN {{SRC}} > 1000 AND {{SRC}} < 5000 THEN 'Medium'
  WHEN {{SRC}} >= 5000 THEN 'Large'
  ELSE 'Small'
END

Use IF/IFF when you have:

  • Simple binary choices (true/false)
  • Single value replacements
  • One condition requiring clean readability
-- Snowflake
IFF({{SRC}} IS NULL, 'Unknown', {{SRC}})

-- Databricks  
IF({{SRC}} IS NULL, 'Unknown', {{SRC}})

Use DECODE when you have:

  • Exact value matching requirements
  • Code-to-description replacements
  • Lookup-style transformations
DECODE({{SRC}}, 
  1, 'Active',
  2, 'Inactive', 
  3, 'Pending',
  'Unknown')