Skip to content

Skill 1.1.5: Ingest from and write by using appropriate data formats (e.g., Apache Parquet, JSON, CSV, ORC) based on data access patterns

Task 1.1: Data Preparation for ML and AI – Collect and store data

The Governing Rule

Format follows the access pattern, not the system that produced the data. The producer's native format (CSV from an export, JSON from an API, binary from a device) is a source constraint, not a storage decision. Convert at the landing/curated boundary.

  • Two questions decide the format: (1) Do reads touch all fields of a few records (row) or a few fields of many records (columnar)? (2) Is the write pattern append-at-a-time (row) or bulk-replace (columnar)?
  • Cost is downstream of format: Athena/Redshift Spectrum bill per TB scanned. Columnar + compression + partitioning cuts scanned bytes by 10–100x versus CSV/JSON on the same data.
  • Columnar files are immutable. "Appending" always means writing a new file. Mutable-row semantics require an open table format (Iceberg/Hudi/Delta), not Parquet alone.

Format Comparison Matrix

Format Layout Schema Nested Splittable Write Pattern Read Pattern That Wins ML/AI Use
CSV Row, text None (inferred) No Yes* Append Small interchange, manual inspection Quick baselines, SageMaker built-in algos (File mode)
JSON Row/document, text Self-describing, loose Yes Yes (JSON Lines)* Append Semi-structured records, nested payloads API ingestion, Ground Truth manifests, config
Apache Avro Row, binary Embedded writer schema Yes Yes Append, streaming Write-heavy landing, schema evolution Kafka/MSK landing zone, streaming sinks
Apache Parquet Columnar, binary Embedded (footer) Yes (Dremel) Yes Bulk/append-by-file Analytic scan, projection + predicate pushdown Default curated layer; Athena/Spark/EMR/SageMaker
Apache ORC Columnar, binary Embedded (footer) Yes Yes Bulk/append-by-file Hive/Trino analytic scan, ACID Hive tables EMR/Hive-centric lakes
RecordIO-Protobuf Row, binary Implicit No Yes Bulk Sequential streaming consumption SageMaker built-in algos, Pipe mode
TFRecord Row, protobuf None (external) Yes Limited Append tf.data pipelines TensorFlow training
libsvm Row, sparse text None No Yes Bulk Sparse high-dimensional features SageMaker Linear Learner, FM
Arrow / Feather Columnar, in-memory Embedded Yes N/A In-memory Zero-copy IPC between processes Not a storage format — interchange only

* Splittability caveat: CSV/JSON lose splittability when whole-file compressed (.csv.gz). Parquet/ORC keep it regardless of codec (compression is at page/row-group level inside the file).

Row vs Columnar — The Core Trade-off

Dimension Row-Oriented (CSV, JSON, Avro) Columnar (Parquet, ORC)
Read amplification Reads entire record to get one field Reads only requested columns (projection pushdown)
Filter efficiency Full scan Row-group/stripe min-max stats + bloom filters (predicate pushdown)
Compression Weaker — mixed types per block Strong — same-type values per block (dictionary, RLE, delta encoding)
Write cost Cheap per-record append Expensive per-record; designed for bulk writes
Single-record lookup Fine Poor — wrong tool (use DynamoDB/RDS)
Schema evolution CSV: silent column shift. JSON: permissive Parquet: add columns at end → old files read as NULL; renaming/reordering breaks readers
Human readability Yes No (a compliance/governance feature, not a control)

Parquet vs ORC (common distractor): functionally equivalent for most engines. Parquet has the broader ecosystem (Spark, Athena, Presto, Impala, BigQuery, Redshift Spectrum, Arrow); ORC has denser stripe-level indexes and Hive ACID support and is preferred in Hive/Trino-centric stacks. Neither is universally "faster."

Exam trap: "Convert everything to CSV so any tool can read it." For analytic workloads this is the single most expensive anti-pattern — it forces full-column scans and eliminates pushdown.

Compression — Splittability Is the Hidden Constraint

Codec Splittable (Hadoop/Spark whole-file) Ratio CPU Cost Notes
None Yes 1x — Maximum parallelism
Snappy Yes (block) Moderate Low Parquet default; not designed for max ratio
LZ4 Yes (block) Moderate Low Low-latency
Zstandard Yes (seekable frames) High Low–Med Modern default for Parquet/ORC
Gzip No High Medium Kills parallelism on .csv.gz/.json.gz
Bzip2 Yes High Very High Splittable but slow
LZO Yes (with index) Moderate Low Indexing step required

Hard rule: A single .csv.gz or .json.gz in S3 = one mapper / one task. All parallelism is lost. This is the classic "job slows as data grows" symptom — the fix is format/layout, not more compute.

Nuance that trips candidates: Inside Parquet/ORC, compression operates on pages/stripes, so the file stays splittable no matter which codec is used. Gzip's splittability problem applies to whole-file compression only.

Write-Side Design (S3 Layout)

  • File sizing: target 128 MB – 1 GB per file. Millions of tiny files cause per-object listing, request, and open overhead that dwarfs scan cost.
  • Partitioning: Hive-style prefixes (year=/month=/day=/region=) enable partition pruning. Partition on low-cardinality filter columns only.
  • Sorting within files: sort by the highest-selectivity filter column so row-group min/max statistics skip more data.
  • Compaction: schedule periodic jobs to merge small files — Glue ETL, EMR, or an open table format's built-in compaction.
  • Catalog registration: Glue Crawlers populate the Data Catalog; partition metadata must be registered (MSCK REPAIR-equivalent or partition projection) or pruning silently degrades to a full scan.
  • Crawler gotchas: JSON needs a JSON classifier with a jsonPath for multi-document files; headerless CSV needs allowSingleColumn/custom classifier; crawlers do not resolve schema conflicts across sources — that is an engineering decision you document.

SageMaker Training Data Modes

Mode Mechanism Supported Formats Best For Constraint
File Full download from S3 to local EBS before training starts Any Small/medium datasets, many epochs, random access Startup latency + EBS capacity scale with dataset size
Pipe Streams directly from S3 into the container, no local copy RecordIO-Protobuf, RecordIO-WebDataset, Augmented Manifest Very large datasets, fast start, minimal disk Cannot stream arbitrary CSV/Parquet
FastFile POSIX-like streaming with random access, no full download Any Large datasets needing random access / multi-epoch Newer default recommendation for large data

Pipe mode is format-locked. Built-in algorithms stream RecordIO-Protobuf; raw images/audio/video require an augmented manifest. Choosing CSV as the training artifact forces File mode and forfeits Pipe-mode startup savings.

  • Augmented Manifest: JSON Lines file where each line carries a source-ref (S3 URI to the object) plus label attributes. This is also the native output format of SageMaker Ground Truth.
  • Format↔algorithm mapping: XGBoost/Linear Learner accept CSV, libsvm, Parquet (File mode); image/video algorithms accept RecordIO or augmented manifest (Pipe mode).

Streaming Ingestion Formats

  • Kinesis Data Streams: format-agnostic byte payloads with a partition key. Ordering is guaranteed within a shard only; the consumer owns deserialization.
  • Kinesis Data Firehose: can perform in-flight JSON → Parquet/ORC conversion — requires a source of JSON and a Glue Data Catalog table for the target schema. Also supports GZIP/Snappy/Zip/Hadoop-Snappy compression, buffer size/time hints, and dynamic partitioning.
  • Amazon MSK / Apache Kafka: standardize on Avro + Schema Registry for schema evolution, or Protobuf for compactness. Row-oriented formats win because the write pattern is append-per-record.
  • Managed Service for Apache Flink: consumes Kinesis/MSK, applies windowed transforms, and writes columnar output (Parquet) to S3 for downstream analytic/training reads.
  • Backpressure diagnosis: a consumer falling progressively behind is either under-provisioned or blocked downstream. Adding consumers to a pipeline blocked on a slow write target just relocates the queue — scale the target first.

Unstructured & Multimodal Data

  • Binaries stay in S3 (images, audio, video, PDFs). Metadata, labels, and embeddings go in Parquet / DynamoDB / Feature Store so they are queryable and traceable back to the source object.
  • Text corpora: JSON Lines for raw ingestion; Parquet with a text column for curated storage; embeddings as Parquet float-array columns or pushed into a vector store.
  • Multi-shard large-scale training: WebDataset / RecordIO-WebDataset shards for sequential streaming; TFRecord for TensorFlow-native pipelines.
  • PyTorch/TensorFlow over Parquet: use Petastorm or an Arrow-backed reader rather than converting to TFRecord, avoiding a redundant copy.

Open Table Formats (When Plain Parquet Is Not Enough)

Format Strength Typical Engine
Apache Iceberg Hidden partitioning, snapshot isolation, time travel, row-level deletes, schema evolution Athena, EMR, Glue, Spark
Apache Hudi Upserts + incremental pulls; copy-on-write vs merge-on-read EMR, Spark
Delta Lake ACID transactions + time travel on object storage EMR, Spark
  • Use them to solve: small-file accumulation, concurrent writers, GDPR/CCPA row-level deletes, and schema evolution — none of which plain Parquet handles.
  • They do not replace the format decision — Parquet/ORC remain the underlying columnar file format.

Vector Stores & Feature Store Formats

  • Vector store selection: OpenSearch Service (k-NN/HNSW, low-latency ANN), RDS PostgreSQL + pgvector (embeddings colocated with relational records — no data movement), S3 Vectors (large, cost-optimized, less frequent querying).
  • Always store alongside each embedding: source document ID, chunk ID, embedding model version, and timestamp. Without the pointer you cannot cite, refresh, or re-embed.
  • SageMaker Feature Store:
  • Offline store → Parquet in S3 + Glue Data Catalog. Serves batch training with point-in-time-correct joins using the event time.
  • Online store → low-latency single-record lookup of the latest values for inference.
  • Requires a record identifier name and an event time feature name at feature group creation.
  • Ingest via streaming PutRecord or batch ingestion jobs (Spark/Glue).

  • The reason Feature Store exists: training–serving skew. One feature definition serving both paths eliminates the divergence that produces models that test well and disappoint in production.

  • Feature Store ≠ vector store. Feature store answers "for this entity, what are its values?" Vector store answers "what is similar to this embedding?"

Security, Governance & Compliance

  • Encryption at rest: S3 SSE-S3 / SSE-KMS / DSSE-KMS / SSE-C. KMS customer-managed keys give you key rotation, audit trails, and cross-account grants.
  • Columnar-native encryption: Parquet Modular Encryption encrypts selected columns/fields with KMS-managed keys; ORC supports column-level encryption in Hive. Use when a single dataset mixes PII and non-PII columns.
  • Encryption in transit: enforce TLS-only bucket policies (aws:SecureTransport).
  • Network isolation: S3 gateway VPC endpoint + interface endpoints for Glue/Athena/Feature Store keep ingestion traffic off the public internet.
  • Fine-grained access: Lake Formation enforces table/column/row-level and tag-based permissions across Athena, EMR, and Redshift Spectrum; the Glue Data Catalog is the enforcement point.
  • PII discovery: Macie scans S3 for sensitive data — run it before choosing partition keys, since a partition like ssn= leaks data through the path itself.
  • Retention & residency: S3 Lifecycle transitions to Glacier/Deep Archive; S3 Object Lock for WORM immutability; region pinning for data residency.

  • Format is not a security control. Parquet's binary encoding obscures data but does not protect it. Encryption and access policy do.

Anti-Patterns & Distractor Traps

  • .csv.gz / .json.gz at scale → single-task bottleneck; no split parallelism.
  • Pretty-printed multi-line JSON → not line-splittable; use JSON Lines (NDJSON).
  • Millions of small files → per-object overhead dominates; compact to 128 MB–1 GB.
  • Format inherited from the source system → the defining anti-pattern of this skill.
  • Columnar for point lookups → wrong tool; OLTP key lookups belong in DynamoDB/RDS.
  • Partitioning on high-cardinality keys (user_id, request_id) → partition explosion and catalog bloat.
  • In-place Parquet overwrite → impossible; Parquet is immutable. Use new files + an open table format for mutation.
  • Schema drift ignored → CSV silently misaligns columns; Parquet/ORC/Avro detect or evolve.
  • JSON in Athena at scale → order-of-magnitude higher scan cost than Parquet/ORC.
  • Embeddings without source pointers → cannot cite, refresh, or re-embed after a model upgrade.
  • "ORC always beats Parquet" → false; equivalent for most engines, ecosystem differs.
  • "Pipe mode works with any format" → false; RecordIO-Protobuf/WebDataset or augmented manifest only.
  • "Gzip always for best compression" → false at whole-file scope; it destroys parallelism.