Skip to content

Generating Snowflake SQL

From CrossModel to a loaded Snowflake warehouse

This example generates a complete Snowflake pipeline from the BrightGreen models: source tables, transformation SQL, warehouse tables and the stored procedures that load them.

Like the dbt example it covers both halves of the workflow - model and generate in CrossModel, then execute in Snowflake - but here the output is plain SQL you upload and run.

Two environments, one repository

Modeling environment Execution environment
Tools CrossModel Snowflake workspace
Who Data modeller / engineer Data engineer / analyst
What Define models, generate SQL, commit Upload SQL, run scripts, query results

The repository is the handoff point: the modelling team never needs Snowflake access, and the execution team never needs CrossModel.

The pipeline

Source Tables  -->  Entity Mappings  -->  DWH Tables
(raw data)          (transformations)     (final output)

The worked example is the same one used throughout: Customer from JuiceERP flows through a mapping that concatenates FirstName and LastName, producing CustomerFullName.

Get the workspace

On the welcome page, under Tutorials & Examples, choose:

examples → code-generation → snowflake

If you do not have a Snowflake account, a 30-day trial is available at signup.snowflake.com - any cloud provider and region will do.

What's in the workspace

Path Purpose
CrossGenerate/template/source_entity_model.sql Source layer table DDL
CrossGenerate/template/entity_mapping.sql Mapping / transformation SQL
CrossGenerate/template/dwh_entity_model.sql Warehouse layer table DDL
CrossGenerate/template/load_procedure.sql Stored procedures that load the warehouse
BrightGreen/target/ The generated SQL - this is what you upload
Snowflake/setup.sql One-time database and schema setup
Snowflake/sample_data.sql Sample rows for the source Customer table
Snowflake/run_load_procedure.sql Executes the load procedure

Part 1 - generate (in CrossModel)

  1. Browse the .cm files under BrightGreen/Sources/ and BrightGreen/ExampleDwh/ to see what drives the generation.
  2. Open CrossGenerate from the right-hand bar, or from View → CrossGenerate.
  3. Choose engine Nunjucks and run the four templates, writing output into BrightGreen/target/.
  4. Commit the generated SQL.

Part 2 - run (in Snowflake)

Upload BrightGreen/target/ and Snowflake/ to your Snowflake workspace, then:

  1. Set up once - run Snowflake/setup.sql to create the CrossModel database and its dwh and SourceVault schemas.

  2. Run the generated SQL in order:

    Order Folder What it creates
    1 target/source_entities/ Source tables
    2 target/dwh_entities/ Warehouse tables
    3 target/entity_mappings/ Transformation SQL
    4 target/load_procedures/ Load stored procedures

  3. Load sample data - run Snowflake/sample_data.sql.

  4. Run the load - run Snowflake/run_load_procedure.sql.

Order matters

Mappings reference both source and warehouse tables, and the load procedures reference the mappings. Running the folders out of order will fail on missing objects.

Part 3 - verify

SELECT * FROM CustomerFullName;

You should see the full customer names built from the sample data by the mapping.

What to try next

  • Add an entity or mapping in CrossModel, re-generate, and upload just the new files.
  • Adapt the templates in CrossGenerate/template/ to match your own Snowflake conventions - naming, clustering keys, warehouse sizing.
  • Extend the sample data in Snowflake/sample_data.sql to exercise more of the mapping logic.
  • Compare with dbt. Generating dbt models solves the same problem with a dbt project and DuckDB, which is quicker to try locally.