> ## Documentation Index > Fetch the complete documentation index at: https://deepline.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Semantic Layer > Give people and agents consistent, inspectable definitions for warehouse metrics, dimensions, filters, relationships, and funnels. 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. ```yaml theme={null} 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 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. Use the most stable account, contact, or user identifier. Set `main_id: true` on exactly one dimension in the model. 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. Keep row-level values in `facts` and aggregate calculations in `metrics`. Guard ratios against division by zero with `NULLIF`. Use named filters for common conditions. Define a relationship only when the unique side of the join is clear. 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. ```yaml theme={null} 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. ```text theme={null} 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: ```bash theme={null} 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. ```bash theme={null} 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)" ``` ```bash theme={null} 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.