Duration: 3

In this Quickstart, you'll build an end-to-end data pipeline in Snowflake using the Ingestion–Transformation–Delivery (I-T-D) framework — and you'll do it the modern way: by prompting Snowflake CoCo to write the SQL for you, inside a single notebook running in a Git-backed Snowsight Workspace.

Ingestion

We'll load data from:

Transformation

We'll transform our data using:

Delivery

We'll deliver a final data product using:

Duration: 5

To complete this lab, you'll need a Snowflake account. A free Snowflake trial account will work just fine. To open one:

1. Navigate to https://signup.snowflake.com/. This link pre-selects AWS and the US East (Ohio) region for you.

2. Complete the first page of the form.

3. On the next section, set the Snowflake edition to Enterprise (Most popular).

4. Confirm AWS – Amazon Web Services is selected as the cloud provider (pre-filled by the link).

5. Confirm US East (Ohio) is selected as the region (pre-filled by the link).

6. Complete the rest of the form and click Get started.

Duration: 3

Tasty Bytes is a food truck company that operates globally. You're a data engineer on the Tasty Bytes team. Data analysts recently flagged a troubling pattern:

Your goal is to find out why — and to build an end-to-end pipeline that keeps analysts informed about Hamburg weather and sales in the future.

Before diving in, it's worth understanding the framework we'll use: Ingestion–Transformation–Delivery (I-T-D).

I-T-D is a standard pattern for structuring data pipelines. Instead of mixing raw data, business logic, and presentation into one place, it separates them into three distinct layers — each with a clear responsibility:

This separation matters because it makes pipelines easier to debug and extend. When something breaks, you know which layer to look at. When requirements change, you change the logic in one layer without touching the others.

Ingestion

Transformation

Delivery

The agent will surface the sales anomaly and correlate it with weather data.

Everything up through Transformation runs from a single notebook (data-eng-coco.ipynb) that you'll clone from the companion repo. For Delivery, you'll use CoCo to create the Semantic View, then configure the Cortex Agent in the Snowsight UI. Let's get started!

Duration: 10

There are no setup scripts to run and no SQL worksheets to open first. Your very first action is to create a Git-backed Workspace that clones the companion repo — which contains the notebook you'll run (data-eng-coco.ipynb).

Make sure you are signed into your trial account. Confirm your active role is ACCOUNTADMIN.

1. In Snowsight, navigate to Projects » Workspaces.

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

3. Fill out the modal:

   • Repository URL: https://github.com/Snowflake-Labs/sfguide-snowflake-northstar-data-engineering
   • Workspace name: anything you like (e.g., northstar-data-eng)
   • API integration: click + API Integration and provide:
     • Name: GITHUB_SNOWFLAKE_LABS
     • Allowed prefixes: https://github.com/Snowflake-Labs
   • Check Public repository.

4. Click Create.

The Workspace opens with the repo's files visible in the file explorer on the left including the main notebook you'll need for this lab: data-eng-coco.ipynb.

1. In the Workspace file explorer, open data-eng-coco.ipynb.

2. Click Connect next to the Run button and select Create and connect. You can monitor the connection status in the status bar at the bottom of the notebook — wait for it to show Connected before proceeding.

3. Set the notebook's active role and warehouse using the picker at the top-left of the Notebooks editor (you can also do this in a cell with USE ROLE / USE WAREHOUSE):

   • Role: ACCOUNTADMIN
   • Warehouse: COMPUTE_WH (the default warehouse in every trial account)

   This is the notebook's query warehouse — it runs every SQL cell and any Snowpark pushdown compute. (Rendering the interactive results grid doesn't consume credits.)

4. Note on context: notebooks in Workspaces do not automatically select a database or schema. This notebook handles that for you — its cells run USE DATABASE / USE SCHEMA and use fully qualified names (e.g., TASTY_BYTES.RAW_POS.COUNTRY) — so objects resolve no matter where you run it.

5. Work top-to-bottom through the notebook. Markdown cells explain each step; SQL cells contain the code (some of which you'll generate yourself with CoCo). Run a cell with ▶ (or Shift+Enter), or use Run all from the toolbar.

The first SQL cell in the notebook (setup) creates the TASTY_BYTES database and three schemas that map directly to the I-T-D pipeline layers:

…and grants the Cortex Agent and CoWork privileges your role needs. Run the setup cell before continuing. You should see: "Statement executed successfully."

Duration: 5

The first data source in our pipeline is live weather data. Snowflake Marketplace lets you mount a live dataset directly into your account without copying or moving any data — the provider keeps it fresh automatically.

Rather than clicking through the Marketplace UI, we install the listing programmatically from the notebook. This is the repeatable, scriptable way to acquire a Marketplace dataset: accept the listing's legal terms, then create a database directly from the listing.

Run the weather_install cell in the notebook. You should see: "Database FROSTBYTE_WEATHERSOURCE successfully created."

The share is now live in your account as FROSTBYTE_WEATHERSOURCE. No ingestion logic needed — the data is owned and refreshed by Pelmorex. Later transformation steps reference this database by name.

Duration: 20

Now let's load the Tasty Bytes sales data. It lives across many CSV files in a public AWS S3 bucket. We'll use Snowflake's COPY INTO command to bring it in.

The notebook's next cell contains CREATE TABLE statements for all the raw POS tables plus the ANALYTICS.ORDERS_V view. ORDERS_V is a denormalized flat join across the raw POS tables — it exists so that every downstream query has a single, simple source for sales data without needing to know the raw schema. This is boilerplate — there's nothing to "solve" here. Run the raw_tables cell to create the empty target tables and the view.

Open the CoCo chat panel from the notebook toolbar. Throughout this lab you'll copy prompts directly from the guide (and from the prompt cells in the notebook) and send them to CoCo.

A file format tells Snowflake how to parse raw files during loading. We need one for the CSV data on S3.

Send this prompt to CoCo. Compare the output against the expected output in the notebook — if they match, click Allow to run it. Optionally, copy the SQL into the notebook cell for future reference.

A stage is a pointer to an external storage location (in this case, an S3 bucket) so Snowflake knows where to find the files.

Send this prompt to CoCo. Compare the output against the expected output in the notebook — if they match, click Allow to run it.

COPY INTO is Snowflake's bulk-loading command — it reads files from a stage and inserts them into a table. This single load teaches you the pattern you'll repeat for every table.

Send this prompt to CoCo. Compare the output against the expected output in the notebook — if they match, click Allow to run it.

You should see about 30 rows loaded successfully. Optionally, copy the SQL into the notebook cell for future reference.

Now we apply the same COPY INTO pattern at scale — loading six more tables in one shot. CoCo will also spin up a larger warehouse for performance, then tear it down to avoid idle credit burn.

Send this prompt to CoCo. Compare the output against the expected output in the notebook — if they match, click Allow to run it.

CoCo will generate SQL that creates a LARGE warehouse (LOAD_WH), runs six COPY INTO statements, then drops LOAD_WH and restores COMPUTE_WH. Optionally, copy the SQL into the notebook cell for future reference.

After all six loads complete, confirm the tables and their row counts in the object explorer on the left.

This completes the Ingestion stage of the pipeline.

Duration: 15

We have the raw data. Now we need to transform it into something analysts can query. We'll start with two SQL UDFs for unit conversion, then build four Dynamic Tables that automatically stay fresh as source data updates.

A SQL UDF (User-Defined Function) is a reusable function you define once and call anywhere in SQL. The Pelmorex weather data uses imperial units, but analysts want metric. We'll create two UDFs — one to convert Fahrenheit to Celsius and one to convert inches to millimeters — that we'll invoke directly inside Dynamic Table queries.

Send each prompt to CoCo one at a time. Each UDF encapsulates a single unit-conversion formula so you can call it like a built-in function anywhere in SQL. Compare CoCo's output against the expected output in the notebook — if they match, click Allow to run it. Optionally, copy the SQL into the notebook cell for future reference.

STEP 1:

STEP 2:

Confirm both appear in TASTY_BYTES.ANALYTICS in the object explorer.

A Dynamic Table is a table whose contents are defined by a query that Snowflake keeps up to date for you automatically. You write the SELECT once and declare a target freshness (TARGET_LAG); Snowflake works out the refresh schedule and, where possible, only reprocesses the rows that changed (incremental refresh). You get the readability of a view with the query performance of a table — and you never write or schedule pipeline code.

The four Dynamic Tables in this pipeline build on each other in layers: DAILY_WEATHER_DT (base weather) feeds WINDSPEED_HAMBURG_DT and WEATHER_HAMBURG_DT (both Hamburg weather). SALES_HAMBURG_DT filters the sales data to Hamburg with a full date spine so zero-sales days are visible. WEATHER_HAMBURG_DT and SALES_HAMBURG_DT are the two tables that power the Semantic View.

This is the base weather Dynamic Table. It joins the live Pelmorex share with Hamburg postal codes to produce one row per postal code per day with city and country labels.

Run the daily_weather_dt cell in the notebook.

This Dynamic Table filters DAILY_WEATHER_DT to Hamburg specifically, tracking daily maximum wind speed. It's the intermediate table that isolates Hamburg's weather pattern — critical context for understanding why sales dropped on specific days.

Run the windspeed_dt cell in the notebook.

This is the final weather Dynamic Table — one of the two that power the Semantic View. It aggregates the postal-level data from DAILY_WEATHER_DT into one row per date and converts temperature and precipitation to metric units using the UDFs you just created.

Run the weather_dt cell in the notebook.

This is the sales-side counterpart to WEATHER_HAMBURG_DT. It filters sales to Hamburg and adds a date spine — a generated sequence of every calendar day — so that days with no orders still appear as rows in the data. Without the date spine, days with no activity would be absent entirely, making it impossible for the Semantic View to detect gaps in the sales record.

Run the sales_dt cell in the notebook.

After running the sales_dt cell, confirm SALES_HAMBURG_DT appears in TASTY_BYTES.HARMONIZED. The initial refresh runs in the background and may take a minute — wait before checking the preview.

This completes the Transformation stage of the pipeline.

Duration: 15

We have clean, refreshing data. Now we need to make it accessible to analysts — in natural language. We'll ask CoCo to create the Semantic View, configure a Cortex Agent in Snowsight, and access the agent through Snowflake CoWork.

A Semantic View describes the tables in business terms: dates to group by, sales and order values to analyze, weather conditions to compare, and the relationship that connects them. Cortex Analyst uses that model to answer questions about Hamburg sales and weather.

1. In Snowsight, navigate to AI & ML → Cortex Analyst and click Create in Workspaces.

2. Click Create with CoCo.

3. Send CoCo this prompt:

   "Create the semantic view with these details:

   • Name: HAMBURG_INSIGHTS_SV
   • Location: TASTY_BYTES.ANALYTICS
   • Source tables: TASTY_BYTES.HARMONIZED.SALES_HAMBURG_DT and TASTY_BYTES.HARMONIZED.WEATHER_HAMBURG_DT
   • Include daily Hamburg sales, order counts, temperature, precipitation, and wind speed, with clear descriptions
   • Use DATE_VALID_STD as the unique key for WEATHER_HAMBURG_DT. Do not set a unique key on SALES_HAMBURG_DT. Define the relationship as many-to-one from ORDER_DATE to DATE_VALID_STD."

4. Allow CoCo to create the Semantic View draft in the Workspace. This creates an editable draft; it does not publish the view yet.

6. Review the draft in the Semantic View editor. Confirm it contains:

   • Both SALES_HAMBURG_DT and WEATHER_HAMBURG_DT, with DAILY_SALES and NUM_ORDERS under Facts for sales
     
   • DATE_VALID_STD as the unique key for WEATHER_HAMBURG_DT, and no unique key on SALES_HAMBURG_DT.ORDER_DATE. To find this, hover over the name of the table and click on the pencil to view the dimensions.
     
     
   • A many-to-one relationship from SALES_HAMBURG_DT.ORDER_DATE to WEATHER_HAMBURG_DT.DATE_VALID_STD
     
   • AVG_TEMPERATURE_CELSIUS, AVG_PRECIPITATION_MM, and MAX_WIND_SPEED_MPH under Facts for WEATHER_HAMBURG_DT

   CoCo may also add time dimensions or metrics. You do not need to find a category named "measures" or require extra SUM metrics for this lab. If one of the items above differs, correct the draft before publishing.

7. Click Publish. If Snowsight asks for a name and location, confirm Name HAMBURG_INSIGHTS_SV, Database TASTY_BYTES, and Schema ANALYTICS. Confirm the published view appears under TASTY_BYTES.ANALYTICS before adding it to the agent.

Cortex Analyst is Snowflake's text-to-SQL engine — it reads the Semantic View to understand your data model and converts natural-language questions into SQL queries. The Cortex Agent is the AI orchestration layer that receives questions from analysts and routes them to Cortex Analyst as the tool.

1. In Snowsight, navigate to AI & ML → Agents.

2. Click Create agent in the top right.

3. Configure:

   • Database and schema: TASTY_BYTES.ANALYTICS
   • Agent object name: HAMBURG_AGENT

4. Click Create.

5. Click Configuration near the top of the agent editor.

6. Under the General tab, set:

   • Description: I am a Hamburg Sales & Weather Intelligence Agent. I analyze Tasty Bytes food truck sales in Hamburg, Germany alongside local weather data to help answer questions about why sales fluctuated.
   • Example questions:
     • What caused the Hamburg sales gap in February 2022?
     • What were Hamburg's best and worst sales months in 2022?
     • Is there a relationship between temperature and daily sales in Hamburg?

7. Under the Instructions tab, set:
   • Orchestration Instruction: Whenever you can answer visually with a chart, always choose to generate a chart even if the user didn't specify to.
   • Response instructions: Give concise, accurate answers about Tasty Bytes Hamburg sales and weather.

8. Click Tools → Add semantic view and select Add semantic view from the options.

9. Configure the tool:
   • Service database & schema: TASTY_BYTES.ANALYTICS
   • Select semantic view: HAMBURG_INSIGHTS_SV
   • Name: TASTY_BYTES_SALES_ANALYST
   • Description: Answers questions about Tasty Bytes Hamburg sales and weather

10. Click Add then click Save in the top right.

Since you created the agent through the UI, it is already available in Snowflake CoWork.

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

2. Select HAMBURG_AGENT from the agent list.

3. Ask:

   "What caused the Hamburg sales gap in February 2022?"

The first question starts the investigation, rather than ending it. CoWork can use the Cortex Agent to turn a business question into queries against the Semantic View, then help you explore the results conversationally. Review the answer and any generated chart or SQL before drawing a conclusion: a sales gap that coincides with high wind speeds is a useful lead, but correlation alone does not prove what caused the gap.

Continue the same conversation with these follow-up questions:

1. Locate the gap. Ask: "Show daily Hamburg sales and order counts for February 2022. Which dates had zero orders?"

   Check that zero-order dates appear in the result. The date spine in SALES_HAMBURG_DT is what makes those days visible instead of leaving holes in the timeline.

2. Bring in weather. Ask: "For those zero-order dates, show daily sales, order counts, and maximum wind speed alongside the surrounding days. Plot the results by date."

   This is where the Semantic View's relationship matters: it connects each sales date to the corresponding weather date. If CoWork offers a chart, use it to look for a pattern; if it returns a table, compare the dates and values directly.

3. Test the explanation. Ask: "Were there other high-wind days in February 2022 when Hamburg still recorded sales? Compare them with the zero-order days."

   A good investigation looks for counterexamples, not just a matching spike. Ask CoWork to show the dates and values behind its summary so you can check whether the proposed explanation holds up.

4. Summarize the evidence. Ask: "Summarize what the sales and weather data show about the February 2022 gap. Separate observations from possible explanations, and say what additional data would be needed to confirm the cause."

Your results may vary with the available data and the agent's response. Do not treat a generated narrative or visualization as proof without checking its underlying dates, measures, and query.

The notebook produced reusable, automatically refreshed sales and weather tables. The Semantic View gives those tables business meaning and defines how they relate. The Cortex Agent makes that model available to CoWork, where an analyst can move from a broad question to a date-by-date comparison without writing a new SQL query for every follow-up.

You have now completed the Delivery stage: the pipeline does more than store transformed data — it gives analysts a way to investigate it, challenge an initial hypothesis, and communicate what the evidence does and does not show.

Duration: 2

Once you've finished the lab, run the following to remove all objects created and stop any ongoing credit consumption. The Dynamic Tables created in this lab will continue to refresh automatically until they are dropped.

Run the teardown cell in the notebook, or execute the following in a SQL worksheet:

Duration: 3

Congratulations! You've successfully built a fully prompt-driven, end-to-end data pipeline in Snowflake using the Ingestion–Transformation–Delivery framework — all from a single notebook plus a few UI steps.

Ingestion

Transformation

Delivery

The pipeline surfaced the answer: an analysis of Hamburg sales and weather data in February 2022 reveals a 6-day gap in sales that correlates with a significant windspeed event.