What you will be able to do
- Predict how Databricks resolves fully qualified, two-part and unqualified object names
- Use USE CATALOG and USE SCHEMA to set the current catalog and schema, and know their side effects
- Choose between a three-part identifier, a /Volumes path and a cloud URI for each kind of object
- Explain why access to an object depends on privileges on the catalog and schema above it
1.Three-part identifiers and how short names are resolved
In Unity Catalog, a table's full name is <catalog-name>.<schema-name>.<table-name>. A fully qualified name always points to exactly one object. You can leave parts out, and Databricks fills them in from the session's current catalog and current schema. A new session starts in the workspace's default catalog. Compute settings such as spark.databricks.sql.initial.catalog.namespace, or a pipeline's own catalog and schema settings, can override that default.
| Identifier pattern | Behavior |
|---|---|
| catalog_name.schema_name.object_name | Refers to the database object specified by the identifier |
| schema_name.object_name | The object with that schema and name in the current catalog |
| object_name | The object with that name in the current catalog and schema |
Two statements change the current context. USE CATALOG sets the catalog and resets the schema to default. USE SCHEMA sets the schema inside the current catalog, and USE SCHEMA catalog_name.schema_name sets both at once. Querying an object by its fully qualified name does not change the current catalog or schema. To check where you are, call current_catalog() and current_schema(), as in the documented session below.
-- Setting the catalog resets the schema to `default`
> USE CATALOG some_cat;
> SELECT current_catalog(), current_schema();
some_cat default
-- Setting the schema within the current catalog
> USE SCHEMA some_schem;
> SELECT current_catalog(), current_schema();
some_cat some_schemaCheckpoint 1 of 6· Check yourself
Your current catalog is dev and your current schema is default. You run SELECT * FROM sales.orders. Which table is read?
A two-part name is schema.relation. Databricks completes it with the current catalog, so it becomes dev.sales.orders.
“If the identifier consists of two parts: schema.relation, it is further qualified with the result of SELECT current_catalog() to make it unique.”Source: docs.databricks.com
A one-part name does not always point to a table in the namespace. Databricks first looks for a matching common table expression (CTE), then for a temporary view or temporary table in the session. Only if neither matches does it add the current catalog and schema and look for a persisted object. For this reason, Databricks recommends fully qualified names whenever a workload uses objects from more than one schema or catalog.
Checkpoint 2 of 6· Put it in order
Put these in the order Databricks checks them when resolving an unqualified table reference. The first match wins.
- 1.A persisted table in the current catalog and current schema
- 2.A common table expression from an enclosing WITH clause
- 3.A temporary view or temporary table in the current session
A CTE wins over a temporary view, and a temporary view wins over the persisted table. Only the persisted table is looked up in the three-level namespace.
“An unqualified reference to a common table expression wins even over a temporary view”Source: docs.databricks.com
Sources1
2.Volumes: a three-part name to manage, a path to read files
Volumes sit at the same third level as tables, but you use them differently. The three-part name <catalog_name>.<schema_name>.<volume_name> is for management commands such as CREATE VOLUME and DROP VOLUME. To read or write the files in a volume, you use a path built from the same three names:
/Volumes/<catalog>/<schema>/<volume>/<path>/<file-name>
The path is the same in Spark, SQL, Python and other languages. Spark also accepts an optional dbfs:/ prefix. Unity Catalog manages the /<catalog>/<schema>/<volume> directories and makes them read-only, so filesystem commands can't create or delete them. The paths /Volumes and dbfs:/Volumes are reserved, and so are common typos such as /volumes, /Volume and /volume. Volumes need Databricks Runtime 13.3 LTS or above. On 12.2 LTS and below, writes to /Volumes paths might seem to succeed but only go to temporary disks on the cluster.
Checkpoint 3 of 6· Fill the gap
Which path segment completes the volume file path pattern?
/ ? /<catalog_name>/<schema_name>/<volume_name>/<path_to_file>The volume file path starts with /Volumes, capitalised and plural. The other segments are not the root of a volume path.
Source: docs.databricks.comTables and volumes also differ in whether you can skip the namespace and use a raw cloud storage URI. Managed objects can only be reached through the namespace. External objects can also be reached by URI, and Unity Catalog privileges still govern that access.
| Object | Object identifier | File path | Cloud URI |
|---|---|---|---|
| External location | no | no | yes |
| Managed table | yes | no | no |
| External table | yes | no | yes |
| Managed volume | no | yes | no |
| External volume | no | yes | yes |
Checkpoint 4 of 6· Check yourself
An analyst wants to read a managed table by pointing a query at its s3:// location instead of using its name. What happens?
Managed tables can only be accessed by their object identifier. Cloud URIs work for external tables and volumes, not managed ones.
“Path-based access to Unity Catalog managed tables is not supported.”Source: docs.databricks.com
3.Why every level of the name matters for access
Each part of a three-part name is also a place where access is checked. Catalogs and schemas are container objects. To work with an object, you need a usage privilege on each container above it. Reading a table needs three privileges together: USE CATALOG on its catalog, USE SCHEMA on its schema and SELECT on the table. Reading files in a volume works the same way, with READ VOLUME instead of SELECT.
Grants also flow down the hierarchy. A privilege granted on a catalog or schema automatically applies to every object inside it, both current and future. Grants on the metastore are the exception. They cover metastore-level operations, such as creating catalogs, and nothing below the metastore inherits them.
USE CATALOG has two meanings, and they are easy to mix up. As a SQL statement, it only sets the current catalog for your session. As a Unity Catalog privilege, it is what you must hold before you can work with anything in that catalog. Running the statement does not give you the privilege.
Checkpoint 5 of 6· Check yourself
A user has SELECT on main.finance.invoices and USE CATALOG on main, but no privileges on the finance schema. Can they read the table?
Reading needs USE CATALOG, USE SCHEMA and SELECT together. Without the usage privilege on the schema, the user can't read the table even though they have SELECT on it.
“All three are required. Having only the SELECT privilege on a table is not sufficient to read it”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
In Unity Catalog, what are the three levels of the namespace used to fully qualify a table, listed from the top (broadest) level down to the object itself?
Correct answer: A — Catalog, schema, table
- A. This is the correct order: a catalog is the top-level container, a schema (database) sits inside a catalog, and the table, view, volume, or function is the object inside the schema, giving the fully qualified `catalog.schema.table` form.
- B. This is incorrect because the metastore sits above the catalog level and is not part of the three-level object reference; queries never include the metastore name in the qualified path.
- C. This is incorrect because 'workspace' is not a level in the object namespace at all — a single Unity Catalog metastore, and its catalogs, can be attached to multiple workspaces.
- D. This is incorrect because 'account' is an organizational boundary above the metastore, not a namespace level used when qualifying a table reference.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.After USE CATALOG, the schema you selected earlier is still the current schema.Why is that wrong?
USE CATALOG resets the current schema to default. Until you run USE SCHEMA, unqualified names resolve in that catalog's default schema.
Covered in Three-part identifiers and how short names are resolved
2.Because a volume has a three-part name, you can read its files with catalog.schema.volume, just like a table.Why is that wrong?
The three-part volume name is for management commands such as CREATE VOLUME and DROP VOLUME. To work with the files, you need the /Volumes path.
Covered in Volumes: a three-part name to manage, a path to read files
3.Running the SQL statement USE CATALOG gives you the USE CATALOG privilege on that catalog.Why is that wrong?
The statement only sets your session's current catalog. The privilege with the same name is a separate grant, and you need it to work with objects in the catalog.
Practise it for real
Watch the current catalog and schema change as you move through the namespace, and see how short names are resolved against them.
1.In a SQL editor or notebook, run SELECT current_catalog(), current_schema();
Why: Shows where you start. This is normally the workspace default catalog, unless a compute setting overrides it.
You should see: One row showing your default catalog and the current schema.
2.Run USE CATALOG with a catalog you hold the USE CATALOG privilege on, then run SELECT current_catalog(), current_schema(); again.
Why: Shows that setting the catalog resets the schema.
You should see: The catalog you chose, with the schema shown as default.
3.Run USE SCHEMA with a schema in that catalog, then check current_catalog() and current_schema() again.
Why: USE SCHEMA moves to another schema without changing the current catalog.
You should see: The same catalog as before, and the schema you selected.
4.Query one table three ways: as catalog.schema.table, as schema.table, and as table on its own.
Why: Confirms that a two-part name uses the current catalog, and a one-part name uses both the current catalog and the current schema.
You should see: All three return the same rows, as long as no CTE or temporary view has the same name as the table.
Stuck? Get a nudge
If USE SCHEMA fails with a not-found error, the schema isn't in the current catalog. Run USE SCHEMA catalog_name.schema_name to set both at once.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/queryOfficial docs
“Databricks recommends using fully-qualified identifiers when queries or workloads interact with database objects stored across multiple schemas or catalogs.”
↩︎ Three-part identifiers and how short names are resolved“If you're using Unity Catalog, tables use a three-tier namespace with the following format: <catalog-name>.<schema-name>.<table-name>.”
↩︎ Three-part identifiers and how short names are resolved - 2.https://docs.databricks.com/aws/en/volumesOfficial docs
“These directories are read-only and managed automatically by Unity Catalog.”
↩︎ Volumes: a three-part name to manage, a path to read files“Volumes are only supported on Databricks Runtime 13.3 LTS and above.”
↩︎ Volumes: a three-part name to manage, a path to read files - 3.https://docs.databricks.com/aws/en/volumes/pathsOfficial docs
“Access to data through cloud URIs for these objects is fully governed by Unity Catalog privileges”
↩︎ Volumes: a three-part name to manage, a path to read files“To actually work with files in volumes, you must use path-based access.”
↩︎ Exam trap 2“Path-based access to Unity Catalog managed tables is not supported.”
↩︎ Checkpoint - 4.https://docs.databricks.com/aws/en/data-governance/unity-catalog/access-control/permissions-conceptsOfficial docs
“When you grant a privilege on a container object, that privilege automatically applies to all current and future child objects.”
↩︎ Why every level of the name matters for access“All three are required. Having only the SELECT privilege on a table is not sufficient to read it”
↩︎ Checkpoint - 5.
“Importantly, privileges granted at the metastore level do not inherit to child objects in the hierarchy.”
↩︎ Why every level of the name matters for access
Also cited
“Setting the catalog also resets the current schema to default.”
↩︎ Exam trap 1“For the USE CATALOG Unity Catalog privilege, which users need to interact with objects in a catalog, see USE CATALOG.”
↩︎ Exam trap 3“Setting the catalog also resets the current schema to default.”
↩︎ Prediction“If the identifier consists of two parts: schema.relation, it is further qualified with the result of SELECT current_catalog() to make it unique.”
↩︎ Checkpoint“An unqualified reference to a common table expression wins even over a temporary view”
↩︎ Checkpoint