What you will be able to do
- Explain what Unity Catalog controls for a managed table compared with an external table
- Create an empty managed table and an external table with a LOCATION, and name the privileges each one needs
- Predict what happens to the data files when each table type is dropped
- Choose between a managed and an external table for a given scenario
Key concept
Managed vs external table — Unity Catalog governs every registered table: it controls access, auditing and lineage. For a managed table it also decides where the data files are stored and controls their lifecycle. For an external table, you choose the storage location and you look after the files.
1.Two meanings of "managed"
Before you write a CREATE TABLE statement, you need to know what the word "managed" means. In Unity Catalog it has two meanings, and exam questions often turn on the difference. In the loose sense, every object registered in Unity Catalog is "managed": Unity Catalog stores its metadata and controls governance, which covers access, auditing and lineage. External tables are governed this way too.
The narrower sense is the one in the name "managed table". For a managed table, Unity Catalog also decides where in your cloud account the data files go and controls their lifecycle: how they are organized, how they are optimized and when they are deleted. The data stays in your cloud account either way, and Databricks doesn't take ownership of it. The difference is who looks after the files. A third use of the word is the MANAGE privilege. It lets a user grant or revoke privileges on an object, transfer its ownership, or delete it without being the owner.
Checkpoint 1 of 5· Match them up
Match each use of "manage" to what it means
Tap a term, then the definition that fits it.
Only the managed table (or managed volume) meaning covers storage and lifecycle. The governance meaning applies to every registered object.
“When people say that an object is managed by Unity Catalog, they typically mean that Unity Catalog governs access to it.”Source: docs.databricks.com
Sources1
2.Creating a managed table
Managed tables are the default table type and the one Databricks recommends. They use Delta Lake or Apache Iceberg. A plain CREATE TABLE with no USING clause and no LOCATION creates a managed Delta table. You add USING iceberg only when you want Iceberg. You don't give a path: Unity Catalog puts the files in the managed storage location defined on the containing schema, catalog or metastore. To create one, you need USE CATALOG on the parent catalog, plus USE SCHEMA and CREATE TABLE on the parent schema.
-- Create a managed Delta table
CREATE TABLE <catalog-name>.<schema-name>.<table-name>
(
<column-specification>
);Note the three-level name. Every read and write to a managed table must use catalog.schema.table. Path-based access to managed tables isn't supported (except in Compatibility Mode), because it would bypass Unity Catalog access controls. In return for giving up control of the files, you get features that external tables don't have, including predictive optimization and automatic deletion of the files after a DROP TABLE.
Checkpoint 2 of 5· Check yourself
An analyst runs CREATE TABLE main.sales.orders (id INT, amount DOUBLE); with no USING and no LOCATION clause. What do they get?
With no LOCATION, the table is managed. With no USING iceberg, Databricks creates a Delta Lake table by default.
“To create an Apache Iceberg table, explicitly specify USING iceberg. Otherwise, Databricks creates a Delta Lake table by default.”Source: docs.databricks.com
Checkpoint 3 of 5· Exam question
An analyst wants a new sales table in Unity Catalog to be fully managed by Databricks, so that a later `DROP TABLE` also deletes the underlying data files, without pointing the table at any existing cloud storage path. Which approach achieves this?
Correct answer: B — Run `CREATE TABLE catalog.schema.sales AS SELECT * FROM source_view` with no `LOCATION` clause, so Unity Catalog writes the data into the schema's own managed storage path.
- A. The explicit `EXTERNAL` keyword and `LOCATION` clause register only metadata in Unity Catalog while the Parquet files remain owned by the bucket, which is the opposite of the fully-managed lifecycle the analyst wants.
- B. A `CREATE TABLE AS SELECT` with no `LOCATION` clause is written into the schema's default managed storage path, so Unity Catalog owns both the metadata and the data files and removes the files on `DROP TABLE`.
- C. Supplying a `LOCATION` clause makes the table reference the existing files in place rather than copying them into managed storage, so dropping it would leave the underlying files untouched.
- D. Pointing `USING CSV` at an existing file path also creates a table that references data outside managed storage, so the files would not be removed when the table is later dropped.
3.Creating an external table
An external table points to files in cloud object storage that you control. When you define one, you must give a storage location, and that location has to fall under an external location registered in Unity Catalog. Unity Catalog still stores the table's metadata and governs access to it. It doesn't manage the table's lifecycle, optimization, storage location or layout. External tables accept more formats than managed tables: DELTA, CSV, JSON, AVRO, PARQUET, ORC and TEXT.
CREATE TABLE <catalog>.<schema>.<table-name>
(
<column-name> <data-type>
)
LOCATION 's3://<bucket-path>/<table-directory>';An external table needs one privilege that a managed table doesn't: CREATE EXTERNAL TABLE on the external location that covers the path. You also need USE CATALOG, USE SCHEMA and CREATE TABLE, the same as for a managed table. Use a separate directory for each table, and keep paths to standard ASCII characters.
Checkpoint 4 of 5· Put it in order
Put the steps for creating an external table in order
- 1.Run CREATE TABLE ... LOCATION 's3://<bucket-path>/<table-directory>'
- 2.Get the CREATE EXTERNAL TABLE privilege on that external location
- 3.Configure an external location that grants access to the cloud storage path
The external location has to exist before you can be granted CREATE EXTERNAL TABLE on it. Only then can a CREATE TABLE point a LOCATION at that path.
“Before you create an external table, you must first configure an external location that grants access to your cloud storage.”Source: docs.databricks.com
4.Dropping tables, and choosing a table type
The drop behavior follows from who owns the file lifecycle. Dropping a managed table removes the metadata, and Unity Catalog later deletes the data files. Until then, UNDROP TABLE can recover the table, by default for 7 days according to the managed-tables page. Dropping an external table removes only the metadata, and the files stay where they were. To drop either type, you must own the table or hold MANAGE on it.
| Feature | Managed tables | External tables |
|---|---|---|
| Storage location | Unity Catalog manages | You specify |
| Data lifecycle management | Unity Catalog manages | You manage |
| Formats supported | Delta Lake, Apache Iceberg | Delta Lake (recommended), CSV, JSON, AVRO, PARQUET, ORC, TEXT |
| Automatic optimizations | Yes | Limited |
| Data deleted on DROP TABLE | Yes | No |
| Best for | Production workloads, frequently queried data | Legacy integrations, existing data |
In most cases, Databricks recommends managed tables because they cost less to store and query and they maintain themselves. Choose an external table in two cases. The first is when you need to register existing data that a managed table can't hold, such as JSON or Avro files. The second is when non-Databricks clients need direct access to the files and don't support other external access patterns. Even for an external table, Databricks recommends the Delta format.
Checkpoint 5 of 5· Exam question
In Unity Catalog, what table type and storage format result from running `CREATE TABLE catalog.schema.tbl (id INT)` with neither a `USING` clause nor a `LOCATION` clause specified?
Correct answer: A — Databricks creates a managed Delta table whose data files are written to the schema's default managed storage location that Unity Catalog controls.
- A. Delta is the default table format when `USING` is omitted, and omitting `LOCATION` makes the table managed, so the data lands in the schema's Unity Catalog managed storage path.
- B. Parquet is not the default format for a bare `CREATE TABLE` statement, and Unity Catalog managed tables do not write to a legacy DBFS root path rather than the schema's own managed storage.
- C. Omitting both `USING` and `LOCATION` produces a managed table, not an external one, so there is no cluster instance profile involved in choosing where the files are written.
- D. CSV is not the implicit default format, and a table created this way already has a fixed schema from the column list rather than one inferred later from a query.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An external table is outside Unity Catalog governance, so its access isn't controlled.Why is that wrong?
Unity Catalog governs access, auditing and lineage for every registered object, including external tables. What external tables lack is Unity Catalog control over the storage lifecycle.
Covered in Two meanings of "managed"
2.CREATE TABLE on the schema is enough to create an external table.Why is that wrong?
You also need the CREATE EXTERNAL TABLE privilege on the external location that covers the LOCATION path.
Covered in Creating an external table
3.Dropping any table in Unity Catalog deletes its data files.Why is that wrong?
Only dropping a managed table deletes the data files. Dropping an external table removes the metadata, and the files stay in cloud storage until you delete them.
Covered in Dropping tables, and choosing a table type
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/data-governance/unity-catalog/managed-versus-externalOfficial docs
“Managed assets: Unity Catalog controls both governance (access control, auditing, lineage) and the underlying file storage lifecycle”
↩︎ Two meanings of "managed"“Unity Catalog stores managed tables in the managed storage location defined on the containing schema, catalog, or metastore.”
↩︎ Creating a managed table“External assets: Unity Catalog controls governance only. The underlying file storage lifecycle is controlled by you or an external system.”
↩︎ Key concept“This applies to all registered Unity Catalog objects, including external tables and volumes.”
↩︎ Exam trap 1“This applies to all registered Unity Catalog objects, including external tables and volumes.”
↩︎ Prediction“When people say that an object is managed by Unity Catalog, they typically mean that Unity Catalog governs access to it.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/tables/managedOfficial docs
“Unity Catalog managed tables are the default and recommended table type in Databricks for Delta Lake and Apache Iceberg.”
↩︎ Creating a managed table“Path-based access to Unity Catalog managed tables is not supported (except in Compatibility Mode) because it bypasses Unity Catalog access controls”
↩︎ Creating a managed table“Unity Catalog supports the UNDROP TABLE command to recover accidentally dropped managed tables. By default, tables are recoverable for 7 days after being dropped.”
↩︎ Dropping tables, and choosing a table type“To create an Apache Iceberg table, explicitly specify USING iceberg. Otherwise, Databricks creates a Delta Lake table by default.”
↩︎ Checkpoint - 3.https://docs.databricks.com/aws/en/tables/externalOfficial docs
“When you define a Unity Catalog external table, you must specify a storage location.”
↩︎ Creating an external table“You need to register a table backed by existing data that isn't compatible with Unity Catalog managed tables, such as JSON or Avro.”
↩︎ Dropping tables, and choosing a table type“The CREATE EXTERNAL TABLE privilege on an external location that grants access to the LOCATION accessed by the external table.”
↩︎ Exam trap 2“Unity Catalog does not delete the underlying data in cloud storage when you drop an external table.”
↩︎ Exam trap 3“Before you create an external table, you must first configure an external location that grants access to your cloud storage.”
↩︎ Checkpoint“Unity Catalog does not delete the underlying data in cloud storage when you drop an external table.”
↩︎ Prediction - 4.https://docs.databricks.com/aws/en/tables/typesOfficial docs
“Unity Catalog external tables support the Delta Lake, CSV, JSON, AVRO, PARQUET, ORC, and TEXT formats.”
↩︎ Creating an external table“Databricks recommends that you use the Delta Lake format because it has transactional guarantees and performance optimizations that the other formats don't.”
↩︎ Dropping tables, and choosing a table type