Test the assumptions that matter

Turn assumptions into executable checks

A successful SQL query only proves that the database accepted the query. Data checks prove specific claims about the result.

Choose checks from the model contract and how the source changes. More checks are not automatically better. A wrong unique check creates noise, while a missing duplicate-row check can let duplicated revenue reach a dashboard.

Check order IDs and status values

Add this metadata to the Bruin header in commerce/assets/staging/stg_orders.sql:

columns:
  - name: order_id
    type: integer
    primary_key: true
    checks:
      - name: not_null
      - name: unique
  - name: customer_id
    type: string
    checks:
      - name: not_null
  - name: order_status
    type: string
    checks:
      - name: not_null
      - name: accepted_values
        value: ["paid", "pending", "refunded"]
  - name: order_amount
    type: decimal
    checks:
      - name: non_negative

These checks cover source assumptions that affect every later model. If a new status arrives, the pipeline should stop until somebody decides how that status affects revenue.

Check the reporting table for duplicate rows

Add this metadata to the header in commerce/assets/analytics/customer_daily_revenue.sql, after its materialization block:

columns:
  - name: customer_id
    type: string
    primary_key: true
    checks:
      - name: not_null
  - name: order_date
    type: date
    primary_key: true
    checks:
      - name: not_null
  - name: recognized_revenue
    type: decimal
    checks:
      - name: non_negative
custom_checks:
  - name: unique_customer_day
    query: |
      SELECT customer_id, order_date
      FROM analytics.customer_daily_revenue
      GROUP BY customer_id, order_date
      HAVING COUNT(*) > 1
    count: 0
  - name: has_rows
    query: "SELECT COUNT(*) > 0 FROM analytics.customer_daily_revenue"
    value: 1

The custom check returns any duplicate customer_id and order_date pairs, and expects zero rows. Testing either column as unique by itself would reject valid data.

Ask your coding agent

Run the checks

bruin validate commerce/pipeline.yml
bruin run commerce/pipeline.yml

The run should finish with all checks passing.

Watch a useful failure

Temporarily add this row to the end of commerce/assets/raw/orders.csv:

1006,C004,2026-01-03 14:20:00,paid,120.00

Run the pipeline again. The unique check on staging.stg_orders.order_id should fail. Read the error, remove the duplicate source row, and rerun the pipeline.

This is the normal job of a data check: stop a known bad state close to its source and give the person on call a useful place to start.

Checkpoint

Finish with the original six order rows and a passing pipeline. You have now tested the source values, unique order IDs, unique customer-date rows, and one business rule.