Pointing an AI agent at your data is easy. Getting an answer you can trust is not, because real data arrives in different formats, uses different names for the same thing, and sometimes contradicts itself.

In this lab you're the data engineer for Northwind Outfitters, an online store that sells shoes, clothing, accessories, and home goods in the US, Canada, and Europe. Leadership asks:

You'll start by asking an agent that question over the raw data, and you'll get a quick, confident answer. Then a colleague's reply will make you doubt it, and you'll use Snowflake CoCo to find out why it's wrong and fix what "a return" means, use AI_TRANSLATE and AI_CLASSIFY to turn multilingual customer comments into something you can count, and build a Cortex Search service over those comments. Finally, in Snowsight, you'll define your business terms in a semantic view, give an agent both the numbers (Cortex Analyst) and the customers' own words (Cortex Search), and ask in Snowflake CoWork to get the real answer, with charts and customer quotes. To finish, the agent turns that answer into a polished HTML report you can share.

The data is 2,000 order lines: big enough that you can't eyeball the answer, small enough that every step runs in seconds. The same steps work unchanged on millions of rows.

You'll see a lot of SQL. You don't need to understand every line: CoCo writes it, and each step ends with a check that tells you whether the numbers are right.

There are no setup scripts to run first. Your first action is to create a Git-backed Workspace that clones the lab repo. It contains the notebook you'll run (lab.ipynb) and the three data files (data/).

1. In Snowsight, navigate to Projects » Workspaces.

2. Click the + icon next to Workspaces/Databases → Create new Git workspace.

   

3. Fill in:

   • Repository URL: https://github.com/Snowflake-Labs/sfguide-messy-data-to-instant-answers
   • Workspace name: messy-data-to-answers
   • API integration: click + / Create new and provide:
     • Name: GITHUB_RETURNS_LAB
     • Allowed prefixes: https://github.com/Snowflake-Labs
   • Check Public repository.

4. Click Create.

The Workspace opens with the repo's files in the file explorer on the left. Check that you see lab.ipynb and a data/ folder with three files.

From here on, each step matches a step in the notebook, with the same number and title. The notebook has the same instructions, so you can follow along from either one.

You created this workspace from the lab's GitHub repo in the guide. You'll work in this notebook through STEP 7, then finish the lab in Snowsight.

1. Open lab.ipynb from the file explorer on the left, if it isn't open already.

2. Click Connect next to the Run button and select Create and connect.

   

3. Wait for the status bar at the bottom of the notebook to show Connected. The first connection can take a few minutes while Snowflake starts the notebook's compute; read the rest of this step and STEP 2 while you wait. If it's still stuck (for example at 99%) after about 5 minutes, refresh the browser tab.

   

Run each cell when the instructions reach it, with Run on the cell or Shift+Enter. Every object in this lab uses a fully qualified name, so you don't need to pick a database or schema.

Now run the setup cell. It creates the lab database ONLINE_RETURNS with three schemas and a stage:

Before you can fix the data, you need it in Snowflake exactly as the source systems sent it. Keeping an untouched copy means you can always check a cleaned number against the original.

The repo's data/ folder has three files:

Run the upload_files cell. It finds each file in your workspace and copies it, unchanged, to the stage ONLINE_RETURNS.RAW.LAB_FILES. You should see three Uploaded lines.

Now run the load_raw cell. It creates three raw tables and copies each file from the stage into one of them:

The last query counts what landed. Expect 2,000 order lines, 1,900 return events, and 775 comments, matching the file descriptions above.

The fastest thing you could do is point an AI agent straight at the order export and ask. Run the quick_agent cell. It creates two objects:

This is a first attempt on purpose. In STEP 8 you'll build a better semantic view, and in STEP 9 you'll edit this same agent to use it.

Run the quick_ask cell. It sends the agent this question and prints the answer:

"Which product categories have the highest and lowest return rate?"

You should get an answer like: Apparel has the highest return rate, at about 45%, and Footwear the lowest, at about 14%. The agent may even merge the messy category spellings for you. It sounds confident, and it's a clean, simple answer.

Most people would take that answer and move on. But you share it with the team, and the EMEA warehouse lead replies:

"That can't be right. Shoes are most of what comes back to us. And since January, shoe returns go through our new returns partner, and their refunds don't sync back to the store system."

So the agent says Footwear has the fewest returns, and the people handling returns say it has the most. Both can't be true. The agent counted what the store's order export says; the warehouse lead is talking about returns that actually happened. Those are recorded in a different place: the return events. In the next step you'll compare the two.

To find out which answer is right, you'll use CoCo.

CoCo (Cortex Code) is Snowflake's AI assistant, built into Snowsight and Workspaces. You tell it what you want in natural language, and it works out the steps: it finds the right tables, writes SQL or Python, runs it, and explains what it found. It can also fix a query that fails. You'll use it to investigate here, and to build tables and a search service in STEPs 5–7.

How to use it

Why use it here

Comparing two records means exploring unfamiliar tables, matching up different spellings, and writing joins. CoCo does that legwork in a minute, so you can spend your time deciding whether the answer makes sense.

Why you can trust it

Note: CoCo and the agents phrase things differently every time. Don't compare wording; compare the numbers.

Click on the blue CoCo icon and send this prompt. It includes what the warehouse lead told you, so CoCo knows what to look for:

CoCo prompt: "The warehouse lead says shoe returns moved to a new returns partner in January, and their refunds don't sync back to the store system. Check this. Compare ONLINE_RETURNS.RAW.ORDERS with the return events in ONLINE_RETURNS.RAW.RETURN_EVENTS. For each product category, show reported_returns (lines whose order_status says returned) and completed_returns (lines whose latest return event is a refund or an exchange). Group the different category spellings together. Don't change any data."

What this prompt asks for

The two columns come from two different places:

Expected result:

The warehouse lead was right. The export reports 75 Footwear returns, but 200 were completed. That's why the quick agent called Footwear the best category. In every other category, the export reports more returns than were completed.

Run the peek_raw cell. It shows two order lines next to the full history of their return events.

Things to notice:

The quick agent wasn't wrong because the AI was bad. It was wrong because it counted the wrong thing: the export's order_status is not a reliable record of returns. To get an answer you can trust, you'll:

1. Agree on what "a return" means, and build that rule into a clean table (STEP 5).
2. Turn the customer comments into reasons you can count (STEP 6).
3. Give the agent the clean data and the comments (STEPs 7–9), then ask again (STEP 10).

The rule for this lab:

Click on the blue CoCo icon and send this prompt. Compare CoCo's SQL with the expected output below. If it does the same thing, click Allow to run it. It doesn't have to match word for word, but the table and column names must match, because later cells use them.

CoCo prompt: "Create ONLINE_RETURNS.CURATED.ORDER_LINE_RETURNS with one row per order line from ONLINE_RETURNS.RAW.ORDERS. Map category to Footwear, Apparel, Accessories, or Home, and region to US East, US West, Canada, or EMEA. Convert item_price to a number and order_date to a date column named order_dt. Using each line's latest event in ONLINE_RETURNS.RAW.RETURN_EVENTS, add latest_return_status (requested, received, refunded, exchanged, or rejected), is_returned (true only for refunded or exchanged), and is_delivered (true when the export says delivered or returned)."

What this prompt asks for

Run the check1 cell. Each number comes from somewhere you can trace:

If a number is off, send CoCo: "returned_lines in ORDER_LINE_RETURNS came out as [your number] instead of 500. Find out why and fix it." Then run the check again.

The numbers now say where returns happen. The comments say why, but a third of them aren't in English, and free text can't be counted. Two AI functions fix that. They run inside a normal SQL query, one call per row.

AI_TRANSLATE(text, source_language, target_language) returns the text in the target language. Passing '' as the source language tells it to detect the language itself, so AI_TRANSLATE(comment, '', 'en') turns German, French, or Spanish into English and leaves English as it is.

AI_CLASSIFY(text, categories, config) picks the category that fits the text best:

Because the model can only answer with one of your labels, every comment ends up in a bucket you can count. Snowflake recommends English input for AI_CLASSIFY, so the lab translates first and classifies comment_english.

AI functions are billed by the tokens they read and write. The comment, and the category list with its descriptions, all count, so 775 short comments cost very little.

Run the ai_try cell. It takes one German comment from the lab data, translates it, and then classifies the English text into one of three labels.

Expected result:

AI_TRANSLATE detected German because the source language is ''. AI_CLASSIFY could only answer with one of the three labels, and it picked the one whose description matched. The English wording may differ slightly; the label is what to check.

Next, CoCo applies the same two functions to all 775 comments, with the full list of six labels.

Click on the blue CoCo icon and send this prompt:

CoCo prompt: "Create ONLINE_RETURNS.CURATED.RETURN_COMMENT_INSIGHTS from the comments in ONLINE_RETURNS.RAW.RETURN_COMMENTS. Keep order_line_id and the original comment, translate it to English with AI_TRANSLATE into comment_english, then use AI_CLASSIFY on comment_english to set return_reason to one of these labels, each with a description: Wrong size (the item does not fit the customer: too big, too small, too tight, too loose, or too long or too short, for example sleeves or legs); Damaged or defective (the item arrived broken, scratched, or failed after little use); Not as described (the color, material, look, or listed dimensions do not match the listing or photos; not about how the item fits the customer); Arrived late (the item arrived too late to be useful); Changed mind (nothing is wrong with the item; the customer no longer wants it); Other (no return reason given, such as a question about refund status). Then create ONLINE_RETURNS.CURATED.RETURN_REVIEW: every row of ORDER_LINE_RETURNS plus comment, comment_english, and return_reason."

What this prompt asks for

Check that CoCo's SQL translates first and classifies the translation. The query runs AI on 775 comments, so it takes longer than a normal query (about 15–30 seconds).

The warehouse lead pointed at shoes in EMEA, so look there. Run the review_labels cell. It shows the first 10 comments on returned Footwear lines in EMEA, with each translation and label.

Every comment is in German, French, or Spanish. You should see 7 about size ("Die Schuhe fallen eine ganze Nummer zu klein aus.") tagged Wrong size, and 3 about soles coming off tagged Damaged or defective.

Read each translation and decide whether you agree with its label:

Here's what the AI bought you. Run the before_after cell. It answers one question two ways for the 100 returned Footwear lines in EMEA: how many are about size?

Expected result:

The keyword search finds nothing, because every comment is written in another language. The AI labels show that three out of four Footwear returns in EMEA are about size.

If these numbers are wrong, an agent will only give you the wrong answer faster. Run the check2 cell.

Expect exactly 175. If it's lower, break the count down by category and region: every combination should be a multiple of 25, so the one that's short tells you which comments got the wrong label.

If a number is off, send CoCo: "wrong_size_returns in RETURN_REVIEW came out as [your number] instead of 175. Find out why and fix it." Then run the check again.

An agent needs two kinds of help. For numbers, it writes SQL. For "show me what customers said", it needs to find the right comments, and a customer who says "Me quedan pequeñas" never typed the English word "small".

Cortex Search is a search service you build over a text column. It combines meaning-based (vector) search with keyword search, then reranks the results, so a search for "shoes too small" finds comments that say the same thing in other words. You choose:

Attributes don't change which comments match a query. They let a person or an agent narrow the search first, such as region = EMEA or latest_return_status = refunded, and then rank what's left.

Snowflake keeps the service in sync with the table, refreshing on the schedule you set with TARGET_LAG.

Click on the blue CoCo icon and send this prompt:

CoCo prompt: "Create Cortex Search service ONLINE_RETURNS.ANALYTICS.RETURN_COMMENTS_SEARCH over ONLINE_RETURNS.CURATED.RETURN_REVIEW, only for rows that have a comment. The searchable column is called comment_text: the original comment, followed by its English translation in parentheses when the comment is not already in English. Also include order_line_id. Use category, region, return_reason, and latest_return_status as attributes. Use warehouse COMPUTE_WH, a target lag of 1 day, and AUTO_SUSPEND = 1800."

What this prompt asks for

The service takes a minute or so to build.

Run the check_search cell. It searches for "shoes too small" and returns the top 20 matches. Expect size complaints about shoes, with the original comment in German, French, or Spanish followed by its English translation in parentheses. Read the English part of each result: they should all be about shoes that don't fit. The customers wrote in German, French, or Spanish, and the search found them anyway, because each comment carries its English translation and Cortex Search matches by meaning as well as by words.

Many comments open with the same sentence in their language, so several results will look almost identical. That's expected. Check that the matches are about shoes that don't fit, not which one comes first.

If the results aren't shoe size complaints, send CoCo: "Searching RETURN_COMMENTS_SEARCH for 'shoes too small' doesn't return shoe size complaints. Find out why and fix it." Then run the check again.

You've finished the notebook work: clean order lines, AI-tagged comments, and a search service. The rest of the lab happens in Snowsight, where you'll give it to an agent. You won't need to come back to this notebook until clean-up.

The steps continue below and in the guide; follow along from either.

Cortex Analyst is Snowflake's text-to-SQL engine: it turns a question into SQL and runs it. To write correct SQL, it needs to know what your columns mean, and that's what a semantic view holds:

The quick agent's semantic view defined a return the wrong way. This one uses the rule from STEP 5.

1. In Snowsight, navigate to AI & ML » Analyst.

   

2. Click Create in Workspaces in the top right.

3. Click Create with CoCo. Snowsight opens a new .sv.yaml file, and the CoCo panel asks what to build.

4. Send CoCo this prompt:

   CoCo prompt: "Create semantic view RETURNS_SV in ONLINE_RETURNS.ANALYTICS over ONLINE_RETURNS.CURATED.RETURN_REVIEW, with order_line_id as the primary key. Dimensions: category, region, return_reason, latest_return_status, is_delivered, is_returned. Metrics: delivered_lines (count where is_delivered), returned_lines (count where is_returned), returned_value (sum of item_price where is_returned), and return_rate (returned_lines divided by delivered_lines). Add verified queries for: return rate by category and region; overall return rate and returned value; and why customers return items, by category, region, and return_reason."

   What this prompt asks for

   Part of the prompt	Why it's there
   "over RETURN_REVIEW, with order_line_id as the primary key"	Names the one table to describe and what makes each row unique
   "Dimensions: …"	What people can group and filter by
   "Metrics: … (count where is_delivered) …"	Each calculation, spelled out once, so Analyst never has to guess what "return rate" means
   "Add verified queries for: …"	The questions you expect most, so Analyst has checked examples to follow

   

5. Allow CoCo to create the draft. This creates an editable draft; it doesn't publish the view yet.

6. Review the draft. Confirm it contains:

   • Dimensions: CATEGORY, REGION, RETURN_REASON, LATEST_RETURN_STATUS, IS_DELIVERED, IS_RETURNED

     

   • Metrics: DELIVERED_LINES, RETURNED_LINES, RETURNED_VALUE, RETURN_RATE. Open RETURN_RATE and check that it divides returned lines by delivered lines, not all lines.

     

   • Verified queries: three, one for each question in the prompt

   CoCo may add descriptions or synonyms. That's fine. If a metric is missing or defined differently, ask CoCo to fix it before publishing ("return_rate should divide returned_lines by delivered_lines").

7. Click Publish in the top right. Confirm Name RETURNS_SV, Database ONLINE_RETURNS, and Schema ANALYTICS, then click Publish.

   

8. Test it. After publishing, open the Playground and ask "How many lines were delivered and how many were returned, what is the return rate, and how much revenue was returned?" Expect 500 returned of 1,800 delivered lines, a return rate of 0.2778 (27.8%), and $46,958.00, the same numbers as check1.

   

   Click the SQL to see the query Analyst wrote. It won't look like the expected definition below, and it may look odd (for example, a NULLIF inside another NULLIF). That's fine: Analyst builds SQL from the metric definitions. Judge it by the numbers, and check that the rate divides by delivered lines.

An agent reads a question, decides which tool can answer it, calls the tool, and writes the answer. The RETURNS_AGENT you created in STEP 3 has one tool, pointed at the quick semantic view. You'll edit it so it has two tools and clear instructions.

1. In Snowsight, navigate to AI & ML » Agent Studio.

2. Select RETURNS_AGENT (database ONLINE_RETURNS, schema ANALYTICS).

   

3. Click Configuration.

4. Under General, set what people see in CoWork before they ask anything. The description tells them what the agent knows about, and the example questions appear as one-click starters.

   • Description: Answers questions about Northwind Outfitters returns: return rates and returned value by category and region, and what customers said about why they returned items.
   • Example questions: add these as two separate questions:
     • Which product categories have the highest and lowest return rate? Show it as a chart.
     • Why are customers returning footwear in EMEA? Show me what they said.
   

5. Under Instructions, tell the agent how to work, in plain English. Instructions guide which tool to use: Analyst for numbers, Search for what customers said. Response instructions guide what the answer looks like: which definitions to use, how precise to be, and how to quote customers. The definition of a return appears here as well as in the semantic view, so even a free-text answer uses it. Paste:

   • Instructions: Use returns_analytics for any count, rate, value, or ranking question. Use customer_comments to find what customers said, and quote them when you explain a reason.
   • Response instructions: A return is an order line whose latest return status is refunded or exchanged. Return rate is returned lines divided by delivered lines. State counts, percentages, and USD values precisely. When you search customer_comments for quotes, filter to latest_return_status refunded or exchanged. Quote the comment text exactly as the search returns it: it holds the customer's original wording, followed by the English translation in parentheses when the original is not in English. Start with the answer and answer only the question asked.
   

The agent doesn't read your tables directly. It answers by calling tools, and you'll give it two:

You give each tool a name and a description. The agent reads the descriptions to decide which tool fits a question, and the instructions you just pasted refer to the tools by name (returns_analytics, customer_comments). A tool's name is only a label inside the agent; it doesn't have to match the object it points to. A why question uses both tools: numbers from Analyst, quotes from Search.

6. Under Tools, remove the QUICK_RETURNS_SV tool (the quick semantic view from STEP 3). Then add Cortex Analyst by clicking on the + Add semantic view button:

   • Databases/Schemas: ONLINE_RETURNS.ANALYTICS
   • Select semantic view: RETURNS_SV
   • Name: returns_analytics
   • Description: Return counts, rates, values, and AI-tagged reasons by category and region, one row per order line.
   • Warehouse: COMPUTE_WH
   

7. Under Tools, add a Cortex Search service by clicking the + Search Services button:

   • Database/Schema: ONLINE_RETURNS.ANALYTICS
   • Search service: RETURN_COMMENTS_SEARCH
   • Name: customer_comments
   • Description: Customer return comments in their original language with an English translation, filterable by category, region, and reason.
   • ID column and Title column: ORDER_LINE_ID
   

8. Under Tools, turn on the Code Execution tool. CoWork needs it to build documents and reports, such as the one you'll make at the end of STEP 10.

   

9. Click Save.

Before you move on, check the Tools list. It should show exactly:

Snowflake CoWork is where anyone on the team chats with your agents, without opening a notebook or writing SQL. Agents you can use show up there automatically.

1. In Snowsight, navigate to AI & ML » Snowflake CoWork.

   

2. Select RETURNS_AGENT from the agent list.

   

3. Ask these two questions in the same conversation. The first one is the example question you added, so you can click it instead of typing it.

   1. "Which product categories have the highest and lowest return rate? Show it as a chart."

      

   2. "Why are customers returning footwear in EMEA? Show me what they said."

      

Expected:

Click the steps in each answer to see which tool the agent used: Cortex Analyst for question 1, and both tools for question 2.

In STEP 3, the same agent said Apparel was worst (about 45%) and Footwear best (about 14%). The only difference is the work you did on the data.

Answers in a chat are useful to you. A report is something you can hand to the merchandising team. Start a new chat with RETURNS_AGENT (in a long conversation the report tends to come out less polished) and send:

CoWork prompt: "Build a one-page HTML report for the merchandising team on our footwear returns problem. Include: a chart of return rate by category, the EMEA footwear numbers (returned lines, return rate, and returned value, split by reason), three customer quotes in the original language with English translations, and three recommended actions. Make it look polished: a title banner, summary cards for the key numbers, a consistent color palette, and clear section headings."

What this prompt asks for

The agent writes and runs code with its Code Execution tool to build the file, then CoWork shows a preview you can open and download. This takes a minute or two.

Expected: a styled one-page report showing Footwear highest at 42.1%; EMEA footwear at 100 of 150 delivered lines returned (66.7%), worth $12,490, mostly Wrong size (75 lines, $9,435); three quotes in German, French, or Spanish with translations; and three recommendations. Layout and wording will vary, so judge the report by its numbers and quotes.

Every number in that report traces back to a definition you wrote, and every quote to a real return. That's the difference from STEP 3.

If CoWork says it can't build the report, go back to STEP 9 and confirm that Code Execution is turned on.

Your agent used the same AI in STEP 3 and STEP 10. What made the difference was the work you did on the data in between.

The search service keeps a small amount of compute running until you drop it. If you want to remove everything, run this only after you've finished the lab and any validation checks.

Run the teardown cell, or run the same SQL in a SQL worksheet. Dropping the database removes everything inside it: tables, semantic views, the search service, and the agent. The second statement removes the Git API integration you created in setup. After that, the workspace can no longer pull from GitHub, so run teardown last.

Congratulations! You turned messy online-store return data into a trusted data product and asked questions about it in natural language with Snowflake CoWork.

Your agent first said Footwear had the lowest return rate, about 14%. The real answer is that Footwear has the highest, 42.1%, driven by customers in EMEA whose shoes run a size small. The agent used the same AI both times. The difference came from the work you did on the data first: deciding what a return is, which source to trust, and what each business word means.