Context engineering: before and after.

# LAYER 1 of 5: joins and relationships, and nothing else.
#
# Starter topic plus one declared path: accounts -> subscriptions. The
# subscriptions table sits in the same schema the whole time; the raw model can
# see it but the Topic cannot reach it, so every MRR question dies before it
# starts. Declaring the relationship is what puts those fields in scope.
#
# Docs: https://docs.omni.co/modeling/relationships
base_view: cx_training__opportunities
label: Sales Pipeline (Workshop)

# The joins block is the PATH, not a list of tables. subscriptions hangs off
# accounts, not off the base view, so it nests under accounts. Declaring it flat
# here is the classic mistake: Omni cannot find a route from opportunities to
# subscriptions and drops the view from the Topic with a warning.
joins:
  cx_training__accounts:
    cx_training__subscriptions: {}
  cx_training__activities: {}

relationships:
  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__accounts
    join_type: always_left
    on_sql: ${cx_training__opportunities.account_id} =
      ${cx_training__accounts.account_id}
    relationship_type: many_to_one

  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__activities
    join_type: always_left
    on_sql: ${cx_training__opportunities.opportunity_id} =
      ${cx_training__activities.opportunity_id}
    relationship_type: one_to_many

  # The layer. Reach subscriptions THROUGH accounts, not from opportunities:
  # one subscription per account, so this is one_to_one from the account side.
  # relationship_type is not paperwork. It is what lets Omni sum MRR once per
  # subscription instead of once per opportunity the account happens to have.
  - join_from_view: cx_training__accounts
    join_to_view: cx_training__subscriptions
    join_type: always_left
    on_sql: ${cx_training__accounts.account_id} =
      ${cx_training__subscriptions.account_id}
    relationship_type: one_to_one

# LAYER 2 of 5: field metadata and governed measures, and nothing else.
#
# Same columns as the raw view. What changes is that every field now carries the
# five elements that earn their space: label (with the unit), description (what
# it excludes and what to use instead), synonyms (the words users actually say),
# values (every value of an enum, never a sample), and governed measures so
# "revenue" resolves to one agreed calculation instead of a guess.
#
# No ai_context here on purpose, that is Layer 3. This layer is the answer to
# "show me revenue": closed_revenue exists, it is labeled Bookings, and
# "revenue" is one of its synonyms.
#
# Docs: https://docs.omni.co/modeling/dimensions/parameters/index
#       https://docs.omni.co/modeling/measures/index
#       https://docs.omni.co/modeling/dimensions/parameters/all-values

# Reference this view as cx_training__opportunities
schema: CX_TRAINING
table_name: OPPORTUNITIES

dimensions:
  opportunity_id:
    sql: '"OPPORTUNITY_ID"'
    label: Opportunity ID
    primary_key: true

  account_id:
    sql: '"ACCOUNT_ID"'
    label: Account ID
    description: Foreign key to the account. Join to accounts for name and segment.

  amount:
    sql: '"AMOUNT"'
    label: Deal amount (USD)
    description: Deal value in USD. For booked revenue only, filter to Closed Won
      (see the closed_revenue measure).
    synonyms: [ deal value, deal size, contract value ]

  stage_name:
    sql: '"STAGE_NAME"'
    label: Sales stage
    description: Current pipeline stage. The first four are open, the last two are closed.
    all_values:
      [
        Prospecting,
        Qualification,
        Proposal,
        Negotiation,
        Closed Won,
        Closed Lost
      ]
    synonyms: [ stage, pipeline stage, deal stage ]

  close_date:
    sql: '"CLOSE_DATE"'
    label: Close date
    description: "Close date for closed deals, projected close date for open deals.
      Use this for fiscal-period math. The fiscal year starts in JULY: FY26 =
      2025-07-01 through 2026-06-30."
    synonyms: [ closed date, expected close ]

  created_date:
    sql: '"CREATED_DATE"'
    label: Created date
    description: Date the opportunity was created.
    synonyms: [ opened date, create date ]

  is_closed:
    sql: '"IS_CLOSED"'
    label: Is closed
    description: TRUE for BOTH Closed Won AND Closed Lost. Does NOT mean won. Pair
      with Is Won to tell wins from losses. Open pipeline is is_closed = FALSE.
    synonyms: [ closed ]

  is_won:
    sql: '"IS_WON"'
    label: Is won
    description: TRUE only for Closed Won. is_closed = TRUE and is_won = FALSE means
      Closed Lost.
    synonyms: [ won ]

  type:
    sql: '"TYPE"'
    label: Opportunity type
    description: Type of business for the deal.
    synonyms: [ deal type, business type ]

  competitor_c:
    sql: '"COMPETITOR_C"'
    label: Win channel / competitor
    description: "MISLEADING NAME. WON deals: the win channel (values like 'CH -
      Partner Referral'). LOST deals: the competitor who won. NULL for open
      deals. Channel wins = competitor_c LIKE 'CH%' AND stage_name = 'Closed
      Won'. To answer 'who do we lose to', filter to Closed Lost first."
    sample_values:
      [
        CH - Partner Referral,
        CH - Existing Relationship,
        CH - Direct Outreach,
        CH - RFP Response,
        Heighliner Systems,
        Mentat Analytics,
        No Decision
      ]
    synonyms: [ win channel, competitor, channel wins, who we lose to ]

measures:
  count:
    label: Number of opportunities
    aggregate_type: count
    synonyms: [ deal count, number of deals ]

  total_amount:
    sql: '"AMOUNT"'
    label: Total deal value
    description: Sum of amount across ALL opportunities regardless of stage. For
      booked revenue use closed_revenue.
    aggregate_type: sum
    format: usdcurrency
    synonyms: [ sum of amount ]

  closed_revenue:
    sql: CASE WHEN "IS_WON" = TRUE THEN "AMOUNT" ELSE 0 END
    label: Bookings (closed won revenue)
    description: Sum of amount for Closed Won deals only. This is bookings. Use
      this, not total_amount, for won or booked revenue.
    aggregate_type: sum
    format: usdcurrency
    synonyms: [ bookings, closed revenue, won revenue, revenue ]

  open_pipeline:
    sql: CASE WHEN "IS_CLOSED" = FALSE THEN "AMOUNT" ELSE 0 END
    label: Open pipeline
    description: Sum of amount for open deals (is_closed = FALSE). Excludes won and lost.
    aggregate_type: sum
    format: usdcurrency
    synonyms: [ pipeline, open deals ]

  won_count:
    sql: CASE WHEN "IS_WON" = TRUE THEN 1 ELSE 0 END
    label: Won deals
    description: Count of Closed Won deals.
    aggregate_type: sum
    synonyms: [ wins ]

  closed_count:
    sql: CASE WHEN "IS_CLOSED" = TRUE THEN 1 ELSE 0 END
    label: Closed deals
    description: Count of all closed deals, won and lost. This is the win rate denominator.
    aggregate_type: sum

  latest_close_date:
    sql: '"CLOSE_DATE"'
    label: Latest close date
    description: Most recent close_date in the group. Used to break ties in rankings.
    aggregate_type: max

  win_rate:
    sql: ${won_count} / NULLIF(${closed_count}, 0)
    label: Win rate
    description: Won deals divided by closed deals. Only closed deals are in the
      denominator; open deals are excluded. Do NOT divide by all deals.
    synonyms: [ win percentage, close rate ]

# LAYER 3 of 5, file 3 of 3: model-level ai_context.
#
# View ai_context says what fields mean. Topic ai_context says what the dataset
# is. Model ai_context says how to THINK: which source to trust first, and when
# an answer is allowed to call itself finished. It is the thin router, read
# before anything else, and it is the layer that turns a silent guess into a
# clarifying question.
#
# Deliberately absent: fiscal_month_offset. Prose alone has to argue the AI into
# a July fiscal year on every single question. The combined branch sets the
# offset once and stops arguing. That contrast is the point of the lab.
#
# Docs: https://docs.omni.co/modeling/models/ai-context

ai_context: |
  ORDER OF RESORT (which source to trust first):
  1. Metrics and rules written into the curated model context come first.
  2. Curated, documented dimensions next.
  3. Raw, undocumented columns last, and only when nothing above answers the question.
     Flag it when you do, and name the field and view you used.

  DEFINITION OF DONE for every answer:
  - Name the dataset (Topic) and the filters you applied.
  - State the time window (and remember the fiscal year starts in JULY).
  - When the question is ambiguous (no time window, no metric named), ASK a clarifying
    question instead of guessing. A guessed window is a wrong answer with good posture.

ai_chat_topics: [ cx_sales_pipeline, cx_product_usage ]

# Short cache so workshop data refreshes show up quickly. To force it after a
# reload: POST /api/v1/models/{id}/cache_reset/workshop_fresh (see RUNBOOK).
cache_policies:
  workshop_fresh:
    max_cache_age: 1 hour

default_cache_policy: workshop_fresh

# LAYER 3 of 5, file 1 of 3: ai_context, and nothing else.
#
# The raw view, untouched, plus one block at the bottom. No labels, no
# descriptions, no measures. Every field is still named exactly what the
# warehouse named it. The only thing that changed is that someone wrote down
# what they know.
#
# Note what this ai_context does NOT say: it never names closed_revenue or
# win_rate, because on this branch those measures do not exist. Context and
# model objects co-evolve. Compare with the combined branch, where the same
# rules can point at a governed measure instead of spelling out the filter.
#
# Docs: https://docs.omni.co/modeling/views/parameters/ai-context

# Reference this view as cx_training__opportunities
schema: CX_TRAINING
table_name: OPPORTUNITIES

dimensions:
  opportunity_id:
    sql: '"OPPORTUNITY_ID"'
    primary_key: true

  account_id:
    sql: '"ACCOUNT_ID"'

  amount:
    sql: '"AMOUNT"'

  stage_name:
    sql: '"STAGE_NAME"'

  close_date:
    sql: '"CLOSE_DATE"'

  created_date:
    sql: '"CREATED_DATE"'

  is_closed:
    sql: '"IS_CLOSED"'

  is_won:
    sql: '"IS_WON"'

  type:
    sql: '"TYPE"'

  competitor_c:
    sql: '"COMPETITOR_C"'

measures:
  count:
    aggregate_type: count

ai_context: |
  One row per sales opportunity (deal). opportunity_id is unique.
  amount is the deal value in USD. It is NOT revenue on its own: it counts open and
  lost deals too. Bookings (revenue) = SUM(amount) WHERE stage_name = 'Closed Won'.
  competitor_c is MISLEADINGLY named: for WON deals it holds the win CHANNEL
  (values like 'CH - Partner Referral'); for LOST deals it holds the competitor who won;
  for OPEN deals it is NULL. "Channel wins" means competitor_c LIKE 'CH%' AND
  stage_name = 'Closed Won'. "Who do we lose to" reads competitor_c on Closed Lost rows ONLY.
  is_closed is TRUE for BOTH Closed Won AND Closed Lost, so always pair it with is_won.
  Open pipeline is is_closed = FALSE.
  Win rate = won deals / CLOSED deals. Open deals are excluded from the denominator.
  stage_name is one of: Prospecting, Qualification, Proposal, Negotiation, Closed Won, Closed Lost.
  The fiscal year starts in JULY: FY26 = 2025-07-01 through 2026-06-30.

# LAYER 3 of 5, file 2 of 3: Topic ai_context.
#
# The starter Topic plus a description and an ai_context block. View ai_context
# covers what the fields mean; Topic ai_context covers what this DATASET is: its
# grain, its edges (what it can and cannot reach), and the business rules that
# span more than one field.
#
# Docs: https://docs.omni.co/modeling/topics/parameters/ai-context
base_view: cx_training__opportunities
label: Sales Pipeline (Workshop)
description: One row per opportunity. Pipeline, win rate, and bookings, with
  account and activity context.

joins:
  cx_training__accounts: {}
  cx_training__activities: {}

relationships:
  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__accounts
    join_type: always_left
    on_sql: ${cx_training__opportunities.account_id} =
      ${cx_training__accounts.account_id}
    relationship_type: many_to_one

  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__activities
    join_type: always_left
    on_sql: ${cx_training__opportunities.opportunity_id} =
      ${cx_training__activities.opportunity_id}
    relationship_type: one_to_many

ai_context: |
  Grain: one row per opportunity. opportunity_id is unique. A plain count of rows equals
  a count of distinct opportunity_id. This Topic exposes accounts and activities only;
  it does NOT reach subscriptions or product usage.

  CRITICAL BUSINESS RULES:
  1. competitor_c is MISLEADINGLY named. For WON deals it is the win channel
     (values like 'CH - Partner Referral'). For LOST deals it is the competitor who won.
     For open deals it is NULL. "Channel wins" = competitor_c LIKE 'CH%' AND
     stage_name = 'Closed Won'. "Who do we lose to" = competitor_c on Closed Lost rows only.
  2. is_closed is TRUE for BOTH Closed Won AND Closed Lost. Always pair with is_won.
  3. Bookings (revenue) = SUM(amount) WHERE stage_name = 'Closed Won'. Never report a
     bare SUM(amount) as revenue: it includes open pipeline and lost deals.
  4. Win rate = won deals / closed deals. Open deals are EXCLUDED from the denominator.
     Never divide wins by all opportunities.
  5. The fiscal year starts in JULY. FY26 = 2025-07-01 through 2026-06-30. Fiscal
     quarters: Q1 Jul-Sep, Q2 Oct-Dec, Q3 Jan-Mar, Q4 Apr-Jun. "Last fiscal quarter" is
     the prior 3-month block by close_date.
  6. For "top N accounts" rankings, break ties by the most recent close_date.
  7. activities is a child of opportunities. Count activities; do not read deal amounts
     off a joined activity row (the deal repeats once per activity).

# LAYER 4 of 5: sample_queries, and nothing else.
#
# The starter Topic plus worked examples. No labels, no descriptions, no
# ai_context anywhere: the fields are still named competitor_c and is_closed and
# nothing explains them. The model is not told the rule; it is SHOWN the answer
# to a question it actually gets, and it matches the next question against it.
#
# Each entry pairs a prompt (what a human types) with the canonical query (what
# a careful analyst would run). The prompt is half the example: it is what the
# match happens against.
#
# The per-query ai_context parameter is deliberately omitted here. It exists,
# and the combined branch uses it, but on this branch it would smuggle Layer 3
# in through the back door and we would not know which piece did the work.
#
# Note what is missing and cannot be added: a bookings example. Summing amount
# needs a measure, and measures are Layer 2. Sample queries teach the model to
# reuse your pattern, not to invent your metric.
#
# Docs: https://docs.omni.co/modeling/topics/parameters/sample-queries
base_view: cx_training__opportunities
label: Sales Pipeline (Workshop)

joins:
  cx_training__accounts: {}
  cx_training__activities: {}

relationships:
  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__accounts
    join_type: always_left
    on_sql: ${cx_training__opportunities.account_id} =
      ${cx_training__accounts.account_id}
    relationship_type: many_to_one

  - join_from_view: cx_training__opportunities
    join_to_view: cx_training__activities
    join_type: always_left
    on_sql: ${cx_training__opportunities.opportunity_id} =
      ${cx_training__activities.opportunity_id}
    relationship_type: one_to_many

sample_queries:
  Channel wins:
    query:
      fields: [ cx_training__opportunities.count ]
      base_view: cx_training__opportunities
      filters:
        cx_training__opportunities.competitor_c:
          starts_with: CH
        cx_training__opportunities.stage_name:
          is: Closed Won
      topic: cx_sales_pipeline
    prompt: how many channel wins did we have?

  Who we lose to:
    query:
      fields:
        [
          cx_training__opportunities.competitor_c,
          cx_training__opportunities.count
        ]
      base_view: cx_training__opportunities
      filters:
        cx_training__opportunities.stage_name:
          is: Closed Lost
      sorts:
        - field: cx_training__opportunities.count
          desc: true
      topic: cx_sales_pipeline
    prompt: who do we lose to most?

  Open opportunities:
    query:
      fields: [ cx_training__opportunities.count ]
      base_view: cx_training__opportunities
      filters:
        cx_training__opportunities.is_closed:
          is: false
      topic: cx_sales_pipeline
    prompt: how many open opportunities do we have right now?

# LAYER 5 of 5: one line of model config, and nothing else.
#
# This layer exists because of a result we did not expect and kept anyway.
#
# The ai_context layer (Layer 3) says, in plain English, "The fiscal year starts
# in JULY. FY26 = 2025-07-01 through 2026-06-30." The Agent reads it, believes
# it, and even reaches for the right literal: it writes
# OMNI_DATETIME_LITERAL('this fiscal year') into the SQL. And it STILL returns
# the calendar-year answer, because the literal itself resolves against the
# model's fiscal calendar, and that calendar is a setting, not a sentence.
#
# fiscal_month_offset: 6 means the fiscal year starts 6 months after January:
# July. One line, and every relative date literal in every question, on every
# surface, resolves correctly forever.
#
# The lesson is not "prose is useless". It is that context engineering has more
# than one instrument, and prose is the wrong instrument for a calendar. Some
# things you tell the model. Some things you configure, and then stop arguing.
#
# Docs: https://docs.omni.co/modeling/models/fiscal-month-offset

fiscal_month_offset: 6

ai_chat_topics: [ cx_sales_pipeline, cx_product_usage ]

# Short cache so workshop data refreshes show up quickly. To force it after a
# reload: POST /api/v1/models/{id}/cache_reset/workshop_fresh (see RUNBOOK).
cache_policies:
  workshop_fresh:
    max_cache_age: 1 hour

default_cache_policy: workshop_fresh