POST_START

Creating and Managing External Delta Tables

I recently needed to create an external Delta table in Databricks Unity Catalog to integrate data from an external source. This table would be used to store sales orders, and I wanted to ensure it was properly managed within the catalog. I started by defining the structure of the table and specifying the location where the data would reside.

CREATE TABLE training.sales.external_orders (order_id BIGINT, customer_id BIGINT, amount DECIMAL(12,2)) USING DELTA LOCATION '/orders';

I verified that the table was created successfully by checking the catalog state. Next, I wanted to understand the structure of the table in more detail, including column data types and comments. I ran the DESCRIBE TABLE EXTENDED command to get this information.

DESCRIBE TABLE EXTENDED training.sales.external_orders;

I noticed that the table had additional columns beyond what I initially defined, which I realized were added by the system for metadata or other purposes. This helped me understand how Unity Catalog manages external Delta tables and their metadata.

To confirm that the table was accessible and contained data, I ran a SELECT query to retrieve the contents of the table.

SELECT * FROM training.sales.external_orders;

I verified that the data was being read correctly and that the table was functioning as expected. Finally, I decided to clean up by dropping the table, as it was no longer needed for this task.

DROP TABLE training.sales.external_orders;

I made sure that the table was completely removed from the catalog and the associated data was no longer accessible. This exercise helped me understand the lifecycle of external Delta tables within Unity Catalog and how to manage them effectively.