Relationships between metric views
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 | Equality between the foreign key columns and the key columns of the target |
Cardinality | Declared as | Not declared |
Enforcement | Not enforced | Not enforced |
Query optimization | Not used | Used when you specify |
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 |
| 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. |
The | Defines a fixed join, like a join between tables, that runs every time you query the metric view. | |
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, 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, 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, Databricks deletes its relationships.
- When you drop the target metric view, the relationships that point to it no longer appear in
DESCRIBE TABLE EXTENDEDorINFORMATION_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
MANAGEprivilege on the source metric view or ownership of it, theSELECTprivilege on the target metric view, andUSE CATALOGandUSE SCHEMAon the catalog and schema that contain each metric view. See Set permissions. - To drop a relationship: the
MANAGEprivilege on the source metric view or ownership of it, andUSE CATALOGandUSE SCHEMAon 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, Databricks names the relationship<source>_to_<target>, using the unqualified names of the two metric views. For example, an unnamed relationship frommain.sales.orders_mvtomain.sales.customers_mvis namedorders_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, orMANY 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
ONcondition. A relationship from a metric view to itself requires an alias that differs from the source name. - ON condition: A deterministic
BOOLEANexpression 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
ONcondition. 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. - Databricks doesn't enforce relationships, check data against the declared cardinality, or use relationships to optimize queries.
RELYisn'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
ONcondition 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.