Scenario

You are helping an archaeological team manage their artifact inventory stored in a single table.

Task

Write a Snowflake SQL query that:

1. Selects all columns from {{ ref("artifacts") }}
2. Converts the Material column to uppercase using UPPER
3. Filters to only include rows where Quantity is strictly greater than 100
4. Returns columns: ID, Item, Period, Material, Quantity

Schema

artifacts

Example

artifacts:

Expected Output:

Note: Row 2 is excluded (Quantity 75 is not greater than 100). Row 4 is excluded (Quantity 100 is not strictly greater than 100). Material values are converted to uppercase.

SELECT
    ID,
    Item,
    Period,
    UPPER(Material) AS Material,
    Quantity
FROM {{ ref("artifacts") }}
WHERE Quantity > 100

Explanation

Step 1: Converting a column to uppercase

UPPER(Material) AS Material

The UPPER function converts every character in a string to uppercase. Aliasing the result AS Material keeps the output column name the same as the original, so the schema stays consistent.

Step 2: Filtering rows with WHERE

WHERE Quantity > 100

The WHERE clause removes rows before they appear in the result set. The strict greater-than operator (>) excludes rows where Quantity equals exactly 100. If you needed 100 to be included, you would use >= instead.

Step 3: Alternative using a CTE

WITH filtered AS (
    SELECT
        ID,
        Item,
        Period,
        UPPER(Material) AS Material,
        Quantity
    FROM {{ ref("artifacts") }}
    WHERE Quantity > 100
)
SELECT * FROM filtered

Wrapping the logic in a CTE (Common Table Expression) produces the same result. CTEs become more useful when you need to chain multiple transformation steps together.