Introduction to Snowflake SQL
George Boorman
Senior Curriculum Manager, DataCamp
❌ Incorrect ❌
SELECT *
FROM order_details AS od
JOIN pizzas AS p -- Missing ON condition leading to exploding joins
✅ Correct ✅
SELECT *
FROM order_details AS od
JOIN pizzas AS p
ON od.pizza_id = p.pizza_id
UNION
or UNION ALL
: Know the differenceUNION
removes duplicates, slows down the queryUNION ALL
is faster if no duplicatesSELECT
*
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
SELECT *
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
LIMIT 10
SELECT *
⌛SELECT
*
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
SELECT *
⚡SELECT o_orderdate,
o_orderstatus
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
WHERE
Clause Early On JOIN
sJOIN
will process fewer rowsSELECT orders.order_id,
orders.order_date,
pizza_type.name,
pizzas.pizza_size
FROM orders
JOIN order_details
ON orders.order_id = order_details.order_id
JOIN pizzas
ON order_details.pizza_id = pizzas.pizza_id
JOIN pizza_type
ON pizzas.pizza_type_id = pizza_type.pizza_type_id
WHERE orders.order_date = '2015-01-01'; -- Filtering after JOIN
WITH filtered_orders AS (
SELECT *
FROM orders
WHERE order_date = '2015-01-01' -- Filtering in CTE before JOIN
)
SELECT filtered_orders.order_id,
filtered_orders.order_date,
pizza_type.name,
pizzas.pizza_size
FROM filtered_orders -- Joining with CTE
JOIN order_details
ON filtered_orders.order_id = order_details.order_id
JOIN pizzas
ON order_details.pizza_id = pizzas.pizza_id
JOIN pizza_type
ON pizzas.pizza_type_id = pizza_type.pizza_type_id;
snowflake.account_usage.query_history
SELECT query_text, start_time, end_time, execution_time
FROM
snowflake.account_usage.query_history
WHERE query_text ILIKE '%order_details%'
ILIKE
: Case-insensitive string-matchingSELECT query_text,
start_time,
end_time,
execution_time
FROM
snowflake.account_usage.query_history
WHERE
execution_time > 1000
Introduction to Snowflake SQL