Unit 02 of 02
Modeling and Format
Counting what the shape of a table and the format it lands on disk cost a query: the cells stored, the joins and the identical result when facts are separated from dimensions against a single flat table, 2200 joins and 212 dimension rows in the star schema against 4400 joins and 217 rows in the snowflake schema, with the join count doubling to gain five rows, a dimension that keeps no history making the answer wrong without slowing the query and sending 2748 cubic meters to the wrong bucket, with the commercial total coming out 35893 instead of 33145, against history keeping raising the dimension from 200 rows to 2200, the columnar read reading 400 rows against 1400 cells for the row-oriented read when two columns are selected and the point where the gap closes as the column count grows, the cost rising from 4520 to 11000 on a filter outside the partition key, and fine partitioning moving the cost from 520 to 1600 even on a pruning query without raising the cells read at all, and what moving to the archive gains in the hot tier against the question it makes impossible to answer retroactively.
Published Updated
Lessons
- 01 Dimensional Modeling Measuring the cells stored and the joins charged to a query when facts are separated from dimensions: the same 2200 rows hold 15400 cells in a single flat table and 7236 cells in a fact table plus two dimensions, 8164 cells fewer, and the total per type from the two layouts stays exactly the same; in return the query touches one file and pays a cost of 4520, while fact plus dimension touches two files and reads 4800 cells for a cost of 5040, and zero joins become 2200; when a region name is corrected, 506 rows are updated in the flat table and 46 rows in the dimension. 14 min read
- 02 Star and Snowflake Schema Measuring what the degree of normalization gains on the dimension side in rows and what it makes the query pay in joins: when the region is moved to a separate table, dimension rows go from 212 to 217, dimension cells go from 636 to 646, and joins double from 2200 to 4400, while the region total the two schemas produce stays exactly the same; the cost rises from 5040 to 5170, and 120 of the difference is the per-file constant; when the region table carries a single attribute, normalization loses 10 cells, at the second attribute it starts to gain 185 cells, and correcting a region name touches 1 row instead of 46. 14 min read
- 03 Slowly Changing Dimensions Measuring how a dimension that keeps no history makes the answer wrong without slowing the query: when the type of twenty subscribers changes in one period, the overwriting dimension qualifies past periods with today's type, 100 fact rows fall into the wrong bucket, and the commercial total comes out 35893 instead of 33145, sending 2748 cubic meters to the wrong bucket; because the total of the two buckets stays the same at 58454, the fact rows never change and the join count stays fixed at 2200, all four of the schema, type, uniqueness and row count checks stay green, and the distribution check does not see it at a band of 0.25; the price of keeping history is the dimension going from 200 to 2200 rows and the cost going from 7240 to 13440, while the validity interval gives the same correct answer with 220 rows and a cost of 7500. 11 min read
- 04 Columnar File Formats Counting selective reading and compression in terms of cells read: when two columns are selected, the columnar read gives 400 cells and a cost of 520 while the row-oriented read gives 1400 cells and a cost of 1520; as the number of selected columns grows, the gap closes by 200 cells at a time and reaches exactly zero at the seventh column, that is, the gain of the columnar format is as large as the number of columns the query does not read. When compression is modeled in terms of cells read, this closing point comes out of the table: all seven columns come to 851 cells in the columnar layout and 1400 in the row-oriented layout. The gain is the work of the number of runs, not the number of distinct values; the region column carries five distinct values yet gives 158 runs. Ordering within the file is chosen for a single query: ordering by region and type brings that query from 253 cells down to 15 but takes the cubic meter column from 196 back up to 200, that is, to its uncompressed value. 13 min read
- 05 Partitioning and File Layout Counting what the choice of partition key and the fineness of the partition make a query pay: four layouts give exactly the same answer to the same three questions (200, 1122 and 46 rows) but their costs part ways. When the filter falls outside the partition key no file can be pruned, and the cost of 4520 in the unpartitioned layout rises to 11000 in the period plus region layout; the whole of the difference is the per-file constant paid fifty-four times more. Even on a pruning query the fineness carries the cost from 520 to 1000 and to 1600, while the cells read stay at 400 in all three layouts: this is the small file problem, and while the fixed share of the cost rises from 120 to 600 and to 1200, the cell share does not change at all. In three different mixes of the same ten queries the cheapest layout turns out to be three different layouts, so the key is chosen by looking at the query mix and not at the data. 14 min read
- 06 Data Retention Policy Counting what moving to the archive gains and what it closes: in a six-period hot window the hot tier falls from 2200 rows to 1200 and from 55 files to 30, a query with a filter outside the key pays 6000 instead of 11000, and because the 1000 archived rows come down to 25 summary entries the twelve-period region total costs 6675 instead of 11000; the more is archived, the cheaper the fully dated query becomes. The price is in resolution: the region breakdown is answered in all eleven periods and the hot tier plus the archive total comes out exactly equal to the full data, whereas the type, subscriber and region plus type breakdowns are answered in only six periods and 10, 1000 and 50 buckets are closed respectively. An unguarded type query does not raise an error, it returns the commercial total as 16818 instead of 29932 and 13114 cubic meters stay invisible. The lesson is the wrap-up of the course. 7 min read