Databricks Certified Data Engineer Associate free question 10 / 10 · Domain 7: Governance and Security
In Unity Catalog there are the external table sales_ext and the foreign table sales_fgn (both in Delta format). You convert both to Unity Catalog managed tables with ALTER TABLE ... SET MANAGED. "Attach nothing" in the choices means adding neither MOVE nor COPY to SET MANAGED. Which combination makes both statements run without an error?
- Attach nothing to sales_ext and attach MOVE to sales_fgn
- Attach MOVE to both sales_ext and sales_fgn
- Attach MOVE to sales_ext and attach nothing to sales_fgn
- Attach nothing to either sales_ext or sales_fgn
Show the answer and explanation
Answer: A
Why this is correct
The way SET MANAGED is written depends on the kind of source table. For an external table it is ALTER TABLE ... SET MANAGED, with no MOVE or COPY. For a foreign table it is ALTER TABLE ... SET MANAGED {MOVE | COPY}, and you must attach one of them. MOVE and COPY decide whether the original table on the side of the external catalog stays usable after the conversion, which does not apply to an external table that has no external catalog.
Why the other options are wrong
"Attach MOVE to sales_ext and attach nothing to sales_fgn" … Both are reversed. Attaching MOVE to an external table fails with DELTA_ALTER_TABLE_SET_MANAGED_UNSUPPORTED_COPY_MOVE_SYNTAX. Attaching neither MOVE nor COPY to a foreign table fails with DELTA_ALTER_TABLE_SET_MANAGED_COPY_OR_MOVE_REQUIRED.
"Attach MOVE to both sales_ext and sales_fgn" … The statement for sales_fgn is right, but the statement for sales_ext fails. MOVE and COPY are for foreign tables, and attaching one to an external table gives DELTA_ALTER_TABLE_SET_MANAGED_UNSUPPORTED_COPY_MOVE_SYNTAX.
"Attach nothing to either sales_ext or sales_fgn" … The statement for sales_ext is right, but the statement for sales_fgn fails. A foreign table needs either MOVE or COPY, and without one you get DELTA_ALTER_TABLE_SET_MANAGED_COPY_OR_MOVE_REQUIRED.
Key point
SET MANAGED converts to a managed table while keeping the name, settings, permissions, views and table history. A conversion with CREATE TABLE AS SELECT is also possible, but the documentation recommends SET MANAGED. Before converting, check whether the source table is an external table or a foreign table with Catalog Explorer or DESCRIBE EXTENDED.