Data Quality Monitoring

version: "1"
datasources:
  - id: pg
    type: postgresql
    configuration:
      host: localhost
      port: 5432
      username: readonly_user
      password: ${PG_PASSWORD}
      database: analytics
    datasets:
      - public.users
      - public.orders

version: "1"
rules:
  - dataset: pg@[public.users]
    checks:
      # Schema-level: make sure expected columns exist
      - schema_check:
          expect_columns:
            columns: [user_id, email, created_at, country]
        desc: "Users table must have core columns"
        on_fail: error

      # Table-level: volume sanity check
      - row_count between 1000 and 1000000:
          desc: "Should have a reasonable number of users"
          on_fail: error

      # Column-level checks
      - not_null(user_id):
          desc: "User ID is required"

      - not_null(email):
          desc: "Email is required"

      - uniqueness(email):
          desc: "Emails must be unique"
          on_fail: error

      - freshness(created_at) < 7d:
          desc: "Should have new users in the last week"
          on_fail: warn

# Columns exist (any order)
- schema_check:
    expect_columns:
      columns: [id, name, created_at]
  desc: "Required columns exist"

# Columns in specific order
- schema_check:
    expect_columns_ordered:
      columns_order: [id, name, email, created_at]
  desc: "Columns are in expected order"

# Sensitive columns are absent
- schema_check:
    columns_not_present:
      columns: [credit_card_number, ssn]
      pattern: "pii_*"
  desc: "No PII columns in this table"

- row_count > 1000:
    desc: "Must have minimum data volume"

- row_count between 1000 and 1000000:
    desc: "Row count within expected range"

# Custom SQL for complex validations
- raw_query:
    query: "SELECT COUNT(*) FROM {{dataset}} WHERE status = 'pending' AND created_at < NOW() - INTERVAL '24 hours'"
    desc: "No orders stuck in pending for over 24h"
    on_fail: warn

# Null checks
- not_null(user_id):
    desc: "User ID is mandatory"

# Uniqueness
- uniqueness(email):
    desc: "Emails must be unique"
    on_fail: error

# Numeric range
- min(price) > 0:
    desc: "Price must be positive"
- max(price) < 50000:
    desc: "Price within expected range"
- avg(price) between 20.0 and 500.0:
    desc: "Average price looks reasonable"

# Statistical checks
- stddev(trip_distance) < 100:
    desc: "Trip distance variation within normal range"

# Freshness
- freshness(updated_at) < 24h:
    desc: "Data should update daily"
    on_fail: warn

version: "1"
rules:
  # ClickHouse: NYC Taxi dataset
  - dataset: ch@[nyc_taxi.trips_small]
    where: "pickup_datetime > '2024-01-01'"
    checks:
      - schema_check:
          expect_columns:
            columns: [trip_id, fare_amount, trip_distance]
        desc: "Required columns exist"
        on_fail: error

      - row_count between 10000 and 3500000:
          desc: "Dataset has a reasonable number of trips"

      - not_null(trip_id):
          desc: "Trip ID is mandatory"
      - uniqueness(trip_id):
          desc: "Trip IDs must be unique"

      - min(trip_distance) >= 0:
          desc: "Trip distance cannot be negative"
      - min(fare_amount) > 0:
          desc: "Fare amount should be positive"
      - avg(trip_distance) between 1.0 and 20.0:
          desc: "Average trip distance looks reasonable"

  # PostgreSQL: UK Land Registry
  - dataset: pg@[public.land_registry_price_paid_uk]
    where: "transfer_date >= '2025-01-01'"
    checks:
      - not_null(price):
          desc: "Property price is mandatory"
      - min(price) >= 100:
          desc: "Minimum price should be realistic"
      - max(price) < 50000000:
          desc: "Maximum price within UK market range"
      - uniqueness(transaction):
          desc: "Each transaction must have a unique ID"
      - freshness(transfer_date) < 1d:
          desc: "Transfer date should be recent"
          on_fail: warn

# .github/workflows/data-quality.yml
name: Data Quality Checks
on:
  schedule:
    - cron: '0 */6 * * *'
  workflow_dispatch:
jobs:
  quality-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install dbqctl
        run: |
          curl -L https://github.com/DataBridgeTech/dbqctl/releases/latest/download/dbqctl-linux-amd64 -o dbqctl
          chmod +x dbqctl && sudo mv dbqctl /usr/local/bin/
      - name: Run Quality Checks
        env:
          PG_PASSWORD: ${{ secrets.PG_PASSWORD }}
        run: dbqctl check --checks ./checks.yaml