Browse cheat sheets · Data Engineering

Technology: Data EngineeringCategory: Storage & File FormatsDifficulty: IntermediateUpdated

CSV vs JSON vs Parquet vs Delta

Choose between CSV, JSON, Parquet, and Delta based on schema, analytics performance, mutability, interoperability, reliability, and production data-lake requirements.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. CSV: read and write with pandas
  5. 2. CSV: define types explicitly
  6. 3. CSV: handle null values deliberately
  7. 4. CSV: specify encoding when required
  8. 5. CSV: use chunks for large files
  9. 6. CSV: validate row shape
  10. 7. CSV: quoting matters
  11. 8. CSV: delimiter is part of the contract
  12. 9. CSV: strengths
  13. 10. JSON: read newline-delimited JSON with pandas
  14. 11. JSON Lines is preferable to one giant JSON array for streaming-style files
  15. 12. Standard JSON array
  16. 13. JSON: nested structure
  17. 14. Flatten nested JSON carefully
  18. 15. JSON arrays can change data grain
  19. 16. JSON: use explicit timestamps
  20. 17. JSON: schema is flexible, not nonexistent
  21. 18. JSON: avoid giant deeply nested documents for analytics
  22. 19. JSON: strengths
  23. 20. Parquet: read with pandas
  24. 21. Parquet: write with pandas
  25. 22. Parquet: read with PySpark
  26. 23. Parquet: select only required columns
  27. 24. Parquet: filter early
  28. 25. Parquet stores typed columnar data
  29. 26. Parquet is good for analytics
  30. 27. Parquet is still just files
  31. 28. Partition Parquet deliberately
  32. 29. Avoid very high-cardinality partitions
  33. 30. Avoid tiny Parquet files
  34. 31. Avoid one giant Parquet file
  35. 32. Parquet compression
  36. 33. Parquet schema evolution is not a transaction protocol
  37. 34. Delta: what it adds
  38. 35. Delta: read table with PySpark
  39. 36. Delta: append
  40. 37. Delta: overwrite deliberately
  41. 38. Delta: MERGE
  42. 39. Delta MERGE requires deterministic source keys
  43. 40. Delta supports deletes
  44. 41. Delta supports update operations
  45. 42. Delta table history
  46. 43. Delta time travel
  47. 44. Vacuum affects historical recoverability
  48. 45. Delta is not a database server
  49. 46. Delta is not “a different file type replacing Parquet”
  50. 47. Do not modify Delta data files directly
  51. 48. Do not treat a Delta directory as plain Parquet casually
  52. 49. CSV vs Parquet scan example
  53. 50. JSON vs Parquet for logs
  54. 51. CSV vs JSON for interchange
  55. 52. Parquet vs Delta
  56. 53. CSV should rarely be the curated analytical format
  57. 54. JSON should rarely be the final high-volume analytics format
  58. 55. Preserve raw source where lineage matters
  59. 56. Add ingestion metadata
  60. 57. Validate schema explicitly
  61. 58. Validate data types after parsing
  62. 59. Use explicit schema for Spark ingestion
  63. 60. Avoid schema inference on every production run
  64. 61. Decimal data deserves explicit types
  65. 62. Dates and timestamps require clear timezone semantics
  66. 63. CSV cannot distinguish types natively
  67. 64. JSON has types but still has ambiguity
  68. 65. Parquet preserves richer physical types
  69. 66. Compression is format-dependent
  70. 67. Gzipped CSV can be small but still expensive to query
  71. 68. Splittability matters for distributed processing
  72. 69. Avoid one file per row/event
  73. 70. Small-file problems are operational problems
  74. 71. File format and table layout are separate decisions
  75. 72. Format and compression are separate decisions
  76. 73. Format and table semantics are separate decisions
  77. 74. Avoid writing Delta if consumers only understand raw Parquet
  78. 75. Choose interchange format based on the consumer
  79. 76. Do not make CSV the internal source of truth just because users want CSV exports
  80. 77. Do not make JSON the source of truth solely because the source API uses JSON
  81. 78. Use immutable raw plus mutable curated tables
  82. 79. Validate round trips when interoperability matters
  83. 80. Choose by workload, not popularity
  84. What the result means
  85. What to look for
  86. CSV columns change type between runs
  87. JSON row counts grow unexpectedly after flattening
  88. Parquet query still scans too much data
  89. Delta MERGE fails or behaves ambiguously
  90. Delta time travel no longer reaches an old version
  91. Storage has millions of tiny files
  92. Production notes
  93. Interchange and analytical formats serve different goals
  94. Schema should be explicit
  95. Delta requires Delta-aware readers/writers
  96. File layout can matter as much as format
  97. Retention affects recoverability
  98. External consumers define interoperability constraints
  99. Common mistakes
  100. 1. Treating CSV as typed data
  101. 2. Flattening JSON without defining grain
  102. 3. Using JSON for repeated large analytical scans
  103. 4. Treating Parquet as a transactional table
  104. 5. Treating Delta as a replacement encoding for Parquet
  105. 6. Reading Delta files directly as plain Parquet
  106. 7. Partitioning by high-cardinality identifiers
  107. 8. Creating one giant output file
  108. 9. Allowing schema inference to define production contracts
  109. 10. Choosing one format for every layer
  110. Next step
  111. Related deep dives

Problem

You need to store or exchange data and must choose a format that fits the workload.

The wrong choice can create unnecessary:

  • parsing cost
  • schema drift
  • storage overhead
  • slow analytics
  • fragile updates
  • duplicate data
  • poor interoperability
  • difficult recovery

The goal is not to pick one universal winner.

The goal is to use the right format for the role it plays in the data platform.

Quick answer

Use this decision model:

Simple human-readable interchange
→ CSV

Nested or semi-structured API/event payloads
→ JSON

Read-heavy analytics on immutable files
→ Parquet

Analytics tables that need ACID transactions, updates, deletes, MERGE, schema management, and table history
→ Delta

A common production pattern is:

external/source interchange
→ CSV / JSON

raw durable landing
→ source-native or Parquet/Delta

curated analytics
→ Parquet-backed Delta tables

serving/warehouse layer
→ Delta / warehouse tables

The practical distinction:

CSV / JSON
→ serialization formats

Parquet
→ columnar analytics file format

Delta
→ table format built on Parquet + transaction log

Code / commands

1. CSV: read and write with pandas

Read:

import pandas as pd

df = pd.read_csv(
    "orders.csv",
    dtype={
        "order_id": "string",
        "customer_id": "string",
    },
    parse_dates=["created_at"],
)

Write:

df.to_csv(
    "orders.csv",
    index=False,
)

Use CSV when interoperability and simplicity matter more than strong schema or analytical efficiency.


2. CSV: define types explicitly

Bad:

df = pd.read_csv("orders.csv")

if identifiers or dates must be interpreted reliably.

Better:

df = pd.read_csv(
    "orders.csv",
    dtype={
        "order_id": "string",
        "postal_code": "string",
    },
    parse_dates=["created_at"],
)

This helps avoid problems such as:

00123
→ parsed as 123

large identifier
→ parsed as numeric

date-like text
→ interpreted inconsistently

3. CSV: handle null values deliberately

Example:

df = pd.read_csv(
    "orders.csv",
    na_values=["", "NULL", "null"],
    keep_default_na=True,
)

Do not assume every empty string or string "NULL" has the same business meaning.

Define the source contract explicitly.


4. CSV: specify encoding when required

df = pd.read_csv(
    "orders.csv",
    encoding="utf-8",
)

If encoding is unknown, do not silently guess in a production pipeline.

Capture failures and inspect the source contract.


5. CSV: use chunks for large files

for chunk in pd.read_csv(
    "large_orders.csv",
    chunksize=100_000,
):
    process(chunk)

Use when the file is larger than convenient in-memory processing.

Chunking does not make CSV columnar or indexed; it only controls memory usage.


6. CSV: validate row shape

Typical failure:

expected 12 columns
one row contains 13 delimiters

In production, track malformed-row counts and isolate bad records rather than silently dropping them.


7. CSV: quoting matters

Example field:

"Smith, John"

contains a comma but should remain one column.

Use standards-compliant readers/writers instead of manual split(",").


8. CSV: delimiter is part of the contract

Some systems use:

,
;
|
\t

Example:

df = pd.read_csv(
    "orders.psv",
    sep="|",
)

Do not assume every “CSV” uses a comma.


9. CSV: strengths

Good for:

simple exports
manual inspection
spreadsheet compatibility
legacy integrations
small interchange files

Weaknesses:

no strong schema
no nested structure
poor typing
large scan cost
delimiter/quoting issues

10. JSON: read newline-delimited JSON with pandas

For JSON Lines / NDJSON:

df = pd.read_json(
    "events.jsonl",
    lines=True,
)

This is common for event and log datasets.


11. JSON Lines is preferable to one giant JSON array for streaming-style files

JSON Lines:

{"eventId": 1, "type": "click"}
{"eventId": 2, "type": "view"}
{"eventId": 3, "type": "purchase"}

Each line is one independent JSON value.

Benefits:

  • incremental writing
  • line-by-line processing
  • easier partial recovery
  • common in log/event pipelines

12. Standard JSON array

Example:

[
  {
    "eventId": 1,
    "type": "click"
  },
  {
    "eventId": 2,
    "type": "view"
  }
]

This is useful for API responses and bounded documents.

It is less convenient for appending large event datasets.


13. JSON: nested structure

Example:

{
  "orderId": 123,
  "customer": {
    "customerId": 45,
    "country": "US"
  },
  "items": [
    {
      "sku": "A1",
      "qty": 2
    }
  ]
}

JSON is a natural fit for nested/semi-structured data.

CSV is not.


14. Flatten nested JSON carefully

With pandas:

import json
import pandas as pd

with open("orders.json", "r", encoding="utf-8") as f:
    payload = json.load(f)

orders = pd.json_normalize(payload)

If arrays exist, define the intended grain first.

Flattening nested data can multiply rows unexpectedly.


15. JSON arrays can change data grain

Example:

1 order
with
5 items

Flattening to one row per item creates:

5 rows

Do not aggregate later assuming the original grain is still one order per row.


16. JSON: use explicit timestamps

Prefer standard ISO-like representations:

{
  "createdAt": "2026-10-04T15:30:00Z"
}

over locale-dependent strings such as:

10/04/26 3:30 PM

Clients should still parse according to the documented contract.


17. JSON: schema is flexible, not nonexistent

JSON does not enforce a schema itself.

Production systems should still define:

  • required fields
  • types
  • nullable fields
  • versioning
  • compatibility rules

Flexible representation does not remove the need for a data contract.


18. JSON: avoid giant deeply nested documents for analytics

Nested JSON can be expensive to parse repeatedly.

A good pattern is:

ingest source JSON
→ preserve raw payload
→ parse/normalize once
→ write analytical Parquet/Delta

19. JSON: strengths

Good for:

APIs
events
logs
semi-structured data
nested records
schema evolution at the edge

Weaknesses:

verbose
row-oriented
repeated field names
expensive analytical scans
type ambiguity

20. Parquet: read with pandas

df = pd.read_parquet(
    "orders.parquet",
    columns=[
        "order_id",
        "created_at",
        "amount",
    ],
)

Selecting only required columns reduces I/O.

This is a key advantage of columnar storage.


21. Parquet: write with pandas

df.to_parquet(
    "orders.parquet",
    index=False,
)

Compression depends on engine/default configuration.

For production, choose compression deliberately when interoperability or performance matters.


22. Parquet: read with PySpark

df = (
    spark.read
    .parquet("/data/orders")
)

23. Parquet: select only required columns

df = (
    spark.read
    .parquet("/data/orders")
    .select(
        "order_id",
        "created_at",
        "amount",
    )
)

Column pruning reduces the amount of data read.


24. Parquet: filter early

df = (
    spark.read
    .parquet("/data/orders")
    .filter("order_date >= '2026-10-01'")
    .select(
        "order_id",
        "order_date",
        "amount",
    )
)

Predicate pushdown and partition pruning may reduce scan work when file statistics/layout support it.

Verify with the query plan instead of assuming every filter is pushed down.


25. Parquet stores typed columnar data

Unlike CSV, Parquet can preserve types such as:

integer
decimal
timestamp
boolean
string
nested structures

This reduces repeated parsing and schema inference.


26. Parquet is good for analytics

Typical advantages:

column pruning
compression
typed schema
predicate pushdown support
efficient scans
nested data support

This makes it much better than CSV/JSON for repeated analytical queries.


27. Parquet is still just files

A folder of Parquet files does not automatically provide:

ACID table transactions
multi-file atomic updates
MERGE semantics
table history
concurrent write coordination

That is where table formats such as Delta add value.


28. Partition Parquet deliberately

Example directory layout:

/orders/order_date=2026-10-01/
/orders/order_date=2026-10-02/

Use partitions when they help common filters and maintenance.

Do not partition on every possible column.


29. Avoid very high-cardinality partitions

Poor candidates:

order_id
transaction_id
customer_email

These can create huge numbers of directories/small files.

Prefer partition columns with practical data-management/filtering value.


30. Avoid tiny Parquet files

Thousands or millions of tiny files increase:

  • metadata overhead
  • file-open cost
  • scheduler work
  • query planning overhead

Compact files when the platform/workload requires it.

There is no universal ideal file size.


31. Avoid one giant Parquet file

One massive file can reduce parallelism and make rewrite/recovery awkward.

File size should reflect:

  • engine
  • storage
  • workload
  • concurrency
  • partitioning

32. Parquet compression

Common codecs include:

Snappy
Gzip
Zstandard

Tradeoff:

more compression
↔
more CPU

Choose based on workload and interoperability.


33. Parquet schema evolution is not a transaction protocol

Adding/removing/changing fields across separate Parquet files can create mixed schemas.

Some engines can merge schemas, but this has correctness and performance implications.

Govern schema changes explicitly.


34. Delta: what it adds

Delta tables typically consist of:

Parquet data files
+
_delta_log transaction log

The transaction log provides table semantics such as:

  • ACID transactions
  • schema enforcement/evolution controls
  • updates/deletes
  • MERGE
  • table history
  • concurrency handling
  • time travel/version reads

35. Delta: read table with PySpark

df = (
    spark.read
    .format("delta")
    .table("silver.orders")
)

or by path:

df = (
    spark.read
    .format("delta")
    .load("/tables/silver/orders")
)

Prefer table references when your platform/catalog manages them.


36. Delta: append

(
    df.write
    .format("delta")
    .mode("append")
    .saveAsTable("bronze.orders")
)

Use append for immutable/new records when duplicate handling is already defined.


37. Delta: overwrite deliberately

(
    df.write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("silver.orders_rebuild")
)

Overwrite can replace table data.

Use only when rebuilding the intended scope.

For partition-scoped replacement, use the platform’s supported partition-replacement pattern rather than blindly overwriting the whole table.


38. Delta: MERGE

from delta.tables import DeltaTable

target = DeltaTable.forName(
    spark,
    "silver.orders",
)

(
    target.alias("t")
    .merge(
        source=df.alias("s"),
        condition="t.order_id = s.order_id",
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Use when rows can be inserted or updated.

Validate source uniqueness before MERGE.


39. Delta MERGE requires deterministic source keys

Bad source:

order_id 123 appears twice

A MERGE may become ambiguous or fail depending on operation/engine behavior.

Check source grain:

from pyspark.sql import functions as F

duplicates = (
    df.groupBy("order_id")
    .count()
    .filter(F.col("count") > 1)
)

Resolve duplicates before writing.


40. Delta supports deletes

Example:

target.delete(
    "is_deleted = true"
)

Delete behavior is transactional at the table level.

Be careful with broad predicates in production.


41. Delta supports update operations

Example:

target.update(
    condition="status = 'pending'",
    set={
        "status": "'expired'"
    },
)

Use narrowly scoped, reviewed predicates.


42. Delta table history

SQL:

DESCRIBE HISTORY silver.orders;

Use to inspect:

  • operation
  • version
  • timestamp
  • user/job metadata where available
  • operation parameters/metrics depending on platform

History improves observability but does not replace business audit logging.


43. Delta time travel

Example:

previous = (
    spark.read
    .format("delta")
    .option("versionAsOf", 42)
    .table("silver.orders")
)

or:

previous = (
    spark.read
    .format("delta")
    .option(
        "timestampAsOf",
        "2026-10-04T12:00:00Z",
    )
    .table("silver.orders")
)

Historical availability depends on retained log/data files.


44. Vacuum affects historical recoverability

Delta maintenance may remove old data files that are no longer needed by current table state.

If retention is reduced aggressively, old versions may no longer be readable.

Do not shorten retention without understanding:

  • active readers
  • streaming jobs
  • time-travel requirements
  • rollback/recovery needs

45. Delta is not a database server

Delta provides table semantics over files/object storage.

It does not automatically provide:

  • row-level OLTP latency
  • arbitrary high-concurrency transactional application serving
  • stored procedures
  • database connection semantics

Use it for analytical/lakehouse workloads.


46. Delta is not “a different file type replacing Parquet”

The underlying data files are Parquet.

The Delta transaction log adds table behavior around those files.

This distinction matters when troubleshooting or integrating with engines.


47. Do not modify Delta data files directly

Bad:

delete one Parquet file manually
replace one file outside the transaction protocol

This can corrupt table consistency because the transaction log still references table state.

Write through Delta-aware tools.


48. Do not treat a Delta directory as plain Parquet casually

Reading only:

*.parquet

can ignore:

  • deletes
  • updates
  • transaction state
  • removed files
  • version semantics

Use a Delta-aware reader for Delta tables.


49. CSV vs Parquet scan example

Suppose a query needs:

order_id
amount

from a 50-column dataset.

CSV normally requires parsing rows containing all columns.

Parquet can read only required column chunks.

This is why Parquet is generally better for analytical scans.


50. JSON vs Parquet for logs

Good flow:

raw events
→ JSON Lines
→ normalize
→ Parquet/Delta

JSON keeps source fidelity.

Parquet/Delta improve repeated analytical queries.


51. CSV vs JSON for interchange

Choose CSV when:

flat tabular structure
maximum compatibility
simple export/import

Choose JSON when:

nested data
optional fields
hierarchical payloads
API/event semantics

52. Parquet vs Delta

Choose Parquet when:

immutable analytical files
simple file exchange
no transactional table behavior needed

Choose Delta when:

table is mutable
MERGE required
concurrent writers/readers
schema enforcement matters
history/time travel needed

53. CSV should rarely be the curated analytical format

CSV may still be valid when interoperability requires it.

But for internal analytics at scale, repeatedly parsing large CSV datasets is usually unnecessary overhead.

Prefer a typed columnar format.


54. JSON should rarely be the final high-volume analytics format

JSON is valuable at ingestion boundaries.

Curated analytics usually benefits from converting it to:

Parquet
or
Delta

once the schema/grain is understood.


55. Preserve raw source where lineage matters

A useful Bronze pattern:

store original file/payload
+
ingestion metadata

This helps:

  • replay
  • audit
  • parser fixes
  • schema changes
  • source disputes

56. Add ingestion metadata

Useful columns:

source_file
ingested_at
batch_id
source_system
record_hash

Do not overwrite source semantics with ingestion metadata.

Keep both.


57. Validate schema explicitly

Example in PySpark:

expected = {
    "order_id",
    "created_at",
    "amount",
}

actual = set(df.columns)

missing = expected - actual

if missing:
    raise ValueError(
        f"Missing required columns: {sorted(missing)}"
    )

Schema validation is especially important for CSV/JSON sources.


58. Validate data types after parsing

Example:

df.printSchema()

Look for unexpected:

string instead of timestamp
double instead of decimal
null-only inferred column

Do not trust inference for critical production pipelines.


59. Use explicit schema for Spark ingestion

Example:

from pyspark.sql.types import (
    StructType,
    StructField,
    StringType,
    TimestampType,
    DecimalType,
)

schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("created_at", TimestampType(), True),
    StructField("amount", DecimalType(18, 2), True),
])

Then:

df = (
    spark.read
    .schema(schema)
    .json("/landing/orders/*.json")
)

This prevents inconsistent inference across files.


60. Avoid schema inference on every production run

Inference can:

  • scan additional data
  • change depending on source samples
  • infer different types after source changes

Explicit schema is more predictable.


61. Decimal data deserves explicit types

Financial values should not automatically be stored as floating point.

Prefer appropriate decimal precision/scale where exact arithmetic is required.

Example:

DECIMAL(18,2)

The correct precision depends on business requirements.


62. Dates and timestamps require clear timezone semantics

For interchange:

2026-10-04T15:30:00Z

is clearer than a local timestamp without offset.

Store timezone/UTC semantics explicitly.


63. CSV cannot distinguish types natively

These values:

00123
123
true
2026-10-04

are all text in the file.

The reader decides their types.

This is a major reason to validate parsing.


64. JSON has types but still has ambiguity

JSON supports:

string
number
boolean
null
array
object

But it does not distinguish:

integer widths
decimal precision
timestamp type
date type

without a schema contract.


65. Parquet preserves richer physical types

This improves interoperability between analytical engines when compatible types are used.

Still validate cross-engine behavior for:

  • timestamps
  • decimals
  • nested types
  • logical annotations

66. Compression is format-dependent

General tendency:

CSV
→ usually large unless externally compressed

JSON
→ verbose because field names repeat

Parquet
→ strong columnar compression

Delta
→ same Parquet data compression + transaction-log overhead

Actual size depends on data distribution and codec.


67. Gzipped CSV can be small but still expensive to query

Compression reduces storage/transfer size.

It does not make CSV:

  • typed
  • columnar
  • selectively readable

A query may still need to decompress and parse the stream.


68. Splittability matters for distributed processing

Large compressed files may reduce parallel read efficiency depending on compression/container behavior.

Parquet is designed for parallel analytical reads.

Do not create one enormous compressed text file and expect ideal distributed performance.


69. Avoid one file per row/event

Every format suffers from excessive file counts.

Object storage and distributed engines have non-trivial metadata/file-open overhead.

Batch records into reasonable files according to workload.


70. Small-file problems are operational problems

Symptoms:

  • long planning time
  • slow directory listing
  • excessive task count
  • metadata overhead
  • poor scan efficiency

Fix at ingestion/write layout rather than only adding compute.


71. File format and table layout are separate decisions

Example:

Parquet format
+
partition by date
+
files sized sensibly

can perform well.

Poor partitioning and tiny files can make even Parquet slow.


72. Format and compression are separate decisions

Example:

Parquet + Snappy
Parquet + Zstd
CSV + gzip
JSONL + gzip

Choose both intentionally.


73. Format and table semantics are separate decisions

Parquet
→ file format

Delta
→ table format/protocol using Parquet files

This is one of the most important distinctions.


74. Avoid writing Delta if consumers only understand raw Parquet

If the external consumer cannot interpret Delta table semantics, decide whether to:

  • expose supported table access
  • export Parquet snapshot
  • export CSV/JSON
  • use a sharing/query interface

Do not ask consumers to read the Delta directory as arbitrary Parquet.


75. Choose interchange format based on the consumer

For external delivery:

business user
→ CSV may be easiest

API integration
→ JSON

analytics engine
→ Parquet

lakehouse-aware platform
→ Delta

Architecture should account for downstream capabilities.


76. Do not make CSV the internal source of truth just because users want CSV exports

Keep a typed internal representation.

Export CSV at the boundary.

This preserves analytical quality while maintaining compatibility.


77. Do not make JSON the source of truth solely because the source API uses JSON

Preserve raw JSON if needed, then normalize into governed analytical tables.


78. Use immutable raw plus mutable curated tables

Common pattern:

Bronze
→ append raw/source-aligned

Silver
→ cleaned/deduplicated/upserted Delta

Gold
→ consumer-ready Delta/warehouse

This separates replayability from curated mutability.


79. Validate round trips when interoperability matters

Example:

write file
→ read using target engine
→ compare row count, schema, key fields

Do not assume that successful write in one library means perfect compatibility everywhere.


80. Choose by workload, not popularity

Decision order:

1. What consumes the data?
2. Is data flat or nested?
3. Is strong typing required?
4. Is workload analytical?
5. Are updates/deletes/MERGE required?
6. Is interoperability more important than query speed?
7. Do we need transactions/history?
8. What scale/file layout are expected?

Then choose the format.

What the result means

Format Best fit Main strength Main limitation
CSV Flat interchange/export Universal compatibility Weak typing, expensive analytics
JSON APIs/events/semi-structured data Nested flexible structure Verbose, expensive repeated analytics
Parquet Analytical files Columnar typed scans No transactional table semantics by itself
Delta Mutable lakehouse tables ACID + MERGE + history on Parquet Requires Delta-aware tooling

A practical mental model is:

CSV
→ spreadsheet-friendly text

JSON
→ application/event-friendly structured text

Parquet
→ analytics-friendly columnar files

Delta
→ analytics table semantics around Parquet

What to look for

CSV columns change type between runs

Check:

  • schema inference
  • leading zeros
  • null markers
  • malformed rows
  • locale/date changes

Use explicit schema.

JSON row counts grow unexpectedly after flattening

Nested arrays probably changed the output grain.

Validate business keys before aggregation.

Parquet query still scans too much data

Check:

  • selected columns
  • filters
  • partition pruning
  • file statistics
  • file layout
  • small files
  • query plan

Delta MERGE fails or behaves ambiguously

Check:

  • source duplicate keys
  • target grain
  • merge condition
  • concurrent writes

Delta time travel no longer reaches an old version

Check retention/VACUUM and data-file availability.

Storage has millions of tiny files

Fix upstream batching/partitioning/compaction strategy.

Production notes

Interchange and analytical formats serve different goals

CSV/JSON optimize compatibility and source fidelity.

Parquet/Delta optimize analytical processing and governed table behavior.

Schema should be explicit

Text formats especially need deliberate schema handling.

Inference is convenient for exploration, not a strong production contract.

Delta requires Delta-aware readers/writers

Do not bypass the transaction protocol by modifying files manually.

File layout can matter as much as format

Partitioning and small-file behavior can dominate performance.

Retention affects recoverability

Delta history and time travel depend on retained transaction/data files.

Do not reduce retention casually.

External consumers define interoperability constraints

Sometimes CSV is the correct output even if the internal platform uses Delta.

Use different formats at different boundaries.

Common mistakes

1. Treating CSV as typed data

The file stores text. Readers infer/assign types.

2. Flattening JSON without defining grain

Arrays can multiply records.

3. Using JSON for repeated large analytical scans

Normalize once and write columnar data.

4. Treating Parquet as a transactional table

Parquet alone does not provide ACID table semantics.

5. Treating Delta as a replacement encoding for Parquet

Delta uses Parquet data files plus a transaction log.

6. Reading Delta files directly as plain Parquet

You can ignore table state and removed files.

7. Partitioning by high-cardinality identifiers

This creates excessive directories/files.

8. Creating one giant output file

This can reduce parallelism and increase operational risk.

9. Allowing schema inference to define production contracts

Schema can drift unexpectedly.

10. Choosing one format for every layer

Source exchange, raw storage, curated analytics, and external delivery often need different formats.

Next step

Choose based on the workload.

  • Need simple flat export/import → use CSV.
  • Need nested API/event payloads → use JSON/JSON Lines.
  • Need efficient immutable analytical scans → use Parquet.
  • Need MERGE, updates, deletes, concurrency, and history → use Delta.
  • CSV/JSON ingestion is slow repeatedly → normalize once into Parquet/Delta.
  • Parquet queries are still slow → inspect partitioning, pruning, selected columns, and file layout.
  • Delta writes conflict → inspect concurrency and write patterns.
  • Time travel matters → define Delta retention/VACUUM policy.
  • External consumers cannot read Delta → export an appropriate interoperable format.
  • Need broader table-layer design → move to Medallion Architecture Quick Reference.

← All cheat sheets