Cross-source examples

These workbench examples use fictional catalog names. pg is a PostgreSQL connection, bq is BigQuery, and lake is FastBI Lake. They are patterns to adapt, not statements that these tables already exist.

Aggregate orders before joining customers

Assume one customer record per id. The order CTE uses portable functions and belongs entirely to BigQuery, so it is eligible for source execution. The customer CTE is independent and belongs to PostgreSQL.

WITH order_totals AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM bq.sales.orders
  WHERE order_date >= DATE '2026-09-01'
    AND order_date < DATE '2026-10-01'
  GROUP BY customer_id
),
customer_dimensions AS (
  SELECT id, country
  FROM pg.public.customers
)
SELECT c.country, SUM(o.revenue) AS revenue
FROM order_totals AS o
JOIN customer_dimensions AS c ON c.id = o.customer_id
GROUP BY c.country
ORDER BY revenue DESC;

This transfers customer-level totals instead of raw orders when source-side filtering is used. Inspect Explain to confirm the source fragment includes the date filter and aggregate. A non-unique customer dimension still multiplies totals; deduplicate it according to your business rule first.

Compare warehouse sales with a Lake budget

Assume the Lake budget has one row per country for September 2026, and both amounts use the same currency.

WITH sales AS (
  SELECT country, SUM(amount) AS revenue
  FROM bq.sales.orders
  WHERE order_date >= DATE '2026-09-01'
    AND order_date < DATE '2026-10-01'
  GROUP BY country
)
SELECT s.country,
       s.revenue,
       b.budget,
       s.revenue - b.budget AS variance,
       PERCENT(s.revenue, b.budget) AS attainment_percent
FROM sales AS s
LEFT JOIN lake.finance.budget AS b ON b.country = s.country
ORDER BY s.country;

A missing or zero budget produces a null percentage. Decide whether an unmatched country should be shown as missing data rather than interpreted as zero budget.

Filter first, then use a calendar function

Keep the source CTE free of functions so the selected fields and date filter can run at the source.

WITH source_orders AS (
  SELECT order_date, amount
  FROM bq.sales.orders
  WHERE order_date >= DATE '2026-01-01'
    AND order_date < DATE '2027-01-01'
)
SELECT MONTH(order_date) AS month,
       SUM(amount) AS revenue
FROM source_orders
GROUP BY MONTH(order_date)
ORDER BY month;

MONTH preserves the year and groups by the start of the month. This still transfers source rows for the function calculation. If a source-native aggregation can safely produce the needed result, compare its plan and cost.

Keep unmatched customers

WITH customers AS (
  SELECT id, country FROM pg.public.customers
),
orders AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM bq.sales.orders
  GROUP BY customer_id
)
SELECT c.id AS customer_id,
       c.country,
       COALESCE(o.revenue, 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY revenue DESC
LIMIT 100;

Use LEFT JOIN because the customer list is the population you want to retain. Putting a right-side condition in the outer WHERE can remove unmatched rows and effectively change that meaning.

Avoid duplicate output names

A query such as SELECT a.*, b.* can produce repeated names, including two id columns. Alias fields explicitly:

SELECT c.id AS customer_id,
       p.id AS product_id
FROM pg.public.customers AS c
CROSS JOIN bq.sales.products AS p
LIMIT 10;

This example demonstrates aliases, not an efficient production query. A cross join can create a large product, and the final limit may not reduce source transfer. Prefer a meaningful join condition for real analysis.