Kater SQL

How Kater measures work

A measure is a named aggregate, such as Revenue or Orders. Kater does not store its value. Each time you use a measure, Kater works it out again from the source rows, for the grouping and the filters of that place. This page shows how, with the 3,000 orders of Kater's sample shop.

One measure
Four contexts, four numbers
The 3,000 orders. Lit squares are the rows that the selected number read.
Each square is one order, from January 2025 at the top to September 2026 at the bottom. Hover a square to see the order.
Step 1

Declare a measure once

You declare a measure with MEASURE in the final SELECT of a model. This is Revenue, exactly as the shop's orders dataset declares it. Select a part to see what it does.

    Most measures are written for you. When you select Sum on Amount in the Aggregations menu, Kater writes MEASURE('sum__amount', KATER__SUM(o.amount)). You write a measure by hand only for a formula that no menu writes.

    The formula must aggregate rows or evaluate other measures. A formula that reads one row's value fails with measure_requires_aggregation_or_evaluation, and its quick fix declares a DIMENSION instead. A window function is never a measure.

    Step 2

    The context decides the number

    A measure's input is a context: a grouping and a set of filters. With no grouping, a measure gives one number. Grouped by region, it gives one number for each region.

    Kater does not look those numbers up. For each result row, it selects the source rows that match the row's group and the filters, and it runs the formula on them. Change the query below, then select a result row to see the orders it read.

    Measure
    Group by
    Filters
    
      
    
      
    
        
    
        
    
        
    
      
    
    
    
  
    
  
    
    
    Now select Revenue. Kater refuses the query, because the grouping does not hold currency to one value. Step 5 explains why.
    
  
    

    



    
  
    Step 3
    Totals come from the rows, not from other totals
    
  
    
    
    Kater always works a total out from the source rows. It never adds up numbers that were already grouped. For a sum, the two ways give the same result. For a distinct count, they do not.
    
    
    These are the shop's customers, grouped by the region of their orders. Most customers ordered in more than one region, so most customers are in more than one row. Select a row, or one of the two totals.
    
  
    
  
    
    
    
      
    
      
    
        
    
        
    
        
    Each square is one of the 240 customers. When you select "Add up the rows", a square shows how many rows count that customer.
    
      
    
    
    
  
    
  
    
    
    This is why a model with measures is a function, not a table. Its grouped rows are a preview. A reader who adds them up gets a wrong number. A reader who asks Kater for the total gets 240, because Kater counts the customers again from the rows.
    
  
    

    



    
  
    Step 4
    A measure built on measures recomputes each input
    
  
    
    
    The shop's Region Mix query declares Revenue per Customer from two other measures. Each EVAL works out its measure in the reader's context. Then the formula divides the two results.
    
  
    
  
    
  
    
    
    So the total of a ratio is never an average of its rows. Here is Revenue per Customer for USD orders, by region. Select a way to get the value for all USD orders.
    
  
    
  
    
    
    
      
    
      
    
        
    The value for all USD orders
    
        
    
        
    
      
    
    
    
  
    

    



    
  
    Step 5
    Rules travel with the measure
    
  
    
    
    Revenue carries a rule: NOT_ADDITIVE_ACROSS(currency). Euros added to dollars give a number with no meaning. So every place that reads Revenue must hold currency to one value in each result row. It can group by currency, or filter currency to one value.
    
    
    The compiler checks the rule in every model and query that reads Revenue, and in every measure built on it. This query groups Revenue by region only. EMEA has orders in euros and in dollars.
    
  
    
  
    
    
    
      
    
        
    
        
    
      
    
      
    
    
    
  
    
  
    
    
    The rule goes with Revenue through every layer of the shop. No layer has to repeat it, and no layer can drop it. A measure built on Revenue gets the rule too: Revenue per Customer with no grouping is refused with the same message.
    
  
    
  
    
  
    
    
    Other tools handle this in other ways. dbt MetricFlow's non_additive_dimension selects one value along a time dimension for you. Kater refuses the query until the reader holds the dimension to one value, so a wrong total never reaches a chart.
    
  
    

    



    
  
    Step 6
    EVAL works out one value in another context
    
  
    
    
    EVAL changes the context of one value. The rows that the query returns stay the same. Its modifiers say what to change. Below, Orders is grouped by region and channel. Select a modifier and see which cells change, then select a cell to see the rows it read.
    
  
    
  
    
    
    
    
    
      
    
        
    
        
    
        
        
      
    
      
    
        
    
        
    
        
    
        
    
        
    In this cell onlyRead by the EVAL onlyBoth
    
      
    
    
    
  
    
  
    
    
    Put two EVALs in a new measure to get a share, a ratio to a benchmark or a comparison. This measure is each region's share of all orders. The first EVAL follows the grouping. The CLEAR() one is always the grand total.
    
  
    
  
    
    
    
      
    
      
    
    
    
  
    
  
    
    
      
      
    • One kind of modifier in each EVAL. Several SETs apply in order. REMOVE with SET, or SET with CLEAR(), is refused.
      • 
      
    • An alias does not rename an EVAL. Two EVALs of Orders in one SELECT are both named count_distinct__order_id, and the compile fails with duplicate_output_field. Give the second value its own name with MEASURE.
      • 
    
    
  
    

    



    
  
    Step 7
    Pass it on, fix a value, or make a new measure
    
  
    
    
    When your model reads a measure, you select what the readers of your model get. The shop's three queries each make a different choice.
    
  
    
  
    
    
    
    
    
  
    

    



    
  
    Step 8
    Read a measure's card
    
  
    
    
    Kater turns each measure into a card: what you can pass in, what it computes, and the rules for using it. You see the same card in the Definitions view, in the CLI and in your agent. Read the card before you use a measure you did not write.
    
  
    
  
    

    



    
  
    Step 9
    If you know another tool
    
  
    
    
    Most ideas here have a name in a tool you know. Select your tool to see the closest Kater form.
    
  
    
  
    
    
    
    
    
    
    
    
    Other tools change their syntax. Check their own docs for the current form.
    
  
    

    



    
  
    Step 10
    Reference
    
  
    The forms on this page, with a value from the shop for each. Here m is Orders, shop.count_distinct__order_id.
    
  
    
  
    What the compiler refuses, in its own words, and the quick fix it offers.