> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Validate Data Before Loading with Data Flows

> Learn how to use Ocient data flows to validate data quality before committing a load, using IF, ELSEIF, and RAISE as guard clauses.

export const Ocient = "Ocient®";

This tutorial demonstrates how to use {Ocient} 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](/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 SQL theme={null}
CREATE TABLE events (
    event_id INT,
    user_id INT,
    event_type VARCHAR,
    event_time TIMESTAMP,
    payload_size INT
);
```

## Build and Execute the Validation Pipeline

<Steps>
  <Step title="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.
  </Step>

  <Step title="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 SQL theme={null}
    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.
  </Step>

  <Step title="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 SQL theme={null}
    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;
    ```
  </Step>

  <Step title="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 SQL theme={null}
    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](https://en.wikipedia.org/wiki/SQLSTATE).
  </Step>
</Steps>

## Related Links

[Data Flows](/data-flows)

[Stage and Transform Data with Data Flows](/stage-and-transform-data-with-data-flows)

[Build Iterative Queries with Data Flows](/iterative-queries-with-data-flows)

[Data Manipulation Language (DML) Statement Reference](/data-manipulation-language-dml-statement-reference)

[Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference)