A report queries a wide Lakehouse table of digitised-object metadata that has over a hundred columns, including large free-text conservation-note fields, but the report only ever needs five specific columns such as object ID, category, and current branch. The query currently uses SELECT * and is notably slower than reports of similar row count that select only a few columns. What is the most direct explanation, and the most direct fix?
Select an answer to reveal the explanation.
Short Explanation
A columnar file format is organized so each column's values are stored together, like a spreadsheet where you can grab just one column without touching the others. Asking for every column, including some bulky free-text ones, means reading data you're not even going to use. Naming only the five columns actually needed lets the engine skip the rest entirely — and with wide text fields in the mix, that's often a big chunk of the bytes.
Full Explanation
Delta tables store data in columnar Parquet files, which group each column's values together rather than storing whole rows contiguously. Naming only the needed columns lets the engine read just those from each file, skipping the rest, while SELECT forces it to read every column even though most get discarded before use — and with large free-text fields among them, that wasted volume can dwarf what's actually needed. Selecting only the required columns is the direct fix because it cuts bytes scanned without touching table layout. The claim that SELECT always forces a full scan, fixable only by partitioning, ignores that column pruning works independently of partitioning: partitioning controls which files get read, column selection controls which columns within those files get read. V-Order is a write-time encoding with no per-query toggle, so a query's shape can't disable it. And SELECT doesn't route work to a different engine — the SQL analytics endpoint and Spark both execute a query as written against the same files. A caveat: on a workload that genuinely needs most columns most of the time, this benefit shrinks. A concrete check: compare bytes scanned and duration for the SELECT version against the five-column version in the engine's execution statistics.