Relationships between metric views

Important

This feature is in Beta.

A relationship between Unity Catalog metric views has a name and a direction, from a source metric view to a target metric view. It records the cardinality (such as MANY TO ONE) and the condition that joins the two metric views. Relationships are metadata only, so they describe how metric views fit together without fixing a join. This allows AI assistants and BI tools to combine them as each analysis requires.

Why use relationships

A metric view typically describes facts or dimensions at a single grain. For example, an orders_mv metric view measures revenue for each order, and a customers_mv metric view describes each customer by segment and region. Many analyses combine metric views like these, such as analyzing order revenue by customer segment.

Because a relationship doesn't prescribe a fixed join, the same metric views can support many different analyses. AI assistants and BI tools read the relationship from Unity Catalog, so they join the metric views the same way instead of inferring a join from column names.

Relationships compared to foreign key constraints

A relationship is similar to a foreign key constraint. The following table compares them.

Aspect Relationship Foreign key constraint
Applies to Unity Catalog metric views Tables and materialized views
Requirement on the target None A primary key or unique constraint
Join condition Any deterministic BOOLEAN condition (ON), or columns with the same name in both metric views (USING) Equality between the foreign key columns and the key columns of the target
Cardinality Declared as MANY TO ONE, ONE TO MANY, ONE TO ONE, or MANY TO MANY Not declared
Enforcement Not enforced Not enforced
Query optimization Not used Used when you specify RELY

Relationships compared to other ways to model joins

The following table shows how relationships compare to other ways of modeling joins:

Approach Where you define it How it's used
Relationships between metric views ALTER VIEW on the source metric view Records how metric views fit together as metadata only. AI assistants and BI tools read the relationship from Unity Catalog and choose joins freely for each analysis.
Joins in metric views The joins section of a metric view YAML definition Defines a fixed join, like a join between tables, that runs every time you query the metric view.
Dashboard relationships The Relationships pane of an AI/BI dashboard Encodes the dashboard's query and filtering logic so that aggregations stay correct across joined datasets.

How it works

A relationship belongs to its source metric view. When you add one, Azure Databricks stores it with the source, and the source returns it in DESCRIBE TABLE EXTENDED and in INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

When you add a relationship, Azure Databricks validates the condition the same way it validates the ON clause of a join. The statement fails if a column doesn't resolve or if the condition isn't a valid join condition.

A relationship follows the lifecycle of its endpoints:

  • When you drop the source metric view, Azure Databricks deletes its relationships.
  • When you drop the target metric view, the relationships that point to it no longer appear in DESCRIBE TABLE EXTENDED or INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

Requirements

To work with relationships, you need the following:

  • Databricks Runtime 19 and above, or a SQL warehouse. See Metric view feature availability.
  • A source and a target that are both Unity Catalog metric views. Temporary metric views aren't supported.
  • To add a relationship: the MANAGE privilege on the source metric view or ownership of it, the SELECT privilege on the target metric view, and USE CATALOG and USE SCHEMA on the catalog and schema that contain each metric view. See Set permissions.
  • To drop a relationship: the MANAGE privilege on the source metric view or ownership of it, and USE CATALOG and USE SCHEMA on its catalog and schema. You don't need any privilege on the target.

Add a relationship

To add a relationship, run ALTER VIEW ... ADD CONSTRAINT on the source metric view:

ALTER VIEW source_metric_view ADD [ CONSTRAINT name ]
  { MANY | ONE } TO { MANY | ONE } RELATIONSHIP TO target_metric_view [ [ AS ] target_alias ]
  { ON condition | USING ( column [, ...] ) }

Use the following rules when you write the statement:

  • Name: The name must be unique among all constraints in the schema. If you omit CONSTRAINT name, Azure Databricks names the relationship <source>_to_<target>, using the unqualified names of the two metric views. For example, an unnamed relationship from main.sales.orders_mv to main.sales.customers_mv is named orders_mv_to_customers_mv. Because a second unnamed relationship to the same target generates the same name, it fails. Name each relationship when a source has more than one relationship to the same target.
  • Cardinality: Use MANY TO ONE, ONE TO MANY, ONE TO ONE, or MANY TO MANY. Separate the keywords with spaces, not underscores.
  • Target: The target can be in any catalog or schema of the same metastore.
  • Target alias: Optional. Use an alias to refer to the target in the ON condition. A relationship from a metric view to itself requires an alias that differs from the source name.
  • ON condition: A deterministic BOOLEAN expression over the columns of both metric views. Qualify each column with the source name, the target name, or the target alias. The condition can't reference a measure, and it can't call an aggregate function, a window function, or a generator.
  • USING columns: Columns that exist with the same name in both metric views.

For the complete syntax and limits, see ALTER VIEW.

Examples

The following example declares that each order matches one customer, using a column that exists in both metric views:

ALTER VIEW main.sales.orders_mv ADD CONSTRAINT orders_customer
  MANY TO ONE RELATIONSHIP TO main.sales.customers_mv
  USING (customer_id);

The following example declares two relationships from the same source to the same target, one for each role that the target plays:

ALTER VIEW main.travel.flights_mv ADD CONSTRAINT departure_city
  MANY TO ONE RELATIONSHIP TO main.travel.cities_mv AS c
  ON flights_mv.departure_city_id = c.city_id;

ALTER VIEW main.travel.flights_mv ADD CONSTRAINT arrival_city
  MANY TO ONE RELATIONSHIP TO main.travel.cities_mv AS c
  ON flights_mv.arrival_city_id = c.city_id;

The following example declares a relationship from a metric view to itself. The mgr alias distinguishes the target from the source:

ALTER VIEW main.hr.employees_mv ADD CONSTRAINT manager
  MANY TO ONE RELATIONSHIP TO main.hr.employees_mv AS mgr
  ON employees_mv.manager_id = mgr.employee_id;

The following example declares a ONE TO MANY relationship from the customer side. Use this direction when you can manage the customers metric view but not the orders metric view:

ALTER VIEW main.sales.customers_mv ADD CONSTRAINT customer_orders
  ONE TO MANY RELATIONSHIP TO main.sales.orders_mv AS o
  ON customers_mv.customer_id = o.customer_id;

After a statement succeeds, DESCRIBE TABLE EXTENDED on the source metric view lists the new relationship.

View relationships

To view the relationships of a metric view, run DESCRIBE TABLE EXTENDED on the source metric view:

DESCRIBE TABLE EXTENDED main.travel.flights_mv;

The # Constraints section of the output contains one row for each relationship, with the relationship name and its definition:

# Constraints
departure_city   MANY TO ONE RELATIONSHIP TO `main`.`travel`.`cities_mv` AS `c` ON flights_mv.departure_city_id = c.city_id
arrival_city     MANY TO ONE RELATIONSHIP TO `main`.`travel`.`cities_mv` AS `c` ON flights_mv.arrival_city_id = c.city_id

DESCRIBE TABLE EXTENDED ... AS JSON returns the same relationships in the table_constraints field.

To list the relationships in a catalog, query INFORMATION_SCHEMA.TABLE_CONSTRAINTS and filter on the RELATIONSHIP constraint type:

SELECT table_schema, table_name, constraint_name
FROM main.information_schema.table_constraints
WHERE constraint_type = 'RELATIONSHIP';

Drop a relationship

To drop a relationship, run ALTER VIEW ... DROP CONSTRAINT on the source metric view with the relationship name:

ALTER VIEW main.travel.flights_mv DROP CONSTRAINT IF EXISTS arrival_city;

Without IF EXISTS, the statement fails with NO_SUCH_CONSTRAINT_EXCEPTION if the source metric view has no relationship with that name.

Use relationships with Genie Code for dashboard authoring

Genie Code for dashboard authoring uses the relationships declared on Unity Catalog metric views when it builds an AI/BI dashboard:

  • Import: When a dashboard uses Unity Catalog metric views, the agent reads the relationships declared on them and adds the ones that the analysis needs to the dashboard as dashboard relationships.
  • Export: When you ask the agent to save a dashboard relationship to Unity Catalog, it declares the relationship on the source metric view after you approve the change. Export requires both datasets to be Unity Catalog metric views, and the exported relationship always uses an ON condition. You need the privileges listed in Requirements.

Limitations

The following limitations apply to relationships between metric views:

  • You can declare a relationship only with ALTER VIEW. You can't declare a relationship in the metric view YAML definition.
  • Azure Databricks doesn't enforce relationships, check data against the declared cardinality, or use relationships to optimize queries. RELY isn't supported.
  • A target metric view doesn't list its inbound relationships. To find them, check each source metric view.
  • A source metric view can have at most 50 outbound relationships, and a target metric view can have at most 100 inbound relationships. Adding a relationship beyond either limit fails with UC_RESOURCE_QUOTA_EXCEEDED.
  • Renaming a metric view doesn't update an ON condition that names the old view. Drop the relationship and add it again with the new name.
  • Relationships aren't available in the REST API or SDKs. Use SQL to manage them.

Additional resources

  • Last updated on