Lakehouse OLTP Platform

Problem Being Solved

Root Cause: OLTP and OLAP have historically required different storage formats:

• Row vs Columnar: OLTP needs row-oriented for point lookups; OLAP needs columnar for scans
• Different File Formats: PostgreSQL uses heap files; warehouses use Parquet/ORC
• No Shared Standard: Proprietary formats prevent direct querying
• ETL Required: Must transform and copy data between systems

HeliosDB Solution: Unified Lakehouse

Key Capabilities

2. Real-Time Analytics (Zero ETL)

-- Same table, analytical query (immediate, no ETL)
SELECT
    DATE_TRUNC('hour', order_date) AS hour,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE
GROUP BY 1
ORDER BY 1;
-- Data is current to the second, not hours/days old

-- Complex analytics on live data
WITH hourly_trends AS (
    SELECT
        DATE_TRUNC('hour', order_date) AS hour,
        customer_segment,
        SUM(total_amount) AS revenue,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY 1, 2
)
SELECT * FROM hourly_trends
WHERE revenue > (SELECT AVG(revenue) FROM hourly_trends)
ORDER BY revenue DESC;

3. Time Travel Queries

-- Query historical state
SELECT * FROM orders
FOR SYSTEM_TIME AS OF '2025-11-01 00:00:00';

-- Compare current vs historical
SELECT
    'current' AS snapshot,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2025-11-01'
UNION ALL
SELECT
    'week_ago' AS snapshot,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP - INTERVAL '7 days'
WHERE order_date >= '2025-11-01';

-- Rollback to previous state (disaster recovery)
ALTER TABLE orders REVERT TO SNAPSHOT 'snapshot_20251101';

4. Open Format Interoperability

-- Query from Spark (same Iceberg tables)
spark.read.format("iceberg").load("heliosdb.orders")

-- Query from Trino
SELECT * FROM iceberg.heliosdb.orders WHERE ...

-- Query from Flink (streaming)
CREATE TABLE orders_stream WITH ('connector' = 'iceberg', ...)

-- Zero lock-in: data is portable to any Iceberg-compatible tool

5. Delta Lake Support

-- Create Delta table
CREATE TABLE events (
    event_id BIGINT,
    event_type VARCHAR(50),
    event_data JSONB,
    event_time TIMESTAMP
) USING DELTA
WITH (
    'delta.enableChangeDataFeed' = 'true'
);

-- Z-ordering for query optimization
OPTIMIZE events ZORDER BY (event_type, event_time);

-- Change data feed for downstream consumers
SELECT * FROM table_changes('events', 1, 100);

Concrete Examples

Example 1: E-Commerce Real-Time Analytics

After HeliosDB:

-- Single database for OLTP and OLAP
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date TIMESTAMP,
    status VARCHAR(20),
    total_amount DECIMAL(10,2),
    items JSONB
) USING ICEBERG;

-- Real-time dashboard query
CREATE MATERIALIZED VIEW realtime_metrics AS
SELECT
    DATE_TRUNC('minute', order_date) AS minute,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= CURRENT_DATE
GROUP BY 1;

-- Refresh every 10 seconds
ALTER MATERIALIZED VIEW realtime_metrics
SET (refresh_interval = '10 seconds');

Example 2: Financial Services Trade Analytics

Challenge: Real-time trade surveillance and compliance reporting

-- Trades table on Iceberg with OLTP performance
CREATE TABLE trades (
    trade_id BIGINT PRIMARY KEY,
    account_id BIGINT,
    symbol VARCHAR(10),
    side VARCHAR(4),
    quantity INT,
    price DECIMAL(12,4),
    trade_time TIMESTAMP,
    execution_venue VARCHAR(20)
) USING ICEBERG
WITH (
    partition = 'DAYS(trade_time)',
    oltp_optimized = true
);

-- Real-time trade surveillance (runs continuously)
CREATE CONTINUOUS QUERY suspicious_activity AS
SELECT
    account_id,
    symbol,
    COUNT(*) AS trade_count,
    SUM(quantity * price) AS total_value,
    MAX(trade_time) - MIN(trade_time) AS time_window
FROM trades
WHERE trade_time >= CURRENT_TIMESTAMP - INTERVAL '5 minutes'
GROUP BY account_id, symbol
HAVING COUNT(*) > 50 OR SUM(quantity * price) > 1000000;

-- End-of-day compliance report (same table, analytical query)
SELECT
    execution_venue,
    symbol,
    COUNT(*) AS trade_count,
    SUM(quantity) AS total_volume,
    AVG(price) AS avg_price,
    SUM(quantity * price) AS notional_value
FROM trades
WHERE DATE(trade_time) = CURRENT_DATE
GROUP BY 1, 2
ORDER BY notional_value DESC;
-- Executes on columnar Parquet in seconds

Example 3: IoT Data Platform

Company Profile: Manufacturing company with 10,000 sensors

Challenge: Store high-volume sensor data with real-time and historical analytics

-- Sensor data on Iceberg (optimized for time-series)
CREATE TABLE sensor_readings (
    sensor_id INT,
    reading_time TIMESTAMP,
    temperature FLOAT,
    pressure FLOAT,
    vibration FLOAT,
    status VARCHAR(20),
    PRIMARY KEY (sensor_id, reading_time)
) USING ICEBERG
WITH (
    partition = 'HOURS(reading_time)',
    sort_order = 'sensor_id, reading_time',
    target_file_size = '256MB'
);

COPY sensor_readings FROM STDIN WITH (FORMAT binary);
-- Streaming insert from Kafka
CREATE SUBSCRIPTION sensor_stream
CONNECTION 'kafka://...'
PUBLICATION sensor_events;

-- Real-time anomaly detection
CREATE CONTINUOUS QUERY anomalies AS
SELECT
    sensor_id,
    reading_time,
    temperature,
    AVG(temperature) OVER w AS avg_temp,
    STDDEV(temperature) OVER w AS stddev_temp
FROM sensor_readings
WINDOW w AS (
    PARTITION BY sensor_id
    ORDER BY reading_time
    RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
)
WHERE ABS(temperature - AVG(temperature) OVER w) > 3 * STDDEV(temperature) OVER w;

-- Historical analysis (1 year of data, 50B rows)
SELECT
    sensor_id,
    DATE_TRUNC('day', reading_time) AS day,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_readings
WHERE reading_time >= '2025-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;
-- Would take hours on traditional OLTP database

Storage economics:
- Raw data: 50TB

Example 4: Healthcare Data Lakehouse

Company Profile: Hospital system with EHR integration needs

Challenge: Combine operational EHR data with analytical research queries

-- Patient encounters table (OLTP + OLAP)
CREATE TABLE encounters (
    encounter_id BIGINT PRIMARY KEY,
    patient_id BIGINT,
    provider_id BIGINT,
    encounter_date TIMESTAMP,
    encounter_type VARCHAR(50),
    diagnosis_codes ARRAY<VARCHAR(10)>,
    procedure_codes ARRAY<VARCHAR(10)>,
    notes TEXT
) USING ICEBERG
WITH (
    partition = 'MONTHS(encounter_date)',
    oltp_optimized = true
);

-- Real-time EHR operations
INSERT INTO encounters VALUES (...);
UPDATE encounters SET notes = ... WHERE encounter_id = ...;

-- Research query: Disease cohort analysis
SELECT
    diagnosis_codes[1] AS primary_diagnosis,
    COUNT(DISTINCT patient_id) AS patient_count,
    AVG(DATEDIFF(discharge_date, admission_date)) AS avg_los
FROM encounters
WHERE encounter_date >= '2020-01-01'
  AND 'I21' = ANY(diagnosis_codes)  -- Heart attack
GROUP BY 1
ORDER BY patient_count DESC;
-- Executes on columnar data, HIPAA audit logged

-- Time travel for regulatory audit
SELECT * FROM encounters
FOR SYSTEM_TIME AS OF '2025-06-30 23:59:59'
WHERE patient_id = 12345;
-- Exact state at audit date

Example 5: Multi-Cloud Data Architecture

Company Profile: Enterprise with AWS, Azure, and on-premise data centers

Challenge: Unified analytics across cloud boundaries

-- Primary on AWS
CREATE TABLE aws_orders (...)
USING ICEBERG
LOCATION 's3://data-lake-aws/orders';

-- Secondary on Azure
CREATE TABLE azure_orders (...)
USING ICEBERG
LOCATION 'abfs://data-lake-azure/orders';

-- Cross-cloud federation (no data movement)
SELECT
    'AWS' AS source,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM aws_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
UNION ALL
SELECT
    'Azure' AS source,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM azure_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
-- Query pushdown to each cloud
-- Only aggregated results transferred

-- Unified catalog across clouds
SELECT
    table_catalog,
    table_schema,
    table_name,
    row_count,
    size_bytes
FROM heliosdb_information_schema.tables
WHERE table_format = 'ICEBERG';
-- See all Iceberg tables across all clouds

Implementation Roadmap

Phase 1: Assessment (Week 1-2)

Analysis:
├── Inventory current OLTP databases
├── Analyze Snowflake/warehouse workloads
├── Identify ETL pipelines
├── Calculate potential savings
└── Select pilot workload

Deliverables:
- Current state architecture diagram
- Target state architecture
- ROI analysis
- Migration plan

Phase 2: Pilot (Week 3-6)

Implementation:
├── Deploy HeliosDB cluster
├── Create Iceberg tables
├── Migrate pilot OLTP workload
├── Connect BI tools
└── Validate performance

Testing:
├── OLTP latency benchmarks
├── OLAP query performance
├── Data consistency validation
└── Failover testing

Phase 3: Migration (Month 2-3)

Execution:
├── CDC-based migration (zero downtime)
├── Query compatibility validation
├── BI dashboard migration
├── ETL pipeline retirement
└── Production cutover

Post-Migration:
├── Performance monitoring
├── Cost tracking
├── Team training
└── Documentation

Conclusion

HeliosDB's lakehouse OLTP platform eliminates the artificial boundary between operational and analytical databases.

• Eliminate ETL: Zero data pipelines, zero latency
• Enable Real-Time: Analytics on live operational data