ExamFinch.com My Study

Study guide

3.2 Managed vs External Tables and CTAS

Practice this exam

Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Spark Architecture and Execution

1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage

2 Chapter 2: Shuffles, Memory and Persistence

2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels

3 Chapter 3: Spark SQL Sessions, Tables and Views

3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views

4 Chapter 4: SQL Sources and Stored Layout

4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout

5 Chapter 5: SQL Queries and Analytics

5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics

6 Chapter 6: DataFrame Construction and Columns

6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions

7 Chapter 7: DataFrame Validation and Inspection

7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion

8 Chapter 8: Aggregations and Dates

8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions

9 Chapter 9: DataFrame Joins and File IO

9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning

10 Chapter 10: Shared Variables and Stateful Processing

10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores

11 Chapter 11: Troubleshooting and Tuning

11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins

12 Chapter 12: Structured Streaming

12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks

13 Chapter 13: Spark Connect and Deployment

13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices

14 Chapter 14: Pandas API on Spark

14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization

3.2 Managed vs External Tables and CTAS

Key Takeaways Explain This Topic AI

Managed-table drop behavior includes the data lifecycle; physical deletion and recovery depend on the catalog and provider.

An external table uses an explicit location; dropping its catalog registration normally leaves the external data files.

Use DESCRIBE TABLE EXTENDED or catalog.getTable(...).tableType for managed/external classification.

CTAS derives columns from SELECT and creates/populates a table; atomicity and enforced constraints are provider-specific.

Last updated: October 2026

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:

The metadata definition stored in the catalog metastore (table schema, column types, partition boundaries, and table properties).

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): Warehouse Path: spark.sql.warehouse.dir / <database_name>.db / <table_name> \text{Warehouse Path: } \texttt{spark.sql.warehouse.dir} / \texttt{<database\_name>.db} / \texttt{<table\_name>} Warehouse Path: spark.sql.warehouse.dir / <database_name>.db / <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:

  • Operational Dimension Managed (Internal) Tables External (Unmanaged) Tables
  • Metastore Metadata Ownership Owned and managed by Spark/Metastore Owned and managed by Spark/Metastore
  • Physical Storage Ownership Owned and managed by Spark Owned by external storage layer / bucket administrator

Creation Syntax (SQL) CREATE TABLE ... USING format (no LOCATION ) CREATE TABLE ... USING format LOCATION 'path'

Creation Syntax (PySpark) df.write.saveAsTable("name") df.write.option("path", "...").saveAsTable("name")

Storage Directory Location Default warehouse directory ( spark.sql.warehouse.dir ) Arbitrary cloud path ( s3:// , abfss:// , gs:// )

DROP TABLE Behavior Removes metadata and invokes the managed-data deletion/recovery lifecycle Deletes ONLY metadata; data files remain intact

TRUNCATE TABLE Behavior Supported subject to provider rules Generic Spark TRUNCATE TABLE excludes external tables; provider-specific rules differ

Cross-Engine Interoperability Primarily accessed via Spark/Databricks Readily shared with Trino, Snowflake, Presto, or Athena

Governance & Access Control Centralized access enforced by Lakehouse metastore Permissions governed by metastore and cloud IAM bucket policies

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:

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.

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.

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.