Cross-fact blend

web_sales and store_sales are different fact tables with different row grains. You want web revenue and store revenue side by side, one row per sold date, filtered to a few categories. Joining the two facts row to row would multiply every web row by every store row on the same date. The planner never does that: it aggregates each fact on its own to the common grain and stitches the results together afterwards. This is drill-across, and you get it by listing the two measures in one spec.

The model side

Each fact carries its own sold-date dimension, and both belong to one extended blending group, so a query grouped by Web Sold Date can be answered by store_sales through its own date member. The fragment below is the shape of the model these requests plan against.

# models/web/tbl.web_sales.yml
  - type: dimension
    name: Web Sold Date
    data_type: integer
    extended_blend_group: blendable_fact_dates
    expression:
      sql: ws_sold_date_sk
  - type: measure
    name: Web Net Paid
    data_type: decimal
    expression:
      sql: sum(ws_net_paid)

# models/store/tbl.store_sales.yml
  - type: dimension
    name: Store Sold Date
    data_type: integer
    extended_blend_group: blendable_fact_dates
    expression:
      sql: ss_sold_date_sk
  - type: measure
    name: Net Paid
    data_type: decimal
    expression:
      sql: sum(ss_net_paid)

See Extended blending groups for how formation turns the group into blend paths. A dimension on a shared table (Date on date_dim, Category on item) needs no group: both facts join to it already.

The request

curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_..." \
  -H "Content-Type: application/json" \
  -d '{"spec": {
    "name": "Query One",
    "projections": [
      {"field": "ws_net_paid", "alias": "Web Net Paid"},
      {"field": "ws_sold_date", "alias": "Web Sold Date"},
      {"field": "net_paid", "alias": "Net Paid"}
    ],
    "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]
  }}'
zsql sql --expr "ws_net_paid as Web Net Paid, ws_sold_date as Web Sold Date, net_paid as Net Paid, category in (men, children)"

The shorthand strips quotes from list items, so the third item "books,com" cannot be written on one line. Use the JSON spec when a list value contains a comma. See Filters and top-n.

The SQL

WITH ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (
SELECT
	T0."ss_sold_date_sk" AS "dimed56b67",
	sum(T0."ss_net_paid") AS "msr621f67c"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T0."ss_sold_date_sk"
), ag29130fae5cf6548d6ddb1bd37298b407 AS (
SELECT
	sum(T0."ws_net_paid") AS "msr501e4a8",
	T0."ws_sold_date_sk" AS "dimed56b67"
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
	T0."ws_sold_date_sk"
)
SELECT
	A0.msr501e4a8 AS "Web Net Paid",
	COALESCE(A1.dimed56b67, A0.dimed56b67) AS "Web Sold Date",
	A1.msr621f67c AS "Net Paid"
FROM
	ag29130fae5cf6548d6ddb1bd37298b407 A0
	FULL OUTER JOIN ag7098b0d0901f2eb16d14f9356f0bb2a0 A1
		ON A1.dimed56b67 = A0.dimed56b67

Why the SQL looks like this

  • One aggregation CTE per fact. ag7098… sums store_sales, ag2913… sums web_sales. Each groups by its own sold-date key, which the blend group maps to the same output column (dimed56b67). Each CTE applies the category filter through its own join to item, so the filter is honoured on both sides.
  • Grain safety. Neither CTE sees the other fact. There is no row-level join between web_sales and store_sales, so no measure is inflated.
  • FULL OUTER JOIN on the conformed key. The final SELECT joins the two CTEs on the date key. A date with web sales but no store sales still appears, with Net Paid as NULL, and the other way round.
  • COALESCE on the key. COALESCE(A1.dimed56b67, A0.dimed56b67) picks the date from whichever side has it, so the projected dimension is never NULL just because one fact is missing that date.
  • The join type is a dialect setting. final_pass_measure_join_type is full in the base dialect. Override it per request through db_settings (next section).

Variations

  • A calculation standing on one of the measures. Add {"alias": "Bucket", "calculation": true, "sql": "[Net Paid] / 100", "data_type": "decimal"} to the projections. The CTEs do not change; the final SELECT gains A1.msr8a51bb0 / 100 AS "Bucket". The full request and SQL are the first example on Complex measures.
  • Keep only dates the web fact has. Add "db_settings": {"final_pass_measure_join_type": "left"} to the spec. The final join becomes a LEFT JOIN from the first aggregation CTE; dates present only in store_sales drop out. The CTEs are unchanged.
  • Blend on a shared dimension instead. Replace ws_sold_date with {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]}. Both CTEs then join to date_dim and group by DATE_TRUNC('month', d_date); the final join key is the month. No blend group is needed because Date lives on a table both facts reach.

Next steps