File Format Optimization

In this lesson, you will learn how to select the ideal storage format—Parquet, Avro, or ORC—by matching file architecture to your specific analytical query patterns.

Understanding the Trade-offs

When building pipelines in your medallion architecture, the physical storage format dictates your performance ceiling. Each format is designed for a different stage of the data lifecycle:

• Avro (Row-Based): Ideal for write-heavy workloads or stream processing. Because Avro stores data row-by-row, it is incredibly efficient for capturing full records during ingestion, especially when schemas evolve frequently. Use Avro when your primary task is landing data into your "Bronze" layer.
• Parquet (Columnar): The industry standard for analytical "Silver" and "Gold" layers. By storing data column-by-column, Parquet allows your query engine to skip irrelevant columns entirely. If you have a table with 100 columns but a dashboard only needs three, Parquet reduces I/O by orders of magnitude compared to row-based formats.
• ORC (Optimized Row Columnar): Similar to Parquet but often preferred in Hive or legacy Hadoop-integrated environments. ORC features more sophisticated indexing (like stripe-level min/max stats), which makes it highly effective for complex, filtered analytical queries where you need to "prune" large chunks of data before processing.

Practical Selection

To optimize your pipeline, follow this heuristic:

1. Ingestion/CDC: Use Avro for high-velocity, append-only streams.
2. Ad-hoc Analytics: Use Parquet if you are using modern MPP engines (like Spark, Trino, or cloud data warehouses) that leverage predicate pushdown.
3. High-Cardinality Filtering: Use ORC if your storage layer requires deep, index-based skipping for specific analytical dimensions.

-- Example: Conceptually defining a table optimized for Parquet
-- The engine ignores columns not selected, saving IOPS
CREATE TABLE gold_sales_metrics
STORED AS PARQUET
AS SELECT product_id, total_revenue 
FROM silver_sales_data;

When you choose the right format, you aren't just storing bytes; you are configuring your database's physical layout to minimize the work required for every subsequent SQL query.

Key Takeaway: Choose Avro for write-intensive ingestion, Parquet for standard columnar analytical efficiency, and ORC for advanced, index-heavy filtering requirements.