dataset: datasource/db/schema/account_balances

variables:
  FRESHNESS_HOURS:
    default: 24
  RECONCILIATION_TOLERANCE:
    default: 0.01

checks:
  - schema:
      allow_extra_columns: false
      allow_other_column_order: false
  - row_count:
      threshold:
        must_be_greater_than: 0
  - freshness:
      column: balance_date
      threshold:
        unit: hour
        must_be_less_than_or_equal: ${var.FRESHNESS_HOURS}
  - failed_rows:
      name: "balance_date must not be in the future"
      qualifier: balance_date_not_future
      expression: balance_date > CURRENT_DATE
  - failed_rows:
      name: "closing_balance must not be negative for standard accounts"
      qualifier: closing_non_negative
      expression: closing_balance < 0
  - failed_rows:
      name: "Duplicate balance per account per date"
      qualifier: dup_account_date
      query: |
        SELECT account_id, balance_date
        FROM datasource.db.schema.account_balances
        GROUP BY account_id, balance_date
        HAVING COUNT(*) > 1
      threshold:
        must_be: 0
  - failed_rows:
      name: "Opening balance must equal previous day closing balance"
      qualifier: opening_eq_prev_closing
      query: |
        SELECT curr.account_id, curr.balance_date
        FROM datasource.db.schema.account_balances curr
        JOIN datasource.db.schema.account_balances prev
          ON curr.account_id = prev.account_id
          AND curr.balance_date = DATEADD('day', 1, prev.balance_date)
        WHERE ABS(curr.opening_balance - prev.closing_balance) > ${var.RECONCILIATION_TOLERANCE}
      threshold:
        must_be: 0

columns:
  - name: account_id
    data_type: string
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "account_id length guardrail"
          valid_min_length: 1
          valid_max_length: 64
  - name: balance_date
    data_type: date
    checks:
      - missing:
          name: No missing values
  - name: opening_balance
    data_type: decimal
    checks:
      - missing:
          name: No missing values
  - name: closing_balance
    data_type: decimal
    checks:
      - missing:
          name: No missing values
  - name: currency
    data_type: string
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "Currency must be ISO-4217 (3 uppercase letters)"
          valid_format:
            name: ISO-4217 code
            regex: "^[A-Z]{3}$"