3.2 Managed vs External Tables and CTAS

When storing structured datasets in Spark SQL, developers must choose between managed and external tables. This architectural decision dictates how Spark coordinates metadata lifecycle with physical storage on distributed cloud object stores such as AWS S3, Azure Data Lake Storage Gen2 (ADLS), or Google Cloud Storage (GCS).

---

1. Managed Tables (Internal Tables)

A managed table (historically called an internal table) is a relation where Spark maintains full, lifecycle ownership over both:

1. The metadata definition stored in the catalog metastore (table schema, column types, partition boundaries, and table properties).
2. The underlying physical data files stored on file systems or cloud object stores.

Default Storage Directory

When creating a managed table, the developer does not provide an explicit storage path. Spark writes the underlying Parquet or Delta files into the default warehouse location configured by spark.sql.warehouse.dir (or the catalog/schema root storage location in Databricks Unity Catalog):

$$\text{Warehouse Path: } \texttt{spark.sql.warehouse.dir} / \texttt{<database\_name>.db} / \texttt{<table\_name>}$$

Creating Managed Tables

In Spark SQL, omitting the LOCATION clause defines a managed table:

-- Create a managed Delta table
CREATE TABLE regional_orders (
    order_id BIGINT,
    customer_id STRING,
    order_amount DECIMAL(10, 2),
    order_date DATE
)
USING delta
PARTITIONED BY (order_date);

In PySpark, using DataFrameWriter.saveAsTable() without specifying an external storage path option produces a managed table:

# Creating a managed table from an existing DataFrame
df.write \
    .format("delta") \
    .mode("overwrite") \
    .partitionBy("order_date") \
    .saveAsTable("regional_orders")

Drop Semantics for Managed Tables

Because Spark owns both the metadata and the physical storage for managed tables, executing DROP TABLE removes the table registration and invokes the managed-data lifecycle; exact deletion/recovery timing depends on the catalog:

DROP TABLE regional_orders;

When this statement completes:

• The table schema, column definitions, and partition statistics are erased from the catalog metastore.
• Managed data is removed or marked for deletion under the catalog lifecycle. Unity Catalog supports recovery during its configured undrop period (default seven days as of this source check). Keep streaming checkpoints outside table data directories; dropping table data is not a checkpoint-management procedure.

2. External Tables (Unmanaged Tables)

An external table (also known as an unmanaged table) is a relation where Spark manages only the catalog metadata. The underlying physical data files reside at an independent cloud storage path managed outside of Spark's warehouse directory.

Creating External Tables

To define an external table, the developer must explicitly supply a storage location using the LOCATION keyword in SQL or .option("path", ...) in PySpark.

-- Create an external Delta table pointing to designated cloud storage
CREATE TABLE external_sensor_telemetry (
    device_id STRING,
    temperature DOUBLE,
    vibration DOUBLE,
    recorded_at TIMESTAMP
)
USING delta
LOCATION 's3://telemetry-data-lakehouse-prod/sensors/';

In PySpark, the DataFrameWriter must supply the designated storage path using .option("path", ...) before calling .saveAsTable():

# Creating an external table pointing to an explicit cloud storage location
df.write \
    .format("delta") \
    .mode("append") \
    .option("path", "s3://telemetry-data-lakehouse-prod/sensors/") \
    .saveAsTable("external_sensor_telemetry")

Drop Semantics for External Tables

The fundamental exam principle regarding external tables is decoupled lifecycle management:

DROP TABLE external_sensor_telemetry;

When this statement executes:

• The metadata pointer in the metastore is dropped.
• The underlying physical files and directories at s3://telemetry-data-lakehouse-prod/sensors/ remain completely untouched.

If the data files were accidentally disconnected, an engineer can immediately re-create the table over the existing path without copying or restoring files from backup:

-- Restores full query access to existing data files
CREATE TABLE external_sensor_telemetry
USING delta
LOCATION 's3://telemetry-data-lakehouse-prod/sensors/';

Identifying Table Ownership at Runtime

To determine whether an existing table is managed or external, query the table's extended metadata:

DESCRIBE EXTENDED external_sensor_telemetry;

In the generated output, locate the Type row:

• Type: MANAGED indicates an internal managed table.
• Type: EXTERNAL indicates an unmanaged external table.

DESCRIBE DETAIL is a Delta-specific command describing format, location and table statistics; it does not provide a generic managed/external tableType column. Use the extended catalog description or spark.catalog.getTable(name).tableType for that distinction.

3. Comprehensive Comparison: Managed vs External Tables

The following table outlines the architectural and operational differences between managed and external tables:

4. Create Table As Select (CTAS) Mechanics

Create Table As Select (CTAS) is a powerful Spark SQL construct that creates a target table and populates it with records returned by an arbitrary query expression with one statement; atomicity depends on the table provider/catalog.

SQL Syntax and Structural Elements

CREATE TABLE high_value_customers
USING delta
PARTITIONED BY (country)
COMMENT 'Aggregated gold tier customer metrics'
TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true', 'retention_days' = '90')
AS
SELECT
    c.customer_id,
    c.full_name,
    c.country,
    SUM(o.total_amount) AS lifetime_spend,
    COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_status = 'DELIVERED'
GROUP BY c.customer_id, c.full_name, c.country
HAVING SUM(o.total_amount) >= 5000.00;

Schema Inference and Typing Rules

CTAS statements exhibit specific rules regarding schema definition:

1. Automatic Schema Propagation: The SELECT projection determines column names and data types. Persisted nullability and enforced constraints depend on the provider; CTAS does not universally copy NOT NULL constraints from source metadata.
2. Alias Requirement: Give computed expressions explicit aliases for clear and provider-compatible column names; this is a practical recommendation, not a universal SQL requirement. For example, use SUM(o.total_amount) AS lifetime_spend. If an alias is omitted, Spark generates synthetic column names like sum(total_amount), which may be awkward or rejected by the selected provider.
3. Prohibition of Explicit Schema Lists: In standard Spark SQL CTAS, specifying an explicit schema list such as CREATE TABLE tbl (id INT, name STRING) AS SELECT ... is invalid syntax and triggers an AnalysisException.

PySpark Programmatic Equivalent

In PySpark, a CTAS operation is achieved by performing DataFrame transformations and writing to a table using .saveAsTable():

from pyspark.sql import functions as F
summary_df = (customers_df.alias("c")
    .join(orders_df.alias("o"), F.col("c.customer_id") == F.col("o.customer_id"))
    .filter(F.col("o.order_status") == "DELIVERED")
    .groupBy(F.col("c.customer_id"), F.col("c.full_name"), F.col("c.country"))
    .agg(F.sum("o.total_amount").alias("lifetime_spend"),
         F.count("o.order_id").alias("total_orders"))
    .filter(F.col("lifetime_spend") >= 5000.0))
(summary_df.write.format("delta").mode("errorifexists")
    .partitionBy("country").saveAsTable("high_value_customers"))

Comparing CTAS to Alternative DDL Patterns

Understanding how CTAS relates to other table creation techniques is frequently tested:

• CTAS vs. CREATE TABLE LIKE: CREATE TABLE new_tbl LIKE existing_tbl; copies the schema, storage format, and partitioning structure of an existing table without copying any data rows. CTAS copies both the schema structure and populates the table with data.
• CTAS vs. INSERT OVERWRITE: INSERT OVERWRITE TABLE target_tbl SELECT ... requires that the target table already exists in the catalog with a compatible schema. CTAS requires that the target table does not already exist (unless combined with CREATE TABLE IF NOT EXISTS ... AS SELECT, which does not overwrite if the table exists).

Official reference

Table drop lifecycle — source checked October 16, 2026. The examples state their runtime assumptions.