Templates

Portfolio Holdings

Data Contract Template

Ensure Portfolio Holdings data is fresh, complete, and reliable before it’s used for valuation, risk aggregation, and regulatory reporting.

Data contract description

This data contract enforces schema stability, a 24-hour freshness SLA based on the as_of_date, and required portfolio, asset, and valuation fields to ensure accurate position reporting. It prevents future-dated records, blocks duplicate holdings per portfolio, asset, and date, enforces non-negative quantities and market values, and ensures logical consistency between quantity and market value. Together, these checks protect portfolio valuation accuracy, reduce reconciliation issues, and ensure downstream risk, performance, and regulatory calculations are based on consistent and trustworthy position data.

Dataset

Defines the dataset this contract applies to, including its source and scope. This is the single point of reference for what data is being validated and protected.

Dataset checks

Validations that apply to the dataset as a whole, such as schema stability, freshness SLAs, and overall structural guarantees. This contract includes a schema check to detect unexpected structural changes and a row count check to prevent empty position loads. It defines a freshness check to verify holdings are no more than 24 hours old based on as_of_date, blocks future-dated records, prevents duplicate holdings per portfolio and asset, enforces non-negative quantities and market values, and ensures zero-quantity positions cannot carry non-zero market value—protecting valuation and risk calculations from structural and logical errors.

Column checks

Column-level rules that define required fields, data types, allowed formats, and constraints for individual attributes. This contract enforces that portfolio_id, asset_id, asset_type, quantity, market_value, and as_of_date are not missing, applies length guardrails to identifiers, restricts asset_type to approved categories, and ensures quantity is zero or positive. These controls protect position-level integrity and ensure reliable aggregation across portfolios, asset classes, and valuation reports.

portfolio_holdings_data_contract.yaml

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

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

Data contract description

Dataset

Defines the dataset this contract applies to, including its source and scope. This is the single point of reference for what data is being validated and protected.

Dataset checks

Column checks

portfolio_holdings_data_contract.yaml

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

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

Data contract description

portfolio_holdings_data_contract.yaml

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

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

How to Enforce Data Contracts with Soda

Embed data quality through data contracts at any point in your pipeline.

# pip install soda-{data source} for other data sources

pip install soda-postgres

# verify the contract locally against a data source

soda contract verify -c contract.yml -ds ds_config.yml

# publish and schedule the contract with Soda Cloud

soda contract publish -c contract.yml -sc sc_config.yml

Check out the CLI documentation to learn more.

How to Automatically Create Data Contracts.
In one Click.

Automatically write and publish data contracts using Soda's AI-powered data contract copilot.

Make data contracts work in production

Business knows what good data looks like. Engineering knows how to deliver it at scale. Soda unites both, turning governance expectations into executable contracts.

Book a demo