> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/resources/vulcan/quality/data-quality.md).

# Data quality

DQ checks (\`kind: dq\`): expectations, dimensions, and profiling.

A **DQ check** is a promise about a table after Vulcan has built it. The promise might be "every customer has an email," "subscription ids are unique," "revenue is never negative," or "this table was updated within the last day."

You write those promises in a YAML file next to the model. Vulcan runs them after a successful build and stores the result: passed, failed, or could not be evaluated. A failed check tells consumers the table may be unsafe to trust. The table itself stays as the build left it.

Unlike [assertions](https://v2.dataos.info/references/resources/vulcan/quality/assertions.md), which block model execution when their audit fails, DQ checks never stop the pipeline. Use them to record a durable score and build a historical picture of data quality.

There are two kinds of check:

* A **built-in check** uses a metric Vulcan already knows how to score, such as missing values, duplicates, row count, or freshness.
* A **custom check** is a SQL query you write. The query returns the rows that break a business rule.

Both kinds are written as **expectations** under an `expectations:` list. The older `rules:` syntax still loads.

{% hint style="warning" %}
**`rules:` is deprecated from December 2026**

The `rules:` syntax will be deprecated in December 2026. Write new checks as expectations, and move existing `rules:` files to `expectations:` before then. Each example on this page shows the expectation next to the `rules:` line it replaces.
{% endhint %}

**What makes a DQ check different:**

* Configured in simple YAML files in `dq/`, using `kind: dq`
* One file per checklist, each targeting a single model via `depends_on:`
* Runs after the data is written, and only when that write succeeded
* Don't block models or roll them back (a failed check is a recorded result)
* Track historical patterns and trends
* Integrate with Activity API for monitoring and alerting

## Data quality vs assertions vs profiles

Assertions, data quality checks, and profiles all watch your data, but they answer different questions. Pick the wrong one and you'll either block a run that should have shipped or let bad rows reach production.

| Feature                 | Assertions                     | Data quality checks                                 | Profiles                               |
| ----------------------- | ------------------------------ | --------------------------------------------------- | -------------------------------------- |
| **Purpose**             | Critical validation            | Monitoring and scoring                              | Observation and tracking               |
| **When runs**           | With model (inline)            | After a successful plan or run apply                | After a successful plan or run apply   |
| **Blocks models?**      | Yes (always)                   | No                                                  | No                                     |
| **Configuration**       | In MODEL DDL or .sql files     | `expectations:` in YAML files in `dq/` (`kind: dq`) | Under `profiles:` in a `kind: dq` file |
| **Output**              | Pass/fail                      | Passed, failed, or could not be evaluated + samples | Statistical metrics                    |
| **Best for**            | Business rules, data integrity | Durable quality scores, trend monitoring, drift     | Understanding data                     |
| **Historical tracking** | No                             | Yes (Activity API)                                  | Yes (`_check_profiles`)                |

**The Three-Layer Strategy:**

A layered approach to data quality:

```
┌──────────────────────────────────────────┐
│  ASSERTIONS (Critical: Blocks models)    │
│  • Primary keys must be unique           │
│  • Revenue must be non-negative          │
│  • Foreign key relationships valid       │
└──────────────────────────────────────────┘
              ↓
┌──────────────────────────────────────────┐
│  DATA QUALITY (Monitoring: Non-Blocking) │
│  • Row count within expected range       │
│  • Cross-table consistency               │
│  • Freshness of the newest rows          │
└──────────────────────────────────────────┘
              ↓
┌──────────────────────────────────────────┐
│  PROFILES (Observation: Metrics)         │
│  • Track null percentages                │
│  • Monitor column distributions          │
│  • Detect data drift                     │
└──────────────────────────────────────────┘
```

Assertions stop bad data at the door. Data quality checks score the data but don't interfere. Profiles observe patterns and help you understand what's normal.

## When to use data quality

**Use data quality checks for:**

* A durable score: what was promised, what was observed, and which quality dimension broke
* Monitoring data quality trends over time (is completeness getting worse?)
* Cross-model validation (do orders match customers?)
* Non-critical validation (a recorded failure, not a blocker)
* Freshness checks (was the table updated recently enough?)
* Building data quality dashboards

**Use assertions instead for:**

* Critical business rules that must pass (revenue can't be negative)
* Model-specific validation (runs inline with the model)
* Simple SQL assertions
* Blocking invalid data from flowing downstream

**Use profiles instead for:**

* Understanding data characteristics (what does this column look like?)
* Discovering patterns (not validation)
* Detecting data drift over time
* Informing which checks and assertions to add

**Example: Revenue validation strategy**

Here's how you'd layer all three for a revenue table:

```sql
-- ASSERTION (Critical: blocks if it fails)
-- This stops the model if revenue is invalid
MODEL (
  name analytics.revenue,
  assertions (
    not_null(columns := (customer_id, revenue)),
    accepted_range(column := revenue, min_v := 0, max_v := 100000000)
  )
);
```

{% tabs %}
{% tab title="Expectations" %}

```yaml
# DATA QUALITY (Monitoring + observation)
# dq/revenue.yml
# Profiles watch and record. Checks score the table.
# Neither blocks the model.
kind: dq
name: revenue_dq
depends_on: analytics.revenue

profiles:
  - revenue
  - order_count
  - customer_tier

expectations:
  - expectation: expect_column_mean_to_be_between
    column: revenue
    min_value: 1000
    max_value: 100000
    meta:
      name: revenue_in_expected_range
      dimension: accuracy

  - expectation: expect_table_row_count_to_be_between
    min_value: 1
    meta:
      name: revenue_rows_present
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# DATA QUALITY (Monitoring + observation)
# dq/revenue.yml
kind: dq
name: revenue_dq
depends_on: analytics.revenue

profiles:
  - revenue
  - order_count
  - customer_tier

rules:
  - avg(revenue) between 1000 and 100000:
      name: revenue_in_expected_range
      dimension: accuracy

  - row_count > 0:
      name: revenue_rows_present
      dimension: completeness
```

{% endtab %}
{% endtabs %}

## When a check runs

Checks run after the data is already written, and only when that write succeeded.

* A **scheduled refresh** builds the models that are due. When that refresh finishes with no build errors, Vulcan scores the data quality files for the models that were just built.
* **Applying a plan** does the same thing after the apply finishes cleanly. The score is stored with that apply.
* A model that **failed to build** is left unscored. There is no finished table to judge.
* If the refresh or apply **hits any error**, scoring for that job is skipped.

A failed check is a recorded result. It does not roll the table back, and it does not cancel models that already built in the same job. Use an [assertion](https://v2.dataos.info/references/resources/vulcan/quality/assertions.md) when a bad row must stop the pipeline. Use a data quality check when the business needs a durable score.

Each file is attached to one model through `depends_on`. The check reads that model's table in the environment that just ran, such as production or a development environment.

## Quick start

Write your first checklist, then take a tour of common patterns you'll reuse.

{% hint style="info" %}
**Snowflake: write column names in uppercase**

On Snowflake, write column names in uppercase everywhere in a `kind: dq` file, such as `REVENUE`, `EMAIL`, and `CUSTOMER_ID`. Snowflake stores unquoted column names in uppercase. The examples on this page use lowercase; convert them when your engine is Snowflake.
{% endhint %}

### Your first data quality check

Create your first check. It's simpler than you might think.

Create a file `dq/customers.yml`:

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: customers_dq
depends_on: analytics.customers

expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      name: no_missing_emails
      dimension: completeness
      description: "All customers must have an email address"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: customers_dq
depends_on: analytics.customers

rules:
  - missing_count(email) = 0:
      name: no_missing_emails
      dimension: completeness
      description: "All customers must have an email address"
```

{% endtab %}
{% endtabs %}

This check ensures every customer has an email address. After the model builds successfully, the check runs and records a failure if any emails are missing.

**What happens when it runs:**

Checks and profiles run after a successful **plan** apply or **run**. Here's what the execution output looks like:

```bash
Check Executions (1 Models)
└── hello.subscriptions
    ├── completeness (4/4)
    ├── uniqueness (1/1)
    └── validity (3/3)

Profiled 1 model (3 columns):
  warehouse.hello.subscriptions: 3 columns
```

Here are common patterns you'll use:

### Common check patterns

The patterns you'll use most often. Copy them, tweak for your tables, and go.

#### Pattern 1: completeness checks

Make sure required data is present:

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: orders_completeness
depends_on: analytics.orders

expectations:
  - expectation: expect_column_values_to_not_be_null
    column: customer_id
    meta:
      name: customer_id_required
      dimension: completeness

  - expectation: expect_table_row_count_to_be_between
    min_value: 1001
    meta:
      name: sufficient_orders
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: orders_completeness
depends_on: analytics.orders

rules:
  - missing_count(customer_id) = 0:
      name: customer_id_required
      dimension: completeness

  - row_count > 1000:
      name: sufficient_orders
      dimension: completeness
```

{% endtab %}
{% endtabs %}

The first check ensures every order has a customer ID (zero tolerance). The second makes sure you have enough data to work with. `row_count > 1000` becomes a minimum of 1001, because the expectation bound is inclusive.

The not-null check is a "no nulls" promise. A check that allows some nulls, such as `missing_percent(email) < 5`, is skipped at load. See [Built-in check lines that do not run yet](#built-in-check-lines-that-do-not-run-yet).

#### Pattern 2: validity checks

Validate data format and values:

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: users_validity
depends_on: analytics.users

expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT user_id, email
      FROM analytics.users
      WHERE email NOT LIKE '%@%'
    meta:
      name: invalid_emails
      dimension: validity

  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT user_id, age
      FROM analytics.users
      WHERE age < 0 OR age > 120
    meta:
      name: invalid_ages
      dimension: validity
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: users_validity
depends_on: analytics.users

rules:
  - failed rows:
      name: invalid_emails
      dimension: validity
      fail query: |
        SELECT user_id, email
        FROM analytics.users
        WHERE email NOT LIKE '%@%'

  - failed rows:
      name: invalid_ages
      dimension: validity
      fail query: |
        SELECT user_id, age
        FROM analytics.users
        WHERE age < 0 OR age > 120
```

{% endtab %}
{% endtabs %}

A custom check is flexible. You can write any SQL query. If it returns rows, the check fails and Vulcan keeps example failing rows.

#### Pattern 3: uniqueness checks

Ensure no duplicates:

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: customers_uniqueness
depends_on: analytics.customers

expectations:
  - expectation: expect_column_values_to_be_unique
    column: email
    meta:
      name: unique_emails
      dimension: uniqueness

  - expectation: expect_compound_columns_to_be_unique
    column_list: [customer_id, order_date]
    meta:
      name: unique_customer_date_combination
      dimension: uniqueness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: customers_uniqueness
depends_on: analytics.customers

rules:
  - duplicate_count(email) = 0:
      name: unique_emails
      dimension: uniqueness

  - duplicate_count(customer_id, order_date) = 0:
      name: unique_customer_date_combination
      dimension: uniqueness
```

{% endtab %}
{% endtabs %}

The second example shows composite keys: maybe a customer can have multiple orders, but only one per day.

## Data quality configuration

How data quality files are structured and configured, from file layout to per-check metadata.

### File structure

Data quality files live in YAML files in `dq/`. **Each file is one checklist and targets exactly one model** via `depends_on:`. Organize them however makes sense for your project:

```
project/
├── models/
│   ├── dq/
│   │   ├── users.yml           # Checks for analytics.users
│   │   ├── orders.yml          # Checks for analytics.orders
│   │   ├── revenue.yml         # Checks for analytics.revenue
│   │   └── cross_model.yml     # Checks anchored on one model, joining others via SQL
│   ├── semantics/
│   ├── metrics/
│   └── *.sql
└── config.yaml
```

**File naming:**

* Must end with `.yml` or `.yaml`
* The file name doesn't matter (Vulcan reads all files in `dq/`)
* Convention: name files after the model they target (e.g. `subscriptions.yml` for `hello.subscriptions`)

### Basic data quality syntax

A data quality file has a file-level header followed by an `expectations:` list. Put built-in checks and custom checks in that list.

{% hint style="warning" %}
**Still using the `rules:` syntax?**

Files that list checks under `rules:` still load. Vulcan converts each rule into an expectation when the project loads, then runs the expectation. That conversion is backward compatible until the `rules:` syntax is deprecated in December 2026, so plan to move current projects to `expectations:` before then.

Write new checks as expectations, and move older files to `expectations:` when you next touch them. A file contains one of the two lists, not both.
{% endhint %}

**How to read the examples:** each example on this page has two tabs.

* **Expectations** is the syntax to write.
* **Rules** is the older line under `rules:`. It still loads and is converted into the expectation beside it.

```yaml
kind: dq                             # Required: file kind
name: <checklist_name>               # Required: unique name for this checklist
depends_on: <fully.qualified.model>  # Required: the model these checks belong to

# Optional file-level fields:
filter: "<sql_predicate>"            # Rows every check on the file judges
profiles:                            # Columns to profile alongside the checks
  - <column_1>
  - <column_2>

expectations:
  - expectation: <expectation_name>  # e.g. expect_column_values_to_not_be_null
    column: <column>                 # Arguments depend on the expectation
    meta:
      name: <check_name>
      dimension: <dimension>         # completeness | validity | accuracy | ...
      description: <human_readable_description>
```

`kind: dq` is the file type. Older files that say `type: dq` still load. `name` labels the checklist. `depends_on` is the model the checks belong to.

| Field          | Required | Description                                                                                                  |
| -------------- | -------- | ------------------------------------------------------------------------------------------------------------ |
| `kind`         | Yes      | Always `dq`. `type: dq` still loads.                                                                         |
| `name`         | Yes      | Labels the checklist.                                                                                        |
| `depends_on`   | Yes      | The model the checks belong to. Checks read this model's table in the environment that just ran.             |
| `filter`       | No       | A SQL condition. Every check on the file, including freshness, judges only rows that match it.               |
| `profiles`     | No       | Columns to summarize with counts, min, max, average, and frequent values. Profiles describe the whole table. |
| `expectations` | No       | The list of built-in checks and custom checks.                                                               |

{% hint style="warning" %}
DQ YAML is loaded without key conversion, so keys must already be `snake_case`: a camelCase key like `dependsOn` is silently unrecognized rather than converted.
{% endhint %}

**Example:**

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: customers_dq
depends_on: analytics.customers

expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 101
    meta:
      name: sufficient_customers
      dimension: completeness
      description: "At least 100 customers expected in production"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: customers_dq
depends_on: analytics.customers

rules:
  - row_count > 100:
      name: sufficient_customers
      dimension: completeness
      description: "At least 100 customers expected in production"
```

{% endtab %}
{% endtabs %}

### Check forms: short and full

An expectation can be written with or without a `meta` block.

**Short form:** just the expectation and its arguments. Vulcan fills in `meta.name` and `meta.dimension`:

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: user_id
  - expectation: expect_column_values_to_not_be_null
    column: event_date
  - expectation: expect_column_values_to_be_unique
    column: event_id
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - missing_count(user_id) = 0
  - missing_count(event_date) = 0
  - duplicate_count(event_id) = 0
```

{% endtab %}
{% endtabs %}

**Full form:** the expectation with a `meta` block:

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: event_type
    meta:
      name: no_missing_event_types
      dimension: completeness
      description: "All events must have an event type"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - missing_count(event_type) = 0:
      name: no_missing_event_types
      dimension: completeness
      description: "All events must have an event type"
```

{% endtab %}
{% endtabs %}

You can mix both in the same list. Use the short form for obvious checks, and the full form whenever you want a stable name, a dimension, or a description.

### Check metadata

Each expectation can carry a `meta` block:

| Field              | Description                                                                                                                                              |
| ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `meta.name`        | Stable identifier for the check. Set it on any check you want to follow over time, or look up in the Activity API.                                       |
| `meta.dimension`   | The business concern the check protects: `completeness`, `uniqueness`, `validity`, `consistency`, `accuracy`, `timeliness`, `conformity`, or `coverage`. |
| `meta.description` | Human-readable explanation.                                                                                                                              |

If you omit `meta.name` or `meta.dimension`, Vulcan fills them in.

**Naming rules:**

* A name starts with a letter.
* A name contains only letters, digits, and underscores.
* Names in one file are unique.

### Data quality dimensions

The `meta.dimension` field classifies checks. Vulcan supports 8 standard dimensions (based on ODPS v3.1):

| Dimension      | The promise                                                      |
| -------------- | ---------------------------------------------------------------- |
| `completeness` | Required rows and fields are present                             |
| `uniqueness`   | The key matches the grain                                        |
| `validity`     | Values have the right form, range, or allowed set                |
| `consistency`  | Related columns and tables agree                                 |
| `accuracy`     | A number reconciles to the rule you stated                       |
| `timeliness`   | The data is recent enough, or changed within the range you allow |
| `conformity`   | The shape of the data matches the contract                       |
| `coverage`     | The population you promised is represented                       |

#### 1. Completeness

No missing required data. The most common dimension.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: customer_id
    meta:
      dimension: completeness
  - expectation: expect_table_row_count_to_be_between
    min_value: 1001
    meta:
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - missing_count(customer_id) = 0:
      dimension: completeness
  - row_count > 1000:
      dimension: completeness
```

{% endtab %}
{% endtabs %}

#### 2. Validity

Data conforms to format/syntax. Is that email actually an email? Is that status one of the allowed values?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT * FROM analytics.users
      WHERE email NOT LIKE '%@%'
    meta:
      dimension: validity
  - expectation: expect_column_values_to_be_in_set
    column: status
    value_set: [active, churned, suspended]
    meta:
      dimension: validity
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - failed rows:
      dimension: validity
      fail query: |
        SELECT * FROM analytics.users
        WHERE email NOT LIKE '%@%'
  - invalid_count(status) = 0:
      dimension: validity
      valid values: [active, churned, suspended]
```

{% endtab %}
{% endtabs %}

#### 3. Accuracy

Data matches reality. Is the average age reasonable? Is revenue in the expected range?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_mean_to_be_between
    column: age
    min_value: 18
    max_value: 65
    meta:
      dimension: accuracy
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - avg(age) between 18 and 65:
      dimension: accuracy
```

{% endtab %}
{% endtabs %}

#### 4. Consistency

Data agrees across sources. Do orders match customers? Are totals consistent?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT src.*
      FROM {batch} AS src
      LEFT JOIN analytics.customers AS ref
        ON src.customer_id = ref.customer_id
      WHERE src.customer_id IS NOT NULL
        AND ref.customer_id IS NULL
    meta:
      dimension: consistency
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - values in (customer_id) must exist in analytics.customers (customer_id):
      dimension: consistency
```

{% endtab %}
{% endtabs %}

#### 5. Uniqueness

No duplicates. Is that email really unique? Is each order id used once?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_be_unique
    column: email
    meta:
      dimension: uniqueness
  - expectation: expect_column_values_to_be_unique
    column: order_id
    meta:
      dimension: uniqueness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - duplicate_count(email) = 0:
      dimension: uniqueness
  - duplicate_count(order_id) = 0:
      dimension: uniqueness
```

{% endtab %}
{% endtabs %}

#### 6. Timeliness

Data is current. Is the data fresh? Are updates happening on time?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: vulcan_freshness
    column: updated_at
    op: "<"
    threshold_seconds: 604800
    meta:
      dimension: timeliness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - freshness(updated_at) < 1w:
      dimension: timeliness
```

{% endtab %}
{% endtabs %}

#### 7. Conformity

Follows standards. Does the zip code have the right shape? Are codes valid?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_value_lengths_to_equal
    column: zip_code
    value: 5
    meta:
      dimension: conformity
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - invalid_count(zip_code) = 0:
      dimension: conformity
      valid length: 5
```

{% endtab %}
{% endtabs %}

#### 8. Coverage

All records are present. Did we get all the data we expected?

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 1000
    meta:
      dimension: coverage
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - row_count >= 1000:
      dimension: coverage
```

{% endtab %}
{% endtabs %}

### Filtering checks

`filter` is a SQL condition set once on the file. Every check on the file, including freshness, judges only rows that match it.

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: completed_recent_orders_dq
depends_on: analytics.orders

filter: "status = 'completed' AND order_date >= CURRENT_DATE - INTERVAL '30 days'"

expectations:
  - expectation: expect_column_values_to_not_be_null
    column: customer_id
    meta:
      name: completed_orders_have_customers
      dimension: completeness
  - expectation: expect_table_row_count_to_be_between
    min_value: 1
    meta:
      name: completed_orders_exist
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: completed_recent_orders_dq
depends_on: analytics.orders

filter: "status = 'completed' AND order_date >= CURRENT_DATE - INTERVAL '30 days'"

rules:
  - missing_count(customer_id) = 0:
      name: completed_orders_have_customers
      dimension: completeness
  - row_count > 0:
      name: completed_orders_exist
      dimension: completeness
```

{% endtab %}
{% endtabs %}

A few things to know about `filter`:

* In a custom check, `{batch}` is the table being checked with the file's `filter` applied. A query that names the table directly reads every row.
* For freshness, the filter limits which rows contribute the newest timestamp.
* Column `profiles` describe the whole table, including rows the filter excludes.

Need different checks for different slices of the same model (e.g. US vs EU customers)? Create a separate file per slice. Each file has a single `depends_on:` and its own `filter:`.

### Per-check metadata example

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: revenue_dq
depends_on: analytics.revenue

expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 1001
    meta:
      name: sufficient_revenue_data
      dimension: completeness
      description: "Revenue table must have more than 1000 rows for analysis"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: revenue_dq
depends_on: analytics.revenue

rules:
  - row_count > 1000:
      name: sufficient_revenue_data
      dimension: completeness
      description: "Revenue table must have more than 1000 rows for analysis"
```

{% endtab %}
{% endtabs %}

**Metadata recap:**

* `meta.name`: stable identifier you can follow over time.
* `meta.dimension`: the business concern the check protects.
* `meta.description`: human-readable explanation.

## Built-in checks

A built-in check names a metric and a limit. Vulcan computes the metric on the model table and compares it with that limit.

### No empty values

The column is required. A null fails the check.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      name: email_required
      dimension: completeness
      description: "Every user has an email"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - missing_count(email) = 0:
      name: email_required
      dimension: completeness
      description: "Every user has an email"
```

{% endtab %}
{% endtabs %}

`missing_percent(email) = 0` converts to the same expectation. A check that allows some nulls, such as `missing_count(email) < 5`, is skipped at load.

### Unique values

The column, or the combination of columns, is the grain. A repeated value fails the check.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_be_unique
    column: user_id
    meta:
      name: unique_user_ids
      dimension: uniqueness

  - expectation: expect_compound_columns_to_be_unique
    column_list: [user_id, plan_id]
    meta:
      name: unique_user_plan
      dimension: uniqueness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - duplicate_count(user_id) = 0:
      name: unique_user_ids
      dimension: uniqueness

  - duplicate_count(user_id, plan_id) = 0:
      name: unique_user_plan
      dimension: uniqueness
```

{% endtab %}
{% endtabs %}

`duplicate_percent(user_id) = 0` converts to the single-column expectation. A check that allows duplicates is skipped at load.

### Row count

The table has enough rows, or no more than you allow. Set `min_value` for "at least," `max_value` for "at most," or both for a range. Setting both to the same number means "exactly this many."

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 21
    meta:
      name: enough_users
      dimension: completeness

  - expectation: expect_table_row_count_to_be_between
    min_value: 10
    max_value: 1000
    meta:
      name: row_count_in_range
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - row_count > 20:
      name: enough_users
      dimension: completeness

  - row_count between 10 and 1000:
      name: row_count_in_range
      dimension: completeness
```

{% endtab %}
{% endtabs %}

`>`, `>=`, `<`, `<=`, and `=` are accepted on `row_count`. `row_count > 20` becomes a minimum of 21, because the expectation bound is inclusive.

The range check is useful when you know the table should land between two sizes: anything outside that range is suspicious. To count only a subset, such as active users, set `filter` on the file.

### Allowed and forbidden values

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_be_in_set
    column: status
    value_set: [active, churned, suspended]
    meta:
      name: valid_status
      dimension: validity

  - expectation: expect_column_values_to_not_be_in_set
    column: status
    value_set: [deleted]
    meta:
      name: status_not_deleted
      dimension: validity
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - invalid_count(status) = 0:
      name: valid_status
      valid values: [active, churned, suspended]

  - invalid_count(status) = 0:
      name: status_not_deleted
      invalid values: [deleted]
```

{% endtab %}
{% endtabs %}

`invalid_percent(status) = 0` with the same block converts to the same expectation. The rule needs one of the validity settings below. Without that setting, the rule is skipped.

| Rule setting                            | Expectation it becomes                                             |
| --------------------------------------- | ------------------------------------------------------------------ |
| `valid values`                          | `expect_column_values_to_be_in_set`                                |
| `invalid values`                        | `expect_column_values_to_not_be_in_set`                            |
| `valid regex`                           | `expect_column_values_to_match_regex` with `regex`                 |
| `invalid regex`                         | `expect_column_values_to_not_match_regex` with `regex`             |
| `valid format`                          | `expect_column_values_to_match_regex` using a built-in pattern     |
| `invalid format`                        | `expect_column_values_to_not_match_regex` using a built-in pattern |
| `valid length`                          | `expect_column_value_lengths_to_equal` with `value`                |
| `valid min length` / `valid max length` | `expect_column_value_lengths_to_be_between`                        |
| `valid min` / `valid max`               | `expect_column_values_to_be_between`                               |

Built-in format names include `email`, `uuid`, `integer`, `decimal`, `percentage`, `money`, `date iso 8601`, `date eu`, `date us`, `ip address`, and `phone number`.

Regex and range checks:

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_values_to_match_regex
    column: country_code
    regex: '^[A-Z]{2}$'
    meta:
      name: country_code_shape
      dimension: validity

  - expectation: expect_column_values_to_be_between
    column: seats
    min_value: 1
    max_value: 100
    meta:
      name: seats_range
      dimension: accuracy
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - invalid_count(country_code) = 0:
      name: country_code_shape
      valid regex: '^[A-Z]{2}$'

  - invalid_count(seats) = 0:
      name: seats_range
      valid min: 1
      valid max: 100
```

{% endtab %}
{% endtabs %}

### Numeric summaries

These built-in checks judge one number computed over the column: average, minimum, maximum, sum, or a percentile. `percentile` takes a fraction from 0 to 1, so `0.95` is the 95th percentile.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: expect_column_mean_to_be_between
    column: mrr
    min_value: 10
    max_value: 500
    meta:
      name: mrr_average
      dimension: accuracy

  - expectation: expect_column_min_to_be_between
    column: seats
    min_value: 1
    meta:
      name: seats_at_least_one
      dimension: accuracy

  - expectation: expect_column_max_to_be_between
    column: discount
    max_value: 100
    meta:
      name: discount_cap
      dimension: accuracy

  - expectation: expect_column_sum_to_be_between
    column: amount
    min_value: 0
    strict_min: true
    meta:
      name: amount_positive
      dimension: accuracy

  - expectation: expect_column_quantile_values_to_be_between
    column: mrr
    quantile_ranges:
      quantiles: [0.95]
      value_ranges: [[0, 10000]]
    meta:
      name: mrr_p95
      dimension: accuracy
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - avg(mrr) between 10 and 500:
      name: mrr_average

  - min(seats) >= 1:
      name: seats_at_least_one

  - max(discount) <= 100:
      name: discount_cap

  - sum(amount) > 0:
      name: amount_positive

  - percentile(mrr, 0.95) < 10000:
      name: mrr_p95
```

{% endtab %}
{% endtabs %}

`strict_min: true` and `strict_max: true` make the bound exclusive. Percentile bounds in the expectation are inclusive. A rule that uses `>` or `<` on a percentile is stored as the nearest inclusive bound.

### Values that must exist in another table

A value that is filled in here and missing from the other table fails the check. Nulls on the source side are ignored. The two column lists have the same length.

The rule form is a built-in expression. The expectation form is a custom query, because that is how the reference is scored.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT src.*
      FROM {batch} AS src
      LEFT JOIN customer.users AS ref
        ON src.user_id = ref.id
      WHERE src.user_id IS NOT NULL
        AND ref.id IS NULL
    meta:
      name: user_exists
      dimension: consistency
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - values in (user_id) must exist in customer.users (id):
      name: user_exists
      dimension: consistency
```

{% endtab %}
{% endtabs %}

`{batch}` in the expectation query is the table being checked, including the file's `filter` when one is set. The rule line is converted into that query.

### Freshness (`vulcan_freshness`)

Freshness answers a business question: is the newest timestamp in this table recent enough?

Vulcan reads the latest non-null value in the column, compares it with the moment the check runs, and turns that gap into an age in seconds. The check passes when that age matches the operator and the limit. `freshness(updated_at) < 1d` means "the newest `updated_at` is less than one day old."

The expectation name is `vulcan_freshness`. A `freshness(...)` rule is converted into it.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: vulcan_freshness
    column: updated_at
    op: "<"
    threshold_seconds: 86400
    meta:
      name: updated_within_a_day
      dimension: timeliness
      description: "The newest update is less than one day old"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - freshness(updated_at) < 1d:
      name: updated_within_a_day
      dimension: timeliness
      description: "The newest update is less than one day old"
```

{% endtab %}
{% endtabs %}

`column` is the timestamp column. `op` is `<`, `<=`, `=`, `>`, or `>=`. `threshold_seconds` is the age limit. On a rule line, the limit is a number plus a unit:

| Rule unit | Seconds to write on the expectation | Meaning    |
| --------- | ----------------------------------- | ---------- |
| `30s`     | `30`                                | 30 seconds |
| `15m`     | `900`                               | 15 minutes |
| `24h`     | `86400`                             | 24 hours   |
| `1d`      | `86400`                             | 1 day      |
| `1w`      | `604800`                            | 1 week     |

If every value in the column is null, the check is recorded as an error: there is no timestamp to judge. A timestamp stored without a time zone is read in the project's `naive_timestamp_timezone`, which defaults to UTC. A `filter` on the file limits which rows contribute the newest timestamp.

{% hint style="warning" %}
**Change-over-time and anomaly detection checks are deprecated**

`vulcan_change_over_time` and `vulcan_anomaly_detection` are deprecated, along with the `change ...` and `anomaly detection for ...` lines under `rules:`. Existing projects that use these checks should review their usage. Don't use them in new data quality files.
{% endhint %}

## Custom checks

A custom check is for a promise the built-in metrics cannot say. You write a SQL query that returns the bad rows. Any returned row fails the check. An empty result passes.

Query the model named in `depends_on`. Select the columns a person needs in order to see why the row failed.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT user_id, email
      FROM customer.users
      WHERE email NOT LIKE '%@%'
    meta:
      name: invalid_emails
      dimension: validity
      description: "Email must contain @"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - failed rows:
      name: invalid_emails
      dimension: validity
      fail query: |
        SELECT user_id, email
        FROM customer.users
        WHERE email NOT LIKE '%@%'
      description: "Email must contain @"
```

{% endtab %}
{% endtabs %}

On an expectation, the SQL goes in `unexpected_rows_query`. On a rule, `fail query` is required, and every `failed rows` rule needs its own `name`.

**How it works:**

* `unexpected_rows_query`: a `SELECT` statement that returns invalid rows.
* Empty result: the check passes (no invalid rows found).
* One or more rows: the check fails, and Vulcan keeps example rows so you can see what's wrong.
* `{batch}`: write `FROM {batch}` to read the table being checked with the file's `filter` applied.

**Complex validation:**

You can use joins and CTEs. This finds orders that reference customers that don't exist: a classic referential integrity check.

{% tabs %}
{% tab title="Expectations" %}

```yaml
expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT o.order_id, o.customer_id
      FROM analytics.orders o
      LEFT JOIN analytics.customers c ON o.customer_id = c.customer_id
      WHERE c.customer_id IS NULL
    meta:
      name: orphaned_orders
      dimension: consistency
```

{% endtab %}

{% tab title="Rules" %}

```yaml
rules:
  - failed rows:
      name: orphaned_orders
      dimension: consistency
      fail query: |
        SELECT o.order_id, o.customer_id
        FROM analytics.orders o
        LEFT JOIN analytics.customers c ON o.customer_id = c.customer_id
        WHERE c.customer_id IS NULL
```

{% endtab %}
{% endtabs %}

### Failed-row samples

The project keeps example failing rows when `capture_failed_row_samples` is on, which is the default. `max_items` in the project data quality config caps how many examples are stored. The default is 20.

## Built-in check lines that do not run yet

These rule lines are read and then skipped when the file loads. The rest of the file still runs. Write the expectation in the right-hand column.

| Rule line that is skipped                                                                | Expectation to write                                                                          |
| ---------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------- |
| `schema`, `schema evolution`                                                             | A column contract on the model, or a custom check that selects unexpected columns             |
| `distribution(status)`, `distribution_difference(status)`                                | A custom check                                                                                |
| `valid_count(email) > 0`                                                                 | `expect_table_row_count_to_be_between` together with a validity expectation                   |
| `min_length(email) > 3`, `max_length(email) < 255`, `avg_length(email) > 3`              | `expect_column_value_lengths_to_be_between`, or a custom check using `LENGTH`                 |
| `stddev(mrr)`, `stddev_pop`, `stddev_samp`, `variance`, `var_pop`, `var_samp`            | A custom check, or leave the spread to column profiles                                        |
| `missing_count(email) < 5`, `missing_percent(email) < 5`, and other allowances for nulls | `expect_column_values_to_not_be_null` when the promise is "no nulls"                          |
| `duplicate_count(email) < 5` and other allowances for duplicates                         | `expect_column_values_to_be_unique` when the promise is "no duplicates"                       |
| `invalid_count(status) = 0` with no validity setting                                     | Add `valid values`, `valid regex`, `valid min`, or the matching expectation                   |
| `missing values`, `missing format`, `missing regex` on an invalid rule                   | Define the valid side (`valid values`, `valid regex`, `valid format`) or write a custom check |
| `avg(mrr) != 0` and any `!=` on `avg`, `min`, `max`, or `sum`                            | A range expectation, or a custom check                                                        |
| `row_count` with only `warn` and `fail` bands                                            | `expect_table_row_count_to_be_between` with the limit you actually want                       |

A length promise written as an expectation:

```yaml
expectations:
  - expectation: expect_column_value_lengths_to_be_between
    column: email
    min_value: 5
    max_value: 255
    meta:
      name: email_length
      dimension: validity
```

## A file you can copy

Built-in checks and one custom check, all written as expectations:

```yaml
kind: dq
name: users_dq
depends_on: customer.users

profiles:
  - email
  - status

expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      name: email_required
      dimension: completeness
      description: "Every user has an email"

  - expectation: expect_column_values_to_be_unique
    column: user_id
    meta:
      name: unique_user_ids
      dimension: uniqueness

  - expectation: expect_table_row_count_to_be_between
    min_value: 1
    meta:
      name: users_not_empty
      dimension: completeness

  - expectation: expect_column_values_to_be_in_set
    column: status
    value_set: [active, churned, suspended]
    meta:
      name: valid_status
      dimension: validity

  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT user_id, email
      FROM customer.users
      WHERE email NOT LIKE '%@%'
    meta:
      name: invalid_emails
      dimension: validity
      description: "Email must contain @"

  - expectation: vulcan_freshness
    column: updated_at
    op: "<"
    threshold_seconds: 86400
    meta:
      name: updated_within_a_day
      dimension: timeliness
      description: "The newest update is less than one day old"
```

## Data profiling

What profiling captures, where it's stored, how to query it, and how to turn what you learn into checks.

### What is profiling?

**Profiles automatically collect statistical metrics about your data over time.**

Unlike checks (which validate), profiles **observe and track** data characteristics. They're like a data scientist watching your tables and taking notes.

You enable profiling in a `kind: dq` file via the top-level `profiles:` list. Add the columns you want to track alongside (or instead of) the checks for the same model:

{% tabs %}
{% tab title="Expectations" %}

```yaml
# dq/customers.yml
kind: dq
name: customers_dq
depends_on: analytics.customers

profiles:
  - revenue
  - signup_date
  - customer_tier
  - order_count

expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# dq/customers.yml
kind: dq
name: customers_dq
depends_on: analytics.customers

profiles:
  - revenue
  - signup_date
  - customer_tier
  - order_count

rules:
  - missing_count(email) = 0:
      dimension: completeness
```

{% endtab %}
{% endtabs %}

A file can be profile-only (omit `expectations:`), checks-only (omit `profiles:`), or both. Vulcan collects the listed metrics each time the model in `depends_on:` builds successfully. Profiles run for numeric and text columns, and they describe the whole table, including rows the file's `filter` excludes.

**What gets profiled:**

**Table-level metrics:**

* Row count

**Column-level metrics (all listed columns):**

* Null count & percentage
* Distinct count
* Duplicate count
* Uniqueness percentage

**Numeric columns:**

* Min, max, avg, sum
* Standard deviation, variance
* Histogram buckets

**Text columns:**

* Min, max, avg length
* Most frequent values

Profiles track how things change over time so you can spot trends and drift.

### Profile storage

Vulcan stores profiles in the `_check_profiles` table, which you can query like any other table:

| Column         | Meaning                                                                                                       |
| -------------- | ------------------------------------------------------------------------------------------------------------- |
| `id`           | Unique identifier for this metric row                                                                         |
| `run_id`       | Identifies which profiling run this metric belongs to                                                         |
| `table_name`   | Name of the table being profiled                                                                              |
| `column_name`  | Name of the column being profiled (NULL for table-level metrics like row\_count)                              |
| `profile_type` | The type of metric, e.g., row\_count, distinct, missing\_count, frequent\_values, min, max, avg\_length, etc. |
| `value_number` | Numeric metric value (for metrics like row\_count, distinct, min, max, avg, etc.)                             |
| `value_text`   | Used for text values (rare)                                                                                   |
| `value_json`   | JSON-encoded metric (for histograms, frequent values, etc.)                                                   |
| `value_type`   | Type of value stored (number, json, etc.)                                                                     |
| `profiled_at`  | When the profiling was performed (epoch ms)                                                                   |
| `created_ts`   | When the row was inserted                                                                                     |

### Querying profiles

Two example queries against `_check_profiles`: one tracking a metric over time, one comparing it to its historical average.

#### Track missing count over time

See how null counts change:

```sql
SELECT
  to_timestamp(profiled_at/1000)::date AS date,
  value_number AS missing_count
FROM _check_profiles
WHERE table_name = 'warehouse.hello.subscriptions'
  AND column_name = 'mrr'
  AND profile_type = 'missing_count'
ORDER BY profiled_at DESC
LIMIT 30;  -- Last 30 days
```

Shows a time series of missing values for spotting trends.

#### Monitor data drift

Compare current values to historical averages:

```sql
WITH latest_profile AS (
  -- Pick the most recent profiling timestamp for that table/column
  SELECT profiled_at
  FROM _check_profiles
  WHERE table_name = 'warehouse.hello.subscriptions'
    AND column_name = 'mrr'
  ORDER BY profiled_at DESC
  LIMIT 1
),

current AS (
  -- Get the most recent distinct count and average value from that profiling run
  SELECT
    MAX(CASE WHEN profile_type = 'distinct' THEN value_number END)     AS distinct_count,
    MAX(CASE WHEN profile_type IN ('avg', 'mean', 'average', 'avg_value') THEN value_number END) AS avg_value
  FROM _check_profiles p
  JOIN latest_profile l ON p.profiled_at = l.profiled_at
  WHERE p.table_name = 'warehouse.hello.subscriptions'
    AND p.column_name = 'mrr'
),

historical AS (
  -- 30-day historical averages (profiled_at stored as epoch ms → convert to timestamp)
  SELECT
    AVG(CASE WHEN profile_type = 'distinct' THEN value_number END)      AS avg_distinct,
    AVG(CASE WHEN profile_type IN ('avg', 'mean', 'average', 'avg_value') THEN value_number END) AS avg_mrr
  FROM _check_profiles
  WHERE table_name = 'warehouse.hello.subscriptions'
    AND column_name = 'mrr'
    AND to_timestamp(profiled_at/1000) >= CURRENT_DATE - INTERVAL '30 days'
)

SELECT
  c.distinct_count,
  h.avg_distinct,
  CASE
    WHEN h.avg_distinct IS NULL THEN NULL
    ELSE (c.distinct_count - h.avg_distinct) / NULLIF(h.avg_distinct, 0) * 100
  END AS distinct_change_pct,
  c.avg_value,
  h.avg_mrr,
  CASE
    WHEN h.avg_mrr IS NULL THEN NULL
    ELSE (c.avg_value - h.avg_mrr) / NULLIF(h.avg_mrr, 0) * 100
  END AS mrr_change_pct
FROM current c, historical h;
```

This query compares current metrics to 30-day historical averages and calculates percentage changes. Use it to detect drift.

### Using profiles to inform checks

**Workflow:**

1. **Enable profiling** on new models (add a `profiles:` list to the `kind: dq` file for that model)
2. **Observe patterns** for 30+ days (let profiles collect data)
3. **Identify anomalies** in profile data (query `_check_profiles` and look for trends)
4. **Create checks** based on observed patterns (now you know what's normal)

**Example:**

{% tabs %}
{% tab title="Expectations" %}

```yaml
# Step 1: Enable profiling alongside the checks
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 1
    meta:
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# Step 1: Enable profiling alongside the rules
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

rules:
  - row_count > 0:
      dimension: completeness
```

{% endtab %}
{% endtabs %}

```sql
-- Step 2: Query profiles after 30 days
SELECT
    MIN(value_number) AS min_revenue,
    MAX(value_number) AS max_revenue,
    AVG(value_number) AS typical_revenue,
    STDDEV(value_number) AS revenue_stddev
FROM _check_profiles
WHERE table_name = 'warehouse.analytics.orders'
  AND column_name = 'revenue'
  AND profile_type IN ('avg', 'mean', 'average', 'avg_value')
  AND to_timestamp(profiled_at/1000) >= CURRENT_DATE - INTERVAL '30 days';

-- Results:
--   min_revenue:     45000
--   max_revenue:     75000
--   typical_revenue: 58000
--   revenue_stddev:  6000
```

{% tabs %}
{% tab title="Expectations" %}

```yaml
# Step 3: Add checks informed by what you observed
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

expectations:
  - expectation: expect_column_mean_to_be_between
    column: revenue
    min_value: 40000
    max_value: 80000
    meta:
      name: revenue_within_observed_range
      dimension: accuracy
      description: "Based on 30-day historical analysis"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# Step 3: Add rules informed by what you observed
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

rules:
  - avg(revenue) between 40000 and 80000:
      name: revenue_within_observed_range
      dimension: accuracy
      description: "Based on 30-day historical analysis"
```

{% endtab %}
{% endtabs %}

Now your checks are informed by actual data patterns, not guesses.

### Profile best practices

**DO:**

* Profile high-value production tables (the ones that matter)
* Profile columns used in downstream analysis (if it's important, profile it)
* Use profiles to understand new data sources (what does this data look like?)
* Query profiles to detect data drift (is something changing?)
* Use profiles to inform check thresholds (data-driven thresholds are better)

**DON'T:**

* Profile sensitive/PII columns (privacy risk, be careful)
* Profile every column (performance overhead, pick what matters)
* Profile temporary/experimental models (waste of resources)
* Use profiles as a replacement for checks (they serve different purposes)
* Profile very high-frequency models (storage cost adds up)

**When to use profiles:**

* Building new models (understand the data first)
* Monitoring production tables (watch for changes)
* Detecting data drift (is the data changing?)
* Informing assertion and check strategy (what should we check?)
* Debugging data quality issues (what's normal vs abnormal?)

**When to skip profiles:**

* Temporary models (they won't be around long)
* Models with sensitive data (privacy concerns)
* Very high-frequency models (> 100 runs/day, storage costs)
* Models where you only need pass/fail validation (profiles are overkill)

## Advanced patterns

Patterns for validation that goes beyond a single check on a single column.

### Cross-model validation

Each file targets a single model via `depends_on:`, but custom check queries can reference any model in your warehouse. Anchor the file on the model whose quality is at stake, then JOIN to the related models in SQL.

{% tabs %}
{% tab title="Expectations" %}

```yaml
# dq/orders_cross_model.yml
kind: dq
name: orders_cross_model_dq
depends_on: analytics.orders

expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT o.order_id, o.customer_id
      FROM analytics.orders o
      LEFT JOIN analytics.customers c ON o.customer_id = c.customer_id
      WHERE c.customer_id IS NULL
    meta:
      name: orphaned_orders
      dimension: consistency
      description: "All orders must have a valid customer"

  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT
        o.order_id,
        o.revenue AS order_revenue,
        r.revenue AS revenue_table_revenue
      FROM analytics.orders o
      JOIN analytics.revenue r ON o.order_id = r.order_id
      WHERE ABS(o.revenue - r.revenue) > 0.01
    meta:
      name: revenue_mismatch
      dimension: consistency
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# dq/orders_cross_model.yml
kind: dq
name: orders_cross_model_dq
depends_on: analytics.orders

rules:
  - failed rows:
      name: orphaned_orders
      dimension: consistency
      fail query: |
        SELECT o.order_id, o.customer_id
        FROM analytics.orders o
        LEFT JOIN analytics.customers c ON o.customer_id = c.customer_id
        WHERE c.customer_id IS NULL
      description: "All orders must have a valid customer"

  - failed rows:
      name: revenue_mismatch
      dimension: consistency
      fail query: |
        SELECT
          o.order_id,
          o.revenue AS order_revenue,
          r.revenue AS revenue_table_revenue
        FROM analytics.orders o
        JOIN analytics.revenue r ON o.order_id = r.order_id
        WHERE ABS(o.revenue - r.revenue) > 0.01
```

{% endtab %}
{% endtabs %}

The first check finds orders without valid customers (orphaned records). The second ensures revenue matches across tables. Both belong to the `orders` file because that's the model whose quality they describe. For a plain "this key must exist over there" promise, the built-in [values that must exist in another table](#values-that-must-exist-in-another-table) check is shorter.

### Time-based validation

Ensure data timeliness. Is your data fresh? Are updates happening on schedule? Use `vulcan_freshness` for "the newest row is recent enough," and custom checks for row-level timing rules.

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: orders_timeliness_dq
depends_on: analytics.orders

expectations:
  - expectation: vulcan_freshness
    column: updated_at
    op: "<"
    threshold_seconds: 86400
    meta:
      name: orders_updated_daily
      dimension: timeliness

  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT *
      FROM analytics.orders
      WHERE updated_at < CURRENT_TIMESTAMP - INTERVAL '24 hours'
        AND status != 'completed'
    meta:
      name: stale_data
      dimension: timeliness
      description: "Pending orders should update within 24 hours"

  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT *
      FROM analytics.orders
      WHERE order_date > CURRENT_DATE
    meta:
      name: future_dates
      dimension: timeliness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: orders_timeliness_dq
depends_on: analytics.orders

rules:
  - freshness(updated_at) < 1d:
      name: orders_updated_daily
      dimension: timeliness

  - failed rows:
      name: stale_data
      dimension: timeliness
      fail query: |
        SELECT *
        FROM analytics.orders
        WHERE updated_at < CURRENT_TIMESTAMP - INTERVAL '24 hours'
          AND status != 'completed'
      description: "Pending orders should update within 24 hours"

  - failed rows:
      name: future_dates
      dimension: timeliness
      fail query: |
        SELECT *
        FROM analytics.orders
        WHERE order_date > CURRENT_DATE
```

{% endtab %}
{% endtabs %}

The first check confirms the table was updated within a day. The second finds stale pending orders. The third catches future dates (data entry errors).

### Statistical outlier detection

Custom outlier detection using SQL. Use this when a simple range check isn't precise enough:

{% tabs %}
{% tab title="Expectations" %}

```yaml
kind: dq
name: revenue_outliers_dq
depends_on: analytics.revenue

expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      WITH stats AS (
        SELECT
          AVG(revenue) AS mean,
          STDDEV(revenue) AS stddev
        FROM analytics.revenue
      )
      SELECT r.*,
        (r.revenue - s.mean) / s.stddev AS z_score
      FROM analytics.revenue r, stats s
      WHERE ABS((r.revenue - s.mean) / s.stddev) > 3
    meta:
      name: revenue_outliers
      dimension: accuracy
```

{% endtab %}

{% tab title="Rules" %}

```yaml
kind: dq
name: revenue_outliers_dq
depends_on: analytics.revenue

rules:
  - failed rows:
      name: revenue_outliers
      dimension: accuracy
      fail query: |
        WITH stats AS (
          SELECT
            AVG(revenue) AS mean,
            STDDEV(revenue) AS stddev
          FROM analytics.revenue
        )
        SELECT r.*,
          (r.revenue - s.mean) / s.stddev AS z_score
        FROM analytics.revenue r, stats s
        WHERE ABS((r.revenue - s.mean) / s.stddev) > 3
```

{% endtab %}
{% endtabs %}

This finds rows where revenue is more than 3 standard deviations from the mean (classic outlier detection). The z-score tells you how extreme each outlier is.

## Best practices

Guidelines for organizing, naming, tuning, and layering checks as a project grows.

### File organization

Organize your data quality files in a way that makes sense for your team. Here are 2 common approaches:

**By domain:**

```
dq/
├── customers/
│   ├── customers_completeness.yml
│   ├── customers_validity.yml
│   └── customers_consistency.yml
├── orders/
│   ├── orders_completeness.yml
│   └── orders_timeliness.yml
└── revenue/
    └── revenue_accuracy.yml
```

**By priority:**

```
dq/
├── customers_critical.yml      # Must never fail
├── customers_important.yml     # Should rarely fail
├── customers_monitoring.yml    # Track trends
└── customers_experimental.yml  # Testing new checks
```

Each file still maps to a single model via `depends_on:`. Splitting by dimension or priority is just a way to keep individual files small and ownership clear.

### Naming conventions

**Use descriptive names:**

{% tabs %}
{% tab title="Expectations" %}

```yaml
# Bad: what does "check1" tell you?
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      name: check1
      dimension: completeness

# Good: clear and descriptive
expectations:
  - expectation: expect_column_values_to_not_be_null
    column: email
    meta:
      name: no_missing_customer_emails
      dimension: completeness
      description: "All customers must have an email for marketing"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# Bad: what does "check1" tell you?
rules:
  - missing_count(email) = 0:
      name: check1
      dimension: completeness

# Good: clear and descriptive
rules:
  - missing_count(email) = 0:
      name: no_missing_customer_emails
      dimension: completeness
      description: "All customers must have an email for marketing"
```

{% endtab %}
{% endtabs %}

**Naming pattern:**

* `<dimension>_<what>_<constraint>` or `<what>_<constraint>`
* Start with a letter, and use only letters, digits, and underscores
* Keep names unique within a file
* Examples:
  * `completeness_email_required` or `no_missing_emails`
  * `validity_email_format` or `valid_email_format`
  * `uniqueness_email_no_duplicates` or `unique_emails`
  * `timeliness_order_within_24hrs` or `orders_update_daily`

The key is that someone reading the name should understand what it checks without looking at the code. Set `meta.name` on every check you want to follow over time.

When you change what a check tests, give it a new name too, for example by adding a `_v2` suffix. Reusing a name with new logic fails the run. See [Saving a scan fails after you change a check's logic](#saving-a-scan-fails-after-you-change-a-checks-logic).

### Threshold selection

**Start conservative, adjust based on data:**

{% tabs %}
{% tab title="Expectations" %}

```yaml
# Step 1: Start with a wide range
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 101
    meta:
      name: sufficient_orders_v1
      dimension: completeness

# Step 2: Monitor for 30 days, see actual range: 5000-10000

# Step 3: Tighten based on observed patterns
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 4000
    max_value: 12000
    meta:
      name: sufficient_orders_v2
      dimension: completeness
      description: "Based on 30-day historical analysis"
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# Step 1: Start with a wide range
rules:
  - row_count > 100:
      name: sufficient_orders_v1
      dimension: completeness

# Step 2: Monitor for 30 days, see actual range: 5000-10000

# Step 3: Tighten based on observed patterns
rules:
  - row_count between 4000 and 12000:
      name: sufficient_orders_v2
      dimension: completeness
      description: "Based on 30-day historical analysis"
```

{% endtab %}
{% endtabs %}

Don't set thresholds from guesses. Run profiles for a few weeks first, read the observed `min`, `max`, and `stddev`, then set checks from what you see.

**Use profiles to inform thresholds:**

```sql
-- Query profiles to understand your data
SELECT
  MIN(value_number) AS min_observed,
  MAX(value_number) AS max_observed,
  AVG(value_number) AS typical,
  STDDEV(value_number) AS stddev
FROM _check_profiles
WHERE table_name = 'warehouse.analytics.orders'
  AND profile_type = 'row_count'
  AND to_timestamp(profiled_at/1000) >= CURRENT_DATE - INTERVAL '90 days';

-- Set threshold as: typical ± 3*stddev
```

This gives you data-driven thresholds instead of wild guesses.

### Integration strategy

**Layer validation:**

```sql
-- LAYER 1: Assertions (critical - blocks)
-- Stop bad data at the door
MODEL (
  name analytics.orders,
  assertions (
    not_null(columns := (order_id, customer_id)),
    unique_values(columns := (order_id))
  )
);
```

{% tabs %}
{% tab title="Expectations" %}

```yaml
# LAYER 2 + 3: Data quality checks + profiles (monitoring + observation)
# dq/orders.yml
# Profiles observe trends, checks record a score. Neither blocks the model.
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 5000
    max_value: 15000
    meta:
      name: order_count_in_range
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# LAYER 2 + 3: Data quality checks + profiles (monitoring + observation)
# dq/orders.yml
kind: dq
name: orders_dq
depends_on: analytics.orders

profiles:
  - order_count
  - revenue
  - customer_tier

rules:
  - row_count between 5000 and 15000:
      name: order_count_in_range
      dimension: completeness
```

{% endtab %}
{% endtabs %}

3 layers, each with a clear job: assertions stop bad rows, data quality checks record a non-blocking score, and profiles record what "normal" looks like. The two monitoring layers share the same `kind: dq` file, so a single file describes everything Vulcan should watch for one model.

## Troubleshooting

Common problems and how to work through them.

### Check failures

Steps to figure out why a check failed and see the specific rows behind it.

#### Investigate a failed check

Start with the failed-row samples Vulcan kept for the check. They're stored when `capture_failed_row_samples` is on (the default), up to `max_items` rows (default 20).

To see every failing row, run the custom query yourself with [`vulcan fetchdf`](https://v2.dataos.info/references/resources/vulcan/cli.md#fetchdf). If the query uses `{batch}`, replace it with the model name and the file's `filter`:

```bash
vulcan fetchdf "SELECT user_id, email FROM customer.users WHERE email NOT LIKE '%@%'"
```

#### A check never runs

A check line can be read and then skipped when the file loads. The rest of the file still runs. Common causes:

* An allowance instead of a strict promise, such as `missing_count(email) < 5` or `duplicate_count(email) < 5`.
* An `invalid_count` or `invalid_percent` rule with no validity setting.
* A metric that isn't supported yet, such as `stddev(mrr)` or `min_length(email) > 3`.

See [Built-in check lines that do not run yet](#built-in-check-lines-that-do-not-run-yet) for the expectation to write instead.

#### A check has no result, or an error result

* The model **failed to build**. It's left unscored, because there is no finished table to judge.
* The refresh or plan apply **hit an error**. Scoring for that whole job is skipped.
* A **freshness** check found only nulls in its column. It's recorded as an error, because there is no timestamp to judge.

#### Saving a scan fails after you change a check's logic

**Problem:** The run fails while saving the data quality scan. The log repeats `[gx] Failed to persist scan for model <model> (attempt n/4)` and ends with:

```
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "_dq_definitions_check_name_unique_idx"
DETAIL:  Key (environment, model_name, dimension, check_name)=(prod, silverv1.fct_daily_sales, accuracy, shipment_rate_matches_counts) already exists.
```

The `DETAIL` line names the failing check. Here it's `shipment_rate_matches_counts` on `silverv1.fct_daily_sales`, dimension `accuracy`, environment `prod`.

**Cause:** The logic of an existing check was changed without renaming it. Vulcan treats the changed logic as a new check, but that name is already stored for the same environment, model, and dimension. Every retry hits the same conflict.

**Solution: Rename the check**

Give the check a new name, then rerun. For example, change `shipment_rate_matches_counts` to `shipment_rate_matches_counts_v2`:

{% tabs %}
{% tab title="Expectations" %}

```yaml
    meta:
      name: shipment_rate_matches_counts_v2   # was: shipment_rate_matches_counts
      dimension: accuracy
```

{% endtab %}

{% tab title="Rules" %}

```yaml
      name: shipment_rate_matches_counts_v2   # was: shipment_rate_matches_counts
      dimension: accuracy
```

{% endtab %}
{% endtabs %}

The old definition stays under the original name, and the updated logic is stored under the new one.

### Performance issues

What to do when a check takes too long to run.

#### Slow check queries

**Problem:** A check takes too long to run.

**Solution: Add a filter**

Set `filter` on the file and read from `{batch}`, so the custom query only scans the rows that matter:

```yaml
# Slow: scans entire table
kind: dq
name: orders_dq
depends_on: analytics.orders

expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT * FROM analytics.orders
      WHERE email NOT LIKE '%@%'
    meta:
      dimension: validity

# Fast: file-level filter scopes the check to recent data
kind: dq
name: orders_dq
depends_on: analytics.orders

filter: "order_date >= CURRENT_DATE - INTERVAL '30 days'"

expectations:
  - expectation: unexpected_rows_expectation
    unexpected_rows_query: |
      SELECT * FROM {batch}
      WHERE email NOT LIKE '%@%'
    meta:
      dimension: validity
```

Filtering reduces the amount of data the check needs to scan, which makes it faster. Built-in checks on the file use the filter automatically.

### False positives

What to do when a check fails during normal variance.

#### Threshold too strict

**Problem:** Check fails during normal variance.

{% tabs %}
{% tab title="Expectations" %}

```yaml
# Too strict: exact match is unrealistic
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 10000
    max_value: 10000
    meta:
      dimension: completeness

# Allow variance: more realistic
expectations:
  - expectation: expect_table_row_count_to_be_between
    min_value: 9000
    max_value: 11000
    meta:
      dimension: completeness
```

{% endtab %}

{% tab title="Rules" %}

```yaml
# Too strict: exact match is unrealistic
rules:
  - row_count = 10000:
      dimension: completeness

# Allow variance: more realistic
rules:
  - row_count between 9000 and 11000:
      dimension: completeness
```

{% endtab %}
{% endtabs %}

Real data has variance. Strict thresholds just produce false positives.

## Summary

Data quality checks give you a way to score data quality over time without blocking your models. Here's what we covered:

### Core concepts

**1. Data quality files**

* YAML files in `dq/` with `kind: dq` (`type: dq` still loads)
* One checklist per file, targeting a single model via `depends_on:`
* Top-level fields: `kind`, `name`, `depends_on`, optional `filter:` and `profiles:`, then `expectations:`
* Run after a successful build or plan apply; failed models are left unscored
* Non-blocking: a failed check is recorded, not rolled back
* Integrate with Activity API

**2. Expectations and metadata**

* Each check is an `expectation:` with its arguments
* Optional `meta` block: `name`, `dimension`, `description`
* Legacy `rules:` files still load and are converted into expectations at load time, until the `rules:` syntax is deprecated in December 2026

**3. Check types**

* Built-in checks: not-null, unique, row count, allowed values, regex, ranges, numeric summaries, references to another table
* Freshness: `vulcan_freshness`
* Custom checks: `unexpected_rows_expectation` with `unexpected_rows_query`

**4. Data profiling**

* Automatic statistical metric collection
* Enabled via the `profiles:` list at the top of a `kind: dq` file
* Stored in `_check_profiles` table
* Observe patterns without validation
* Inform check threshold selection

**5. Data quality strategy**

* **Assertions**: Critical, blocking (stop bad data)
* **Data quality checks**: Monitoring, non-blocking (record a durable score)
* **Profiles**: Observation, tracking (understand what's normal)

Start simple. Use profiles to understand your data, then write expectations based on what you learn. If a check is throwing too many false positives, adjust the threshold or scope it with a filter. The goal is better data quality, not perfect check scores.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/vulcan/quality/data-quality.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
