Turn many small files into queryable Delta tables

Small files can make a data-lake workload expensive to list, open, and scan. This 2020 implementation converted frequent JSON and CSV drops into Delta tables, then tested partitioning, deduplication, and compaction against the workload’s query pattern.

The architecture and code are retained as a dated implementation record. Databricks now automates more file-size and layout work, especially for Unity Catalog managed tables.

The original ingestion shape

Near-real-time APIs wrote many small files into an Azure Data Lake Storage raw zone. Azure Data Factory then orchestrated Databricks notebooks that parsed the files and wrote Delta tables.

The design separated the transmission format from the analytical table. That distinction still matters: a format that is convenient for an API response may not be efficient for repeated analytical reads.

Partitioning followed the query pattern

The implementation derived year, month, and day fields from the source timestamp, then wrote a date-partitioned Delta table.

partitioned_df = source_df \
    .withColumn("year", from_unixtime(col("header_timestamp"), "yyyy")) \
    .withColumn("month", from_unixtime(col("header_timestamp"), "MM")) \
    .withColumn("day", from_unixtime(col("header_timestamp"), "dd"))

partitioned_df.write \
    .partitionBy("year", "month", "day") \
    .format("delta") \
    .mode("append") \
    .save(destination_path)

This is not a universal recommendation. Current Databricks guidance recommends liquid clustering for many new tables and automatic tuning for Unity Catalog managed tables. Partitioning remains a workload-specific choice.

Deduplication used a bounded MERGE

The source API could return repeated records. The implementation created a row identifier, then limited the merge comparison to recent partitions rather than scanning the full table.

hashed_df = source_df.withColumn(
    "row_sha2",
    sha2(concat_ws("||", *source_df.columns), 256),
)

delta_table.alias("current").merge(
    hashed_df.alias("incoming"),
    "current.row_sha2 = incoming.row_sha2 "
    "AND to_date(current.header_timestamp) >= current_date() - INTERVAL 1 DAY",
).whenNotMatchedInsertAll().execute()

The key lesson is the boundary, not the exact interval. The team needs a stable identifier and a defensible window for late or repeated records.

Current maintenance is more automatic

Databricks’ current file-size guidance says:

• Unity Catalog managed tables use automatic file-size tuning.
• Optimised writes and auto compaction are enabled for several write operations.
• Predictive optimisation can run OPTIMIZE for managed tables.
• Manual repartitioning before a write is not recommended when optimised writes are used.

For current table layout, Databricks also recommends liquid clustering for many workloads. The current OPTIMIZE guide describes compaction, clustering, and the cost/performance tradeoff.

Validate with the workload’s own evidence

The original project compared the compacted Delta table with the raw-file path and recorded a material improvement for its selected query. The exact figures are omitted here because the source does not provide the cluster, runtime, query, or repeatable benchmark needed to generalize them.

For a current review, capture table type, runtime, file counts, file-size distribution, query plan, maintenance history, duration, and compute cost. That evidence shows whether a layout change helped this workload rather than promising a universal result.