Handbook

✅ Export data from a database for a colleague
✅ Share data with non-technical users (opens in Excel)
✅ Small datasets (< 100 MB)
✅ Simple reporting or one-time analysis
✅ Testing/prototyping data pipelines

❌ Large-scale analytics (use Parquet instead)
❌ Complex nested data (use JSON instead)
❌ When data contains lots of commas (use TSV)

Use case: Monthly sales report for business team
- Export 50,000 sales records to CSV
- Email to business analysts
- They open in Excel, make pivot tables
- Perfect! CSV is simple and works everywhere

✅ Data with addresses ("123 Main St, Apt 5, NYC")
✅ Text that naturally contains commas
✅ Database exports (PostgreSQL COPY)
✅ When CSV parsing keeps breaking

❌ Sharing with Excel users (CSV is more common)
❌ When data contains both commas AND tabs

Use case: Exporting customer addresses
Customers: 10,000 records with addresses

CSV problem: "John Smith, 123 Main St, Apt 5"
Result: Parser thinks these are 3 columns!

TSV solution: John Smith[TAB]123 Main St, Apt 5
Result: Works perfectly! ✅

✅ Application logs
✅ Error messages
✅ Documentation/notes
✅ Simple data dumps
✅ Human-readable output

❌ Structured data (use CSV/JSON)
❌ Machine parsing (too unstructured)
❌ Large-scale processing

Use case: Application error logs
2025-01-15 10:30:00 ERROR: Database connection timeout
2025-01-15 10:30:05 ERROR: Retry attempt 1 failed
2025-01-15 10:30:10 INFO: Connection restored

Perfect for debugging, grep searching, human reading

✅ REST API responses
✅ Configuration files (settings.json)
✅ NoSQL databases (MongoDB)
✅ Nested/hierarchical data
✅ JavaScript/web applications
✅ When data has varying structure

❌ Large datasets (use Parquet)
❌ High-performance analytics
❌ When file size matters (too verbose)

Example 1: API response
GET /api/users/123
{
  "id": 123,
  "name": "John",
  "orders": [
    {"id": 1, "total": 99.99},
    {"id": 2, "total": 149.99}
  ]
}

Example 2: Application config
{
  "database": {
    "host": "localhost",
    "port": 5432,
    "credentials": {...}
  },
  "features": {
    "darkMode": true,
    "notifications": false
  }
}

Perfect for nested, hierarchical data!

✅ Legacy enterprise systems (you have no choice!)
✅ SOAP APIs (older web services)
✅ When you need strict schema validation (XSD)
✅ Microsoft Office formats (docx, xlsx are XML!)
✅ RSS feeds, sitemaps

❌ New projects (use JSON)
❌ Performance-critical systems (too verbose)
❌ Human readability (JSON is cleaner)

Use case: Enterprise SOAP API integration
Your company's 20-year-old ERP system only speaks XML

Request:
<soap:Envelope>
  <soap:Body>
    <GetCustomer>
      <CustomerId>12345</CustomerId>
    </GetCustomer>
  </soap:Body>
</soap:Envelope>

You don't choose XML - XML chooses you! 😅

✅ Kubernetes manifests
✅ Docker Compose files
✅ CI/CD pipelines (GitHub Actions, GitLab CI)
✅ Configuration files (human-edited frequently)
✅ When you need comments in config

❌ APIs (use JSON)
❌ Large data files (use Parquet)
❌ When precision matters (whitespace errors!)

Example 1: Docker Compose
version: '3'
services:
  web:
    image: nginx
    ports:
      - "80:80"
  db:
    image: postgres
    environment:
      POSTGRES_PASSWORD: secret

Example 2: GitHub Actions
name: Deploy
on:
  push:
    branches: [main]
jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - run: npm test

Clean, readable, perfect for DevOps!

❌ Never for new projects!
✅ Only if forced to support old Excel (pre-2007)
✅ Legacy systems that can't be upgraded

Real talk: If someone asks for XLS, give them XLSX instead

✅ Sharing data with business users (Excel is universal)
✅ Reports with formatting, charts, formulas
✅ Multiple sheets in one file
✅ < 100K rows (stays performant)
✅ Monthly/quarterly business reports

❌ Large datasets (>1M rows hits Excel limits)
❌ Data pipeline intermediate storage (use Parquet)
❌ Production systems (too slow)
❌ Version control (binary, can't diff)

Example 1: Monthly executive dashboard
- Sheet 1: Revenue by region (with chart)
- Sheet 2: Top 10 customers (formatted table)
- Sheet 3: YoY comparison (with formulas)
Send to CEO - they open in Excel, perfect! ✅

Example 2: Ad-hoc analysis for business analyst
- Export 50K customer records
- Business analyst adds pivot tables
- Shares with their manager
- Everyone has Excel, everyone's happy!

Example 3: When NOT to use XLSX
- 5 million log records ❌
- Real-time data pipeline ❌
- Use Parquet instead!

✅ Large datasets (> 100 MB)
✅ Analytics queries (SELECT specific columns)
✅ Data warehouses / data lakes
✅ Apache Spark / Hadoop processing
✅ When you need compression (10x smaller than CSV!)
✅ Cloud storage (S3, GCS) - saves $$

❌ Streaming data (use Avro)
❌ Transactional updates (use Delta Lake)
❌ Need Excel compatibility (use CSV/XLSX)
❌ Very small files (< 1 MB, overhead not worth it)

Example 1: Data warehouse storage
Situation: 1 billion e-commerce orders
- CSV size: 500 GB
- Parquet size: 50 GB (10x smaller!)
- Query time: 100x faster for analytics
- Cloud storage cost: 10x cheaper
Perfect choice! ✅

Example 2: Spark analytics pipeline
Raw data (CSV) → Transform in Spark → Save as Parquet
- Read 100 GB CSV: 10 minutes
- Save as Parquet: 10 GB
- Future queries: 10 seconds (100x faster!)

Example 3: AWS Athena / Google BigQuery
Upload Parquet to S3 → Query with SQL
- 1 TB CSV: $5 per query scan
- 100 GB Parquet: $0.50 per query scan
10x cost savings!

✅ Using Apache Hive extensively
✅ Heavy Hadoop ecosystem (not Spark)
✅ Need best possible compression
✅ ACID transactions in Hive

❌ Using Spark (Parquet is better integrated)
❌ Using Python/Pandas (Parquet has better support)
❌ Cloud-native (Parquet is more common)

Example 1: Legacy Hadoop cluster
Company has:
- 1000-node Hadoop cluster
- All data in Hive
- Hive queries for everything
Use ORC! Perfect fit ✅

Example 2: Modern cloud stack
Company has:
- AWS S3 data lake
- Spark for processing
- Python for data science
Use Parquet! Better ecosystem ✅

Rule of thumb:
Hive/Hadoop → ORC
Everything else → Parquet

✅ Apache Kafka streaming (most common use!)
✅ Schema evolution (adding fields without breaking)
✅ High-throughput writes
✅ When schema changes frequently
✅ Event streaming / message queues

❌ Analytics queries (use Parquet)
❌ Data warehouse storage (use Parquet)
❌ When schema never changes

Example 1: Kafka event streaming (MOST COMMON)
Producer: Website clicks → Kafka (Avro format)
Consumer: Read from Kafka → Process events

Why Avro?
- Fast writes (1M events/sec)
- Schema embedded (self-documenting)
- Compact binary format
- Schema Registry handles evolution

Example 2: Schema evolution
Week 1: User event { user_id, action }
Week 5: Add field { user_id, action, timestamp }
Week 10: Add field { user_id, action, timestamp, device }

Old consumers still work! Avro handles it ✅

Example 3: When NOT to use Avro
Storing 1 TB of sales data for analytics ❌
- Avro is row-based (slow for analytics)
- Use Parquet instead (100x faster queries)

Rule of thumb:
Streaming/Kafka → Avro
Analytics/Storage → Parquet

✅ Database migration (MySQL → PostgreSQL)
✅ Sharing database schema with team
✅ Version controlling database structure
✅ Small database backups (< 100 MB)
✅ Seeding test databases

❌ Large production backups (too slow)
❌ Big data transfers (use binary dumps)
❌ Analytics (export to Parquet)

Example 1: Share database schema with new developer
$ pg_dump --schema-only my_db > schema.sql
Send schema.sql to developer
They run: psql < schema.sql
Perfect! They have exact same structure ✅

Example 2: Version control migrations
git repository:
migrations/
  001_create_users.sql
  002_add_email_column.sql
  003_create_orders.sql
Team always knows database history!

Example 3: When NOT to use SQL
Backing up 500 GB production database ❌
- SQL dump takes 10 hours
- Use pg_dump -Fc (binary) instead: 30 minutes

✅ Temporary ML model storage (prototyping)
✅ Caching Python objects during development
✅ Saving complex Python data structures
✅ Quick experiments / notebooks

❌ Production ML models (use ONNX, SavedModel)
❌ Sharing between languages
❌ Long-term storage (pickle breaks across Python versions!)
❌ Untrusted data (SECURITY RISK! Can execute code)

Example 1: ML model prototyping (OK for dev)
from sklearn.ensemble import RandomForestClassifier
model = RandomForestClassifier()
model.fit(X_train, y_train)
pickle.dump(model, open('model.pkl', 'wb'))

Dev environment: ✅ Fast and easy
Production: ❌ Use proper model serving

Example 2: Caching expensive computations
expensive_result = run_10_hour_computation()
pickle.dump(expensive_result, 'cache.pkl')
# Next time: just load in 1 second!

Example 3: THE DANGER ⚠️
# Someone sends you data.pkl
import pickle
data = pickle.load(open('data.pkl', 'rb'))  # ☠️ DANGER!
# This can execute arbitrary code! Could delete your files!

NEVER load pickle from untrusted sources!

Example 4: Version incompatibility
# Python 3.8: save model
pickle.dump(model, 'model.pkl')

# Python 3.11: load model
model = pickle.load('model.pkl')  # ERROR: incompatible!

Use JSON/ONNX for long-term storage

✅ Compress any file before storing/sending
✅ Log files (they compress amazingly well!)
✅ CSV files for storage
✅ Transferring data over network
✅ Cloud storage (S3, GCS) - save costs

❌ Already compressed files (JPEG, PNG, video)
❌ When you need to read data without decompressing

Example 1: Saving storage costs on S3
data.csv: 1 GB
data.csv.gz: 100 MB (10x smaller!)
S3 cost: $0.023/GB/month
- CSV: $0.023/month
- GZ: $0.0023/month
10x savings! ✅

Example 2: Log file compression
application.log: 10 GB/day
application.log.gz: 500 MB/day (20x smaller!)
Store 30 days of logs:
- Uncompressed: 300 GB
- Compressed: 15 GB
Massive savings!

Example 3: Pandas can read GZ directly!
import pandas as pd
df = pd.read_csv('data.csv.gz')  # Automatic decompression!
No need to decompress first ✅

Rule: Always gzip CSVs before storing

✅ Distributing software / code
✅ Sharing multiple files with users
✅ Email attachments (multiple files)
✅ Archiving project backups
✅ Website downloads

❌ Single large file (use gzip instead)
❌ Production data pipelines (Parquet has better compression)

Example 1: Sending project files to client
project/
  code/
  docs/
  images/
  
$ zip -r project.zip project/
Email project.zip to client
Client unzips - gets exact folder structure ✅

Example 2: Monthly report package
monthly_report.zip containing:
  - sales_report.xlsx
  - customer_data.csv
  - charts/graph1.png
  - charts/graph2.png
  - readme.txt

Perfect for business users!

Example 3: Code repository downloads
GitHub → Download as ZIP
Get entire repository in one file

✅ Long-term archival (best compression)
✅ When storage space is expensive
✅ When compression time doesn't matter
✅ Maximum compression needed

❌ Real-time processing (too slow)
❌ Frequently accessed files
❌ Most normal use cases (use gzip instead)

Compressing 1 GB log file:

GZIP:
- Result: 100 MB
- Time: 10 seconds
- Use for: Active logs ✅

BZIP2:
- Result: 80 MB (20% smaller!)
- Time: 60 seconds (6x slower)
- Use for: Archive old logs ✅

Rule of thumb:
- Active files → GZIP (fast)
- Archive/storage → BZIP2 (smaller)

✅ Need ACID transactions on data lake
✅ Frequent UPDATE/DELETE operations
✅ Need to undo changes (time travel)
✅ Using Databricks or Apache Spark
✅ Data quality requirements (schema enforcement)
✅ Regulatory compliance (audit trail)

❌ Simple read-only analytics (Parquet is simpler)
❌ Not using Spark ecosystem
❌ Real-time streaming (consider Hudi)

Example 1: GDPR compliance (Delete user data)
Traditional Parquet:
- Find user data across 1000 files
- Rewrite all files without that user
- Hours of work! ❌

Delta Lake:
DELETE FROM users WHERE user_id = 12345;
- Done in seconds!
- Transactional - either all data deleted or none
- Can rollback if mistake! ✅

Example 2: Time travel for debugging
User: "Report was correct yesterday, wrong today!"

Traditional:
- No way to see yesterday's data ❌

Delta Lake:
SELECT * FROM revenue 
  VERSION AS OF 5;  -- Yesterday's version
  
Find exactly what changed! ✅

Example 3: Concurrent writes
Situation: 10 Spark jobs writing to same table

Traditional Parquet:
- Data corruption possible! ❌
- Manual coordination needed

Delta Lake:
- ACID transactions prevent corruption ✅
- All writes succeed or fail cleanly

Example 4: Slowly changing dimensions
UPDATE customers 
  SET address = '123 New St' 
  WHERE customer_id = 456;
  
Traditional: Rewrite entire file
Delta Lake: Just write changes, super fast!

✅ Multi-engine environment (Spark + Trino + Flink)
✅ Need vendor independence (avoid lock-in)
✅ Large tables with complex partitioning
✅ Open-source requirements
✅ Using AWS, Azure, or GCP (good cloud support)

❌ Only using Databricks (Delta Lake is better integrated)
❌ Simple use case (Parquet is simpler)
❌ Small team (Delta Lake has more resources)

Example 1: Multi-engine analytics platform
Company uses:
- Apache Spark for ETL
- Trino for interactive queries  
- Flink for streaming

Delta Lake: Only works well with Spark ❌
Iceberg: Works with all three! ✅

Example 2: Hidden partitioning (awesome feature!)
Traditional:
SELECT * FROM events 
WHERE date = '2025-01-15'
You must remember table is partitioned by date!

Iceberg:
SELECT * FROM events 
WHERE timestamp = '2025-01-15 10:30:00'
Iceberg automatically finds right partition! ✅

Example 3: Avoiding vendor lock-in
Databricks offers Delta Lake... but worried about lock-in?
Iceberg is vendor-neutral, works anywhere ✅
- AWS Athena: ✅
- Google BigQuery: ✅  
- Azure Synapse: ✅
- Apache Spark: ✅

✅ High-frequency updates (every minute/second)
✅ CDC (Change Data Capture) from databases
✅ Streaming ingestion from Kafka
✅ Need incremental processing (only read new data)
✅ Uber-style use case (constantly updating data)

❌ Read-only data lake (use Parquet)
❌ Batch updates only (Delta Lake or Iceberg simpler)
❌ Small team (less documentation than Delta/Iceberg)

Example 1: Real-time CDC from database
MySQL → Debezium → Kafka → Hudi

Updates streaming every second!
- User updates profile: Immediate
- Order status changes: Immediate  
- Analytics always current ✅

Hudi's Merge-on-Read perfect for this!

Example 2: Incremental processing
Situation: Daily ETL on 1 billion records

Traditional:
- Process entire 1 billion records daily
- 8 hours to complete ❌

Hudi:
- Read only records changed today (1 million)
- 5 minutes to complete ✅
- 100x faster!

Example 3: Uber ride updates
10 million rides in progress
Each ride status updates every 30 seconds
- Traditional: Impossible to keep current ❌
- Hudi: Designed for exactly this! ✅

Rule of thumb:
- Batch updates → Delta Lake or Iceberg
- Streaming updates → Hudi

CSV → Parquet → Delta Lake

❌ model.pkl  (can't read in 2 years)
✅ model.json (always readable)