Skip to content

Generating data warehouse views

Turn your mappings into runnable SQL

The staging example generates DDL from entities - structure only. This example goes a step further and generates SQL from mappings, so the transformation logic you modelled becomes a CREATE OR REPLACE VIEW statement you can run.

This is where CrossModel's mapping model starts to pay off: the joins, the source columns and the expressions you defined in the mapping editor all end up in the generated view.

How it works

Generating from entities gives you structure. Generating from mappings gives you logic, and that is a different proposition: a mapping already knows which sources feed the target, how they join, and what expression produces each attribute. All of that is model, not SQL.

The template's scope is Mapping, so it runs once per mapping. Inside it:

  • the target's attribute mappings become the select list
  • the source objects and their join roles become the FROM and JOIN clauses
  • the dependency conditions become the join predicates
  • any expression is passed through into the select

Which means the SQL is not written anywhere - it is a rendering of the mapping. Change the join from an inner to a left join in the model, regenerate, and the SQL follows. This is where the effort of modelling mappings properly starts paying back, and it is also why lineage stays trustworthy: the thing you documented and the thing you run are generated from one source.

Get the workspace

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

examples → code-generation → dwh → nunjucks (or handlebars)

This branch extends the staging example - the staging templates are still there, so you can generate both layers from one workspace.

What's in the workspace

Path Purpose
BrightGreen/Generation/Templates/mapping/MappingView.njk Generates a view from a mapping. Scope: Mapping
BrightGreen/Generation/Templates/entity/StagingTable.njk Carried over from the staging example. Scope: Entity
BrightGreen/Generation/Templates/entity/attributes/SqlColumnDefinition.njk Column definition include

Generate the views

  1. Open CrossGenerate from the right-hand bar, or from View → CrossGenerate.
  2. Select a Mapping - any mapping under ExampleDwh/mappings/ - or choose All Mappings.
  3. Choose engine Nunjucks (or Handlebars).
  4. Select template MappingView.njk.
  5. Set the output filename pattern to Vw_{{mapping.id}}.sql.
  6. Press Generate.

The output is a CREATE OR REPLACE VIEW statement implementing the mapping: the target attributes become the select list, the source objects become the FROM/JOIN clauses, and any expressions become SQL expressions in the select.

Try a multi-source mapping

Pick a mapping with more than one source - Customer_JuiceERP_PreciseFinance is a good one - and look at how the join is rendered. That mapping is the subject of Creating a Mapping with Multiple Sources, so you can compare the model and the generated SQL side by side.

Generating both layers

Because this branch contains the staging templates too, you can produce a complete two-layer build from one workspace:

  1. Generate staging tables from All Entities using StagingTable.njk.
  2. Generate warehouse views from All Mappings using MappingView.njk.

Run the staging DDL first, then the views - the views select from the staging tables.

What to try next

  • Materialize instead of view. Change CREATE OR REPLACE VIEW to CREATE TABLE AS in the template if you want physical tables.
  • Target a real platform. Snowflake and dbt take this same idea further, with load procedures and a full project layout respectively.