POST_START
Discovering Schema Metadata with INFORMATION_SCHEMA
I recently needed to understand the structure of schemas in my Databricks environment to better organize and manage data assets. I decided to explore the schema metadata using the INFORMATION_SCHEMA in Unity Catalog, which provides a standardized way to query metadata about databases and schemas.
Exploring Schema Metadata
I started by running a query to list all schemas across all catalogs in the environment. This helped me get a high-level overview of the schemas available and their owners.
SELECT * FROM system.information_schema.schemata;| catalog_name | schema_name | schema_owner |
|---|---|---|
| production | sales | data_engineers |
| production | finance | finance_engineers |
From the output, I noticed that the production catalog contains multiple schemas, such as sales and finance, each owned by different teams. This gave me a clear view of how data is organized and who is responsible for each schema.
Focusing on a Specific Catalog
Next, I wanted to focus on the training catalog to see what schemas were available there. I adjusted my query to filter by the catalog_name and retrieved the relevant schemas and their owners.
SELECT catalog_name, schema_name FROM system.information_schema.schemata WHERE catalog_name = 'training';| catalog_name | schema_name | schema_owner |
|---|---|---|
| production | sales | data_engineers |
| production | finance | finance_engineers |
Even though I was querying for the training catalog, the output showed that the production catalog still had schemas. This was a bit confusing, but it made me realize that the query might have returned schemas from different catalogs, and I needed to be more specific in my filtering.
Through these steps, I learned how to effectively use the INFORMATION_SCHEMA to discover schema metadata in Unity Catalog. This knowledge will help me navigate and manage schemas more efficiently in the future.
Leave a Reply