# Hand-authored curated contract for a transform/python product (ADR 0015/0019).
# The python job is an independent producer, so it owns a contract for the product it
# writes — committed here rather than generated, since dbt's generator does not run python.
schema_version: 0.1.0
kind: product
id: finance.monthly_revrec_orders
name: monthly_revrec_orders
version: 1.0.0
layer: curated
owner:
  type: team
  name: data-platform
domain:
  name: finance
purpose: Curated-layer product monthly_revrec_orders (one row per order with recognised /
  adjusted revenue), produced by the transform layer (the Python revenue-recognition job
  transform/python/jobs/monthly_cloud_billing_revrec.py). Output parity with the legacy
  BigQuery/dbt Python model is proven by the monthly_cloud_billing_revrec output harness.
usage:
  intended:
  - Cloud order revenue-recognition analysis
  - Downstream modeling that accepts the documented monthly_cloud_billing_revrec parity scope
  - Agent-mediated querying with upstream revenue-source provenance
  prohibited:
  - Use as a source-faithful raw record (see the raw-snapshot contract).
  - Treat the upstream Revenue__c freeze as the go-forward revenue business process.
assets:
- id: monthly_revrec_orders
  kind: table
  ref: transform_python_dwh.monthly_revrec_orders
  materialization: table
  produced_by:
    kind: python
    ref: monthly_cloud_billing_revrec
schema:
  kind: relational
  columns:
  - name: tenant_id
    type: VARCHAR
    nullable: true
  - name: bill_month
    type: VARCHAR
    nullable: true
  - name: is_new_order
    type: BOOLEAN
    nullable: true
  - name: is_new_salesforce_order
    type: BOOLEAN
    nullable: true
  - name: account_surrogate_key
    type: VARCHAR
    nullable: true
  - name: account_id
    type: VARCHAR
    nullable: true
  - name: account_name
    type: VARCHAR
    nullable: true
  - name: account_region
    type: VARCHAR
    nullable: true
  - name: payment_type
    type: VARCHAR
    nullable: true
  - name: stripe_tax_rate
    type: VARCHAR
    nullable: true
  - name: collections_from
    type: VARCHAR
    nullable: true
  - name: order_number
    type: VARCHAR
    nullable: true
  - name: order_start_date
    type: DATE
    nullable: true
  - name: order_end_date
    type: DATE
    nullable: true
  - name: order_billing_start_date
    type: DATE
    nullable: true
  - name: order_billing_end_date
    type: DATE
    nullable: true
  - name: order_deferred_revenue_usd
    type: DOUBLE
    nullable: true
  - name: order_termination_date
    type: VARCHAR
    nullable: true
  - name: order_total_fees_usd
    type: DOUBLE
    nullable: true
  - name: order_accumulated_revenue_before_adjust
    type: DOUBLE
    nullable: true
  - name: order_accumulated_credit_amount
    type: DOUBLE
    nullable: true
  - name: revenue_amount
    type: DOUBLE
    nullable: true
  - name: credit_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_shortfall_amount
    type: DOUBLE
    nullable: true
  - name: is_order_closing
    type: BOOLEAN
    nullable: true
  - name: billing_allocation_amount
    type: DOUBLE
    nullable: true
  - name: end_of_contract_shortfall_amount
    type: DOUBLE
    nullable: true
  - name: actual_revenue_recognition_date
    type: VARCHAR
    nullable: true
  - name: serverless_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_serverless_amount
    type: DOUBLE
    nullable: true
  - name: dedicated_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_dedicated_amount
    type: DOUBLE
    nullable: true
  - name: support_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_support_amount
    type: DOUBLE
    nullable: true
  - name: starter_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_starter_amount
    type: DOUBLE
    nullable: true
  - name: essential_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_essential_amount
    type: DOUBLE
    nullable: true
  - name: premium_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_premium_amount
    type: DOUBLE
    nullable: true
  - name: lake_revenue_amount
    type: DOUBLE
    nullable: true
  - name: adjusted_lake_amount
    type: DOUBLE
    nullable: true
freshness:
  max_age: P1D
tests:
- id: monthly_revrec_orders_not_empty
  type: sql
  severity: blocking
  description: The orders output must contain rows for the most recent completed Python revrec
    run.
  sql: select row_count from (select count(*) as row_count from transform_python_dwh.monthly_revrec_orders) where row_count = 0
- id: monthly_revrec_orders_key_unique
  type: sql
  severity: blocking
  description: Orders remain unique at the stable row identity of tenant, bill month, and
    order number.
  sql: select tenant_id, bill_month, order_number, count(*) from transform_python_dwh.monthly_revrec_orders group by 1, 2, 3 having count(*) > 1
- id: monthly_revrec_orders_order_number_unique
  type: sql
  severity: blocking
  description: Generated tenant-based on-demand order numbers make order_number unique in each
    completed output run.
  sql: select order_number, count(*) from transform_python_dwh.monthly_revrec_orders group by 1 having count(*) > 1
- id: monthly_revrec_orders_required_fields_present
  type: sql
  severity: blocking
  description: Fields used for revenue conservation and downstream allocation semantics must
    be present on every order row.
  sql: >
    select *
    from transform_python_dwh.monthly_revrec_orders
    where tenant_id is null
       or bill_month is null
       or order_number is null
       or is_new_order is null
       or is_new_salesforce_order is null
       or account_surrogate_key is null
       or account_id is null
       or account_name is null
       or account_region is null
       or order_start_date is null
       or order_end_date is null
       or order_billing_start_date is null
       or order_billing_end_date is null
       or order_deferred_revenue_usd is null
       or order_total_fees_usd is null
       or order_accumulated_revenue_before_adjust is null
       or order_accumulated_credit_amount is null
       or revenue_amount is null
       or credit_amount is null
       or adjusted_revenue_amount is null
       or adjusted_shortfall_amount is null
       or is_order_closing is null
       or billing_allocation_amount is null
       or end_of_contract_shortfall_amount is null
       or serverless_revenue_amount is null
       or adjusted_serverless_amount is null
       or dedicated_revenue_amount is null
       or adjusted_dedicated_amount is null
       or support_revenue_amount is null
       or adjusted_support_amount is null
       or starter_revenue_amount is null
       or adjusted_starter_amount is null
       or essential_revenue_amount is null
       or adjusted_essential_amount is null
       or premium_revenue_amount is null
       or adjusted_premium_amount is null
       or lake_revenue_amount is null
       or adjusted_lake_amount is null
    limit 1
- id: monthly_revrec_orders_adjusted_components_sum
  type: sql
  severity: blocking
  description: Adjusted revenue must equal the adjusted product-category amounts plus adjusted
    shortfall at order grain.
  sql: >
    select
      tenant_id,
      bill_month,
      order_number,
      adjusted_revenue_amount,
      adjusted_serverless_amount,
      adjusted_dedicated_amount,
      adjusted_support_amount,
      adjusted_starter_amount,
      adjusted_essential_amount,
      adjusted_premium_amount,
      adjusted_lake_amount,
      adjusted_shortfall_amount
    from transform_python_dwh.monthly_revrec_orders
    where abs(
      adjusted_revenue_amount
      - (
          adjusted_serverless_amount
        + adjusted_dedicated_amount
        + adjusted_support_amount
        + adjusted_starter_amount
        + adjusted_essential_amount
        + adjusted_premium_amount
        + adjusted_lake_amount
        + adjusted_shortfall_amount
      )
    ) > 0.01
    limit 1
- id: monthly_revrec_orders_adjusted_revenue_matches_revenue_presence
  type: sql
  severity: blocking
  description: Adjusted revenue and raw revenue must be present together and non-zero together,
    matching the legacy monthly_cloud_billing_revrec post-run dbt test.
  sql: >
    select
      tenant_id,
      bill_month,
      order_number,
      adjusted_revenue_amount,
      revenue_amount
    from transform_python_dwh.monthly_revrec_orders
    where (adjusted_revenue_amount is null and revenue_amount is not null)
       or (adjusted_revenue_amount is not null and revenue_amount is null)
       or (adjusted_revenue_amount = 0 and revenue_amount > 0)
       or (adjusted_revenue_amount > 0 and revenue_amount = 0)
    limit 1
- id: monthly_revrec_orders_new_order_components_sum
  type: sql
  severity: blocking
  description: Generated on-demand order revenue must equal the generated product-category
    revenue components, the normalized-output equivalent of the legacy new_order_amount check.
  sql: >
    select
      tenant_id,
      bill_month,
      order_number,
      revenue_amount,
      serverless_revenue_amount,
      dedicated_revenue_amount,
      support_revenue_amount,
      starter_revenue_amount,
      essential_revenue_amount,
      premium_revenue_amount,
      lake_revenue_amount
    from transform_python_dwh.monthly_revrec_orders
    where is_new_order = true
      and abs(
        revenue_amount
        - (
            coalesce(serverless_revenue_amount, 0)
          + coalesce(dedicated_revenue_amount, 0)
          + coalesce(support_revenue_amount, 0)
          + coalesce(starter_revenue_amount, 0)
          + coalesce(essential_revenue_amount, 0)
          + coalesce(premium_revenue_amount, 0)
          + coalesce(lake_revenue_amount, 0)
        )
      ) > 0.01
    limit 1
- id: monthly_revrec_orders_on_demand_key_shape
  type: sql
  severity: blocking
  description: Generated on-demand orders must use the bill-month and tenant-id key shape.
  sql: >
    select tenant_id, bill_month, account_id, order_number
    from transform_python_dwh.monthly_revrec_orders
    where is_new_order = true
      and order_number <> concat(replace(bill_month, '-', '_'), '__ON_DEMAND__', replace(tenant_id, '-', '_'))
    limit 1
- id: monthly_revrec_orders_on_demand_tenant_unique
  type: sql
  severity: blocking
  description: The job must not duplicate generated on-demand rows within the same tenant and
    bill month.
  sql: >
    select tenant_id, bill_month, count(*)
    from transform_python_dwh.monthly_revrec_orders
    where is_new_order = true
    group by 1, 2
    having count(*) > 1
declared_lineage:
  upstreams:
  - kind: data_product
    ref: finance.salesforce_org_monthly_revrec
  - kind: data_product
    ref: finance.salesforce_order_daily_view
governance:
  classifications:
  - financial
  - customer_data
  access:
    default: restricted
  audit:
    required: true
    events:
    - export
    - agent_action
agent_permissions:
  default: deny
compatibility:
  change_policy: semver
  deprecation_window: P90D
  migration_notes: >
    Version 1.0.0 intentionally changes generated on-demand order_number values from
    bill-month plus account-id to bill-month plus tenant-id so order_number is unique in a
    completed monthly_revrec_orders run. Consumers that keyed generated on-demand rows by
    the previous account-based shape should migrate to the stable tenant_id, bill_month,
    order_number grain or regenerate downstream references after accepting the new key
    shape. Finance data-platform owner approval and consumer impact review are required
    before promotion because this is a major contract change.
semantics:
  terms:
  - name: salesforce_revrec_order_revenue_input
    description: This product consumes finance.salesforce_order_daily_view, whose accumulated
      revenue fields come from finance.salesforce_revrec.
  - name: monthly_cloud_billing_revrec_output_parity_scope
    description: >
      Output behavior is derived from the legacy BigQuery/dbt monthly_cloud_billing_revrec
      Python model for the same billing_month, order_date, and revenue mode, with an intentional
      generated on-demand order_number divergence: datalake uses tenant id in generated order
      numbers so downstream consumers can treat order_number as unique in a completed output run.
  - name: generated_on_demand_order_scope
    description: Generated on-demand order numbers use the bill-month plus tenant-id shape so
      order_number is unique in each completed monthly_revrec_orders output run.
  entities:
  - name: revenue_recognition_order
    description: One order-level revenue-recognition output row produced by the Python transform.
  metrics:
  - name: revenue_amount
    expression: sum(revenue_amount)
    description: Recognized revenue amount in the product's current parity revenue context.
    grain: revenue_recognition_order
  - name: adjusted_revenue_amount
    expression: sum(adjusted_revenue_amount)
    description: Adjusted recognized revenue amount in the current parity revenue context.
    grain: revenue_recognition_order