Skip to content

cube-export.md

Latest commit

 

History

History
661 lines (560 loc) · 45 KB

cube-export.md

File metadata and controls

661 lines (560 loc) · 45 KB
Raw

Cube export: the cube-model generator

cubeModel() — write the reporting vocabulary of a model as Cube data-model files: cubes, joins, dimensions, measures, segments, and a rollup for each served report.

Status: a TypeScript reference helper (FR-044 Plan 4; ADR-0034 Amendment 3). It is listed by meta gen --list, copied into your repo with meta eject cube-model, and checked by meta verify --codegen. It is not core: what MetaObjects guarantees is the vocabulary and the loader's checks on it, and a generator that writes files into your repo is a helper you can copy and own. It adds no vocabulary (metamodelVersion stays 1.1) and has no runtime: the files it writes import nothing.

What it does. For each concrete entity that has a table and declares a dimension, measure or segment, it writes model/cubes/<Entity>.yml. The file holds one cube: the table, the joins to the cubes its references reach, and its dimensions, measures and segments. For each served report it adds a rollup pre-aggregation to the report's @from cube, and for a served report that declares @spine it writes a Cube view, model/views/<Report>.yml, instead. Cube reads these files from its own project.

What it does not do. It never talks to Cube or to a database. There is no dbt MetricFlow exporter (it waits for the first adopter who asks, spec decision D5). No other port has an exporter (spec R6): the files are language-neutral YAML, and the Node meta CLI writes them.

Entirely opt-in. meta init wires no generator. A project that does not configure cube-model gets no file. A model that declares no dimension, measure or segment gets no file even when the generator is configured.

What it writes

Paths are relative to the generator's target outDir. Every file starts with # @generated by @metaobjectsdev/codegen-ts — cube-model.

The model declares The run writes
a concrete entity with a writable source.rdb table that declares, or inherits through extends, at least one dimension, measure or segment model/cubes/<Entity>.yml, the entity's cube. Its rollups and report scope segments are in the same file.
an entity that a dimension's @via reaches and that is not a cube already a join-target cube, model/cubes/<Entity>.yml, with public: false: its primary key and the members the reaching dimensions read
two or more to-one hops from one cube onto the same entity, or a hop onto the cube's own entity an alias cube for each hop, model/cubes/<Cube>_<hop>.yml, with public: false
a TPH subtype that declares reporting vocabulary a cube whose sql is a SELECT over the base table with the subtype's discriminator predicate, in place of sql_table
an abstract entity nothing. Its members land on each concrete entity that inherits them.
an entity with reporting vocabulary and no table nothing (it is inert, like any object with no source)
a served report no file. In the cube of its @from entity: a rollup, unless the report holds a relative date anywhere (then it has none, see Reports), and a scope segment when it has a @filter.
a served report that declares @spine a Cube view, model/views/<Report>.yml; its facts cube, model/cubes/<Report>Facts.yml, public: false; for a spine of more than one hop, a chain cube model/cubes/<Report>_<hop>.yml, public: false, for each entity between; and one one_to_many join on the spine cube. No rollup and no scope segment (see Reports with @spine).
a report with no source.*, an abstract report, or one whose read source is not @kind: view nothing
none of the reporting vocabulary no file

A report is served when it is concrete and its read source has @kind: view, the rule every port's route generators use (How a report is served).

Wiring it

Cube reads its model from model/cubes/ in its own project, so give the generator a target that points there:

// metaobjects.config.ts
import { defineConfig } from "@metaobjectsdev/cli";
// Copied in by `meta eject cube-model`: yours to edit (ADR-0034).
import { cubeModel } from "./codegen/generators/cube-model.js";

export default defineConfig({
  outDir: "src/generated",
  dialect: "postgres",
  targets: { cube: { outDir: "cube" } },   // the Cube project: files land in cube/model/cubes/
  generators: [/* ... */ cubeModel({ target: "cube" })],
});

meta eject cube-model copies one file, codegen/generators/cube-model.ts, and prints the import and the entry to wire. It copies nothing into codegen/runtime/, because the output imports nothing. What you own in the copy is which entities get a cube, where the files land and the YAML call. The mapping (buildCubeModel, which raises every refusal below) and the YAML writer (renderCubeYaml) stay in the package, both exported from @metaobjectsdev/codegen-ts. To run the package's own copy, import cubeModel from @metaobjectsdev/codegen-ts instead.

Option Meaning
dialect "postgres" or "mysql", the database Cube reads. Default: the config's dialect.
filter A predicate over entities, ANDed with the gate in the table above. It is the universe of the build: an entity it excludes gets no cube of its own, and its dimensions add no member to another cube. If another cube's @via reaches an excluded entity, it is still written as a join-target cube, without its own measures, segments or rollups, unless every hop onto it goes through an alias cube.
target The named output target the files are written under.

The config's columnNamingStrategy (default snake_case) names the columns in the SQL, as it does for the report views.

Dialects. The exporter writes SQL for Postgres and MySQL only, the two dialects it quotes and casts for. A config whose dialect is sqlite or d1 raises ERR_CUBE_UNSUPPORTED_DIALECT, and the message says to pass the dialect of the database Cube reads, as in cubeModel({ dialect: "postgres" }). The error is raised only when the run would write a cube file. A project with no reporting vocabulary, or a run that selects no cube, writes nothing and raises nothing under any dialect.

A worked example

This is the Purchase and Program model from reporting, with two served reports. RevenueByProgram groups by program title and month. RecentRevenue has a relative date in its @filter.

{ "metadata.root": {
    "package": "acme::shop",
    "children": [
      { "object.entity": {
          "name": "Program",
          "children": [
            { "source.rdb": { "@table": "programs" } },
            { "field.long":   { "name": "id" } },
            { "field.string": { "name": "title" } },
            { "identity.primary": { "name": "id", "@fields": ["id"] } }
          ]
      }},
      { "object.entity": {
          "name": "Purchase",
          "children": [
            { "source.rdb": { "@table": "purchases" } },
            { "field.long":      { "name": "id" } },
            { "field.long":      { "name": "programId" } },
            { "field.string":    { "name": "customerEmail" } },
            { "field.currency":  { "name": "amountCents" } },
            { "field.string":    { "name": "status" } },
            { "field.timestamp": { "name": "purchasedAt" } },
            { "identity.primary": { "name": "id", "@fields": ["id"] } },
            { "identity.reference": { "name": "fkProgram", "@fields": ["programId"],
                                      "@references": "Program" } },
            { "relationship.association": { "name": "program", "@objectRef": "Program",
                                            "@cardinality": "one" } },
            { "segment.filter":      { "name": "active", "@filter": { "status": "active" } } },
            { "dimension.attribute": { "name": "programTitle",
                                       "@of": "Program.title", "@via": "Purchase.program" } },
            { "dimension.time":      { "name": "purchasedAt", "@of": "Purchase.purchasedAt",
                                       "@grains": ["day", "month"] } },
            { "measure.aggregate":   { "name": "purchases", "@agg": "count", "@of": "Purchase.id",
                                       "@segment": "active" } },
            { "measure.aggregate":   { "name": "revenue", "@agg": "sum", "@of": "Purchase.amountCents",
                                       "@segment": "active" } }
          ]
      }},
      { "object.report": {
          "name": "RevenueByProgram",
          "@from": "Purchase",
          "@dimensions": ["programTitle", "purchasedAt:month"],
          "@measures": ["purchases", "revenue"],
          "children": [
            { "source.rdb": { "@kind": "view", "@view": "v_revenue_by_program" } }
          ]
      }},
      { "object.report": {
          "name": "RecentRevenue",
          "@from": "Purchase",
          "@measures": ["purchases", "revenue"],
          "@filter": { "purchasedAt": { "gte": { "now": "-P30D" } } },
          "children": [
            { "source.rdb": { "@kind": "view", "@view": "v_recent_revenue" } }
          ]
      }}
    ]
}}

With dialect: "postgres" and the default naming strategy, the generator writes two files. model/cubes/Purchase.yml:

# @generated by @metaobjectsdev/codegen-ts — cube-model
cubes:
  - name: Purchase
    sql_table: '"purchases"'
    joins:
      - name: Program
        relationship: many_to_one
        sql: '{CUBE}."program_id" = {Program}."id"'
    dimensions:
      - name: id
        sql: '{CUBE}."id"'
        type: number
        primary_key: true
      - name: programTitle
        sql: '{Program.title}'
        type: string
      - name: purchasedAt
        sql: '{CUBE}."purchased_at"'
        type: time
        meta:
          grains: [day, month]
    measures:
      - name: purchases
        sql: '{CUBE}."id"'
        type: count
        filters:
          - sql: '{CUBE}."status" = ''active'''
      - name: revenue
        sql: '{CUBE}."amount_cents"'
        type: sum
        filters:
          - sql: '{CUBE}."status" = ''active'''
    segments:
      - name: active
        sql: '{CUBE}."status" = ''active'''
      - name: recentRevenueScope
        sql: '{CUBE}."purchased_at" >= (now() - INTERVAL ''P30D'')'
    pre_aggregations:
      - name: RevenueByProgram
        type: rollup
        measures: [CUBE.purchases, CUBE.revenue]
        dimensions: [CUBE.programTitle]
        time_dimension: CUBE.purchasedAt
        granularity: month

and model/cubes/Program.yml, which exists only because programTitle reads Program.title:

# @generated by @metaobjectsdev/codegen-ts — cube-model
cubes:
  - name: Program
    sql_table: '"programs"'
    public: false
    dimensions:
      - name: id
        sql: '{CUBE}."id"'
        type: number
        primary_key: true
      - name: title
        sql: '{CUBE}."title"'
        type: string
        public: false

RevenueByProgram became a rollup. RecentRevenue became the segment recentRevenueScope and no rollup, because its filter holds a relative date (see Reports).

How the model maps

Each rule below is pinned by a golden case in fixtures/cube-model/, named in that directory's README.

Cubes and primary keys

A cube is named after its entity, with no package. Its sql_table is the quoted table, with the schema in front when source.rdb declares @schema ("sales"."programs"). It carries the entity's title and description when it has them; notes is never written.

Each field of the entity's identity.primary becomes a dimension named after the field, with primary_key: true (composite key: one dimension per field). Cube makes a primary-key dimension non-public by default, so no public key is written.

A declared dimension without @via that is named after a key field and reads that field is that key dimension, not a second one: the cube gets one dimension, with primary_key: true, public: true (the declaration says the key is meant to be queried), and the declared dimension's title, description and meta.grains. A report lists it like any dimension. A dimension named after a key field that reads another field, or that has a @via, is ERR_CUBE_MEMBER_COLLISION.

A TPH subtype's cube has no sql_table. Its sql selects from the base table and applies the subtype's discriminator, such as SELECT * FROM "auths" WHERE "type" = 'Bridge'.

Dimensions

The @of field's subtype decides the Cube type and the sql, as it decides the column type of a report view.

@of field Cube type sql
string, enum (string-backed), uuid, time (the field.time subtype), uri, inet string the column
enum with @intValueMap string CASE <col> WHEN 1 THEN 'LOW' … END, so the value is the member symbol, as every port's read returns it
int, long, double, float, decimal, currency number the column
boolean boolean the column
timestamp, with or without @localTime time the column
date time CAST(<col> AS TIMESTAMP); on MySQL CAST(<col> AS DATETIME), because MySQL's CAST has no TIMESTAMP target
a field with isArray, a field.object, a field.map, or a field with @objectRef none ERR_CUBE_UNMAPPABLE_DIMENSION: Cube has no array or JSON dimension type

<col> is {CUBE}."<physical column>", the owning cube's column, quoted unconditionally.

A dimension.time carries its @grains as meta: { grains: [...] }, in declared order. Cube offers every granularity on a time dimension and cannot restrict them, so the grains are carried and not enforced.

A dimension with @via reads a member of the cube its last hop joins, such as sql: '{Program.title}'. It never reads that cube's column: a dimension whose sql is {Program}."title" makes Cube fail the whole model. The member is a declared dimension of that cube (attribute or time) with no @via over the same field when there is one, or the cube's primary-key dimension for a key field. Otherwise the exporter adds a dimension named after the field, public: false, to that cube. A multi-hop @via (Week.fkProgram.fkOrg) joins each hop's cube, and the dimension reads the far cube ({Org.name}); Cube follows the joins.

Joins

Joins exist between cubes only. The exporter never writes a cube so that a reference has somewhere to go: an identity.reference onto an entity that is not a cube makes no join.

  • A to-one identity.reference the cube holds, onto a cube: a join with relationship: many_to_one and sql: '{CUBE}."program_id" = {Program}."id"', one per reference, in declaration order. A composite reference joins every column pair, ANDed in the order of the reference's @fields. The key side is the reference's @references fields, or else the referenced entity's identity.primary. A reference whose @fields and key differ in count is ERR_CUBE_UNMAPPABLE_JOIN, and so is a hop the view's own walk refuses (an ambiguous to-one relationship).
  • A to-one relationship.* whose reference the other entity holds, onto a cube: a join on this cube with relationship: one_to_one and sql: '{CUBE}."id" = {Profile}."program_id"'.
  • Two or more to-one hops onto one entity, or a hop onto the cube's own entity. Cube allows one join per target cube and none onto the cube itself. Each such hop gets an alias cube named <Cube>_<hop> (Match_homeRef, Match_awayRef, Node_fkParent), and the join and the dimensions use it. It is never the plain target for one hop and an alias for another, which would depend on declaration order. An alias cube is a standalone, minimal cube over the target's table: public: false, the primary key, the members the dimensions that reach it read, and the onward joins of any multi-hop path that continues through it. It does not extends the target, because extends copies every measure and rollup of the parent and each alias would rebuild them. An entity reached only through aliases gets no join-target cube.
  • A cube in a join whose entity has no identity.primary is ERR_CUBE_NO_PRIMARY_KEY: Cube needs a primary key on both sides of a join.
  • A multi-hop @via that Cube could join by more than one route is ERR_CUBE_AMBIGUOUS_PATH, naming both routes. See Known limits.

Measures

x is the @of column on {CUBE}. A measure's condition is its @segment's filter and its @filter, ANDed, written as one filters entry.

Measure Cube
count type: count, sql: x
count with @distinct type: count_distinct, sql: x
count with @distinct over a tuple x1, x2 Postgres: type: count_distinct, sql: 'ROW(x1, x2)', with one filters entry x1 IS NOT NULL AND x2 IS NOT NULL, so a tuple with a null component is not counted, as in the view. MySQL: the view's own COUNT(DISTINCT x1, x2) as a type: number measure (a condition c makes it COUNT(DISTINCT CASE WHEN c THEN x1 END, x2)); MySQL's multi-argument form skips a tuple with a null component and compares by the columns' collation, which a JSON_ARRAY(x1, x2) key does not: on mysql:8.4 under the default utf8mb4_0900_ai_ci it counted 'abc' and 'ABC' as two tuples where the view counts one
sum, avg, min, max type: sum, avg, min, max, sql: x
any of the above with a condition c the same, with one filters entry c. For a Postgres tuple, c is ANDed after the not-null terms in that one entry.
measure.ratio type: number. Postgres: CAST({num} AS NUMERIC) / NULLIF({den}, 0). MySQL: {num} / NULLIF({den}, 0).
a measure.aggregate with @default: n two members. <m>Raw is the aggregate as the rows above write it (its type, sql and filters), with public: false. <m> is type: number, sql: 'COALESCE({<m>Raw}, n)', and carries the measure's title and description.
a measure.ratio with @default: n type: number, the quotient above inside COALESCE(…, n)

In a ratio, {num} and {den} are references to the two operand measures, so each operand is its full Cube expression, condition included, and an operand need not be listed in any report. An operand that declares its own @default is its COALESCE member, so its default reaches the ratio whether or not the ratio declares one, as in the view (revenue with @default: 0 over buyers reads 0, not null, for a group with buyers and no revenue).

@default is the value the view reads in place of null (COALESCE(E, n)), and Cube reads the same: a defaulted measure is never null, on an empty group, a group whose rows the measure's condition filters out, or a ratio whose denominator is zero. Cube has no nullability to declare for a measure, so nothing else is written for it. A rollup lists <m>, never <m>Raw. As a number measure, <m> is served from a rollup only when the query's dimensions are the rollup's own, which is the query a report makes.

No measure sets format: Cube's named formats are display hints and the model carries none.

Segments and relative dates

A segment.filter becomes a segment whose sql is the filter, written by the function that writes the WHERE of a report view (projection/report-sql.ts in codegen-ts). The exporter has no second filter translator, so a filter means the same thing in the view and in Cube.

A relative date { "now": "-P7D" } becomes the view's own SQL, on the Postgres spelling: (now() - INTERVAL 'P7D') for an instant, ((now() AT TIME ZONE 'UTC') - INTERVAL 'P7D') for a naive (@localTime) timestamp, and CAST(((now() AT TIME ZONE 'UTC') - INTERVAL 'P7D') AS DATE) for a date. The exporter does not map it to a Cube query dateRange: segments and measure filters are SQL, so a relative filter has to be SQL there anyway, and the view's own SQL is exact.

Reports: rollups and scope segments

A served report writes into the cube of its @from entity, a TPH subtype's cube included. The rollup is named after the report.

Report part In the cube
attribute dimensions dimensions: [CUBE.d, …] in listed order. A @via dimension is a member of the @from cube, so it is listed the same way.
one time dimension time_dimension: CUBE.d and granularity: <grain>
two or more time dimensions, including one dimension at two grains time_dimensions:, a block list with one { dimension, granularity } entry for each, in listed order
measures measures: [CUBE.m, …] in listed order
@segment segments: [CUBE.<segment>]
@filter a public segment <report>Scope on the cube, where <report> is the report's name with a lower-case first letter (RecentRevenue gives recentRevenueScope), whose sql is the filter. It is listed after @segment in the rollup's segments.
no dimensions a rollup of measures only (the totals row)

A report with a relative date gets no rollup. That is so when its @filter, its @segment's filter, or the condition of any measure it lists (a ratio's operands included) holds one. Cube builds a rollup when it refreshes it, so the rollup's "now" would be the build's. The view's is the query's. The scope segment is written whenever the report has a @filter, relative date or not. So a report whose relative date is only in its @segment or a measure's condition, and that has no @filter, adds nothing to the cube: that segment and measure are already members of it.

The rollup and the scope segment are all the exporter writes for a report. It writes neither a refresh_key nor a partition: Cube's defaults apply, and partitioning is a deployment choice.

Rollups are written coarsest first. Cube answers a query from the first rollup, in definition order, that can serve it, and a finer rollup can serve a coarser query whose measures are additive. On Cube 1.7.43, in report order, the query of a totals report (no dimensions) was answered from the rollup of a report grouped by two dimensions. The rows were right either way, but the exporter orders each cube's rollups so that a report's query reaches a rollup built for it before any strictly finer one. The keys, in turn: fewer grouping columns (attribute plus time dimensions); the coarser time grain, comparing time dimensions in listed order; fewer measures; report order.

The names are one namespace. A rollup and a <report>Scope segment share the cube's member namespace with its dimensions, measures and segments, because Cube reports a pre-aggregation named like a member as defined twice. A clash is ERR_CUBE_MEMBER_COLLISION.

A report with no cube to hold it is refused. A served report whose @from entity is abstract or has no table is ERR_CUBE_UNMAPPABLE_REPORT. The check runs when the run selects at least one cube.

In development mode Cube names the table of a rollup dev_pre_aggregations.<cube>__<rollup>, both parts in snake case, joined by two underscores (week__program_minutes).

Reports with @spine: a Cube view

A served report that declares @spine reads its rows from the spine entity: one row for each distinct dimension tuple among that entity's rows, the ones no fact refers to included, where a count reads 0 and any other measure null or its @default (reporting). A rollup cannot hold those rows. Cube builds a rollup declared on the spine cube from the fact cube, so the empty rows are missing, and once it exists it changes the answer of a matching query (executed on Cube 1.7.43: 3 rows where the view has 7). So the report is a Cube view, with no rollup and no scope segment:

Part Written
the view model/views/<Report>.yml, named after the report, with the report's title and description. Views and cubes share Cube's one namespace.
the spine cube the cube each listed dimension reaches after the spine's hops: the spine entity's own cube, its join-target cube, or the alias cube of the last hop. It gets one one_to_many join onto the report's facts cube (or onto its first chain cube), on the columns of the spine hop's reference.
<Report>Facts a standalone cube, public: false, whose sql is SELECT * FROM <the @from table> <alias> with the report's @segment filter and then its @filter, ANDed, as its WHERE (none when the report has neither; for a TPH subtype, the base table, its discriminator first). The alias is the one the report view gives @from, so the condition reads as the view's join condition does. It holds @from's primary key and @from's own definitions of the measures the report lists, the operands of a listed ratio and the <m>Raw of a defaulted measure, in @from's order; what the report does not list is public: false.
<Report>_<hop> for a spine of more than one hop, a standalone chain cube, public: false, for each entity between @from and the spine entity: its table, its primary key and one one_to_many join onward, towards the facts. <hop> is the hop that reaches the entity from @from's side, so Session.fkWeek.fkProgram gives ProgramSessions_fkWeek.
the view's cubes first join_path: <spine cube> (and, for a dimension past the spine, the join path its own joins take from there), including the member each listed dimension reads, under the dimension's name (id as programKey) and with the dimension's title, description and meta.grains; last, join_path: <spine cube>[.<chain cubes>].<Report>Facts, including the listed measures.

The report's scope sits in the facts cube's own sql, so it scopes the facts inside the join and a spine row whose facts are all filtered out keeps its row, as the view's join condition does. A Cube segment would be a WHERE on the whole query, which turns the outer join back into an inner one. No join is added to @from's own cube or to any cube of an entity between, so every ad-hoc answer of the ordinary cubes is unchanged: on 1.7.43, { "measures": ["Week.weeks"], "dimensions": ["Program.id"] } is still rooted at Week (3 rows), with Program's joins onto the facts cubes in place. The measure definitions are copied into the facts cube, which is the cost of keeping the ordinary cubes as they are.

A TPH subtype is exported in either place. As the spine entity, its own cube, whose sql is the base table scoped by the subtype's discriminator, is the spine cube, so the view's rows are that subtype's rows only. As @from, the facts cube's sql puts the discriminator before the report's scope. Both are correct by construction, and the report view lowering refuses both reports (a TPH subtype has no table of its own), so there is no view to compare them with.

A facts or chain cube is private plumbing: it carries no title or description, while the view carries the report's.

Executed on Cube 1.7.43 before this was built, and checked by the cube lane since: a view includes a private primary key and a public: false member under an alias, and one member twice under two aliases (two dimensions over one field and path); the include-level title, description and meta reach /v1/meta; the roster view returns every program, with weeks 0, its plain sum null and its defaulted measures 0 for the programs with no weeks; the scoped facts cube keeps the programs whose weeks are all short; and a two-hop chain returns the view lowering's rows. A view is answered from the tables.

Names, quoting and escaping

Rule Behaviour
cube name the entity's name. Two entities of one name in two packages, or an alias, facts or chain cube or a @spine report's view named like a cube, is ERR_CUBE_NAME_COLLISION, naming both (the message says "cube or view" when one is a view): Cube's cubes and views share one namespace. Rename one, or narrow the generator's filter to leave one out; the filter helps only when no @via reaches the entity it leaves out, since an entity a @via reaches is still written as a join-target cube, unless every hop onto it goes through an alias cube.
member name the dimension, measure or segment name as written, so a report field and its Cube member share a name
a name Cube refuses Cube names start with a letter, hold only letters, digits and _, and are not a Python keyword (from, class, in, is, not, and, or, if, else, for, while, with, as, def, return, yield, import, pass, global, nonlocal, lambda, del, assert, break, continue, try, except, finally, raise, async, await, True, False, None, elif). That is ERR_CUBE_INVALID_NAME, naming the node. The exporter never renames: the name is the report field's.
members the exporter adds primary-key dimensions, reached-column dimensions, the <m>Raw measure of a defaulted measure, <report>Scope segments, rollups. A name that collides with another member of the cube is ERR_CUBE_MEMBER_COLLISION, naming both. The one exception is a declared dimension over a key field under its own name, which is that key dimension (see Cubes and primary keys).
identifiers every table, schema and column is quoted: "…" on Postgres, backticks on MySQL
string literals SQL quoting first (' doubled; MySQL also doubles \). Cube compiles every sql as a template literal, where a backslash is an escape (\b a backspace, \_ a plain _, a trailing \ swallows the closing quote) and {x} a member reference. So every \ is then doubled, and after that { becomes \{ and } becomes \} (in that order, so a backslash before a brace stays a backslash). Then, when the literal holds a Jinja opener ({{, {% or {#), it is wrapped in {% raw %}…{% endraw %} for Jinja, because a backslash does not stop Jinja. A wrapped literal holding endraw is ERR_CUBE_UNESCAPABLE_LITERAL; one that is not wrapped is never inside a raw block, so endraw there is plain text. A column name written with @column gets the same treatment. The cube lane reads each literal of the escaping case back from Cube's /v1/sql, where it is the view's own SQL.
free text title and description. Cube reads them as templates too: {x} is a member reference, ${x} an interpolation and a backslash an escape. Each \ is doubled, each brace escaped, the text raw-wrapped when the original holds {{, {% or {# (the same rule as a SQL literal), and the result is written as a JSON string. Raw-wrapped text holding endraw is ERR_CUBE_UNESCAPABLE_LITERAL.
YAML scalars A sql, sql_table or filter value is single-quoted (' doubled), or written as a JSON double-quoted string when it holds a line break, a control character, U+007F to U+009F, U+2028, U+2029 or U+FEFF. Names, types and CUBE.<member> references are plain, except a name a YAML reader would take for a boolean or null (true, false, null, yes, no, on, off, y, n, in any case), which is single-quoted. The emitter is hand-written, with no YAML dependency, so the bytes are the same on every run: two-space indent, LF line endings, one trailing newline, no trailing spaces.

What the exporter refuses

Anything this mapping cannot express is a generation error that names the node and says what to change. Nothing is dropped silently. The ERR_CUBE_* codes belong to this generator: they are printed in its messages and are not loader codes.

Code Raised when
ERR_CUBE_UNMAPPABLE_DIMENSION a dimension reads an array, object or map field, or a field with @objectRef; or its @via crosses a to-many hop, comes back to an entity already on the path (a self-referencing hop is the exception), reaches an entity with no table, or passes through one cube twice
ERR_CUBE_UNMAPPABLE_JOIN a reference's @fields do not pair with the key it references, or the view's walk refuses a hop
ERR_CUBE_UNMAPPABLE_REPORT a served report's @from has no cube to hold its rollup
ERR_CUBE_AMBIGUOUS_PATH a multi-hop @via reads a cube that the cube graph reaches by more than one path
ERR_CUBE_NO_PRIMARY_KEY a cube in a join has no identity.primary
ERR_CUBE_INVALID_NAME a cube or member name Cube cannot take
ERR_CUBE_MEMBER_COLLISION two members of one cube would have one name
ERR_CUBE_NAME_COLLISION two cubes, or a cube and a view, would have one name
ERR_CUBE_UNESCAPABLE_LITERAL a literal, an identifier or free text that is raw-wrapped (it holds a Jinja opener) holds endraw
ERR_CUBE_UNSUPPORTED_DIALECT the dialect is neither postgres nor mysql and the run would write a cube

Which files a run writes

What a cube holds never depends on the run. The build covers the whole loaded model, narrowed only by the generator's own filter, which is fixed config. So meta gen Week and a full run write the same bytes for Week.yml.

The run's selection decides which files are written. That selection is meta gen <Entity>, or the scope of the project's collection. The run writes the selected entities' own cubes and every cube they reach through joins, transitively. It writes a reached cube because a selected cube changes it: Week.programTitle adds title to Program, and a Program.yml left out might lack that member. A report is never selected itself; its rollup rides with its @from cube. A @spine report's view is written when every cube its join paths name is written: it reads them and nothing else. A run that selects no entity with reporting vocabulary writes nothing and raises nothing, under any dialect.

scope narrows what is written, not what is built. An error on an entity outside the scope still fails the run. To step around an entity, exclude it with the generator's filter.

Cleanup. Cube compiles the whole model directory, so a stale <Cube>.yml left by a removed or renamed entity, or by a join no longer reached, can break the compile for every cube. The generator opts in to the runner's orphan cleanup for its own files, the direct .yml children of model/cubes/ and model/views/ under its target. The runner removes such a file only when a previous run wrote it, this run did not write it again, and it is byte-identical to what was written. A file you edited is refused and named, never deleted, and a file the generator never wrote is never a candidate. A run that names entities (meta gen <Entity>) skips the cleanup. A project scope is persisted config and not a narrowed run, so after you change it, a full run removes the untouched cube files the new scope no longer selects. meta gen --dry-run reports the pending removals and performs none.

Querying a report in Cube

This is the Cube query that reproduces a served report. The live check builds it from the report.

{ "measures": ["<From>.<m>", "…"],
  "dimensions": ["<From>.<attribute dimension>", "…"],
  "timeDimensions": [{ "dimension": "<From>.<time dimension>", "granularity": "<grain>" }],
  "segments": ["<From>.<@segment>", "<From>.<report>Scope"],
  "timezone": "UTC" }

For RevenueByProgram above:

{ "measures": ["Purchase.purchases", "Purchase.revenue"],
  "dimensions": ["Purchase.programTitle"],
  "timeDimensions": [{ "dimension": "Purchase.purchasedAt", "granularity": "month" }],
  "timezone": "UTC" }

Cube answers it from the report's rollup when the model has one. The query fixes timezone at UTC because the view buckets in UTC (see the differences below). The key <From>.<m> in Cube's rows is the report's field <m>, and <From>.<d>.<grain> is the field <d><Grain> (Purchase.purchasedAt.month is purchasedAtMonth). RecentRevenue has a scope segment and no rollup, so its query names Purchase.recentRevenueScope under segments and Cube reads the table.

A report with @spine is queried through its view, by the report's name, and with no segments, because its scope is inside the view's facts cube:

{ "measures": ["ProgramRoster.weeks", "ProgramRoster.totalMinutesOrZero"],
  "dimensions": ["ProgramRoster.programKey", "ProgramRoster.programTitle"],
  "timezone": "UTC" }

Where a Cube query and the view differ

The numbers are equal on the conformance data. These differences are known, and none shows there.

Difference Why Effect
Joins Cube compiles every join to a LEFT JOIN. The view joins a required belongs-to reference INNER. A fact row whose reference matches no row forms a null group in Cube and is dropped by the view. A foreign key constraint on the column makes such a row impossible.
Query time zone A Cube query may pass timezone. The view is UTC only. With a zone other than UTC, Cube re-buckets instants, which is its feature, and also shifts naive timestamps and dates, which the view buckets as stored.
Grains Cube offers every granularity on a time dimension. @grains is carried as meta.grains, not enforced.
Ad-hoc queries Cube lets a caller pick any members. Nothing here checks the numbers for a combination no report declares.
Rollup freshness Cube refreshes a rollup on its refresh key, and the exporter writes none, so Cube's default applies. A rollup can trail the table until it refreshes. The view is never stale. A report with a relative date gets no rollup for this reason.
Empty scope A report with no dimension whose scope reads no rows returns one row from the view: the count 0 and each measure's @default. Cube answers from the rollup, which is empty, and sums it to null for every measure, the count and the default included. ProgramsEmptyScope is the one canonical report where the rows differ; the live check requires both answers, so the difference stays an executed check.
Several grains A report that lists one time dimension at several grains gets a rollup naming that dimension once per grain. A Cube query names a time dimension once, at one granularity, so Cube 1.7.43 matches no query to that rollup. The rollup is declared and never used; Cube answers from the source table and the rows equal the view's. ProgramsOverTime is the canonical case.
Encodings Cube's REST API sends numbers as strings and time buckets as YYYY-MM-DDTHH:MM:SS.sss. Where a rollup sends 60, the view sends 60.0000000000000000, and where Cube sends 2026-05-01T00:00:00.000, the view sends 2026-05-01. Clients normalize them. The live check does, before it compares rows.

Checking it

meta verify --codegen regenerates the configured output into a temporary directory and compares it with the committed files. The Cube project is a target, so its files are checked like any other generated output: a changed model that was not regenerated, a missing cube file and a stale one are drift. A hand edit to a generated file is not drift, but the gate lists it, and meta verify --codegen --forbid-hand-edits makes it fail.

The mapping corpus is fixtures/cube-model/: 44 cases, each the smallest model for one rule, 34 with the exact tree the generator writes and 10 with the exact error message. Every expected file was written by hand from its rule and then compared with the generator, never copied from it. codegen-ts/test/cube/cube-model-corpus.test.ts runs it. The canonical golden, fixtures/cube-model/canonical/expected/, is the output for the persistence corpus's fitness model, and cube-model-canonical.test.ts fails on any difference. It is TypeScript-only, as the generator is.

The cube lane loads the output into a real Cube. It runs Cube cubejs/cube:v1.7.43 in development mode, with its embedded Cube Store, against a private postgres:16-alpine that holds the persistence-conformance schema and a seed (fixtures/cube-model/canonical/seed.sql). Each run owns a private Docker network and binds one ephemeral port on 127.0.0.1; it never uses the shared Postgres sidecar, and every container and the network are removed on every exit path. It checks six things on Postgres:

  1. The generated canonical model is the reviewed golden.
  2. Cube compiles it and lists every cube and member the files declare.
  3. For each of the canonical reports, the Cube query above, answered from the report's rollup when the model has one (the lane asserts the rollup name in usedPreAggregations), returns the rows of SELECT * FROM <view>, after the normalization the Encodings row above describes. The three @spine reports, ProgramRoster, ProgramLongWeeks and ProgramSessions, are queried through their Cube views, and their 11 rows each include the programs with no weeks (and, for ProgramLongWeeks, the two whose weeks are all short); FitnessTotalsFilled, a report of defaulted measures, is answered from its rollup.
  4. Every case of the mapping corpus that has an expected tree (34 of the 44: 32 Postgres and 2 MySQL) compiles in the same Cube, and each title and description it declares comes back from /v1/meta as declared. Compiling runs no SQL, so the MySQL cases compile against the Postgres data source. Cube returns a member's own title as shortTitle; its title joins the cube's title and the member's. This turns the shapes no live query reaches (alias cubes, a TPH subtype's sql, one-to-one joins, an int-backed enum's CASE, Jinja-escaped text) into a check that Cube accepts them. It does not run a query through them.
  5. For the escaping case, Cube's /v1/sql for a query on each of its segments holds the segment's literal exactly as the report view writes it ('a\b', 'ends\', 'a\{b}', 'a{b}c', '{{x}}', 'it''s' and a line break), and the braced column "co{de}".
  6. For the measure-default case, Cube's /v1/sql for a ratio whose numerator declares @default divides that numerator's COALESCE(sum(…), 0), with and without the ratio's own default, as the view does.

The MySQL path is a second file in the same lane (cube-model-mysql.live.ts). It runs the same Cube over a private MySQL 8.4 holding the report views buildReportViews lowers (fixtures/cube-model/canonical/seed.mysql.sql), and for each served report it checks item 3: the Cube query returns the rows of SELECT * FROM <view>, from the rollup when the model has one. Checks 4, 5 and 6 are Postgres only.

Run it with scripts/ci-local.sh --only cube. The full scripts/ci-local.sh runs it after the integration suite, and --quick and --no-integration drop it. The first run pulls the Cube image, which is about 1 GB. With Docker down, the lane records a SKIP behind a banner, never a pass; under --strict-toolchains it is a failure. In CI it is a cube entry in integration-tests.yml, which runs on release tags and on manual dispatch, not on every push.

Rollups in production

A rollup needs Cube Store. Cube's development mode embeds one, and the live check runs in that mode only. A production deployment runs Cube Store as a separate service you set up and size; the exporter does not deploy one. It writes no refresh key and no partition, so those follow Cube's defaults until you set them. The live check has not run in Cube's production mode, so the table names above are the development-mode form.

Known limits

  • Two @via paths through two different alias cubes onto one far entity are refused. A home team and an away team that each reach a city (Match.homeRef.fkCity and Match.awayRef.fkCity) make the cube graph reach City by two routes, and the exporter refuses the model with ERR_CUBE_AMBIGUOUS_PATH, naming both. The lossless form needs a nested alias for each path. That is more machinery than a rare model is worth yet, so the exporter errors rather than guess.
  • The two MySQL mapping cases are compiled, never executed. The corpus pins their SQL byte for byte and Cube compiles them, but no MySQL database runs them. The MySQL path runs only the served reports. The tuple distinct count's MySQL form was compared with the view's on mysql:8.4 by hand when it was built.
  • Some shapes are compile-checked, not query-checked. Cube accepts the alias cubes, the TPH subtype's sql, one-to-one joins, the int-backed enum's CASE and a two-hop @spine chain (the corpus pass), but no live query of the lane crosses them. The two-hop chain's rows were compared with the view lowering's by hand on 1.7.43 when it was built.
  • A @spine report has no rollup. Its view is answered from the tables. A rollup on the spine cube would lose the zero rows (see Reports with @spine).
  • No sqlite or d1. See Dialects.
  • No extends, no refresh_key, no partitions, no format. The exporter writes cubes, and a Cube view only for a served @spine report.
  • No derived measure. measure.derived is not registered, so there is nothing to map.
  • Rollups in production mode are unverified (see above).

Compatibility

Nothing here changes a model or a file you already have. No vocabulary was registered, and metamodelVersion stays 1.1. The report view lowering and the exporter now share one SQL module, a move that left every view's bytes as they were. No other generator's output changed.

The design, the contract tables and the decisions made while building it are in docs/superpowers/plans/2026-10-09-fr-044-plan-4-cube-exporter.md.