Entity YAML

Entity YAML files define the features, metrics, join rules, and examples for a single business entity.

First time here? If you haven't read the other file-type docs yet, start with knowledge-md.md to understand the scoping model, then task-instructions-md.md for SQL guidance patterns — both are shorter and establish context this file builds on. Come back here when you're ready to model entities.

---

Quick Reference — Feature Types

Type	What it does	When to use
field	Pulls a column directly from a source table	Any direct attribute — name, status, amount
metric	Aggregates from a related entity (feature chaining)	Per-entity totals — total revenue, order count
first_last	Gets the first or last value ordered by another field	Most recent plan, first order date
formula	Derives a value from other features on the same entity	Ratios, tiers, days-since calculations

Each type is documented in detail below.

---

Top-Level Structure

name: {entity_name}
description: {one or two sentence description}

key_source: {warehouse_table}    # primary table — defines entity identity
keys:                            # fields that uniquely identify a row
  - {field_name}

features:                        # attributes — see Feature Types below
  - ...

metrics:                         # aggregation logic — see Entity Metrics below
  - ...

related_sources:                 # secondary warehouse tables joined to the key_source
  {table_name}:
    ...

examples:                        # entity-level query examples
  - ...

Field	Required	Description
name	Yes	Entity identifier — referenced in queries as FROM <name>
description	Yes	Human-readable summary for agent context
key_source	Yes	The primary warehouse table (schema.db.table format)
keys	Yes	List of fields that form the primary key
features	Yes	List of feature definitions
metrics	No	List of entity metric definitions
related_sources	No	Secondary tables for enrichment
examples	No	Entity-scoped query examples

---

Name and Description

When a user asks a question, the agent decides which entities are relevant based on their name and description. These are the two fields the agent reads first — before looking at features or metrics.

name is the entity identifier. It appears in queries (FROM order), in relationships, and in metric feature references. Keep it short, lowercase, and unambiguous.

description is what the agent uses to decide whether this entity is relevant to the question. A vague description means the agent may miss the entity entirely or pick the wrong one. A good description answers:

• What does this entity represent?

• What questions or topics is it used for?

# TOO VAGUE — agent has no signal for when to use this
name: order
description: Order data

# GOOD — agent knows what this is and when it applies
name: order
description: >
  A completed purchase transaction. Use this entity for questions about
  revenue, order volume, purchase dates, and product-level sales.

# TOO VAGUE
name: session
description: Session information

# GOOD
name: session
description: >
  A single user visit to the product. Use this entity for questions about
  engagement, retention, feature usage, and user behavior.

What not to put in description

The description should only explain what this entity represents and what questions it answers. Two things that don't belong here:

• Filtering rules or SQL instructions — "always exclude is_inactive = true records when analyzing performance" belongs in task instructions, not here.

• References to other entities — "use the player entity for career aggregates" is navigation guidance, not a description of what this entity is. The agent selects entities based on question relevance, not cross-entity pointers in descriptions.

If your project has multiple entities that could answer similar questions (e.g., order and order_item), the description is how the agent chooses between them. Be explicit about what the entity represents and what questions it answers.

---

The source Field

Every feature (except formula) has a source field that tells the agent where to get the data. source always means "where this data comes from" — it can be a warehouse table or an entity.

Feature type	source can be	Format
field	A warehouse table/view or an entity	schema.db.table or entity name
first_last	A warehouse table/view or an entity	schema.db.table or entity name
metric	An entity only	entity name (e.g., order)
formula	—	No source — references features on this entity

metric features require an entity as source because they need aggregation logic (metrics:) that only exists on entities, not on raw tables.

When source is an entity (for field and first_last features), the join between the two entities must be defined in entities_relationships.yml. Specify which join to use with join_name if more than one join exists for that entity pair.

---

Feature Types

field — Direct Column

Pulls a column from a source without aggregation. A source can be one of two things:

1. A warehouse table or view where each row on this entity maps to at most one source row. For example: enriching the customer entity with customer_last_signup_date from a warehouse table that is not the customer's key_source.

2. A Lynk entity where each row on this entity maps to at most one source row. For example: enriching the order entity with the customer name from the customer entity (each order has exactly one customer).

Declare the related_source or the entity relationship before defining the field feature. If the source isn't declared first, the feature won't resolve.

# Defined on the customer entity — pulls directly from the key_source table
- type: field
  name: full_name
  data_type: string
  source: db_prod.public.customer
  description: Customer's full name
  field: full_name

# Defined on the order entity — pulls the customer name from the related customer entity
- type: field
  name: customer_name
  data_type: string
  source: customer              # entity name, not a table path
  description: Name of the customer who placed this order
  field: full_name              # same column as example 1 — only the source path differs
  join_name: null               # null = use the default join from entities_relationships.yml

Field	Description
name	Feature name — what users see and query
data_type	string, number, boolean, datetime
source	Where the field comes from. Either a warehouse table (schema.db.table) or an entity name.
field	The column name in the source table (may differ from name)
join_name	Which join to use. For a related_source table, the join defined on that source; for an entity source, a named join on the relationship. Omit (or set null) to use the default.
filters	Pre-filters applied to the source before retrieving the field. Omit if no filters.

---

metric — Aggregated Value from a Related Entity

Pulls an aggregated value from a metric defined on a related entity. This is feature chaining.

- type: metric
  name: total_revenue
  description: Total revenue from all orders placed by this customer
  data_type: number
  source: order
  metric: sum_amount

Field	Description
source	The related entity whose metric to use. Must be an entity name (e.g., order), not a raw table name — metric: is only defined on entities.
metric	The metric name from the related entity's metrics: section
join_name	Which relationship join to use. Omit to use the default join for this entity pair.
filters	Pre-filters applied to the related entity before aggregating. Use a sql expression with {source}.{field_name} references. Omit if no filters.

For the full mechanic — feature chaining, filtered metric features, which join is used, and metric-over-metric composition — see Metrics.

---

first_last — First or Last Value from a Source

Retrieves the first or last value from a set of rows, ordered by a specified field. Useful for "most recent plan", "first order date", "signup source".

- type: first_last
  name: first_order_date
  description: Date of the customer's first order
  data_type: datetime
  source: order
  join_name: customer_to_order
  options:
    method: first
    sort_by: order_date
    field: order_date
    data_type: datetime

Option	Description
method	first = smallest sort value; last = largest sort value
sort_by	The field to order rows by before selecting
field	The field to return from that row
offset	Optional. Which position to take — 1 returns the first/last row, 2 returns the second-from-first/last, etc. Defaults to 1 if omitted.

---

formula — Derived Value

Computes a value from other features on the same entity. References other features using {feature_name} syntax.

- type: formula
  name: days_since_signup
  data_type: number
  description: Number of days since this customer signed up
  sql: DATEDIFF('day', {signup_date}, CURRENT_DATE)

- type: formula
  name: revenue_per_order
  data_type: number
  description: Average revenue per order for this customer
  sql: CASE WHEN {total_orders} > 0 THEN {total_revenue} / {total_orders} ELSE 0 END

Formula features can reference any feature on the same entity — field, first_last, formula, or metric. They cannot reference features on other entities.

---

Entity Metrics

Entity metrics define how to aggregate rows of this entity. They are the targets of metric features on other entities.

metrics:
  - name: sum_amount
    description: Total order amount across all rows
    sql: SUM({amount})

  - name: count_orders
    description: Total number of orders
    sql: COUNT({order_id})

  - name: avg_order_value
    description: Average order value
    sql: AVG({amount})

Field	Description
name	Metric identifier — referenced by METRIC('name') in queries
description	Explains what it measures and how to use it
sql	Aggregation expression. References entity features with {feature_name} and can reference other metrics on the same entity via METRIC('name') (metric-over-metric composition). See Metrics for the full list of allowed patterns and dialect notes.

Entities are the source of truth. Raw warehouse tables are inputs — they exist to enrich entities, not to be queried directly. Metrics are defined on entities because entities are where business meaning lives. A raw table has columns; an entity has features, definitions, and metrics that the agent can reason about.

If you need a metric on data that currently lives only in a raw table, you have two options:

1. Create a new entity from that table — if the table's level of granularity doesn't exist as an entity yet. Define the entity, bring in the fields as features, and add the metric there.

2. Use an existing entity — if an entity already exists at the same level of granularity, create a relationship between that entity and the raw table, bring the fields in as features via related_sources, and define the metric as a rollup on those features.

In both cases, the metric ends up on an entity — which is the only place the agent can find and use it.

For what's allowed inside a metric's sql: — including conditional aggregation, dialect-specific constructs, and metric-over-metric composition via METRIC('name') — see Metrics.

---

related_sources

A related_source is a secondary warehouse table bolted onto an entity to enrich it with additional columns. The table is not an entity itself — it feeds columns into one. Each entry in related_sources: defines the join from the entity's key_source to the secondary table.

Why define one:

• Pull dimension or lookup columns from a separate table (e.g. a country name from a country reference table).

• Enrich an entity with CRM, billing, or third-party columns that don't warrant their own entity.

• Bring in flat reference tables (mapping tables, code-to-label lookups) whose rows have no independent business meaning.

What features you can build from a related_source:

Feature type	Supported	Why
field	Yes	Pulls a single column through the defined join
first_last	Yes	Picks one row from the related table ordered by a field
metric	No	Aggregations require the data to live in an entity — promote the table to an entity instead
formula	No	Formulas reference features on the same entity, never tables

If you need to count, sum, or average rows from this table, do not use related_sources — promote it to an entity.

Only use related_sources for tables that are NOT the key_source of any entity. If the table already has its own entity, define the join in entities_relationships.yml instead — not here. related_sources is strictly for enrichment tables without their own entity representation.

Entity vs. related_source — the granularity test:

The decision rule above is about features. This one is about the table itself: what level of granularity does this table represent? Create a new entity if the table represents a business concept at its own level of granularity (something you'd ask questions about on its own — e.g. order, subscription, session) or if other entities need to relate to it via the relationship graph. Otherwise — if it's flat enrichment with no independent business meaning — use related_sources.

How to pick the key_source for an entity:

A table is the right key_source for an entity if it contains all instances of the concept and each instance appears exactly once. For a customer entity, the customers table where every customer has exactly one row is the right key_source. Other tables at the same customer level (e.g., a CRM enrichment table with one row per customer) connect as related_sources. Tables at a finer granularity (e.g., orders, one row per order per customer) connect as relationships — they can be aggregated up to the customer level via metric features.

related_sources:
  db_prod.public.customer_details:
    relationship: one_to_one
    description: Extended customer info including billing address and plan details
    joins:
      - name: customer_id
        default: true
        type: sql
        sql: "{source}.{id} = {destination}.{customer_id}"

Join types for related_sources:

Type	Description
sql	Explicit SQL join condition using {source} and {destination}
lookup	Multi-hop join through intermediate tables

---

When to Use This File

Create or update an entity YAML file when a concept meets one of these conditions:

1. You're modeling a new business concept the agent should be able to query

2. You're adding or changing a metric, feature, or dimension on an existing entity

3. The agent produces wrong results for a specific entity — field choices, joins, or calculations need fixing

Examples:

• "We want the agent to answer questions about orders — revenue, volume, channel performance" → create an order entity

• "Users keep asking about customer health but there's no entity for it yet — model customer with ARR, NPS, and churn status" → create a customer entity

• "Add a new metric to customer — total revenue from the last 90 days" → add a filtered metric feature to the existing entity

• "The agent is joining the wrong table for revenue questions — there are two revenue fields and it keeps picking the wrong one" → add entity-level task instructions and improve feature descriptions

• "We onboarded a new data source for product usage — model it as a session entity so the agent can answer engagement questions" → create a new entity from the source table

---

Best Practices

Write descriptions that tell the agent when to use this entity. The agent selects entities based on name and description — a vague description means missed or wrong matches. Answer: what does this entity represent, and what questions should it answer?

Write feature descriptions that tell the agent when to use a field. When two date fields exist on an entity (e.g., created_at and completed_at), the description should state which one to use for filtering — for example, "use this for all date filtering, not created_at".

Define entity metrics before referencing them in metric features on other entities. Metric features on customer that reference order metrics depend on those metrics being defined on the order entity. Build the source entity first.

Prefer metric features for cross-entity aggregation. If you need a per-customer revenue total, define it as a metric feature — not a formula that approximates it. Metric features use the relationship join; formulas do not.

Use first_last for single values from a many-side entity. If you need "most recent order date" on customer, use first_last — not a formula. Formulas cannot aggregate across rows.

Keep key_source as the primary grain table. Enrichment from secondary tables belongs in related_sources. The key_source defines the entity's identity — one row = one entity instance.

---

Common Pitfalls

Avoid these common pitfalls when defining entity features and metrics.

Using a raw table name in a metric feature's source

# WRONG
- type: metric
  source: db_prod.core.orders    # this is a source table, not an entity
  metric: sum_amount

# RIGHT
- type: metric
  source: order                  # this is the entity name
  metric: sum_amount

Defining metrics on related_sources Sources cannot have metrics. If you need to aggregate from a source, create an entity whose key_source is that table, define the metrics on the entity, then use a metric feature.

Circular formula references Formula features can only reference features that are already computed at the same level. A formula cannot reference another formula that references it back.

Missing relationship for a metric feature Metric features require a relationship between the two entities in entities_relationships.yml. If the relationship does not exist, the metric feature cannot be resolved.

Defining entity aliases in the entity YAML Aliases — the different names business users use to refer to an entity — belong in the entity knowledge file, not here. The entity YAML defines schema, features, and metrics. The knowledge file is where the agent learns how users naturally refer to this entity in questions.

Using {feature_name} curly braces in the examples: section's expected_output Curly-brace {feature_name} is required in feature-definition SQL — formula sql:, entity-metric sql:, metric/first_last filter sql:, and join sql: — because Lynk resolves those references at compile time. But the examples: section's expected_output is a Lynk SQL query, not feature-definition SQL, so features are referenced by name without braces. See Lynk SQL for the full query-side rules.

Putting filtering rules, SQL instructions, or cross-entity references in the entity description The description field is for what the entity represents and what questions it answers — nothing more. Filtering rules like "always exclude is_inactive = true" belong in task instructions. Cross-entity pointers like "use the player entity for career aggregates" don't belong here either — the agent selects entities based on question relevance, not navigation hints embedded in descriptions.

---

Full Examples

These examples cover three different companies. Example 1 is Grove (B2B SaaS) — the customer entity, which uses metric features to pull subscription data from the subscription entity. Example 2 is Bly (e-commerce) — the order entity, standalone with its own metrics. Example 3 is Arcadia (mobile gaming) — the player entity, which uses metric features from purchase and formula-based segmentation. Reading all three shows the same pattern — entity metrics → feature chaining → query examples — in three distinct business contexts.

---

Example 1 — Grove (B2B SaaS), customer entity

The customer entity includes metric features from the subscription entity (active subscription count and total MRR). The customer-subscription relationship must be defined in entities_relationships.yml for these features to resolve.

name: customer
description: >
  A paying customer account at Grove. Use this entity for questions about ARR,
  churn, plan type, customer tier, NPS, and account health. For subscription-level
  detail (billing cycle, cancellation dates, MRR per subscription), use the subscription entity.

key_source: db_prod.public.customers

keys:
  - id

features:

  - type: field
    name: id
    data_type: number
    source: db_prod.public.customers
    description: Unique customer identifier
    field: id

  - type: field
    name: company_name
    data_type: string
    source: db_prod.public.customers
    description: Customer's company name
    field: company_name

  - type: field
    name: status
    data_type: string
    source: db_prod.public.customers
    description: >
      Account status.
      'active' — paying customer with a current subscription.
      'churned' — previously paying, subscription ended without renewal.
      'trial' — in free trial period, has not yet converted to paid.
    field: account_status

  - type: field
    name: plan_type
    data_type: string
    source: db_prod.public.customers
    description: >
      Subscription plan tier.
      'starter' — entry-level plan, typically SMB customers.
      'growth' — mid-tier plan for scaling teams.
      'enterprise' — full-featured plan for large organizations.
    field: plan_type

  - type: field
    name: arr
    data_type: number
    source: db_prod.public.customers
    description: Annual Recurring Revenue for this account, in USD
    field: arr

  - type: field
    name: nps_score
    data_type: number
    source: db_prod.public.customers
    description: Most recent NPS survey score (0–10). Updated quarterly.
    field: nps_score

  - type: field
    name: first_paid_at
    data_type: datetime
    source: db_prod.public.customers
    description: Timestamp of the customer's first payment — use this for acquisition date, not created_at
    field: first_paid_at

  - type: field
    name: is_test_account
    data_type: boolean
    source: db_prod.public.customers
    description: True if this is a test or internal account — always exclude from analytics queries
    field: is_test_account

  - type: field
    name: is_deleted
    data_type: boolean
    source: db_prod.public.customers
    description: True if this account has been soft-deleted — always exclude from analytics queries
    field: is_deleted

  # Metric features — pulled from the subscription entity via feature chaining
  # Requires the customer-subscription relationship to be defined in entities_relationships.yml
  - type: metric
    name: active_subscription_count
    description: Number of active subscriptions currently held by this customer
    data_type: number
    source: subscription
    filters:
      - type: sql
        sql: "{source}.{status} = 'active'"
    metric: count_subscriptions

  - type: metric
    name: total_mrr
    description: Total Monthly Recurring Revenue from all active subscriptions for this customer, in USD
    data_type: number
    source: subscription
    filters:
      - type: sql
        sql: "{source}.{status} = 'active'"
    metric: total_mrr

  # Formula — ARR-based customer tier for segmentation and reporting
  - type: formula
    name: customer_tier
    data_type: string
    description: >
      ARR-based tier used for segmentation and reporting.
      Enterprise = ARR >= $100K, Mid-Market = $20K–$99K, SMB = below $20K.
    sql: |
      CASE
        WHEN {arr} >= 100000 THEN 'Enterprise'
        WHEN {arr} >= 20000  THEN 'Mid-Market'
        ELSE 'SMB'
      END

metrics:

  - name: count_customers
    description: Total number of customers
    sql: COUNT(*)

  - name: total_arr
    description: Sum of ARR across all customers, in USD
    sql: SUM({arr})

  - name: avg_arr
    description: Average ARR per customer, in USD
    sql: AVG({arr})

  - name: churn_rate
    description: Percentage of customers with status = 'churned'
    sql: AVG(CASE WHEN {status} = 'churned' THEN 1.0 ELSE 0 END) * 100

examples:

  - type: SQL
    name: arr_by_customer_tier
    description: Total ARR and customer count by tier for active customers
    input: What is our ARR by customer tier?
    expected_output: |
      SELECT
        customer_tier,
        METRIC('total_arr') AS arr,
        METRIC('count_customers') AS customers
      FROM customer
      WHERE status = 'active'
        AND is_test_account = false
        AND is_deleted = false
      GROUP BY customer_tier
      ORDER BY arr DESC;
    tags:
      difficulty: EASY

  - type: SQL
    name: at_risk_customers
    description: >
      Active customers with low NPS and no active subscriptions in the last 60 days —
      churn risk signal. Uses the active_subscription_count metric feature from subscription.
    input: Show me active customers who are at risk of churning this quarter
    expected_output: |
      SELECT
        company_name,
        customer_tier,
        arr,
        nps_score,
        active_subscription_count,
        total_mrr
      FROM customer
      WHERE status = 'active'
        AND nps_score < 6
        AND active_subscription_count = 0
        AND is_test_account = false
        AND is_deleted = false
      ORDER BY arr DESC;
    tags:
      difficulty: MEDIUM

---

Example 2 — Bly (E-commerce), order entity

The order entity is standalone — all metrics are computed directly from order-level features. Note that order_date is the time field, not created_at.

name: order
description: >
  A purchase transaction at Bly. Use this entity for questions about revenue,
  order volume, average order value, channel performance, and refund rates.
  For product-level questions (best-selling SKUs, category performance), use the product entity
  joined through order_items.

key_source: db_prod.core.orders

keys:
  - order_id

features:

  - type: field
    name: order_id
    data_type: number
    source: db_prod.core.orders
    description: Unique order identifier
    field: order_id

  - type: field
    name: customer_id
    data_type: number
    source: db_prod.core.orders
    description: ID of the customer who placed this order
    field: customer_id

  - type: field
    name: status
    data_type: string
    source: db_prod.core.orders
    description: >
      Order fulfillment status.
      'completed' — order fulfilled and paid. Use this filter for all revenue analysis.
      'cancelled' — order placed but not fulfilled.
      'refunded' — completed order that was subsequently refunded.
      'pending' — order received but not yet processed.
    field: status

  - type: field
    name: channel
    data_type: string
    source: db_prod.core.orders
    description: >
      Last-click acquisition channel at time of purchase —
      'organic', 'paid_search', 'paid_social', 'email', or 'direct'
    field: channel

  - type: field
    name: gross_amount
    data_type: number
    source: db_prod.core.orders
    description: Order total before discounts and refunds, in USD. Use net_amount for revenue by default.
    field: gross_amount

  - type: field
    name: net_amount
    data_type: number
    source: db_prod.core.orders
    description: Order total after discounts and refunds, in USD. Default revenue field.
    field: net_amount

  - type: field
    name: discount_pct
    data_type: number
    source: db_prod.core.orders
    description: Discount percentage applied to this order (0–100)
    field: discount_pct

  - type: field
    name: order_date
    data_type: datetime
    source: db_prod.core.orders
    description: >
      Date and time the order was placed — use this for all date filtering, not created_at.
    field: order_date

  - type: field
    name: is_test_order
    data_type: boolean
    source: db_prod.core.orders
    description: True if this is a test or internal order — always exclude from revenue and analytics queries
    field: is_test_order

  # First/last feature — primary product category from order_items (many items per order)
  - type: first_last
    name: primary_category
    description: Product category of the highest-value item in this order — use for category-level analysis
    data_type: string
    source: db_prod.core.order_items
    join_name: order_to_items
    options:
      method: last
      sort_by: item_amount
      offset: 1
      field: category
      data_type: string

related_sources:
  db_prod.core.order_items:
    description: Line-item detail for each order — one order has many items
    relationship: one_to_many
    joins:
      - name: order_to_items
        default: true
        type: sql
        sql: "{source}.{order_id} = {destination}.{order_id}"

metrics:

  - name: count_orders
    description: Total number of orders
    sql: COUNT(*)

  - name: sum_net_revenue
    description: Total net revenue across orders, in USD
    sql: SUM({net_amount})

  - name: avg_order_value
    description: Average net order value, in USD
    sql: AVG({net_amount})

  - name: refund_rate
    description: Percentage of orders that were refunded
    sql: AVG(CASE WHEN {status} = 'refunded' THEN 1.0 ELSE 0 END) * 100

examples:

  - type: SQL
    name: net_revenue_by_channel
    description: Net revenue and order count by acquisition channel for the current month
    input: Show me net revenue and order count by channel this month
    expected_output: |
      SELECT
        channel,
        METRIC('sum_net_revenue') AS net_revenue,
        METRIC('count_orders')    AS order_count,
        METRIC('avg_order_value') AS aov
      FROM order
      WHERE status = 'completed'
        AND is_test_order = false
        AND order_date >= DATE_TRUNC('month', CURRENT_DATE)
      GROUP BY channel
      ORDER BY net_revenue DESC;
    tags:
      difficulty: EASY

  - type: SQL
    name: refund_rate_by_channel
    description: >
      Refund rate by acquisition channel — includes both completed and refunded orders
      to compute the percentage correctly.
    input: What is our refund rate by channel?
    expected_output: |
      SELECT
        channel,
        METRIC('refund_rate') AS refund_rate_pct,
        METRIC('count_orders') AS total_orders
      FROM order
      WHERE status IN ('completed', 'refunded')
        AND is_test_order = false
      GROUP BY channel
      ORDER BY refund_rate_pct DESC;
    tags:
      difficulty: MEDIUM

---

Example 3 — Arcadia (Mobile gaming), player entity

This example shows formula-based segmentation (player_segment derived from rolling 30-day spend), two metric features from purchase (lifetime and filtered), and a MEDIUM query that identifies high-value players at churn risk. The player-purchase relationship must be defined in entities_relationships.yml for the metric features to resolve.

name: player
description: >
  A registered Arcadia player who has completed at least one game session. Use this entity
  for questions about player counts, activity, spend, segmentation, and D7 retention.
  For session-level metrics (session length, level reached), use the session entity.
  For purchase-level detail, use the purchase entity.

key_source: db_game.public.players

keys:
  - player_id

features:

  - type: field
    name: player_id
    data_type: number
    source: db_game.public.players
    description: Unique player identifier
    field: player_id

  - type: field
    name: username
    data_type: string
    source: db_game.public.players
    description: Player's display name
    field: username

  - type: field
    name: install_date
    data_type: datetime
    source: db_game.public.players
    description: >
      Date the player installed the game. Do not use for cohort analysis on players
      who installed before 2022-03-01 — those records have install_date set to the
      migration date. Use the player_cohort entity for pre-migration cohorts.
    field: install_date

  - type: field
    name: last_session_at
    data_type: datetime
    source: db_game.public.players
    description: >
      Timestamp of the player's most recent session. Use this for all activity checks —
      never use is_active, which lags the actual session data by up to 24 hours.
    field: last_session_at

  - type: field
    name: device_type
    data_type: string
    source: db_game.public.players
    description: Primary device type — 'ios' or 'android'
    field: device_type

  - type: field
    name: country
    data_type: string
    source: db_game.public.players
    description: Player's country, derived from install-time IP
    field: country

  - type: field
    name: d7_retained
    data_type: boolean
    source: db_game.public.players
    description: >
      True if the player had a session on exactly day 7 after install.
      Pre-calculated at day 7 and never updated. Use player_cohort for cohort-level D7 rates.
    field: d7_retained

  # Formula — active player flag based on session recency
  - type: formula
    name: is_active_7d
    data_type: boolean
    description: True if this player had a session in the last 7 days. Use this for all active player filters — do not recalculate inline.
    sql: "{last_session_at} >= CURRENT_DATE - INTERVAL '7 days'"

  # Metric features — pulled from the purchase entity via feature chaining
  # Requires the player-purchase relationship to be defined in entities_relationships.yml
  - type: metric
    name: total_spend_usd
    description: >
      Total lifetime spend in USD from real-money purchases (hard currency only).
      Does not include soft currency earned through gameplay.
    data_type: number
    source: purchase
    filters:
      - type: sql
        sql: "{source}.{purchase_currency} = 'USD'"
    metric: sum_net_revenue_usd

  # Filtered metric feature — spend in the last 30 days for the player_segment formula
  - type: metric
    name: spend_last_30_days_usd
    description: Real-money spend in USD over the last 30 days. Basis for the player_segment formula.
    data_type: number
    source: purchase
    filters:
      - type: sql
        sql: >
          {source}.{purchase_currency} = 'USD'
          AND {source}.{purchase_date} >= CURRENT_DATE - INTERVAL '30 days'
    metric: sum_net_revenue_usd

  # Formula — player segment based on rolling 30-day spend (recalculated weekly via batch job)
  - type: formula
    name: player_segment
    data_type: string
    description: >
      Spend-based segment derived from spend_last_30_days_usd.
      Whale = >$100, Dolphin = $10–$100, Minnow = <$10 with ≥1 purchase, Non-payer = $0.
      Recalculated weekly by batch job — may be up to 7 days stale. Always use this feature
      for segment filtering; never recalculate manually from total_spend_usd.
    sql: |
      CASE
        WHEN {spend_last_30_days_usd} > 100 THEN 'whale'
        WHEN {spend_last_30_days_usd} >= 10  THEN 'dolphin'
        WHEN {spend_last_30_days_usd} > 0    THEN 'minnow'
        ELSE 'non_payer'
      END

metrics:

  - name: count_players
    description: Total number of players
    sql: COUNT(*)

  - name: avg_spend_usd
    description: Average lifetime spend per player in USD
    sql: AVG({total_spend_usd})

examples:

  - type: SQL
    name: active_players_by_device
    description: Count of active players (session in last 7 days) grouped by device type
    input: How many active players do we have by device type?
    expected_output: |
      SELECT
        device_type,
        METRIC('count_players') AS active_players
      FROM player
      WHERE last_session_at >= CURRENT_DATE - INTERVAL '7 days'
      GROUP BY device_type
      ORDER BY active_players DESC;
    tags:
      difficulty: EASY

  - type: SQL
    name: whale_churn_risk
    description: >
      Whale players with no session in the last 14 days — highest-value segment showing
      early lapse signal. Filters on player_segment formula and last_session_at.
    input: Show me whale players who haven't played in the last 14 days
    expected_output: |
      SELECT
        player_id,
        username,
        country,
        device_type,
        total_spend_usd,
        spend_last_30_days_usd,
        last_session_at
      FROM player
      WHERE player_segment = 'whale'
        AND last_session_at < CURRENT_DATE - INTERVAL '14 days'
      ORDER BY total_spend_usd DESC;
    tags:
      difficulty: MEDIUM