What is `MSCK REPAIR TABLE`

• Purpose – reconciles the gap between partition folders on storage and partition entries in the metastore catalog.
• When needed – when files are written into new partition directories by tools other than the SQL engine (streaming jobs, direct S3 uploads, other team pipelines).
• Behavior – scans the base path, discovers folder names matching the partition pattern, and adds missing entries to the catalog.
• Limitation – plain MSCK only adds; SYNC PARTITIONS adds new and drops stale.

When does the metastore drift from storage?

A partitioned table stores data in a directory layout where each partition value becomes a folder. The metastore holds a list of these partitions so the planner can prune directories. When you write with Spark SQL, the metastore is updated automatically. But external writes create a gap.

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("MSCKDemo").getOrCreate()

# Partition structure on disk:
# /lake/trades/trade_date=2026-10-28/part-0000.parquet  (known)
# /lake/trades/trade_date=2026-10-29/part-0000.parquet  (known)
# /lake/trades/trade_date=2026-10-30/part-0000.parquet  (NEW, added by external job)

# Query for Oct 30 returns nothing because metastore has no entry
spark.sql("SELECT COUNT(*) FROM trades WHERE trade_date = '2026-10-30'").show()

+--------+
|count(1)|
+--------+
|       0|
+--------+

The data is there, but the metastore does not know about it.

How does MSCK REPAIR TABLE fix this?

It scans the base path, finds folders matching the partition column pattern, and adds the missing ones.

spark.sql("MSCK REPAIR TABLE trades")

# Now the query finds the data
spark.sql("SELECT COUNT(*) FROM trades WHERE trade_date = '2026-10-30'").show()

+--------+
|count(1)|
+--------+
|   45000|
+--------+

What does SYNC PARTITIONS do?

Plain MSCK REPAIR only adds partitions. If a folder was deleted, the stale catalog entry remains. SYNC PARTITIONS handles both directions.

# Add new partitions AND drop stale ones
spark.sql("MSCK REPAIR TABLE trades SYNC PARTITIONS")

# This is equivalent to:
# 1. Scan disk for all partition folders
# 2. Compare with metastore entries
# 3. Add missing entries
# 4. Remove entries whose folders no longer exist

When is ALTER TABLE ADD PARTITION better?

MSCK scans the entire directory tree, which is slow and expensive on S3 with millions of partitions.

# Expensive: full directory listing on S3
spark.sql("MSCK REPAIR TABLE trades")  # slow with millions of partitions

# Cheap: add exactly the partition you know about
spark.sql("""
    ALTER TABLE trades
    ADD IF NOT EXISTS
    PARTITION (trade_date = '2026-10-30')
    LOCATION '/lake/trades/trade_date=2026-10-30'
""")

The ALTER approach is O(1) per partition instead of scanning everything.

How do you automate partition registration?

The best pattern is to have the writing job register its own partition.

# The writing job adds the partition immediately after writing
def ingest_daily_trades(trade_date, data):
    path = f"/lake/trades/trade_date={trade_date}"
    data.write.parquet(path)

    spark.sql(f"""
        ALTER TABLE trades
        ADD IF NOT EXISTS
        PARTITION (trade_date = '{trade_date}')
        LOCATION '{path}'
    """)

This eliminates the drift entirely without any MSCK repair.

How does Delta Lake eliminate the need for MSCK?

Delta Lake tracks all files in a transaction log, so it never needs the metastore to list partitions.

# Delta Lake: no MSCK needed, ever
data.write.format("delta").mode("append") \
    .partitionBy("trade_date") \
    .save("/lake/delta/trades")

# New partitions are immediately visible because the transaction log
# records every file, and the metastore is updated automatically
spark.sql("SELECT COUNT(*) FROM delta.`/lake/delta/trades` WHERE trade_date = '2026-10-30'").show()

+--------+
|count(1)|
+--------+
|   45000|
+--------+

When should you use MSCK vs other approaches?

The decision depends on your table format and write pattern.

| Scenario | Approach |
|---|---|
| Hive table, external writes, few partitions | MSCK REPAIR TABLE |
| Hive table, external writes, many partitions | ALTER TABLE ADD PARTITION (per write) |
| Hive table, deleted partitions | MSCK REPAIR TABLE SYNC PARTITIONS |
| Delta Lake table | Not needed (transaction log handles it) |
| Iceberg / Hudi table | Not needed (metadata handles it) |

# Check if the table is Delta or Hive
spark.sql("DESCRIBE EXTENDED trades").show(truncate=False)
# Look for Provider: delta vs hive

What should a senior engineer know?

• MSCK REPAIR is a legacy solution for a Hive-era problem: metadata drift.
• Modern table formats (Delta, Iceberg, Hudi) eliminate the problem entirely.
• On S3, prefer per-partition ALTER over full MSCK for cost and speed.
• Always schedule MSCK or ALTER right after the external write completes.
• The checkpoint and schema location in Auto Loader + Delta Lake is the modern replacement for MSCK + Hive tables.

# Production pattern: schedule repair after the nightly batch
def post_batch_hook(table_name):
    spark.sql(f"MSCK REPAIR TABLE {table_name}")
    # Or better: migrate to Delta Lake

What are common interview follow-up questions?

• Does MSCK REPAIR work with Delta Lake? It is not needed and not applicable. Delta uses its own transaction log.
• Can MSCK REPAIR fail? Yes, if the directory listing is too large it can time out or exhaust Driver memory.
• Does MSCK REPAIR trigger a data read? No, it only lists directories and updates the metastore catalog.