CoginitiScript Reference

CoginitiScript is a block-based scripting language designed for data engineering and analytics workflows. It encourages code reusability, modular design, and follows software engineering best practices like the DRY (Don't Repeat Yourself) principle.

Overview

CoginitiScript is built around blocks - self-contained units of source code where each block represents code in a specific language. This design makes CoginitiScript files backward-compatible with existing language files:

Any SQL file is a valid CoginitiScript file
Any Python file is a valid CoginitiScript file
Any supported language file is a valid CoginitiScript file

Block Structure

Each block is defined using the following syntax:

#+src language name([arg=val...])[: returntype]
#+begin
  ... source code here
#+end

Source code outside defined blocks is treated as a default block (typically SQL). For example:

SELECT 1;
SELECT 2;

#+src sql foo()
#+begin
   SELECT 3;
#+end

SELECT * FROM {{ foo() }};

This represents three blocks:

Default SQL block with "SELECT 1" and "SELECT 2"
Named SQL block "foo" with "SELECT 3"
Default SQL block with "SELECT * FROM {{ foo() }}"

Code Blocks

Block Definition

Blocks are defined with a language, optional name, optional parameters, and optional return type:

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

Block Invocation

Reference blocks using {{ block-name(args) }} syntax:

SELECT * FROM {{ SalesPerStore() }} WHERE store_name = 'Main Store';

Block Execution

Anonymous blocks: Execute immediately when the script runs
Named blocks: Execute only when referenced via {{ block-name(args) }}
Development execution: Blocks execute when cursor is inside and run-at-cursor is used

-- This executes immediately
#+src sql
#+begin
  SELECT 1;
#+end

-- This executes only when called
#+src sql selectOne()
#+begin
  SELECT 1;
#+end

SELECT * FROM {{ selectOne() }}; -- Execution happens here

Block Parameters

Blocks can accept zero or more parameters:

#+src sql GetCustomerByEmail(country, domain)
#+begin
  SELECT *
  FROM dim_customers
  WHERE
    country = '{{ country }}'
    AND email LIKE '%{{ domain }}'
#+end

Calling with positional arguments:

SELECT * FROM {{ GetCustomerByEmail("USA", "gmail.com") }};

Calling with named arguments:

SELECT * FROM {{ GetCustomerByEmail(country="USA", domain="gmail.com") }};

note

You cannot mix positional and named arguments in a single block call.

Parameter placeholders

Parameter placeholders are a separate substitution mechanism from block formal parameters and {{ }} interpolation. A placeholder is a $- or $$-prefixed token that is filled in from the execution context — the parameters supplied when a script is run — rather than from a block call. Placeholders are not declared in the block header; they are recognized directly in block body text. They are most often used by LLM blocks and other contexts that substitute external values into the body.

A placeholder name is one or more of the characters a-z, A-Z, 0-9, and _. There are two forms:

$name — the regular form.
$$name — the alternative form.

#+src sql
#+begin
  SELECT $p1 FROM accounts WHERE region = $p2 AND tier = $$alt_p1;
#+end

Default values

Append {default} to a placeholder to supply a value used when the caller does not provide one. The default applies to both forms — $name{default} and $$name{default} — and the default text is everything between the braces:

#+src sql
#+begin
  SELECT *
  FROM events
  WHERE event_type = '$event_type{login}'
    AND created_at >= '$$since{2024-01-01}';
#+end

Regular vs. alternative is mode-based

The two forms are never substituted at the same time. The execution context runs in one parameter-parsing mode, and only the matching form is substituted:

In regular mode, only $name placeholders are substituted; any $$name is left in the body as literal text.
In alternative mode, only $$name placeholders are substituted; any $name is left as literal text.
When parameter parsing is disabled, neither form is substituted.

How a value is resolved

The values that fill placeholders come from the execution context — the parameter values you enter in the UI when running a script, or the values configured for a scheduled job run.

When a placeholder is substituted, its value is resolved in this order:

The value supplied for that placeholder by the execution context.
The {default} value, if one is declared.
Otherwise, execution fails because the parameter has no value.

tip

Placeholders are the mechanism behind the $$table_name, $$column_name, and $$filter_value{default_value} tokens shown in LLM block prompt bodies. They work the same way in any block language, not only in LLM blocks.

Return Types

Blocks support two return types:

recordset (default)

Returns the result of the last SELECT statement:

#+src sql CustomerData()
#+begin
  -- Multiple statements allowed
  CREATE TEMP TABLE temp_customers AS SELECT * FROM customers;
  
  -- This result is returned
  SELECT * FROM temp_customers WHERE active = true;
#+end

void

Performs actions without returning values:

#+src sql GenCustomersTable(cnt, minAge, maxAge): void
#+meta {
  :doc "Generate customers table with random data"
}
#+begin
  DROP TABLE IF EXISTS customers;
  CREATE TABLE customers(
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
  );

  INSERT INTO customers
  SELECT
    generate_series AS id,
    'Customer ' || generate_series AS name,
    FLOOR(RANDOM() * ({{ maxAge }} - {{ minAge }} + 1))::INTEGER + {{ minAge }} AS age
  FROM generate_series(1, {{ cnt }});
#+end

Execution Mechanics

When a block is referenced, CoginitiScript materializes the block's results using one of several strategies, depending on the SQL complexity and platform capabilities:

Default Strategy: CTE (Common Table Expression)

By default, CoginitiScript uses CTEs for referenced blocks:

-- Original code
#+src sql selectOne()
#+begin
  SELECT 1 AS one;
#+end

SELECT * FROM {{ selectOne() }};

-- Generated SQL (CTE approach)
WITH _selectOne AS (
  SELECT 1 AS one
)
SELECT * FROM _selectOne;

Fallback Strategy: Temporary Tables

When the final SQL becomes too complex for CTEs or platform limitations prevent CTE usage, CoginitiScript falls back to temporary tables:

-- Generated SQL (temp table approach)
CREATE TEMPORARY TABLE _random_name_ AS
SELECT 1 AS one;

SELECT * FROM _random_name_;

Ephemeral Tables

For certain platforms or complex scenarios, CoginitiScript may create standard tables that are automatically cleaned up after execution (ephemeral tables):

-- Generated SQL (ephemeral table approach)  
CREATE TABLE _ephemeral_random_name_ AS
SELECT 1 AS one;

SELECT * FROM _ephemeral_random_name_;

-- Automatic cleanup
DROP TABLE _ephemeral_random_name_;

The strategy selection is automatic and transparent to the user, ensuring optimal performance while maintaining consistent behavior across different database platforms.

Result Caching

By default, block results are cached within an execution. When the same block call (with the same arguments) is referenced more than once in the script being executed, the block runs once and subsequent references reuse the materialized result. Arguments are part of the cache key — Foo("hello") and Foo("bye") are distinct cache entries.

Caching applies to both table-style references ({{ block() }}) and recordset reads (iterator(block())). The materialization follows the rules described in Execution Mechanics: a CTE when all references fit in a single statement, otherwise a temporary table (or an ephemeral table on platforms without temp table support, such as Athena, BigQuery, Databricks, and Trino).

Disable caching with :cache_results false:

#+src sql Number()
#+meta {
  :cache_results false
}
#+begin
  SELECT 1 + (random() * 999999)::INTEGER AS num
#+end

-- Each reference re-executes Number(), so t1_num and t2_num differ
SELECT t1.num AS t1_num, t2.num AS t2_num
FROM {{ Number() }} t1
CROSS JOIN {{ Number() }} t2;

With caching enabled (the default), the same script produces identical values for t1_num and t2_num because Number() is executed once and the result is reused.

Cache scope

The cache is scoped to the code currently being executed:

Whole-script execution considers all references in the script. A block referenced more than once is cached.
Run at cursor considers only the statement(s) under the cursor. A block referenced only once at the cursor is not cached, even if the surrounding script references it again elsewhere.
publication.Run and test.Run have isolated cache scopes. Caching inside each call is determined solely by the code invoked within that call. A cache built inside publication.Run is not visible to statements outside it, and a cache built in the surrounding script is not reused inside publication.Run or test.Run.

For example:

#+import "std/publication"

#+src sql Number()
#+begin
  SELECT 1 + (random() * 999999)::INTEGER AS num
#+end

SELECT t1.num AS t1_num, t2.num AS t2_num
FROM {{ Number() }} t1
CROSS JOIN {{ Number() }} t2;

#+src sql PublishNumbers()
#+meta {
  :publication { :type "table", :name "numbers" }
}
#+begin
  SELECT * FROM {{ Number() }};
#+end

{{ publication.Run(blocks=[PublishNumbers]) }};

Number() is cached for the CROSS JOIN query (two references in the surrounding script scope). That cache is not reused inside publication.Run — PublishNumbers has its own scope and references Number() only once, so no caching occurs there. Conversely, if Number() were cached inside publication.Run (because PublishNumbers referenced it twice), a follow-up SELECT * FROM {{ Number() }} outside the publication.Run call would not reuse that cache and would re-execute the block.

Limitations

Snowflake: Requires explicit column aliases in SELECT statements for referenced blocks
Explain plans: Cannot be executed for statements with block references

Recursion and circular references

CoginitiScript does not allow recursion. A code block, macro, or test cannot reference itself, either directly or through a chain of other references. Likewise, packages cannot form an import cycle. Cycles are detected at parse time, so the script fails with an error before any statement runs, and the error names the references that form the cycle.

Block, macro, and test cycles

A cycle exists when following references leads back to something already in the chain — a block that calls itself, two blocks that call each other, or a longer loop. Macros are checked the same way, and a cycle may mix macros and #+src blocks. The check also follows blocks passed as arguments: if a block is passed to another block that then invokes it, and the path loops back, it is rejected.

#+src sql foo()
#+begin
  select * from {{ bar() }};
#+end

#+src sql bar()
#+begin
  select * from {{ foo() }};   -- foo references bar, bar references foo
#+end

Package import cycles

A package's #+import declarations must form an acyclic graph. A package cannot import itself, and a chain of imports cannot loop back to a package already in the chain.

Packages

A package is a directory containing CoginitiScript files and/or other packages. Files in the catalog root belong to the "main" package.

Public/Private Blocks

CoginitiScript follows Go-like identifier export rules:

Public: Identifiers starting with uppercase letters
Private: All other identifiers

#+src sql foo()  -- Private
#+begin
   SELECT 1;
#+end

#+src sql Bar()  -- Public
#+begin
  SELECT 2;
#+end

Importing Packages

Use the #+import directive:

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

Reference imported blocks with package qualification:

#+import "sales/customer"
#+import "sales/fact_sales" as sales

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

note

Package names with spaces or special characters require aliases:

#+import "Users/username/Sales Interaction" as sales
SELECT * FROM {{ sales.BlockName() }};

Projects

Packages can be grouped into a versioned, releasable project with shared dependencies and configuration. See the Coginiti Projects Reference for the project.toml manifest and project import paths.

Metadata Properties

Blocks support arbitrary metadata as key-value pairs:

#+src sql SalesPerCountry()
#+meta {
   :author "John Doe",
   :version "1.2.0",
   :last_updated "2024-01-15"
}
#+begin
   SELECT country, SUM(sales) FROM fact_sales GROUP BY country;
#+end

Documentation

Use the :doc metadata key for documentation:

#+src sql CalculateMetrics(start_date, end_date)
#+meta {
  :doc "Calculate performance metrics for a date range.
  
  This function computes key performance indicators including revenue,
  conversion rates, and customer acquisition costs for the specified
  date range. 
  
  Parameters:
  - `start_date`: Start date for the analysis (YYYY-MM-DD format)
  - `end_date`: End date for the analysis (YYYY-MM-DD format)
  
  Returns a recordset with metrics aggregated by day."
}
#+begin
  SELECT
    DATE(created_at) as metric_date,
    SUM(revenue) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers
  FROM fact_orders
  WHERE DATE(created_at) BETWEEN '{{ start_date }}' AND '{{ end_date }}'
  GROUP BY DATE(created_at)
  ORDER BY metric_date;
#+end

Script Metadata

In addition to per-block metadata, CoginitiScript supports script-level metadata — a single #+meta declaration at the very top of a file that applies to the whole script. It must come before any #+import directive and before any #+src block.

#+meta {
  :run_condition <expression>
}

#+import "std/runtime"

#+src sql Foo()
#+begin
  SELECT 1;
#+end

A file may contain at most one script-level #+meta. A second top-level #+meta, or one placed after an #+import or a block declaration, is rejected at parse time.

`:run_condition`

:run_condition controls whether the script is runnable. When the expression evaluates to false, blocks declared in that file are skipped — any reference to them from another file in the same package falls through to other runnable definitions, and an attempt to execute a non-runnable block directly fails with "Cannot execute code block. The :run_condition is not met, so the script is not runnable".

The value is any boolean expression — a literal or a CSL expression that returns a boolean:

#+meta { :run_condition false }

#+meta { :run_condition runtime.IsPostgres() }

The runtime package used above is part of the standard library and is implicitly available — see std/runtime Package.

The typical use case is composing a package from multiple platform-specific files. Each file gates itself on the current platform, and an importer transparently picks the runnable variant:

-- file: postgres_impl
#+meta { :run_condition runtime.IsPostgres() }

#+src sql BuildIndex(): void
#+begin
  CREATE INDEX ON events (user_id, event_ts);
#+end

-- file: bigquery_impl
#+meta { :run_condition runtime.IsBigQuery() }

#+src sql BuildIndex(): void
#+begin
  CREATE OR REPLACE TABLE events
  CLUSTER BY user_id AS SELECT * FROM events;
#+end

-- file: index
{{ BuildIndex() }};

Running index against Postgres invokes the postgres_impl variant; running it against BigQuery invokes the bigquery_impl variant. Files whose condition is false contribute no blocks.

note

Evaluating a :run_condition that depends on the active connection (e.g. uses runtime.Platform() or runtime.Is<Platform>()) requires a connection to be selected. In contexts that resolve scripts without one — lineage analysis, dry runs — Coginiti raises "Connection should be specified when :run_condition is used".

String literals

CoginitiScript has two kinds of string literals: double-quoted strings, which process escape sequences and normalize multiline indentation, and raw backtick strings, which are taken verbatim.

Double-quoted strings

A double-quoted string is delimited by " and may span multiple lines. It recognizes exactly the following escape sequences:

Escape	Meaning
`\"`	Double quote
`\\`	Backslash
`\n`	Line feed (newline)
`\r`	Carriage return
`\t`	Tab
`\b`	Backspace
`\f`	Form feed

Any other character following a backslash is invalid and raises a parse error — unrecognized escapes are not passed through literally. A backslash that is itself escaped is not an escape introducer: the source "foo\\nfoo" produces the literal text foo\nfoo (a backslash followed by n), because \\ is consumed as a single backslash and the following n is an ordinary character.

Multiline re-indentation

When a double-quoted literal spans more than one physical line, CoginitiScript removes the incidental indentation that comes from laying the literal out neatly in source. The algorithm is:

The first line is never used to compute the common indentation. It only has its trailing whitespace removed; any leading whitespace on the first line is preserved.
The common (minimum) leading-whitespace count is computed from the remaining lines. Blank lines are ignored when computing this minimum, except the last line, whose leading whitespace is always counted even if it is blank.
That many leading whitespace characters are removed from each line after the first (a line shorter than the common indent is left unchanged), and every line has its trailing whitespace removed.
Blank interior lines are preserved.

Re-indentation runs before escape sequences are translated. As a consequence, a string written on a single physical source line is returned unchanged by the re-indentation step even if it contains \n escapes — the newlines appear only after escape translation, and any leading spaces on such a line are preserved. Empty strings and single-line strings are likewise returned unchanged by re-indentation.

For example, this literal:

SELECT * FROM {{ block("abc
       xyz
   foo") }}

produces:

abc
    xyz
foo

The first line (abc) is kept as written; the common indentation of the remaining lines (3 spaces) is stripped, so xyz (7 spaces) becomes xyz (4 spaces) and foo becomes foo.

Raw strings

A raw string is delimited by backticks (`) and is taken exactly as written: no escape sequences are interpreted and no indentation normalization is applied. The only character a raw string cannot contain is a backtick, which always ends it. Use raw strings for content such as Windows paths or regular expressions where backslashes should be preserved literally.

:path `C:\Projects\Reports\sales_report.csv`

Publication

Publication materializes block results as external database objects or files.

Database Publication

Table Publication

#+src sql salesFact()
#+meta {
  :publication {
    :type "table",
    :name "mart_sales_fact",
    :schema "analytics"  -- Optional
  }
}
#+begin
  SELECT * FROM raw_sales_data;
#+end

View Publication

#+src sql customerSummary()
#+meta {
  :publication {
    :type "view",
    :name "customer_summary_view"
  }
}
#+begin
  SELECT customer_id, SUM(order_value) as total_value
  FROM orders GROUP BY customer_id;
#+end

Platform-Specific Properties

Database publications (:type "table" or :type "view") can include a platform-specific map under a platform key. Properties inside that map are only applied when the publication runs against the matching platform — switching the active connection to a different platform causes the corresponding map to be ignored.

#+src sql Events()
#+meta {
  :publication {
    :type "table",
    :name "events",
    :postgres { :unlogged true },
    :bigquery { :partition_by "DATE(created_at)" }
  }
}
#+begin
  SELECT * FROM raw_events;
#+end

Each platform validates its own map; unknown keys or wrong value types raise an error before the publication runs.

Postgres — `:postgres`

Property	Type	Description
`:unlogged`	boolean	Create the table as `UNLOGGED`
`:partition_type`	string	`"hash"`, `"range"`, or `"list"`
`:partition_by`	list of strings	Columns to partition by. Required when `:partition_type` is set
`:partitions`	list of maps	Partition definitions. Required when `:partition_type` is set. Each entry has `:name` (string) and `:definition` (string)

:postgres {
  :partition_type "hash",
  :partition_by ["col1"],
  :partitions [
    { :name "events_0", :definition "FOR VALUES WITH (MODULUS 2, REMAINDER 0)" },
    { :name "events_1", :definition "FOR VALUES WITH (MODULUS 2, REMAINDER 1)" }
  ]
}

Redshift — `:redshift`

Property	Type	Description
`:dist_style`	string	`"EVEN"`, `"KEY"`, `"ALL"`, or `"AUTO"`
`:dist_key`	string	Distribution column. Required when `:dist_style` is `"KEY"`; not allowed with other dist styles
`:sort_style`	string	`"COMPOUND"` or `"INTERLEAVED"`
`:sort_key`	list of strings	Sort columns. Required when `:sort_style` is set

:redshift {
  :dist_style "KEY",
  :dist_key "customer_id",
  :sort_style "COMPOUND",
  :sort_key ["order_date", "customer_id"]
}

BigQuery — `:bigquery`

Property	Type	Description
`:cluster_by`	list of strings	Up to 4 clustering columns
`:partition_by`	string	Partition expression (column name or function like `DATE(ts)`, `RANGE_BUCKET(...)`)
`:iceberg`	map	Iceberg-managed table configuration (see below)
`:options`	map	Additional table options (see below)

:iceberg map:

Key	Type	Description
`:connection`	string	Required. BigQuery connection name
`:storage_uri`	string	Required. Cloud Storage URI for table data
`:file_format`	string	Optional. Currently only `"PARQUET"` is supported

:options map:

Key	Type	Description
`:partition_expiration_days`	number	Days after which partitions expire. Requires `:partition_by`
`:require_partition_filter`	boolean	Force every query to filter on the partition column. Requires `:partition_by`
`:expiration_timestamp`	string	Table expiration timestamp
`:kms_key_name`	string	Cloud KMS key for encryption
`:friendly_name`	string	Display name
`:description`	string	Table description
`:labels`	map of string → string	Resource labels
`:default_rounding_mode`	string	Default rounding mode for NUMERIC/BIGNUMERIC columns
`:enable_change_history`	boolean	Enable change history
`:max_staleness`	string	Maximum staleness interval

:bigquery {
  :partition_by "DATE(created_at)",
  :cluster_by ["user_id"],
  :options {
    :description "Daily events",
    :labels { "env": "prod" },
    :require_partition_filter true
  }
}

Snowflake — `:snowflake`

Property	Type	Description
`:table_type`	string	`"permanent"` (default) or `"transient"`
`:secure`	boolean	Mark a view as secure. Applies to `:type "view"` publications
`:cluster_by`	list of strings	Clustering columns
`:iceberg`	map	Iceberg table configuration (see below)
`:dynamic`	map	Dynamic table configuration (see below)

:iceberg map:

Key	Type	Description
`:base_location`	string	Required. Storage path for Iceberg data
`:external_volume`	string	Optional. Snowflake external volume name

:dynamic map:

Key	Type	Description
`:target_lag`	string	Required. Target lag (e.g. `"1 hour"`, `"DOWNSTREAM"`)
`:warehouse`	string	Required. Snowflake warehouse used to refresh the table
`:refresh_mode`	string	Optional. `"auto"`, `"full"`, or `"incremental"`
`:initialize`	string	Optional. `"on_create"` or `"on_schedule"`

:snowflake {
  :table_type "transient",
  :cluster_by ["date_day"],
  :dynamic {
    :target_lag "1 hour",
    :warehouse "ANALYTICS_WH",
    :refresh_mode "auto"
  }
}

Iceberg Catalog (Snowflake) — `:iceberg_snowflake`

For publications routed through an Iceberg Catalog connection backed by Snowflake. Accepts the same properties as Snowflake — :table_type, :secure, :cluster_by, :iceberg, and :dynamic.

:iceberg_snowflake {
  :secure true
}

Athena — `:athena`

Property	Type	Description
`:iceberg`	map	Iceberg table configuration (see below)

:iceberg map:

Key	Type	Description
`:location`	string	Required. S3 location for Iceberg data
`:format`	string	Optional. `"PARQUET"`, `"ORC"`, or `"AVRO"`
`:compression`	string	Optional. `"GZIP"`, `"LZ4"`, `"SNAPPY"`, `"ZLIB"`, `"ZSTD"`, or `"NONE"`
`:partitioning`	list of strings	Optional. Partitioning expressions

:athena {
  :iceberg {
    :location "s3://my-bucket/iceberg/events/",
    :format "PARQUET",
    :compression "SNAPPY",
    :partitioning ["bucket(16, id)"]
  }
}

Trino — `:trino`

Property	Type	Description
`:iceberg`	map	Iceberg table configuration (see below)

:iceberg map:

Key	Type	Description
`:location`	string	Optional. Storage location for the table
`:format`	string	Optional. `"PARQUET"`, `"ORC"`, or `"AVRO"`
`:partitioning`	list of strings	Optional. Partitioning expressions
`:sorted_by`	list of strings	Optional. Sort columns within each file

:trino {
  :iceberg {
    :format "PARQUET",
    :partitioning ["day(event_ts)"],
    :sorted_by ["user_id"]
  }
}

File Publication

CSV Publication

#+src sql salesReport()
#+meta {
  :publication {
    :type "csv",
    :path "/reports/sales_report.csv",
    :connection "S3 Connection",  -- Optional
    :options {
      :delimiter ",",
      :null_value "",
      :quote_char "\"",
      :overwrite true,
      :header true,
      :encoding "UTF-8",
      :compression "GZIP"
    }
  }
}
#+begin
  SELECT * FROM sales_summary;
#+end

Windows Paths

Use raw strings (backticks) for Windows paths to avoid escaping issues:

:path `C:\Projects\Reports\sales_report.csv`

Parquet Publication

#+src sql salesData()
#+meta {
  :publication {
    :type "parquet",
    :path "/data/sales_data.parquet",
    :options {
      :row_group_size 134217728,  -- 128 MiB
      :page_size 1048576,         -- 1 MiB
      :overwrite false,
      :compression "snappy"       -- snappy, gzip, none
    }
  }
}
#+begin
  SELECT * FROM fact_sales;
#+end

Object Store Publication

CoginitiScript supports publishing to cloud object stores like Amazon S3, Azure Blob Storage, and Google Cloud Storage:

#+src sql DataExport()
#+meta {
  :publication {
    :type "csv",
    :path "analytics-bucket/exports/daily_report.csv",
    :connection "AWS S3 Connection"
  }
}
#+begin
  SELECT 
    customer_id,
    order_date,
    total_amount
  FROM fact_orders
  WHERE order_date = CURRENT_DATE;
#+end

Connection Configuration: Object store connections must be configured with appropriate credentials and permissions in Coginiti before use. The connection name references the configured connection profile.

Referencing Published Blocks

When referencing blocks with database publication, the published table/view is used instead of executing the block:

-- This query
SELECT * FROM {{ salesFact() }};

-- Becomes this
SELECT * FROM mart_sales_fact;

warning

Referencing blocks with file publication (CSV/Parquet) is not supported and will result in a runtime error.

Incremental Publication

Incremental publication publishes only new data since the last run. Three strategies are supported:

Append Strategy

#+src sql DailyActiveUsers()
#+meta {
  :publication {
    :type "table",
    :name "daily_active_users",
    :incremental "append"
  }
}
#+begin
  SELECT
    DATE_TRUNC('day', visit_date) as date_day,
    COUNT(DISTINCT user_id) AS users_count
  FROM visits
  #+if publication.Incremental() then
    WHERE visit_date >= (SELECT MAX(date_day) FROM {{ publication.Target() }})
  #+end
  GROUP BY date_day;
#+end

Merge Strategy

#+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

Conditional Merge Strategy

#+src sql CustomerDimension()
#+meta {
  :publication {
    :type "table",
    :name "dim_customer",
    :incremental "merge_conditionally",
    :unique_key ["customer_id"],
    :update_on_changes_in ["first_name", "last_name", "email"]
  }
}
#+begin
  SELECT
    customer_id,
    first_name,
    last_name,
    email,
    updated_at
  FROM source_customers;
#+end

Merge Execution Strategy by Platform

Both :incremental "merge" and :incremental "merge_conditionally" dispatch to one of two execution strategies depending on the target platform's native MERGE support and on whether :update_on_changes_in is used:

Strategy	Platforms	Mechanics
`MERGE`	Snowflake, MS SQL Server, Oracle, BigQuery, Trino, Databricks, Iceberg, Netezza, DB2, PostgreSQL ≥ 15, Redshift (default)	A single native `MERGE INTO target USING source` statement. Matched rows are `UPDATE`d in place; unmatched source rows are `INSERT`ed.
`DELETE_INSERT`	PostgreSQL < 15, Greenplum, Yellowbrick, Redshift (when `:update_on_changes_in` is set)	The source query is materialized into a stage table, matched rows are deleted from the target, then the stage is re-inserted in a single transaction.

Notes:

PostgreSQL dispatches by server version: 15 and later use native MERGE; older versions fall back to DELETE_INSERT.
Redshift uses MERGE by default, but switches to DELETE_INSERT whenever :update_on_changes_in is set, because Redshift's MERGE INTO does not accept on-match conditions with a subquery source.
Athena and Hive do not support merge publications at all — :incremental "merge" and :incremental "merge_conditionally" are rejected.

The choice of strategy is mostly transparent for plain merge publications, but it changes the semantics of :exclude_columns (see below) — review that section before relying on excluded columns for surrogate keys, audit timestamps, or other "set-once" values.

Excluding Columns from Merge

:exclude_columns keeps the listed target columns out of the merge so their existing values — or the column's DEFAULT — are preserved instead of being overwritten by the source query.

#+src sql CustomerDimension()
#+meta {
  :publication {
    :type "table",
    :name "dim_customer",
    :incremental "merge",
    :unique_key ["customer_id"],
    :exclude_columns ["created_at", "surrogate_id"]
  }
}
#+begin
  SELECT customer_id, first_name, last_name, email
  FROM source_customers;
#+end

Validation

Valid only when :incremental "merge" or :incremental "merge_conditionally".
At least one non-key column must remain updatable. Excluding every non-key column fails fast with: "All non-key columns are excluded: merge has no columns to update".
Listing a column that is not in the target is a no-op.
Listing a unique-key column is a no-op — keys are never updated by merge.
Identifier case follows the platform's rules. Quote names (e.g. "AuditTs") when exact-case matching is required.

Behavior matrix

The semantics of :exclude_columns depend on the merge execution strategy chosen for the target platform (see Merge Execution Strategy by Platform):

Scenario	`MERGE` strategy	`DELETE_INSERT` strategy
Excluded column in source query	Existing value preserved; new rows get `DEFAULT` / `NULL`	Existing value preserved (carried via stage); new rows get `NULL`
Excluded column missing from source	Existing value preserved; new rows get `DEFAULT` / `NULL`	Existing value NOT preserved; all rows (matched and new) get `DEFAULT` / `NULL`

DELETE_INSERT preservation gap

On DELETE_INSERT platforms, every matched row is deleted and re-inserted. An excluded column that is missing from the source query cannot survive the round-trip — DEFAULT (or NULL) is written for matched rows, not the prior value. Concretely:

Surrogate keys (SERIAL, IDENTITY, AUTOINCREMENT) get a fresh value on each merge, breaking downstream foreign keys.
Audit timestamps with DEFAULT NOW() / CURRENT_TIMESTAMP reset on every merge — not only on insert.
NOT NULL columns without a default fail the INSERT for new rows.

Unchanged rows — those not present in the source delta — are never touched, so their excluded-column values are preserved regardless of strategy. Only rows matched by the merge (and therefore re-inserted under DELETE_INSERT) are affected.

For true "set-once" preservation under DELETE_INSERT, include the column in the source query with the value you want to write.

Common use cases

Audit timestamps (created_at, updated_at) with server-generated DEFAULT NOW().
Surrogate keys (SERIAL / IDENTITY) that must remain stable — use a MERGE-strategy platform.
Lineage columns (source_system, loaded_by) set on first load.
Computed or derived columns that the source query intentionally does not recompute.
Schema-drift tolerance — the target has columns the source query does not yet produce.

Related metadata

:unique_key — required for :incremental "merge"; defines the match condition.
:update_on_changes_in — narrows matched-row updates to rows where the listed columns actually changed; composes with :exclude_columns. On Redshift, setting this also forces the DELETE_INSERT strategy.

Executing Publications

Use the std/publication package to execute publications programmatically:

#+import "std/publication"
#+import "analytics/sales"
#+import "analytics/customers"

{{
  publication.Run(
    blocks=[sales.SalesDetail, sales.SalesHeader],
    packages=[customers],
    postOp=grantSelectPermissions
  )
}}

#+src sql grantSelectPermissions(): void
#+begin
  #+if publication.Type() == "table" || publication.Type() == "view" then
    GRANT SELECT ON {{ publication.Target() }} TO GROUP "analysts";
  #+end
#+end

Parameters:

blocks: List of specific code blocks to publish (each block must contain publication metadata configuration)
packages: List of packages to publish (all blocks with publication metadata)
parallelism: Maximum degree of concurrency for publication block execution within each dependency step (default: 1 (sequential execution))
fullRefresh: Boolean flag to force full refresh regardless of incremental settings (default: false)
beforeAll: Block to execute before any publications start (must return void)
beforeEach: Block to execute before each publication starts (must return void)
afterEach: Block to execute after each publication completes (must return void)
afterAll: Block to execute after all publications complete (must return void)
postOp: [DEPRECATED] Use afterEach instead - alias for afterEach parameter

Parameter Requirements

At least one of blocks or packages must be provided
All lifecycle blocks (beforeAll, beforeEach, afterEach, afterAll, postOp) must return void
Only user-defined packages are allowed (not built-in/standard library packages)

Parallelism parameter

The parallelism parameter controls the level of concurrent execution when running publication blocks. It determines how many publication blocks can be executed simultaneously within each execution step of the publication dependency graph.

note

A step in the execution block graph represents a set of blocks that have no dependencies on each other and can execute safely in parallel. The system automatically analyzes block dependencies and groups blocks into sequential steps, where:

All blocks within a step can run concurrently
All dependencies from previous steps must complete before the next step begins
Each step respects the dependency constraints defined by block references

Consider publication blocks with dependencies:

a -> d -> g
b -> d -> g
c -> e
f -> h
i (no dependencies)

Step grouping with parallelism > 1:

Step 1: [a, b, c, f, i] - No dependencies
Step 2: [d, e, h] - Dependencies from Step 1 satisfied
Step 3: [g] - Depends on d from Step 2

Accepted Values:

Range: Integer between 1 and 32 (inclusive)
Default behavior: When null or not provided, defaults to 1 (sequential execution)

Execution Behavior:

parallelism = 1: blocks execute one after another
parallelism > 1: blocks within each step execute concurrently using a thread pool

Performance Benefits:

Faster Execution: When parallelism > 1, independent publication blocks within the same execution step run concurrently, significantly reducing overall execution time
Resource Utilization: Better utilizes available system resources (CPU, database connections) by executing multiple blocks simultaneously
Scalability: Allows scaling publication execution based on system capacity and workload requirements

Use Cases:

Large Publications: When publishing many independent tables/views, parallel execution can dramatically reduce total runtime
Resource-Rich Environments: Systems with sufficient database connections and processing power can benefit from higher parallelism
Time-Sensitive Workflows: Critical data pipelines that need to complete within specific time windows

Example Usage:

-- Execute up to 4 blocks concurrently
{{
  publication.Run(
    blocks=[customers.CustomerAnalysis, sales.SalesReport],
    packages=[inventory, finance],
    parallelism=4
  )
}}

Important Considerations:

Dependencies: Blocks with dependencies still execute in the correct order - parallelism only affects independent blocks within the same execution step
Resource Limits: Higher parallelism requires more database connections and system resources
Error Handling: If any block fails during parallel execution, the entire publication run is aborted

Built-in functions available:

publication.Target(): Returns the target table/view name
publication.Type(): Returns publication type ("table", "view", "csv", "parquet")
publication.Incremental(): Returns true if block should execute in incremental mode, false otherwise

publication.Incremental() Function

The publication.Incremental() function is a built-in function available within publication blocks that returns a boolean indicating whether the current block should execute in incremental mode.

Function Behavior:

Returns: true if the block should execute incrementally, false for full refresh

Incremental mode is enabled when ALL of the following conditions are met:

The block has incremental publication configuration (:incremental "append" or :incremental "merge")
The fullRefresh parameter is not set to true in publication.Run()
The target table/view already exists in the database

Usage Context:

Only available within publication blocks during execution
Cannot be called outside of publication context
Typically used in conditional #+if statements to modify query behavior

Example Usage:

The function is commonly used to implement different logic for initial vs. incremental runs:

#+src sql DailyActiveUsers()
#+meta {
  :publication {
    :type "table",
    :name "daily_active_users",
    :incremental "append"
  }
}
#+begin
  SELECT
    DATE_TRUNC('day', visit_date) as date_day,
    COUNT(DISTINCT user_id) AS users_count,
    '{{ publication.Incremental() }}' AS was_incremental  -- Shows true/false
  FROM visits
  #+if publication.Incremental() then
    WHERE visit_date >= (SELECT MAX(date_day) FROM {{ publication.Target() }})
  #+end
  GROUP BY date_day;
#+end

Truth Table:

Incremental Config	fullRefresh	Target Exists	Result
`"append"`	`false`	`true`	`true`
`"append"`	`false`	`false`	`false`
`"append"`	`true`	`false`	`false`
`"append"`	`true`	`true`	`false`
`"merge"`	`false`	`true`	`true`
`"merge"`	`false`	`false`	`false`
`"merge"`	`true`	`false`	`false`
`"merge"`	`true`	`true`	`false`
(none)	any	any	`false`

Error Conditions:

Execution Context Error: Function must be called from within an execution context
Publication Context Error: Function must be called from within a publication block context

Lifecycle hooks

publication.Run accepts four optional lifecycle hooks. Each must reference a void-returning code block. They fire in a fixed order around block execution:

beforeAll — once, before any publication block runs.
For every publication block, in dependency-graph order: beforeEach → the publication block → afterEach.
afterAll — once, after all publication blocks have completed.

postOp is a deprecated alias for the afterEach parameter; prefer afterEach. Each hook must reference a code block that returns void, otherwise publication.Run fails with '<hook>' code block should return void (for example 'afterEach' code block should return void).

#+import "std/publication"
#+import "analytics/sales"

{{
  publication.Run(
    packages=[sales],
    beforeAll=startRun,
    beforeEach=logStart,
    afterEach=grantSelectPermissions,
    afterAll=finishRun
  )
}}

#+src sql startRun(): void
#+begin
  INSERT INTO publish_audit (event, ts) VALUES ('run_started', NOW());
#+end

#+src sql logStart(): void
#+begin
  INSERT INTO publish_audit (event, target, ts)
  VALUES ('block_started', '{{ publication.Target() }}', NOW());
#+end

#+src sql grantSelectPermissions(): void
#+begin
  #+if publication.Type() == "table" || publication.Type() == "view" then
    GRANT SELECT ON {{ publication.Target() }} TO GROUP "analysts";
  #+end
#+end

#+src sql finishRun(): void
#+begin
  INSERT INTO publish_audit (event, ts) VALUES ('run_completed', NOW());
#+end

publication.Target() and publication.Type() Functions

Two built-in functions expose the configuration of the publication block currently being executed:

publication.Type() — returns the publication type as a string: one of "table", "view", "csv", or "parquet".
publication.Target() — for table and view publications, returns the resolved, schema-qualified target relation. For csv and parquet publications the target is a file location rather than a relation, so publication.Target() returns null.

Where the context is available

The publication context is established only for code that runs as a publication, namely:

the publication block's own body during execution, and
the beforeEach and afterEach hooks, which run within the context of the block they wrap.

The context is not established for beforeAll and afterAll, because those hooks run once for the whole run rather than for a specific publication. Calling either context function outside a publication context fails (the same constraint that applies to publication.Incremental()):

publication.Type() → 'publication.Type' is empty. It should be called only from the publication context.
publication.Target() → 'publication.Target' is empty. It should be called only from the publication context.

Because csv / parquet targets are not relations, guard on publication.Type() before referencing publication.Target():

#+src sql grantSelectPermissions(): void
#+begin
  #+if publication.Type() == "table" || publication.Type() == "view" then
    GRANT SELECT ON {{ publication.Target() }} TO GROUP "analysts";
  #+end
#+end

Tests

CoginitiScript supports built-in testing for data quality and validation.

Defining Tests

#+test sql TestCustomerEmailFormat()
#+begin
  SELECT * FROM {{ customerData() }} WHERE email IS NULL OR email NOT LIKE '%@%';
#+end

Test Results

Tests return three types of objects:

Nil or empty Dataset: Test passes
Non-empty Dataset or Error: Test fails

Running Tests

Individual test: Place cursor on test block and execute

All tests: Use run-all command

Programmatic execution:

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

-- Run specific tests
{{ test.Run(tests=[customers.TestEmailFormat, customers.TestPhoneNumbers]) }}

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

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

Options for onFailure:

test.Stop: Stop execution on test failure (default)
test.Continue: Continue execution despite test failures

LLM Blocks

LLM blocks bring AI-generated data directly into CoginitiScript pipelines. An LLM block sends a prompt to a large language model and returns structured tabular data that SQL blocks can query, join, and transform — just like any other data source.

Which model is used

LLM blocks use the AI provider and model configured as the active AI Assistant in your Coginiti instance (see How to Configure AI Assistant). There is no per-block provider or model selection — every LLM block in a script uses the same active AI Assistant, so changing the model means changing that configuration. If no provider is configured, the block fails with AI Assistant is not configured….

Defining an LLM Block

LLM blocks use the #+src llm directive:

#+src llm generate_products()
#+meta {
  :schema {
    :columns [{:name "id", :type "INT"},
              {:name "name", :type "STRING"},
              {:name "price", :type "DECIMAL(10,2)"}]
  }
}
#+begin
Generate 5 sample product records for an electronics store.
#+end

The structure mirrors SQL blocks: a header line, optional #+meta and #+const sections, and a #+begin...#+end body containing the prompt text. See Constants for the difference between file-level and block-level #+const declarations.

Output Schema

Every LLM block that returns data should declare a :schema in its #+meta map. The schema tells the LLM what columns and types to produce and validates the response.

The :schema map contains a :columns key (required) with a list of column definitions, plus an optional :validation key (see Schema Validation below). Each column is a map with:

:name — column name (required)
:type — data type (required)
:description — column description sent to the model to guide the generated values (optional)
:nullable — whether the column may contain null values (optional, default true)

#+meta {
  :schema {
    :columns [{:name "user_id", :type "INT", :description "Unique user identifier"},
              {:name "email", :type "STRING"},
              {:name "balance", :type "DECIMAL(12,2)"},
              {:name "created_at", :type "TIMESTAMP(0)"},
              {:name "is_active", :type "BOOL"}]
  }
}

Supported schema types:

Type	Parameters	Example
`BOOL`	none	`"BOOL"`
`INT`	none	`"INT"`
`FLOAT`	none	`"FLOAT"`
`STRING`	none	`"STRING"`
`BINARY`	none	`"BINARY"`
`DATE`	none	`"DATE"`
`TIME`	optional scale	`"TIME"`, `"TIME(3)"`
`TIMESTAMP`	optional scale	`"TIMESTAMP"`, `"TIMESTAMP(0)"`
`DECIMAL`	precision[, scale]	`"DECIMAL(10)"`, `"DECIMAL(10,2)"`

Schema Validation

The optional :validation key on the :schema map controls how strictly the model's response must conform to the declared column types. It accepts "lenient" (the default) or "strict":

#+meta {
  :schema {
    :columns [{:name "amount", :type "DECIMAL(10,2)"},
              {:name "currency", :type "STRING", :nullable false}],
    :validation "strict"
  }
}

"lenient" (default) — best-effort conversion. The model is steered to convert values to the declared types, and reader-side checks coerce a value where a faithful coercion exists (for example, rounding a decimal to the declared scale).
"strict" — refusal over guessing. Ambiguous, under-specified, or unrepresentable values are reported as an error instead of being coerced, and reader-side type checks fail rather than adjusting the value.

Per-column :nullable (default true) declares whether a column may contain null values; set :nullable false to require a non-null value for every row. Non-nullable columns are also emitted as plain typed fields instead of anyOf: [type, null] unions in the request schema, which helps wide tables stay within providers' limits on union-typed fields (for example, Anthropic's cap of 16).

Prompt Body

The #+begin...#+end body contains the prompt sent to the LLM. It supports the full CoginitiScript template syntax:

Expressions:

#+src llm analyze()
#+meta {
  :schema {
    :columns [{:name "result", :type "STRING"}]
  }
}
#+const
  threshold = 100;
#+begin
Analyze data where the threshold is {{ threshold }}.
The cutoff is {{ threshold * 2 }}.
#+end

Parameters:

#+src llm generate_query()
#+meta {
  :schema {
    :columns [{:name "sql", :type "STRING"}]
  }
}
#+begin
Generate a SQL query to select from $$table_name where $$column_name = '$$filter_value{default_value}'.
#+end

Conditionals and loops:

#+src llm build_report()
#+meta {
  :schema {
    :columns [{:name "analysis", :type "STRING"}]
  }
}
#+const
  categories = ["sales", "marketing", "engineering"];
  include_details = true;
#+begin
Analyze data for the following categories:
#+for cat : categories separator ", " do{{ cat }}#+end.
#+if include_details then
Include detailed breakdowns for each category.
#+end
#+end

Block arguments:

#+src llm greet(name, greeting)
#+meta {
  :schema {
    :columns [{:name "message", :type "STRING"}]
  }
}
#+begin
{{ greeting }}, {{ name }}! How are you today?
#+end

#+src sql call_greet()
#+begin
SELECT * FROM {{ greet("Alice", "Hello") }}
#+end

Referencing LLM Blocks from SQL

SQL blocks reference LLM blocks using the standard {{ block() }} syntax. The LLM result is materialized into a temporary table that the SQL block queries:

#+src llm inventory()
#+meta {
  :schema {
    :columns [{:name "item_name", :type "STRING"},
              {:name "quantity", :type "INT"},
              {:name "in_stock", :type "BOOL"}]
  }
}
#+begin
Generate inventory data for a warehouse.
#+end

#+src sql get_in_stock_items()
#+begin
SELECT item_name, quantity
FROM {{ inventory() }}
WHERE in_stock = true AND quantity > 10
ORDER BY item_name
#+end

Multiple LLM blocks can be joined in a single SQL query:

SELECT c.name, o.amount
FROM {{ customers() }} c
JOIN {{ orders() }} o ON c.id = o.customer_id
ORDER BY c.name

LLM results can also be chained through multiple SQL blocks:

#+src llm raw_metrics()
#+meta {
  :schema {
    :columns [{:name "metric_id", :type "INT"},
              {:name "value", :type "FLOAT"}]
  }
}
#+begin
Generate raw metric data.
#+end

#+src sql processed_metrics()
#+begin
SELECT metric_id, value * 2 AS doubled_value FROM {{ raw_metrics() }}
#+end

#+src sql final_report()
#+begin
SELECT metric_id, doubled_value FROM {{ processed_metrics() }} WHERE doubled_value > 50
#+end

Platform Support

Not all database platforms support referencing LLM blocks from SQL. The platform must support creating tables from recordset data. Platforms like Athena and SQL Server do not currently support this capability.

Embedding SQL Data in LLM Prompts

Use print.Csv() to include SQL query results as CSV text in an LLM prompt:

#+import "std/print"

#+src sql sample_data()
#+begin
SELECT 'Alice' AS name, 30 AS age
UNION ALL
SELECT 'Bob' AS name, 25 AS age
ORDER BY 1
#+end

#+src llm analyze_csv()
#+meta {
  :schema {
    :columns [{:name "summary", :type "STRING"}]
  }
}
#+begin
Analyze the following data:
{{ print.Csv(sample_data()) }}
#+end

The prompt sent to the LLM includes the CSV inline:

Analyze the following data:
name,age
"Alice",30
"Bob",25

Chaining LLM Blocks

LLM blocks cannot be used as table references from other LLM blocks (i.e., {{ llmBlock() }} inside an LLM body is not allowed). However, LLM blocks can consume another LLM block's results using print.Csv() or iterator():

Using print.Csv() to embed LLM results as CSV:

#+import "std/print"

#+src llm get_raw_data()
#+meta {
  :schema {
    :columns [{:name "id", :type "INT"},
              {:name "text", :type "STRING"}]
  }
}
#+begin
Generate sample data records.
#+end

#+src llm analyze_data()
#+meta {
  :schema {
    :columns [{:name "result", :type "STRING"}]
  }
}
#+begin
Analyze the following data:
{{ print.Csv(get_raw_data()) }}
#+end

Using iterator() to access individual rows:

#+src llm get_context()
#+meta {
  :schema {
    :columns [{:name "context", :type "STRING"}]
  }
}
#+begin
Provide context data for the analysis.
#+end

#+src llm analyze_with_context()
#+meta {
  :schema {
    :columns [{:name "result", :type "STRING"}]
  }
}
#+begin
Analyze using context from: {{ iterator(get_context())[0]["context"] }}
#+end

The iterator() function executes the referenced LLM block and returns its rows as a list of maps. Columns are accessible by name (row["col"]) or by zero-based index (row[0]).

Caching

LLM block results follow the same caching rules as SQL blocks. By default, the LLM is called once per unique (block, arguments) combination within the execution scope, and the result is reused across all references in that scope. Set :cache_results false to force a fresh LLM call for every reference:

#+src llm get_data()
#+meta {
  :schema {
    :columns [{:name "id", :type "INT"},
              {:name "value", :type "STRING"}]
  },
  :cache_results false
}
#+begin
Generate data with id and value
#+end

LLM Block Publication

LLM blocks support the same publication targets as SQL blocks except views — a database table (shown below) or a CSV/Parquet file. Publishing to a table is the most common case:

#+src llm GenerateAnalytics()
#+meta {
  :schema {
    :columns [{:name "metric", :type "STRING"},
              {:name "value", :type "FLOAT"}]
  },
  :publication {
    :type "table",
    :name "llm_analytics",
    :schema "reports"
  }
}
#+begin
Generate analytics metrics for the quarterly report.
#+end

Incremental publication is supported with the same strategies as SQL blocks:

#+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 sentiment scores.
#+end

LLM publications can be executed programmatically using std/publication:

#+import "std/publication"

{{ publication.Run(blocks=[DailySentiment], parallelism=2) }}

File publication (CSV or Parquet) writes the LLM result to a file instead of a table, following the same rules as SQL block file publication:

#+src llm ExportReport()
#+meta {
  :schema {
    :columns [{:name "metric", :type "STRING"},
              {:name "value", :type "FLOAT"}]
  },
  :publication {
    :type "csv",
    :path "/reports/llm_report.csv"
  }
}
#+begin
Generate report metrics.
#+end

As with SQL blocks, a block that publishes to a file cannot be referenced from SQL — the target is a file location, not a relation.

warning

View publication (:type "view") is not supported for LLM blocks. Table, CSV, and Parquet publication are all supported.

Iterating Over LLM Results in SQL

Use iterator() to loop over LLM block rows within a SQL block:

#+src llm categories()
#+meta {
  :schema {
    :columns [{:name "id", :type "INT"},
              {:name "name", :type "STRING"}]
  }
}
#+begin
Generate category data
#+end

#+src sql generate_inserts()
#+begin
SELECT
  #+for cat : iterator(categories()) separator "," do
    '{{ cat["id"] }}_{{ cat["name"] }}'
  #+end
;
#+end

Columns can be accessed by name or by positional index:

#+for emp : iterator(employee_data()) separator "," do
  '{{ emp[0] }}:{{ emp["first_name"] }}:{{ emp[2] }}:{{ emp["active"] }}'
#+end

Return Types

LLM blocks support the same return types as SQL blocks:

recordset (default): Returns tabular data defined by the schema
void: Performs an action without returning data

#+src llm generate_data(): recordset
...

#+src llm log_action(): void
...

Errors and Validation

When an LLM block runs, the model returns either rows matching the schema or a refusal. Coginiti validates the response against the declared :schema and surfaces any failure as a script error.

Response validation and automatic retry. Every declared column must be present and each value convertible to its declared type. Under "lenient" validation (the default), values are coerced where a faithful conversion exists; under "strict", mismatches fail instead of being coerced. If a response is malformed or fails type conversion, Coginiti automatically re-issues the call (up to 3 total attempts), feeding the previous response and the error back to the model so it can self-correct. Retries stop once any rows have already been delivered.

Model refusals. If the model determines a requested value or column cannot be produced faithfully, it returns a short explanation instead of rows. This surfaces as an error and is not retried — adjust the prompt or schema and re-run. "strict" validation routes more cases to this channel by design.

Common errors:

Error (representative message)	Cause	Fix
`schema metadata is required for LLM block '<name>'`	The block has no `:schema`	Declare a `:schema` with `:columns`
`'<TYPE>' is unsupported as field type, one of […] is expected`	A `:type` value isn't a supported type	Use a supported type
`Failed to convert value '<v>' to type <T> for column '<c>'`	The model produced a value incompatible with the declared type	Clarify the column `:description`, choose a wider type, or keep `"lenient"` validation
output columns don't match the schema	The model returned different or renamed columns	Keep the prompt and `:schema` column names aligned
(the model's refusal message)	The model used the refusal channel	Read the message; adjust the prompt or schema
`Circular references are not allowed (<a> -> <b>)`	A block references itself, directly or transitively	Break the cycle
`AI Assistant is not configured…`	No AI provider is set up	Configure a provider (AI Assistant setup)
`LLM call exceeded deadline of PT1H`	The call ran longer than the one-hour limit	Reduce prompt/output size or split the work across smaller blocks

Because AI output can vary between runs, validate it with data quality tests even when the schema check passes.

Expressions, Loops, and Conditions

CoginitiScript supports template preprocessing with control flow constructs.

Expressions

Interpolate values using {{ }} delimiters:

SELECT {{ 1 + 1 }}     -- SELECT 2
SELECT {{ 2 > 5 }}     -- SELECT false
SELECT {{ "string" }}  -- SELECT string

note

String values are inserted without quotes to allow dynamic identifier generation:

#+const
  columnPrefix = "sales";
#+end

SELECT {{ columnPrefix }}_amount FROM fact_sales;
-- Generates: SELECT sales_amount FROM fact_sales;

For quoted strings in SQL:

SELECT '{{ paymentType }}';  -- SELECT 'credit_card';

Operators

Expressions inside {{ }} — and the conditions of #+if / #+elseif — support a small operator language over CoginitiScript values (numbers, strings, and booleans). The same grammar is used everywhere an expression is accepted.

Arithmetic operators

Operator	Meaning	Operand types
`+`	Addition, or string concatenation	numbers, or strings
`-`	Subtraction	numbers only
`*`	Multiplication	numbers only
`/`	Division	numbers only
`%`	Remainder	numbers only

+ is overloaded: with two numbers it adds, and with two strings it concatenates. The other four operators (-, *, /, %) are numbers-only.

SELECT {{ 42 + 5 }}              -- SELECT 47
SELECT {{ 10 % 3 }}             -- SELECT 1
SELECT {{ "sales" + "_amount" }} -- SELECT sales_amount

Numbers are arbitrary-precision decimals, so fractional values are preserved exactly:

SELECT {{ 1.5 + 0.001 }}   -- SELECT 1.501

Comparison operators

Comparison operators return a boolean.

Operator	Meaning	Operand types
`==`	Equal	numbers, strings, or booleans
`!=`	Not equal	numbers, strings, or booleans
`<`	Less than	numbers or strings
`<=`	Less than or equal	numbers or strings
`>`	Greater than	numbers or strings
`>=`	Greater than or equal	numbers or strings

== and != compare numbers, strings, and booleans. The ordering operators (<, <=, >, >=) compare numbers numerically and strings lexicographically.

SELECT {{ 2 > 5 }}         -- SELECT false
SELECT {{ "a" < "b" }}     -- SELECT true

#+if publication.Type() == "table" then
  GRANT SELECT ON {{ publication.Target() }} TO GROUP "analysts";
#+end

Logical operators

Operator	Meaning	Operand types
`&&`	Logical AND	booleans only
`\|\|`	Logical OR	booleans only
`!`	Logical NOT (unary)	boolean only

&& and || are short-circuit:

&& evaluates its right operand only when the left operand is true.
|| evaluates its right operand only when the left operand is false.

#+if isActive && hasAccess then
  SELECT 'visible';
#+end

Unary operators

A -, +, or ! may prefix an operand:

Operator	Meaning	Operand type
`-`	Negation	number
`+`	Identity (no-op sign)	number
`!`	Logical NOT	boolean

SELECT {{ -5 }}            -- SELECT -5
SELECT {{ !true }}         -- SELECT false

Parenthesized grouping

Wrap a subexpression in parentheses to override the default precedence:

SELECT {{ 42 + 5 * 6 - 4 / (1.5 + 0.001) }}

Without the parentheses, 1.5 + 0.001 would not be evaluated first, because / binds tighter than +.

Precedence

Operators are listed from lowest to highest precedence. Operators on the same row share a precedence level and associate left to right.

Precedence	Operators
1 (lowest)	`\|\|`
2	`&&`
3	`==` `!=`
4	`<` `<=` `>` `>=`
5	`+` `-`
6	`*` `/` `%`
7	unary `-` `+` `!`
8 (highest)	indexing `[ ]`, function calls, literals, parenthesized expressions

For example, 1 + 2 * 3 == 7 && true parses as (((1 + (2 * 3)) == 7) && true).

Type errors

Operators are strongly typed; operands are never coerced across types. Applying an operator to incompatible operands raises an error of the form Unknown operator: <left-type> <op> <right-type> — for example, 1 - "x" reports Unknown operator: number - string. The type names used in these messages are number, string, and boolean.

Literals

Inside {{ }} expressions, #+const declarations, #+meta maps, and #+if / #+for conditions, CoginitiScript recognizes the following literal forms.

Numbers

A number literal can be an integer, a decimal, or use scientific notation. All numbers are arbitrary-precision decimals internally; there is no separate integer type at the value level.

SELECT {{ 42 }}        -- integer
SELECT {{ 3.14 }}      -- decimal
SELECT {{ 42. }}       -- trailing decimal point
SELECT {{ .5 }}        -- leading decimal point (same as 0.5)
SELECT {{ 1e10 }}      -- scientific notation
SELECT {{ 1.5e-3 }}    -- signed exponent
SELECT {{ 2E+6 }}      -- uppercase exponent marker

Accepted forms:

Digits with a decimal point: 12.34, 42. (trailing point allowed).
A leading decimal point: .5.
Plain integers: 42.
An optional exponent on any of the above: 1e10, 1.5e-3, 2E+6. The exponent marker is e or E and may carry a + or - sign.

Strings

A standard string is enclosed in double quotes and supports backslash escape sequences. A raw string is enclosed in backticks and is taken verbatim. Both forms are described in detail under String literals.

SELECT '{{ "hello world" }}';
SELECT '{{ `C:\Projects\Reports` }}';   -- raw string: backslashes are literal

Booleans

SELECT {{ true }}    -- SELECT true
SELECT {{ false }}   -- SELECT false

Booleans support the logical operators &&, ||, and !, along with == and != (see Operators).

null

The null literal represents the absence of a value:

#+if config_value == null then
  SELECT 'no value provided';
#+end

null semantics:

Comparable — use == and != to test for null. null == null is true, and null is not equal to any non-null value.
Renders as the empty string — interpolating null with {{ }} produces no text.
Not usable in arithmetic — null cannot be an operand of +, -, *, /, or %.
Not a valid index — indexing a value with null raises an error.

Reading a key that is absent from a map yields null (see Index access).

Keywords

A keyword is an identifier prefixed with a colon. Keywords are most often used as map keys and configuration values:

#+const
  mode = :production;
  config = { :environment "staging", :debug false };
#+end

Lists

An ordered, comma-separated sequence in square brackets. Elements may be of any type, including nested lists and maps:

#+const
  columns = ["name", "email", "phone"];
  matrix = [[1, 2], [3, 4]];
#+end

Maps

A map is a comma-separated set of key/value pairs in curly braces. An entry can be written in a few equivalent ways:

#+const
  -- A keyword key followed by its value
  a = { :environment "production", :debug false };

  -- A keyword key with an explicit colon separator
  b = { :environment : "production", :debug : false };

  -- Any expression key, a colon, then the value
  c = { "environment": "production", "debug": false };
#+end

With the key: value form, the key may be any expression — a string, a number, or even a computed value:

#+const
  scores = { "alice": 90, "bob": 85 };
  by_number = { 1: "one", 2: "two" };
  computed = { 1 + 1: "two" };
#+end

A trailing comma after the last entry is allowed; entries must otherwise be separated by commas.

Index access

Use the expr[index] operator to read an element of a list, string, map, row, or recordset. Index expressions can be chained for nested access.

Lists and strings are indexed by a zero-based integer. Strings return the single character at that position:

SELECT {{ [10, 20, 30][1] }}       -- 20
SELECT '{{ "foo bar"[4] }}';        -- b

An integer index below 0 or at/beyond the length raises an out-of-range error.

Maps are indexed by a key. A keyword key uses the keyword form; a string or numeric key uses that literal:

SELECT {{ {:k 42}[:k] }}             -- 42
SELECT {{ {"name": "Ada"}["name"] }} -- Ada

Reading a key that is not present returns null rather than raising an error:

SELECT {{ {:k 42}[:absent] }}       -- (empty — null renders as "")

Rows returned by iterator() support both forms: a string key looks up a column by name, and an integer looks up a column by zero-based position. Referencing a column name that does not exist raises an error. This is the pattern used throughout the Iterator Function and LLM block examples:

#+for row : iterator(GetReportDates()) separator ", " do
  '{{ row["report_date"] }}'    -- by column name
#+end

#+for emp : iterator(employee_data()) separator "," do
  '{{ emp[0] }}:{{ emp["first_name"] }}'   -- by position and by name
#+end

Index access can be nested to reach into collections returned from a block:

{{ iterator(get_context())[0]["context"] }}   -- first row, then its "context" column

Indexing a value that is not a list, string, map, row, or recordset raises a type error.

Conditions

Use #+if statements for conditional code execution:

#+if debug == true then
  SELECT 'Debug mode enabled';
#+else
  SELECT 'Production mode';
#+end

`#+elseif` branches

Chain additional conditions with #+elseif <expression> then. Each #+if and #+elseif branch takes a condition followed by the then keyword; #+else takes no condition. Branches are evaluated top to bottom, and only the first branch whose condition is true is rendered. An #+if may have any number of #+elseif branches, with an optional trailing #+else:

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

If no branch condition matches and there is no #+else, the construct produces no output.

Loops

Iterate over collections with #+for. The loop variable is bound to each value in turn, and an optional separator is inserted between iterations (only between iterations, never after the last one):

#+for item : ["col1", "col2", "col3"] separator "," do
  {{ item }}
#+end

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

Looping with an index

A two-variable form, #+for index, value : collection do, binds both a position and a value on each iteration. What the first variable holds depends on the collection type:

Lists — the first variable is the zero-based index of the current element.
Maps — the first variable is the current key. Maps iterate in insertion order, and keys may be keyword, number, or boolean literals as well as strings.

SELECT
#+for pos, col : ["name", "email", "phone"] separator "," do
  {{ col }} AS col_{{ pos }}
#+end
FROM customers;
-- Generates: SELECT name AS col_0, email AS col_1, phone AS col_2 FROM customers;

The separator modifier composes with both the single- and two-variable forms; it is placed after the collection and before do. The index form also works with iterator() results:

SELECT *
FROM (
  #+for idx, row : iterator(GetReportDates()) separator "\nUNION ALL\n" do
    SELECT {{ idx }} AS ordinal, '{{ row["report_date"] }}' AS report_date
  #+end
) ranked_dates;

Iterator Function

Process block results dynamically:

#+src sql GetReportDates()
#+begin
  SELECT report_date FROM active_reports;
#+end

SELECT *
FROM fact_sales
WHERE transaction_date IN (
  #+for row : iterator(GetReportDates()) separator ", " do
    '{{ row["report_date"] }}'
  #+end
);

len Function

len() is a global built-in function. Like iterator(), it is available everywhere without an #+import, and it returns the size of its argument as a number.

The meaning of "size" depends on the argument type:

Argument type	`len()` returns
string	Number of characters
list	Number of elements
map	Number of key/value pairs
row (a single record from `iterator()`)	Number of columns

SELECT
  {{ len("coginiti") }}        AS name_length,  -- 8
  {{ len(["a", "b", "c"]) }}   AS item_count;   -- 3

To count the rows of a block, read it with iterator() first — iterator(Block()) returns the rows as a list, and len() gives the list length:

#+src sql ActiveReports()
#+begin
  SELECT report_date FROM active_reports;
#+end

#+if len(iterator(ActiveReports())) > 0 then
  SELECT 'There are {{ len(iterator(ActiveReports())) }} active reports';
#+else
  SELECT 'No active reports';
#+end

Errors

Calling len() with a null argument raises: len function can only be used with non-null values.
Calling len() with an unsupported type (for example a boolean or number) raises a type-mismatch error: Type mismatch: string, list, map, row, recordset expected, actual <type>.

Constants

Constants can be declared in two scopes: package-level (visible to every block in the package, regardless of which file declares them) or block-level (visible only inside a single block).

Package-level constants

Declared at the top level of a script with a #+const ... #+end section. A package-level constant declared in one file of a package is accessible from every other file in that same package without an #+import:

#+const
  userLimit = 100;
  emailDomain = "company.com";
  reportFields = ["name", "email", "signup_date"];
  config = { :environment "production", :debug false };
#+end

SELECT
#+for field : reportFields separator "," do
  {{ field }}
#+end
FROM users
WHERE email LIKE '%@{{ emailDomain }}'
LIMIT {{ userLimit }};

A package (or a single file within it) may contain multiple #+const ... #+end sections, and package-level constants may reference each other regardless of declaration order or which file they live in. For example, given a package with two files:

-- file: index
#+const
  five = three + two;
#+end

#+const
  two = 2 * one;
  one = 1;
#+end

-- file: index2
#+const
  three = two + one;
#+end

five resolves to 5. The constants form a single namespace: forward references (five uses three before it is declared), out-of-order dependencies (two uses one before it is declared), and cross-file references (three in index2 uses two and one from index) all work because the whole package is resolved together.

Block-level constants

Constants can also be attached to a specific block. The #+const section appears after the block header and before #+begin. If the block also has a #+meta section, #+const and #+meta may appear in either order. There is no separate #+end terminator for the block-level #+const — the #+begin line (or the next #+meta) closes the #+const section.

#+src sql Foo()
#+const
  a_number = 123.45;
  b_string = "foo";
#+begin
  SELECT
    {{ a_number }} AS a,
    '{{ b_string }}' AS b
#+end

Block-level constants follow these rules:

Local scope — they are visible only inside their own block. Sibling blocks in the same file (or package) cannot see them.
Shadowing — a block-level constant with the same name as a package-level constant overrides the package-level value inside that block.

Access to block parameters — block-level constants can reference the enclosing block's parameters in their expressions:

#+src sql Foo(a_string)
#+const
  local_const = "local val" + a_string;
#+begin
  SELECT '{{ local_const }}' AS a
#+end

No name collision with parameters — a block-level constant cannot use the same name as a parameter of its block; this raises an error.

Supported data types

Integers: 42, 1000
Floats: 3.14, 0.75
Strings: "hello world"
Keywords: :environment, :debug
Lists: ["item1", "item2"]
Maps: { :key "value", :count 5 }

Public/private constants

Package-level constants follow the same export rules as blocks:

Public: Package-level constants whose name starts with an uppercase letter can be referenced from other packages via package.ConstName (the importing package must #+import the declaring package).
Private: Lowercase package-level constants are accessible from any file within the same package, but not from other packages.
Block-level constants are always private to their block regardless of naming.

Macros

Macros provide code inlining for reusable snippets:

#+macro countryGroup(country)
#+meta {
  :doc "Returns CASE statement for country grouping"
}
#+begin
  CASE
    WHEN {{ country }} IN ('US', 'CA') THEN 'North America'
    WHEN {{ country }} IN ('GB', 'FR', 'DE') THEN 'Europe'
    ELSE 'Other'
  END
#+end

SELECT
  country,
  {{ countryGroup(country="country") }} AS region,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY country, region;

Query Tags

Query tags add metadata to SQL queries for monitoring, cost allocation, and audit purposes.

Defining Query Tags

#+src sql AnalyticsQuery()
#+meta {
  :query_tags {
    :department "analytics",
    :project "q4_report",
    :priority 1,
    :automated true
  }
}
#+begin
  SELECT * FROM sales_data;
#+end

Platform-Specific Implementation

Snowflake:

ALTER SESSION SET query_tag = '{"department":"analytics","project":"q4_report","priority":1,"automated":true}';
SELECT * FROM sales_data;
ALTER SESSION UNSET query_tag;

BigQuery:

SET @@query_label = "department:analytics,project:q4_report,priority:1,automated:true";
SELECT * FROM sales_data;

Redshift:

SET query_group TO '{"department":"analytics","project":"q4_report","priority":1,"automated":true}';
SELECT * FROM sales_data;
SET query_group TO 'default';

Querying Tagged Queries

Snowflake:

SELECT query_tag, query_id, execution_time 
FROM snowflake.account_usage.query_history 
WHERE query_tag IS NOT NULL
ORDER BY start_time DESC;

BigQuery:

SELECT j.labels, j.job_id, j.total_bytes_processed 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER j 
WHERE j.labels IS NOT NULL
ORDER BY creation_time DESC;

Standard Library Packages

std/time Package

The std/time package provides date and time manipulation functions:

#+import "std/time"

#+src sql DailyReport()
#+meta {
  :publication {
    :type "table",
    :name "report_" + time.Format(time.Now(), time.IsoBasicDate)
  }
}
#+begin
  SELECT 
    '{{ time.Format(time.NowUTC(), time.IsoDateTime) }}' as generated_at,
    COUNT(*) as record_count
  FROM source_data;
#+end

Key Functions:

time.Now(tz): Current time in specified timezone
time.NowUTC(): Current UTC time
time.Format(time, format): Format time value

Supported Formats:

time.IsoBasicDate: 20111203
time.IsoLocalDate: 2011-12-03
time.IsoLocalTime: 10:15:30
time.IsoDateTime: 2011-12-03T10:15:30+01:00[Europe/Paris]
time.IsoOrdinalDate: 2012-337
time.IsoWeekDate: 2012-W48-6

std/publication Package

Provides programmatic publication execution:

#+import "std/publication"

{{
  publication.Run(
    blocks=[sales.MonthlySummary],
    packages=[analytics],
    postOp=notifyCompletion
  )
}}

std/test Package

Enables programmatic test execution:

#+import "std/test"

{{
  test.Run(
    tests=[validation.TestDataQuality],
    packages=[compliance],
    onFailure=test.Continue
  )
}}

std/runtime Package

Exposes information about the platform the script is currently executing against. Use it to write platform-aware logic — typically inside :run_condition (see Script Metadata) or #+if blocks.

Unlike other std/* packages, std/runtime is implicitly imported in every script — the runtime.* identifiers are available without an explicit #+import "std/runtime" directive.

#+src sql LatestPerUser()
#+begin
  #+if runtime.IsBigQuery() then
    SELECT * FROM events
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) = 1;
  #+else
    SELECT DISTINCT ON (user_id) * FROM events
    ORDER BY user_id, ts DESC;
  #+end
#+end

Functions:

runtime.Platform() — returns the ID of the current platform as a string (e.g. "postgres", "bigquery", "snowflake"). Raises an error if no platform connection is active.
runtime.Is<Name>() — one predicate function per available platform, returning a boolean. Examples: runtime.IsPostgres(), runtime.IsRedshift(), runtime.IsBigQuery(), runtime.IsSnowflake(), runtime.IsDatabricks(), runtime.IsAthena(), runtime.IsTrino(), runtime.IsOracle(), runtime.IsSQLServer().

Platform constants:

For every available platform, the package exposes a string constant whose value is the platform ID — useful for comparisons against runtime.Platform():

Constant	Value
`runtime.Postgres`	`"postgres"`
`runtime.Redshift`	`"redshift"`
`runtime.BigQuery`	`"bigquery"`
`runtime.Snowflake`	`"snowflake"`
`runtime.Databricks`	`"databricks"`
`runtime.Athena`	`"athena"`
`runtime.Trino`	`"trino"`
`runtime.Oracle`	`"oracle"`
`runtime.SQLServer`	`"sql_server"`

#+if runtime.Platform() == runtime.Snowflake then
  ALTER SESSION SET QUERY_TAG = 'reporting';
#+end

The constants and Is<Name>() predicates are generated from the platforms registered at runtime, so the exact set depends on the Coginiti edition and installed connectors. Predicate functions read more naturally than constant comparisons for single-platform checks; constants are useful when dispatching on a value computed from runtime.Platform().

std/print Package

The std/print package formats recordset data as text. Its primary use is embedding SQL or LLM query results into LLM prompts (see Embedding SQL Data in LLM Prompts).

#+import "std/print"

Functions:

print.Csv(recordset, options?) — renders a recordset as CSV text. The first line is a header row of column names; each subsequent line is a record. String values are quoted; numeric and boolean values are not. The optional options map customizes the output (see CSV options below).

#+import "std/print"

#+src sql sample_data()
#+begin
SELECT 'Alice' AS name, 30 AS age
UNION ALL
SELECT 'Bob' AS name, 25 AS age
ORDER BY 1
#+end

#+src llm analyze_csv()
#+meta {
  :schema {
    :columns [{:name "summary", :type "STRING"}]
  }
}
#+begin
Analyze the following data:
{{ print.Csv(sample_data()) }}
#+end

print.Csv(sample_data()) expands to:

name,age
"Alice",30
"Bob",25

The first argument can be any block that returns a recordset, including SQL blocks and LLM blocks. This makes print.Csv() the standard way to feed one block's results into an LLM prompt as inline text.

CSV options

Pass a map as the second argument to override the default CSV formatting. All keys are optional; omitted keys keep their defaults.

Key	Type	Default	Description
`:header`	boolean	`true`	Emit a header row of column names. Set `false` to output records only.
`:delimiter`	string	`","`	Field separator. Must be a single character or a supported escape sequence.
`:quote_char`	string	`"\""`	Character used to wrap string values. Set to `""` to disable quoting entirely.
`:null_value`	string	`"NULL"`	Text substituted for SQL `NULL` values.

Notes:

:delimiter and :quote_char must resolve to a single character. A multi-character value is accepted only if it is one of the supported escape sequences: \t, \n, \r, \b, \f, ", '. Any other multi-character string raises an error.
With the default quote character, fields that contain the delimiter or the quote character are quoted automatically, and embedded quotes are escaped by doubling them (has"quote → "has""quote").
Passing a value of the wrong type (for example a number for :delimiter, or a string for :header) raises an error.

Disable the header row:

{{ print.Csv(Data(), {:header false}) }}

1.23,"abc",false

Custom delimiter:

{{ print.Csv(Data(), {:delimiter ";"}) }}

col1;col2;col3
1.23;"abc";false

Custom null placeholder (for a row 1, NULL, 'active'):

{{ print.Csv(Data(), {:null_value "N/A"}) }}

id,name,status
1,N/A,"active"

Disable quoting:

{{ print.Csv(Data(), {:quote_char ""}) }}

id,text,count
1,simple,100

Combine options (for a row 1, NULL, 'active'):

{{ print.Csv(Data(), {:delimiter ";", :header false, :null_value "", :quote_char "?"}) }}

1;;?active?

Best Practices

Code Organization

Use meaningful block names that describe their purpose
Group related blocks into packages
Document blocks with :doc metadata
Follow DRY principles by reusing blocks instead of duplicating code

Performance

Use incremental publication for large datasets
Consider publication strategy for frequently referenced blocks
Optimize block dependencies to minimize execution time
Use appropriate return types (void vs recordset)

Testing

Write tests for critical data transformations
Use descriptive test names that explain what is being tested
Test edge cases and data quality constraints
Integrate tests into CI/CD pipelines

Metadata Usage

Add documentation to all public blocks
Use query tags for monitoring and cost allocation
Version your blocks using metadata
Document parameters and return values

This reference provides comprehensive coverage of CoginitiScript features. For examples and tutorials, see the Getting Started Guide and How-to Guides.

Overview​

Block Structure​

Code Blocks​

Block Definition​

Block Invocation​

Block Execution​

Block Parameters​

Parameter placeholders​

Default values​

Regular vs. alternative is mode-based​

How a value is resolved​

Return Types​

recordset (default)​

void​

Execution Mechanics​

Default Strategy: CTE (Common Table Expression)​

Fallback Strategy: Temporary Tables​

Ephemeral Tables​

Result Caching​

Cache scope​

Limitations​

Recursion and circular references​

Block, macro, and test cycles​

Package import cycles​

Packages​

Public/Private Blocks​

Importing Packages​

Projects​

Metadata Properties​

Documentation​

Script Metadata​

:run_condition​

String literals​

Double-quoted strings​

Multiline re-indentation​

Raw strings​

Publication​

Database Publication​

Table Publication​

View Publication​

Platform-Specific Properties​

Postgres — :postgres​

Redshift — :redshift​

BigQuery — :bigquery​

Snowflake — :snowflake​

Iceberg Catalog (Snowflake) — :iceberg_snowflake​

Athena — :athena​

Trino — :trino​

File Publication​

CSV Publication​

Parquet Publication​

Object Store Publication​

Referencing Published Blocks​

Incremental Publication​

Append Strategy​

Merge Strategy​

Conditional Merge Strategy​

Merge Execution Strategy by Platform​

Excluding Columns from Merge​

Executing Publications​

publication.Incremental() Function​

Lifecycle hooks​

publication.Target() and publication.Type() Functions​

Tests​

Defining Tests​

Test Results​

Running Tests​

LLM Blocks​

Defining an LLM Block​

Output Schema​

Schema Validation​

Prompt Body​

Referencing LLM Blocks from SQL​

Embedding SQL Data in LLM Prompts​

Chaining LLM Blocks​

Caching​

LLM Block Publication​

Iterating Over LLM Results in SQL​

Return Types​

Errors and Validation​

Overview

Block Structure

Code Blocks

Block Definition

Block Invocation

Block Execution

Block Parameters

Parameter placeholders

Default values

Regular vs. alternative is mode-based

How a value is resolved

Return Types

recordset (default)

void

Execution Mechanics

Default Strategy: CTE (Common Table Expression)

Fallback Strategy: Temporary Tables

Ephemeral Tables

Result Caching

Cache scope

Limitations

Recursion and circular references

Block, macro, and test cycles

Package import cycles

Packages

Public/Private Blocks

Importing Packages

Projects

Metadata Properties

Documentation

Script Metadata

`:run_condition`

String literals

Double-quoted strings

Multiline re-indentation

Raw strings

Publication

Database Publication

Table Publication

View Publication

Platform-Specific Properties

Postgres — `:postgres`

Redshift — `:redshift`

BigQuery — `:bigquery`

Snowflake — `:snowflake`

Iceberg Catalog (Snowflake) — `:iceberg_snowflake`

Athena — `:athena`

Trino — `:trino`

File Publication

CSV Publication

Parquet Publication

Object Store Publication

Referencing Published Blocks

Incremental Publication

Append Strategy

Merge Strategy

Conditional Merge Strategy

Merge Execution Strategy by Platform

Excluding Columns from Merge

Executing Publications

publication.Incremental() Function

Lifecycle hooks

publication.Target() and publication.Type() Functions

Tests

Defining Tests

Test Results

Running Tests

LLM Blocks

Defining an LLM Block

Output Schema

Schema Validation

Prompt Body

Referencing LLM Blocks from SQL

Embedding SQL Data in LLM Prompts

Chaining LLM Blocks

Caching

LLM Block Publication

Iterating Over LLM Results in SQL

Return Types

Errors and Validation