Silent exit from a Dataform Workflow

[valentiniacov]

Hello Community,

I have the following situation:

In our project we have some config type: operations scripts that get executed every day.

They are merge scripts like the following:

config {

    type: "operations",

    dependencies: ["dependecy1", "dependecy2"],

    tags: ["deltaLoad"]

}

DECLARE

  deltaLoadTimestamp TIMESTAMP

---

DECLARE

  deltaLoadDate DATE

---

SET

  deltaLoadTimestamp = CURRENT_TIMESTAMP

---

SET

  deltaLoadDate = ${functions.getDeltaLoadDate()}

---

MERGE INTO

  ${ref("aTable")} AS target

USING

  (

  SELECT

    *

  FROM

    ${ref("anotherTable")} t1

  WHERE conditions

) AS source

ON

CONDITIONS

  WHEN MATCHED THEN UPDATE SET 

COLUMNS

  WHEN NOT MATCHED

  THEN

INSERT

COLUMNS

VALUES()

The issue is that for some days there is no data to be merged so I do not need to run the merge operation.

Is it any way to "silent" exit from the current custom config type: operations scripts and the result of the execution of the script to be successful? I need to run the next merge script that depends on the current one being executed.

To better clarify, we have an ingestion status table. The condition would be:

IF ingestion_date = CURRENT_DATE() THEN

MERGE INTO

  ${ref("aTable")} AS target

USING

  (

  SELECT

    *

  FROM

    ${ref("anotherTable")} t1

  WHERE conditions

) AS source

ON

CONDITIONS

  WHEN MATCHED THEN UPDATE SET 

COLUMNS

  WHEN NOT MATCHED

  THEN

INSERT

COLUMNS

VALUES()

ELSE

SILENT EXIT

I know that I can add a dependency that blocks the execution of the merge script, but I do not want that because I need to run all merge scripts irrespective if for previous merge scripts there was no data to be merged. I just want the entire 35 merge scripts to be executed but in case there is no data to be merged I do not want to execute the MERGE operation, BUT to silent exit the config type: operations script.

Thank you

[ms4446 · Accepted solution]

The below script accounts for when there's no data to merge, while still ensuring the successful execution and dependency flow of your operations:

config {
  type: "operations",
  dependencies: ["dependecy1", "dependecy2"],
  tags: ["deltaLoad"]
}

DECLARE deltaLoadTimestamp TIMESTAMP;
DECLARE deltaLoadDate DATE;
SET deltaLoadTimestamp = CURRENT_TIMESTAMP;
SET deltaLoadDate = ${functions.getDeltaLoadDate()};

-- Check ingestion status directly
IF (SELECT COUNT(*) FROM ${ref("ingestion_status_table")} WHERE ingestion_date = CURRENT_DATE()) > 0 THEN

  MERGE INTO ${ref("aTable")} AS target
  USING (
    SELECT * 
    FROM ${ref("anotherTable")} t1 
    WHERE conditions
  ) AS source
  ON CONDITIONS
  WHEN MATCHED THEN
    UPDATE SET COLUMNS
  WHEN NOT MATCHED THEN
    INSERT COLUMNS
    VALUES();

ELSE 
  -- Silent exit with successful execution signal
  SELECT 1;
END IF;

• This script queries your ingestion_status_table to see if there's an entry for the current date.
• If there's an entry, the MERGE operation proceeds as usual.
• If there's no entry, the ELSE block executes a simple SELECT 1; statement, effectively indicating a successful script run without any data modifications.

[valentiniacov]

Thank you very much for the provided solution.