Skip to content
academia.sh
tr
Log in

Course Intermediate

Data Storage Architectures

Published Updated

By the end of this course

Start course

01

Storage Approaches

Counting what storing the same data in four different approaches costs on the write and read sides: the row enforcement rejects and the cells a query reads in schema-conforming storage, writing being cheap and reading expensive in raw storage and which class of corruption cannot be caught at write time at all, comparing the detection lag with the same unit when the raw layer is kept and schema enforcement moves to read time, the query cost and the number of places the same row stands when a subset belonging to a region is written separately, the number of parties making the layout decision when ownership is distributed and the number of layouts an end-to-end query touches, and which results are sensitive to the per-file constant when it is swept from 60 to 480, the number of entries looked at to find an asset with and without a catalog and how many entries of the catalog now point to a layout that no longer exists.

  1. 01 Data Warehouse Counting what a query pays to touch the layout that the 2200 rows leaving the pipeline are written into: on the unpartitioned, row-oriented baseline a period-filtered query costs 15400 cells and 15520 cost, 4400 cells and 4520 cost when only the selected columns are read, and 400 cells and 520 cost under period partitioning. The same layout gives 5720 instead of 4520 on a filter outside the partition key, so a layout gains for one query and loses for another. Schema enforcement on write catches two of the six corruption classes and rejects 400 rows, yet in all 400 of the rejected rows the measurement is still intact; the schema throws away the row, not the number. The per-file constant of 120 is a choice: the 4000-cell gain on the period query is independent of the constant, while the 1200 loss on the other query is entirely the constant's doing and equals ten files.
  2. 02 Data Lake Counting what storing raw data as it is gives the write side and costs the read side: the lake writes 2400 rows into 14400 cells without any transformation, while the warehouse processes 7200 rows to write 2200 rows into 15400 cells; the same period query gives 14400 cells and 14520 cost on the lake and 400 cells and 520 cost on the warehouse, so the 1000 cells the lake gains on write are paid back as 14000 cells in a single query. Of the six corruption classes only two can be seen by looking at a single row; two need the whole set and two need the aggregate, so no row gate at write time can see four of them, and a unit change is invisible in both the row and the set scope. Two consumers reading the same lake layer find 1800 and 2200 rows, 45 and 55 aggregate cells, 48004 and 58454 cubic meters.
  3. 03 Lakehouse Approach Counting what moving schema enforcement to read time gains and loses while the raw layer is preserved: the write gate catches two of the six corruption classes with zero stages of lag and never catches four, the setup with no enforcement catches none of the six, the setup that enforces at read time catches all six and its mean lag becomes 1.33 stages. The rise of the mean is not a deterioration but four undefined values becoming finite. Under a field-name change the setup that preserves the raw layer produces 2200 curated rows, while the setup that screens at the gate rejects 200 rows and stays at 1800; the cost is stored cells being 29800 instead of 12600. A unit change is written to the curated layer without catching on any gate and is seen only at the fourth stage.
  4. 04 Data Mart Counting what a query costs and in how many places the same row stands when a subset dedicated to a single unit is written separately: on a set of three questions the unpartitioned warehouse pays 13560, the best central layout 3256, two marts 2580, and the mart's gain is zero on the region question and 676 on the type question. The cost is multiplication: six marts store 3322 more rows, 2.51 copies per row, and 1122 rows stand in three separate places. When a late-arriving record is processed into the warehouse, the six unrefreshed marts diverge on 126 (row, place) pairs, and on a half-refreshed set the five region marts total 4707 while the warehouse total is 5340.
  5. 05 Data Fabric and Data Mesh Measuring the two numbers that change when ownership is distributed: the number of parties that make the layout decision and the number of layouts an end-to-end query touches. The central setup gives 1/1, the data fabric 1/5, the data mesh 5/5; on a mix of five local questions plus one countrywide question the cost comes out 3120, 2000 and 6280. The mesh is expensive not because of distribution but because of unmeasured key choices: the decision of two units alone costs 2450. The lesson sweeps the per-file constant over 60, 120, 240 and 480: the two numbers are independent of the constant, the ranking is not — the constant at which the fabric and the central setup are equal is 400, and when a single unpruned question is added the distributed setups lose at all four values.
  6. 06 Metadata Catalog Counting the entries looked at to find a data asset with and without a catalog: on a set of twelve assets and 119 files five searches look at 295 entries without a catalog and 29 with one, and the most expensive search drops from 117 to 11. When the catalog goes stale the number comes back: after three assets change, three of the twelve entries point to a layout that no longer exists, the same five searches cost 406 without a catalog and 251 with the stale one, but two searches end up more expensive than without a catalog (122 against 129 and 100 against 104), because the dead entry is scanned and the full scan is still done afterwards.

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

Final exam

Data Storage Architectures — Final Exam

In preparation

This course's question bank is not complete yet. The exam opens here automatically once enough questions are added.

Start typing to search.

↑↓ ↵ Esc navigate · open · close