POST_START

Creating and Managing External Delta Tables with Unity Catalog

I recently needed to integrate external data into my Delta Lake pipeline using Databricks Unity Catalog. The data came from a third-party system, and it was stored in a shared location that wasn’t managed by Databricks. I decided to create an external Delta table to access and manage this data within my Unity Catalog environment.

Creating the External Delta Table

I started by defining the structure of the data. The external data contained order details with order IDs, customer IDs, and amounts. I used the CREATE TABLE command to register this data as an external Delta table in Unity Catalog.

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

I chose the LOCATION parameter to point to the shared folder where the data was stored. This allowed me to access the data without moving it. I also made sure to assign the correct schema and data types to ensure compatibility with downstream processing.

Verifying the Table Definition

After creating the table, I wanted to confirm that the metadata was correctly registered in Unity Catalog. I ran the DESCRIBE TABLE EXTENDED command to view the full details of the table.

DESCRIBE TABLE EXTENDED training.sales.external_orders;

The output showed me the table’s location, format, and schema, which helped me understand how the table was structured and where it was stored. This step was crucial for debugging and ensuring that the table was properly registered.

Querying the External Data

Next, I wanted to test if I could query the data through the new table. I ran a simple SELECT statement to retrieve all the rows.

SELECT * FROM training.sales.external_orders;

The query returned the expected results, confirming that the external Delta table was correctly pointing to the data and that I could access it like any other Delta table in Unity Catalog. This gave me confidence that the data was ready for further processing and analysis.

Managing the Table Lifecycle

As part of my workflow, I also considered the possibility of removing the table if it was no longer needed. I decided to test the DROP TABLE command to understand its impact.

DROP TABLE training.sales.external_orders;

I noticed that the table itself was removed from Unity Catalog, but the underlying data in the external location remained untouched. This behavior is intentional, as external tables do not manage the data files. This distinction was important to understand when planning for data retention and cleanup.

Throughout this process, I learned how to effectively use Unity Catalog to manage external Delta tables, ensuring that I could access, query, and manage data from external sources without altering their original location. This approach is especially useful in environments where data is shared across teams or systems.