Model configuration

Download JSON Schema

Example

apiVersion: leapview.dev/v1
kind: Model
metadata:
  id: model:cash_forecast
  name: cash_forecast
  displayName: 13-Week Cash Forecast Fact
  description: Weekly cash-forecast fact at scenario and forecast-week grain.
spec:
  fields:
    - {name: cash_row_id, datatype: String, label: Cash row ID}
    - {name: week_index, datatype: Integer, label: Week}
    - {name: scenario, datatype: String, label: Scenario key}
    - {name: collections, datatype: Decimal, label: Customer collections}
    - {name: supplier_payments, datatype: Decimal, label: Supplier payments}
    - {name: payroll, datatype: Decimal, label: Payroll}
    - {name: operating_expenses, datatype: Decimal, label: Operating expenses}
    - {name: taxes, datatype: Decimal, label: Taxes}
    - {name: net_cash_flow, datatype: Decimal, label: Net cash flow}
    - {name: base_collections, datatype: Decimal, label: Base collections}
    - {name: base_supplier_payments, datatype: Decimal, label: Base supplier payments}
    - {name: base_net_cash_flow, datatype: Decimal, label: Base net cash flow}
    - {name: closing_cash, datatype: Decimal, label: Closing cash}
    - {name: scenario_ending_cash, datatype: Decimal, label: Scenario ending cash}
    - {name: scenario_headroom, datatype: Decimal, label: Scenario cash headroom}
    - {name: current_cash, datatype: Decimal, label: Current cash}
    - {name: base_ending_cash, datatype: Decimal, label: Base ending cash}
    - {name: base_headroom, datatype: Decimal, label: Base cash headroom}
    - {name: overdue_receivables, datatype: Decimal, label: Overdue receivables}
    - {name: dso_days, datatype: Decimal, label: Days sales outstanding}
  definition:
    type: sql
    sql: |
      WITH revenue_scale AS (
        SELECT SUM(net_sales) / 104.0 AS weekly_sales
        FROM model.financial_performance
      ),
      flows AS (
        SELECT
          w.week_index,
          s.scenario,
          r.weekly_sales * s.collection_factor * CASE WHEN w.week_index = 3 OR w.week_index = 8 THEN 1.12 ELSE 1.0 END AS collections,
          -(r.weekly_sales * 0.66 * s.payment_factor * CASE WHEN w.week_index = 4 OR w.week_index = 9 THEN 1.08 ELSE 1.0 END) AS supplier_payments,
          -(r.weekly_sales * 0.10) AS payroll,
          -(r.weekly_sales * 0.12) AS operating_expenses,
          -(r.weekly_sales * CASE WHEN w.week_index = 5 OR w.week_index = 12 THEN 0.06 ELSE 0.015 END) AS taxes,
          r.weekly_sales * 10.0 AS opening_cash
        FROM model.cash_weeks w
        INNER JOIN model.cash_scenarios s ON true
        INNER JOIN revenue_scale r ON true
      ),
      projected AS (
        SELECT
          week_index,
          scenario,
          collections,
          supplier_payments,
          payroll,
          operating_expenses,
          taxes,
          collections + supplier_payments + payroll + operating_expenses + taxes AS net_cash_flow,
          opening_cash + SUM(collections + supplier_payments + payroll + operating_expenses + taxes)
            OVER (PARTITION BY scenario ORDER BY week_index) AS closing_cash,
          opening_cash
        FROM flows
      )
      SELECT
        scenario || '-' || printf('%02d', week_index) AS cash_row_id,
        week_index,
        scenario,
        CAST(round(collections, 2) AS DECIMAL(38,2)) AS collections,
        CAST(round(supplier_payments, 2) AS DECIMAL(38,2)) AS supplier_payments,
        CAST(round(payroll, 2) AS DECIMAL(38,2)) AS payroll,
        CAST(round(operating_expenses, 2) AS DECIMAL(38,2)) AS operating_expenses,
        CAST(round(taxes, 2) AS DECIMAL(38,2)) AS taxes,
        CAST(round(net_cash_flow, 2) AS DECIMAL(38,2)) AS net_cash_flow,
        CASE WHEN scenario = 'Base' THEN CAST(round(collections, 2) AS DECIMAL(38,2)) ELSE NULL END AS base_collections,
        CASE WHEN scenario = 'Base' THEN CAST(round(supplier_payments, 2) AS DECIMAL(38,2)) ELSE NULL END AS base_supplier_payments,
        CASE WHEN scenario = 'Base' THEN CAST(round(net_cash_flow, 2) AS DECIMAL(38,2)) ELSE NULL END AS base_net_cash_flow,
        CAST(round(closing_cash, 2) AS DECIMAL(38,2)) AS closing_cash,
        CASE WHEN week_index = 12 THEN CAST(round(closing_cash, 2) AS DECIMAL(38,2)) ELSE NULL END AS scenario_ending_cash,
        CASE WHEN week_index = 12 THEN CAST(round(closing_cash - opening_cash * 0.45, 2) AS DECIMAL(38,2)) ELSE NULL END AS scenario_headroom,
        CASE WHEN scenario = 'Base' AND week_index = 0 THEN CAST(round(opening_cash, 2) AS DECIMAL(38,2)) ELSE NULL END AS current_cash,
        CASE WHEN scenario = 'Base' AND week_index = 12 THEN CAST(round(closing_cash, 2) AS DECIMAL(38,2)) ELSE NULL END AS base_ending_cash,
        CASE WHEN scenario = 'Base' AND week_index = 12 THEN CAST(round(closing_cash - opening_cash * 0.45, 2) AS DECIMAL(38,2)) ELSE NULL END AS base_headroom,
        CASE WHEN scenario = 'Base' AND week_index = 0 THEN CAST(round(opening_cash * 0.31, 2) AS DECIMAL(38,2)) ELSE NULL END AS overdue_receivables,
        CASE WHEN scenario = 'Base' AND week_index = 0 THEN CAST(47 AS DECIMAL(38,2)) ELSE NULL END AS dso_days
      FROM projected
  entities:
    - name: cash_forecast_row
      type: primary
      fields: [cash_row_id]
    - name: cash_week
      type: foreign
      fields: [week_index]
    - name: cash_scenario
      type: foreign
      fields: [scenario]
  grain:
    entity: cash_forecast_row

Fields

Nested definitions

AIContext

AcceptedValuesDatasetCheck

AuthoritativeDefinition

AzureBlobConnection

BlobPathSourceLocation

CSVPathSourceLocation

CSVReaderOptions

Connection

ConnectionSpec

ConnectionSpecBase

ContractMetadata

ContractResourceMetadata

DatasetCheck

DatasetCheckAcceptedValuesVariant

DatasetCheckFreshnessVariant

DatasetCheckNonNullVariant

DatasetCheckRelationshipVariant

DatasetCheckRowCountVariant

DatasetCheckUniqueVariant

DatasetField

DatasetSchema

DeltaPathSourceLocation

DeltaReaderOptions

DirectModelDefinition

DuckLakeConnection

ExcelPathSourceLocation

ExcelReaderOptions

FieldDeprecation

FreshnessDatasetCheck

FreshnessDuration

GCSConnection

HTTPConnection

IcebergPathSourceLocation

IcebergReaderOptions

JSONPathSourceLocation

JSONReaderOptions

LancePathSourceLocation

ManagedConnection

Model

ModelDefinition

ModelDefinitionBase

ModelEntity

ModelGrain

ModelSpec

MySQLConnection

NonNullDatasetCheck

ParquetPathSourceLocation

ParquetReaderOptions

PathSourceLocation

PathSourceLocationBase

PostgresConnection

PublicAccess

QuackConnection

R2Connection

ReaderDefaults

RelationSourceLocation

RelationshipDatasetCheck

ResourceMetadata

ResourceProvenance

RowCountDatasetCheck

S3Connection

SQLModelDefinition

SQLiteConnection

Source

SourceLocation

SourceLocationPathVariant

SourceLocationRelationVariant

SourceSpec

TextPathSourceLocation

UniqueDatasetCheck

VortexPathSourceLocation