Skip to main content

Pre-SQL and Post-SQL

Some Nodes come with Pre-SQL and Post-SQL boxes which allow you to run SQL either before or after the main operations.

  • Pre‑SQL box lets you specify SQL that runs before the Node’s primary DML/DDL operations.
  • Post‑SQL box executes after the primary operation has completed.

This guide has examples showing how pre-sql and post-sql can be used.

This screenshot shows a data-loading interface for the STG_SUPPLIER table with columns and data displayed. On the right side, there are Pre-SQL and Post-SQL boxes, along with toggles for multi-source and truncate-before options. The interface also includes status indicators such as Validated, Created, and Data Loaded, plus a button to Fetch Data.

Creating a Process Log and Masking Sensitive Data​

Before processing supplier data from a CSV file, you want to create a log table to capture processing details. After loading data into STG_SUPPLIER, you update sensitive fields for suppliers with high account balances.

How this works:

  • The Pre‑SQL step sets up a logging table to track processing details.
  • The Post‑SQL step masks part of the address for high‑value suppliers.
  • There must a stage in between SQL statements to run multiple.
  • ref_no_link('WORK', 'STG_SUPPLIER') ensures the correct fully qualified table name is used without linking Nodes in the DAG.
--Create a process log table.
CREATE TABLE DOCUMENTATION.LOGS.PROCESS_LOG_NINE ( 
    -- DATABASE.SCHEMA.TABLE
    LOG_ID NUMBER IDENTITY(1,1),
    PROCESS_NAME VARCHAR(100),
    START_TIME TIMESTAMP,
    END_TIME TIMESTAMP,
    STATUS VARCHAR(20),
    RECORD_COUNT NUMBER
);

See Identity and Surrogate Keys in Coalesce for how Coalesce generates identity columns on each platform.

UPDATE {{ ref_no_link('WORK', 'STG_SUPPLIER') }}
SET "S_ADDRESS" = SUBSTRING("S_ADDRESS", 1, 10) || '***'
WHERE "S_ACCTBAL" > 5000;
{{stage('INSERT VALUES INTO THE LOG PROCESS TABLE')}}

INSERT INTO DOCUMENTATION.LOGS.PROCESS_LOG_NINE (PROCESS_NAME, START_TIME, END_TIME, STATUS, RECORD_COUNT)
VALUES (
    'Supplier Load Process',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    'SUCCESS',
    (SELECT COUNT(*) FROM {{ ref_no_link('WORK', 'STG_SUPPLIER') }})
);
This screenshot shows a data-loading interface with a sequence of steps such as Pre-SQL, Truncate Stage Table, Insert STG_SUPPLIER, Post-SQL, and inserting values into a log table, all marked with green checkmarks to indicate successful completion. The interface also includes status indicators for example Validated, Created, and Data Loaded, and options like Fetch Data and View Data.
Results of running the Pre-SQL and Post-SQL

Back Up and Audit Supplier Data​

You need to back up the existing data for auditing purposes. After the load, you want to identify any duplicate supplier IDs and log these duplicates for further review.

How it works:

  • The Pre‑SQL creates or replaces a backup table by copying the current contents of STG_SUPPLIER from the WORK mapping. This backup ensures you have a snapshot of the data before any transformations occur.
  • The Post‑SQL checks for an audit log table, then audits the stage data for duplicate supplier IDs. Any duplicates found are inserted into the audit log for further investigation.
  • {{ ref_no_link('WORK', 'STG_SUPPLIER') }} makes sure the correct reference to the STG_SUPPLIER table without creating dependencies.
Pre-SQL Create Audit Table
CREATE OR REPLACE TABLE DOCUMENTATION.DEV.SUPPLIER_BACKUP AS
SELECT * FROM {{ ref_no_link('WORK', 'STG_SUPPLIER') }};
-- Create the audit log table if it doesn't exist
CREATE TABLE IF NOT EXISTS DOCUMENTATION.DEV.SUPPLIER_AUDIT_ONE (
    S_SUPPKEY NUMBER,
    S_NAME VARCHAR(100),
    DUPLICATE_COUNT NUMBER,
    AUDIT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

{{stage('Log Duplicates')}}

-- Log duplicates found in the stage table
INSERT INTO DOCUMENTATION.DEV.SUPPLIER_AUDIT_ONE (S_SUPPKEY, S_NAME, DUPLICATE_COUNT)
SELECT "S_SUPPKEY", "S_NAME", COUNT(*) AS DUPLICATE_COUNT
FROM {{ ref_no_link('WORK', 'STG_SUPPLIER') }}
GROUP BY "S_SUPPKEY", "S_NAME"
HAVING COUNT(*) > 1;

Run Multiple SQL Statements​

You can either run them as separate stages or as one stage. If you try to run multiple SQL statements without this, you'll get an error: Actual statement count 2 did not match the desired statement count 1.

Two stages (all platforms)
SELECT * FROM <table>

{{stage('SOME STAGE INFO')}}

INSERT INTO <table>
BEGIN
SELECT * FROM <table>;
INSERT INTO <table>;
END;

What's Next?​