Week 154 - SQL & Query Techniques

Challenge

Frosty's Ice Cream Parlour is booming. What started as a single cart on the frozen lake has grown into a five-location operation across the tundra — and with growth comes chaos. Staff schedules are submitted by shift managers as raw dumps, and the data is a mess: interns listed under roles that don't exist, shifts that end before they start, hourly rates below minimum wage, and 13-hour marathon shifts that would make even a polar bear call in sick.

Until now, the application layer was supposed to catch all of this. It didn't. But Snowflake now supports CHECK constraints on standard tables — SQL expressions that are enforced on every INSERT, UPDATE, and MERGE. If a row violates the rule, the entire operation fails. No bad data gets in. Ever.

Assignment

Help the Frosty Parlour ops team build a staff schedule table with CHECK constraints that enforce four business rules, then load only the clean rows from a messy raw dump and prove that every violation was caught.

Setup Code

Setup Code (click me)

-- Frosty Friday Week 154 — Setup
-- CHECK Constraints
-- https://frostyfri.day/en/challenges/week-154

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS FROSTY_DB;
CREATE SCHEMA IF NOT EXISTS FROSTY_DB.WEEK_154;
USE SCHEMA FROSTY_DB.WEEK_154;

-- Raw staff schedule from shift managers (all VARCHAR — no type safety at all)
CREATE OR REPLACE TABLE STAFF_SCHEDULE_RAW (
    employee_id    VARCHAR,
    employee_name  VARCHAR,
    role           VARCHAR,
    hourly_rate    VARCHAR,
    shift_date     VARCHAR,
    shift_start    VARCHAR,
    shift_end      VARCHAR,
    hours_worked   VARCHAR
);

INSERT INTO STAFF_SCHEDULE_RAW VALUES
    ('E001', 'Yuki Tanaka',      'MANAGER',  '28.50', '2026-04-07', '08:00', '16:00', '8.0'),
    ('E002', 'Bjorn Frostvik',   'SCOOPER',  '16.00', '2026-04-07', '10:00', '18:00', '8.0'),
    ('E003', 'Luna Glacier',     'SCOOPER',  '15.50', '2026-04-07', '12:00', '20:00', '8.0'),
    ('E004', 'Kira Snowden',     'CASHIER',  '14.00', '2026-04-07', '09:00', '17:00', '8.0'),
    ('E005', 'Olaf Berg',        'DRIVER',   '20.00', '2026-04-07', '06:00', '14:00', '8.0'),
    ('E001', 'Yuki Tanaka',      'MANAGER',  '28.50', '2026-04-08', '08:00', '16:00', '8.0'),
    ('E002', 'Bjorn Frostvik',   'SCOOPER',  '16.00', '2026-04-08', '11:00', '19:00', '8.0'),
    ('E003', 'Luna Glacier',     'SCOOPER',  '10.00', '2026-04-08', '14:00', '22:00', '8.0'),
    ('E004', 'Kira Snowden',     'CASHIER',  '14.00', '2026-04-08', '09:00', '17:00', '8.0'),
    ('E005', 'Olaf Berg',        'DRIVER',   '20.00', '2026-04-08', '05:00', '18:00', '13.0'),
    ('E006', 'Freya Isdottir',   'SCOOPER',  '15.00', '2026-04-09', '10:00', '18:00', '8.0'),
    ('E001', 'Yuki Tanaka',      'MANAGER',  '28.50', '2026-04-09', '08:00', '16:00', '8.0'),
    ('E002', 'Bjorn Frostvik',   'SCOOPER',  '16.00', '2026-04-09', '10:00', '14:00', '4.0'),
    ('E007', 'Nils Vansen',      'INTERN',   '8.00',  '2026-04-09', '09:00', '17:00', '8.0'),
    ('E004', 'Kira Snowden',     'CASHIER',  '14.00', '2026-04-09', '09:00', '17:00', '8.0'),
    ('E005', 'Olaf Berg',        'DRIVER',   '20.00', '2026-04-09', '07:00', '15:00', '8.0'),
    ('E003', 'Luna Glacier',     'SCOOPER',  '15.50', '2026-04-10', '12:00', '20:00', '8.0'),
    ('E006', 'Freya Isdottir',   'SCOOPER',  '15.00', '2026-04-10', '10:00', '18:00', '8.0'),
    ('E001', 'Yuki Tanaka',      'MANAGER',  '28.50', '2026-04-10', '07:00', '17:00', '10.0'),
    ('E008', 'Sven Blizzard',    'SCOOPER',  '15.00', '2026-04-10', '20:00', '18:00', '-2.0');

Your Objective

Step 1: Create the constrained schedule table

Create a table called STAFF_SCHEDULE with proper column types and four named CHECK constraints that enforce these business rules:

Column	Type	Nullable	Business Rule
employee_id	VARCHAR(4)	NOT NULL	—
employee_name	VARCHAR(50)	NOT NULL	—
role	VARCHAR(10)	NOT NULL	Must be one of: MANAGER, SCOOPER, CASHIER, DRIVER
hourly_rate	NUMBER(5,2)	NOT NULL	Must be at least $12.00 (minimum wage)
shift_date	DATE	NOT NULL	—
shift_start	TIME	NOT NULL	—
shift_end	TIME	NOT NULL	Shift end must be after shift start
hours_worked	NUMBER(3,1)	NOT NULL	Must be positive and no more than 12 hours

Name your constraints: chk_valid_role, chk_minimum_wage, chk_hours_valid, chk_shift_order.

🧊 I'm Stuck — Help! (Click me)

💡 CHECK constraints can be defined inline on a column or out-of-line at the end of the column list. Out-of-line is the way to go when you want named constraints or when the expression references multiple columns (like comparing shift_end to shift_start). Use CONSTRAINT chk_name CHECK (expr) syntax.

Step 2: Load it and watch it fail

Try to load all rows from STAFF_SCHEDULE_RAW into STAFF_SCHEDULE using INSERT INTO ... SELECT with appropriate casts. Run it and observe the error.

🧊 I'm Stuck — Help! (Click me)

💡 Unlike DML Error Logging, CHECK constraints fail the entire batch on the first violation — zero rows get inserted. The error message tells you which constraint was violated. This is by design: CHECK constraints are a hard guarantee, not a soft filter.

Step 3: Filter and load only clean rows

Add a WHERE clause to your INSERT that mirrors the four CHECK constraint expressions, so only rows that satisfy all rules are inserted. Count the rows loaded.

🧊 I'm Stuck — Help! (Click me)

💡 Your WHERE clause needs to replicate every CHECK expression: role IN (...), hourly_rate >= 12.00, hours_worked > 0 AND hours_worked <= 12, and shift_end::TIME > shift_start::TIME. You should get 16 rows inserted.

Expected Output:

number of rows inserted
16

Step 4: Build the violation report

Query STAFF_SCHEDULE_RAW to find the 4 rows that violate the business rules. For each, return the EMPLOYEE_ID, EMPLOYEE_NAME, SHIFT_DATE, and a VIOLATION_REASON column that describes what went wrong. Order by EMPLOYEE_ID, then SHIFT_DATE.

🧊 I'm Stuck — Help! (Click me)

💡 Use a CASE expression to check the same conditions as your CHECK constraints, but inverted — find the rows where any rule fails. Each row only violates one rule, so the first matching CASE branch gives you the reason.

Expected Output:

EMPLOYEE_ID	EMPLOYEE_NAME	SHIFT_DATE	VIOLATION_REASON
E003	Luna Glacier	2026-04-08	Below minimum wage: $10.00
E005	Olaf Berg	2026-04-08	Exceeds 12-hour max: 13.0
E007	Nils Vansen	2026-04-09	Invalid role: INTERN
E008	Sven Blizzard	2026-04-10	Non-positive hours: -2.0

Step 5: Inspect your constraints

Query the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view to list all CHECK constraints on your table. Return the constraint name and the check clause. Order by constraint name.

🧊 I'm Stuck — Help! (Click me)

💡 The view lives at FROSTY_DB.INFORMATION_SCHEMA.CHECK_CONSTRAINTS. Filter by CONSTRAINT_SCHEMA = 'WEEK_154' and you'll see all four named constraints with their SQL expressions. This is why naming constraints matters — compare CHK_VALID_ROLE to SYS_CONSTRAINT_7b8a....

Expected Output:

CONSTRAINT_NAME	CHECK_CLAUSE
CHK_HOURS_VALID	hours_worked > 0 AND hours_worked <= data-preserve-html-node="true" 12
CHK_MINIMUM_WAGE	hourly_rate >= 12.00
CHK_SHIFT_ORDER	shift_end > shift_start
CHK_VALID_ROLE	role IN ('MANAGER', 'SCOOPER', 'CASHIER', 'DRIVER')

Step 6: Reconciliation check

Prove that every row in STAFF_SCHEDULE_RAW is accounted for — either loaded into STAFF_SCHEDULE or identified as a violation. Write a single query that returns three numbers:

• rows_loaded — count of rows in STAFF_SCHEDULE
• rows_rejected — difference between raw and loaded
• total_raw_rows — count of rows in STAFF_SCHEDULE_RAW

🧊 I'm Stuck — Help! (Click me)

💡 Use scalar subqueries: SELECT (SELECT COUNT(*) FROM STAFF_SCHEDULE) AS rows_loaded, .... The three counts should reconcile: 16 + 4 = 20.

Expected Output:

ROWS_LOADED	ROWS_REJECTED	TOTAL_RAW_ROWS
16	4	20

Hints

• CHECK constraints on standard tables are always enforced — unlike PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints which are declarative only on standard tables. This is the one constraint type where Snowflake says "no, really, enforced."
• CHECK expressions can use table columns, constants, and deterministic scalar functions. They cannot use UDFs, aggregates, window functions, subqueries, or non-deterministic functions like CURRENT_DATE or RANDOM.
• When a CHECK constraint is violated, the entire batch fails on the first violation. Zero rows are inserted. This is different from DML Error Logging (Week 152), which captures bad rows and keeps going.
• Name your constraints! An unnamed CHECK constraint gets a SYS_CONSTRAINT_<uuid> name, which is unreadable in error messages and the CHECK_CONSTRAINTS view.
• ENABLE VALIDATE (the default) enforces the constraint on all existing and new rows — but only works on new/empty tables. Use ENABLE NOVALIDATE if you need to add a CHECK constraint to an existing table that might have violations.
• CHECK constraints are not supported with COPY INTO, Snowpipe, or streaming ingestion. They're designed for DML operations (INSERT, UPDATE, MERGE, CTAS).
• Check out the CHECK constraint documentation for the full reference.

Squarespace Metadata

Category: SQL & Query Techniques Difficulty: Medium