Diagram Features

Save Diagram State

Saving diagram positions after rearrangement. And the positions are synced across your team in the same workspace.

Optimize

Optimize/refactor query for better metadata. E.g.,

• Qualified column names from select * from my_cte;

• Detect ambiguous references

-- e.g.,
SELECT a, b FROM foo
LEFT JOIN bar ON foo.a = bar.a;

References should be qualified if select has more than one referenced table/view. Learn more: references.qualification

1. Qualified column names

Qualified column names from 'SELECT *'

WITH monthly_sales AS (
    SELECT
      DATE_TRUNC('month', o.order_date) AS month,
      c.customer_id,
      c.customer_name,
      p.product_name,
      SUM(oi.quantity * oi.unit_price) AS revenue,
      ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', o.order_date), c.customer_id ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS revenue_rank
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE
      o.order_date >= '2024-01-01'
      AND o.order_date <= '2024-12-31'
    GROUP BY
      DATE_TRUNC('month', o.order_date),
      c.customer_id,
      c.customer_name,
      p.product_name
  ),
top_sales AS (
    SELECT
      ms.*    -- 👈 Qualified column names from 'SELECT *'
    FROM
      monthly_sales ms
    WHERE
      revenue_rank <= 3
  )
SELECT * FROM top_sales;

In the background, this helps map from ms.* to ms.customer_id, ms.revenue,...and so on.

2. Refactor subqueries to CTEs for easier table lineage tracking

For example:

SELECT
    b.borrowing_id,
    b.book_id,
    b.borrower_id,
    bg.genre_name,
    br.borrower_name
FROM borrowings AS b
LEFT JOIN (
    SELECT DISTINCT
        bk.book_id AS book_id,
        g.genre_name AS genre_name
    FROM books AS bk
    JOIN genres AS g ON bk.genre_id = g.genre_id
) AS bg
    ON b.book_id = bg.book_id
LEFT JOIN borrowers AS br
    ON b.borrower_id = br.borrower_id
ORDER BY b.borrowing_id;

This above subquery will be refactored to: