Microsoft Fabric: Parquet Format (Notes)

Disclaimer: This is work in progress intended to consolidate information from various sources. 

Last updated: 31-Jan-2024

Parquet format

• {definition} open source, column-oriented data file format designed for efficient data storage and retrieval [1]
• provides efficient data compression and encoding schemes with enhanced performance to handle complex data in bulk [1]
• designed to be a common interchange format for both batch and interactive workloads [1]
• {characteristic} open source file format
• similar to other columnar-storage file formats available in Hadoop [1]
• e.g. RCFile, ORC
• became an industry standard 
•  {benefit} provides interoperability across multiple tools
• {characteristic} language agnostic [1]
• different programming languages can be used to manipulate the data
• {characteristic} column-based format [1]
• files are organized by column
• ⇐ rather than by row
• ⇒ saves storage space and speeds up analytics queries [1]
•  reads only the needed columns 
• ⇐ non-relevant data are skipped
• ⇒ greatly minimizes the IO [1]
• aggregation queries are less time-consuming compared to row-oriented databases [1]
• {benefit} increased data throughput and performance [1]
• ⇒ recommended for analytical workloads
• {characteristic} highly efficient data compression/decompression [1]
• supports flexible compression options and efficient encoding schemes [1]
• data can be compressed by using one of the several codecs available [1]
• ⇒ different data files can be compressed differently [1]
•  reduced storage requirements [1]
• by at least one-third on large datasets
• ⇒ {benefit} saves on cloud storage space
•  greatly improves scan and deserialization time [1]
• ⇒ {benefit} reduces the processing costs
• {downside} can be slower to write than row-based file formats
• primarily because they contain metadata about the file contents 
• though have fast read times
• {characteristic} supports complex data types and advanced nested data structures [1]
• implemented using the record-shredding and assembly algorithm
• accommodates complex data structures that can be used to store the data [1]
• optimized to work with complex data in bulk and features different ways for efficient data compression and encoding types [1]
• the approach is best especially for those queries that need to read certain columns from a large table [1]
• {characteristic} cloud-ready
• works best with interactive and serverless technologies [1]
• {characteristic} immutable
• a file can't be update to modify the column name, reorder or drop columns [2]
• ⇐ requires rewriting the whole file [2]
• {characteristic} binary-based file
• ⇒ not easily readable (by humans)
• {characteristic} self-describing 
•  contains metadata about schema and structure
• {concept} row groups (aka segments) 
• contains data from the same columns
• {constraint} column names are case sensitive
• {concept} file footer 
• stores metadata statistics for each row group [2]
• min/max statistics 
• the number of rows
• can be leveraged by data processing engines to run queries more efficiently [2]
• ⇐ depending on the query, entire row group can be skipped [2]
• {concept} file header
•  large datasets can be split across multiple parquet files
• ⇐ the structure can be flat or hierarchical 
• managing multiple files has several challenges
• the files can be used to define a table (aka parquet table)
• ⇐ {constraint} the files must have the same definition
• ⇐ schema enforcement must be coded manually [2]
• {limitation} [Data Lake] no support for ACID transactions [2]
• ⇒ easy to corrupt [2]
• partially written files will break any subsequent read operations
• the compute engine will try to read in the corrupt files and error out [2]
• corrupted files must be manually identified and deleted manually to fix the issues [2]
• {limitation} it's not easy to delete rows from it [2]
• requires reading all the data, filtering out the data not needed, and then rewriting the entire table [2]
• {limitation} doesn't support DML transactions [2]
• {limitation} there is no change data feed [2]
• {limitation} slow file listing [2]
• small files require excessive I/O overhead
• ideally the files should be between 64 MB and 1 GB
• ideally the files should be compacted into larger files (aka small file compaction, bin-packing)
• {limitation} expensive footer reads to gather statistics for file skipping [2]
• fetching all the footers and building the file-level metadata for the entire table is slow [2]
• ⇐ it requires a file-listing operation [2]
• the effectiveness of data skipping depends on how many files can be can skipped when performing a query [2]
• {limitation} doesn't support schema enforcement [2]
• {limitation} doesn't support check constraints [2]
• {limitation} doesn't support data versioning [2]
• {concept} table partitioning
• {definition} common optimization approach used to store the data of the same table in different directories, with partitioning column values encoded in the path of each partition directory [6]
• {recommendation} avoid partitioning by columns with very high cardinality
• {concept} bin-packing (aka compaction, bin-compaction)
• aims to produce evenly-balanced data files with respect to their size on disk, 
• ⇐ but not necessarily in respect to the number of tuples per file [7]
• requires an algorithm that efficiently organizes the files into equal size containers [6]
• {characteristic} idempotent
•  if it is run twice on the same dataset, the second run has no effect [7]
• {feature} [Microsoft Fabric] V-order
• {definition} write time optimization to the parquet file format that enables lightning-fast reads under the MF compute engines [3]
• applies special sorting, row group distribution, dictionary encoding and compression on parquet files [3]
• requires less compute engines resources in to read it [3]
• provides further cost efficiency and performance
• has a 15% impact on average write times but provides up to 50% more compression [3]
• {characteristic} open-source parquet format compliant
• all parquet engines can read it as a regular parquet file [3]
• ⇐ table properties and optimization commands can be used on control V-Order on its partitions [3]
• compatible with other features [3]
• applied at parquet file level [3]
• enabled by default
• {command} OPTIMIZE
• merges all changes into bigger, consolidated parquet files (aka bin-compaction) [3]
• [Spark] dynamically optimizes partitions while generating files with a default 128 MB size [5]
• the target file size may be changed per workload requirements using configurations [5]
• properly designing the table physical structure based on the ingestion frequency and expected read patterns is likely more important than running the optimization command [3]
• running the compaction operation brings the data lake in an unusable state for readers [7]
• {warning} manually compacting the files is inefficient and error prone [7]
• no way to differentiate files that contain new data from files that contain existing data that was just compacted into new files [7]
• [Delta Lake] when ZORDER and VORDER are used together, Apache Spark performs bin-compaction, ZORDER, VORDER sequentially [3]

Previous Post <<||>> Next Post

Acronyms:
ACID - atomicity, consistency, isolation, durability
IO - Input/Output

MF - Microsoft Fabric
ORC - Optimized Row Columnar
RCFile - Record Columnar File

Resources:
[1] Databricks (2023) What is Parquet? (link)
[2] Data Lake (2023) Delta Lake vs. Parquet Comparison (link)
[3] Data Mozart (2023) Parquet file format – everything you need to know! (link)
[4] Microsoft Learn (2023) Query Parquet files using serverless SQL pool in Azure Synapse Analytics (link)
[5] Microsoft Learn (2023) Lakehouse tutorial: Prepare and transform data in the lakehouse (link)
[6] Apache Spark (2023) Spark SQL Guide (link)
[7] Delta Lake (2023) Delta Lake Small File Compaction with OPTIMIZE (link)
[8] Delta Lake (2023) Optimizations (link)

DBMS: Event Streaming Databases (More of a Kafka’s Story)

Event streaming architectures are architectures in which data are generated by different sources, and then processed, stored, analyzed, and acted upon in real-time by the different applications tapped into the data streams. An event streaming database is then a database that assures that its data are continuously up-to-date, providing specific functionality like management of connectors, materialized views and running queries on data-in-motion (rather than on static data). 

Reading about this type of technologies one can easily start fantasizing about the Web as a database in which intelligent agents can process streams of data in real-time, in which knowledge is derived and propagated over the networks in an infinitely and ever-growing flow in which the limits are hardly perceptible, in which the agents act as a mind disconnected in the end from the human intent. One is stroke by the fusing elements of realism and the fantastic aspects, more like in a Kafka’s story in which the metamorphosis of the technologies and social aspects can easily lead to absurd implications.

The link to Kafka was somehow suggested by Apache Kafka, an open-source distributed event streaming platform, which seems to lead the trends within this new-developing market. Kafka provides database functionality and guarantees the ACID (atomicity, concurrency, isolation, durability) properties of transactions while tapping into data streams. 

Data streaming is an appealing concept though it has some important challenges like data overload or over-flooding, the complexity derived from building specific (business) and integrity rules for processing the data, respectively for keeping data consistency and truth within the ever-growing and ever-changing flows. 

Data overload or over-flooding occurs when applications are not able to keep the pace with the volume of data or events fired with each change. Imagine the raindrops falling on a wide surface in which each millimeter or micrometer has its own rules for handling the raindrops and this at large scale. The raindrops must infiltrate into the surface to be processed and find their way to the beneath water flows, aggregating up to streams that could nurture seas or even oceans. Same metaphor can be applied to the data events, in which the data pervade applications accumulating in streams processed by engines to derive value. However heavy rains can easily lead to floods in which water aggregates at the surface. 

Business applications rely on predefined paths in which the flow of data is tidily linked to specific actions found themselves in processual sequences that reflect the material or cash flow. Any variation in the data flow from expectations will lead to inefficiencies and ultimately to chaos. Some benefit might be derived from data integrations between the business applications, however applications must be designed for this purpose and handle extreme behaviors like over-flooding. 

Data streams are maybe ideal for social media networks in which one broadcasts data through the networks and any consumer that can tap to the network can further use the respective data. We can see however the problems of nowadays social media – data, better said information, flow through the networks being changed as fit for purposes that can easily diverge from the initial intent. Moreover, information gets denatured, misused, overused to the degree that it overloads the networks, being more and more difficult to distinguish between reliable and non-reliable information. If common sense helps in the process of handling such information, not the same can be said about machines or applications. 

It will be challenging to deal with the vastness, vagueness, uncertainty, inconsistency, and deceit of the networks of the future, however data streaming more likely will have a future as long it can address such issues by design. 

DBMS: NoSQL (Definitions)

"An umbrella term for non-relational data stores, hence the name. These stores sacrifice ACID transactions for greater scalability and availability." (Dean Wampler, "Functional Programming for Java Developers", 2011)

"A set of technologies that created a broad array of database management systems that are distinct from relational database systems. One major difference is that SQL is not used as the primary query language. These database management systems are also designed for distributed data stores." (Marcia Kaufman et al, "Big Data For Dummies", 2013)

"A class of database management systems that consist of non-relational, distributed data stores. These systems are optimized for supporting the storage and retrieval requirements of massive-scale data-intensive applications." (IBM, "Informix Servers 12.1", 2014)

"A database that doesn’t adhere to relational database structures. Used to organize and query unstructured data." (Jason Williamson, "Getting a Big Data Job For Dummies", 2015)

"Any of a class of database management systems that reject the limitations and drawbacks dictated by, or associated with, the relational model. NoSQL products tend to specialize in a single or limited number of areas, such as high-performance processing, big data (giga-record systems), diverse data types (video, pictures, mathematical models), documents, and so on. Their specialized focus often requires deemphasizing other areas such as data consistency and backup and recovery." (George Tillmann, "Usage-Driven Database Design: From Logical Data Modeling through Physical Schmea Definition", 2017)

"In general, NoSQL databases provide a mechanism for storage and retrieval of data modeled in means other than the tabular relations used in relational databases." (Prashant Natarajan et al, "Demystifying Big Data and Machine Learning for Healthcare", 2017)

"NoSQL means 'not only SQL' or 'no SQL at all'. Being a new type of non-relational databases, NoSQL databases are developed for efficient and scalable management of big data." (Zongmin Ma & Li Yan, "Towards Massive RDF Storage in NoSQL Databases: A Survey", 2019)

"A broad term for a set of data access technologies that do not use the SQL language as their primary mechanism for reading and writing data. Some NoSQL technologies act as key-value stores, only accepting single-value reads and writes; some relax the restrictions of the ACID methodology; still others do not require a pre-planned schema." (MySQL, "MySQL 8.0 Reference Manual Glossary")

"A NoSQL database is distinguished mainly by what it is not - it is not a structured relational database format that links multiple separate tables. NoSQL stands for 'not only SQL', meaning that SQL, or structured query language is not needed to extract and organize information. NoSQL databases tend to be more diverse and flatter than relational databases (in a flat database, all data is contained in the same, large table)." (Statistics.com)

"NoSQL is a database management system built for the complexities of working with Big Data. Unlike SQL, NoSQL does not store data in a relational format." (Xplenty) [source]

"No-SQL (aka not only SQL) database systems are distributed, non-relational databases designed for large-scale data storage and for massively-parallel data processing across a large number of commodity servers." (IBM) 

"NoSQL is short for 'not only SQL'. NoSQL databases include mechanisms for storage and retrieval of data based on means other than the tabular relations used in relational databases." (Idera) [source]

"sometimes referred to as ‘Not only SQL’ as it is a database that doesn’t adhere to traditional relational database structures. It is more consistent and can achieve higher availability and horizontal scaling." (Analytics Insight)

DBMS: Atomicity (Definitions)

"One of the ACID properties; all or none of the transaction must occur. If all parts of the transaction cannot occur successfully, all effects of the transaction must be undone or 'rolled back'." (Atul Apte, "Java Connector Architecture: Building Custom Connectors and Adapters", 2002)

"Atomicity is a feature provided by transactions. It is a principle that states either all of the transactions’ data modifications are performed or none of them are performed." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

[atomic transaction:] "A possibly complex series of actions that is considered as a single operation by those not involved directly in performing the transaction." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"The requirement that tasks within a transaction occur as a group as if they were a single complex task. The tasks are either all performed or none of them are performed. It's all or nothing." (Rod Stephens, "Beginning Database Design Solutions", 2008)

[atomic unit of work:] "A general category of work that an activity might perform. This kind of activity encapsulates the logic to perform a unit of work synchronously on the workflow thread. The unit of work that is performed is atomic in the sense that it is completed entirely during a single execution of the activity. It doesn’t need to suspend execution and wait for external input. It is short-lived and doesn’t perform time-consuming operations. It executes synchronously on the workflow thread and doesn’t create or use other threads." (Bruce Bukovics, "Pro WF: Windows Workflow in .NET 4", 2010)

"Atomicity is the state or fact of being composed of individual units (NOAD). With regard to data, atomicity refers to what constitutes a unit. In modeling, as data is normalized, each attribute is expected to represent one thing, not a set of things. One system may define a name as one thing. Another may define it as three things (first name, middle initial, last name). Atomicity results from decisions about how to structure data." (Laura Sebastian-Coleman, "Measuring Data Quality for Ongoing Improvement", 2012)

"The characteristic of a transaction whereby database modifications must adhere to an 'all or nothing' rule. If any single part of a transaction fails, the entire transaction fails. A database management system (DBMS) must maintain atomicity despite software and hardware failures (the A in ACID)." (Craig S Mullins, "Database Administration", 2012)

"An attribute or property of a transaction whereby a group of statements are run as if a single operation or none of the statements are run." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)