Your Team Analytics Engine: DuckDB in the Cloud

I've been running DuckDB locally for two years. It replaced most of my ad-hoc BigQuery sessions, eliminated a dozen Pandas scripts, and gave me sub-second analytical queries on data that would've required a warehouse five years ago. I've written about why it's faster than Pandas and why it's the right engine for shared dashboards.

But there's a problem I keep hitting: my DuckDB queries reference local files that nobody else has. My joins depend on CSV exports I pulled from Stripe yesterday. My identity matching logic lives in a notebook that runs on my machine and dies when I close the lid.

DuckDB is an incredible engine. What it's missing — by design — is everything around the engine: ingestion from live sources, scheduled sync, identity resolution across systems, and team access. That's what we built Fastero's cross-source DuckDB store to be.

Why DuckDB and not Postgres or SQLite

This is a fair question. Postgres is battle-tested. SQLite is embedded and simple. Why pick a third option?

Postgres is a row-store. Its storage engine writes data row by row — great for OLTP (insert a user, update an order), terrible for analytical queries that scan millions of rows across a handful of columns. A GROUP BY channel, month over 10 million payment records in Postgres will sequentially read every column in every row, even though you only need three. DuckDB's columnar engine reads only the columns referenced in your query. On the same aggregation, that's typically a 10-50x speed difference.

SQLite is single-threaded and row-oriented. It shares DuckDB's embedded deployment model — no server, runs in-process — but it's architecturally an OLTP database. No vectorized execution, no parallel scans, no columnar storage. For analytical workloads, SQLite performs roughly on par with Pandas: fine under a million rows, painful above that.

DuckDB was purpose-built for analytics. The team at CWI Amsterdam designed it as a columnar OLAP engine that runs in-process. Three properties make it the right foundation for a team analytics store:

1. Vectorized execution. DuckDB processes data in batches of vectors (typically 2048 values), not one row at a time. This is the same execution model that powers BigQuery and ClickHouse — pipeline-friendly, cache-friendly, and automatically parallelized across available cores.

2. Native Parquet support. Parquet is the lingua franca of analytical data. DuckDB reads and writes it natively, with predicate pushdown and column pruning. When your data store is backed by Parquet on S3, you get durability and portability without the overhead of a proprietary storage format.

3. Out-of-core processing. Dataset bigger than memory? DuckDB spills to disk automatically. This means a managed instance with 8GB of RAM can process 50GB+ datasets without the query engine falling over.

None of these properties exist in Postgres or SQLite. They're not bugs — those databases solve different problems. But for "run analytical SQL across data from six different SaaS tools," DuckDB is the correct engine.

What the cross-source data store actually does

Fastero's DuckDB store is not DuckDB-as-a-service. It's a managed analytical layer that uses DuckDB as its engine and wraps it with the infrastructure a team actually needs.

The architecture has four components:

Ingestion layer. You connect your sources — Postgres, MySQL, Stripe, HubSpot, BigQuery, Shopify, Google Sheets, S3 Parquet, and 21+ others. The ingestion layer pulls the tables or objects you select and writes them as DuckDB tables in your managed instance. This isn't a CDC stream or a complex pipeline. It's a direct pull: read from source, write to DuckDB, track what changed.

Scheduled sync. Each source connection has a sync cadence — hourly, daily, or event-triggered. The sync is incremental where the source supports it (e.g., Postgres tables with an updated_at column, Stripe events with a cursor) and full-refresh where it doesn't (e.g., Google Sheets, small dimension tables). You don't configure this manually; the platform picks the right strategy based on the source type and table size.

Identity resolution. This is the piece most people underestimate. Your customer is cus_L4xB7qK9 in Stripe, contact-12847 in HubSpot, and user_id = 8823 in your Postgres database. Without identity resolution, your cross-source joins are manual email-matching hacks. The DuckDB store maintains a resolution table that maps identifiers across systems — email, external IDs, custom mapping rules — so your queries reference a canonical entity, not a source-specific key.

Query engine. Standard DuckDB SQL. No proprietary dialect, no abstraction layer. You write the same SQL you'd write locally, and it runs against tables populated from your live sources.

Concrete SQL: Stripe + HubSpot + Postgres in one query

Here's a query I run weekly. It joins Stripe payment data with HubSpot deal records and product usage from our Postgres application database — all in a single DuckDB query against the managed store:

SELECT
    r.canonical_email,
    r.canonical_name,
    SUM(sp.amount) / 100.0 AS lifetime_revenue,
    MAX(sp.created) AS last_payment,
    hd.deal_stage,
    hd.deal_owner,
    pu.monthly_active_days,
    pu.features_used,
    CASE
        WHEN pu.monthly_active_days < 5 AND SUM(sp.amount) > 50000
            THEN 'high-value churn risk'
        WHEN pu.monthly_active_days > 20 AND hd.deal_stage = 'customer'
            AND SUM(sp.amount) < 10000
            THEN 'expansion candidate'
    END AS signal
FROM stripe_charges sp
JOIN resolved_identities r ON sp.customer_id = r.stripe_id
JOIN hubspot_deals hd ON r.hubspot_contact_id = hd.contact_id
LEFT JOIN pg_product_usage pu ON r.app_user_id = pu.user_id
WHERE sp.status = 'succeeded'
  AND sp.created >= '2026-01-01'
GROUP BY r.canonical_email, r.canonical_name, 
         hd.deal_stage, hd.deal_owner,
         pu.monthly_active_days, pu.features_used
HAVING SUM(sp.amount) > 10000
ORDER BY lifetime_revenue DESC

Three sources. One query. No ETL pipeline. The resolved_identities table is maintained by the platform's identity resolution — I didn't build a mapping table by hand.

Running this same analysis in a traditional stack would mean: Fivetran syncing Stripe and HubSpot into Snowflake, a dbt model joining the three sources with a manually maintained ID mapping, and a BI tool on top. That's a month of setup and $2,000+/month in tooling. In the DuckDB store, it's a SQL query that runs in under two seconds.

How identity resolution works

Cross-source identity resolution sounds complex, but the mechanics are straightforward.

When data is ingested, the platform extracts known identifiers from each record — email addresses, external IDs, customer references. It builds a resolution graph: if Stripe customer cus_L4xB7qK9 has email alice@acme.com, and HubSpot contact 12847 has the same email, they're the same entity. If your Postgres users table has a stripe_customer_id column, that's another edge in the graph.

The resolution table exposes this as a queryable DuckDB table. You join through it, or you query it directly to audit matches:

-- Find entities that exist in Stripe and Postgres but NOT in HubSpot
SELECT canonical_email, stripe_id, app_user_id
FROM resolved_identities
WHERE hubspot_contact_id IS NULL
  AND stripe_id IS NOT NULL
  AND app_user_id IS NOT NULL

This query surfaces customers who are paying and using your product but somehow fell out of your CRM — a revenue leak that's invisible if you're looking at each system in isolation.

What this replaces (and what it doesn't)

The DuckDB store replaces the analytical warehouse for teams under 50 who don't need petabyte scale. Specifically:

It replaces Snowflake/BigQuery as the query engine for teams whose total data volume is under 500GB. DuckDB's vectorized columnar engine runs the same analytical SQL patterns — window functions, CTEs, complex aggregations — at comparable speed on this scale. You lose Snowflake's elastic compute scaling and BigQuery's serverless auto-scaling, but you don't need either when your largest table has 50 million rows, not 50 billion.

It replaces Fivetran/Airbyte as the ingestion layer for the common case. The platform's connectors pull data from your sources directly. You don't need a separate sync tool, a staging area, or a transformation pipeline for data to arrive in a queryable state.

It replaces dbt's join/transform layer for operational analytics. If your transformations are "join these three sources, filter to active customers, aggregate by month" — that's a SQL query, not a 200-line dbt model with tests and documentation. For teams with complex multi-hop transformations, dbt still has a place. For most cross-source questions, a well-written query is enough.

It does NOT replace a production OLTP database. Your application still reads and writes to Postgres or MySQL. The DuckDB store is a read-only analytical layer that ingests from those sources.

It does NOT replace a data lake at scale. If you're processing 10TB+ of event data daily, you need Spark or Flink or a proper lakehouse. DuckDB is not that. It's the right engine for the 90% of teams whose analytical data fits on a single beefy machine.

Sync, not snapshot

The scheduled sync is worth explaining because it's the difference between a stale export and a live analytical layer.

Each connector tracks its sync state. For Stripe, that's an event cursor — the sync picks up where it left off, pulling only new charges, refunds, and subscription changes since the last run. For Postgres, it's a high-water mark on updated_at or the WAL position. For Google Sheets, it's a full refresh (the sheet is small enough that incremental doesn't matter).

The result is that your DuckDB tables stay current within your configured cadence. A daily sync means yesterday's data is always there. An hourly sync means your dashboard shows numbers from the last 60 minutes. This isn't eventually-consistent replication — it's a pull-based sync with clear freshness guarantees.

You can also trigger a sync manually or via API when you need the absolute latest before running a report. The sync completes in seconds for incremental sources, minutes for full refreshes of larger tables.

The analytical layer your team is missing

Most teams I talk to have the same gap. They have Postgres for their app, Stripe for billing, HubSpot for sales, and Google Sheets for everything that doesn't fit. The data exists. The questions are known. What's missing is a place to bring it together and query across it without a six-month warehouse project.

The DuckDB store fills that gap. It's a managed analytical engine that ingests from your live sources, resolves identities across systems, stays fresh on a schedule, and lets your team query everything with standard SQL. The engine underneath is DuckDB — columnar, vectorized, fast on analytical workloads — and the infrastructure around it handles the parts DuckDB was never designed to handle.

If you've been running DuckDB locally and wishing your team could access the same queries, this is the next step. If you've been putting off the warehouse project because it's too expensive and too slow to set up, this might be the reason you never need to start it.

Try Fastero free — connect your databases and SaaS tools, query across all of them with DuckDB, no warehouse required. No credit card required.