DuckDB (In-Process Zero-Copy Execution):
Python / Rust App ──► [ DuckDB runs INSIDE app RAM ] ──► Direct Zero-Copy Apache Arrow pointers!
(Zero network hops, zero socket serialization, sub-millisecond local execution!) ✅

ClickHouse (Distributed Clustered Lakehouse):
100 Microservices ──► [ 12-Node ClickHouse Cluster: 10M writes/sec ] ──► Serves 5,000 QPS concurrent dashboards! ✅

┌──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ Dimension        │ DuckDB (v1.2+)                │ ClickHouse (v26+)             │
├──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ Architecture     │ **Embedded In-Process**       │ **Client-Server Clustered**   │
│                  │ (C++ Library linked in app)   │ (Distributed MPP Nodes)       │
├──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ Primary Scale    │ Single-Node (Megabytes to     │ **Multi-Node Cluster**        │
│ Horizon          │ Terabytes fitting on NVMe)    │ (Gigabytes to Multi-Petabytes)│
├──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ Concurrency      │ Single/Few Writers            │ **Thousands of Concurrent**   │
│ Model            │ (Multi-threaded embedded)     │ Real-Time Dashboard Queries   │
├──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ External Data    │ **Direct Querying on Parquet, │ Native S3/HDFS tables and     │
│ Formats          │ Arrow, Delta, Iceberg on S3** │ remote object storage engines │
├──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ Zero-Copy Arrow  │ Native Zero-Copy memory       │ Requires HTTP/Native TCP      │
│ Integration      │ sharing with Pandas/Polars    │ network socket transfer       │
└──────────────────┴───────────────────────────────┴───────────────────────────────┘

# duckdb_analytics.py - Zero-Copy Local Parquet & Polars Analytics
import duckdb
import polars as pl

# 1. Initialize In-Process DuckDB Engine in RAM
con = duckdb.connect(database=":memory:")

# 2. Query 50GB of Remote S3 Parquet files directly using HTTP range requests!
con.execute("""
    INSTALL httpfs;
    LOAD httpfs;
    SET s3_region='us-east-1';
    
    CREATE VIEW s3_sales AS 
    SELECT * FROM read_parquet('s3://enterprise-lakehouse/sales/*.parquet');
""")

# 3. Vectorized Aggregation running across all local CPU cores
query = """
    SELECT 
        tenant_id,
        to_char(order_date, 'YYYY-MM') AS month,
        count(*) AS total_orders,
        sum(revenue_usd) AS gross_revenue,
        quantile_cont(0.95)(latency_ms) AS p95_latency
    FROM s3_sales
    GROUP BY tenant_id, month
    ORDER BY gross_revenue DESC
    LIMIT 20;
"""

# 4. Zero-Copy conversion directly into Polars DataFrame without data copying!
polars_df: pl.DataFrame = con.execute(query).pl()
print(polars_df)

-- ClickHouse SQL: Distributed Lakehouse Cluster Table
CREATE TABLE default.sales_distributed ON CLUSTER production_cluster
(
    tenant_id UUID,
    order_date DateTime CODEC(DoubleDelta, ZSTD),
    revenue_usd Float64 CODEC(Gorilla, ZSTD),
    latency_ms UInt32 CODEC(T64, ZSTD)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/sales', '{replica}')
PARTITION BY toYYYYMM(order_date)
ORDER BY (tenant_id, order_date);

Query Execution Speed on 100M Parquet Rows (Seconds - Lower is Better):
┌─────────────────────────────────────────────────────────┐
│ DuckDB Embedded:       ██ 1.14 s                        │
│ ClickHouse Single:     ███ 1.82 s                       │
│ ClickHouse 4-Node:     █ 0.48 s (Fastest Multi-Node!) 🏆│
└─────────────────────────────────────────────────────────┘

┌──────────────────────────────────────┬──────────────────────────────────────┐
│ CHOOSE DUCKDB IF:                    │ CHOOSE CLICKHOUSE IF:                │
├──────────────────────────────────────┼──────────────────────────────────────┤
│ 1. Data analysis runs inside CLI     │ 1. You are building user-facing      │
│    tools, Python notebooks, or lambdas│    dashboards serving 1000+ users    │
│ 2. You want zero server management   │ 2. Continuous real-time streaming    │
│ 3. You need to query S3 Parquet /    │    inserts from Kafka/Flink (CDC)    │
│    Delta lake directly in-process    │ 3. Data size exceeds single NVMe disk│
└──────────────────────────────────────┴──────────────────────────────────────┘