Creating and Managing Delta Tables with SQL

1. The CREATE TABLE Command

The CREATE TABLE statement is your primary tool for defining the structure of your data. While there are several variations, the core distinction between creating a managed and an external table comes down to a single clause: LOCATION.

To get a broad overview of the different ways you can create tables in Databricks, the following reading provides excellent, practical examples.

Now, let's break down the two main patterns for creating managed and external tables.

Creating a Managed Table

As you learned previously, managed tables are the default and recommended type in Databricks. Their creation is straightforward because you don't need to worry about the storage path.

The basic syntax defines the table name and its schema (column names and data types).

CREATE TABLE [IF NOT EXISTS] table_name (
  col_name1 data_type1,
  col_name2 data_type2,
  ...
)
USING DELTA; -- This is the default, but it's good practice to be explicit.

When you execute this, Databricks creates the table's metadata in the metastore and stores its data files in a managed storage location associated with the schema you're working in.

Creating an External Table

To create an external table, you use the same fundamental syntax but add the crucial LOCATION clause. This clause tells Databricks where the data files for this table should be stored in your cloud environment.

CREATE TABLE [IF NOT EXISTS] table_name (
  col_name1 data_type1,
  col_name2 data_type2,
  ...
)
USING DELTA
LOCATION 'path/to/your/cloud/storage';

The video below provides a concise demonstration of creating both table types side-by-side.

2. Practice: Create Your Own Tables

It's time for some hands-on practice. Please open a Databricks notebook and execute the following SQL commands.

Step 1: Create a Managed Table
Let's create a managed table to catalog some famous physicists.

-- Ensure you have a schema selected, e.g., by running USE CATALOG main; USE SCHEMA default;
-- Create an empty managed Delta table
CREATE OR REPLACE TABLE physicists_managed (
  id INT,
  full_name STRING,
  field STRING
)
USING DELTA
COMMENT 'Managed table of famous physicists';

-- Insert some data into the table
INSERT INTO physicists_managed VALUES
  (1, 'Albert Einstein', 'Relativity'),
  (2, 'Marie Curie', 'Radioactivity'),
  (3, 'Richard Feynman', 'Quantum Electrodynamics');

-- View the data
SELECT * FROM physicists_managed;

Step 2: Create an External Table using CTAS
Now, let's create an external table. We'll use the Create Table As Select (CTAS) pattern, which creates and populates a new table from the result of a SELECT query. This is a very common technique in ELT pipelines.

Important: You must replace 'path/to/your/location' with a real path in your cloud storage that you have write access to. A good option is a path within your user directory, which looks something like 'dbfs:/Users/your.email@your-company.com/physicists_external'.

-- Create an external table from the managed table's data
CREATE OR REPLACE TABLE physicists_external
USING DELTA
LOCATION 'path/to/your/location' -- <-- IMPORTANT: CHANGE THIS PATH
COMMENT 'External table of famous physicists'
AS SELECT * FROM physicists_managed;

-- View the data
SELECT * FROM physicists_external;

Step 3: Verify Your Work
Use the DESCRIBE EXTENDED command we discussed in the last lesson to confirm that your tables were created correctly.

DESCRIBE EXTENDED physicists_managed;
-- Look at the output. You should see 'Type: MANAGED' and a location path inside a managed storage area.

DESCRIBE EXTENDED physicists_external;
-- Here, you should see 'Type: EXTERNAL' and the exact path you provided in the LOCATION clause.

3. The DROP TABLE Command

Dropping a table is much simpler syntactically—the command is the same for both managed and external tables.

DROP TABLE [IF EXISTS] table_name;

However, as you know from our previous lesson, the consequence is entirely different.

• Dropping a managed table: Deletes both the metadata from the metastore and the underlying data files from the managed location (after a 7-day retention period in Unity Catalog).
• Dropping an external table: Deletes only the metadata. The data files at your specified LOCATION remain untouched.

The following video clearly demonstrates this critical difference.

4. Practice: Clean Up Your Tables

Let's apply the DROP command and verify the outcome.

Step 1: Drop Both Tables
Run these commands in your notebook.

DROP TABLE physicists_managed;
DROP TABLE physicists_external;

Step 2: Verify the Data Locations
Now, check what happened to the underlying data.

• External Table: The data should still be there. Run the following command in a Python cell, using the same path you used for your external table's location.

  
  
  
  # Use the same path you specified in the LOCATION clause earlier
  dbutils.fs.ls('path/to/your/location')
  

  You should see a list of files, including Parquet data files and a _delta_log directory. This confirms that dropping an external table does not delete the data.

• Managed Table: Because you are using Unity Catalog, the managed table's data isn't deleted immediately. You could even recover it using UNDROP TABLE physicists_managed;. However, after the retention period, Databricks would automatically clean up and delete these files for you.

Conclusion

In this lesson, you gained hands-on experience with the fundamental SQL commands for managing the lifecycle of your Delta tables.

Key Takeaways

• Creating a Managed Table: Use CREATE TABLE ... without a LOCATION clause. Databricks manages the data location and lifecycle.
• Creating an External Table: Use CREATE TABLE ... LOCATION 'path/...' to specify where the data lives. You are responsible for managing the data files.
• Dropping Tables: The DROP TABLE table_name command works for both types, but with different results:
  • DROP MANAGED TABLE: Removes metadata and eventually the data.
  • DROP EXTERNAL TABLE: Removes only the metadata, leaving the data intact.

Next Steps

Now that you can create and drop tables, the next logical step is to learn how to modify the data within them. In our next lesson, we will cover the core DML (Data Manipulation Language) operations: INSERT, UPDATE, and DELETE.