Databricks Metric View compatibility

The pinned rules

Required extras

The YAML body is read; the DDL is not

• A view is named after its file’s stem, because the YAML carries no name. views/orders.yaml is the view orders. The name it is deployed under in Unity Catalog is not read.
• Two files with one stem are refused in configuration, case ignored. They would name one view twice.
• A .sql file is refused by name. The DDL bundles deployment settings (OR REPLACE, TBLPROPERTIES, column aliases, COPY *) with the definition. Dex reads definitions and never deploys, so it does not guess the YAML out of a statement.

What a file is checked against

• YAML 1.2 booleans. A join’s bare on: key stays the text on, where YAML 1.1 would read it as true.
• Duplicate keys. A key written twice in one mapping is refused.

• a missing or empty source;
• neither fields nor measures;
• malformed parameters: each needs a name and a data_type, and once one parameter has a default, every later one needs one too;
• malformed materialization: mode: relaxed, a non-empty materialized_views list with a name and a type on each, at most one unaggregated entry with no dimensions or measures, and dimensions or measures on each aggregated one;
• materialization on a view that declares parameters, which Databricks binds per query, so there is no one result to maintain.

• Field or measure. A non-empty expr, and an explicit name that is not empty. Names are unique across fields and measures, case ignored, and the later of two is refused.
• display_name. At most 255 characters.
• synonyms. At most 10 entries of at most 255 characters each.
• format. Checked by type (number, currency, percentage, byte, date, date_time) with that type’s keys:
  • a currency format needs an ISO-4217 currency_code;
  • a date format needs date_format, and a date_time format needs both date_format and time_format (Databricks refuses either without, checked with CREATE VIEW ... WITH METRICS in #1258);
  • decimal_places of type max or exact needs places from 0 to 10;
  • date_format and time_format cannot both be none.
• Measure window.
  • order and range are required.
  • range is current, cumulative, all, or trailing|leading <n> [<unit>] [inclusive|exclusive].
  • semiadditive is first or last.
  • offset is a signed integer or <n> day|month|year[s].
  • The number in a range or an offset may be an integer parameter the view declares, by its bare name (trailing days_back day). A name that is no integer parameter of the view is refused.
• Measure partition. include is a list of field names, and outer_aggregate is one of SUM, AVG, MIN, MAX, COUNT or MEDIAN. A measure has a window or a partition, never both.
• Join. A name that is an identifier and is not source, and a source.
  • It needs on or using. With both, on wins, as in Databricks, and Dex warns.
  • cardinality is many_to_one (the default) or one_to_many, and every join below a join keeps that join’s cardinality.
  • rely takes at_most_one_match: true|false.
  • Sibling joins have distinct names. A refused join takes the joins below it with it.
• Expressions. These are read with the Databricks SQL dialect:
  • every MEASURE(name) names a measure of the view defined before it, which also rules out a cycle. A refused measure retracts the measures that read it, to a fixed point;
  • no field reads a one_to_many join. Only a measure can aggregate over one;
  • a nested join is read through its whole path: orders.customer.c_name, never customer.c_name.

• An unknown key, at any level, rely included. Databricks refuses it on create, but the rest of the definition is still readable. Authoring refuses it in a file the plan writes (see Authoring).
• on beside using.
• fields and its older spelling dimensions both written. Databricks creates the view from dimensions and drops fields, and so does the Ossie converter. Dex reads dimensions too and names what it dropped. Authoring refuses it in a file the plan writes.
• A field reading a later field’s name, or a filter reading a field’s name. A field reads only the fields before it, and a filter reads source columns only, so Databricks reads such a name as a source column and refuses the view if there is none. Whether there is one is the warehouse’s to say, so Dex warns rather than refuses.
• A field or measure with no name. It is named by the rule Databricks applies on DBR 19 and later. A plain column keeps its final identifier, including inside CAST; any other expression keeps its text with the whitespace removed (SUM(a+b)).
• A wildcard (source.*, <join>.* EXCEPT (...)). Databricks expands it from the warehouse schema when the view is created. Dex does not expand it, and the members it would add are not listed. A wildcard carrying a name or other metadata is refused.
• An expression sqlglot cannot parse. Its column is not resolved.

• Version 0.1. Databricks still creates it; Dex reads 1.1 only.
• An unknown key. Databricks refuses the view; Dex warns while reading, and refuses it in a file a plan writes.
• A duplicate name. Databricks refuses the view; Dex refuses the later member and keeps the rest while reading. A plan that writes a duplicate name is refused, since any error in the set refuses the plan.

References and lineage

• In a field or measure, source.<col> or a bare <col> reads the view’s source. <join>.<col> reads a join, and a nested join is read through its whole path (customer.nation.n_name).
• A bare name that matches an earlier field is that field, not a source column of the same name. A field reads only the fields before it.
• In a join’s on, source. is the view’s source, the join’s own name (or a bare column) is the joined table, and a nested join names its parent by the parent’s name.

• A table name is the relation, read with the active connector’s folding. A name that is not fully qualified (Databricks completes it from the session’s catalog and schema) claims no relation, with a note. The PII gate still matches it by suffix.
• A query is a relation only as SELECT <columns or *> FROM <one table> with no other clause, and its columns are traced through the projection.
• A filtered single-table query keeps its table for the PII gate alone, with a note, because its rows are not the table’s.
• A joined, grouped or otherwise shaped query has no relation.

How a Metric View maps onto the catalog

• a view’s label, model_ref, agg_time_dimension and primary_entity;
• a metric’s time_axis and queryable_granularities;
• a dimension’s queryable_granularities;
• a measure’s agg_time_dimension;
• every entity field.

What the declarations reach in explore

• Joins. Each join with key pairs is a declared edge at confidence 1.0, declared_by: databricks relationship '<view>.<join>'. --verify measures it.
• rely keys. Each rely.at_most_one_match key is a declared unique key on the joined relation. Profiling that finds duplicates says so against “the Databricks Metric View layer”. Databricks does not check rely, and a wrong one returns wrong numbers, which is why Dex measures it rather than trusting it.
• Exposure. explore map --use-project marks each relation a view sits on with that view under semantic_models.

query and values are not available yet

• explore semantic query refuses by name.
• explore semantic values resolves the token and runs the values PII gate first, so a personal field is refused as PII before it is refused for want of a runtime.

What maintain makes of the layer

• Each view is one semantic model. Its fields and aggregating measures on the source are mapped to their columns.
• Each joined table that is not another view’s source is one semantic model of its own (view.join.path). It holds the fields that read it and its rely key.
• Each measure is one metric. Each join with key pairs is one relationship oriented child to parent, so its arity is many_to_one.

• maintain semantic reports definition drift: a view, measure or join added, removed or changed. It also reports a column a field reads that the warehouse no longer has.
• maintain grain measures each rely key (declared_grain_not_unique) and each join’s declared arity. A contradicted rely key is worded as the view’s claim (rely.at_most_one_match on a join to the table), not as the project’s grain: Databricks trusts the flag and returns wrong numbers when it is false.
• maintain semantic reports a view re-saved in a spec version Dex does not read as semantic_format_changed for that file, naming it and both versions: the fingerprint records each file’s declared version under definition_versions, so the other views are still compared by content.

• a view or a join whose source is gone: a vanished table is as often a rename;
• a filter reading a dropped column: the filter is the view’s population;
• a query source reading a dropped column: no member removal repairs it;
• a removal a materialization lists, or one that would leave the view no field and no measure;
• a file already holding a key Databricks refuses.

The Ossie bridge

dex semantic databricks plan "views from the sales model" \
    --from-ossie semantic/sales.ossie.yaml --fact orders --into views/sales.yaml

dex semantic ossie plan "an Ossie document from the orders view" \
    --from-metric-view views/orders.yaml --name sales --dataset-metrics

Two layers: the port, and what dex adds

What is named rather than dropped

• on export, datatype, dimension.is_time, the members of an object ai_context other than synonyms, a dataset’s description and ai_context, the model’s ai_context, and foreign-vendor custom_extensions;
• primary_key and unique_keys, which set rely.at_most_one_match where they cover a join’s columns and are otherwise not carried;
• a metric naming a dataset reached by two join paths (a diamond), which is dropped;
• a complex field on a fanned-out join, which is dropped;
• a field on a one_to_many branch, which cannot be a dimension and is dropped;
• synonyms beyond ten, truncated;
• a column that reads one already dropped, which is dropped too;
• on import, a wildcard field or measure, and a view writing both dimensions and fields.

Stored or proposed

• A path in the configured files is validated by the target layer as a hand-written --edits-file would be, with the Metric View write rules for a view, and stored as a plan. define refuses an existing view or model; update classifies it.
• Any other path stores no plan. The document is validated the same way and returned under the content of its own data.bridge.conversions entry. With no configured output in the batch, plan_id is null. next names the dex config set that adopts the path and the command to run again (update where every output of the rerun already exists, define where none does, plan for a mix). update refuses such a path.

The stash

Known differences from the converter

• The YAML reader. Both directions read YAML 1.2, as the converter does, so on, off, yes and no stay text. Dex’s own Ossie reader reads YAML 1.1. A hand-written Ossie document with a bare on value therefore reads as text in the bridge and as true in the layer.
• YAML timestamps. An unquoted timestamp stays a timestamp on the way through. The converter would write it as epoch milliseconds.
• Recursive aliases. A recursive YAML alias is refused. The converter would recurse into it without end.
• What is not ported. The converter’s serialization failures (a Java bean Jackson cannot write) have no Python counterpart, since a parsed document is always plain data.

Authoring

• The write surface is exactly semantic.databricks.files. A path off it, a .sql file, another suffix and a stem another file already names are refused by name, the first with the dex config set that adopts it.
• Edits are whole files, written byte-for-byte, all or none. A file changed after planning is a conflict. The set apply would leave is validated again.
• The whole prospective set is validated by the rules above. Every error anywhere in the set refuses the plan, and so do two warnings in a file the plan writes: an unknown key, and fields beside dimensions.
• YAML 1.2, never a 1.1 round trip. A key that is not text is refused while reading; a true: key names the on: rewrite a YAML 1.1 writer makes. Dex’s own writers (derivation and annotation) read and write YAML 1.2, so on: is written plain. A file whose YAML aliases expand past 100,000 values, or refer to themselves, is refused too.
• Derivation from a declared dbt mart, and annotation with a declarations payload, follow the mapping in the transform skill’s declarations.md. A Metric View has no metadata key, so the population is carried only as the last sentence of the view’s comment, which the catalog withholds, and the NULL rule, additivity, assumptions and grain are named as not carried. A population is never written as a filter.