Northbound Coffee just bought Daily Grind. The CEO wants one revenue dashboard by Monday. The problem: the two companies' order systems disagree on almost everything.

In this guide you play the data engineer who has to make the numbers agree. You'll use CoCo (Cortex Code), the AI coding agent built into Snowsight, to compare the two sources, build a pipeline with Dynamic Tables, survive a surprise schema change upstream, and build the CEO's dashboard as a Streamlit app from scratch.

Every step ends with a check query and the exact numbers you should see, so you always know whether you're on track.

Two companies, two order systems, one board meeting.

The one table both companies agree on is MERGER_DB.RAW.STORE_CROSSWALK. It maps each source's store key to a unified STORE_ID, along with each store's name, city, and time zone.

Each of those differences is a way to get the wrong revenue number:

Your job is to make the number right, and keep it right.

Sign in to Snowsight. In the user menu in the lower-left corner, switch your role to ACCOUNTADMIN. You need it only for setup.

The companion repo holds every file you'll run in this lab. Open it as a workspace so the files are already in Snowsight.

1. In the navigation menu, select Projects » Workspaces.
2. Select the + icon next to Workspaces/Databases, then Create new Git workspace.
3. Fill out the dialog:
   • Repository URL: https://github.com/sfc-gh-JCHAEARLEY/sfguide-merge-two-order-systems-with-cortex-code
   • Workspace name: anything you like, for example the-merger
   • API integration: select + / Create new and enter:
     • Name: GITHUB_MERGER_LAB
     • Allowed prefixes: https://github.com/sfc-gh-JCHAEARLEY
   • Select Public repository.
4. Select Create.

The workspace opens with the repo's files in the file explorer on the left:

Open setup.sql and select Run all. The script:

The data is generated deterministically, so everyone gets exactly the same rows.

In the user menu in the lower-left corner, switch your role to MERGER_ROLE. CoCo works with whatever role is active in Snowsight, so from here on it can change only the objects the lab created.

Open checks.sql. You'll run one block from it after each step. Select and run the CHECK 1 block. Every row should say PASS:

Select the CoCo icon in the lower-right corner of the workspace. You'll use one CoCo conversation for the discovery and pipeline steps, so CoCo can reuse what it learns.

Before writing any pipeline, have CoCo study both sources and tell you where they disagree.

CoCo runs a few exploratory queries, such as sampling rows, flattening the JSON, and counting distinct order IDs, then summarizes. Check that its answer covers all five differences:

1. Format: STORE_SALES has typed columns, while WEB_ORDERS stores each order as JSON in a VARIANT column.
2. Units: TOTAL_USD is in dollars, while amount_cents is in cents.
3. Time zones: SALE_TIMESTAMP is store-local time, while created_at is a UTC string. Each store's time zone is in STORE_CROSSWALK.TIMEZONE.
4. Store IDs: POS uses register numbers (101–105), web uses location codes (DG-SF-01 and so on), and both map to a STORE_ID through STORE_CROSSWALK.
5. Duplicates: WEB_ORDERS has 3,106 documents but only 3,040 distinct order_id values. The retried copies share an order_id and have a later LOADED_AT.

If CoCo misses one, ask about it directly, for example: "Are there duplicate order IDs in WEB_ORDERS? What unit is amount_cents in?"

Run the CHECK 2 block in checks.sql. These numbers should match what CoCo reported:

Now turn the mapping into a pipeline. You'll build two Dynamic Tables:

A Dynamic Table is defined by a SELECT. Snowflake keeps it up to date as the source tables change, within the TARGET_LAG you set, and works out the refresh order from the dependency graph. TARGET_LAG = DOWNSTREAM means "refresh only when a table downstream needs fresh data."

Creating core tables is a good time to review before anything runs. Press Shift+Tab in the CoCo message box to switch to Plan mode. CoCo lays out what it intends to do and waits for your approval before it creates anything.

In the same CoCo conversation, send:

Before you approve, check that the plan:

If something is off, tell CoCo in plain language before approving. When the plan looks right, approve it and let CoCo create the tables.

Run the CHECK 3 block in checks.sql:

That's 4,216 in-store orders plus 3,040 web orders. Northbound brings in $41,789.95 and Daily Grind $30,912.60.

If a number doesn't match, the wrong value usually tells you which rule was missed:

Ask CoCo to fix the Dynamic Table rather than editing the SQL yourself, then run CHECK 3 again.

It's Monday. Over the weekend, Northbound's POS vendor shipped a "minor" update: the export now calls the dollar column SALE_AMOUNT_USD instead of TOTAL_USD. The first batch in the new format, Tuesday Sept 29, just landed. Then the CEO opens the dashboard.

Open break_batch.sql and select Run all. The script:

1. renames TOTAL_USD to SALE_AMOUNT_USD on RAW.STORE_SALES
2. inserts 150 sales for Sept 29 using the new column name
3. asks DAILY_REVENUE for a refresh

The last statement fails. That's the point. You'll see an error like:

Don't go looking for the cause yourself. In CoCo, switch back to normal mode (Shift+Tab) and send:

1. Query INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY for MERGER_DB.ANALYTICS and find two problems:
   • ORDERS_UNIFIED is FAILED with invalid identifier 'S.TOTAL_USD'.
   • DAILY_REVENUE is UPSTREAM_FAILED: it was skipped because its input failed. The root cause is upstream.
2. Inspect RAW.STORE_SALES and see that TOTAL_USD is gone and SALE_AMOUNT_USD has replaced it.
3. Update ORDERS_UNIFIED to read SALE_AMOUNT_USD, then refresh DAILY_REVENUE.

CoCo may use CREATE OR ALTER DYNAMIC TABLE or CREATE OR REPLACE DYNAMIC TABLE for the fix. Both work here. CREATE OR ALTER updates the existing table's definition, while CREATE OR REPLACE rebuilds the table, which makes downstream incremental Dynamic Tables reinitialize. That difference matters more in large pipelines than in this lab.

Run the CHECK 4 block in checks.sql. Before the fix, every row fails, and the last one shows UPSTREAM_FAILED. After the fix, every row should say PASS:

The numbers agree, and the pipeline survives upstream changes. Now give the CEO something to look at.

1. In the workspace file explorer, select + Add new » Streamlit app. Name it merger_dashboard.
2. Snowflake creates a folder with starter files, including streamlit_app.py. Open streamlit_app.py.
3. Open the CoCo panel. Start a new conversation so CoCo focuses on the app.

CoCo rewrites streamlit_app.py. Review the changes and accept them.

Select Run in the action bar at the top of the file. This starts a private preview only you can see. With the full date range and both brands selected, the KPIs should read:

Change the filters to explore. Saturdays are busier than weekdays, and Daily Grind's web orders skew toward the evening.

If the app shows an error, paste the error into CoCo and ask it to fix the app. If you're stuck, copy the contents of solutions/streamlit_app.py into your streamlit_app.py.

The preview is private. To share the dashboard, deploy it as a Streamlit object other users can open.

1. In the project pane toolbar, select Deploy.
2. Review the settings:
   • Location: MERGER_DB.ANALYTICS
   • Execution: keep the default compute pool, and choose MERGER_WH as the query warehouse. Unless your account has changed it, the default pool is the Snowflake-created SYSTEM_COMPUTE_POOL_CPU.
   • Sharing: optionally add the roles or users who should see the app.
3. Select Deploy.

Open the deployed app. That's the dashboard the CEO gets on Monday: one revenue number, from two systems that didn't agree on anything.

To stop all credit consumption from this guide, run:

You can also delete the the-merger workspace from Projects » Workspaces.

You merged two order systems that disagreed on format, units, time zones, store IDs, and duplicates, and turned them into one revenue number that holds up. Then you kept it right when an upstream change broke the pipeline, and built a dashboard on top of it, without writing the code by hand.

Code:

Guides:

Documentation: