How do you design a partitioning strategy for time-series data in Fabric lakehouse?

• A. Partition by time (e.g., daily/monthly), incorporate partition pruning, balance partition count to avoid small files, and manage partition metadata for efficient queries.
• B. Partition by time with random hash of the data.
• C. Do not partition time-series data.
• D. Partition by user ID.

Answer: (A)

Time-based partitioning is essential for efficient time-series queries in a Fabric lakehouse. When data is organized by time, queries that filter on a date or time range can skip entire partitions that don’t match, thanks to partition pruning. This means you read far less data and get faster results.

Choosing a time-based scheme, like daily or monthly partitions, gives predictable shard sizes, makes retention policies easier, and helps with archiving. But it’s important to balance how many partitions you create: too many partitions leads to overhead from managing metadata and many small files, while too few partitions reduce the effectiveness of pruning.

Managing partition metadata is also crucial. As new data arrives, you need to register new partitions in the catalog and keep statistics up to date so the query engine can accurately prune and optimize scans.

Alternative approaches miss these benefits. Randomly hashing data breaks the time-based pruning idea, causing queries to scan unnecessary data. Not partitioning at all forces full dataset scans. Partitioning by user ID doesn’t align with the common time-range access patterns of time-series data and limits pruning opportunities.