Skip to main content

Running Dual Execution (or hybrid) queries

MotherDuck can use local data and remote data in the same query. The editors on this page connect to your my_db MotherDuck database, so you can run each example against your own account. "Local" data in these examples comes from an inline VALUES clause, which DuckDB evaluates in the browser; the sales table lives in MotherDuck, so the planner runs it remotely.

Create a remote sales table​

The editor below writes to my_db.main.remote_sales_table. The preview shows what the second statement returns; run the query to materialize the table in your own account.

Create a remote sales table
CREATE OR REPLACE TABLE my_db.main.remote_sales_table AS
SELECT
  'ABCD' [floor(random() * 3.999)::int + 1] AS item,
  current_date() - interval(random() * 100) days AS dt,
  floor(random() * 50)::int AS tally
FROM
  generate_series(1000);

FROM
  my_db.main.remote_sales_table
LIMIT
  10;
SQL Editor loading...
Login to connect
PreviewLogin for live results

In your own CLI or notebook you can use any database name. For example CREATE OR REPLACE DATABASE remote_db; followed by CREATE TABLE remote_db.sales AS ....

Join local and remote data​

The query below joins inline pricing data (local) with the sales table you created above (remote) to produce revenue by month. DuckDB executes the VALUES clause locally and reads remote_sales_table from MotherDuck.

Revenue by month
SELECT
  date_trunc('month', sales.dt) AS mo,
  round(sum(pricing.price * sales.tally), 2) AS rev
FROM
  my_db.main.remote_sales_table AS sales
  JOIN (
    VALUES
      ('A', 1.4),
      ('B', 1.12),
      ('C', 2.552),
      ('D', 5.23)
  ) AS pricing (item, price) ON sales.item = pricing.item
WHERE
  pricing.price > 2
GROUP BY
  mo
ORDER BY
  mo;
SQL Editor loading...
Login to connect
PreviewLogin for live results

Inspect the hybrid query plan​

Prefix the query with EXPLAIN to see which operators run locally and which run on MotherDuck. The editor renders DuckDB's JSON plan as a tree; each operator carries an L (local) or R (remote) tag and the JOIN branches into its two inputs.

Explain the hybrid query plan
EXPLAIN (FORMAT JSON)
SELECT
  date_trunc('month', sales.dt) AS mo,
  round(sum(pricing.price * sales.tally), 2) AS rev
FROM
  my_db.main.remote_sales_table AS sales
  JOIN (
    VALUES
      ('A', 1.4),
      ('B', 1.12),
      ('C', 2.552),
      ('D', 5.23)
  ) AS pricing (item, price) ON sales.item = pricing.item
WHERE
  pricing.price > 2
GROUP BY
  mo
ORDER BY
  mo;
SQL Editor loading...
Login to connect
PreviewLogin for live results

Data is transferred between local and remote with matching pairs of sinks and sources, identified by bridge_id.

A Dual Execution (or hybrid) query can run on any database format supported by DuckDB, including sqlite, postgres and many others.