Diagram Features

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:

Diagram Design

Watch the video: https://youtu.be/dZrzov7KdJ0

1. Crow's foot notation

| is mandatory, O is optional.

2. Table colors

Table color setting is applied globally (ensures consistency across your diagrams)

3. Swap edge direction

Easily change the direction of relationships

4. Highlight edge relations

When hovering over any keys, we highlight the related columns for that specific key.

---

Auto rearrange

Auto-rearrange diagram feature dynamically adjusts and optimizes the layout of complex lineage diagrams

We use topological sorting to figure out the optimal tables ordering in layers, based on their dependencies.

Credit: algodaily

---

Save state on cloud

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