# Gemma Lightdash Examples

Practical before/after examples showing how to enrich dbt schema.yml files with Lightdash semantic layer configuration.

> **Note:** These examples use `config.meta` syntax (dbt v1.10+), which is the Gemma convention. The native `developing-in-lightdash` skill uses bare `meta:` — do not mix the two formats in the same file. For the full Lightdash property reference, see the native skill.

---

## Example 1: Fact Table — Before & After

### Before (bare dbt schema)

```yaml
models:
  - name: fct_meal_invoicing
    description: >
      Transaction fact: one row per invoice line per customer per date.
    columns:
      - name: invoice_line_key
        data_tests:
          - not_null
          - unique
      - name: company_key
        data_tests:
          - not_null
      - name: date_key
        data_tests:
          - not_null
      - name: quantity
        description: "Number of units on this line."
      - name: net_amount
        description: "Net revenue in EUR."
      - name: gross_amount
        description: "Gross revenue in EUR (incl. VAT)."
      - name: invoice_line_type
        data_tests:
          - accepted_values:
              values: ['meal', 'article', 'flatrate', 'other']
      - name: is_subsidized
        description: "Whether the line is subsidized."
```

### After (enriched with Lightdash meta)

```yaml
models:
  - name: fct_meal_invoicing
    description: >
      Transaction fact: one row per invoice line per customer per date.
    config:
      meta:
        label: "Meal Invoicing"
        group_label: "Finance"
        order_fields_by: "index"
        joins:
          - join: dim_company
            sql_on: "${fct_meal_invoicing.company_key} = ${dim_company.company_key}"
            relationship: many-to-one
          - join: dim_date
            sql_on: "${fct_meal_invoicing.date_key} = ${dim_date.date_key}"
            relationship: many-to-one
        metrics:
          revenue_per_meal:
            type: number
            label: "Revenue per Meal"
            description: "Average net revenue per meal unit."
            sql: "${total_net_revenue} / NULLIF(${total_meal_quantity}, 0)"
            format: "[$€]#,##0.00"
            groups: ["Derived"]
    columns:
      - name: invoice_line_key
        data_tests:
          - not_null
          - unique
        config:
          meta:
            dimension:
              type: string
              hidden: true

      - name: company_key
        data_tests:
          - not_null
        config:
          meta:
            dimension:
              type: string
              hidden: true

      - name: date_key
        data_tests:
          - not_null
        config:
          meta:
            dimension:
              type: date
              label: "Invoice Date"
              groups: ["Date"]

      - name: quantity
        description: "Number of units on this line."
        config:
          meta:
            dimension:
              type: number
              label: "Quantity"
              groups: ["Volume"]
            metrics:
              total_quantity:
                type: sum
                label: "Total Quantity"
                description: "Sum of units across all lines."
                format: "#,##0"
                groups: ["Volume"]
              total_meal_quantity:
                type: sum
                label: "Total Meal Quantity"
                description: "Sum of units for meal lines only."
                filters:
                  - invoice_line_type: "meal"
                format: "#,##0"
                groups: ["Volume"]

      - name: net_amount
        description: "Net revenue in EUR."
        config:
          meta:
            dimension:
              type: number
              label: "Net Amount"
              format: "[$€]#,##0.00"
              hidden: true
            metrics:
              total_net_revenue:
                type: sum
                label: "Total Net Revenue"
                description: "Sum of net revenue (excl. VAT)."
                format: "[$€]#,##0.00"
                groups: ["Revenue"]
              avg_net_revenue:
                type: average
                label: "Avg Net Revenue per Line"
                format: "[$€]#,##0.00"
                round: 2
                groups: ["Revenue"]

      - name: gross_amount
        description: "Gross revenue in EUR (incl. VAT)."
        config:
          meta:
            dimension:
              type: number
              label: "Gross Amount"
              format: "[$€]#,##0.00"
              hidden: true
            metrics:
              total_gross_revenue:
                type: sum
                label: "Total Gross Revenue"
                description: "Sum of gross revenue (incl. VAT)."
                format: "[$€]#,##0.00"
                groups: ["Revenue"]

      - name: invoice_line_type
        data_tests:
          - accepted_values:
              values: ['meal', 'article', 'flatrate', 'other']
        config:
          meta:
            dimension:
              type: string
              label: "Line Type"
              groups: ["Product"]

      - name: is_subsidized
        description: "Whether the line is subsidized."
        config:
          meta:
            dimension:
              type: boolean
              label: "Is Subsidized"
              groups: ["Flags"]
```

**Key patterns demonstrated:**
- Surrogate keys (`_key` suffix) → `hidden: true`
- Numeric amounts → `hidden: true` on dimension, metrics exposed with `sum`/`average`
- Boolean flags → `type: boolean`, placed in "Flags" group
- Filtered metrics using other columns (`filters: - invoice_line_type: "meal"`)
- Model-level calculated metric referencing column-level metrics (`${total_net_revenue} / NULLIF(...)`)

---

## Example 2: Dimension Table

```yaml
models:
  - name: dim_company
    description: >
      Company dimension — one row per legal entity.
    config:
      meta:
        label: "Companies"
        group_label: "Organisation"
        order_fields_by: "index"
    columns:
      - name: company_key
        data_tests:
          - not_null
          - unique
        config:
          meta:
            dimension:
              type: string
              hidden: true

      - name: company_name
        description: "Legal entity name."
        config:
          meta:
            dimension:
              type: string
              label: "Company"
              groups: ["Organisation"]
            metrics:
              company_count:
                type: count_distinct
                label: "Number of Companies"
                groups: ["Organisation"]

      - name: company_subgroup
        description: "Operational subgroup."
        config:
          meta:
            dimension:
              type: string
              label: "Subgroup"
              groups: ["Organisation"]

      - name: company_group
        description: "Top-level corporate group."
        config:
          meta:
            dimension:
              type: string
              label: "Group"
              groups: ["Organisation"]
```

---

## Example 3: Date Dimension with Time Intervals

```yaml
columns:
  - name: date_key
    config:
      meta:
        dimension:
          type: date
          label: "Date"
          time_intervals:
            - DAY
            - WEEK
            - MONTH
            - QUARTER
            - YEAR
            - MONTH_NAME
            - DAY_OF_WEEK_NAME
          groups: ["Date"]

  - name: created_at
    config:
      meta:
        dimension:
          type: timestamp
          label: "Created At"
          time_intervals:
            - RAW
            - DAY
            - WEEK
            - MONTH
            - QUARTER
            - YEAR
```

---

## Example 4: Accumulating Snapshot Fact

```yaml
models:
  - name: fct_institution_lifecycle
    description: >
      Accumulating snapshot: one row per institution.
    config:
      meta:
        label: "Institution Lifecycle"
        group_label: "Operations"
        joins:
          - join: dim_institution
            sql_on: "${fct_institution_lifecycle.institution_key} = ${dim_institution.institution_key}"
            relationship: one-to-one
    columns:
      - name: institution_key
        config:
          meta:
            dimension:
              type: string
              hidden: true

      - name: first_invoice_month
        config:
          meta:
            dimension:
              type: date
              label: "First Invoice Month"
              groups: ["Lifecycle"]

      - name: last_invoice_month
        config:
          meta:
            dimension:
              type: date
              label: "Last Invoice Month"
              groups: ["Lifecycle"]

      - name: is_churned
        config:
          meta:
            dimension:
              type: boolean
              label: "Churned"
              groups: ["Lifecycle"]
              colors:
                "true": "#ef4444"
                "false": "#22c55e"
            metrics:
              churned_count:
                type: count
                label: "Churned Institutions"
                filters:
                  - is_churned: "true"
                groups: ["Lifecycle"]
              active_count:
                type: count
                label: "Active Institutions"
                filters:
                  - is_churned: "false"
                groups: ["Lifecycle"]

      - name: lifetime_revenue
        config:
          meta:
            dimension:
              type: number
              hidden: true
            metrics:
              total_lifetime_revenue:
                type: sum
                label: "Total Lifetime Revenue"
                format: "[$€]#,##0.00"
                groups: ["Revenue"]
              avg_lifetime_revenue:
                type: average
                label: "Avg Lifetime Revenue"
                format: "[$€]#,##0.00"
                groups: ["Revenue"]
```

---

## Grouping Conventions

Recommended standard groups for organizing dimensions and metrics in the sidebar:

| Group           | Use For                                           |
|-----------------|---------------------------------------------------|
| `Organisation`  | Company, entity, department, team names            |
| `Product`       | Product types, categories, line types              |
| `Date`          | Date/time dimensions                               |
| `Revenue`       | Revenue and monetary metrics                       |
| `Volume`        | Count and quantity metrics                         |
| `Pricing`       | Unit prices, averages per unit                     |
| `Flags`         | Boolean indicator columns                          |
| `Identifiers`   | IDs that users might need to see (not hidden keys) |
| `Lifecycle`     | Churn, retention, first/last dates                 |
| `Derived`       | Model-level calculated metrics                     |
| `Operations`    | Operational status, processing flags               |