Validate Data Before Loading with Data Flows

This tutorial demonstrates how to use data flows to validate data quality before committing a load into production tables. You use conditional logic as guard clauses to verify that source data meets your requirements, and abort the load with a clear error message if any check fails. This pattern is useful for extract, load, and transform (ELT) workflows where bad data can cause downstream issues. By validating within the data flow, you ensure that either all checks pass and the load completes, or the data flow cleanly aborts the load, and the FINALLY clause cleans up temporary resources. For details about data flow syntax and supported statements, see Data Flows.

Prerequisites

Before you begin, ensure that you have:

A running Ocient deployment with access to execute SQL statements.
The CREATE TABLE and INSERT privileges on the target schema.

Prepare the Target Table

For the purposes of this tutorial, assume a source staging table named staged_events with these columns.

Column	Type
`event_id`	INT
`user_id`	INT
`event_type`	VARCHAR
`event_time`	TIMESTAMP
`payload_size`	INT

Create the target table that receives validated data.

SQL

CREATE TABLE events (
    event_id INT,
    user_id INT,
    event_type VARCHAR,
    event_time TIMESTAMP,
    payload_size INT
);

Build and Execute the Validation Pipeline

Understand the Validation Checks

Before building the data flow, define the validation rules that the source data must satisfy. This example enforces three rules:

Row count check — The staging table must contain at least one row.
NULL check — No rows can have a NULL user_id column value.
Value range check — No rows can have a payload_size column value exceeding 10,000,000 bytes.

If any check fails, the data flow raises a descriptive error and does not load any data into the target table.

Build the Validation Data Flow

Create a void data flow that executes each validation check in sequence. If all checks pass, the data flow loads the staged data into the production table.

SQL

BEGIN DATAFLOW
    DECLARE @row_count BIGINT;
    DECLARE @null_count BIGINT;
    DECLARE @oversized_count BIGINT;

    -- Check 1: Verify the staging table is not empty
    SET @row_count = (SELECT COUNT(*) FROM staged_events);
    IF (@row_count = 0) THEN
        RAISE 'Validation failed: staged_events contains no rows';
    END IF;

    -- Check 2: Verify no NULL user identifiers
    SET @null_count = (SELECT COUNT(*) FROM staged_events WHERE user_id IS NULL);
    IF (@null_count > 0) THEN
        RAISE 'Validation failed: ' || CAST(@null_count AS VARCHAR) || ' rows have NULL user_id';
    END IF;

    -- Check 3: Verify payload sizes are within limits
    SET @oversized_count = (
        SELECT COUNT(*) FROM staged_events WHERE payload_size > 10000000
    );
    IF (@oversized_count > 0) THEN
        RAISE 'Validation failed: ' || CAST(@oversized_count AS VARCHAR) || ' rows exceed maximum payload size';
    END IF;

    -- All checks passed — load the data
    INSERT INTO events
        SELECT * FROM staged_events;

FINALLY
    DROP TABLE IF EXISTS staged_events;
END DATAFLOW;

If any RAISE statement executes, the data flow aborts immediately and does not insert any data into the events table. The FINALLY clause still executes, dropping the staging table regardless of whether the load succeeded or failed.

Use Multiple Severity Levels with the ELSEIF Element

You can use the ELSEIF element to implement tiered validation where different conditions produce different error messages or take different actions. This example checks row count thresholds and raises warnings at different levels.

SQL

BEGIN DATAFLOW
    DECLARE @row_count BIGINT;

    SET @row_count = (SELECT COUNT(*) FROM staged_events);

    IF (@row_count = 0) THEN
        RAISE 'Validation failed: no rows in staged_events';
    ELSEIF (@row_count < 100) THEN
        RAISE 'Validation failed: only ' || CAST(@row_count AS VARCHAR) || ' rows — expected at least 100';
    ELSEIF (@row_count > 10000000) THEN
        RAISE 'Validation failed: ' || CAST(@row_count AS VARCHAR) || ' rows exceeds maximum batch size of 10,000,000';
    END IF;

    -- Checks passed — proceed with the load
    INSERT INTO events
        SELECT * FROM staged_events;

FINALLY
    DROP TABLE IF EXISTS staged_events;
END DATAFLOW;

Combine Validation with Error Handling

You can wrap the entire validation and load sequence in the TRY and EXCEPTION WHEN elements to catch unexpected errors. This example logs the error for diagnostics, then re-raises it so the data flow still aborts on failure.

SQL

BEGIN DATAFLOW
    DECLARE @row_count BIGINT;

    TRY
        SET @row_count = (SELECT COUNT(*) FROM staged_events);

        IF (@row_count = 0) THEN
            RAISE 'Validation failed: staged_events contains no rows';
        END IF;

        INSERT INTO events
            SELECT * FROM staged_events;

    EXCEPTION WHEN OTHERS THEN
        -- Log the error for diagnostics, then re-raise to abort the data flow
        INSERT INTO error_log VALUES (CURRENT_TIMESTAMP, @SQLERRM, @SQLSTATE);
        RAISE @SQLERRM;
    END TRY;

FINALLY
    DROP TABLE IF EXISTS staged_events;
END DATAFLOW;

The @SQLERRM variable contains the error message from the caught exception, including the message from any RAISE statement. The @SQLSTATE variable contains the corresponding SQLSTATE code.

Data Flows Stage and Transform Data with Data Flows Build Iterative Queries with Data Flows Data Manipulation Language (DML) Statement Reference Data Definition Language (DDL) Statement Reference

​Prerequisites

​Prepare the Target Table

​Build and Execute the Validation Pipeline

​Related Links

Prerequisites

Prepare the Target Table

Build and Execute the Validation Pipeline

Related Links