Partitioning and compaction in Lakehouse with Fabric

A poorly partitioned Lakehouse costs more on queries than the entire ingestion process.

In bConcepts practice we see this repeatedly: teams build pipelines that land data without thinking about how it will be read. The result is thousands of small files, unnecessary scans and slow reports in Power BI. In this article we describe, with concrete steps and actionable metrics, how to define a partitioning strategy and compaction policies for a Lakehouse on Microsoft Fabric, including heuristics, numerical examples and operational considerations to make these decisions reproducible and safe.

Why partition a Lakehouse?

Partitioning means organizing the physical files of the Lakehouse into folders (directories) that reflect frequently used query predicates — for example, date, country or product category. The immediate gain is called partition pruning: when a query filters by date, the system only reads the relevant folders instead of all the files in the table.

Without proper partitioning, even simple queries can require reading terabytes of data. Imagine a table with 50 TB of history; a dashboard that filters only the last month can, without pruning, result in reading 5–10 TB per execution. If that dashboard is refreshed 2,000 times per month in DirectQuery, the cumulative cost and latency become unacceptable. In practical scenarios we have seen unnecessary reads that multiplied vCore‑hours consumption by 10 and increased response time from 5 seconds to minutes.

Beyond response time and costs, there is operational impact: more files mean more catalog operations, higher probability of I/O conflicts and more complexity in failure recovery. Proper partitioning is one of the first data engineering decisions with direct impact on user experience and monthly platform costs.

How to choose the correct partition key

There is no “magic” key. The choice depends on two main factors: query patterns (what BI queries filter on) and data characteristics (cardinality and distribution). Start by analyzing reports and queries: which filters are most common? Event date, store, country, user, processing state?

Some practical rules we apply in client projects:

• Prioritize columns used in frequent filters by interactive reports (e.g., event date, region). If 70–90% of queries filter by month, partitioning by month substantially reduces reads.
• Avoid partitioning by high‑cardinality columns (e.g., user ID) — this generates too many small folders and efficiency losses. As a rule of thumb, avoid partitions with more than 10k to 100k distinct values; for user counts in the millions, it is not a good idea.
• Consider composite schemes (for example, year=/month=) for temporal data. This approach facilitates purge and maintenance operations: it is simple to remove year=2022 when retention applies annually.
• For semi‑expected filters (e.g., country + category), evaluate using a materialized or aggregated table instead of a composite partitioning that creates many rarely used folders.

Example: in an e‑commerce system, business reports typically filter by month and by country. A partitioning by year/month and, additionally, filter columns such as country and category as regular columns (but ordering‑aware) tends to offer the best compromise. This avoids creating 200+ folders per day (if using day) and ensures most queries touch few directories.

Granularity, cardinality and impact on queries

Partition granularity is a trade‑off between selectivity (how many folders are read) and metadata/file overhead. Too fine partitions lead to the "small files" problem: hundreds of thousands of small files that degrade I/O and increase latency. Too coarse partitions lose selectivity and force reading more data than necessary.

Indicators we monitor to adjust granularity:

• Number of files per partition: if a partition has >10k files, suspect the small files problem. In many cases we aim to have at most 1k–2k files per partition, depending on total data size.
• Average file size: ideal between 128 MB and 512 MB for parquet/Delta files in analytical scenarios. Average values below 50 MB are a warning; below 20 MB is generally unsustainable long term.
• Bytes read per query: evaluate how many GB are read in critical reports. If a KPI requires only 50 MB of aggregated data and the query reads 50 GB, the partitioning (or data design) needs revision.

A useful heuristic: if 80% of queries filter by month, adopt month‑partitions; if many queries filter by hour (e.g., streaming pipelines and alerting), combine day partitions and create auxiliary indexes or aggregated tables for low‑latency scenarios. For telemetry data with 100M events/day, a daily partition with aggressive compaction to produce files of ~256 MB is a common configuration.

Compaction: when, how and with what objectives

Compaction (or "compaction") is the process of rewriting small files into larger, ordered files, reducing file count and improving sequential reads. The goal is not only to reduce files: it is to optimize physical reads and, when it makes sense, improve data ordering (z‑ordering) to accelerate multidimensional filtering and reduce volume read.

When to compact?

• After heavy loads that generate many small files (e.g., ingestions by stream or micro‑batches). For example, if a daily ingestion of 1 TB results in 20k files, it is time to compact.
• When the average file size in a partition is below 50 MB — action plan: rewrite until reaching 128–512 MB per file.
• When the number of files per partition exceeds 5k–10k or when report latency consistently increases.

Compaction strategies:

• Incremental compaction (minor compaction): merges small files within a partition to reduce file count without rewriting the whole partition. Useful when data is append‑heavy and you want to minimize rewrite cost.
• Full compaction (major compaction): rewrites the entire partition to define a new layout and apply ordering/Z‑order. More costly, but necessary periodically to reorganize data over long periods.
• Column ordering (z‑order): when queries combine multidimensional filters (e.g., date + user_id + product_id), ordering by columns with high selectivity can greatly reduce the volume read. Evaluate cost/benefit — ordering is CPU‑intensive.

How to compact safely:

• Make compactions idempotent: read -> repartition/coalesce -> write (atomic mode). Test on a small partition before generalizing. Avoid operations that introduce catalog inconsistencies.
• Reserve low‑usage windows for heavy compactions, or use autoscaling to minimize impact on interactive queries. In Fabric, it is common to run compactions outside peak hours or on temporary clusters sized for the task.
• Use retention policies and VACUUM (remove obsolete files) after rewrite operations to avoid inflating storage. For example, after a major compaction, run VACUUM with 7‑day retention (or per policy) to remove old files and ensure backup/retention integrity.

Compaction is not magic: it is the translation of operational know‑how into response time and cost savings.

Practical implementation in Microsoft Fabric

In Microsoft Fabric we have integrated tools to implement the described strategies: lakehouses (OneLake), Spark notebooks, pipelines and orchestration. The typical flow we recommend follows clear phases:

1. Define partitioning scheme when creating the table in the Lakehouse (for example, partitioned by (year, month)). Document that decision in the catalog and runbooks.
2. Ingest data in append mode to temporary or staging folders to avoid creating too many small files directly in the final partition. Group micro‑batches before promoting to the final partition.
3. Schedule a compaction job (Spark notebook) that reads target partitions and rewrites files with coalesce/repartition to the desired number of files, aligned with the target file size (~256 MB).
4. Run maintenance operations: VACUUM for Delta (or equivalent), and update catalog metadata. Automate alerts when metrics exceed thresholds.

Example of a technical snippet (written generically):

spark.read.format("delta").load("/oneLake/cliente/events/year=2026/month=08") .repartition(50) .write.mode("overwrite").format("delta").option("overwriteSchema", "true") .save("/oneLake/cliente/events/year=2026/month=08")

Practical notes:

• Choose the number of final partitions based on the target size (e.g.: choose repartition(50) because the partition volume is ~12 TB and 12 TB / 50 ≈ 240 GB per collective file, then adjust to obtain ~256 MB per file).
• Use Fabric pipelines to orchestrate notebooks and monitor failures — for example, compact only partitions with more than N files and with average size <50 MB.
• Record metrics (number of files, average size, compaction time) in meta‑operational tables for historical analysis and tuning. A simple dashboard with thresholds (files >5k, avg_size <50 MB, time >2h) prevents regressions.

Mini practical case: online retail that reduces latency and costs

Context: an online retail company with 120 employees processes purchase and browsing events. Ingestion: 200 million events per month (≈2.4 TB of parquet / month). Before the intervention, each daily ingestion produced average files of 6 MB, totaling 400k files per month for the events table.

Observed problem:

• Daily sales report (Power BI, DirectQuery) took on average 90s to load and was executed ~3,000 times/month by analysts and automated dashboards.
• Average read per execution: 150 GB of data read, with high processing costs and poor user experience.

Intervention implemented in 4 weeks:

1. Defined partitions by year/month/day and reconfigured the ingestion process to write first to a staging folder. We reduced direct writes to final partitions and aggregated micro‑batches.
2. Daily compaction of each partition producing average files of 250 MB (using Spark notebooks orchestrated by Fabric pipelines). We implemented continuous minor compactions and a weekly major compaction for active partitions.
3. Cleanup policy: VACUUM to remove obsolete files and 365‑day retention on old partitions; we set alerts for partitions with avg_size <50 MB.

Measurable production results (30 days later):

• Monthly file count reduced from 400k to 8k (–98%).
• Average file size increased from 6 MB to 250 MB.
• Average daily report time fell from 90s to 8s (–91%).
• Bytes read per execution fell from 150 GB to 12 GB (–92%).
• vCore‑hours consumption of the shared query cluster dropped from 1,200 vCore‑h/month to 300 vCore‑h/month (–75%), after accounting for compaction overhead.

Business impact: analysts began exploring data more fluidly, dashboard refresh SLA improved and the monthly cost associated with analytical queries dropped significantly, justifying automation of compaction. Even when accounting for compaction job costs (for example, 40 vCore‑h/month extra), the net gain was substantial — both in performance and cost.

In summary

• Partition by query patterns: prioritize columns BI queries use in frequent filters; for temporal data, use year/month instead of day when relevant.
• Keep files large enough (128–512 MB) to optimize I/O and reduce metadata overhead; compact when avg size <50 MB or files per partition >5k–10k.
• Automate compactions with Spark notebooks and Fabric pipelines, monitoring file and execution time metrics to adjust frequency and avoid regressions.
• Balance costs: compaction consumes compute, but reduces repeated reads and latencies, often yielding net monthly savings and a better user experience.

Conclusion and next steps: the technical strategy described should be treated as iterative. Start by mapping query patterns, implement conservative partitions (e.g.: year/month), automate compaction for problematic partitions and track operational metrics. In Microsoft Fabric, the integration between lakehouses, notebooks and pipelines facilitates this operationalization — but it is the discipline (scheduling, tracking, thresholds) that makes it sustainable.

Which partitions are causing the most reads in your organization today — and what small change could reduce your dashboards’ latency as soon as tomorrow?