A water utility stores high-volume IoT readings and needs prune-friendly filters for leak investigations by date and facility. What modeling choice should they make?

Partitioning IoT readings by date and facility so filters can prune irrelevant files is the correct modeling choice for a water utility that investigates leaks by those dimensions. Partition pruning lets leak queries skip files outside the requested dates and facilities, which is essential for high-volume civic sensor history where full scans are prohibitive. Sorting IoT readings by date and facility so filters can prune irrelevant files fails because sort order alone does not create partition directories for pruning; queries may use file statistics for limited skipping but still open many files within the range instead of skipping to the requested facility. Bucketing IoT readings by date and facility so filters can prune irrelevant files fails for leak investigations because fixed bucket files group rows into a set number of buckets rather than prune-friendly directories, so date and facility filters still scan many buckets and cannot skip irrelevant files effectively. Coalescing IoT readings by date and facility so filters can prune irrelevant files fails because coalesce only merges small files to reduce file count and does not organize data into prune-friendly partitions, so date and facility filters still scan merged files instead of skipping irrelevant ones. Exam caveat: Partition columns should match selective, common filters—not bucketed layouts that place date and facility rows into fixed buckets but still force scanning across buckets, so facility filters cannot prune irrelevant files. Sorting only on date-and-facility combinations leaves data unsplit for pruning and still scans many files; date plus facility partitioning is a classic municipal telemetry compromise. Sorting, bucketing, or coalescing features may reduce files or order rows, but they do not create prune-friendly partitions, so the exam concept remains partitioned layout on investigation dimensions. Operational check: Partition the IoT table by date and facility, run a leak investigation filtered to one facility and a short date range, confirm job metrics show pruned partitions/files, and avoid sort-only, bucketed, or coalesced-only designs for the sensor lake.