Skip to content
Cheat sheets

Data Engineer Associate · Domain 7: Governance and Security · 15% of the exam

Task 7.1: Differentiate between managed and external tables in Unity Catalog and perform basic operations (create, modify, delete, and convert between managed and external tables) on them.

Where each kind stores its data, what DROP does to it, UNDROP, creating an external table with LOCATION, and converting between the two.

Study it

  • Managed and external tables: create, alter, drop, undrop and convert

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

Two days ago, a data engineer accidentally ran DROP TABLE main.sales.orders on a Unity Catalog managed table. No table with that name has been created since. How can the table be recovered with its data and privileges?

  1. ANo recovery is possible, because dropping a managed table deletes its files at once
  2. BRESTORE TABLE main.sales.orders TO TIMESTAMP AS OF two days ago
  3. CCreate an external table with LOCATION pointing at the old managed storage path
  4. DUNDROP TABLE main.sales.orders
Show the answer and why

  • ANo recovery is possible, because dropping a managed table deletes its files at once

    Incorrect

    That is the behavior of managed tables in the legacy Hive metastore; Unity Catalog marks the data for deletion after a recovery period.

  • BRESTORE TABLE main.sales.orders TO TIMESTAMP AS OF two days ago

    Incorrect

    RESTORE works on an existing table's history; after DROP the name no longer refers to a table.

  • CCreate an external table with LOCATION pointing at the old managed storage path

    Incorrect

    Path-based access to managed table storage is not supported, and the new table would not bring back the dropped table's privileges.

  • DUNDROP TABLE main.sales.orders

    Correct

    UNDROP recovers a dropped Unity Catalog managed or external table within the recovery period, 7 days by default, including privileges and properties.

In Unity Catalog, a dropped managed table stays recoverable for the recovery period. UNDROP restores it, and SHOW TABLES DROPPED helps when several dropped tables share a name.

Databricks documentation

Task 7.1Report a problem with this question

Question 2 · choose 1

A Unity Catalog external Delta table should become a managed table so it benefits from predictive optimization. The table must keep its name, permissions and history. Which command should the owner run?

  1. AALTER TABLE main.sales.orders SET MANAGED COPY
  2. BCREATE TABLE main.sales.orders_managed AS SELECT * FROM main.sales.orders
  3. CCONVERT TO DELTA main.sales.orders
  4. DALTER TABLE main.sales.orders SET MANAGED
Show the answer and why

  • AALTER TABLE main.sales.orders SET MANAGED COPY

    Incorrect

    MOVE and COPY apply only to foreign tables; adding them for an external table makes the command fail.

  • BCREATE TABLE main.sales.orders_managed AS SELECT * FROM main.sales.orders

    Incorrect

    CTAS copies the data into a new table but loses the original table's history and permissions; Databricks recommends SET MANAGED.

  • CCONVERT TO DELTA main.sales.orders

    Incorrect

    CONVERT TO DELTA turns Parquet or Iceberg data into Delta; the table is already Delta, and the command does not change it to managed.

  • DALTER TABLE main.sales.orders SET MANAGED

    Correct

    SET MANAGED converts an external Delta table to a managed table and keeps its name, settings, permissions, views and history.

Converting external to managed is done in place with ALTER TABLE ... SET MANAGED. The MOVE and COPY options exist only for foreign tables.

Databricks documentation

Task 7.1Report a problem with this question

Question 3 · choose 1

A data engineer runs DROP TABLE on a Unity Catalog external table whose files are in s3://lake/sales/orders. What happens to the files?

  1. AThey are deleted after the recovery period
  2. BThey stay in place, and the table's metadata is removed
  3. CThey are deleted at once with the table
  4. DThey are moved to the schema's managed storage location
Show the answer and why

  • AThey are deleted after the recovery period

    Incorrect

    Unity Catalog does not delete the data files of an external table, at once or later.

  • BThey stay in place, and the table's metadata is removed

    Correct

    Dropping an external table removes the metadata; the underlying files remain and must be deleted directly if needed.

  • CThey are deleted at once with the table

    Incorrect

    Unity Catalog does not delete the data files of an external table.

  • DThey are moved to the schema's managed storage location

    Incorrect

    Dropping a table does not move its files anywhere.

Managed and external tables differ in who owns the file lifecycle. For external tables you do, so DROP leaves the data behind.

Databricks documentation

Task 7.1Report a problem with this question

Question 4 · choose 1

A user runs CREATE TABLE main.sales.orders (...) LOCATION 's3://lake/sales/orders' and gets a permission error. The user has USE CATALOG on main and USE SCHEMA on main.sales. Which privileges must the user also have?

  1. AWRITE VOLUME on a volume in main.sales that points at the path
  2. BREAD FILES on the external location that covers the path
  3. CCREATE TABLE on the catalog main, which main.sales inherits from it
  4. DCREATE EXTERNAL TABLE on the location, CREATE TABLE on main.sales
Show the answer and why

  • AWRITE VOLUME on a volume in main.sales that points at the path

    Incorrect

    Volume privileges govern files in volumes, not table locations.

  • BREAD FILES on the external location that covers the path

    Incorrect

    Reading files does not allow registering a table on the path.

  • CCREATE TABLE on the catalog main, which main.sales inherits from it

    Incorrect

    CREATE TABLE is needed, but so is the right to create an external table on the path.

  • DCREATE EXTERNAL TABLE on the location, CREATE TABLE on main.sales

    Correct

    External tables need CREATE EXTERNAL TABLE on the covering external location plus CREATE TABLE, USE SCHEMA and USE CATALOG.

An external table's location must sit inside a Unity Catalog external location, and the creator needs CREATE EXTERNAL TABLE on it.

Databricks documentation

Task 7.1Report a problem with this question

Question 5 · choose 1

The catalog main has a managed location s3://lake/main, and its schema main.sales has a managed location s3://lake/sales. A user creates a managed table in main.sales without a LOCATION clause. Where are its data files stored?

  1. AUnder the schema's managed location, s3://lake/sales
  2. BUnder the catalog's managed location, s3://lake/main
  3. CUnder the metastore's root storage location
  4. DIn a path the user picks the first time data is written
Show the answer and why

  • AUnder the schema's managed location, s3://lake/sales

    Correct

    When the schema has a managed location, managed table data is stored there; lower levels override higher ones.

  • BUnder the catalog's managed location, s3://lake/main

    Incorrect

    The catalog location is used only when the schema has no managed location of its own.

  • CUnder the metastore's root storage location

    Incorrect

    Metastore storage is used only when neither the schema nor the catalog has a managed location.

  • DIn a path the user picks the first time data is written

    Incorrect

    Users do not choose paths for managed tables; choosing a path makes a table external.

Managed storage resolves schema first, then catalog, then metastore. Databricks recommends assigning managed storage at the catalog level for isolation.

Databricks documentation

Task 7.1Report a problem with this question

Question 6 · choose 1

Existing CSV files in s3://lake/legacy/vendors must be queryable as a Unity Catalog table right away, without converting or copying them, because an outside tool still writes them. What should the data engineer create?

  1. AA managed table USING CSV
  2. BA Unity Catalog volume on the path
  3. CAn external table USING CSV with LOCATION set to the path
  4. DA materialized view defined over read_files on the same path
Show the answer and why

  • AA managed table USING CSV

    Incorrect

    Managed tables use Delta Lake or Apache Iceberg; CSV is a format for external tables.

  • BA Unity Catalog volume on the path

    Incorrect

    A volume governs access to the files but does not make them queryable as a table.

  • CAn external table USING CSV with LOCATION set to the path

    Correct

    External tables register existing data in formats such as CSV, JSON, Avro and Parquet without moving it.

  • DA materialized view defined over read_files on the same path

    Incorrect

    A materialized view stores a copy of query results, which the requirement rules out.

External tables fit data that other systems write or that is not in a managed table format. Databricks recommends managed tables otherwise.

Databricks documentation

Task 7.1Report a problem with this question

Practise domain 7 →Practise all domains →