In short

  • Point an agent at raw Iceberg tables and it will write fluent, confident SQL that is often wrong: joins that double-count, the wrong date column, the wrong time zone, test orders left in.
  • A semantic layer writes those decisions down once, as code: what each entity is, its grain, how metrics are calculated, which joins are allowed, which time zone a "month" is in, and who may see which rows.
  • Agents then ask for metrics by name instead of writing SQL. The layer generates the query, and the answer comes back with the definition and the table snapshot it used.
  • Iceberg makes this easier than older table formats: hidden partitioning, snapshot IDs for reproducible answers, safe column renames, and one catalog shared by every engine.

How an agent gets revenue wrong

Ask an agent with read access to your lakehouse: "What was our revenue from Maharashtra last month?" It finds orders, order_items and customers, and writes something reasonable-looking:

SELECT SUM(o.order_total)
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN customers c   ON c.customer_id = o.customer_id
WHERE c.state = 'Maharashtra'
  AND o.created_at >= DATE '2026-09-01'
  AND o.created_at <  DATE '2026-10-01';

It runs, it returns a number, and the number is wrong in at least five ways:

  1. Fan-out. Joining orders to their items repeats each order once per item, so order_total is summed two, three or ten times.
  2. Which revenue? Gross order value, net of refunds and discounts, or recognised when shipped? Finance has an answer; the table does not.
  3. Which date? Order created, payment captured, or shipped? Each gives a different "last month".
  4. Which time zone? Timestamps are stored in UTC, but an Indian month starts at 18:30 UTC on the last day of the previous month. Orders placed in the first five and a half hours of a month, India time, are counted in the month before.
  5. Which rows? Test orders, cancelled orders and soft-deleted rows are still in the table, flagged by columns only the data team knows about.

None of this is the model's fault. These are decisions your analysts carry in their heads. A semantic layer is where you write them down so that people, dashboards and agents all apply them the same way.

What the semantic layer defines

DefinitionWhat it settlesExample
Entities and keysWhat a thing is and how it is identifiedAn order is identified by order_id; a customer by customer_id
GrainWhat one row meansfct_orders has one row per order, never per item
Measures and metricsHow numbers are calculatedNet revenue = item value − discounts − refunds, in INR
Approved joinsWhich paths are allowed, and their cardinalityOrder to customer is many-to-one; order to item is one-to-many, so never sum order-level values across it
TimeDefault date column, time zone and calendarRevenue uses payment capture time, in Asia/Kolkata
Standing filtersRows that never countExclude test, cancelled and soft-deleted orders
Access rulesWho may see which rowsRegional managers see only their region
Descriptions and synonymsHow people actually ask"Sales", "GMV" and "turnover" map to named metrics, or are flagged as ambiguous

Written as code, a metric definition looks something like this. The format below is illustrative and tool-neutral; dbt's semantic layer, Cube and similar tools each have their own syntax for the same ideas.

entity: order
table: lakehouse.sales.fct_orders        # Iceberg table, one row per order
primary_key: order_id
time:
  default: payment_captured_at
  timezone: Asia/Kolkata
always_filter: "is_test = false AND status <> 'cancelled' AND deleted_at IS NULL"
joins:
  - to: customer
    on: customer_id
    relationship: many_to_one
metrics:
  - name: net_revenue
    label: Net revenue (INR)
    description: Item value minus discounts and refunds, counted when payment is captured.
    sql: SUM(item_value_inr - discount_inr - refunded_inr)
    synonyms: [revenue, net sales]
  - name: gross_merchandise_value
    label: GMV (INR)
    description: Item value before discounts and refunds, counted when the order is placed.
    sql: SUM(item_value_inr)
    time_override: ordered_at

Where Iceberg helps

A semantic layer can sit on any warehouse, but Apache Iceberg tables remove several sources of agent error at the table level.

Hidden partitioning

An Iceberg table can be partitioned by a transform of a column, such as days(payment_captured_at). Queries filter on the timestamp itself and still skip irrelevant files. Generated SQL does not need to know there is a partition column, which removes a whole class of slow or wrong queries.

Snapshots make answers reproducible

Every write to an Iceberg table creates a new snapshot with an ID. If the layer records the snapshot ID used for each answer, anyone can re-run the exact query later, against the exact same data:

-- Trino
SELECT ... FROM lakehouse.sales.fct_orders FOR VERSION AS OF 5826430931275021870;
-- Spark SQL
SELECT ... FROM lakehouse.sales.fct_orders VERSION AS OF 5826430931275021870;

When finance asks why the agent said ₹4.1 crore on Tuesday and the dashboard says ₹4.3 crore today, you can show whether the definition changed or the data did.

Safe schema changes

Iceberg tracks columns by ID, not by name or position, so a rename does not silently attach the wrong data to a column. The semantic layer still references names, which is why it needs the tests described below, but the table itself will not corrupt a query because of a rename.

One catalog for every engine

With an Iceberg REST catalog (for example Apache Polaris, Unity Catalog, or AWS Glue's Iceberg endpoint), Spark, Trino, Flink and other engines all see the same tables and the same snapshots. The semantic layer can then sit over one consistent set of tables, whichever engine runs the query.

Freshness you can report

Each snapshot records when it was committed, so the layer can return "data as of" alongside every answer. In Trino, for example:

SELECT committed_at
FROM lakehouse.sales."fct_orders$snapshots"
ORDER BY committed_at DESC
LIMIT 1;

If the table is fed by a stream, there is a trade-off to settle with the business: committing every few seconds gives fresher answers but creates many small files, which then need regular compaction and snapshot expiry. A commit interval of one to five minutes is a common middle ground, but the right number depends on how fresh the questions need to be.

How the agent should use it

We give the agent a small set of tools on top of the layer instead of a SQL connection:

  • list_metrics() and describe_metric(name), so it can find the right definition and explain it;
  • query_metric(metric, group_by, filters, time_range, grain), which the layer turns into SQL using the approved joins, time zone and standing filters;
  • every result returned with the metric definition, the snapshot ID and the data's "as of" time.

When a question cannot be answered from defined metrics, the agent says so and logs the question, rather than improvising SQL. That log becomes the backlog for the next metrics to define. If you do allow free-form SQL for exploration, run it read-only with row limits and label the answer as unverified.

One rule on access: apply row-level rules for the person the agent is acting for, not for the agent's own service account. Otherwise the agent becomes a way around your access controls.

Test it like code

A semantic layer is code, and it breaks like code. We keep a set of golden questions, typically 30 to 50, each with an answer that finance or operations has confirmed for a fixed snapshot. They run automatically on every change to a definition, and on every schema change to the underlying tables. A failing golden question blocks the release, exactly as a failing unit test would.

Where to start

Pick one domain, usually orders and revenue. List the ten numbers people argue about in meetings, the five joins everyone uses, and the time-zone rule. Define those, write the golden questions, and connect one agent or one dashboard to them. Then extend domain by domain, using the log of unanswered questions to decide what comes next.

Sources and further reading