Skip to content

Use Case: Round Trip Between Snowflake and Nexadata

The quickest way to prove a Snowflake connection end to end is a round trip: read a table from Snowflake into Nexadata, run it through a Workflow, write the result back to Snowflake, and check that the totals match. This use case walks through that round trip with Beverage Sales, a sample planning fact table that Nexadata provides, but any table of your own works the same way. It works with either an OAuth or a Key-pair connection.

By the end, you will have:

  • A Nexadata Dataset that reads from Snowflake with a SQL statement

  • A Workflow with a visible Pipeline step that writes its output to a new Snowflake table

  • Proof that every row arrived and the amounts tie to the cent

  • Proof that rerunning the Workflow does not duplicate rows


This example uses one database with two schemas: one Nexadata reads from, and one it writes to. Your names can differ. If they do, use them wherever this guide says FINANCE, ACTUALS, or PLANNING, including in the SQL and the output settings.

ObjectPurposePrivileges for the Nexadata role
FINANCEDatabaseUSAGE
FINANCE.ACTUALSSource data, read by NexadataUSAGE, SELECT on all and future tables
FINANCE.PLANNINGResults, written by NexadataUSAGE, CREATE TABLE, CREATE STAGE, table write privileges, OWNERSHIP on future tables

Splitting read and write this way keeps Nexadata’s access to your source data read-only. See Setting up a Snowflake Connection for how to create the role and the connection, and for the exact grant statements.

In a Snowsight worksheet, run:

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS FINANCE;
CREATE SCHEMA IF NOT EXISTS FINANCE.ACTUALS;
CREATE SCHEMA IF NOT EXISTS FINANCE.PLANNING;

Then run the grants from Part 2, Step 1 of the setup guide with <DATABASE> set to FINANCE, <SOURCE_SCHEMA> set to ACTUALS, and <TARGET_SCHEMA> set to PLANNING.

  1. Download beverage-sales-fact.csv (1.4 MB). It holds the Beverage Sales fact table, generated sample data for a beverage company: 28,800 rows with the columns month, measures, product, market, scenario, pkg_type, and data.

  2. In Snowsight, open Ingestion → Add data, choose Load data into a Table, and select beverage-sales-fact.csv. Load it into a new table FINANCE.ACTUALS.BEVERAGE_SALES_RAW on your warehouse.

  3. Before you click Load, check the column names under Edit Schema. By default, Snowflake treats the header line as data: the columns are named c1 to c7, and every column is VARCHAR.

    The Snowflake Load Data into Table dialog with View options highlighted and the columns named c1 to c7, each holding its header name as the first value.

    Click View options under File format and set Header to First line contains header. The columns are then named month to data, and data is detected as NUMBER. Click Load.

    The same dialog with Header set to First line contains header, the columns named month to data, and data detected as NUMBER, all highlighted.

  4. In a worksheet, copy the table and store the amount as an exact decimal:

CREATE OR REPLACE TABLE FINANCE.ACTUALS.BEVERAGE_SALES AS
SELECT MONTH, MEASURES, PRODUCT, MARKET, SCENARIO, PKG_TYPE,
CAST(DATA AS NUMBER(18,2)) AS DATA
FROM FINANCE.ACTUALS.BEVERAGE_SALES_RAW;
DROP TABLE FINANCE.ACTUALS.BEVERAGE_SALES_RAW;
SELECT COUNT(*) AS ROW_COUNT, SUM(DATA) AS TOTAL
FROM FINANCE.ACTUALS.BEVERAGE_SALES;

For Beverage Sales, this returns 28,800 rows totalling 5,613,357.84. Keep these numbers; you will compare against them at the end.


  1. Create a Snowflake connection with OAuth or Key-pair. See Setting up a Snowflake Connection.

  2. Create a Dataset from that connection. Choose your Warehouse and enter this SQL Statement:

SELECT * FROM FINANCE.ACTUALS.BEVERAGE_SALES
  1. Click Submit. On Define Columns, you should see the seven columns, with DATA as Number and a Decimal Precision of 2. Save the Dataset.

See Using a Snowflake Connection for the Dataset options in detail.


Part 3: Write the Results Back to Snowflake

Section titled “Part 3: Write the Results Back to Snowflake”

Create a Workflow that uses the Dataset. See Creating Nexadata Workflows. An output writes what a Pipeline produces, so add a Pipeline on the Dataset with one transformation: Rename the column DATA to AMOUNT. The rename keeps every row and every amount, so the totals stay checkable, and it shows plainly in Snowflake that the data passed through Nexadata. In practice, this is where your transformations and mappings go.

The Pipeline for Beverage Sales - Snowflake with the Rename DATA to AMOUNT transformation highlighted and the renamed AMOUNT column highlighted in the preview.

Add an output and select your Snowflake connection. Set:

  • Warehouse: The same warehouse as the Dataset.

  • Database: FINANCE

  • Schema: PLANNING

  • Target Table: BEVERAGE_SALES

  • Write Mode: Upsert, with Business Key Columns MONTH, MEASURES, PRODUCT, MARKET, SCENARIO, and PKG_TYPE.

  • Create the table if it doesn’t exist: On.

Save the output and run the Workflow. When it finishes, the output is listed with the status Success.

The Workflow results with Workflow Succeeded, the Pipeline and its Rename DATA to AMOUNT step checked, and the Beverage Sales to Snowflake output showing Success, highlighted.


In a Snowsight worksheet, run:

SELECT NEXADATA_RUN_ID, COUNT(*), COUNT(AMOUNT), SUM(AMOUNT)
FROM FINANCE.PLANNING.BEVERAGE_SALES
GROUP BY 1;

The result is one row: one NEXADATA_RUN_ID, the ID of the Workflow run that wrote the table, with 28,800 rows, 28,800 amounts, and a total of 5,613,357.84. Matching the source to the cent confirms that every amount was written as an exact decimal. Nexadata created AMOUNT as NUMBER(38,2).

A Snowsight result with one row: NEXADATA_RUN_ID 417, 28800 rows written, 28800 rows with an amount, and a total of 5613357.84, highlighted.

Run the Workflow a second time, then repeat the query. The result is still one row with 28,800 rows and 5,613,357.84, now with the second run’s ID: Upsert matched every row on the business key and updated it in place rather than inserting a duplicate.

Snowflake records every statement Nexadata runs under the connection’s user. For a Key-pair connection with the service user NEXADATA_SVC, this query lists the recent loads:

SELECT start_time, query_type, execution_status, rows_produced
FROM TABLE(FINANCE.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(
USER_NAME => 'NEXADATA_SVC', RESULT_LIMIT => 20))
WHERE query_type IN ('MERGE', 'INSERT', 'UPDATE')
ORDER BY start_time DESC;

Each Upsert run appears as one MERGE with rows_produced of 28,800. For an OAuth connection, use the name of the person who signed in. Two things to know when reading the result:

  • Use rows_produced. This view has no rows_inserted or rows_updated columns.

  • Snowflake shows times in Pacific time by default, while Nexadata shows your local time, so the same run can appear hours apart.


Replace Beverage Sales with your own tables, add transformations and mappings to the Pipeline, and write the results to the schemas your planning teams use. If anything in the round trip fails, see Troubleshooting the Snowflake Connector.

Next: Learn about Snowflake connector troubleshooting tips