- Schema:
EXTERNAL_ DWH_ OBJECTS view¶
This Account Usage view displays a row for each object that Horizon Catalog metadata connectors collect from external databases, data warehouses, and data pipeline tools, such as BigQuery, PostgreSQL, and dbt. The view includes both leaf objects, such as tables and views, and the containers that hold them, such as databases and schemas.
For objects collected from BI tools, see the EXTERNAL_BI_OBJECTS view.
Columns¶
| Column name | Data type | Description |
|---|---|---|
| REFERENCE_ID | TEXT | Snowflake-generated identifier of the object. This value is always present and uniquely identifies the object. |
| EXTERNAL_ID | TEXT | Identifier of the object in the source system, when known. Otherwise, NULL. |
| NAME | TEXT | Name of the object in the source system. |
| OBJECT_TYPE | TEXT | Type of the object. Rows for containers have one of the following values: For dbt, incremental, ephemeral, dynamic table, and legacy model rows all have the value |
| PARENT_REFERENCE_ID | TEXT | REFERENCE_ID of the object’s immediate parent in this view. NULL for top-level objects, such as databases. |
| CONNECTOR | TEXT | Display name of the metadata connector that collected the object. This is the connector’s name only, not a fully qualified name. |
| CONNECTOR_TYPE | TEXT | Vendor or type of the source system, such as BIGQUERY or DBT. |
| COMMENT | TEXT | Description of the object. |
| CREATED | TIMESTAMP_LTZ | Date and time when the object was created in the source system. Only rows for tables have a value. For all other object types, NULL. |
| LAST_ALTERED | TIMESTAMP_LTZ | Date and time when the object was last updated in the source system. Only rows for tables have a value. For all other object types, NULL. |
| DELETED | TIMESTAMP_LTZ | Date and time when Snowflake observed that the object was removed from the source system. NULL if the object still exists. |
| PROPERTIES | VARIANT | Additional properties of the object, as an OBJECT. The following keys can be present:
|
| DATABASE_NAME | TEXT | Name of the source database. For a database row, this is the database’s own name. For descendants, it’s the name of the database that contains the object. NULL if unknown. |
| SCHEMA_NAME | TEXT | Name of the source schema. For a schema row, this is the schema’s own name. For tables and views, it’s the name of the schema that contains the object. NULL for database rows. |
| ROW_COUNT | NUMBER | Number of rows in the object, when known. NULL for containers. |
| BYTES | NUMBER | Size of the object in bytes, when known. NULL for containers. |
| COLUMNS | VARIANT | For table-like objects, an ARRAY of OBJECTs that describe the object’s columns. Each element has the following keys:
NULL for containers. |
Usage notes¶
- Latency for the view may be up to 24 hours.
- The view includes objects that were removed from the source system. The
DELETEDcolumn is non-NULL for those objects, and their rows remain in the view; no retention limit currently applies. To list only current objects, filter onDELETED IS NULL. - Columns that apply only to some object types are NULL for the other types. For example,
ROW_COUNT,BYTES, andCOLUMNSare NULL for databases and schemas. - To walk the hierarchy, join
PARENT_REFERENCE_IDtoREFERENCE_ID. Joining on these columns is more reliable than joining on names, because names can collide across containers or change when a container is renamed. The hierarchy for most connectors is database, then schema, then table-like object. - Users and roles that have been granted the OBJECT_VIEWER database role can access this view. For more information, see SNOWFLAKE database roles.
- Access to this view isn’t controlled by privileges on metadata connectors. A role that can query the view sees the objects
from every connector in the account, including connectors it has no
USAGEorREAD EXTERNAL METADATAprivilege on.
Examples¶
List the largest current tables and views that a BigQuery connector collected:
Find schemas that contain no current objects:
List the columns of one object: