Optimize Small Files

Difficulty: Easy
Topics: Delta Lake, Performance, OPTIMIZE
Company Tags: Databricks, Any Delta Lake User

Problem Statement

You have a Delta table that has accumulated thousands of small files due to frequent streaming writes. Query performance has degraded significantly.

-- Table info
DESCRIBE DETAIL slow_table;

-- Shows:
-- numFiles: 15,847
-- sizeInBytes: 2,147,483,648  (2 GB total)
-- Average file size: 135 KB  (should be 128+ MB!)

Your task:

Diagnose the small file problem
Optimize the table to consolidate files
Set up proper configuration to prevent this in the future

Setup

-- Check the current state
DESCRIBE DETAIL interview.delta.small_files_table;

-- Check file distribution
SELECT 
    size / 1024 / 1024 as size_mb,
    COUNT(*) as file_count
FROM (
    SELECT input_file_size() as size 
    FROM interview.delta.small_files_table
)
GROUP BY 1
ORDER BY 1;

Constraints

Table has 2GB of data total
Currently has 15,847 files
Target: ~16 files (128MB each)
Minimize write amplification

Hints

Hint 1

OPTIMIZE command consolidates small files into larger ones.

Hint 2

Consider Z-ORDER if there are frequently filtered columns.

Hint 3

Auto-optimization settings can prevent this in the future.

Solution

Click to reveal solution

-- Step 1: Basic OPTIMIZE
OPTIMIZE interview.delta.small_files_table;

-- Step 2: If you have frequently filtered columns, use Z-ORDER
OPTIMIZE interview.delta.small_files_table
ZORDER BY (date_col, category_col);

-- Step 3: Verify improvement
DESCRIBE DETAIL interview.delta.small_files_table;
-- numFiles should now be ~16-20

-- Step 4: Clean up old files
VACUUM interview.delta.small_files_table RETAIN 168 HOURS;

Preventing small files in the future:

-- Option 1: Enable auto-optimize (Databricks)
ALTER TABLE interview.delta.small_files_table 
SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
);

-- Option 2: Tune target file size
ALTER TABLE interview.delta.small_files_table 
SET TBLPROPERTIES (
    'delta.targetFileSize' = '134217728'  -- 128 MB
);

-- Option 3: For streaming, use trigger.processingTime
-- In your streaming write:
-- .trigger(processingTime="5 minutes")  -- Batch more data per write

For Spark SQL (non-Databricks):

-- Set file size at session level
SET spark.sql.files.maxPartitionBytes = 134217728;

-- Repartition before write
INSERT OVERWRITE interview.delta.small_files_table
SELECT /*+ REPARTITION(16) */ * 
FROM interview.delta.small_files_table;

Why small files are bad:

Metadata overhead: Each file has metadata in the transaction log
Task overhead: Spark creates one task per file (min)
Cloud storage: More API calls = more latency and cost
Query planning: Longer time to list and plan files

Target file sizes:

Scenario	Target Size
General tables	128 MB - 1 GB
Frequently updated	64 MB - 256 MB
Archive/cold data	256 MB - 1 GB

Follow-up Questions

When NOT to optimize? - Right before a large write (optimize after)
OPTIMIZE vs VACUUM? - OPTIMIZE compacts, VACUUM deletes old versions
What about partitioned tables? - Can optimize specific partitions: OPTIMIZE table WHERE date = '2024-01-01'

What Interviewers Look For

Problem recognition: Can you diagnose small files from metrics?
Solution: Know the OPTIMIZE command and options
Prevention: Auto-optimize, tuning, streaming best practices
Trade-offs: When to Z-ORDER vs plain OPTIMIZE