Tables, views, materialized views, and dynamic tables can now carry their transformation logic directly in the data model. The Logic tab of each of these objects has two sections, SQL and dbt, where the SELECT that populates the object and the matching dbt model code are written and documented next to the physical definition.
Keeping business logic alongside the schema gives data architects and analytics engineers a single source of truth: transformations stay in step with the tables they populate, are versioned and compared with the rest of the model, and can be forward engineered as CTAS statements, logic files, or dbt model files.
This feature may not be available by default and might need to be enabled upon request. Please contact your account manager for more information.
Availability
SQL and dbt logic is available in all physical projects that support Table Templates. Logical projects are not affected.
The Logic sections apply to the following object types:
| Object type | SQL section | dbt section |
|---|---|---|
| Tables | New. Optional SELECT logic that populates the table | New |
| Views and materialized views | The existing view definition, unchanged | New |
| Dynamic tables (Snowflake) | The existing dynamic table definition, unchanged | New |
External, hybrid, and Iceberg tables do not have a Logic tab.
The Logic tab
The Logic tab has two subtabs, SQL and dbt, with SQL selected by default. Both use the same SQL editor as the view Logic tab. A subtab that contains logic shows a dot, so you can see that dbt logic exists while working on the SQL subtab (and the other way around).
Opening the Logic tab on a table
Select the table on the diagram or in the Database Explorer.
Open the bottom edit panel for the table. A new Logic tab appears before Metadata.
Click Logic. The SQL subtab is selected by default.
Double-clicking a table still opens the inline column editor, and the panel still opens on the Metadata tab. Click Logic to switch to it.
Opening the Logic tab on views, materialized views, and dynamic tables
Nothing changes here: double-clicking the object opens edit mode on the Logic tab, with the SQL subtab selected. The Logic tab now also has a dbt subtab next to SQL.
[Screenshot: View Logic tab with the dbt section]
SQL section
On tables, the SQL section holds the SELECT statement that populates the table.
The logic is free text. It is not parsed to derive columns and is not synchronized with the table's column list.
It is not included in DDL or alter scripts unless Include Logic as SQL is selected in Forward Engineering (see Forward engineering below). A hint under the editor reads: Included in DDL only when Include Logic as SQL is selected in Forward engineer.
In every other respect it is handled like view logic: it is included in compare, documentation, copy and paste, project conversion, and revisions.
On views, materialized views, and dynamic tables, the SQL section is the existing object definition. It is emitted in DDL exactly as before.
dbt section
The dbt section holds the code for the object's dbt model .sql file. It is available on every object that has a Logic tab, regardless of its dbt object type.
The logic is free text. Jinja is allowed, for example
{{ ref('orders') }}or{{ source('raw_sales', 'customer') }}.It is not parsed, not synchronized with the column list, and never included in DDL or alter scripts.
Editing it never changes the object's definition, columns, or DDL.
It is used when generating dbt model files in Forward Engineering. A hint under the section reads: Used for the dbt model .sql file. Not included in DDL.
Generating logic from table templates
Instead of writing logic from scratch, a table template can generate a runnable SELECT skeleton when it is applied to one or more source objects. The generated SELECT joins the sources on their known relationships and references them as SQL names, as dbt ref() / source() calls, or both.
Template properties
Three object-level properties control logic generation:
generateSqlLogic: true | false # default: true for view, materializedView, dynamicTable; false for table
generateDbtLogic: true | false # default: false
sourceQualification: full | schema | name # default: schema. SQL section onlygenerateSqlLogic: generates logic into the SQL section.
generateDbtLogic: generates logic into the dbt section. Objects created this way are classified as dbt object type Model. Both properties can be
truein the same template; both sections are then generated from the same sources, joins, and columns.sourceQualification: how source objects are named in the SQL section:
full(all qualifiers the platform supports, e.g.database.schema.object),schema(schema.object), orname(objectonly). It is ignored, with a warning, whengenerateSqlLogicisfalse.
With these defaults, existing templates produce exactly what they produced before. Templates with materialization: table generate logic only when you set one of the properties to true.
The generateRelationship property now also accepts a list, so a Dependency relationship can be created together with one relational type, for example generateRelationship: [VFK, VDEP]. When a template generates logic, the default is VDEP. See Table Templates for the full property reference.
Creating an object with generated logic
On the diagram, select the source objects (one or several), right-click, and choose Create from template.
Pick a template with
generateSqlLogicand/orgenerateDbtLogicset totrue.The new object is added to the diagram, and Analyze runs automatically on the generated SQL logic. Open the object's Logic tab to review the generated SQL and/or dbt logic.
[Screenshot: Create from template on two selected tables]
Logic is generated once, when the object is created. If a template is applied to an existing object, a section that already has content is not overwritten.
How the logic is generated
One CTE per source, named after the source object, in alphabetical order by object name. A final CTE selects the columns and joins the sources, and the statement ends with
SELECT * FROM final.Joins are inferred from the relationships between the sources: physical foreign keys first, then virtual relationships with paired columns, then Virtual Join relationships. Joins are
INNER JOIN, except Virtual Join relationships, which keep their join type. Where no relationship exists, a<<join_column>>placeholder is used.Duplicate column names across sources (including join keys) are all kept and aliased as object name plus column name, following the project naming convention (for example,
customer_customer_id). The object's column list matches the final SELECT.Template columns with a
logicvalue are placed in the final SELECT per their position.Source references: the SQL section uses qualified names per
sourceQualification. The dbt section uses{{ source() }}for objects of dbt type Source (with their source header, or the schema name if none is set) and{{ ref() }}for Models and all other objects.Formatting: the SQL section uses uppercase keywords, leading commas between CTEs, and a closing semicolon. The dbt section follows the dbt style guide: lowercase keywords, trailing commas, and no semicolon.
VDEPs (virtual dependencies) are always created when generating Logic and does not need to be expressly declared in "
generateRelationship"
Example
A Snowflake project has tables sales.customer and sales.orders, with a foreign key from orders.customer_id to customer.customer_id. In dbt project properties, customer is a Source with the source header raw_sales and orders is a Model. The following template is applied over both tables selected:
name: customer_orders_model
materialization: view
generateDbtLogic: true
generateRelationship: VFK
physicalName: ${parentPhysicalName}_V
inheritIdentifiersAs: non-pk
inheritAttributes: true
defaultColumns:
- name: etl_src
position: first
dataType: varchar(50)
logic: "'salesforce'"
The new view receives the following SQL section:
WITH customer AS (
SELECT
customer_id,
customer_name,
email
FROM sales.customer
)
, orders AS (
SELECT
order_id,
customer_id,
order_date,
total
FROM sales.orders
)
, final AS (
SELECT
'salesforce' AS etl_src,
customer.customer_id AS customer_customer_id,
customer.customer_name,
customer.email,
orders.order_id,
orders.customer_id AS orders_customer_id,
orders.order_date,
orders.total
FROM customer
INNER JOIN orders
ON customer.customer_id = orders.customer_id
)
SELECT * FROM final
;
And the following dbt section:
with customer as (
select
customer_id,
customer_name,
email
from {{ source('raw_sales', 'customer') }}
),
orders as (
select
order_id,
customer_id,
order_date,
total
from {{ ref('orders') }}
),
final as (
select
'salesforce' as etl_src,
customer.customer_id as customer_customer_id,
customer.customer_name,
customer.email,
orders.order_id,
orders.customer_id as orders_customer_id,
orders.order_date,
orders.total
from customer
inner join orders
on customer.customer_id = orders.customer_id
)
select * from final
The view's columns are etl_src, customer_customer_id, customer_name, email, order_id, orders_customer_id, order_date, and total. A VFK and a VDEP relationship are created from each source, and the view is classified as dbt type Model.
Two default templates help you get started: view (default), which now includes the new properties explicitly and generates the SQL section only, and the new CTAS (default) template, which creates a table with generated SQL logic.
Forward engineering
FE takes advantage of SQL and dbt logic to allow users to output new types of assets that include them: CTAS for tables and dbt model files (.sql).
SQL logic in DDL
View, materialized view, and dynamic table DDL is generated from the SQL section as before. For tables, the SQL logic is included only when you choose to include it:
Open Forward Engineering and select DDL and Full script.
Under Settings, select Include Logic as SQL.
-
Choose how the logic is generated:
CTAS (default): tables with SQL logic are generated as
CREATE TABLE ... AS <logic>. Include column definitions (selected by default) adds the modeled column list where the platform supports it. Constraints that the platform can't declare in CTAS follow asALTER TABLEstatements.Separate logic file: the table DDL is generated as usual, and the logic is written to a separate
<table_name>.logic.sqlfile when pushing to Git.
Tables with an empty SQL section are generated as before. SQL Server does not support CTAS, so only Separate logic file is available in SQL Server projects. Table logic is never included in alter scripts.
dbt logic in dbt model files
Open Forward Engineering, select the dbt format and the dbt model type.
Under Output, select YAML + SQL.
Click Generate YAML + SQL, or push to Git.
Each selected table, view, materialized view, or dynamic table with dbt logic produces a <model_name>.sql file containing the dbt logic exactly as written, in the same folder as its model YAML. Objects with an empty dbt section are listed in the YAML as before and get no .sql file.
The new options can also be saved as project-wide defaults under Project settings → Forward engineering. For details, see Forward Engineering, YAML Generation for dbt, and Pushing to a Git Repository.