Scenario

A bank stores transaction records and customer profiles in separate tables and needs every possible pairing of the two for a coverage analysis.

Task

Write a Snowflake SQL query that:

1. Performs a CROSS JOIN between {{ ref("transactions") }} and {{ ref("customers") }}, producing every combination of rows
2. Uses cust_id from the customers table (not from transactions) in the output
3. Returns all 7 columns: trans_id, trans_amt, date, cust_id, first_name, last_name, age

Schema

transactions

customers

Example

transactions:

customers:

Expected Output (9 rows):

Note: Every transaction is paired with every customer, producing 3 x 3 = 9 rows. The cust_id in the output comes from the customers table, not transactions.

SELECT
    t.trans_id,
    t.trans_amt,
    t.date,
    c.cust_id,
    c.first_name,
    c.last_name,
    c.age
FROM {{ ref("transactions") }} t
CROSS JOIN {{ ref("customers") }} c

Explanation

Step 1: CROSS JOIN produces a Cartesian product

FROM {{ ref("transactions") }} t
CROSS JOIN {{ ref("customers") }} c

A CROSS JOIN pairs every row from the left table with every row from the right table. Unlike INNER JOIN or LEFT JOIN, there is no ON condition. If transactions has M rows and customers has N rows, the result contains M x N rows.

Step 2: Resolving the ambiguous cust_id column

SELECT
    t.trans_id,
    t.trans_amt,
    t.date,
    c.cust_id,
    ...

Both transactions and customers contain a cust_id column. Using c.cust_id (from the customers table) ensures the output reflects the customer side of the pairing rather than the transaction side. Table aliases (t and c) make this distinction clear.

Step 3: Alternative with a CTE

WITH crossed AS (
    SELECT
        t.trans_id,
        t.trans_amt,
        t.date,
        c.cust_id,
        c.first_name,
        c.last_name,
        c.age
    FROM {{ ref("transactions") }} t
    CROSS JOIN {{ ref("customers") }} c
)
SELECT * FROM crossed

Wrapping the cross join in a CTE produces identical results and can improve readability when the query is part of a larger pipeline with additional transformations.