Week 140 – Intermediate

Your company recently migrated a set of invoice records from multiple vendors into Snowflake. Each line item includes a free-text description of services provided — but there’s no consistent naming or structure across entries. To support financial reporting and vendor analysis, your team needs to classify each service description into a standard list of categories maintained by Finance.

Manually tagging hundreds of rows isn’t scalable. Luckily, you’ve just discovered Snowflake Cortex, which lets you run LLM-powered classification directly in SQL using the CLASSIFY_TEXT() function.

Your challenge: write a query that automatically assigns each service description to its most appropriate category.

Run the statements below to create the required tables and sample rows:

-- Create invoice_line_items table
CREATE OR REPLACE TABLE invoice_line_items (
    line_item_id INTEGER,
    service_description STRING
);

-- Create service_categories table
CREATE OR REPLACE TABLE service_categories (
    category_id INTEGER,
    category_name STRING
);

-- Insert sample invoice line items
INSERT INTO invoice_line_items (line_item_id, service_description) VALUES
(1, 'Deployment of Snowflake project - Phase 1'),
(2, 'Data ingestion pipeline optimization'),
(3, 'Security and access review for Snowflake'),
(4, 'Ongoing data modeling support'),
(5, 'Snowflake training session for analysts');

-- Insert sample categories
INSERT INTO service_categories (category_id, category_name) VALUES
(1, 'Snowflake Deployment'),
(2, 'Data Engineering'),
(3, 'Security Review'),
(4, 'Training'),
(5, 'Analytics Support');

Hint: Use a CTE to aggregate the category names into an array using ARRAY_AGG(), then cross join it into the invoice data so each row has access to the full list of options for classification.

P.s. does it look familiar? In that case, it was nice to meet you at #SnowflakeSummit2025 in San Francisco 🙂