Table Management and Properties

Quick Answer: Use ALTER TABLE to modify schemas and properties. TBLPROPERTIES control Delta Lake behavior. DESCRIBE TABLE EXTENDED shows full metadata. COMMENT ON adds documentation for discoverability.

ALTER TABLE Operations

Add and Modify Columns

-- Add a new column
ALTER TABLE orders ADD COLUMN shipping_cost DECIMAL(10,2);

-- Add multiple columns
ALTER TABLE orders ADD COLUMNS (
    discount DECIMAL(10,2),
    coupon_code STRING
);

-- Rename a column
ALTER TABLE orders RENAME COLUMN shipping_cost TO delivery_cost;

-- Change column type (must be compatible: e.g., INT to BIGINT)
ALTER TABLE orders ALTER COLUMN quantity TYPE BIGINT;

-- Add a column comment
ALTER TABLE orders ALTER COLUMN delivery_cost COMMENT 'Cost of shipping in USD';

-- Set column to NOT NULL
ALTER TABLE orders ALTER COLUMN order_id SET NOT NULL;

-- Drop a column
ALTER TABLE orders DROP COLUMN coupon_code;

Table Properties

-- Set table properties
ALTER TABLE orders SET TBLPROPERTIES (
    'delta.deletedFileRetentionDuration' = '30 days',
    'delta.enableChangeDataFeed' = 'true',
    'delta.logRetentionDuration' = '60 days',
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
);

-- Remove a property
ALTER TABLE orders UNSET TBLPROPERTIES ('delta.autoOptimize.autoCompact');

Common Table Properties

DESCRIBE TABLE

-- Basic schema information
DESCRIBE TABLE orders;

-- Extended metadata (location, properties, type, etc.)
DESCRIBE TABLE EXTENDED orders;

-- Column-level detail
DESCRIBE TABLE orders order_id;

-- Show table creation DDL
SHOW CREATE TABLE orders;

Table Documentation

-- Add table comment
COMMENT ON TABLE orders IS 'Production order data from the e-commerce platform. Updated daily via the ETL pipeline.';

-- Add column comments
ALTER TABLE orders ALTER COLUMN total_amount COMMENT 'Total order amount in USD including tax and shipping';
ALTER TABLE orders ALTER COLUMN order_status COMMENT 'Current status: pending, confirmed, shipped, delivered, cancelled';

-- Schema-level comment
COMMENT ON SCHEMA sales IS 'Sales department tables including orders, customers, and products';

DROP and UNDROP

-- Drop a managed table (deletes data AND metadata)
DROP TABLE orders;

-- Drop an external table (deletes metadata only)
DROP TABLE external_orders;

-- Drop only if exists (no error if table missing)
DROP TABLE IF EXISTS temp_staging;

-- Undrop a managed table within retention period (Unity Catalog)
UNDROP TABLE orders;

On the Exam: Know the difference between managed table DROP (deletes data) and external table DROP (keeps data). Understand key TBLPROPERTIES like deletedFileRetentionDuration and enableChangeDataFeed.