Imagine This

I know, I know, what’s the simplest way to share a large dataset with another team? File > Export > Export as CSV. We all do it that way 🤫!

However, let’s consider what happens on the receiving end. When you open a plain CSV file, you have to manually import it and select the delimiter and quote character.

If you choose the wrong settings, you might end up with a mess 🥴: a single logical row could span multiple lines due to unescaped line breaks inside the fields. You try adjusting the settings again, but it still doesn’t work (you lock your computer and go for a coffee break ☕).

So, if CSV isn’t the best choice, are there any alternatives that a data practitioner should know about?

Human-Readable Encoding Formats

Instead of simply asking, “Which format is better?”, we need to evaluate formats based on specific use cases. Some prioritize human readability, while others optimize for efficiency, compactness, or processing speed.

Let’s start with human-readable formats, commonly used for file transmission, downloads, and data sharing between teams.

Encoding Formats Human readable

XML

The old veteran of data formats: still around, still useful in some systems, and definitely starting to show its age. Here are its key characteristics:

  • Readable, but very verbose.
  • Supports schema definitions, but cannot inherently distinguish between numbers and strings.
  • Best suited for document-oriented systems and legacy applications where XML is already deeply embedded.

❌
Avoid using it! If you have a system relying on XML, please refactor it 😉.

CSV

The unlimited beast: simple, universal, and surprisingly resilient despite its limitations.
Here are its key characteristics:

  • Simple and widely supported, easy to generate and read, with no inherent size limit.
  • Lacks a defined schema, does not differentiate between numbers and strings, struggles with handling special characters (e.g. commas, newlines, qualifiers).
  • Used for analysis, reporting, and data exchange between different tools, but unsuitable for complex nested data or schema enforcement.

💪
CSV files are heavily used across industries for big data transfers. Despite lacking a schema, being cumbersome, and not very readable, CSV has no inherent row limit like Excel, and that’s why it’s so popular!

Excel

The immortal tool: flexible, familiar, and still deeply embedded in everyday business workflows.
Here are its key characteristics:

  • User-friendly, used for data exploration, supports rich formatting, formulas, and pivot tables. 
  • Limited to ~1 million rows, inefficient for large-scale data processing due to its XML-based storage structure, and also prone to data integrity issues.
  • Best for business reporting, financial modeling, and interactive data analysis, but not suitable for automated or large-scale data processing.

✔️
You will die one day, but Excel won’t ☠️. It’s a tool you can (and will) use, but be careful. Never store all your critical data in an Excel file, and always keep in mind the 1M limit 😉!

JSON

If you’ve ever worked with an API or a document database like MongoDB, you’ve definitely come across JSON. It’s widely used in the industry for data exchange between services, whether on the web, in software, or across different systems. Here are its key characteristics:

  • Readable and widely adopted, supports schema definitions.
  • Verbose for large datasets, can introduce floating-point precision issues, and lacks native binary data support.
  • Ideal for API responses, configuration files, and cases where human readability matters, but not suited for large-scale data transfers.

💡
JSON is widely used for APIs and microservices, but have you ever tried extracting data from ten levels of nested objects or managing a JSON file with billions of entries? It quickly becomes a nightmare…

Binary Encoding Formats

Human-readable formats are convenient, but when performance and compactness matter more, binary formats become a better fit.

Communication between services

If you need something faster and more compact than JSON, the formats in this section are built for that. They are commonly used for service-to-service communication, where fast serialization and deserialization really matter.

MessagePack & BSON

If you’re looking for formats that serve a similar purpose to JSON but are more efficient and compact, MessagePack and BSON are good alternatives. Instead of storing data as human-readable text, they use a binary representation, reducing size and improving processing speed.

Here are their key characteristics:

  • MessagePack is a general-purpose binary format, while BSON is closely associated with MongoDB and document-oriented data.
  • Binary encodings remove verbose text characters ({}, :, ,, ") and instead encode information such as object sizes and field lengths more compactly.
  • Files can be roughly 1.2× to 2× smaller than JSON/CSV.

💡 In real-life scenarios: Suitable for lightweight data serialization in applications where JSON is used but performance is a concern. BSON is a specialized storage format for MongoDB.

💡
If you’re facing performance issues when handling large JSON exchanges between services, switching to MessagePack or BSON can significantly improve performance!

Thrift & Protocol Buffers

If you need even more efficient service-to-service communication with a well-defined data contract, Thrift and Protocol Buffers are a strong fit. Unlike JSON or MessagePack, they rely on a strict schema defined before serialization.

Here are their key characteristics:

  • Each field is defined in the schema with a unique identifier, enabling compact and reliable serialization and deserialization. Changing existing identifiers can break compatibility and corrupt serialized data.
  • Files can be roughly 2× to 4× smaller than JSON/CSV.
  • Both formats are highly efficient and are widely used in microservice architectures.

💡 In real-life scenarios: These formats are commonly used for internal service-to-service communication, especially with RPC frameworks such as gRPC and in microservice architectures where a strict data contract is important.

❌
Use these formats mainly for internal service-to-service communication, where producers and consumers can rely on the same data contract. Otherwise, compatibility issues can quickly break communication.

Large dataset storage and processing

These formats are great for service-to-service communication, but when it comes to Big Data and analytics, we need to switch gears. The goal is no longer to transfer small payloads as quickly as possible, but to efficiently store, move, and process large volumes of data across systems.

This is where formats designed for large-scale data storage and processing come in, serving a role similar to CSV or Excel, but much more efficiently.

Avro

Avro is a row-based format optimized for fast read and write operations. It supports schema evolution, making it suitable for streaming platforms like Apache Kafka.

Here are its key characteristics:

  • Row-based, used in Hadoop ecosystems.
  • Requires a schema, but does not require field numbers.
  • Files can be roughly 2× to 5× smaller than JSON/CSV.

💡 In real-life scenarios: Avro provides significant file compression while maintaining high I/O performance, making it more efficient than CSV for large datasets.

✔️
If you need to compress a file while keeping it optimized for fast read/write operations, especially in write-heavy workloads, Avro is your friend!

Parquet (or ORC)

The star is here ⭐! Let me introduce Parquet, along with its close alternative, ORC. These formats are widely used in the data world because their columnar storage is highly optimized for analytics, especially in Big Data frameworks like Apache Spark, Hive, and Presto.

Here are their key characteristics:

  • Column-oriented, highly efficient for analytical workloads.
  • Used for data lakes, data lakehouses, and analytical applications involving large datasets.
  • Allows better compression than row-based formats because values in the same column usually have the same data type and similar patterns.
  • Files can be roughly 5× to 20× smaller than JSON/CSV.

💡 In real-life scenarios: You’ll find Parquet files everywhere in data lakes and lakehouses, especially when storing or transferring large datasets. Parquet is also commonly used in staging layers and as the underlying data format for Delta tables, which we’ll see next!

✔️
Parquet is the go-to format for data practitioners, offering everything you need: excellent compression and column-oriented structure, which is crucial for handling large-scale analytical workloads!

Delta Lake (with Hudi and Iceberg)

Unlike CSV or standalone Parquet, a Delta table is not a single file; it’s a directory structure that evolves over time. Each write adds or updates Parquet files and records the changes in a transaction log.

A typical Delta table directory looks like the structure shown below:

  • Parquet files contain the actual data in a compressed and columnar format.
  • JSON files inside _delta_log/ form the transaction log, tracking changes, metadata, and the state of the table over time.

Here are its key characteristics:

  • Supports atomic writes, updates, and deletes (things CSV/JSON/Parquet can’t do ‼️).
  • Enables time travel (query past versions) and schema evolution.
  • Works natively with Spark, Databricks, and Delta-RS.

Beyond Delta Lake, two other open table formats, Apache Hudi and Apache Iceberg, solve similar problems around reliability and large-scale analytics. They can all use Parquet for the actual data, but they differ in how they manage changes:

  • Delta Lake: Stores the data in Parquet files and uses a JSON transaction log to record which files were added, removed, or changed over time.
  • Apache Hudi: Also stores the main data in files such as Parquet, but can keep data changes in separate log files before merging them into the main data files. This makes it well suited for frequent updates and incremental processing.
  • Apache Iceberg: Stores the data in files such as Parquet and uses metadata and manifest files to keep track of which data files belong to each version of the table.

In short, the data files can look very similar. The main difference is how each format handles versions, updates, and metadata. If you want to go deeper, check their respective documentation for the technical details.

✔️
Use Delta or an equivalent when working on a data lakehouse and you need reliable and large-scale support for both batch and streaming analytics.

Arrow

Question: JSON is the go-to format for exchanging data between operational services, but what if you need to exchange large datasets between two analytical systems?

Answer: Apache Arrow comes into play 🙋! It is natively designed for in-memory columnar data processing. It enables efficient data exchange between analytical tools such as Pandas, Spark, and DuckDB, greatly reducing serialization overhead. Unlike Parquet or ORC, which are optimized for disk storage, Arrow keeps data in memory (RAM) for ultra-fast analytics and real-time querying.

💡
If you need a really fast (and in-memory) way to share data between two analytical platforms, Arrow will definitely help you!

Summary

Oh, wait, that’s all? What if I want a human-readable AND a well-compressed file?

Reader question

Unfortunately, aside from slightly compressing your CSV with an archive tool (which doesn’t improve its efficiency), having a highly compressed and optimized file does not go hand in hand with human readability.

✔️
So yes, long live our dear CSV, Excel, and JSON files! But when we start exchanging files of several GB or even TB, let’s not forget to use the right format 🚨!

👉 Curious how format choices like Parquet or Delta fit into a bigger maturity picture? Check the four levels here.

Categorized in:

Data Processing, Technologies & Tools,

Tagged in:

, , , , , , , , , , , , ,