Projections and decorators

A projection is one output column: a field, optionally decorated. Decorators change how the column is computed (truncate a date to months, turn a measure into a running sum or a percent of total) without you writing SQL. This page covers the projection keys and every decorator.

ProjectionSpec

{"field": "ws_net_paid", "alias": "Web Net Paid", "order_by": "desc",
 "decorators": [{"type": "window", "mode": "running", "function": "sum"}]}

Projections keep their listed order. Entries in the separate top-level calculations list follow them. A date projection with no truncate decorator is the raw column; nothing is truncated for you.

Alias derivation

An explicit alias wins. Otherwise the highest-priority decorator lends a label; otherwise the field’s name is used.

Decorator priorities: truncate 0, extract 1, contribute 2, temporalize 3, window 3, customize 10000. The highest wins the alias. Calculations reference other columns by this display alias ([Month(Date)]), so give an explicit alias when the derived one is awkward.

Decorators

A decorator is {"type": "<kind>", ...attributes}. Kinds: truncate, extract, temporalize, contribute, window, customize (alias custom). Anything else: Unknown decorator type: X. Only the attributes listed under each kind may be written; any other key fails with unknown attribute 'x' on the <type> decorator of <owner>.

Kind rules:

• truncate, extract, customize apply to dimensions only: X can only be applied to dimensions. truncate and extract also need a date or datetime field: only date/datetime types can be date truncated.
• window, contribute, temporalize apply to measures only.

Two decorators of the same type on one projection are merged: the attributes of the second extend the first. Different types stack; yoy(month(date)) from the shorthand produces a truncate and a temporalize on the same projection (and then fails lowering, since temporalize is measure-only).

All SQL below is the engine’s output for the TPC-DS example model with the filter category in_list "men ,children, \"books,com\"" present, which is why every statement carries the same WHERE.

truncate

Bucket a date dimension to a grain.

{"type": "truncate", "grain": "month"}

Shorthand: month(date). Grains: raw, millisecond, second, minute, hour, day, week, month, quarter, year. Required: Grain must be set.

{"spec": {"projections": [
  {"field": "ws_net_paid"},
  {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}

SELECT
	sum(T0."ws_net_paid") AS "Web Net Paid",
	DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ws_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	DATE_TRUNC('month', T1."d_date")::DATE

extract

Pull one part out of a date dimension.

{"type": "extract", "extract": "day_of_week"}

Shorthand: dow(date), month_name(date). Parts: minute, hour, day_of_month, day_of_week, day_of_year, week_of_year, month, quarter, year, day_name, month_name, year_month. Required: Extract must be set. The column is grouped like any dimension; the derived alias is the abbreviation from the table above (dow(Date)).

temporalize

Compare a measure to the same measure one period earlier.

{"type": "temporalize", "transform": "month_over_month"}
{"type": "temporalize", "transform": "year_over_year", "percent_change": true}

Shorthand: mom(web net paid), yoy_pct(web net paid). Transforms: year_over_year, quarter_over_quarter, month_over_month, week_over_week, day_over_day. Required: Transform invalid transform type. percent_change (bool) returns the relative change instead of the prior-period value.

The planner builds the prior period by shifting the date in its own CTE, then joins it back at the projected grain. Month over month, with an ordered month projection and a measure filter:

{"spec": {"projections": [
  {"field": "date", "order_by": "asc", "decorators": [{"type": "truncate", "grain": "month"}]},
  {"field": "ws_net_paid", "decorators": [{"type": "temporalize", "transform": "month_over_month"}]}
], "filters": [
  {"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""},
  {"field": "ws_net_paid", "predicate": "greater_than", "value": "100"}
]}}

WITH ag47a9128558e109f9cf0b93c01992e0e7 AS (
SELECT
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval) AS "dim5b35e57",
	sum(T0."ws_net_paid") AS "__msrlm_51898eff8"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ws_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval)
HAVING
	sum(T0."ws_net_paid") > 100
)
SELECT
	A0.dim5b35e57 AS "Month(Date)",
	A0.__msrlm_51898eff8 AS "LM(Web Net Paid)"
FROM
	ag47a9128558e109f9cf0b93c01992e0e7 A0
ORDER BY
	A0.dim5b35e57 asc

Each month’s LM(Web Net Paid) is the previous month’s sum: the CTE adds '1 month'::interval to every sold date so that January’s rows land under February. Because the base measure is not itself projected, the statement is only the shifted side. Project ws_net_paid as well and the plan gains a second CTE for the current period, joined on the month.

contribute

A measure as a share of its total.

{"type": "contribute"}
{"type": "contribute", "partition_refs": ["category"]}

Shorthand: pct(web net paid), share(web net paid, category). Attributes: partition_refs (projected dimension uids that bound the total, or -1 for auto; refs that are not projected are dropped) and ignore_partition_filters (default true). With no partition the total is the whole measure:

{"spec": {"projections": [
  {"field": "ws_net_paid", "decorators": [{"type": "contribute"}]},
  {"field": "category"}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}

WITH ag61495e144c0531efb642c4545df2e6c5 AS (
SELECT
	sum(T0."ws_net_paid") AS "msr9f2f6d5",
	T1."i_category" AS "dim841154e"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
), agbe9574d2ae0d53a3a40c210cf88ff8c4 AS (
SELECT
	sum(T0."ws_net_paid") AS "__msrtotal_51898eff8"
FROM
	web_sales T0
)
SELECT
	A0.msr9f2f6d5 / (A1.__msrtotal_51898eff8*1.00) AS "% Web Net Paid of Total",
	A0.dim841154e AS "Category"
FROM
	ag61495e144c0531efb642c4545df2e6c5 A0
	CROSS JOIN agbe9574d2ae0d53a3a40c210cf88ff8c4 A1

The total CTE has no WHERE: ignore_partition_filters is true, so the share is of all web sales, not of the three filtered categories. The *1.00 forces decimal division.

window

Running and moving aggregates, ranks, lag and lead.

{"type": "window", "mode": "running", "function": "sum"}
{"type": "window", "mode": "moving", "function": "avg", "size": 7}
{"type": "window", "mode": "running", "function": "lag", "offset": 2, "partition_refs": ["date"], "order_by": {"category": "desc"}}

Shorthand: running_sum(web net paid), moving_avg(web net paid, 7), running_sum(web net paid, category).

A moving average of 14 renders as avg(sum(x)) OVER (ORDER BY <date> ROWS BETWEEN 14 PRECEDING AND CURRENT ROW). The lag example, with month and category projected:

{"spec": {"projections": [
  {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]},
  {"field": "category"},
  {"field": "ws_net_paid", "decorators": [
    {"type": "window", "mode": "running", "function": "lag", "offset": 2,
     "partition_refs": ["date"], "order_by": {"category": "desc"}}]}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}

SELECT
	DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
	T2."i_category" AS "Category",
	lag(sum(T0."ws_net_paid"), 2) OVER (PARTITION BY DATE_TRUNC('month', T1."d_date")::DATE ORDER BY T2."i_category" desc) AS "Running Lag(Web Net Paid)"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ws_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	DATE_TRUNC('month', T1."d_date")::DATE,
	T2."i_category"

The window sits over the aggregate in the same SELECT: partition_refs: ["date"] becomes PARTITION BY the truncated date, the order_by map becomes ORDER BY ... desc.

customize

Wrap a dimension’s expression in your own SQL.

{"type": "customize", "custom_sql": "upper(@expression)"}

custom_sql is required, must contain @expression (replaced by the dimension’s column expression) and must not contain avg(, sum(, min(, max( or count(. The derived alias is Custom(Category). Dimensions only. There is no shorthand function for it.

Attribute reference

Every decorator starts from one attribute row; a kind reads the attributes it needs and the rest keep their defaults.

Keys named id, projection_id, prompt, share_full_result_set_with_ai, type, created_at, updated_at, created_by_id, updated_by_id are accepted and ignored.

Next steps

• Filters
• Calculations for anything a decorator cannot express
• Shorthand expressions for the function names behind each decorator