Row-based (OLTP):
  Row 1: [id=1, name="Alice", amount=100, date="2025-06-15"]
  Row 2: [id=2, name="Bob",   amount=200, date="2025-06-15"]
  Row 3: [id=3, name="Carol", amount=150, date="2025-06-16"]

Columnar (OLAP):
  id:     [1, 2, 3]
  name:   ["Alice", "Bob", "Carol"]
  amount: [100, 200, 150]
  date:   ["2025-06-15", "2025-06-15", "2025-06-16"]

                 ┌─────────────┐
Client query ──▶ │   Leader     │  ← Parses, optimises, distributes
                 │   Node       │
                 └──────┬──────┘
           ┌────────────┼────────────┐
           ▼            ▼            ▼
     ┌──────────┐ ┌──────────┐ ┌──────────┐
     │ Compute  │ │ Compute  │ │ Compute  │
     │ Node 1   │ │ Node 2   │ │ Node 3   │
     │ (slice)  │ │ (slice)  │ │ (slice)  │
     └──────────┘ └──────────┘ └──────────┘

-- BigQuery SQL (standard SQL with extensions)
SELECT
    DATE_TRUNC(order_date, MONTH) AS order_month,
    country,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    APPROX_QUANTILES(amount, 100)[OFFSET(50)] AS median_amount
FROM `project.dataset.orders`
WHERE order_date >= '2025-01-01'
GROUP BY 1, 2
ORDER BY total_revenue DESC;

-- Redshift: create a table with distribution and sort keys
CREATE TABLE fact_orders (
    order_id    BIGINT ENCODE az64,
    customer_id BIGINT ENCODE az64,
    order_date  DATE   ENCODE delta,
    amount      DECIMAL(10,2) ENCODE az64,
    status      VARCHAR(20) ENCODE lzo
)
DISTKEY(customer_id)      -- distribute rows by customer_id
SORTKEY(order_date);       -- sort by order_date for range queries

-- Snowflake: virtual warehouse management
ALTER WAREHOUSE analytics_wh SET
    WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 300     -- suspend after 5 minutes idle
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 3; -- multi-cluster auto-scaling

-- Clustering key for large tables
ALTER TABLE fact_orders CLUSTER BY (order_date, customer_id);

-- Query with result caching
SELECT country, SUM(amount) AS revenue
FROM fact_orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY country;
-- Second execution returns cached result instantly

-- ClickHouse: create a table with MergeTree engine
CREATE TABLE events (
    event_date Date,
    user_id    UInt64,
    event_type LowCardinality(String),
    duration   Float32,
    properties String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_date);

-- Fast aggregation query
SELECT
    event_type,
    count() AS cnt,
    avg(duration) AS avg_duration,
    quantile(0.95)(duration) AS p95_duration
FROM events
WHERE event_date >= '2025-06-01'
GROUP BY event_type
ORDER BY cnt DESC;

-- BigQuery external table
CREATE EXTERNAL TABLE dataset.external_logs
OPTIONS (
    format = 'PARQUET',
    uris = ['gs://my-bucket/logs/year=2025/*']
);

-- Redshift Spectrum
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'my_glue_db'
IAM_ROLE 'arn:aws:iam::123456789:role/redshift-spectrum-role';

SELECT * FROM spectrum_schema.external_logs WHERE log_date = '2025-06-15';

-- Snowflake external table
CREATE EXTERNAL TABLE ext_logs (
    log_date DATE AS (VALUE:log_date::DATE),
    message  STRING AS (VALUE:message::STRING)
)
WITH LOCATION = @my_s3_stage/logs/
FILE_FORMAT = (TYPE = PARQUET);