4.3 Table Creation & Data Modification in Databricks SQL

4.3 Table Creation & Data Modification in Databricks SQL

Databricks SQL provides robust Data Definition Language (DDL) and Data Manipulation Language (DML) capabilities built on Delta Lake, the open-source storage layer that brings ACID (Atomicity, Consistency, Isolation, Durability) transactions, schema enforcement, and time travel to cloud data lakes. Governed by Unity Catalog, all tables created in Databricks SQL adhere to the unified three-level namespace architecture (catalog.schema.table), allowing analysts to organize, query, and modify datasets safely across enterprise environments.

Managed vs. External Tables in Unity Catalog

When creating tables in Databricks SQL, analysts must understand the fundamental architectural distinctions between Managed Tables and External Tables:

1. Managed Tables: Managed tables are the default table type in Unity Catalog. Unity Catalog manages both the table metadata definition in the metastore and the underlying physical cloud storage data files in the schema's default root location. When a user issues a DROP TABLE command on a managed table, Unity Catalog deletes both the metadata record and permanently purges the underlying physical Delta files from cloud storage after a 30-day garbage collection safety window.
2. External Tables: External tables are defined with an explicit storage path using the LOCATION 's3://bucket/path' or LOCATION 'abfss://container@account.dfs.core.windows.net/path' clause. Unity Catalog manages only the table metadata definition. When a user issues a DROP TABLE command on an external table, Unity Catalog removes the metadata record from the catalog, but the underlying physical data files remain completely intact in cloud storage.

Table Creation Patterns (DDL & CTAS)

Databricks SQL supports multiple DDL patterns for creating tables, ranging from explicit schema definitions to automatic creation derived from query results.

1. Explicit CREATE TABLE DDL

Analysts can define tables by explicitly declaring column names, data types (e.g., INT, BIGINT, STRING, DOUBLE, BOOLEAN, DATE, TIMESTAMP, DECIMAL), column comments, and table-level properties.

-- Explicit Schema DDL in Unity Catalog
CREATE TABLE IF NOT EXISTS main.finance.monthly_budget (
    budget_id BIGINT GENERATED ALWAYS AS IDENTITY,
    department STRING COMMENT 'Department cost center name',
    fiscal_year INT,
    fiscal_month INT,
    allocated_amount DECIMAL(12, 2),
    is_approved BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
)
USING DELTA
COMMENT 'Monthly financial budget allocations per department';

2. CREATE TABLE AS SELECT (CTAS)

The CREATE TABLE AS SELECT (CTAS) statement creates a new Delta table and populates it with data returned from a SELECT query in a single atomic transaction. CTAS automatically infers column names and data types from the source query.

-- CTAS Table Creation Pattern
CREATE TABLE main.analytics.high_value_customers AS
SELECT 
    customer_id,
    customer_name,
    country,
    SUM(total_order_value) AS lifetime_value
FROM main.sales.orders
GROUP BY customer_id, customer_name, country
HAVING SUM(total_order_value) >= 10000.00;

3. CREATE TABLE LIKE and Table Cloning

• CREATE TABLE LIKE: Copies the schema, column comments, constraints, and table properties of an existing source table into a new target table without copying any underlying data rows.
• DEEP CLONE: Copies both the table metadata and all underlying physical data files. Deep clones create a completely independent copy of the table.
• SHALLOW CLONE: Copies only the table metadata while referencing the original table's physical data files via Delta log pointers. Shallow clones are instantaneous and consume zero extra storage initially, making them ideal for testing DML operations on production schemas.

-- DDL Copy & Clone Syntax Examples
CREATE TABLE main.testing.orders_skeleton LIKE main.sales.orders;

-- Deep Clone (Full Data Copy)
CREATE TABLE main.testing.orders_deep_backup DEEP CLONE main.sales.orders;

-- Shallow Clone (Metadata Copy only, instant zero-copy)
CREATE TABLE main.testing.orders_staging SHALLOW CLONE main.sales.orders;

Data Modification (DML: INSERT, UPDATE, DELETE, MERGE INTO)

Databricks SQL supports full ACID Data Manipulation Language (DML) operations on Delta Lake tables.

1. INSERT INTO and INSERT OVERWRITE

• INSERT INTO: Appends new rows to an existing table.
• INSERT OVERWRITE: Atomically replaces all existing rows in the table (or a specified partition) with the result of a query, preventing partial data reads during refresh cycles.

2. UPDATE and DELETE

• UPDATE: Modifies column values for rows matching a WHERE clause condition. Delta Lake uses copy-on-write or merge-on-read mechanisms to update affected files transactionally.
• DELETE FROM: Removes rows matching a WHERE clause predicate. Removed rows are marked as deleted in the Delta transaction log and purged during subsequent VACUUM operations.

-- Update active customer tier status
UPDATE main.crm.customers
SET customer_tier = 'Gold'
WHERE total_spend >= 5000.00 AND customer_tier = 'Silver';

-- Delete inactive accounts older than 3 years
DELETE FROM main.crm.customers
WHERE last_login_date < '2023-01-01' AND account_status = 'Pending';

3. Atomic Upserts via MERGE INTO

The MERGE INTO statement allows analysts to perform upsert operations (simultaneous INSERT, UPDATE, and DELETE) from a source dataset into a target Delta table in a single atomic transaction. It matches rows on a join key and executes conditional clauses.

-- Production MERGE INTO Upsert Pattern
MERGE INTO main.inventory.product_stock AS target
USING main.staging.daily_stock_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.action = 'DELETE' THEN
    DELETE
WHEN MATCHED AND source.quantity_on_hand <> target.quantity_on_hand THEN
    UPDATE SET 
        target.quantity_on_hand = source.quantity_on_hand,
        target.last_updated = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, quantity_on_hand, last_updated)
    VALUES (source.product_id, source.product_name, source.category, source.quantity_on_hand, CURRENT_TIMESTAMP());

Best Practices & Exam Strategy

• Default to Managed Tables: For standard Unity Catalog analytical workflows, default to managed tables unless external cloud storage governance requires explicit external table locations.
• Prefer MERGE INTO over Separate Operations: Avoid executing separate DELETE followed by INSERT statements when updating datasets; use MERGE INTO to prevent race conditions and maintain ACID atomicity.
• Use Shallow Clones for Safe Sandbox Testing: Always create a SHALLOW CLONE when testing complex UPDATE or DELETE DML scripts on production tables.