Silent exit from a Dataform Workflow

Google Cloud Data Analytics
1

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

6 Likes
2

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.
4 Likes
3

Thank you very much for the provided solution.