Managed vs. External Tables: Data Control

1. Managed vs. External Tables: The Core Concepts

In Databricks, every table has two primary components:

• Metadata: The "data about the data," such as the table name, schema (column names and data types), and location of the data files. This is stored in a metastore, which in modern Databricks is managed by Unity Catalog.
• Data: The actual data files, typically stored in Parquet format in cloud object storage.

The difference between managed and external tables boils down to who is responsible for managing these two components, particularly the data.

Key Differences at a Glance

Based on the documentation, here is a summary of the main distinctions:

The following diagrams provide a great visual summary of these differences.

2. Hands-On: Creating and Inspecting Tables

Now, let's put this into practice. Open a new Databricks notebook and make sure it's attached to a running cluster. We will use SQL to create one of each table type.

Creating a Managed Table

A managed table is the default. Notice that we do not specify a LOCATION in the CREATE TABLE statement. Databricks handles this for us.

Run the following SQL command in a notebook cell:

CREATE OR REPLACE TABLE main.default.managed_table (
  id INT,
  name STRING
);

INSERT INTO main.default.managed_table VALUES (1, 'Alpha'), (2, 'Bravo');

(Note: We are using the main.default catalog and schema, which should be available in your workspace. You can replace this with your own catalog/schema if you prefer.)

Creating an External Table

For an external table, we must provide the LOCATION clause, pointing to a path in your cloud storage where you want the table's data to be stored.

Run the following SQL command in a notebook cell:

CREATE OR REPLACE TABLE main.default.external_table (
  id INT,
  name STRING
)
LOCATION 'dbfs:/mnt/my_data/external_table'; -- Replace with a path you have access to

INSERT INTO main.default.external_table VALUES (3, 'Charlie'), (4, 'Delta');

Important: You might need to change the LOCATION path to a location where you have write permissions. A safe bet is usually within your user directory, like dbfs:/Users/your.email@company.com/external_table.

Inspecting Table Properties

How can we verify that these tables were created as we intended? The DESCRIBE EXTENDED command is perfect for this.

The following video demonstrates how to create both table types and then use DESCRIBE EXTENDED to inspect their properties, confirming their type and location.

Now, try it yourself. Run these commands in your notebook:

-- For the managed table
DESCRIBE EXTENDED main.default.managed_table;

Look for the Type: MANAGED and note the Location path. It will point to a directory managed by Databricks.

-- For the external table
DESCRIBE EXTENDED main.default.external_table;

Here, you'll see Type: EXTERNAL and the Location will be the exact path you specified.

3. The DROP TABLE Experiment: Data Lifecycle in Action

Now for the crucial test: what happens when we drop these tables? This reveals the core difference in data lifecycle management.

The behavior here has a modern twist with Unity Catalog that is essential to know for the certification exam.

Let's replicate this.

Step 1: Drop the Tables
Run the following commands in your notebook:

DROP TABLE main.default.managed_table;
DROP TABLE main.default.external_table;

Step 2: Verify the Data Lifecycle

• External Table: The table definition is gone from the metastore, but the data files remain untouched. You can verify this by listing the files at the location you specified. Run this Python command in a new cell:

  
  
  
  # Remember to use the same path you used in the LOCATION clause
  dbutils.fs.ls('dbfs:/mnt/my_data/external_table')
  

  You will see the Parquet files for your table data are still there. You are responsible for cleaning them up if they are no longer needed.

• Managed Table (with Unity Catalog): As the video explained, when you DROP a managed table in Unity Catalog, the data is not immediately deleted. It is retained for a default period of 7 days. This is a safety feature that allows you to recover accidentally dropped tables using the UNDROP TABLE command. After the retention period, Databricks will garbage-collect and delete the data files automatically.

Conclusion

You have now seen firsthand the fundamental differences between managed and external tables in Databricks. This knowledge is not just theoretical; it directly impacts how you design and manage your data pipelines.

Key Takeaways

• Managed Tables are the default and recommended choice. Databricks manages the entire lifecycle of both the metadata and the data. Use them when the data's lifecycle is fully contained within Databricks.
• External Tables give you direct control over the data files and their lifecycle. Use them when you need to share underlying data with other systems, or when you are registering data that already exists in your cloud storage.
• Location: You specify the LOCATION for an external table; Databricks chooses the location for a managed table.
• Lifecycle (DROP TABLE):
  • DROP EXTERNAL TABLE removes only metadata; the data files remain.
  • DROP MANAGED TABLE (in Unity Catalog) removes the metadata and marks the data for deletion after a retention period, during which it can be recovered with UNDROP.

Next Steps

In this lesson, you used the CREATE TABLE command to define tables. In our next lesson, "Create and drop managed and external Delta tables using SQL," we will delve deeper into the syntax and options available for creating tables, solidifying your ability to manage the schema and structure of your Delta tables effectively.