CSV
Row- No types, so dates, decimals and nulls must be parsed and validated on load.
- Delimiters, quotes and newlines inside values are the most common load failures.
- Agree on encoding (UTF-8), delimiter, header and null marker with the source team.
Notes
File Formats & Table Formats
A file format decides how bytes are laid out on disk. A table format sits on top of those files and adds database behaviour — transactions, schema evolution, and time travel — to plain object storage like S3.
File Formats
Row formats store each record together and are fast to write. Columnar formats store each column together and are fast to query, because an engine reads only the columns it needs.
| Format | Layout | Encoding | Schema | Splittable | Best for |
|---|---|---|---|---|---|
| CSV | Row | Text | None — every value is a string | Yes, unless gzip-compressed | Exchanging data with external systems and humans |
| JSON | Row | Text | Self-describing, nested | JSON Lines yes; a single large array no | API payloads and semi-structured events |
| XML | Row | Text | Optional, via XSD | Only with a row tag | Legacy enterprise and B2B integrations |
| Avro | Row | Binary | Embedded in the file header (JSON schema) | Yes, via sync markers | Streaming messages and write-heavy pipelines |
| Parquet | Columnar | Binary | Embedded in the file footer | Yes, by row group | Analytics storage in data lakes and warehouses |
| ORC | Columnar | Binary | Embedded in the file footer | Yes, by stripe | Hive-centric Hadoop workloads |
JSON
Row- Prefer JSON Lines (one object per line) for anything you load at scale.
- Field names repeat on every record, so files are large — convert to Parquet early.
- Snowflake loads it into VARIANT and queries it with path notation and FLATTEN.
XML
Row- Very verbose — tags often outweigh the data itself.
- Spark needs a rowTag to know where each record starts.
- Flatten into typed columns as soon as it lands; avoid keeping it as the working format.
Avro
Row- Best-in-class schema evolution: add fields with defaults without breaking readers.
- The standard for Kafka topics alongside a Schema Registry.
- Fast to write whole records, slower than columnar formats for analytics.
Parquet
Columnar- Reads only the columns a query needs (column pruning).
- Min/max statistics per row group let engines skip data (predicate pushdown).
- Snappy for speed, ZSTD for size — the default choice for lake storage.
ORC
Columnar- Built-in indexes and bloom filters for fast lookups.
- Very good compression; powers Hive ACID tables.
- Outside the Hive ecosystem, Parquet has wider tool support.
Table Formats
Plain Parquet files on S3 cannot safely handle updates, deletes or two writers at once. Table formats fix this with a metadata layer that tracks exactly which files make up each version of a table.
- ACID transactions on object storage
- Upserts and deletes with MERGE
- Time travel to earlier snapshots
- Schema evolution without rewriting data
Apache Iceberg
Metadata
Metadata file → manifest list → manifest files → data files
Strengths
- Engine-agnostic: Spark, Flink, Trino, Athena, Snowflake
- Hidden partitioning and partition evolution
- Works with AWS Glue, REST and Polaris catalogs
Best for
Multi-engine lakehouses on AWS and Snowflake
Delta Lake
Metadata
_delta_log of JSON commits plus Parquet checkpoints
Strengths
- Tight Spark and Databricks integration
- Change Data Feed for downstream CDC
- Z-ordering and liquid clustering for layout
Best for
Spark and Databricks-first platforms
Apache Hudi
Metadata
Timeline of commits in the .hoodie folder
Strengths
- Copy-on-Write and Merge-on-Read table types
- Record-level upserts with built-in indexing
- Incremental queries that read only changed rows
Best for
High-frequency CDC and streaming upserts
Which One Should I Use?
| If you need… | Use |
|---|---|
| Landing raw data from a source system | Keep it as delivered (CSV, JSON, XML) |
| Kafka or streaming messages | Avro with a Schema Registry |
| Analytics storage in S3 | Parquet with Snappy or ZSTD |
| Existing Hive warehouse | ORC |
| Updates, deletes or time travel across engines | Apache Iceberg |
| Lakehouse built on Databricks | Delta Lake |
| Heavy upsert-driven CDC | Apache Hudi |