LEARN. PRACTICE. GROW.Concept and mechanism
Storage design starts with the questions consumers need to answer. For querying a few fields across many transactions, Parquet offers useful columnar organization; changing format does not remove reconciliation requirements. In serverless SQL, files remain external to the pool. A view does not turn that query into permanent local ingestion. When an OPENROWSET path contains year and month folders, restricting the path or using filepath to select components can reduce reads. Do not confuse TOP with guaranteed partition selection. One folder per payment tends to fragment data and metadata when queries need entire days. Measure files, read volume, and duration using representative data.
Guided application
In a dedicated SQL pool, hash distribution and file partitioning are different choices. Equal hash-key values reach the same distribution; a region representing most rows can create a slow tail. A key aligned with joins can reduce movement but requires concentration analysis. Round-robin helps some loading patterns without guaranteeing customer colocation. In Event Hubs, a stable key can group an account’s events, with hot-partition risk for dominant accounts. Lineage completes the design: Purview captures only supported activities and details. Document custom transformations that are absent, identify versions, and demonstrate the relationship between source and report.
A test with uniform customers passes; the largest real customer represents78% of positions. Repeat distribution comparisons with that concentration before handover.
Common pitfalls
Hash treated as always uniform; views treated as storage; catalog treated as universal lineage.
Related topics: Incremental loads and recovery · Streams, time, and external effects · Lake access and secret management
Choose and measure layout for the actual workload while preserving traceability.
Reference: Serverless SQL storage layout and query efficiency · DP-203 objectives 2024-10-24; retired 2025-03-31