Share via

Delta Sharing materialization history system table reference

The shared materialized data history table represents data materialization created from view sharing, materialized views, and streaming tables using Delta Sharing. It contains information on where the data came from, the securable being materialized, and when the materialization was created.

For more information about shared materializations, see Add views to a share and Read shared views.

Table path: This system table is located at system.sharing.materialization_history.

Shared materialized data history system table schema

The shared materialized data history system table uses the following schema:

Column Name Type Description Example Data Nullable
sharing_materialization_id string The unique ID of a data materialization. da38803f-2a62-4e27-bdb9-29b801c6dd84 False
account_id string The ID of the Azure Databricks account where the materialization was created. False
workspace_id string The ID of the Azure Databricks workspace billed. 6051921418418893 False
recipient_name string Name of the recipient using the data materialization. e2-dogfood True
provider_name string Name of the provider using the data materialization. aws:us-west-2:19a85dee-54bc-43a2-87ab-023d0ec16013 True
share_name string Name of the share used to create data materialization. my_share False
schema_name string Name of the schema of the shared asset. my_schema False
table_name string Name of the table used to create data materialization. stocks False
created_at timestamp Timestamp of when the materialization was created. 2025-01-01 00:00:00 False

Sample queries

This section includes the following sample queries you can use to gain insight into billing attribution:

When the provider is billed for data materialization, only the provider can see query results. When the recipient is billed for data materialization, only the recipient can see query results.

For more details on how Delta Sharing attributes and incurs costs, see How do I incur and check Delta Sharing costs?.

A Delta Sharing recipient asks how many DBUs were spent querying shared views

Replace instances of ... with your information.

SELECT
  SUM(bu.usage_quantity)
FROM
  system.billing.usage bu
INNER JOIN
  system.sharing.materialization_history dm
ON
  dm.sharing_materialization_id = bu.sharing_materialization_id
WHERE
  bu.billing_origin_product = 'DATA_SHARING' AND
 dm.share_name = '...' AND
 dm.schema_name = '...' AND
 dm.table_name IN (...);

A Delta Sharing recipient asks which provider queried the most usage

SELECT
  SUM(bu.usage_quantity) AS total_usage,
  dm.provider_name
FROM
  system.billing.usage bu
INNER JOIN
  system.sharing.materialization_history dm
ON
  dm.sharing_materialization_id = bu.sharing_materialization_id
WHERE
  bu.billing_origin_product = 'DATA_SHARING'
GROUP BY
  dm.provider_name
ORDER BY
  total_usage DESC;

A Delta Sharing provider asks how many DBUs were spent on filtering views for open recipients

SELECT
  SUM(bu.usage_quantity)
FROM
  system.billing.usage bu
INNER JOIN
  system.sharing.materialization_history dm
ON
  dm.sharing_materialization_id = bu.sharing_materialization_id
INNER JOIN
  system.information_schema.table_share_usage tsu
ON
  dm.share_name = tsu.share_name AND
  dm.schema_name = tsu.schema_name AND
  dm.table_name = tsu.table_name
INNER JOIN
  system.information_schema.tables t
ON
  t.catalog_name = tsu.catalog_name AND
  t.schema_name = tsu.schema_name AND
  t.table_name = tsu.table_name
WHERE
  bu.billing_origin_product = 'DATA_SHARING' AND
  t.table_type = 'VIEW';

A Delta Sharing provider asks which open recipient is incurring the most cost

SELECT
 SUM(usage_quantity) usage,
 srp.recipient_name
FROM
  system.billing.usage bu
INNER JOIN
  system.delta_sharing.data_materializations dm
ON
  dm.sharing_materialization_id = bu.sharing_materialization_id
INNER JOIN system.information_schema.share_recipient_privileges srp
ON
  srp.share_name = dm.share_name
WHERE
  bu.billing_origin_product = 'DATA_SHARING'
ORDER BY
  bu.usage DESC
LIMIT 1;