Jump to: Key Takeaways

Jump to: How to Stop Fanout

The Pain Point

I start with payments table -- 3 orders, 6 rows, total revenue $313.15.

One innocent LEFT JOIN later, the same 3 orders have turned into 14 rows, and my revenue has more than doubled.

Replay

Here I show Payments Table in its entirety. Each order has two different entries, reflecting a difference in payment method as well as amount. There are no duplicate rows in this table because each entry is uniquely identified using order_id and payment_sequential as a pair, forming a composite key.

Order Items Table, on the other hand, uses (order_id, order_item_id) as a composite key to uniquely identify each item in a shopper's basket. This stems from the fact that an order can have multiple items. Order ed4c, for example, contains 3 different items from the same seller.

Performing a simple LEFT JOIN:

    SELECT *
    FROM payments p
    LEFT JOIN order_items i ON p.order_id=i.order_id

Now the joined table has matched every payment row to every item row within the same order! To see this more clearly, order ed4c has ballooned into 3 entries per payment_sequential.

So in a way, my JOIN query fanned out because SQL did exactly what I asked: it matched every single order_id from one table with order_id from another table. Only, the two tables weren't zoomed in to the same level of detail (granularity). Payments Table recorded each payment transaction, while Order Items Table focused on each item. It’s the analyst’s job to make sure the JOIN is properly designed.

A Properly Designed JOIN

I have three big takeaways. First and foremost, check a table's PRIMARY KEY if that information is already available, and pay attention to tables with a composite key. In my example, joining two composite-key tables on a single column created a many-to-many fanout, and so it is good data hygiene to check if a column called "id" is truly unique to each row.

Oftentimes, however, our data comes from .csv or .xlsx files that do not carry relational constraints information the way a database system would. A quick and dirty way to get that information is by comparing a count of rows against a count of unique id. A matching count indicates that that table should be safe in a JOIN.

    -- Check key uniqueness before JOIN
    
    SELECT
      COUNT(*) AS row_count,
      COUNT(DISTINCT id) AS id_count
    FROM table

Secondly, financial and transactional metrics deserve extra scrutiny. Most JOINs work exactly as intended, but when a joined table feeds revenue, order counts, or other business metrics, a safer choice can simply be aggregating before joining. This step ensures each row is unique to join key.

    -- Aggregate financial metrics before JOIN
    
    SELECT
    	p.order_id,
    	SUM(p.payment_value) AS order_payment_value
    FROM payments p
    GROUP BY p.order_id

Third, watch JOIN direction because many-to-many is not the only failure mode. Suppose I use the code above to join an aggregated Payments Table with Order Items Table? Fanout persists because it is still a one-to-many relationship.

How to Stop Fanout

A one-to-one relationship guarantees fanout won't happen, but sometimes that is hard to achieve. Take Order Items Table as an example, an analyst would need to make table design choices to aggregate the information to order level. Other times a fanout does not change query results at all and mechanically ensuring a one-to-one relationship does not deliver real benefit.

    -- When fanout does not change query results
    
    SELECT
    	DATE_TRUNC('MONTH', o.order_purchase_timestamp) AS order_month,
    	COUNT(DISTINCT i.seller_id) AS seller_ct
    FROM orders o
    JOIN order_items i ON o.order_id=i.order_id
    GROUP BY order_month

To see the tradeoff, consider the query above. Although the one-to-many relationship made rows multiply, DISTINCT function removed all fanout duplicates and I correctly counted one seller per month. In fact, when spread across roughly 100k orders, fanout inflated my total revenue by ~28%.

A Double-Edged Sword

After this personal run-in with fanout, I had a lot of appreciation for a purposefully designed SQL JOIN. JOINs can comfortably bring together millions of rows, but they can also hide mistakes in plain sight. The final screenshot I would like to share with you contains the same problematic JOIN in its unredacted form. Buried under a wall of UUIDs is a classic, well-identified problem. But because of the sheer size of data, debugging starts to look daunting.

Now imagine fanout in a real database.

Appendix

Based on Olist E-commerce Dataset. See my full analysis here