Scenario

You work for a private equity data platform and have been given three tables containing PE firm details, fund information, and investment records. Not every firm has a fund, not every fund belongs to a firm, and not every investment is tied to a fund.

Task

Write a Snowflake SQL query that:

1. Performs a FULL OUTER JOIN between {{ ref("firms") }} and {{ ref("funds") }} on firm_id
2. Performs a FULL OUTER JOIN between the result and {{ ref("investments") }} on fund_id
3. Uses COALESCE to produce a single firm_id (from firms or funds) and a single fund_id (from funds or investments)
4. Selects all 13 columns listed in the output schema
5. Filters out any rows where every column is NULL

Schema

firms

funds

investments

Expected Output Schema

Example

firms:

funds:

investments:

Expected Output:

Note: Crest Ventures (firm_id=3) has no associated funds. Orphan Fund (firm_id=99) has no matching firm and no investments. SoloVenture (fund_id=88) has no matching fund. All three rows are preserved because they are not entirely NULL. Any row where every column is NULL would be excluded.

WITH combined AS (
    SELECT
        i.investment_id,
        COALESCE(fd.fund_id, i.fund_id) AS fund_id,
        COALESCE(f.firm_id, fd.firm_id) AS firm_id,
        f.firm_name,
        f.founded_year,
        f.location,
        fd.fund_name,
        fd.fund_size,
        fd.fund_start_year,
        fd.fund_end_year,
        i.company_name,
        i.investment_amount,
        i.investment_date
    FROM {{ ref("firms") }} f
    FULL OUTER JOIN {{ ref("funds") }} fd
        ON f.firm_id = fd.firm_id
    FULL OUTER JOIN {{ ref("investments") }} i
        ON fd.fund_id = i.fund_id
)
SELECT *
FROM combined
WHERE NOT (
    investment_id IS NULL
    AND fund_id IS NULL
    AND firm_id IS NULL
    AND firm_name IS NULL
    AND founded_year IS NULL
    AND location IS NULL
    AND fund_name IS NULL
    AND fund_size IS NULL
    AND fund_start_year IS NULL
    AND fund_end_year IS NULL
    AND company_name IS NULL
    AND investment_amount IS NULL
    AND investment_date IS NULL
)

Explanation

Step 1: FULL OUTER JOIN to Combine Tables

FROM {{ ref("firms") }} f
FULL OUTER JOIN {{ ref("funds") }} fd
    ON f.firm_id = fd.firm_id
FULL OUTER JOIN {{ ref("investments") }} i
    ON fd.fund_id = i.fund_id

A FULL OUTER JOIN preserves all rows from both sides of the join. If a firm has no matching fund, the firm row still appears with NULL fund columns. If a fund has no matching firm, the fund row still appears with NULL firm columns. The same logic applies to the second join between funds and investments. This is the key difference from a LEFT JOIN, which only preserves unmatched rows from the left table.

Step 2: COALESCE for Shared Join Keys

COALESCE(fd.fund_id, i.fund_id) AS fund_id,
COALESCE(f.firm_id, fd.firm_id) AS firm_id,

When two tables share a column name used as a join key, a FULL OUTER JOIN can produce NULLs on either side. COALESCE picks the first non-NULL value. For example, an investment with no matching fund will have fd.fund_id as NULL, so COALESCE falls through to i.fund_id. Similarly, a fund with no matching firm will have f.firm_id as NULL, so COALESCE falls through to fd.firm_id.

Step 3: Filtering Out All-NULL Rows

WHERE NOT (
    investment_id IS NULL
    AND fund_id IS NULL
    AND firm_id IS NULL
    ...
)

A FULL OUTER JOIN across three tables can produce rows where every column is NULL (when unmatched NULLs cascade). The WHERE NOT (... IS NULL AND ... IS NULL AND ...) pattern keeps only rows that have at least one non-NULL value. In practice, a simpler alternative like WHERE COALESCE(f.firm_id, fd.fund_id, i.investment_id) IS NOT NULL may also work if those anchor columns are always populated for valid records.