Scenario

You work as a data analyst for a flooring company and have three tables containing customer, order, and product information.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("orders") }} to {{ ref("customers") }} on customer_id and to {{ ref("products") }} on product_id using inner joins
2. Splits the full_name column (space-separated) into first_name and last_name
3. Splits the product_info column (comma-separated) into product_type and product_color
4. Returns these 9 columns: order_id, customer_id, first_name, last_name, location, product_id, product_type, product_color, quantity

Schema

customers

orders

products

Example

customers:

orders:

products:

Expected Output:

Note: full_name is split on the space into first_name and last_name. product_info is split on the comma into product_type and product_color. Only orders with matching customers and products appear in the output.

SELECT
    o.order_id,
    o.customer_id,
    SPLIT_PART(c.full_name, ' ', 1) AS first_name,
    SPLIT_PART(c.full_name, ' ', 2) AS last_name,
    c.location,
    o.product_id,
    SPLIT_PART(p.product_info, ',', 1) AS product_type,
    SPLIT_PART(p.product_info, ',', 2) AS product_color,
    o.quantity
FROM {{ ref("orders") }} AS o
INNER JOIN {{ ref("customers") }} AS c
    ON o.customer_id = c.customer_id
INNER JOIN {{ ref("products") }} AS p
    ON o.product_id = p.product_id

Explanation

Step 1: Joining three tables with INNER JOIN

FROM {{ ref("orders") }} AS o
INNER JOIN {{ ref("customers") }} AS c
    ON o.customer_id = c.customer_id
INNER JOIN {{ ref("products") }} AS p
    ON o.product_id = p.product_id

Start from the orders table and chain two inner joins. The first join brings in customer details by matching customer_id. The second join brings in product details by matching product_id. Only orders that have both a valid customer and a valid product will appear in the result.

Step 2: Splitting full_name with SPLIT_PART

SPLIT_PART(c.full_name, ' ', 1) AS first_name,
SPLIT_PART(c.full_name, ' ', 2) AS last_name

SPLIT_PART(string, delimiter, part_number) splits a string on the given delimiter and returns the specified part. Snowflake uses 1-based indexing, so part 1 is the text before the first space (first name) and part 2 is the text after it (last name). This only works correctly when names contain exactly one space.

Step 3: Splitting product_info with SPLIT_PART

SPLIT_PART(p.product_info, ',', 1) AS product_type,
SPLIT_PART(p.product_info, ',', 2) AS product_color

The same technique applies to product_info, using a comma as the delimiter. Part 1 extracts the product type and part 2 extracts the color. If the original column were more complex (multiple commas), you could continue with part 3, 4, etc.

Step 4: CTE alternative

WITH joined AS (
    SELECT
        o.order_id,
        o.customer_id,
        SPLIT_PART(c.full_name, ' ', 1) AS first_name,
        SPLIT_PART(c.full_name, ' ', 2) AS last_name,
        c.location,
        o.product_id,
        SPLIT_PART(p.product_info, ',', 1) AS product_type,
        SPLIT_PART(p.product_info, ',', 2) AS product_color,
        o.quantity
    FROM {{ ref("orders") }} AS o
    INNER JOIN {{ ref("customers") }} AS c
        ON o.customer_id = c.customer_id
    INNER JOIN {{ ref("products") }} AS p
        ON o.product_id = p.product_id
)
SELECT * FROM joined

Wrapping the logic in a CTE produces the same result and can improve readability when additional transformation steps are needed downstream.