Finance analysts repeatedly filter a payments fact table by citizen-id and period. How should they improve file locality for those filters?

Finance payment facts that are repeatedly filtered by citizen-id and period benefit from Z-ordering (multi-dimensional clustering of related values inside Delta data files). Z-ordering improves file locality so predicates on those columns skip more irrelevant files, cutting scan cost for municipal account-period lookups that dominate payment analytics and citizen service desks.

Partitioning the fact table by citizen-id and period columns for locality would create a massive number of tiny partitions because citizen-id is high-cardinality, causing small-file and listing overhead instead of efficient locality, whereas Z-ordering clusters those values inside files without partition explosion. Enabling bloom filters on the citizen-id and period columns can help skip files for those predicates but does not cluster or co-locate the values to improve file locality, so the analysts' filters still scan scattered data without the locality benefit of Z-ordering. Hash repartitioning on the citizen-id and period columns for improved locality shuffles rows by hash across files but does not sort and co-locate related citizen-id and period values together inside files, so it fails to improve file locality and skipping for the filters that dominate payment analytics.

Exam caveat: Z-ordering targets multi-column locality for selective filters; it is complementary to partitioning/clustering choices and should be applied to the columns that actually appear in WHERE clauses. Operational check: identify the top filter columns on the payments fact (citizen-id, period), run OPTIMIZE … ZORDER BY on those columns, compare bytes scanned for a typical citizen-period query before and after, and schedule Z-order maintenance after substantial loads; partitioning by citizen-id and period would cause partition explosion on high-cardinality citizen-id, so it is not a substitute for Z-ordering for those citizen-period predicates.