Skip to main content

4 - Visualizing and Automating

In part 3 you created a currency_rates table in your docs_playground database and shared it with your team. In this part, you'll turn that table into an interactive visualization with a Dive and keep the data fresh with a scheduled Flight. Both are available on all MotherDuck plans.

👈 Go back to Part 3: Sharing Your Database

Create a Dive from your data​

Dives are interactive visualizations you create with natural language. You describe what you want to see, and MotherDuck generates a persistent, shareable component that queries your live data.

You create a Dive by prompting an AI assistant connected to the MotherDuck MCP Server:

  1. Connect an AI client (Claude, ChatGPT, Cursor, or others) to the MotherDuck MCP Server. The AI data analysis guide walks you through the setup in about 5 minutes.
  2. Ask for a Dive and name your table: "Create a Dive showing the exchange rate to US dollar for each currency in docs_playground.currency_rates as a bar chart."
  3. Iterate conversationally: "sort by rate", "switch to a horizontal bar chart". Each edit saves as a separate version.
  4. Ask the agent to "save this Dive to MotherDuck". The Dive appears in the Object Explorer sidebar of the MotherDuck UI, and under Settings → Dives.

Because a Dive queries live data, it stays up to date as the underlying table changes, which is exactly what the next section takes advantage of.

Keep the data fresh with a Flight​

The currency_rates table from part 3 contains four hand-entered rows that never change. Flights fix that: a Flight is a Python program that MotherDuck runs for you, on demand or on a cron schedule.

The same currency data lives in MotherDuck's public S3 bucket (you queried it in part 2), so this Flight rebuilds the table from that source, replacing the four sample rows with the full public dataset. That dataset carries codes rather than currency names, so the rebuilt table keeps the code, the rate, and the rate date.

Create the Flight​

MD_CREATE_FLIGHT takes the Python source as a dollar-quoted string and pins its dependencies with requirements_txt. Run it here to create the Flight in your own account:

Create the currency refresh Flight
SELECT flight_id, flight_name, current_version
FROM MD_CREATE_FLIGHT(
  name := 'tutorial_refresh_currency_rates',
  requirements_txt := 'duckdb==1.5.5',
  source_code := $flight$
import duckdb

SOURCE = "s3://us-prd-motherduck-open-datasets/misc/csv/popular_currency_rate_dollar.csv"

def main():
  con = duckdb.connect("md:")
  con.execute(f"""
      CREATE OR REPLACE TABLE docs_playground.currency_rates AS
      SELECT
          currency_code,
          exchange_rate AS rate_to_usd,
          to_timestamp("timestamp")::DATE AS rate_date
      FROM read_csv('{SOURCE}')
  """)
  row_count = con.execute("SELECT count(*) FROM docs_playground.currency_rates").fetchone()[0]
  print(f"refreshed docs_playground.currency_rates with {row_count} rows")

if __name__ == "__main__":
  main()
$flight$
);
SQL Editor loading...
Login to connect

Two conventions to note in that Python: the runtime executes the source as a plain script, so end it with if __name__ == "__main__": main(), and duckdb.connect("md:") authenticates as you automatically, no token setup needed.

Run it once​

The Flight has no schedule yet, so it runs only when you trigger it. Store its ID in a SQL variable and start a run:

Run the Flight
SET VARIABLE currency_flight_id = (
  SELECT flight_id
  FROM MD_LIST_FLIGHTS()
  WHERE flight_name = 'tutorial_refresh_currency_rates'
  ORDER BY created_at DESC
  LIMIT 1
);

SELECT run_number, status, flight_version
FROM MD_RUN_FLIGHT(
  flight_id := getvariable('currency_flight_id')
);
SQL Editor loading...
Login to connect
note

The blocks below reuse the currency_flight_id variable. If you reload this page, run the block above again to set it.

Runs are asynchronous, so the run starts out pending. Poll it until ended_at fills in, with a status of succeeded and an exit_code of 0. This Flight takes a few seconds:

Check the run status
SELECT run_number, status, exit_code, ended_at
FROM MD_LIST_FLIGHT_RUNS(
  flight_id := getvariable('currency_flight_id')
)
ORDER BY run_number DESC
LIMIT 3;
SQL Editor loading...
Login to connect

If the run fails, read its output with MD_GET_FLIGHT_LOGS, or open the Flight in the MotherDuck UI, where every run and its log is listed. You can create and manage the same Flight in the UI or from an AI agent instead of SQL.

Once the run succeeds, query the refreshed table:

SQL editor
SELECT
  currency_code,
  rate_to_usd,
  rate_date
FROM
  docs_playground.currency_rates
ORDER BY
  rate_to_usd
LIMIT
  10;
SQL Editor loading...
Login to connect

Your Dive from the previous section picks up the refreshed data on its own, no changes needed.

Put it on a schedule​

With one successful run behind you, add a cron schedule so MotherDuck refreshes the table every morning at 06:00 UTC. Schedule changes are metadata-only, so they don't create a new Flight version:

Schedule the Flight
CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('currency_flight_id'),
  schedule_cron := '0 6 * * *'
);
SQL Editor loading...
Login to connect

That's a daily job running in your account from here on. To switch it off, pass an empty schedule_cron, which leaves the Flight in place with its schedule disabled. To remove it entirely, use MD_DELETE_FLIGHT:

Turn the schedule off
CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('currency_flight_id'),
  schedule_cron := ''
);
SQL Editor loading...
Login to connect

Wrapping up​

Congratulations, you've completed the tutorial! You queried shared data, loaded your own, shared a database with your team, visualized it with a Dive, and automated the refresh with a Flight. To go deeper: