Semantic Layer

A semantic layer is the business contract between your warehouse and the questions people or agents ask. Its YAML maps physical tables, columns, calculations, and joins to stable names such as conversion_rate, segment, and enterprise_accounts. Use a semantic layer when you want recurring questions such as “How many accounts converted?” and “Which segments are most engaged?” to use the same definitions every time, without rebuilding business logic in each query.

Why use a semantic layer?

Warehouse schemas describe how data is stored. They do not, by themselves, define what your organization means by pipeline, conversion, an active account, or the correct date for a report. The semantic layer records those decisions once and makes them available by name.

Without a semantic layer	With a semantic layer
Each query can redefine a business metric	Named metrics reuse the same aggregate expression
Analysts must remember exclusions and row grain	The table documents its grain, while `base_filter` applies its default population
Joins are reconstructed from warehouse knowledge	Declared relationships identify supported join paths and their uniqueness
Agents may guess tables, columns, or filters	Agents choose from known tables, dimensions, metrics, and filters
Generated SQL is difficult to explain	Semantic queries return the rendered SQL with the rows for inspection
Warehouse-specific expressions spread into tools	A dialect hint lets the same model compile expressions for supported destinations

This makes the layer useful for both self-service analytics and agent-driven workflows:

Consistent answers: A semantic request combines a named calculation with declared table grain, base filters, dimensions, and relationship context.
Safer query construction: Agents select declared semantic objects instead of inventing physical table and column names.
Auditable results: The rendered SQL shows how the semantic request became a warehouse query and provides a starting point for debugging.
Controlled flexibility: Named objects are the default, while custom SQL or direct SQL remains an explicit fallback for analysis the layer does not yet cover.
Easier evolution: You can add definitions centrally and treat existing names as contracts for downstream questions and workflows.

A semantic layer does not replace warehouse modeling or data-quality checks. It makes the approved meaning and query path explicit on top of that data.

Structure at a glance

A semantic YAML file contains a model and one or more logical tables.

Section	Purpose
Model details	Names and describes the semantic model
Tables	Maps logical tables to physical warehouse tables
Dimensions	Identifies, groups, and filters records
Time dimensions	Supports date- and timestamp-based analysis
Facts	Exposes row-level values, such as contract value or engagement score
Metrics	Defines aggregate calculations, such as counts, averages, and ratios
Filters	Names reusable SQL conditions
Relationships	Defines supported joins between logical tables
Funnel events	Defines optional milestones, such as creation, engagement, and signup
Verified queries	Records optional SQL examples that have been checked against the data

At minimum, define the model’s name, description, and tables. Each table needs a name, description, and physical base_table.

Choose one stable entity identifier for the model and set main_id: true on that dimension. Mark unique join keys with unique: true or include them in the table’s primary_key.

Field reference

Model fields

Field	Required	Description
`name`	Yes	Stable model name
`description`	Yes	Human-readable purpose of the model
`comments`	No	Author notes
`tables`	Yes	One or more logical table definitions
`relationships`	No	Supported joins between logical tables
`funnel_events`	No	Shared funnel identity and step definitions
`verified_queries`	No	Checked natural-language questions and their SQL
`customer_stream_data_start_date`	No	Earliest date covered by the model, in `YYYY-MM-DD` format
`customer_stream_data_end_date`	No	Latest date covered by the model, in `YYYY-MM-DD` format
`semantic_layer_yaml_dialect`	No	Set to `snowflake` or `bigquery` when expressions use that dialect; otherwise omit it

Table fields

Field	Required	Description
`name`	Yes	Logical table name
`description`	Yes	Table purpose and row grain
`base_table`	Yes	Physical `database`, `schema`, and `table` mapping
`primary_key`	No	One or more columns that uniquely identify a row
`dimensions`	No	Identifiers, categories, labels, and flags
`time_dimensions`	No	Date and timestamp fields
`facts`	No	Row-level values
`metrics`	No	Aggregate calculations
`filters`	No	Reusable named predicates
`base_filter`	No	Predicate applied whenever the table is queried
`funnel_events`	No	Funnel steps scoped to this table; prefer shared model-level steps
`table_type`	No	Optional `event` or `metric` classification

Dimensions, facts, metrics, and filters

Dimensions and time dimensions require name and data_type. Add expr when the physical column or SQL expression differs from the semantic name.
Facts require name and data_type. They represent row-level values and can also use expr.
Metrics require name and expr. Metric expressions aggregate rows with functions such as COUNT, SUM, or AVG.
Filters require name and expr. A filter expression is a SQL predicate, without the WHERE keyword.
All four field types support an optional description and synonyms. Dimensions also support unique and main_id.

Relationships

A relationship connects two logical tables with one column mapping. Composite relationship keys are not supported. Use left_outer or inner for join_type, and many_to_one or one_to_one for relationship_type. For many_to_one, the right-side column must be unique. For one_to_one, both sides must be unique. Use simple semantic column names in relationship_columns.

Funnel events

Define shared funnel events at the model level. Each structured step uses a source_table and either time_dimension or ts_expr. Add filter_expr when the source table contains more than one event type.

funnel_events:
  identity:
    user_id: account_id
  steps:
    - id: account_created
      name: Account Created
      source_table: accounts
      time_dimension: created_at
    - id: activated
      name: Activated
      source_table: activities
      time_dimension: occurred_at
      filter_expr: activity_type = 'activation'

Build a semantic layer

Confirm each table's grain

Write down what one row represents and identify its primary key. For example, an accounts table may contain one row per account, while an activities table contains one row per event.

Choose the main entity ID

Use the most stable account, contact, or user identifier. Set main_id: true on exactly one dimension in the model.

Add dimensions and time dimensions

Start with the fields people already use to group and filter reports. Use lowercase snake_case semantic names and describe any field whose meaning is not obvious.

Add facts and metrics

Keep row-level values in facts and aggregate calculations in metrics. Guard ratios against division by zero with NULLIF.

Add filters and relationships

Use named filters for common conditions. Define a relationship only when the unique side of the join is clear.

Validate representative questions

Test dimensions, metrics, filters, relationships, and funnels against direct SQL results before other workflows depend on the model.

Example template

This example models fictional accounts and activities. All identifiers and values are synthetic.

Show View the complete semantic YAML

name: example_revenue_model
description: Semantic model for account and engagement reporting.

tables:
  - name: accounts
    description: One row per account.
    base_table:
      database: ANALYTICS
      schema: REPORTING
      table: ACCOUNTS
    primary_key:
      columns:
        - account_id
    dimensions:
      - name: account_id
        description: Stable account identifier.
        data_type: VARCHAR
        expr: ACCOUNT_ID
        unique: true
        main_id: true
      - name: segment
        description: Commercial segment assigned to the account.
        data_type: VARCHAR
        expr: SEGMENT
      - name: region
        description: Reporting region.
        data_type: VARCHAR
        expr: REGION
      - name: is_customer
        description: Whether the account has converted to a customer.
        data_type: BOOLEAN
        expr: CUSTOMER_SINCE IS NOT NULL
    time_dimensions:
      - name: created_at
        description: Account creation timestamp.
        data_type: TIMESTAMP
        expr: CREATED_AT
    facts:
      - name: annual_contract_value
        description: Current annual contract value.
        data_type: DECIMAL
        expr: ANNUAL_CONTRACT_VALUE
    metrics:
      - name: total_accounts
        description: Number of accounts in the selected cohort.
        expr: COUNT(*)
      - name: conversion_rate
        description: Share of accounts that converted.
        expr: >-
          SUM(CASE WHEN is_customer THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0)
    filters:
      - name: enterprise_accounts
        description: Restrict results to the enterprise segment.
        expr: segment = 'enterprise'

  - name: activities
    description: One row per account activity.
    base_table:
      database: ANALYTICS
      schema: REPORTING
      table: ACTIVITIES
    primary_key:
      columns:
        - activity_id
    dimensions:
      - name: activity_id
        description: Stable activity identifier.
        data_type: VARCHAR
        expr: ACTIVITY_ID
        unique: true
      - name: account_id
        description: Account associated with the activity.
        data_type: VARCHAR
        expr: ACCOUNT_ID
      - name: activity_type
        description: Normalized activity category.
        data_type: VARCHAR
        expr: ACTIVITY_TYPE
    time_dimensions:
      - name: occurred_at
        description: Timestamp when the activity occurred.
        data_type: TIMESTAMP
        expr: OCCURRED_AT
    metrics:
      - name: total_activities
        description: Number of activities.
        expr: COUNT(*)
      - name: engaged_accounts
        description: Number of accounts with activity.
        expr: COUNT(DISTINCT account_id)

relationships:
  - name: activities_to_accounts
    description: Each activity belongs to one account.
    left_table: activities
    right_table: accounts
    relationship_columns:
      - left_column: account_id
        right_column: account_id
    join_type: left_outer
    relationship_type: many_to_one

How agents use the layer

An agent first reads the available semantic tables and fields, then submits a small request using their canonical names. Deepline renders the request into the warehouse dialect, executes it, and returns both the result rows and rendered SQL.

Business question → semantic names → rendered warehouse SQL → rows and SQL

For example, this request uses the accounts table and conversion_rate metric defined in the template above:

deepline tools execute snowflake_run_semantic_query --payload '{
  "type": "metrics",
  "params": {
    "table_name": "accounts",
    "metrics": ["conversion_rate"],
    "dimensions": ["segment"]
  },
  "rowLimit": 100
}'

Start with one metric and a small row limit. Expand the request after confirming the table, metric, dimensions, filters, rendered SQL, and initial rows. If the semantic layer cannot express the question, use the rendered SQL as the safest starting point for an explicit raw-SQL fallback.

Validate and save the layer

Save the YAML in a local file, then run the connector’s update operation with dryRun before writing it.

Snowflake
Redshift

jq -Rs '{yaml: ., dryRun: true}' semantic-layer.yml > semantic-layer-input.json
deepline tools execute snowflake_update_semantic_layer --payload "$(cat semantic-layer-input.json)"

jq -Rs '{yaml: ., dryRun: true}' semantic-layer.yml > semantic-layer-input.json
deepline tools execute redshift_update_semantic_layer --payload "$(cat semantic-layer-input.json)"

For Snowflake, dryRun checks that the YAML parses and that each table declares a name and physical table reference. It does not confirm that the referenced warehouse objects exist or compile every semantic definition. Treat it as a first check, not a complete runtime validation. Validate and save the layer in a non-production connection first: change dryRun to false, run representative semantic queries, and compare their results with known direct SQL results. Promote the same validated YAML to the target connection only after those checks pass.

A successful syntax check does not confirm that every physical column, expression, metric, or relationship returns the intended result. Validate the generated queries against your warehouse before using the layer in production workflows.

Validation checklist

The YAML parses without unknown or misspelled fields.
Every physical table and referenced column exists.
Exactly one dimension has main_id: true.
Relationship columns use compatible data types, and the “one” side is unique.
Metric results match direct SQL for a known test case.
Filters return the intended cohort.
Time-based results use the expected timezone and date grain.
Names are stable, descriptive, and written in lowercase snake_case.

Change definitions safely

Treat logical table, dimension, metric, filter, and funnel step names as query contracts. Prefer additive changes. If a definition’s meaning or grain changes, introduce a new name and validate it before removing the old definition. Keep the last validated YAML available so you can restore it if a new layer produces incorrect SQL or results.

Common errors

Error	What to check
Missing or multiple main IDs	Set `main_id: true` on exactly one dimension
Unknown field	Check spelling and remove keys that are not part of the schema
Missing physical table or column	Check `base_table` and field expressions against the warehouse
Invalid relationship	Confirm table names, column names, data types, and uniqueness
Ambiguous cross-table reference	Ensure there is one clear relationship path between the tables
Funnel step cannot resolve its event time	Add `time_dimension` or `ts_expr` to the step
Metric returns an unexpected count or rate	Recheck table grain, distinct identifiers, filters, and denominators

Start with the smallest useful layer. Add fields and metrics after the core definitions produce trusted results.

Getting Started

GTM is code

Features

Deepline SDK beta

Integrations

API Reference

Why use a semantic layer?

Structure at a glance

Field reference

Model fields

Table fields

Dimensions, facts, metrics, and filters

Relationships

Funnel events

Build a semantic layer

Example template

How agents use the layer

Validate and save the layer

Validation checklist

Change definitions safely

Common errors

​Why use a semantic layer?

​Structure at a glance

​Field reference

​Model fields

​Table fields

​Dimensions, facts, metrics, and filters

​Relationships

​Funnel events

​Build a semantic layer

​Example template

​How agents use the layer

​Validate and save the layer

​Validation checklist

​Change definitions safely

​Common errors

Why use a semantic layer?

Structure at a glance

Field reference

Model fields

Table fields

Dimensions, facts, metrics, and filters

Relationships

Funnel events

Build a semantic layer

Example template

How agents use the layer

Validate and save the layer

Validation checklist

Change definitions safely

Common errors