When Speed Becomes Strategy: How MerchJar Eliminated the OLTP vs OLAP Trade-off

Spoiler alert: MerchJar transformed query times from "couple of minutes" to sub-second on their Amazon ads optimization platform, while handling a 50/50 split of inserts and updates. Here's how Firebolt's first-class MERGE support makes this possible.

The forced choice every data team faces

Every data-driven business eventually hits the same wall: you can optimize for fast analytics OR efficient updates, but historically never both. This forced trade-off has shaped decades of architectural decisions, creating brittle systems where companies maintain separate databases for operational workloads and analytical queries.

MerchJar, an Amazon ads optimization platform, refused to accept this compromise. Their business model depends on empowering brand owners and agencies to perform sophisticated analysis at data warehouse scale. But their workload is 50/50 new data versus updates. When your users need sub-second insights to optimize $30K monthly ad budgets while historical data continuously updates, you can't compromise on either speed or consistency.

The traditional separation of OLTP and OLAP creates hidden costs that extend far beyond infrastructure:

User engagement loss: When queries take minutes instead of seconds, users lose their analytical momentum. They stop iterating, abandon hypotheses, and miss insights. For MerchJar's brand owners and agencies, this directly translates to missed optimization opportunities and lost revenue.

Operational complexity: Maintaining dual systems means data pipelines, synchronization logic, and the constant risk of inconsistency between operational and analytical views. Engineering teams spend time on infrastructure instead of features.

Economic unpredictability: As workloads grow, traditional solutions force impossible choices: accept degraded performance, re-architect your entire system, or face unpredictable cost increases that threaten business model viability.

MerchJar's challenge: The "oil and water" problem

Tom Myers, MerchJar's founder, describes their workload as the "oil and water" challenge. Their users generate unpredictable, ad-hoc queries across billions of rows (classic analytical workload). But they also process continuous updates as late-attributed conversion data arrives and fraudulent clicks get removed (classic operational workload).

Previous attempts at solving this included:

PostgreSQL: Handled updates beautifully but hit vertical scaling limits with "couple minute query times" for complex temporal analysis.

ClickHouse: Delivered exceptional analytical speed but treated updates as afterthoughts, forcing trade-offs between data correctness, read performance, or write speed.

Snowflake: Provided scalability but stepwise pricing increases made growth planning impossible for a bootstrapped company.

Custom database: Six months of engineering effort proved that "building a database is really hard."

None of these solutions addressed the fundamental business requirement: maintaining user engagement through sub-second queries while continuously updating historical data.

What MerchJar built: Real-time optimization without compromise

MerchJar's platform now handles sophisticated multi-temporal analysis that was previously impossible:

Ad performance with continuous attribution updates

Amazon's attribution model means conversion data arrives over time. A click today might get attributed to a sale next week. MerchJar needs to continuously update historical records while users query that same data in real-time.‍‍

-- Sync attribution updates from staging to production
-- This runs continuously as new conversion data arrives
MERGE INTO ad_performance AS target
USING attribution_updates AS source
ON target.click_id = source.click_id
WHEN MATCHED AND source.conversion_value IS NOT NULL THEN
    UPDATE SET 
        conversion_value = source.conversion_value,
        attributed_at = source.attributed_at,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (click_id, campaign_id, ad_group_id, keyword_id, 
            click_time, conversion_value, attributed_at, last_updated)
    VALUES (source.click_id, source.campaign_id, source.ad_group_id, 
            source.keyword_id, source.click_time, source.conversion_value, 
            source.attributed_at, CURRENT_TIMESTAMP);

Fraud detection with immediate removal

Fraudulent clicks get identified and removed from historical data without affecting query performance:‍


-- Remove fraudulent clicks while preserving analytical speed
MERGE INTO ad_performance AS target
USING detected_fraud AS source
ON target.click_id = source.click_id
WHEN MATCHED THEN DELETE;

Multi-temporal analysis at sub-second speed

The queries that used to take minutes now return in milliseconds:

- Compare 30-day performance against lifetime metrics
-- Filter by recent order volume
-- This is the query pattern that broke PostgreSQL
WITH recent_performance AS (
    SELECT 
        product_id,
        SUM(spend) as spend_30d,
        SUM(sales) as sales_30d,
        SUM(orders) as orders_30d,
        SUM(sales) / NULLIF(SUM(spend), 0) as roas_30d
    FROM ad_performance
    WHERE date >= CURRENT_DATE - 30
    GROUP BY product_id
),
lifetime_performance AS (
    SELECT 
        product_id,
        SUM(spend) as spend_lifetime,
        SUM(sales) as sales_lifetime,
        SUM(orders) as orders_lifetime,
        SUM(sales) / NULLIF(SUM(spend), 0) as roas_lifetime
    FROM ad_performance
    GROUP BY product_id
),
recent_orders AS (
    SELECT product_id, SUM(orders) as orders_90d
    FROM ad_performance
    WHERE date >= CURRENT_DATE - 90
    GROUP BY product_id
    HAVING SUM(orders) >= 5  -- Filter for products with meaningful data
)
SELECT 
    rp.product_id,
    rp.roas_30d,
    lp.roas_lifetime,
    rp.roas_30d - lp.roas_lifetime as roas_trend,
    CASE 
        WHEN rp.roas_30d > lp.roas_lifetime * 1.2 THEN 'Improving'
        WHEN rp.roas_30d < lp.roas_lifetime * 0.8 THEN 'Declining'
        ELSE 'Stable'
    END as performance_trend
FROM recent_performance rp
JOIN lifetime_performance lp ON rp.product_id = lp.product_id
JOIN recent_orders ro ON rp.product_id = ro.product_id
ORDER BY roas_trend DESC
LIMIT 100;

This query pattern, comparing short-term trends against lifetime metrics while filtering by recent activity, is exactly what breaks traditional systems. It requires joining multiple temporal windows across potentially billions of rows. With PostgreSQL, it took minutes. With Firebolt, it's sub-second.

The technical breakthrough: First-class MERGE operations

Firebolt's architecture eliminates the OLTP vs OLAP trade-off through genuine innovation in how updates integrate with analytical workloads. The key is treating MERGE (upsert) operations as first-class citizens, not after thoughts thanks to the strong consistency delivered by the Metadatalayer.

What this means in practice:

• MERGE operations execute at full analytical speed without compromising read performance
• Reads immediately after updates return correct, consistent results with ACID compliance
• No forced trade-offs between write speed, read speed, or data correctness
• Efficient data pruning maintains sub-second performance even for complex multi-temporal queries

This isn't incremental improvement. It's architectural innovation that changes what's possible.

Technical deep dive: For the complete engineering explanation of how Firebolt's MERGE implementation achieves this performance, including query plan details, loopback shuffles, and distributed execution, read Implementing Firebolt MERGE Statement.

Building your own MERGE-powered demo application

To help you explore these patterns hands-on, we've created a complete demo application that mirrors MerchJar's architecture: an ad performance tracking system with continuous attribution updates and real-time analytics.

The core pattern: Atomic upsert with immediate consistency

The heart of the demo is the MERGE pattern that handles MerchJar's 50/50 workload:

-- Single atomic operation handles both updates and inserts
-- No consistency windows, no stale reads
MERGE INTO ad_performance AS target
USING attribution_updates AS source
ON target.click_id = source.click_id
WHEN MATCHED THEN
    UPDATE SET 
        conversion_value = COALESCE(source.conversion_value, target.conversion_value),
        attributed_at = COALESCE(source.attributed_at, target.attributed_at),
        orders = source.orders,
        sales = source.sales,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (click_id, campaign_id, ad_group_id, keyword_id, product_id,
            click_time, conversion_value, attributed_at, spend, impressions,
            clicks, orders, sales, date, last_updated)
    VALUES (source.click_id, source.campaign_id, source.ad_group_id, 
            source.keyword_id, source.product_id, source.click_time,
            source.conversion_value, source.attributed_at, source.spend,
            source.impressions, source.clicks, source.orders, source.sales,
            source.date, CURRENT_TIMESTAMP);

Why this matters: The traditional approach breaks

The demo includes a comparison showing what MerchJar tried with PostgreSQL.

-- Traditional approach: Multiple queries, multiple transactions
-- Step 1: Check for existing records (separate query)
SELECT click_id FROM ad_performance 
WHERE click_id IN (SELECT click_id FROM attribution_updates);


-- Step 2: Update existing (separate transaction)
UPDATE ad_performance SET conversion_value = au.conversion_value
FROM attribution_updates au WHERE ad_performance.click_id = au.click_id;


-- Step 3: Insert new (separate transaction)
INSERT INTO ad_performance
SELECT * FROM attribution_updates au
WHERE NOT EXISTS (SELECT 1 FROM ad_performance ap WHERE ap.click_id = au.click_id);


-- Problems:
-- • Between steps 2 and 3, queries see inconsistent data
-- • Each step is a full table scan
-- • No way to optimize the combined operation


-- Firebolt MERGE: Single atomic operation, single optimized query plan

Try it yourself

The complete demo is available at github.com/johnkennedy-cmyk/firebolt-merge-demo. The repository includes data generation scripts simulating realistic ad performance data, various MERGE patterns, the multi-temporal analytical queries that broke PostgreSQL, a Express API demonstrating real-time analytics, and benchmark comparisons against traditional approaches.

Clone the repo and follow the README to have a working demo in under 10 minutes.

Real-world impact: What changed for MerchJar

MerchJar's deployment demonstrates how technical innovation translates to business outcomes:

User experience transformation: Complex multi-temporal analysis (comparing 30-day performance against lifetime metrics while filtering by recent order volume) drops from minutes to sub-second response times. Users maintain analytical flow and iterate through hypotheses rapidly.

Operational confidence: Historical performance data updates continuously without creating consistency issues or degrading query performance. The system handles the "oil and water" workload seamlessly.

Economic predictability: Linear, usage-based pricing enables confident planning for 2x to 5x query growth without budget surprises. Technical scalability aligns with business scalability.

Business scalability: MerchJar now operates 24/7 handling burst workloads with plans for 24x data growth (hourly granularity) and 3x platform expansion. Growth that was architecturally impossible with previous solutions.

As Tom Myers puts it: "Speed isn't just a nice-to-have. It's a business differentiator. When you enable your users to maintain analytical momentum with sub-second queries, you're not just improving performance metrics, you're fundamentally changing how they interact with data."

Best practices for production MERGE deployments

Based on MerchJar's experience and our engineering work, here are key considerations for production deployments:

1. Understand MERGE query planning

MERGE operations create sophisticated query plans that combine JOIN, INSERT, UPDATE, and DELETE into a single atomic operation. Use EXPLAIN to understand how your specific MERGE will execute:

EXPLAIN 
MERGE INTO target t USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET value = s.value
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.value);

Key elements to look for:

• Join type (INNER, LEFT OUTER, FULL OUTER depending on clauses)
• Loopback shuffle for efficient clause routing
• Key-identity shuffle for distributed DELETE consistency

2. Design tables for MERGE efficiency

Primary index selection significantly impacts MERGE performance:

-- Good: Primary index on join key
CREATE TABLE ad_performance (
    click_id BIGINT,
    ...
) PRIMARY INDEX click_id;


-- MERGE ON target.click_id = source.click_id
-- Will use efficient index-based join

3. Handle the 50/50 workload pattern

When your workload is roughly half updates and half new records, structure your staging data to make MERGE efficient:

-- Staging table with same structure as target
CREATE TABLE attribution_updates (
    click_id BIGINT,
    ...
) PRIMARY INDEX click_id;


-- Load all changes (updates and new records) into staging
-- MERGE handles the routing automatically

4. Monitor MERGE performance

Track key metrics over time:‍

-- Check tablet health after heavy MERGE activity
SELECT 
    table_name,
    COUNT(*) as tablet_count,
    SUM(uncompressed_bytes) as total_bytes,
    AVG(row_count) as avg_rows_per_tablet
FROM information_schema.tablets
WHERE table_name = 'ad_performance'
GROUP BY table_name;


-- Consider VACUUM after many small MERGEs
VACUUM ad_performance;

‍‍
5. Scale with engine sizing

For heavy MERGE workloads, ensure adequate engine resources:

-- Monitor memory usage during MERGE
SELECT 
    pool,
    pool_size_limit_bytes,
    pool_used_bytes
FROM information_schema.engine_caches
WHERE cluster_ordinal = 1;

The architecture advantage: Why native MERGE matters

The most significant benefit of Firebolt's MERGE implementation isn't just speed. It's the elimination of a fundamental architectural constraint.

Traditional approaches force you to choose:

• Row stores (PostgreSQL): Great for updates, struggles with analytical scale
• Column stores (ClickHouse): Great for analytics, treats updates as afterthoughts
• Separate systems: Operational database + analytical warehouse with sync pipelines

Firebolt's approach treats MERGE as a first-class operation, not a compatibility feature. The query planner optimizes the entire operation holistically, using techniques like loopback shuffles to efficiently route rows to their appropriate actions without redundant computation.

This matters because modern workloads don't fit neatly into OLTP or OLAP categories. E-commerce platforms analyze inventory while processing updates. Financial services need immediate consistency alongside complex analytics. AdTech platforms (like MerchJar) optimize campaigns based on continuously updating metrics.

When your business model depends on empowering users to make decisions based on current, consistent data at data warehouse scale, every second of query latency and every moment of data inconsistency directly impacts business outcomes.

Learn more and get started

Ready to eliminate the OLTP vs OLAP trade-off in your data architecture?

Watch the fireside chat: See Tom Myers walk through MerchJar's journey from "couple minute query times" to sub-second analytics with continuous updates here.

Technical deep dive: For the complete engineering explanation of how Firebolt's MERGE achieves this performance, read Implementing Firebolt MERGE Statement.

Try the demo: Clone the complete MERGE example from our GitHub repository: github.com/johnkennedy-cmyk/firebolt-merge-demo

Read the docs: Learn about MERGE syntax, optimization techniques, and best practices in the Firebolt documentation.

Start free trial: Get $200 in credits to test MERGE on your own workloads at firebolt.io/signup.

The question isn't whether your business needs both fast analytics and efficient updates. The question is whether your current architecture lets you have both without compromise.

Questions? Join our community Slack or contact our team to discuss your mixed workload challenges.