* Tags:: #📜Papers, [[Data Warehouses]] * Authors:: [[Benoit Dageville]], [[Thierry Cruanes]], [[Marcin Zukowski]], [[Vadim Antonov]], [[Artin Avanes]], [[Jon Bock]], [[Jonathan Claybaugh]], [[Daniel Engovatov]], [[Martin Hentschel]], [[Jiansheng Huang]], [[Allison W. Lee]], [[Ashish Motivala]], [[Abdul Q. Munir]], [[Steven Pelley]], [[Peter Povinec]], [[Greg Rahn]], [[Spyridon Triantafyllis]], [[Philipp Unterbrunner]] * Link:: https://dl.acm.org/doi/10.1145/2882903.2903741 * Zotero link:: [dagevilleSnowflakeElasticData2016](zotero://select/items/@dagevilleSnowflakeElasticData2016) * Source date:: [[2016-06-4]] * Read date: [[2022-07-01]] ![[Pasted image 20220705071057.png|400]] Where the data storage is S3, and the virtual warehouses are the [[shared-nothing]] compute engines, with local data only for [[LRU]] caches. ## Data storage Influenced by S3. Tables are horizontally partitioned into **inmutable files** ("equivalent to pages in a traditional database system"). Within each file, columns are grouped together and compressed and there is a header with among other metadata, the offset of each column in the file. Since **S3 allows to download parts of files**, compute nodes only need to download the headers and columns they need. The query optimizer assigns input file sets to compute nodes to avoid redundant caching. S3 is also used to "spill data to disk" for large queries. ### Semi structured data Snowflake keeps track of common query paths in semistructured data and removes such paths from the document and stores them in the same compressed columnar format as native relational data. Along with other optimizations, the paper claims that: >...the query performance over semi-structured data with relatively stable and simple schemas (i.e. the majority of machine-generated data found in practice), is nearly on par with the performance over conventional relational data. ## Execution engine Unlike MapReduce, it is: - Vectorized (see https://www.cidrdb.org/cidr2005/papers/P19.pdf) - Push execution model in query operators vs volcano-like pull model. Making easier for Snowflake to handle DAGs ("with" operations, subqueries...) ([Volcano paper](https://paperhub.s3.amazonaws.com/dace52a42c07f7f8348b08dc2b186061.pdf), [[Volcano model]]). In layman's terms ([Query Engines: Push vs. Pull (justinjaffray.com)](https://justinjaffray.com/query-engines-push-vs.-pull/)): ![[Pasted image 20220721071622.png]] >In a _pull based_ system, _consumers_ drive the system. Each operator produces a row when asked for it: the user will ask the root node (`Distinct`) for a row, which will ask `Map` for a row, which will ask `Select` for a row, and so on. > In a _push based_ system, the _producers_ drive the system. Each operator, when it has some data, will tell its downstream operators about it. `customer`, being a base table in this query, will tell `Select` about all of its rows, which will cause it to tell `Map` about of _its_ rows, and so on. ## "Cloud services" ### Query optimization This is how Snowflake calls the part of their offering that performs query optimization. The interesting thing here is that they focus on: >makes the design principles of Snowflake: it does not rely on user input, it scales well, and it is easier to maintain (...) makes the system easier to use (performance becomes more predictable) In fact, the claim something pretty amazing: >As a result, today, **Snowflake has only one tuning parameter: how much performance the user wants** (and is willing to pay for). This leads them to not use indices (and having less decisions to make). Reminded me of the [[Iatrogenics]] in [[📖 Antifragile]]. It also performs pruning (limiting access only to data relevant to the query) through min-max based prunning (also known as small materalized aggregates, zone maps, and data skipping), even for (some) semi-structured data, and even when performing joins: >...the system maintains the data distribution information [for each table data file] in particular minimum and maximum values within the chunk. Depending on the query predicates, these values can be used to determine that a given chunk of data might not be needed for a given query. The effectiveness of this depends on how the data is partitioned in table files, but the paper does not really mention how that is done by Snowflake. The official docs offer a bit more of information ("Tables are transparently partitioned using the ordering of the data as it is inserted/loaded."), but without detail (e.g., if you don't manually indicate and order, "clustering", of a table, which criteria is followed to split the data into files?). See [[Snowflake#Partitioning clustering]]. ### Concurrency Snapshot Isolation (SI) on top of Multi-Version concurrency control (MVCC). >Changs to a file can only be made by uploading it with a different file that includes the change.