In this post you’ll learn

• How MotherDuck extends DuckDB to handle multi-table queries in the cloud.
• The importance of the read-scaling token for 4x faster data loading, especially when wrangling multiple tables.
• How Preswald helps you build live, Python-based data apps that go beyond static dashboards.

Common Pain points

1. Multiple, fragmented tables: e.g., demographics, lab results, comorbidities.
2. Slow ingest and overhead: “Scaling up” typically means big clusters or advanced ETL.
3. One-dimensional dashboards: Spreadsheets and static BI can’t handle evolving questions in real time.

Step 1: Install Dependencies

Make sure you have duckdb, pandas, plotly, and preswald installed in your Python environment.

Copy code
pip install duckdb pandas plotly preswald

Step 2: Connect to MotherDuck

You can connect to MotherDuck using your MotherDuck token. By default, duckdb.connect("md:my_db") will look for an environment variable called MOTHERDUCK_TOKEN. If you’d like read-scaling for faster queries, append ?read_scaling_token=YOUR_TOKEN_HERE to the connection string.

Copy code
import duckdb

# Example with environment variable:
# export MOTHERDUCK_TOKEN=<your_token_here>
con = duckdb.connect("md:my_db")

# OR with read scaling explicitly:
# con = duckdb.connect("md:my_db?read_scaling_token=<your_token_here>")

Step 3: Query the Cholesterol Table

In this example, we’ll pull data from a table named DQS_Cholesterol_in_adults_age_20. Once connected, run a standard SQL query to bring your data into a Pandas DataFrame.

Copy code
# 1. Query your table
df = con.execute("SELECT * FROM DQS_Cholesterol_in_adults_age_20").df()

# 2. Take a quick peek
print(df.head())

This shows you the first few rows, confirming you have the data you expect.

Step 4: Build a Preswald Dashboard

We’ll build three Plotly charts and present them with Preswald:

1. A line chart showing cholesterol estimates over time
2. A bar chart comparing age-adjusted vs. crude estimates
3. A scatter plot to visualize estimates across different subgroups

Here’s the full code with comments explaining each part:

Copy code
import pandas as pd
import duckdb
import plotly.express as px
from preswald import text, plotly, view

# ----------------------------------------------------------------------------
# STEP A: Connect to MotherDuck
# ----------------------------------------------------------------------------
con = duckdb.connect("md:my_db")
df = con.execute("SELECT * FROM DQS_Cholesterol_in_adults_age_20").df()

# ----------------------------------------------------------------------------
# STEP B: Add descriptive text for Preswald
# ----------------------------------------------------------------------------
text("# Cholesterol Data Exploration")
text("Below are several charts that help us visualize cholesterol estimates.")

# ----------------------------------------------------------------------------
# STEP C: Create a line chart of ESTIMATE over TIME_PERIOD
# ----------------------------------------------------------------------------
text("## Chart A: Trend of Cholesterol Estimates Over Time")

# Filter out rows that don’t have an actual ESTIMATE
df_line = df.dropna(subset=["ESTIMATE"]).copy()

fig_a = px.line(
    df_line,
    x="TIME_PERIOD",
    y="ESTIMATE",
    color="ESTIMATE_TYPE",  # e.g., "Percent of population, age adjusted" vs "crude"
    markers=True,
    title="Cholesterol Estimate by Time Period"
)
plotly(fig_a)

# ----------------------------------------------------------------------------
# STEP D: Create a grouped bar chart comparing ESTIMATE_TYPE
# ----------------------------------------------------------------------------
text("## Chart B: Comparison of Age Adjusted vs. Crude Estimates")

fig_b = px.bar(
    df_line,
    x="TIME_PERIOD",
    y="ESTIMATE",
    color="ESTIMATE_TYPE",
    barmode="group",
    title="Age Adjusted vs. Crude Estimates"
)
plotly(fig_b)

# ----------------------------------------------------------------------------
# STEP E: Create a scatter plot of ESTIMATE vs. SUBGROUP
# ----------------------------------------------------------------------------
text("## Chart C: Scatter Plot of Estimate vs. Subgroup")

fig_c = px.scatter(
    df_line,
    x="SUBGROUP_ID",
    y="ESTIMATE",
    color="GROUP",      # e.g. "Total" vs. "Race and Hispanic origin"
    size="ESTIMATE",
    hover_data=["TIME_PERIOD", "ESTIMATE_TYPE"],
    title="Cholesterol Estimate by Subgroup"
)
plotly(fig_c)

# ----------------------------------------------------------------------------
# STEP F: Render the final output in Preswald
# ----------------------------------------------------------------------------
# We'll also show a table preview at the bottom.
view(df)

# Close the DuckDB connection if you like
con.close()

Step 5: Run & View Your Interactive App

With everything in place, run the script using Preswald:

preswald run my_script.py

This launches a local server. Open the provided URL in your web browser, and you’ll see your line chart, bar chart, scatter plot, plus a data table preview. From here, you can:

• Filter or pivot your data (if you add user inputs)
• Refresh the script for near-instant updates
• Share the app link with colleagues for real-time collaboration