The future of BI: Exploring the impact of BI-as-code tools with DuckDB

A. Setup & Project Structure

You can bootstrap an Evidence project with a single command. The Makefile in our demo repo simplifies this process to:

Copy code
# Install dependencies
make evidence-install

# Run the local development server
make evidence

The project structure is simple:

• pages/: Contains your Markdown files, where each file becomes a page in your app.
• evidence.plugins.yaml: Manages plugins and component configurations.

B. Connecting to DuckDB & MotherDuck

Once the local server runs, you can configure your data source in the settings UI at http://localhost:3000/settings.

• For Local DuckDB: Provide the path to your .db file.
• For MotherDuck: Select DuckDB, and in the "Filename / DSN" field, enter your MotherDuck connection string: md:?motherduck_token=YOUR_TOKEN_HERE.
  • Note: For production applications, manage credentials like your MotherDuck token using environment variables or a secrets manager rather than hardcoding them in your connection string.

C. Creating a Visualization

In Evidence, you embed SQL queries directly into your Markdown files using fenced code blocks. You then pass the query result to a component.

Here's how to display the total download count and a line chart in pages/index.md:

Copy code
---
title: DuckDB pypi stats | Evidence
---

## How many people downloaded DuckDB?

<BigValue 
    data={total_downloads} 
    value='download_count' 
    fmt='#,##0.00,,"M"'
/>

### Daily Downloads

<LineChart 
    data={daily_downloads} 
    y=daily_download 
    x=timestamp_day
/>

```sql total_downloads
SELECT SUM(daily_download_count) AS download_count
FROM daily_stats

Copy code
SELECT 
    SUM(daily_download_count) AS daily_download,
    timestamp_day
FROM 
    daily_stats
GROUP BY 
   timestamp_day 
ORDER BY 
    timestamp_day DESC;

The <BigValue> and <LineChart> components automatically render the data returned by the total_downloads and daily_downloads SQL queries.

A. Setup & Project Structure

Rill uses a single Go binary for its CLI. The demo repo's Makefile handles the installation.

Copy code
# Install the Rill CLI
make rill-install

# Start the local development server
make rill

This command starts the Rill UI and points it to our local DuckDB database. The project structure is organized into:

• sources/: YAML files defining your data sources.
• models/: SQL files that transform your source data.
• dashboards/: YAML files that define the metrics and dimensions for your dashboard.

B. Connecting to DuckDB & MotherDuck

• For Local DuckDB: Start Rill using the command rill start rill/rill-project. Then, in the web interface, add the duckdb_stats.db file location as a source.
• For MotherDuck: Set an environment variable with your token before running rill start: export MOTHERDUCK_TOKEN=<YOUR_TOKEN>. Rill will automatically detect and use this token.

C. Creating a Visualization

First, we create a "model" to expose our table to Rill. This model is a simple SQL file, models/model.sql:

Copy code
select * from duckdb_stats.main.daily_stats

Next, we define the dashboard in dashboards/model_dashboard.yaml. Rill automatically generates a dashboard based on the measures (aggregations) and dimensions (group-by columns) you specify.

Copy code
# Visit https://docs.rilldata.com/reference/project-files to learn more
title: Pypi Download Stats
model: daily_stats
timeseries: timestamp_day
measures:
  - label: Total Downloads
    expression: sum(daily_download_count)
    name: total_records
    description: Total number of records present
    format_preset: humanize
dimensions:
  - name: python_version
    label: Python_version
    column: python_version
  - name: system_name
    label: System_name
    column: system_name

Rill uses this definition to create a full-featured dashboard with a time-series line chart, leaderboards for each dimension, and filtering capabilities, all without you needing to configure individual components.

A. Setup & Project Structure

Getting started is as simple as installing the required Python packages. Our Makefile uses Poetry:

Copy code
# Install Python dependencies
make streamlit-install

# Run the Streamlit app
make streamlit

For a simple app, your entire project can be a single app.py file.

B. Connecting to DuckDB & MotherDuck

Connecting to your data is done with standard Python code inside your script.

Copy code
import streamlit as st
import duckdb
import pandas as pd

# For Local DuckDB:
con = duckdb.connect(database='duckdb_stats.db', read_only=True)

# For MotherDuck:
# con = duckdb.connect(database='md:?motherduck_token=YOUR_TOKEN_HERE')
# Note: For production applications, manage credentials 
# using Streamlit's secrets management or environment variables.

C. Creating a Visualization

The workflow is straightforward: query data into a Pandas DataFrame, then pass that DataFrame to one of Streamlit's many charting or display components.

Here's the code from streamlit-demo/app.py to show the total downloads metric and a monthly line chart:

Copy code
import streamlit as st
import duckdb
import pandas as pd

# Connect to your DuckDB database
con = duckdb.connect(database='duckdb_stats.db', read_only=True)

st.title("DuckDB pypi stats | Streamlit")

# Query for data (simplified)
query = "SELECT timestamp_day, daily_download_count FROM duckdb_stats.main.daily_stats"
df = con.execute(query).df()

# Total Downloads Metric
total_downloads = df['daily_download_count'].sum()
st.metric("Total Downloads", f"{total_downloads / 1_000_000:.2f}M")

# Line Graph of Downloads Over Time
st.subheader("Monthly Downloads Over Time")
# Resample data by month for the chart
df['timestamp_day'] = pd.to_datetime(df['timestamp_day'])
df_monthly = df.set_index('timestamp_day').resample('M')['daily_download_count'].sum().reset_index()
st.line_chart(df_monthly, x='timestamp_day', y='daily_download_count')

This approach gives you the full power of Python to manipulate data before visualization.