Skip to main content

SYS.PROJECT.MATERIALIZATIONS

The sys.materializations table contains monitoring history for reflection materialization jobs. There are two types of jobs: REFRESH REFLECTION and COMPACTION.

Syntax for a full SELECT 
SELECT *
FROM sys.project.materializations

Example Output​

reflection_idmaterialization_idcreatedexpiressize_bytesseries_idinit_refresh_job_idseries_ordinaljoin_analysisstatefailure_msgdata_partitionslast_refresh_from_pdslast_refresh_finishedlast_refresh_duration_millis
3f652a0d-af99-4069-a78f-37e3232a79be17d01a44-f659-4354-8153-34a6a4abab0f2023-01-11T19:56:36.6263022-05-14T19:56:36.6231827516734669967771c40eb8b-0687-6a17-0bb8-7bd163b8f3000{"joinTables":[{"tableId":0,"tableSchemaPath":["@julie.irvine@cipesi.com","2vr3-k9wn"]}]}DONENONE2023-01-11T19:56:36.6232023-01-11T19:58:46.946130323
1debae5b-6f78-45c9-b065-b7be2e20367d3132df39-b6d0-4817-81b5-1bfc844104e02021-10-25T18:23:41.4632021-10-25T21:20:01.41228375316351862215711e8909d1-8211-0729-649b-b92bb518b2000{"joinTables":[{"tableId":0,"tableSchemaPath":["__accelerator","6c209200-b522-4f81-bbe0-d10668c7752c","3a24eebd-795e-4d1a-a504-f00dc543e860"]},{"tableId":1,"tableSchemaPath":["__accelerator","02714fb8-28c1-4dd0-a2a0-33c1b86be938","9517e8ba-ff1a-4e28-ab12-76dc6a35f525"]},{"tableId":2,"tableSchemaPath":["Samples","samples.dremio.com","NYC-taxi-trips"]}]}DELETEDNONE2021-10-25T18:20:01.4121970-01-01T00:000
76aad426-ea68-41f3-96ec-6f2890eb1a6a48c2b959-da58-4fe1-8f77-0e65d1bacede2023-05-18T15:56:12.1743022-09-18T15:56:12.16721090116844253723211b99b562-d65d-16cd-aa72-63ac20de1b000{"joinTables":[{"tableId":0,"tableSchemaPath":["@izdihar.jummai@dedupi.com","tate_artist_data"]}]}DONENONE2023-05-18T15:56:12.1672023-05-18T15:58:12.623120456
a105eb15-3010-47cf-a8c4-75018153ed6f6bb608e7-9a38-4ee6-aaaa-e7521241b48b2022-07-15T05:43:33.8743021-11-15T05:43:33.871718516578638139821d2f0179-8e48-8bd6-3f8f-9caa59b0c0000{"joinTables":[{"tableId":0,"tableSchemaPath":["@trish.ulli@absolushi.com","DX46904"]}]}DONENONE2022-07-15T05:43:33.8712022-07-15T05:45:54.656140785
63fd1c83-5cde-4133-9e2d-60543550580a7a0313fb-2436-4cc9-a1ca-81104f4b82e52023-01-24T10:00:54.5173022-05-27T10:00:54.2312079893616745544548411c3053a8-afea-c1e7-3249-358c7c8ef8000{"joinTables":[{"tableId":1,"tableSchemaPath":["s3-test","flex-349","trips_pickupDate"]}]}DONENONE2023-01-24T10:00:54.2312023-01-24T10:03:44.549170318
e673624e-24d9-489c-bc65-fde8993d80d68e3ea85f-d37a-4b7b-bebd-267bb1a7637f2023-05-22T20:09:10.4623022-09-22T20:09:10.4591728053955816847861509831b943419-0853-f79e-6c87-7b0171e4f6000{"joinTables":[{"tableId":1,"tableSchemaPath":["s3-test","flex-293","trips_pickupDate"]}]}DONENONE2023-05-22T20:09:10.4592023-05-22T20:38:00.7461730287
1debae5b-6f78-45c9-b065-b7be2e20367d9f212a9a-018d-4244-bd86-e6c8434b353d2021-10-25T19:22:22.1052021-10-25T22:20:02.06728414916351897422311e88fc11-2c8f-b349-2533-262eebad5f000{"joinTables":[{"tableId":0,"tableSchemaPath":["__accelerator","6c209200-b522-4f81-bbe0-d10668c7752c","8237977a-8f62-4e97-8c57-c7c96cfc08a2"]},{"tableId":1,"tableSchemaPath":["__accelerator","02714fb8-28c1-4dd0-a2a0-33c1b86be938","17ce27c5-8aae-4ea2-a565-976aef2d70cd"]},{"tableId":2,"tableSchemaPath":["Samples","samples.dremio.com","NYC-taxi-trips"]}]}DELETEDNONE2021-10-25T19:20:02.0671970-01-01T00:000
24b9b51f-424c-4478-bd1f-0667cb4e99bda7016287-f92c-449b-b423-962f95a55b182022-07-15T05:43:33.7953021-11-15T05:43:33.792728816578638139401d2f0179-dbcb-c4e1-6c37-15e0e8fef7000{"joinTables":[{"tableId":0,"tableSchemaPath":["@iris.limbani@lowercasea.com","DX46904"]}]}DONENONE2022-07-15T05:43:33.7922022-07-15T05:45:54.106140314
90a00924-80af-47f2-b2bb-4e5bc2d482ffb1dfde8c-4b8d-4893-98e6-1633f1a1c6552023-01-18T13:12:54.4033022-05-21T13:12:54.1576139501516740475752751c380fa8-c8e4-265a-376e-fbac236e45000{"joinTables":[{"tableId":1,"tableSchemaPath":["s3-test","flex-879","trips_pickupDate"]}]}DONENONE2023-01-18T13:12:54.1572023-01-18T13:17:24.651270494
1415a4c8-4a51-467c-b841-cfc957c0af27ddb76d39-4866-4547-a7b0-f17977ee70442022-09-21T17:41:06.0843022-01-22T17:41:06.081761616637820662291cd4b34c-bfa0-4b3f-df38-a66a4ee35b000{"joinTables":[{"tableId":0,"tableSchemaPath":["@boutros.mukami@chisci.com","airtravel"]}]}DONENONE2022-09-21T17:41:06.0812022-09-21T17:43:16.591130510
2b39e158-38f2-479b-895f-ef647ec902b0df542d3b-a002-4be6-a964-bf035a2a7c922023-05-23T22:17:22.7083022-09-23T22:17:22.37983866716848802428631b92c48d-156c-426d-0315-9069d49c91000{"joinTables":[{"tableId":1,"tableSchemaPath":["Samples (23)","samples.dremio.com","NYC-taxi-trips"]}]}DONENONE2023-05-23T22:17:22.3792023-05-23T22:20:22.644180265
9ba24fd3-840d-4045-a635-6d2ed92d03dfe6bb8887-3de1-4eae-838d-b08d5c0c1e1d2022-07-15T05:50:43.8443021-11-15T05:43:33.792886116578642440011d2effcb-a77b-ed4a-9aae-b09eefe7b4000{"joinTables":[{"tableId":1,"tableSchemaPath":["__accelerator","24b9b51f-424c-4478-bd1f-0667cb4e99bd","DX46904""]}]}"DONENONE2022-07-15T05:43:33.7922022-07-15T05:52:54.174560382

Fields​

FieldData TypeDescription
reflection_idvarcharThe UUID of the reflection that was refreshed.
materialization_idvarcharThe UUID of the materialization that was created for the reflection.
createdtimestampThe timestamp of when the record of the materialization was created.
expirestimestampThe timestamp of when the materialization expires. If materializations on the data source are set never to expire, the timestamp is 1000 years after the timestamp for when the materialization was created.
size_bytesbigintThe current size of the materialization, in bytes.
series_idbigintOne of the keys (the other being series_ordinal) into the SYS.PROJECT.REFRESHES table to see the details for single refreshes.
init_refresh_job_idvarcharThe ID of the job that created the materialization.
series_ordinalintegerOne of the keys (the other being series_id) into the SYS.PROJECT.REFRESHES table to see the details for single refreshes.
join_analysisvarcharRuntime information about the input, output and unmatched row counts for probe and build sides of all joins. Used for starflake reflection pruning.
statevarcharThe current state of the materialization, including the states of the job that created the materialization.
  • CANCELED: Indicates that the job was canceled or that the definition of the corresponding reflection was changed while the job was in progress.
  • COMPACTED: Not used.
  • DEPRECATED: Indicates that a new materialization with more recent data is available.
  • DONE: Indicates that the job to create the materialization is complete and that the materialization is available.
  • FAILED: Indicates that the job to create the materialization failed.
  • RUNNING: Indicates that the job to create the materialization is running.
failure_msgvarcharThe message that is logged if the job to create a materialization fails.
data_partitionsvarcharThis field is deprecated.
last_refresh_from_pdstimestampThe timestamps of when the last refresh occurred of the tables from which the reflection is ultimately derived.
last_refresh_finishedtimestampThe timestamp of when the job to create the materialization finished.
last_refresh_duration_millisbigintThe duration in milliseconds of the job to create the materialization.