SYS.PROJECT.HISTORY.AUTONOMOUS_REFLECTIONS
The sys.project.history.autonomous_reflections table contains the historical metadata for Autonomous Reflections in a project.
SELECT *
FROM sys.project.history.autonomous_reflections
Example Output
| reflectionName | reflection_id | createdAt | reflectionType | enabled | operationSource | changeContent | datasetId | datasetName | operationType | autonomous | score | avgDailyAcceleratedCount | avgDailyImprovementInMs | avgImprovementFactor | benefitsScoreDesc | reason | viewSql | sqlType | beforeImage | key | datasetPath |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AutoRef_Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c_aggregation | 4390b0f5-0a0d-4b23-9a96-d9e9c5427baf | 1748883691293 | AGGREGATION | true | REST | "CREATE VIEW "__system_managed_views"."Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c" AS SELECT "NYC-taxi-trips-iceberg"."fare_amount" AS "F0[fare_amount]", "NYC-taxi-trips-iceberg"."passenger_count" AS "F1[passenger_count]"\nFROM "Samples"."samples.dremio.com"."NYC-taxi-trips-iceberg"\n ; ALTER DATASET "__system_managed_views"."Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c" CREATE AGGREGATE Reflection "agg_336b6cd6-9ade-4657-b685-4450c4dca951" USING DIMENSIONS ("F1[passenger_count]") MEASURES ("F0[fare_amount]" (SUM)) | aa0b4932-e51d-4fed-93bf-e03b4a9b0a02 | Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c | ADD | true | 42.47425033306188 | 5.0 | 22366.980051424373 | 10.00000020692081 | FAIR | CREATE autonomous Reflection in Autonomous Mode | CREATE autonomous Reflection in Autonomous Mode","viewSql":"CREATE VIEW "__system_managed_views"."Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c" AS SELECT "NYC-taxi-trips-iceberg"."fare_amount" AS "F0[fare_amount]", "NYC-taxi-trips-iceberg"."passenger_count" AS "F1[passenger_count]"\nFROM "Samples"."samples.dremio.com"."NYC-taxi-trips-iceberg"\n | CREATE_Reflection | false | 4390b0f5-0a0d-4b23-9a96-d9e9c5427baf_CREATE_Reflection | ["__system_managed_views","Dataset_9a48f54a-d241-46cf-9e71-ae25642cf22c"] |
| AutoRef_v1_raw | 68bd2902-ad85-4793-ab6d-4f425b798775 | 1748883691257 | RAW | true | REST | --Default Raw Reflection ; ALTER DATASET test.v1 CREATE RAW REFLECTION "raw_2311879c-79c4-4477-a1ed-6b4c999165bc" USING DISPLAY ("passenger_count", "EXPR$1") | 8984be51-cd00-43f7-95e3-918eeabc51db | v1 | ADD | true | 42.47425033306188 | 5.0 | 22366.980051424373 | 10.00000020692081 | FAIR | CREATE autonomous Reflection in Autonomous Mode | --Default Raw Reflection | CREATE_REFLECTION | false | 68bd2902-ad85-4793-ab6d-4f425b798775_CREATE_REFLECTION | ["test","v1"] |
Columns
| Column | Data Type | Description |
|---|---|---|
| reflectionName | varchar | Names the Autonomous Reflection. |
| reflection_id | varchar | Identifies the UUID of the Autonomous Reflection. |
| createdAt | timestamp | Represents the date and time (in UTC format) when the Autonomous Reflection was created. |
| reflectionType | varchar | Specifies the type of Autonomous Reflection. Enum: AGGREGATION, RAW |
| enabled | boolean | Indicates whether the Autonomous Reflection is currently enabled. |
| operationSource | varchar | Specifies the source of the operation. Enum: SQL, REST |
| changeContent | varchar | Displays content changes based on the value of operationSource. Shows a SQL statement when operationSource is SQL, and lists specific changes when the operationSource is REST. |
| datasetId | varchar | Identifies the ID of the dataset associated with the Autonomous Reflection. |
| datasetName | varchar | Identifies the name of the dataset associated with the Autonomous Reflection. |
| operationType | varchar | Specifies the type of operation performed. Enum: ADD, REMOVE |
| autonomous | boolean | Indicates whether the Reflection is autonomous. |
| score | double | Shows the score generated for the Autonomous Reflection. |
| avgDailyAcceleratedCount | double | Shows the average number of times per day the Autonomous Reflection is used to accelerate queries. |
| avgDailyImprovementInMs | double | Shows the average amount of time (in milliseconds) saved per day due to the Autonomous Reflection. |
| avgImprovementFactor | double | Measures how much faster affected queries run due to the Autonomous Reflection. |
| benefitsScoreDesc | varchar | Provides a qualitative description of the Autonomous Reflection's score. Enum: EMPTY, NEW, POOR, FAIR, GOOD |
| reason | varchar | Provides the rationale used by the Autonomous Reflection in making decisions. |
| viewSql | varchar | Identifies the SQL statement used to create the view associated with the Autonomous Reflection. |
| sqlType | varchar | Spectifies the type of SQL operation. Enum: CREATE_VIEW, DROP_VIEW, CREATE_REFLECTION, DROP_REFLECTION, DROP_TABLE, EXISTING_REFLECTION |
| beforeImage | boolean | Indicates whether the history record captures the object's state before the operation. For example, the beforeImage is false for a CREATE VIEW operation since the view doesn't exist yet. The beforeImage is true for a DROP TABLE operation, as the record reflects the table's state prior to deletion. |
| key | varchar | Identifies the key to the key-value store backing the sys.project.history.autonomous_reflections table. Typically formed by concatenating the reflectionId and sqlType. The key is unique to ensure history entries are not overwritten. |
| datasetPath | varchar | Shows the path to the dataset associated with the Autonomous Reflection. |