Skip to main content

CoginitiScript for dbt Core Users

If you're coming from dbt Core, many CoginitiScript concepts will feel familiar. This guide maps the dbt patterns you already know to their CoginitiScript equivalents, highlights the differences that matter, and introduces capabilities that have no dbt counterpart.

What You'll Learn​

  • How dbt models, refs, sources, and materializations translate to CoginitiScript
  • How CoginitiScript's block-based architecture differs from dbt's file-per-model convention
  • How to convert Jinja templating patterns to CoginitiScript expressions, loops, and conditionals
  • How to write tests, organize packages, and publish results
  • How to use LLM blocks for AI-generated structured data in your pipeline

Prerequisites​

  • Familiarity with dbt Core (models, sources, refs, Jinja, materializations, testing)
  • Basic understanding of SQL
  • A Coginiti instance with a database connection configured

Concept Map​

This table provides a quick reference for how dbt concepts map to CoginitiScript. Each mapping is covered in detail in the sections that follow.

dbt Core ConceptCoginitiScript EquivalentKey Differences
Model (.sql file)Named block (#+src sql)Multiple blocks can live in one file
ref('model_name'){{ BlockName() }}Blocks are invoked like functions, with parentheses
source('schema', 'table')A block wrapping a queryNo YAML source definitions; sources are SQL queries in blocks
Jinja ({{ }}, {% %})Expressions, #+if, #+forSimilar delimiters, different control-flow syntax
Macros (.sql in /macros)#+macro or reusable blocksMacros inline code; blocks materialize as CTEs or temp tables
YAML schema tests#+test sql blocksTests are SQL queries that return rows on failure
MaterializationsPublication metadataConfigured via #+meta, not dbt_project.yml
Packages (packages.yml)#+import directivesImport from the analytics catalog, not a package registry
Seeds (.csv files)No direct equivalentLoad reference data through your warehouse's native tooling
Python modelsLLM blocks (partial)No Python support; LLM blocks cover AI/ML enrichment use cases
No equivalentLLM blocks (#+src llm)AI-generated structured data with schema validation

Models → Blocks​

Defining a Block​

A dbt model is a SELECT statement in its own .sql file. A CoginitiScript block is a named SQL unit defined with directives:

-- dbt: models/sales_per_store.sql
SELECT
  s.name AS store_name,
  SUM(s.quantity * s.price) AS total_sales_amount
FROM {{ ref('fact_sales') }} AS s
INNER JOIN {{ ref('dim_store') }} AS ds ON s.store_id = ds.id
GROUP BY s.name

The CoginitiScript equivalent:

#+src sql SalesPerStore()
#+begin
  SELECT
    s.name AS store_name,
    SUM(s.quantity * s.price) AS total_sales_amount
  FROM {{ FactSales() }} AS s
  INNER JOIN {{ DimStore() }} AS ds ON s.store_id = ds.id
  GROUP BY s.name
#+end
warning

Block names are case-sensitive and follow Go-style visibility rules. Names starting with an uppercase letter are public (accessible from other packages). Names starting with lowercase are private. If you're used to dbt's snake_case model names, adopt PascalCase for anything you want to expose across packages.

Multiple Blocks per File​

Unlike dbt's strict one-model-per-file rule, a CoginitiScript file can contain several blocks. This is useful for keeping closely related transformations together:

#+src sql RawOrders()
#+begin
  SELECT * FROM raw.orders WHERE is_deleted = false
#+end

#+src sql CleanedOrders()
#+begin
  SELECT
    order_id,
    TRIM(customer_name) AS customer_name,
    order_date
  FROM {{ RawOrders() }}
#+end

Any SQL outside of a named block is treated as an anonymous default block and executes immediately when the file runs. Named blocks execute only when referenced.

Multiple Statements per Block​

In dbt, each model is essentially a single SELECT. CoginitiScript blocks can contain multiple SQL statements. The last SELECT in the block is what gets returned:

#+src sql CustomerData()
#+begin
  CREATE TEMP TABLE temp_customers AS
    SELECT * FROM raw.customers;

  -- This result is returned
  SELECT * FROM temp_customers WHERE active = true;
#+end
tip

Use the void return type when your block performs side effects (DDL, inserts) without returning data:

#+src sql SetupSchema(): void
#+begin
  CREATE SCHEMA IF NOT EXISTS analytics;
  GRANT USAGE ON SCHEMA analytics TO GROUP analysts;
#+end

Sources​

In dbt, sources are declared in .yml files and referenced via source('schema', 'table'). In CoginitiScript, a source is a block that wraps a query against a raw table:

#+src sql RawCustomers()
#+begin
  SELECT * FROM raw.customers
#+end

There is no separate YAML configuration layer. Source definitions are plain SQL that follows the same patterns as every other block. If you need to validate data freshness or quality at the source layer, write test blocks against your source blocks.

Referencing and Materialization​

Block References Replace ref()​

Where dbt uses {{ ref('model_name') }}, CoginitiScript uses {{ BlockName() }}. The parentheses are required — blocks are invoked like function calls. For blocks in other packages, qualify with the package name:

#+import "sales/customer"

SELECT * FROM {{ customer.CustomerDimension() }};

How Materialization Works​

When you reference a block, CoginitiScript automatically decides how to materialize it:

  • CTE (default): The block's SQL is inlined as a WITH clause. Similar to dbt's ephemeral materialization.
  • Temporary table (fallback): When CTEs become too complex or the platform doesn't support them well, CoginitiScript creates a temp table behind the scenes.
  • Ephemeral table: For certain platforms, a real table is created and automatically dropped after execution.

You don't configure this per block. The engine optimizes automatically. When you want a block's output to persist as a database object, use Publication.

Publication Replaces Materializations​

dbt's materialized config (table, view, incremental, ephemeral) maps to CoginitiScript's publication metadata:

dbt MaterializationCoginitiScript PublicationNotes
ephemeral(default, no publication)Blocks without publication are inlined as CTEs
view:publication { :type "view" }Identical outcome
table:publication { :type "table" }Identical outcome
incremental (append):incremental "append"Uses #+if publication.Incremental()
incremental (merge):incremental "merge" + :unique_keyDirectly analogous to dbt's unique_key
N/A:type "csv" or "parquet"CoginitiScript can publish directly to files

Here is a dbt incremental model and its CoginitiScript equivalent:

-- dbt incremental model
{{ config(materialized='incremental', unique_key='date_day') }}
SELECT
  DATE_TRUNC('day', event_date) as date_day,
  COUNT(*) as event_count
FROM events
{% if is_incremental() %}
  WHERE event_date >= (SELECT MAX(date_day) FROM {{ this }})
{% endif %}
GROUP BY date_day
-- CoginitiScript equivalent
#+src sql DailyMetrics()
#+meta {
  :publication {
    :type "table",
    :name "daily_metrics",
    :incremental "merge",
    :unique_key ["date_day"]
  }
}
#+begin
  SELECT
    DATE_TRUNC('day', event_date) as date_day,
    COUNT(*) as event_count
  FROM events
  #+if publication.Incremental() then
    WHERE event_date >= (SELECT MAX(date_day) FROM {{ publication.Target() }})
  #+end
  GROUP BY date_day;
#+end
warning

{{ this }} in dbt becomes {{ publication.Target() }} in CoginitiScript. And is_incremental() becomes publication.Incremental(). The logic is the same, but the function names differ.

Templating​

Expressions​

Both dbt and CoginitiScript use {{ }} for expression interpolation. In dbt, these are Jinja expressions. In CoginitiScript, they are native expressions evaluated by the CoginitiScript preprocessor:

SELECT {{ 1 + 1 }}     -- SELECT 2
SELECT {{ "string" }}   -- SELECT string (no quotes)
warning

String interpolation inserts values without quotes. This is intentional — it allows dynamic identifier generation. For quoted string values in SQL, wrap in single quotes: '{{ myVar }}'.

Conditionals​

dbt (Jinja)CoginitiScript
{% if condition %}#+if condition then
{% elif condition %}#+elseif condition then
{% else %}#+else
{% endif %}#+end

Each #+if and #+elseif branch takes a condition followed by the then keyword; #+else takes none. Branches are evaluated top to bottom and only the first match is rendered, so multi-way logic chains the same way it does in Jinja:

-- dbt
{% if target.name == 'prod' %}
  SELECT 'Running against production'
{% elif target.name == 'staging' %}
  SELECT 'Running against staging'
{% else %}
  SELECT 'Unknown environment'
{% endif %}

-- CoginitiScript
#+if environment == "prod" then
  SELECT 'Running against production';
#+elseif environment == "staging" then
  SELECT 'Running against staging';
#+else
  SELECT 'Unknown environment';
#+end

An #+if may have any number of #+elseif branches, and the trailing #+else is optional. If no condition matches and there is no #+else, the construct produces no output.

Loops​

dbt (Jinja)CoginitiScript
{% for item in list %}#+for item : list separator "," do
{{ item }}{{ item }}
{% endfor %}#+end

CoginitiScript loops have a built-in separator keyword that handles comma-separation automatically. No more {% if not loop.last %},{% endif %} patterns:

-- dbt
SELECT
  {% for col in ['name', 'email', 'phone'] %}
    {{ col }}{% if not loop.last %},{% endif %}
  {% endfor %}
FROM customers

-- CoginitiScript
SELECT
  #+for field : ["name", "email", "phone"] separator "," do
    {{ field }}
  #+end
FROM customers

Variables and Constants​

dbt uses var() for project-level variables defined in dbt_project.yml. CoginitiScript uses #+const blocks:

#+const
  userLimit = 100;
  emailDomain = "company.com";
  reportFields = ["name", "email", "signup_date"];
#+end

Constants support integers, floats, strings, keywords, lists, and maps. They follow the same public/private visibility rules as blocks (uppercase = public).

Macros​

dbt macros are Jinja functions. CoginitiScript has two mechanisms for reuse:

FeatureBlock (#+src sql)Macro (#+macro)
MaterializationCTE, temp table, or publicationPure text substitution (inlined)
Return typerecordset or voidN/A (raw SQL fragment)
Use caseData transformationsReusable SQL snippets (CASE statements, filters)

Use a macro when you want to generate a SQL fragment that gets inlined directly into the calling query. Use a block when you're defining a dataset transformation:

-- dbt macro
{% macro country_group(country_col) %}
  CASE
    WHEN {{ country_col }} IN ('US', 'CA') THEN 'North America'
    WHEN {{ country_col }} IN ('GB', 'FR', 'DE') THEN 'Europe'
    ELSE 'Other'
  END
{% endmacro %}

-- CoginitiScript macro
#+macro countryGroup(country)
#+begin
  CASE
    WHEN {{ country }} IN ('US', 'CA') THEN 'North America'
    WHEN {{ country }} IN ('GB', 'FR', 'DE') THEN 'Europe'
    ELSE 'Other'
  END
#+end

For a deeper look at macros, see CoginitiScript: The Power of Macros.

Testing​

dbt uses a mix of YAML-declared generic tests and custom SQL tests. CoginitiScript unifies all testing into SQL test blocks.

Defining Tests​

A test block is defined with #+test sql instead of #+src sql. The test passes if it returns zero rows; it fails if it returns any rows:

-- dbt custom test: tests/assert_no_null_emails.sql
-- SELECT * FROM {{ ref('customers') }} WHERE email IS NULL

-- CoginitiScript equivalent
#+test sql TestCustomerEmailNotNull()
#+begin
  SELECT * FROM {{ CustomerData() }} WHERE email IS NULL;
#+end

Generic Tests​

dbt's built-in generic tests (unique, not_null, accepted_values, relationships) don't have a YAML-based equivalent in CoginitiScript. Write them as explicit SQL test blocks:

#+test sql TestOrderIdUnique()
#+begin
  SELECT order_id, COUNT(*)
  FROM {{ Orders() }}
  GROUP BY order_id
  HAVING COUNT(*) > 1;
#+end

Running Tests Programmatically​

The std/test package lets you run tests with control over failure behavior:

#+import "std/test"
#+import "data_quality/customers"

-- Run all tests in a package
{{ test.Run(packages=[customers]) }}

-- Continue even if tests fail
{{ test.Run(packages=[customers], onFailure=test.Continue) }}

LLM Blocks​

LLM blocks are a CoginitiScript block type with no dbt equivalent. They send a prompt to an AI model and return structured, schema-validated tabular data that you can reference from SQL blocks, join with other datasets, publish to tables, and test with #+test blocks.

note

LLM blocks use whichever AI provider and model is configured as your active AI Assistant. There is no per-block model setting.

Defining an LLM Block​

An LLM block has three parts: the block declaration (#+src llm), a :schema that defines output columns and their types, and a natural-language prompt body:

#+src llm SampleCustomers()
#+meta {
  :schema {
    :columns [{:name "id",    :type "INT",    :description "Customer ID"},
              {:name "name",  :type "STRING", :description "Full name"},
              {:name "email", :type "STRING", :description "Email address"},
              {:name "tier",  :type "STRING", :description "Bronze, Silver, or Gold"}]
  }
}
#+begin
Generate 10 realistic sample customer records for an e-commerce platform.
Include a mix of Bronze, Silver, and Gold tier customers.
#+end

The :description field on each column steers the AI toward appropriate values. A column described as "ISO 3166-1 alpha-2 country code" produces better results than one described as "country".

Using LLM Output in SQL​

Reference an LLM block from SQL with the standard {{ }} syntax. The LLM output is materialized as a temporary table, so you get full SQL capabilities:

#+src llm Inventory()
#+meta {
  :schema {
    :columns [{:name "product",  :type "STRING"},
              {:name "quantity", :type "INT"},
              {:name "in_stock", :type "BOOL"}]
  }
}
#+begin
Generate warehouse inventory for 20 electronics products.
#+end

#+src sql LowStockReport()
#+begin
  SELECT product, quantity
  FROM {{ Inventory() }}
  WHERE in_stock = true AND quantity < 10
  ORDER BY quantity ASC
#+end

Feeding Real Data into LLM Prompts​

Use print.Csv() to embed SQL query results as CSV text in an LLM prompt. This lets the AI analyze, enrich, or classify your actual data:

#+import "std/print"

#+src sql TopProducts()
#+begin
  SELECT product_name, total_sales, return_rate
  FROM product_metrics
  ORDER BY total_sales DESC LIMIT 20
#+end

#+src llm ProductInsights()
#+meta {
  :schema {
    :columns [{:name "product",    :type "STRING"},
              {:name "insight",    :type "STRING"},
              {:name "risk_level", :type "STRING",
                :description "LOW, MEDIUM, or HIGH"}]
  }
}
#+begin
Analyze the following product performance data and provide insights.
Flag products with high return rates as risks.

{{ print.Csv(TopProducts()) }}
#+end

Chaining LLM Blocks​

LLM blocks cannot directly reference other LLM blocks as table relations. Use print.Csv() to embed one LLM block's output as text in another's prompt, or use iterator() to loop over it row by row:

#+src llm PrioritizedFindings()
#+meta {
  :schema {
    :columns [{:name "id",       :type "INT"},
              {:name "priority", :type "INT",
                :description "1=highest, 5=lowest"},
              {:name "action",   :type "STRING"}]
  }
}
#+begin
Review the following findings and assign priorities.
{{ print.Csv(RawFindings()) }}
#+end

Validation and Caching​

  • Validation: By default, values are coerced to declared types on a best-effort basis. Set :validation "strict" to reject ambiguous values instead of guessing. Mark columns as :nullable false when null values should never appear.
  • Caching: LLM blocks are called once per unique (block, arguments) combination within an execution scope, and the result is reused across all references. Set :cache_results false if you need a fresh call on every reference.
  • Retries: If the model returns a malformed response, Coginiti re-issues the call automatically (up to 3 attempts). Model refusals are not retried.
warning

AI output varies between runs. Always write #+test blocks to validate LLM-generated data — test for value ranges, allowed enum values, referential integrity, and row counts.

Publishing LLM Results​

LLM blocks support the same publication metadata as SQL blocks, with one restriction: view publication is not supported. Table, CSV, and Parquet all work:

#+src llm DailySentiment()
#+meta {
  :schema {
    :columns [{:name "date_day",  :type "DATE"},
              {:name "sentiment", :type "FLOAT"},
              {:name "summary",   :type "STRING"}]
  },
  :publication {
    :type "table",
    :name "daily_sentiment",
    :incremental "merge",
    :unique_key ["date_day"]
  }
}
#+begin
Analyze today's customer feedback and generate a sentiment score.
#+end

For the full LLM blocks guide, see CoginitiScript: Working with LLM Blocks.

Project Organization​

Packages​

dbt projects have a rigid directory structure (models/, macros/, tests/, seeds/). CoginitiScript uses a package-based organization where a package is simply a directory in the analytics catalog.

Import packages with #+import directives:

#+import "sales/fact_sales"
#+import "sales/customer/dim_customer"
#+import "sales/fact_sales" as sales  -- alias

There is no external package registry equivalent to dbt Hub. Reusable code is shared through your organization's analytics catalog.

Organizing Your Code​

In dbt, the directory structure separates models, macros, and tests. In CoginitiScript, organize by domain instead. Let blocks, tests, and macros for a domain live together:

analytics/
  sales/
    fact_sales.csl         -- source blocks, transformations, and tests
    customer/
      dim_customer.csl     -- customer dimension blocks and tests
  marketing/
    campaigns.csl

What Doesn't Have an Equivalent​

dbt FeatureNotes
Python modelsNo Python runtime. LLM blocks cover AI/ML enrichment use cases. For other Python needs, use warehouse-native UDFs.
Seeds (.csv)Load reference data through your warehouse's bulk loading tools, or generate test data with LLM blocks.
Snapshots (SCD Type 2)Implement SCD logic manually in SQL blocks with incremental merge.
Source freshnessWrite a test block that queries metadata tables for load timestamps.
ExposuresDocument downstream dependencies in #+meta :doc.
dbt docs (generated site)The Coginiti analytics catalog serves as the documentation and discovery layer.
Hooks (pre/post)Use beforeAll, afterAll, beforeEach, afterEach in publication.Run().
dbt_project.ymlConfiguration lives in #+meta blocks and #+const declarations.

What CoginitiScript Adds​

Several CoginitiScript capabilities go beyond what dbt offers:

  • LLM blocks: Generate structured, schema-validated data from AI models directly inside your pipeline. Feed real data in, get enriched data back, publish it to tables.
  • Parameterized blocks: Blocks accept arguments, making them reusable across different inputs. dbt models are static; CoginitiScript blocks are callable functions.
  • File publication: Publish results directly to CSV or Parquet on local or cloud storage (S3, Azure Blob, GCS).
  • Parallel publication: The parallelism parameter in publication.Run() lets independent blocks execute concurrently.
  • Query tags: Built-in metadata tagging for cost allocation, monitoring, and audit trails across Snowflake, BigQuery, and Redshift.
  • Multi-statement blocks: Blocks can contain multiple SQL statements, enabling procedural patterns.
  • Iterator function: iterator() lets you loop over block results at compile time for dynamic SQL generation.

Quick Reference​

Taskdbt CoreCoginitiScript
Define a transformationmodels/my_model.sql#+src sql MyModel() ... #+end
AI-generated dataPython model (requires runtime)#+src llm MyData() ... #+end
Reference another transformation{{ ref('other_model') }}{{ OtherModel() }}
Reference a source{{ source('raw', 'orders') }}{{ RawOrders() }} (a block)
Set materialization{{ config(materialized='table') }}#+meta { :publication { :type "table" } }
Conditional logic{% if condition %} ... {% endif %}#+if condition then ... #+end
Loop{% for x in list %} ... {% endfor %}#+for x : list separator ... do ... #+end
Define a variable{{ var('name', default) }}#+const name = value; #+end
Write a testtests/my_test.sql#+test sql MyTest() ... #+end
Run testsdbt testtest.Run(packages=[...])
Import shared codepackages.yml#+import "path/to/package"
Build/publishdbt run / dbt buildpublication.Run(...)
Document a modelschema.yml description#+meta { :doc "..." }
Pass data to AI promptN/Aprint.Csv(SqlBlock())

Migration Tips​

If you're migrating an existing dbt project to CoginitiScript:

  1. Start with sources and staging. Convert source definitions and staging models first. These are usually the simplest and form the foundation your other blocks depend on.
  2. Group related models into files. Consider grouping tightly-coupled transformations (a raw source block, a cleaning block, and a business-logic block) into a single CoginitiScript file. If blocks are independently useful, keep them in separate files.
  3. Replace Jinja macros incrementally. Simpler Jinja macros translate directly to CoginitiScript macros or parameterized blocks. Complex Jinja logic (especially anything using Python built-ins via Jinja filters) may need to be rethought in pure SQL.
  4. Convert tests early. CoginitiScript tests are plain SQL, so they're straightforward to write. Converting dbt generic tests to explicit test blocks is tedious but simple.
  5. Organize by domain, not by dbt convention. Let blocks, tests, and macros for a domain live together in the same package rather than splitting them across models/, macros/, and tests/.
  6. Document blocks with #+meta :doc from day one. The analytics catalog benefits from documentation. As you migrate each model, add a :doc entry describing what the block does, what its parameters mean, and what it returns.
  7. Evaluate where LLM blocks can replace external tooling. If your dbt project has Python models for classification or enrichment, or you're running separate scripts for those tasks, LLM blocks may let you bring that logic into the same pipeline.

Next Steps​