9 Data Engineering Storage and Formats Interview Questions and Answers

Short answer: In a row format the values of a single record sit together; in a columnar format all values of one column sit together. Analytical queries (aggregates over a few of dozens of columns) read only the needed columns, compress far better and support predicate pushdown — so less I/O and lower cost.

In depth:

1. Column projection (projection pushdown) — a query needs 3 of 50 columns, and a columnar engine reads only those three off disk. A row format must read the whole row.
2. Compression — a column holds homogeneous values (one type, similar data), so dictionary, run-length and delta encoding reach compression ratios several times higher than on a heterogeneous row.
3. Predicate pushdown — using stored per-block min/max statistics, a filter like WHERE date = ... skips whole blocks without reading them.
4. Row format wins for point operations: read/write a whole record (OLTP, one-record-at-a-time streaming).

Row layout:   [id=1,name=A,ts=..][id=2,name=B,ts=..][id=3,...]
               └── record 1 ──┘└── record 2 ──┘

Columnar layout:
  id:   [1, 2, 3, ...]        ← read only the columns
  name: [A, B, C, ...]          you need, each one
  ts:   [.., .., .., ...]       compressed separately

⚠️ Common mistake: treating columnar as universally better. For row-at-a-time writes and whole-record reads (streaming, transactional updates) a row format (Avro) is more efficient.

Short answer: Parquet is a columnar format where a file is split into horizontal row groups; within each, data is stored by column (column chunks), and a column is made of pages. The footer holds the schema and per-column-chunk statistics (min/max, null counts) that let the engine skip data it doesn't need.

In depth:

1. Row group — a horizontal slice (typically 128–512 MB), the unit of parallelism: different workers read different row groups.
2. Column chunk — one column's data inside a row group; only projected columns are read.
3. Pages — a column is split into pages (~1 MB) with their own statistics and encoding (dictionary, RLE, bit-packing) on top of compression (Snappy/Zstd).
4. Footer — metadata at the file's end: schema, offsets, per-chunk min/max. It's read first, so a filter prunes row groups before any data is read.

Parquet file
┌──────────────────────────────────────────┐
│ Row Group 0                                │
│   col A chunk [page|page|...] (min/max)    │
│   col B chunk [page|page|...] (min/max)    │
│ Row Group 1                                │
│   col A chunk ...   col B chunk ...         │
├──────────────────────────────────────────┤
│ Footer: schema + row-group/column stats    │
└──────────────────────────────────────────┘

⚠️ Common mistake: expecting pushdown but actually producing thousands of tiny files of a couple of row groups each — statistics prune almost nothing, and the overhead of opening files eats the win.

Short answer: Parquet and ORC are columnar formats for analytics (compression, pushdown). Avro is a row-based binary format for streaming and messaging with strong schema evolution. CSV/JSON are text formats for exchange and debugging, but not for analytics at scale.

In depth:

1. Column analytics — Parquet or ORC: you read few of many columns and statistics prune blocks.
2. Streaming and messaging — Avro: you write whole records and the schema evolves without rewriting data.
3. Text CSV/JSON — handy and portable, but no types, no statistics and no efficient compression — expensive for large tables.

⚠️ Common mistake: keeping the analytics layer in CSV or raw JSON. At scale that is several times more I/O and money than the same dataset in Parquet.

Short answer: A table format is a metadata layer over Parquet/ORC files in object storage that turns a bag of files into a table with ACID transactions, atomic commits, time travel, and schema and partition evolution. Iceberg, Delta and Hudi solve the same problem, differing in engine details and ecosystem.

In depth:

1. ACID and atomic commits — readers see either the old or the new table version, never half-written files mid-write.
2. Time travel — snapshots by version/time: you can read the table "as it was" and roll back.
3. Schema and partition evolution — add/rename a column and even change the partitioning scheme without rewriting data.
4. Hidden partitioning (Iceberg) — the partition is derived from a value (e.g. from a timestamp), so users needn't filter on a partition column by hand.

-- Iceberg time travel: read the table at a specific snapshot
SELECT * FROM orders
FOR SYSTEM_VERSION AS OF 3821550127947089009;

⚠️ Common mistake: thinking "I dropped Parquet in S3, so I have a table." Without a table format there is no atomicity under concurrent writes, no time travel and no safe schema evolution.

Short answer: Partitioning lays data out into col=value directories so a filtered query reads only the relevant partitions (partition pruning). Pick a column with moderate cardinality that you actually filter on; avoid partitions that are too small and avoid over-partitioning.

In depth:

1. Partition = path prefix — the engine sees year=2026/month=06 and skips other partitions without reading data.
2. Choosing the key — pick a column from frequent WHERE clauses (date, region) with moderate cardinality; high-cardinality keys (user_id) spawn millions of partitions.
3. File size — aim for 128 MB–1 GB files; lots of tiny files = the small-files problem and slow listing in S3.
4. Don't overdo it — thousands of partitions of a couple of files each make metadata listing expensive and barely speed reads.

s3://lake/events/
  year=2026/
    month=06/
      day=30/
        part-0001.parquet   (~256 MB)
        part-0002.parquet   (~256 MB)
    month=07/
      day=01/
        part-0001.parquet

⚠️ Common mistake: partitioning on a high-cardinality column (e.g. user_id) — you get thousands of directories with tiny files, and listing metadata becomes more expensive than the query itself.

Short answer: A codec is a tradeoff between speed and compression ratio. Snappy is fast but compresses less; Gzip compresses more but is slow and not splittable when raw; Zstd offers the best balance with a tunable level. For analytics, splittability matters so a file can be read in parallel.

In depth:

1. Speed vs ratio — Snappy optimizes CPU (frequent reads), Gzip optimizes size (archive/extract), Zstd spans the range via its compression level.
2. Splittability — whether different parts of a file can be read in parallel. Raw Gzip is not splittable: one worker pulls the whole file, losing parallelism.
3. Rescued inside Parquet — in Parquet/ORC compression is per page/chunk, so even Gzip inside Parquet is read in parallel by row groups.
4. In practice — Snappy as the default for hot data, Zstd when storage matters more at acceptable CPU.

⚠️ Common mistake: dropping a big .csv.gz as a single non-splittable file — one worker reads the whole gigabyte and the cluster's parallelism sits idle. Split into files or use Parquet.

Short answer: Schema evolution is changing the data structure without rewriting already-written files and without breaking old readers/writers. Safety depends on compatibility: adding a column with a default is usually safe, dropping and renaming are risky. Avro handles it via schema resolution, table formats via tracking columns by stable ids.

In depth:

1. Add a column — safe with a default: old files still read and the missing value is filled with the default.
2. Drop a column — safe for new-code readers, but an old reader that requires the field breaks — you need backward compatibility.
3. Rename — the most dangerous done naively: by name it's a drop + add. Avro solves it with aliases; Iceberg tracks stable column ids, not names.
4. Compatibility — backward (new schema reads old data), forward (old schema reads new data), full (both at once); in Kafka the Schema Registry guards it.

⚠️ Common mistake: renaming a column by name in a raw Parquet dataset — old files under the old name stop reading as the new column and data silently disappears from queries.

Short answer: Object storage is a flat key-value store of objects over HTTP, not a filesystem: there are no real directories, no cheap rename, and you pay for storage and for requests. Unlike HDFS/POSIX it separates storage from compute and scales almost limitlessly, but renaming a folder means copying every object.

In depth:

1. Flat key space — "directories" are just prefixes in the object name; listing walks a prefix, not a tree.
2. No atomic rename — rename/move = copy + delete per object, so commit-by-folder-rename (as in HDFS) is expensive; hence committers and table formats.
3. Consistency — modern S3 is strongly read-after-write consistent, but historically it was eventually consistent; account for possible delays in older systems.
4. Cost model — you pay per GB stored, per GB transferred and per number of requests (GET/PUT/LIST) — hence the penalty for millions of tiny files.

⚠️ Common mistake: treating S3 like a POSIX filesystem and doing lots of LIST/rename over millions of keys — request bills and listing latency unexpectedly become the pipeline's bottleneck.

Short answer: The small-files problem is when a table is made of thousands of tiny files instead of a few large ones. Each file needs a separate open, footer read and task scheduling, so overhead outweighs useful reading. It's fixed with compaction — periodically merging small files into target-sized ones.

In depth:

1. Where it comes from — frequent micro-batches and streaming, partitions that are too small, many workers each writing its own tiny file.
2. Why it hurts — per file: an S3 request, a metadata read, a separate split/scheduler task; thousands of files = thousands of opens and slow listing.
3. Compaction — a background job reads small files and rewrites them into large ones (aim for 128 MB–1 GB); Iceberg/Delta have built-in operations (OPTIMIZE, rewrite_data_files).
4. Prevention — write less often and larger, buffer batches, sensible partition granularity, don't spawn needless partitions.

Before compaction:  [f1][f2][f3][f4][f5]...[f999]   ← 999 files × ~1 MB
                          tiny files problem
                               │  compaction
                               ▼
After:              [ big-0001.parquet ][ big-0002.parquet ]  ← 2 files × ~256 MB

⚠️ Common mistake: streaming straight into the lake in one-second micro-batches with no follow-up compaction — within a day the table accumulates hundreds of thousands of files and every analytical query over it slows down several-fold.