# Data Quality Expectations — Post-Load Assertions in Starlake

> Define post-load data quality expectations in Starlake using Jinja SQL macros. Assert uniqueness, row counts and custom conditions on target tables with failOnError support.

<script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": [
        {
          "@type": "Question",
          "name": "What is an expectation in Starlake?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "An expectation is an assertion executed after data loading. It consists of a SQL query evaluated on the target table, whose result is compared to an expected condition."
          }
        },
        {
          "@type": "Question",
          "name": "How do I define an expectation in the table YAML file?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Add an expectations section with a list of entries. Each entry contains expect (a macro call) and optionally failOnError: true to halt the pipeline on failure."
          }
        },
        {
          "@type": "Question",
          "name": "What is the format of an expectation expression?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "The format is <query_name>(<params>). The query_name references a Jinja macro defined in the expectations directory. The macro generates a SQL query whose single-value result determines the outcome: 0 means the expectation is satisfied, any other value fails it."
          }
        },
        {
          "@type": "Question",
          "name": "How do I write an expectation query template?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Templates are Jinja2 macros with a .j2 extension placed in the expectations directory. They generate SQL. The SL_THIS placeholder represents the target table."
          }
        },
        {
          "@type": "Question",
          "name": "How does Starlake decide whether an expectation passed?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "The expectation query must return a single value. 0 means the expectation is satisfied; any other value marks it as failed."
          }
        },
        {
          "@type": "Question",
          "name": "Can I stop the pipeline if an expectation fails?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Yes. Set failOnError: true on the expectation. If the condition is not satisfied, the pipeline halts with an error."
          }
        },
        {
          "@type": "Question",
          "name": "What built-in expectation macros does Starlake provide?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Starlake documents: is_col_value_not_unique, is_row_count_to_be_between, col_value_count_greater_than, count_by_value and column_occurs. They are customizable and extensible."
          }
        }
      ]
    })}
  </script>
  <script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "HowTo",
      "name": "How to add data quality expectations in Starlake",
      "description": "Define post-load assertions using Jinja SQL macros to validate data quality during ingestion.",
      "step": [
        {
          "@type": "HowToStep",
          "name": "Create or reuse an expectation macro",
          "text": "Place a .j2 Jinja2 template in the expectations directory. Use SL_THIS as a placeholder for the target table name."
        },
        {
          "@type": "HowToStep",
          "name": "Open the table YAML file",
          "text": "Edit metadata/load/<domain>/
          .sl.yml."
        },
        {
          "@type": "HowToStep",
          "name": "Add an expectations section",
          "text": "Under the table key, define a list of expectation entries."
        },
        {
          "@type": "HowToStep",
          "name": "Write the expectation expression",
          "text": "Use the format <query_name>(<params>). The macro must generate a SQL query returning a single value: 0 means success, any other value means failure."
        },
        {
          "@type": "HowToStep",
          "name": "Set failOnError",
          "text": "Set to true to halt the pipeline on failure, or false to log and continue."
        },
        {
          "@type": "HowToStep",
          "name": "Run the load",
          "text": "Execute starlake load. Expectations are evaluated after the data has been written to the target table."
        }
      ]
    })}
  </script>

# Data Quality Expectations

Expectations are post-load data quality assertions that run automatically after Starlake writes data to the target table. They complement [pre-load type validation](/starflow/guides/load/validate) by checking business rules on the loaded dataset: uniqueness, row count ranges, value distributions and custom conditions.

Each expectation references a reusable Jinja2 SQL macro and passes it parameters. The macro generates a SQL query whose result determines the outcome: `0` means the expectation is satisfied, any other value fails it. Set `failOnError: true` to halt the pipeline on failure, turning expectations into a lightweight data quality gate.

## How to add data quality expectations

1. **Create or reuse an expectation macro** -- Place a `.j2` Jinja2 template in the `expectations` directory. Use `SL_THIS` as a placeholder for the target table name.
2. **Open the table YAML file** -- Edit `metadata/load/<domain>/<table>.sl.yml`.
3. **Add an `expectations` section** -- Under the `table` key, define a list of entries.
4. **Write the expectation expression** -- Use the format `<query_name>(<params>)`. The macro must generate a SQL query returning a single value: `0` means success, any other value means failure.
5. **Set `failOnError`** -- Set to `true` to halt the pipeline on failure, or `false` to log and continue.
6. **Run the load** -- Execute `starlake load`. Expectations are evaluated after the data has been written.

## Defining expectations in the table YAML

Add expectations in the `expectations` section of the table definition. Each entry contains an `expect` expression and an optional `failOnError` flag.

```yaml
table:
    ...
    attributes:
        - name: id
          type: integer
        ...
    expectations:
      - expect: "is_col_value_not_unique('id')"
        failOnError: true # or false if you want to continue
```

## Expectation expression format

The expectation expression follows this pattern:

```
<query_name>(<param>*)
```

- **`query_name`** -- Name of a Jinja2 macro defined in the `expectations` directory. The macro generates a SQL `SELECT` statement run against the target table.
- **`param`** -- Parameters passed to the macro, separated by commas.

The generated query must return a single value. `0` means the expectation is satisfied; any other value marks it as failed.

:::caution Deprecated syntax
Earlier versions documented the form `<query_name>(<params>) => <condition>` with the condition variables `count`, `result` and `results`. This syntax is deprecated: write macros that return `0` on success instead.
:::

## Writing expectation query macros

Expectation queries are Jinja2 macros stored as `.j2` files in the `expectations` directory. The `SL_THIS` placeholder represents the target table. You can organize macros in subdirectories.

### Built-in macro examples

Starlake provides the following reusable macros. They are customizable and extensible.

```jinja
{# Passes (returns 0) when every value of the column is unique #}
{% macro is_col_value_not_unique(col, table='SL_THIS') %}
    SELECT count(*)
    FROM (SELECT {{ col }}, count(*) as cnt FROM {{ table }}
    GROUP BY {{ col }}
    HAVING count(*) > 1)
{% endmacro %}

{# Passes (returns 0) when the row count is within the given range #}
{% macro is_row_count_to_be_between(min_value, max_value, table_name = 'SL_THIS') -%}
    select
        case
            when count(*) between {{min_value}} and {{max_value}} then 0
        else
            1
        end
    from {{table_name}}
{%- endmacro %}

{# Passes (returns 0) when no value of the column occurs more than min_count times #}
{% macro col_value_count_greater_than(col, min_count, table_name='SL_THIS') %}
    SELECT count(*)
    FROM (
        SELECT {{ col }} FROM {{ table_name }}
        GROUP BY {{ col }}
        HAVING count(*) > {{ min_count }}
    )
{% endmacro %}

{# Passes (returns 0) when no row matches the given value #}
{% macro count_by_value(col, value, table='SL_THIS') %}
    SELECT count(*)
    FROM {{ table }}
    WHERE {{ col }} LIKE '{{ value }}'
{% endmacro %}

{# Passes (returns 0) when every value of the column occurs exactly the given number of times #}
{% macro column_occurs(col, times, table='SL_THIS') %}
    SELECT count(*)
    FROM  (
        SELECT {{ col }}, count(*) as cnt FROM {{ table }}
        GROUP BY {{ col }}
        HAVING cnt <> {{ times }}
    )
{% endmacro %}
```

### Creating your own macros

Create a `.j2` file in the `expectations` directory. Define a Jinja2 macro that generates a SQL `SELECT` statement. Use `SL_THIS` as the table placeholder. The query must return a single value: `0` means the expectation is satisfied, any other value marks it as failed.

## Expectations vs type validation

| Aspect | [Type validation](/starflow/guides/load/validate) | Expectations |
|---|---|---|
| When it runs | Before load (pre-load) | After load (post-load) |
| What it checks | Individual field format (regex) | Business rules on the full dataset |
| Failed records | Rejected to audit table | Pipeline halted or warning logged |
| Engine | Spark only | All engines |

Use both for comprehensive data quality: type validation catches format errors at the record level, and expectations verify aggregate conditions on the loaded data. You can also base expectations on [ingestion metrics](/starflow/guides/load/metrics).

## Frequently Asked Questions

### What is an expectation in Starlake?
An expectation is a post-load assertion executed after data loading. It consists of a SQL query evaluated on the target table, whose result is compared to an expected condition.

### How do I define an expectation in the table YAML file?
Add an `expectations` section with a list of entries. Each entry contains `expect` (a macro call) and optionally `failOnError: true` to halt the pipeline on failure.

### What is the format of an expectation expression?
The format is `<query_name>(<params>)`. The `query_name` references a Jinja macro defined in the `expectations` directory. The macro generates a SQL query whose single-value result determines the outcome: `0` means the expectation is satisfied, any other value fails it.

### How do I write an expectation query template?
Templates are Jinja2 macros with a `.j2` extension placed in the `expectations` directory. They generate SQL returning a single value (`0` = pass). The `SL_THIS` placeholder represents the target table.

### How does Starlake decide whether an expectation passed?
The expectation query must return a single value. `0` means the expectation is satisfied; any other value marks it as failed.

### Can I stop the pipeline if an expectation fails?
Yes. Set `failOnError: true` on the expectation. If the condition is not satisfied, the pipeline halts with an error.

### What built-in expectation macros does Starlake provide?
Starlake documents: `is_col_value_not_unique`, `is_row_count_to_be_between`, `col_value_count_greater_than`, `count_by_value` and `column_occurs`. They are customizable and extensible.