DuckDB vs DynamoDB: Analytical vs Key-Value

DuckDB and DynamoDB are not competing products. They solve fundamentally different problems. Comparing them is like comparing a spreadsheet to a filing cabinet — both store data, but they're optimized for completely different operations.

Understanding the difference helps you use both effectively.

The Fundamental Design Difference#

DynamoDB is a key-value and document store optimized for:

• Single-item lookups by primary key (O(1) latency)
• High write throughput at any scale
• Predictable latency (single-digit milliseconds at any scale)
• Applications that need to look up individual records fast

DuckDB is a columnar analytical database optimized for:

• Scanning millions of rows to compute aggregates
• Complex multi-table joins and window functions
• Analytical queries that read many rows but return few
• Business intelligence and reporting

Query Pattern Comparison#

What DynamoDB Is Good At#

// DynamoDB: fetch a single user's record (fast, O(1))
const user = await docClient.get({
    TableName: 'Users',
    Key: { userId: 'user-123' }
});
 
// DynamoDB: get all orders for a user (fast with GSI)
const orders = await docClient.query({
    TableName: 'Orders',
    KeyConditionExpression: 'userId = :uid',
    ExpressionAttributeValues: { ':uid': 'user-123' }
});

What DuckDB Is Good At#

-- DuckDB: aggregate analysis across all users
SELECT 
    segment,
    AVG(lifetime_value) AS avg_ltv,
    COUNT(*) AS user_count,
    SUM(lifetime_value) AS total_revenue
FROM users
GROUP BY segment
ORDER BY avg_ltv DESC;
 
-- DuckDB: cohort retention
SELECT 
    cohort_month,
    months_retained,
    COUNT(DISTINCT user_id) AS users
FROM user_cohorts
GROUP BY cohort_month, months_retained;

What Happens When You Try the Wrong Tool#

// ❌ DynamoDB: This is expensive and slow (full table scan)
const allUsers = await docClient.scan({ TableName: 'Users' });
const avgLTV = allUsers.Items.reduce((sum, u) => sum + u.ltv, 0) / allUsers.Items.length;
// For 1M users: 1000+ API calls, minutes of runtime, $$$

-- ❌ DuckDB: This isn't what DuckDB does
-- You can't look up individual records by ID at low latency
-- DuckDB reads column ranges, not point lookups
SELECT * FROM users WHERE user_id = 'user-123'; -- Works but slower than DynamoDB

Performance at Scale#

Cost Comparison#

DynamoDB:

• Write: $1.25 per million writes
• Read: $0.25 per million reads
• Storage: $0.25/GB/month
• Analytics (full scan): Can be expensive — 1M rows = 1M read units = $0.25 per scan

DuckDB:

• $0 for the database
• Analytics cost: 0 additional cost per query

For high-frequency transactional reads/writes, DynamoDB's cost is justified by its performance guarantees. For analytics, DuckDB saves significant money.

The Right Architecture: Use Both#

The most common pattern in modern applications:

User actions → Application → DynamoDB (fast writes, fast reads by key)
                                  ↓ (DynamoDB Streams or nightly export)
                              DuckDB (analytics, reporting, ML)

DynamoDB handles the live application. DuckDB handles the analytics.

Setting Up the Pipeline#

Option 1: DynamoDB Streams → Lambda → DuckDB

# Lambda triggered by DynamoDB Streams
def handler(event, context):
    import duckdb
    
    con = duckdb.connect('/tmp/analytics.duckdb')
    
    for record in event['Records']:
        if record['eventName'] in ('INSERT', 'MODIFY'):
            new_image = record['dynamodb']['NewImage']
            # Transform DynamoDB format to flat SQL
            con.execute("""
                INSERT OR REPLACE INTO users VALUES (?, ?, ?, ?)
            """, [
                new_image['userId']['S'],
                new_image['email']['S'],
                float(new_image['ltv']['N']),
                new_image['segment']['S']
            ])
    
    con.close()

Option 2: Nightly Export → DuckDB

import boto3
import duckdb
 
# Export DynamoDB table to S3
dynamodb = boto3.client('dynamodb')
dynamodb.export_table_to_point_in_time(
    TableArn='arn:aws:dynamodb:...',
    S3Bucket='my-analytics-bucket',
    S3Prefix='dynamo-exports/',
    ExportFormat='DYNAMODB_JSON'
)
 
# Query the export in DuckDB
con = duckdb.connect('analytics.duckdb')
con.execute("""
    CREATE OR REPLACE TABLE users AS
    SELECT 
        Item.userId.S AS user_id,
        Item.email.S AS email,
        CAST(Item.ltv.N AS DOUBLE) AS ltv,
        Item.segment.S AS segment
    FROM read_json_auto('s3://my-analytics-bucket/dynamo-exports/*.json.gz')
""")

Option 3: AWS Glue to Parquet → DuckDB

# After Glue converts DynamoDB export to Parquet
con.execute("""
    CREATE OR REPLACE VIEW users AS
    SELECT * FROM read_parquet('s3://my-bucket/users-export/*.parquet')
""")
 
# Now run analytics
result = con.execute("""
    SELECT segment, AVG(ltv) FROM users GROUP BY segment
""").fetchdf()

DenchClaw's Pattern#

DenchClaw uses DuckDB exclusively — no DynamoDB — because it's a local-first, single-user application. There's no need for DynamoDB's distributed write capacity when one person is updating their own CRM.

But for the DenchClaw Cloud product, which serves many users simultaneously, the architecture would look more like:

• DynamoDB for user sessions, real-time workspace state, and high-frequency API calls
• DuckDB per-user for their personal analytics and CRM data
• Analytics pipeline that aggregates across user DuckDB files for platform-level reporting

This is the natural architecture: DynamoDB for the live application layer, DuckDB for the per-user analytical layer. They're complementary, not competitive.

Frequently Asked Questions#

Can DuckDB replace DynamoDB in my production application?#

No. DuckDB doesn't support the use cases DynamoDB is designed for: sub-millisecond point lookups, millions of concurrent writes, infinite horizontal scaling.

Can I run both DuckDB and DynamoDB in the same application?#

Yes, and it's a common pattern. DynamoDB for the operational layer, DuckDB for the analytical layer.

What's the best way to sync DynamoDB data to DuckDB?#

DynamoDB Streams → Lambda → DuckDB for real-time sync. DynamoDB Point-in-Time Recovery exports → Parquet → DuckDB for nightly batch.

Is DuckDB ACID compliant like DynamoDB?#

DuckDB is fully ACID compliant for single-writer transactions. DynamoDB offers per-item atomicity and optional transactions (with additional cost).

Which is better for a startup's first database?#

For applications: DynamoDB (or PostgreSQL). For analytics: DuckDB. Most startups need both eventually — start with PostgreSQL or DynamoDB for the app layer and add DuckDB when you need analytics.

Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →