The SQL You Already Know Can Help You Grow Your SaaS: A Developer's Guide to DuckDB 2 and DuckLake

You know how to build software. You can authenticate users, design database tables, process payments, and ship features.

But ask which acquisition channels bring in customers who stick around, or which onboarding steps help users become paying customers, and the answer can be surprisingly difficult to find.

The information might already exist. Some lives in your application database. Some sits in a billing export. More is buried in product events or JSON logs. Each source tells part of the story.

Putting those pieces together can feel like another project: another language, another platform, another collection of tools to learn while you already have a product to maintain.

If you know SQL, though, you already have the foundation.

DuckDB lets you apply familiar queries to analytical problems and data formats you probably encounter every day. DuckLake gives you a way to organize and retain analytical data when you want to take that work further.

There are a few new concepts to learn. But you can get useful results with SELECT, JOIN, and GROUP BY before you need to understand the infrastructure underneath.

Let's start with a small SaaS dataset and answer three questions:

• Which channels bring in users who reach the product's first meaningful milestone?
• Which channels turn signups into paying customers?
• Is an early product behavior associated with users coming back?

You can complete the first walkthrough on Layerbase's Free plan. The optional managed DuckLake section requires Pro or Dedicated. You do not need a lake, an external database connection, or your own storage bucket to try the analysis.

Why DuckDB is useful when you already have a database

Your application database is already good at answering questions such as "Which projects belong to this account?" or "What is this customer's subscription status?"

Analytical questions look across many accounts, events, and time periods:

Of the accounts that signed up last month, which acquisition channels had the highest activation rate?

You can answer plenty of these questions in PostgreSQL or another relational database. Start there when the data and workload fit.

DuckDB becomes especially useful when your analysis spans files and databases, when you want to explore data without building an import pipeline first, or when you need a database designed around analytical workloads.

It can read CSV, JSON, and Parquet, and combine their contents using SQL. That makes an export something you can query and join, rather than something you have to inspect manually in a spreadsheet.

DuckDB 2 builds on this with asynchronous I/O for file reads, more integrated support for semi-structured data through its VARIANT type, and improvements to Quack, its native remote protocol. Reading CSVs and JSON predates version 2; the new release extends an already useful analytical toolkit. Read the DuckDB 2 announcement.

On Layerbase, you can start in a browser. Create a hosted DuckDB database, open its query console, and work with SQL.

A few terms before we begin

DuckDB runs the queries. DuckLake provides another way to organize the data those queries read and write.

You can use DuckDB without a lake. DuckLake also supports arrangements beyond the managed setup described here, including local catalogs and local files. DuckLake's introduction explains those options.

Start with a small SaaS dataset

Imagine you run a product that helps customers create and publish projects.

You track acquisition channels, project activity, and the date an account first becomes a paying customer. You suspect that publishing a first project is an important milestone, and that templates might help new accounts find value sooner.

For this walkthrough, use these two fictional datasets:

• Download accounts.csv
• Download events.csv

The accounts file contains eight accounts:

account_id,signed_up_at,channel,paid_at
1,2026-10-01,paid_search,2026-10-05
2,2026-10-01,paid_search,
3,2026-10-01,paid_search,
4,2026-10-01,paid_search,
5,2026-10-01,organic,2026-10-06
6,2026-10-01,organic,
7,2026-10-01,referral,2026-10-04
8,2026-10-01,referral,

The events file records actions such as creating a project, using a template, publishing, and opening a project again.

This dataset is deliberately tiny. Its patterns illustrate the queries; eight fictional accounts cannot establish a reliable business trend.

Create a DuckDB database on Layerbase, select version 2, and name it saas-analytics.

Open the query console and import the files as tables named accounts and events. You can drag each file onto the editor; the console creates its table and previews the imported rows. The console supports CSV and JSON uploads. Uploading matters here: the hosted database cannot read a file directly from a path on your laptop. See the import guide.

Preview the data:

SELECT *
FROM layerbase.main.accounts
ORDER BY account_id;

The layerbase.main prefix explicitly selects the local database and schema. We'll use it throughout so the destination stays clear when we attach a lake later.

Make the date types explicit after importing. Run these statements before creating the view below:

ALTER TABLE layerbase.main.accounts
ALTER COLUMN signed_up_at TYPE DATE
USING CAST(signed_up_at AS DATE);

ALTER TABLE layerbase.main.accounts
ALTER COLUMN paid_at TYPE DATE
USING TRY_CAST(paid_at AS DATE);

ALTER TABLE layerbase.main.events
ALTER COLUMN event_at TYPE DATE
USING CAST(event_at AS DATE);

TRY_CAST preserves missing payment dates as NULL and also converts empty strings to NULL if your import retained them.

If you prefer to skip downloading and importing, run this self-contained setup instead of the import steps above, in a fresh tutorial database:

CREATE TABLE layerbase.main.accounts (
    account_id INTEGER,
    signed_up_at DATE,
    channel VARCHAR,
    paid_at DATE
);

INSERT INTO layerbase.main.accounts VALUES
    (1, '2026-10-01', 'paid_search', '2026-10-05'),
    (2, '2026-10-01', 'paid_search', NULL),
    (3, '2026-10-01', 'paid_search', NULL),
    (4, '2026-10-01', 'paid_search', NULL),
    (5, '2026-10-01', 'organic', '2026-10-06'),
    (6, '2026-10-01', 'organic', NULL),
    (7, '2026-10-01', 'referral', '2026-10-04'),
    (8, '2026-10-01', 'referral', NULL);

CREATE TABLE layerbase.main.events (
    account_id INTEGER,
    event_name VARCHAR,
    event_at DATE
);

INSERT INTO layerbase.main.events VALUES
    (1, 'project_created', '2026-10-02'),
    (2, 'project_created', '2026-10-02'),
    (5, 'project_created', '2026-10-02'),
    (6, 'project_created', '2026-10-02'),
    (7, 'project_created', '2026-10-02'),
    (8, 'project_created', '2026-10-02'),
    (5, 'template_used', '2026-10-02'),
    (7, 'template_used', '2026-10-02'),
    (1, 'project_published', '2026-10-03'),
    (5, 'project_published', '2026-10-03'),
    (6, 'project_published', '2026-10-03'),
    (7, 'project_published', '2026-10-03'),
    (1, 'project_opened', '2026-10-10'),
    (5, 'project_opened', '2026-10-10'),
    (7, 'project_opened', '2026-10-10');

Define what you want to measure

Before writing a report, decide what its numbers mean.

For our fictional product:

• Activated: published a project within the first seven days.
• Used a template: used a template within the first seven days.
• Returned in week two: opened a project between seven and fourteen days after signup.
• Converted to paid: became a paying customer within fourteen days.

We'll analyze data available before October 22, 2026.

That fixed cutoff makes the tutorial reproducible. It also lets us exclude accounts that have not had a full fourteen days to complete the observation period. Otherwise, a new signup could look unsuccessful simply because it hasn't had enough time.

Create a view that summarizes these outcomes once per account:

CREATE VIEW layerbase.main.account_metrics AS
SELECT
    a.account_id,
    a.channel,

    count(*) FILTER (
        WHERE e.event_name = 'project_published'
          AND e.event_at < a.signed_up_at + INTERVAL '7 days'
    ) > 0 AS activated,

    count(*) FILTER (
        WHERE e.event_name = 'template_used'
          AND e.event_at < a.signed_up_at + INTERVAL '7 days'
    ) > 0 AS used_template,

    count(*) FILTER (
        WHERE e.event_name = 'project_opened'
          AND e.event_at >= a.signed_up_at + INTERVAL '7 days'
          AND e.event_at < a.signed_up_at + INTERVAL '14 days'
    ) > 0 AS returned_week_two,

    a.paid_at >= a.signed_up_at
      AND a.paid_at < a.signed_up_at + INTERVAL '14 days'
      AS paid_within_14_days

FROM layerbase.main.accounts AS a
LEFT JOIN layerbase.main.events AS e
    ON e.account_id = a.account_id
   AND e.event_at >= a.signed_up_at
   AND e.event_at < DATE '2026-10-22'

WHERE a.signed_up_at + INTERVAL '14 days' <= DATE '2026-10-22'

GROUP BY
    a.account_id,
    a.channel,
    a.signed_up_at,
    a.paid_at;

If FILTER is unfamiliar, it limits which rows contribute to an aggregate. Here, it lets us count different event types in the same query.

The LEFT JOIN keeps accounts that have no recorded activity. Those accounts belong in the denominator too.

The view stores the query definition, so querying it recomputes the results from the underlying tables.

Which channels bring in users who activate?

A signup is encouraging. Reaching a meaningful product outcome tells you more.

SELECT
    channel,
    count(*) AS signups,
    count(*) FILTER (WHERE activated) AS activated_accounts,
    round(
        100.0 * count(*) FILTER (WHERE activated) / count(*),
        1
    ) AS activation_pct
FROM layerbase.main.account_metrics
GROUP BY channel
ORDER BY signups DESC, channel;

Result:

Paid search brought in the most accounts. Organic search brought in more accounts that published a project.

With a larger dataset, that difference could justify investigating the expectations set by your advertisements and landing pages. Are people signing up for a use case the product doesn't serve well? Does one channel attract customers who need more onboarding help?

The query gives you somewhere specific to look. It does not tell you to shut down a marketing channel.

Which channels turn signups into paying customers?

Now connect product acquisition to a business outcome:

SELECT
    channel,
    count(*) AS signups,
    count(*) FILTER (
        WHERE paid_within_14_days
    ) AS paid_accounts,
    round(
        100.0 * count(*) FILTER (
            WHERE paid_within_14_days
        ) / count(*),
        1
    ) AS paid_conversion_pct
FROM layerbase.main.account_metrics
GROUP BY channel
ORDER BY signups DESC, channel;

Result:

Each channel produced one paying account in this sample, despite different signup totals.

This is why signup volume alone is an incomplete measure of acquisition quality.

To understand profitability, you would need more information: acquisition costs, subscription amounts, refunds, and how long customers remain subscribed. A fourteen-day conversion rate is useful, but it is not customer lifetime value or return on advertising spend.

Those additional questions can become additional joins as you bring in more data.

Does an early product behavior line up with users returning?

Suppose templates are intended to help new users get started. Are accounts that use them more likely to return?

SELECT
    used_template,
    count(*) AS accounts,
    count(*) FILTER (
        WHERE returned_week_two
    ) AS returning_accounts,
    round(
        100.0 * count(*) FILTER (
            WHERE returned_week_two
        ) / count(*),
        1
    ) AS week_two_return_pct
FROM layerbase.main.account_metrics
GROUP BY used_template
ORDER BY used_template DESC;

Result:

In this fictional sample, template users returned more often.

That suggests a hypothesis: perhaps templates help users experience value sooner.

It does not establish causation. More motivated customers might be more likely both to discover templates and to return. Acquisition channel, account size, or use case could explain some of the difference.

With real data, you could compare similar accounts, examine larger signup cohorts, and test introducing templates earlier in onboarding.

SQL helps you identify a promising question. Product research and experiments help you determine what to change.

Turn a one-off result into a trend

A single aggregate tells you what happened across one population. Trends require consistent comparisons over time.

The next version of this analysis could group accounts by signup week and compare:

• Seven-day activation rates.
• Fourteen-day paid conversion.
• Week-two return rates.
• The same measures within each acquisition channel.

Keep the definitions and observation windows consistent. Don't compare a mature cohort's fourteen-day conversion with a recent cohort that has only existed for three days.

Also preserve the account-level approach. Joining raw billing rows directly to raw event rows can multiply records and inflate totals. Summarize each source to the unit you want to measure before combining metrics.

These habits matter more than choosing an impressive-looking chart.

Bring your own files and databases

The sample uses CSV because it is easy to inspect. Your real inputs might be a customer export, JSON event logs, or Parquet files containing historical activity.

You can import those files and apply the same process:

1. Identify the account or customer key.
2. Check dates, missing values, and duplicate records.
3. Define the outcome and observation window.
4. Join the relevant data.
5. Interpret the result alongside its sample size.

Use stable account IDs wherever possible. An email address that changes or appears under several accounts can quietly undermine a join.

You can also query eligible PostgreSQL or MariaDB databases in Layerbase through read-only account sources. These are available on every plan within its limits. The source must be in the same personal account and server container; shared-team databases and app backing stores are not eligible.

In the DuckDB database's Sources tab, attach an eligible PostgreSQL database with the alias pg_source and test the connection. If that database has a public.accounts table, you can read it like this:

SELECT *
FROM pg_source.public.accounts
LIMIT 10;

This example requires your own source table; it is not part of the sample setup. You could adapt the metrics view to read that source instead of an imported accounts table.

A live source query still consumes resources on the source database. Read-only access prevents writes; it does not make analytical queries free of load.

External PostgreSQL sources and customer-owned S3/R2 storage are available on Pro and Dedicated as Beta features, still a work in progress. External PostgreSQL uses a SELECT-only login and verified TLS. Your own bucket is optional, and its provider bills storage, requests, and transfer separately. See the supported options.

When you want to keep analytical data, add DuckLake

Everything so far works without DuckLake.

You can keep tables and results in a standalone DuckDB database. That may be enough for your current needs.

DuckLake becomes relevant when you want analytical tables organized in a lake format, with catalog metadata and data files managed together. On Layerbase, the managed arrangement uses PostgreSQL for the catalog and Cloudflare R2 for Parquet storage.

You continue querying through DuckDB.

On Pro or Dedicated, open the DuckDB database's Sources tab, select an eligible PostgreSQL catalog database, and enable the managed lake. The catalog must be in the same personal account and server container; start it before setup. Layerbase handles the managed R2 storage setup. One managed DuckLake is supported per account, within its storage allowance. See the DuckLake setup guide.

Once it is attached, save the results from our walkthrough:

CREATE TABLE ducklake.main.account_metrics_2026_10_22 AS
SELECT
    DATE '2026-10-22' AS observed_before,
    *
FROM layerbase.main.account_metrics;

Then query the saved table:

SELECT
    channel,
    count(*) AS accounts,
    count(*) FILTER (WHERE activated) AS activated_accounts
FROM ducklake.main.account_metrics_2026_10_22
GROUP BY channel
ORDER BY channel;

The SQL is familiar. The destination has changed.

The explicit ducklake.main prefix puts the table in the lake; layerbase.main refers to your local DuckDB tables.

This operation saves a copy of the query result. Future changes to the source tables do not automatically update it. You choose when and how to refresh or append data.

DuckLake also has its own versioned snapshots. Those describe changes within the lake; they do not continuously capture changes in an external application database. Small writes may initially be held in the catalog before becoming Parquet files. Learn how DuckLake stores and versions data.

For a recurring report, you could eventually append results with an observation date and compare them over time. Start by making one query useful and repeatable before adding scheduling.

Understand the current storage and recovery boundaries

A lake snapshot is not an independent backup.

Layerbase currently does not back up DuckLake's Parquet objects. The PostgreSQL catalog follows its own database backups. A DuckDB database with a retained lake is not covered by local backups, and operations including branching, restoring in place, moving, transferring, and deleting it are blocked.

Detaching keeps the catalog and objects; it does not remove those restrictions. Recovery is operator-assisted.

Review the current DuckLake limitations before making the lake your only copy of important data.

Keep using familiar tools

The browser console is enough to complete this walkthrough.

If you prefer another SQL client or want to query from an application, Layerbase also provides a PostgreSQL-compatible connection to DuckDB. You use a compatible client or driver while writing DuckDB SQL. Protocol compatibility does not make it PostgreSQL or guarantee every PostgreSQL extension or ORM feature will work.

Compatible DuckDB clients can use the separate native connection through Quack. Follow the dashboard's connection snippet and the native connection guide, particularly when querying attached sources or lake tables.

You can choose the connection method after you have a useful question and a working query.

Start with one question about your product

You probably already have a question that matters:

Why do some signups never finish setup? Which channels bring customers who stay? Does a particular workflow appear more often among accounts that return?

Pick one. Find the smallest dataset that can help answer it. Write down what success means and how long an account needs to demonstrate it.

Then use the SQL you already know.

Create a free DuckDB database on Layerbase, load the sample files, and run the first query. When you understand the result, replace one sample table with data from your own product.