Going from 0 to 1 with MotherDuck - Webinar - Thursday, January 23 9am PST

Mehdi joins us as a Data Engineering and Analytics Advocate after a career as a Data Engineer and a nighttime hobby of creating YouTube videos and other content for data professionals.

Mehdi Ouazza

If you’re building web apps—whether frontend or backend—you’re probably fine using Postgres or another transactional database for most use cases. But as soon as your app needs data-intensive features, like an analytics dashboard for users or insights on product usage, things can slow down. That’s because transactional databases aren’t built for complex analytical queries.

In the past, you would often hand this off to a separate team with a specialized setup, but today, infrastructure is more straightforward, and SQL has become the go-to tool for analytics.

In this blog, we’ll quickly cover what analytical databases are, when to use them, how to move data from your OLTP database, and a practical examples of using an OLAP cloud service like MotherDuck, directly in your Vercel application.

If you prefer watching over reading : 

<iframe width="560" height="315" src="https://www.youtube.com/embed/DUTCdseUTAc" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen style="margin-bottom: 15px"></iframe>

## What are analytical databases

Analytical databases, or OLAP (Online Analytical Processing) databases, are designed for querying and analyzing large datasets. Unlike transactional databases like Postgres, which is excellent at handling fast, small-scale operations like creating or updating records, OLAP databases are optimized for heavy, read-intensive operations.

![img1](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/webdevelopers_olap_00_01_30_13_Still003_ac80210e4d.png)

They’re built for complex queries, like calculating averages across millions of rows, filtering data by multiple criteria, or aggregating metrics over time. They’re also much faster at these operations because they store and process data differently, typically using columnar storage.

![img2](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/webdevelopers_olap_00_01_41_13_Still004_6e15dc6cab.png)

In short, OLAP databases are ideal for scenarios where you need to crunch large datasets to find trends, patterns, or insights.

## When to use analytical databases 

First, it’s important to note that it’s perfectly fine to start prototyping your analytics use cases on your current transactional database, like Postgres. 
Many analytical projects begin like that, especially for smaller datasets or simple reporting.

However, as your app grows and the complexity or volume of data increases, you’ll likely hit performance bottlenecks. That’s when you should consider moving to an analytical database, as you don’t want these analytical queries consuming your entire database's resources.

![img3](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/webdevelopers_olap_00_02_20_11_Still005_8129e5490c.png)

Here are some common scenarios for analytical databases:

1. **User-Facing Analytics Dashboards:** If your app needs to show users detailed analytics, like tracking usage trends or performance metrics, OLAP databases make it easy to generate fast, interactive reports.
2. **Product Insights:** If you want to understand how users are interacting with your app—like which features are most popular or what leads to churn—OLAP databases let you run exploratory queries efficiently.
3. **Combining Data Sources:** If you need to merge data from multiple systems—like CRM data with app usage data—an analytical database simplifies this process by handling large, diverse datasets.

These are not exclusive use cases but the most common ones you might see.

## How to move data to your analytical database

There are three common methods.

### 1.  ETL Pipelines
ETL stands for Extract, Transform, Load. This is a common approach to move data. You extract it from your OLTP database, clean or reformat it, and load it into your OLAP database. You typically have a process (in Python or whataever have you), that would move the data. 
There are two classic approaches: 
- **Directly to OLAP system:** You can process and load your data directly into the analytical database.
- **Offload to Object Storage:** You can write your data to an object storage system like S3. This gives you more flexibility to process the data later , be free on the processing tool you wanna use instead of leveraging the OLAP database directly. 

### 2.  **Real-Time Streaming:** 
If you need live updates for dashboards or analytics, you can use real-time streaming tools like Kafka or AWS Kinesis to move data continuously. 
These event streaming services often integrate with Change Data Capture (CDC) tools to track and stream changes in real time. They are excellent for capturing incremental updates and syncing them efficiently into your OLAP database.
### 3. Direct Querying
Some OLAP systems allow direct queries on your transactional database without moving data or relying on another process. 

For example:
- **[DuckDB’s Postgres Scanner:](https://duckdb.org/docs/extensions/postgres.html)** DuckDB can connect directly to Postgres to run analytical queries on your existing data.
- **[pg_duckdb Extension:](https://github.com/duckdb/pg_duckdb)** This is a new Postgres extension that embeds DuckDB directly inside Postgres, allowing you to leverage DuckDB’s analytical capabilities without additional infrastructure and to connect to MotherDuck.

Each method depends on your app’s needs. Real-time streaming is ideal for live dashboards, ETL is great for batch analytics, and direct querying works well for smaller-scale use cases, as it's really easy to get started.

## Using MotherDuck (OLAP database) directly in Vercel

Let’s dive into an example of connecting your web application to an OLAP database in your data stack using Vercel and its native integration with MotherDuck, which runs DuckDB in the cloud.

In this use case, we’ll hydrate analytical data stored in MotherDuck to feed directly into your application.

With the native integration, you can create a MotherDuck account without ever leaving Vercel, streamlining the process with a single platform for both setup and billing.

Simply head to [the template listing](https://vercel.com/templates/next.js/next-js-motherduck-wasm-analytics-quickstart), where you can easily deploy a ready-made template with just a few clicks or install the integration into an existing project.

In this demo, we’re showcasing a Vercel data dashboard—and as you’ll notice, it’s incredibly fast and responsive.

<video width="700" height="700" loop autoplay muted playsinline style="border: 2px solid rgb(56, 56, 56); margin-bottom: 24px;max-width: 100%; height: auto">
  <source src="https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/demo_vercel_3a7b940a89.mp4" type="video/mp4">
  Your browser does not support the video tag.
</video>

Here’s why:

1. It leverages [MotherDuck](https://motherduck.com/) Cloud for handling larger queries.
2. It uses [DuckDB Wasm](https://duckdb.org/docs/api/wasm/overview.html), enabling an analytical database to run directly in the browser. This approach takes advantage of the client’s processing power, reducing extra I/O traffic.

The result? It provides a smoother experience for users and lower computing costs for developers.

## Conclusion
To wrap up, analytical databases unlock a world of possibilities for web developers. They help you handle data-intensive features like user dashboards, gain deeper insights into your product, and combine data from multiple sources—all without overloading your transactional database.

With modern tools and SQL as a common language, setting up these workflows has never been easier. So, the next time your OLTP database is struggling, think about OLAP.


Start using [MotherDuck for free today](https://motherduck.com/get-started/), and explore our [documentation on the Vercel integration](https://motherduck.com/docs/integrations/web-development/vercel/)!

Keep quacking and keep coding.

Why web developers should care about analytical databases

Jacob joined MotherDuck after a career as a startup operator in finance and data roles. 

Jacob Matson

Aditya is Co-founder at Zenith. Previously ML Engineer at Uber and Google.

Aditya Somani

## Introduction

Data analytics requires sophisticated tools that can perform complex calculations while maintaining detailed row-level insights. Window functions are a powerful technique to meet this challenge, and MotherDuck provides an easy-to-use experience for implementing these analytical queries.

## What are Window Functions?

Window functions operate on a defined subset of rows within a result set. Unlike traditional aggregate functions that compress data into a single summary value, window functions enable calculations across a group of rows while preserving individual row details. They are particularly useful for:

- **Ranking**: Determining the position of items within a category  
- **Moving Averages**: Calculating smoothed values over a sliding data set  
- **Cumulative Calculations**: Tracking value accumulation over time or within groups

## MotherDuck: DuckDB in the Cloud

MotherDuck offers a [cloud-native approach](https://motherduck.com/docs/concepts/architecture-and-capabilities/) to data analysis, providing a multiplayer environment for running complex queries. Its architecture supports seamless window function implementations, allowing data folks to perform sophisticated analytical tasks quickly and easily from the convenience of their browser or CLI.

Key advantages of MotherDuck include:  
- [Cloud-based collaboration](https://motherduck.com/docs/key-tasks/sharing-data/sharing-within-org/)
- [Dual execution query capabilities](https://motherduck.com/docs/concepts/architecture-and-capabilities/#dual-execution)
- [Beautiful UI for data exploration](https://motherduck.com/docs/getting-started/motherduck-quick-tour/)
- [All the things that make DuckDB fast, but in the cloud](https://duckdb.org/why_duckdb.html#fast)

## Getting Started: A Practical Example

Let's create a sample dataset to illustrate window functions in MotherDuck:

```sql  
CREATE TABLE sales (  
  sales_date DATE,  
  product TEXT,  
  region TEXT,  
  sales_amount DOUBLE  
);

INSERT INTO sales VALUES  
('2023-01-01', 'Product A', 'East', 200),  
('2023-01-02', 'Product A', 'East', 250),  
('2023-01-03', 'Product A', 'East', 300),  
('2023-01-01', 'Product B', 'West', 400),  
('2023-01-02', 'Product B', 'West', 450),  
('2023-01-03', 'Product B', 'West', 500);  
```

## Anatomy of a Window Function: The OVER Clause

In MotherDuck, the [OVER clause defines the window](https://duckdb.org/docs/sql/functions/window_functions.html) for calculations by addressing three key aspects:  
- **Partitioning**: Dividing data into groups  
- **Ordering**: Arranging items within groups  
- **Framing**: Specifying the range of rows to include in calculations

A general template for window functions looks like this:

```sql
-- for illustrative purposes, not executable SQL
function_name(expression) OVER (  
  PARTITION BY column_name  
  ORDER BY column_name  
  ROWS/RANGE BETWEEN start_point AND end_point  
)  
```

## Common Window Functions in MotherDuck

### 1. `row_number()`: Assigning Unique Identifiers

Assigns a unique sequential number to rows within a partition:

```sql  
SELECT  
  sales_date,  
  product,  
  region,  
  sales_amount,  
  row_number() OVER (PARTITION BY region ORDER BY sales_date) AS row_id  
FROM sales;  
```

**Query Result:**  
```  
sales_date | product | region | sales_amount | row_id  
-----------+---------+--------+--------------+-------  
2023-01-01| Product A| East   | 200          | 1  
2023-01-02| Product A| East   | 250          | 2  
2023-01-03| Product A| East   | 300          | 3  
2023-01-01| Product B| West   | 400          | 1  
2023-01-02| Product B| West   | 450          | 2  
2023-01-03| Product B| West   | 500          | 3  
```

### 2. `rank()` and `dense_rank()`: Establishing Order

These functions determine a value's rank within a partition, with different approaches to handling ties:

```sql  
SELECT  
  product,  
  region,  
  sales_amount,  
  rank() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank,  
  dense_rank() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS dense_sales_rank  
FROM sales;  
```

**Query Result:**  
```  
product   | region | sales_amount | sales_rank | dense_sales_rank  
----------+--------+--------------+------------+-----------------  
Product A | East   | 300          | 1          | 1  
Product A | East   | 250          | 2          | 2  
Product A | East   | 200          | 3          | 3  
Product B | West   | 500          | 1          | 1  
Product B | West   | 450          | 2          | 2  
Product B | West   | 400          | 3          | 3  
```

### 3. `lag()` and `lead()`: Analyzing Adjacent Rows

Access values from preceding or following rows within a partition:

```sql  
SELECT  
  sales_date,  
  product,  
  region,  
  sales_amount,  
  lag(sales_amount, 1) OVER (PARTITION BY region ORDER BY sales_date) AS previous_day_sales  
FROM sales
ORDER BY product, sales_date;  
```

**Query Result:**  
```  
sales_date | product | region | sales_amount | previous_day_sales  
------------+---------+--------+--------------+--------------------  
2023-01-01 | Product A | East  | 200          | NULL  
2023-01-02 | Product A | East  | 250          | 200  
2023-01-03 | Product A | East  | 300          | 250  
2023-01-01 | Product B | West  | 400          | NULL  
2023-01-02 | Product B | West  | 450          | 400  
2023-01-03 | Product B | West  | 500          | 450  
```

### 4. Moving Averages: Analyzing Trends

Calculate averages over a sliding window of rows:

```sql  
SELECT  
  sales_date,  
  product,  
  region,  
  sales_amount,  
  avg(sales_amount) OVER (  
    PARTITION BY region  
    ORDER BY sales_date  
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  
  ) AS moving_avg  
FROM sales
ORDER BY product, sales_date;  
```

**Query Result:**  
```  
sales_date | product | region | sales_amount | moving_avg  
------------+---------+--------+--------------+------------  
2023-01-01 | Product A | East  | 200          | 225  
2023-01-02 | Product A | East  | 250          | 250  
2023-01-03 | Product A | East  | 300          | 275  
2023-01-01 | Product B | West  | 400          | 425  
2023-01-02 | Product B | West  | 450          | 450  
2023-01-03 | Product B | West  | 500          | 475  
```

## Advanced Analytical Capabilities in MotherDuck

MotherDuck supports additional window function techniques:  
- The [QUALIFY Clause](https://duckdb.org/docs/sql/query_syntax/qualify.html) for advanced filtering  
- The [ntile()](https://duckdb.org/docs/sql/functions/window_functions.html#ntilenum_buckets) Function for data distribution  
- The [percent_rank()](https://duckdb.org/docs/sql/functions/window_functions.html#percent_rank) Function for relative ranking  
- [Named Windows](https://duckdb.org/docs/sql/query_syntax/window.html) for query optimization
- DuckDB specific querys like [arg_min and arg_max](https://duckdb.org/docs/sql/functions/aggregates.html#arg_maxarg-val)

## Conclusion

MotherDuck provides a powerful platform for implementing window functions, enabling data professionals to perform sophisticated analytical queries with ease. By offering these flexible, easy-to-use analytics capabilities, MotherDuck supports seamless and fast insight generation for even the most complex queries.

As data complexity continues to grow, platforms like MotherDuck demonstrate the importance of these kinds of analytical tools in transforming raw data into meaningful insights.  


Window Functions in Motherduck: An Analytical Approach

We made a [game](https://game.motherduck.com/) 🎮.

About ducks. 

And fake ducks. Your task is to spot which ducks are fake (a.k.a. AI-generated). Watch out—it’s not as easy as it seems!

![Spot the Duck Game](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/Screenshot_2024_12_20_at_10_53_55_AM_47b2a745f3.png)

Oh, and there’s a prize if you manage to waddle your way onto the [leaderboard](https://game.motherduck.com/duckleaderboard) 🏆.

![Leaderboard](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/Screenshot_2024_12_20_at_10_52_49_AM_845bdbe771.png)

The contest runs through the holidays season, from December 20, 2024, to January 4, 2025.

But wait—don’t feel ruffled if you can’t crack the leaderboard during the first week. We’ll take the winners, reset the scores on December 28, and let everyone go quackers again for a second chance at glory.

Let the duck games begin! 🚀

**PS : This game was inspired by another [one](https://huggingface.co/spaces/victor/fake-insects/tree/main) created by [Victor Mustar](https://www.linkedin.com/in/victor-mustar-22466951/). Thanks to Victor for the fun and inspiration—your work is awesome!**

We made a fake duck game: compete to win!

Subscribe to motherduck blog

PREVIOUS POSTS

Why web developers should care about analytical databases

Window Functions in Motherduck: An Analytical Approach