Return an error message from a Business Workflow

A Business Workflow can fail a run with a message you choose. Wrap the message in ASTRATO_WF_ERROR: and :END_ASTRATO_WF_ERROR in the error the warehouse raises, and the run returns only that message instead of a generic failure. This works in the statements of a virtual procedure and in the body of a stored procedure.

Write a tagged message

ASTRATO_WF_ERROR: <message> :END_ASTRATO_WF_ERROR

• Both markers are fixed text. Keep them in SQL string literals.
• The message is the text between the markers, with surrounding whitespace removed.
• The message must be valid UTF-8, at most 512 bytes, and contain no control characters, line breaks, or invisible format characters such as a zero-width joiner (this excludes joined emoji).
• If the error contains several complete pairs, the first one is used.
• Warehouses add their own text around the error. The end marker tells Astrato where the message stops.

A message is shown to everyone allowed to run the Business Workflow. Do not put confidential data or stored user content in it.

Read the failure

A failed run reports the message as an authored error. The outcome keeps its inputIndexes and effects.

{
  "inputIndexes": [0],
  "status": "failed",
  "effects": "rolledBack",
  "error": {
    "code": "DATA_ACTION_AUTHORED_ERROR",
    "message": "Forecast line 42 is already approved.",
    "authored": true,
    "statement": "raise"
  }
}

statement is the name of the statement that failed. A tagged message does not change transaction or batch behavior: the transaction setting and the stop-at-first-failure rule of a perItem batch apply as for any failed statement. Use effects to see whether data changed.

When Astrato recognizes a malformed tag in a warehouse error, DATA_ACTION_AUTHORED_ERROR_INVALID explains what to fix, such as a missing end marker, an empty message, or a message over 512 bytes. Ask the workflow author to correct the tag. The refused text is never shown.

If a parameter containing either reserved marker reaches the authored error channel, DATA_ACTION_RESERVED_ERROR_MARKER names the parameter. Remove the marker before trying again. Astrato does not return the parameter value as an authored message.

Use tags in procedures

• Virtual procedure: put the tagged error in a statement, as in the examples below.
• Stored procedure: raise the tagged error inside the procedure body, with the warehouse’s own error mechanism.

Diagnose other workflow failures

Astrato gives fixed guidance when the warehouse identifies the problem. A permission error can name the operation and the affected object. A missing object error can name a table, view, or procedure; if the warehouse cannot distinguish a missing object from restricted access, the message says it was not found or is inaccessible. A signature mismatch means the workflow’s arguments do not match the procedure. Ask your workflow author or data source administrator to check the reference, access, or procedure signature before trying again.

The classes, codes and retry flag are listed under recognised warehouse failures. The detail depends on what the warehouse reports and what Astrato can verify from the workflow. If a failure cannot be identified, the run still returns EXECUTION_FAILED. To show a business rule from a stored procedure, raise a tagged message in its body.

Raise a fixed message

PostgreSQL and Supabase:

DO $$ BEGIN
  RAISE EXCEPTION 'ASTRATO_WF_ERROR: The approval window has closed. :END_ASTRATO_WF_ERROR';
END $$

In a PostgreSQL RAISE EXCEPTION message, write a literal percent sign as %%.

Snowflake:

EXECUTE IMMEDIATE $$ DECLARE
  e EXCEPTION (-20001, 'ASTRATO_WF_ERROR: The approval window has closed. :END_ASTRATO_WF_ERROR');
BEGIN
  RAISE e;
END; $$

A Snowflake block like this cannot contain action parameters. Use the expression below when the message depends on a parameter.

Build a message from a parameter

Keep both markers as string literals and bind the value between them. Each statement raises its error through the warehouse’s own function.

Warehouse	Expression
Snowflake	TO_NUMBER('ASTRATO_WF_ERROR: ' || {reason:string} || ' :END_ASTRATO_WF_ERROR')
Redshift	CAST('ASTRATO_WF_ERROR: ' || {reason:string} || ' :END_ASTRATO_WF_ERROR' AS integer)
Databricks	raise_error(CONCAT('ASTRATO_WF_ERROR: ', {reason:string}, ' :END_ASTRATO_WF_ERROR'))
BigQuery	ERROR(CONCAT('ASTRATO_WF_ERROR: ', {reason:string}, ' :END_ASTRATO_WF_ERROR'))
DuckDB and MotherDuck	error('ASTRATO_WF_ERROR: ' || {reason:string} || ' :END_ASTRATO_WF_ERROR')
ClickHouse	throwIf(1, concat('ASTRATO_WF_ERROR: ', {reason:string}, ' :END_ASTRATO_WF_ERROR'))

Use each expression as the statement, for example SELECT raise_error(...) on Databricks.

On Snowflake and Redshift, a null parameter makes the concatenation null and the statement raises no error. Wrap the parameter in COALESCE with a non-empty fallback message when a null is possible. Null handling is not verified for the other warehouses.

PostgreSQL and Supabase raise the error through a cast. Put the cast around the whole CASE, so the error fires only in the branch that is selected:

SELECT CAST(CASE WHEN {amount:int} > 100 THEN
  'ASTRATO_WF_ERROR: ' || {reason:string} || ' :END_ASTRATO_WF_ERROR'
  ELSE '1'
END AS integer)

Casting each branch separately raises the first message even when its condition is false. The same applies to Redshift.

ClickHouse needs a constant message. A bound parameter works because Astrato inserts it as a literal before the statement runs. ClickHouse does not offer transaction rollback for Business Workflows.

Check several conditions

Put each check in its own named statement, or combine the checks in one CASE expression. On ClickHouse, use one statement per check, passing the condition to throwIf. Only the first failing check is reported.

Limits

• Trino cannot run named Business Workflows.
• Warehouse wrappers, SQL and raw errors are not returned.

Next steps

• Write a virtual procedure workflow
• Run a workflow from an App