dataset: datasource/db/schema/portfolio_holdings

variables:
  FRESHNESS_HOURS:
    default: 24

checks:
  - schema:
      allow_extra_columns: false
      allow_other_column_order: false
  - row_count:
      threshold:
        must_be_greater_than: 0
  - freshness:
      column: as_of_date
      threshold:
        unit: hour
        must_be_less_than_or_equal: ${var.FRESHNESS_HOURS}
  - failed_rows:
      name: "as_of_date must not be in the future"
      qualifier: as_of_date_not_future
      expression: as_of_date > CURRENT_DATE
  - failed_rows:
      name: "market_value must be non-negative"
      qualifier: market_value_non_negative
      expression: market_value < 0
  - failed_rows:
      name: "quantity must be non-negative"
      qualifier: quantity_non_negative
      expression: quantity < 0
  - failed_rows:
      name: "No duplicate holdings per portfolio, asset, and date"
      qualifier: dup_holding
      query: |
        SELECT portfolio_id, asset_id, as_of_date
        FROM datasource.db.schema.portfolio_holdings
        GROUP BY portfolio_id, asset_id, as_of_date
        HAVING COUNT(*) > 1
      threshold:
        must_be: 0
  - failed_rows:
      name: "Holdings with zero quantity must have zero market value"
      qualifier: zero_qty_zero_mv
      expression: quantity = 0 AND market_value <> 0

columns:
  - name: portfolio_id
    data_type: string
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "portfolio_id length guardrail"
          valid_min_length: 1
          valid_max_length: 64
  - name: asset_id
    data_type: string
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "asset_id length guardrail"
          valid_min_length: 1
          valid_max_length: 64
  - name: asset_type
    data_type: string
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "Allowed asset types"
          valid_values:
            - EQUITY
            - FIXED_INCOME
            - DERIVATIVE
            - FX
            - COMMODITY
            - CASH
            - FUND
            - ALTERNATIVE
  - name: quantity
    data_type: decimal
    checks:
      - missing:
          name: No missing values
      - invalid:
          name: "Quantity must be zero or positive"
          valid_min: 0
  - name: market_value
    data_type: decimal
    checks:
      - missing:
          name: No missing values
  - name: as_of_date
    data_type: date
    checks:
      - missing:
          name: No missing values