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.

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

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() }};

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".

Multiline String Literals

CoginitiScript supports multiline strings with automatic indentation handling:

SELECT * FROM {{ block("
line 1
  line 2
    line 3
  line 4
line 5
") }}

The re-indentation algorithm removes consistent leading whitespace to produce:

line 1
  line 2
    line 3
  line 4
line 5

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

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

Coming in Coginiti 26.6

LLM blocks are scheduled to ship in the Coginiti 26.6 release. This section is published as a preview — the feature is not yet available in earlier versions, and some details may change before general availability.

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.

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 has a single :columns key containing a list of column definitions. Each column is a map with:

:name — column name (required)
:type — data type (required)
:description — column description included in the LLM prompt (optional)

#+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)"`

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 publication to database tables (views are not supported):

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

warning

View publication (:type "view") is not supported for LLM blocks. Only table publication is allowed.

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
...

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';

Conditions

Use #+if statements for conditional code execution:

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

Loops

Iterate over collections with #+for:

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

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

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
);

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().

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​

Return Types​

recordset (default)​

void​

Execution Mechanics​

Default Strategy: CTE (Common Table Expression)​

Fallback Strategy: Temporary Tables​

Ephemeral Tables​

Result Caching​

Cache scope​

Limitations​

Packages​

Public/Private Blocks​

Importing Packages​

Metadata Properties​

Documentation​

Script Metadata​

:run_condition​

Multiline String Literals​

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​

Tests​

Defining Tests​

Test Results​

Running Tests​

LLM Blocks​

Defining an LLM Block​

Output Schema​

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​

Expressions, Loops, and Conditions​

Expressions​

Conditions​

Loops​

Iterator Function​

Constants​

Package-level constants​

Block-level constants​

Supported data types​

Public/private constants​

Macros​

Query Tags​

Defining Query Tags​

Platform-Specific Implementation​

Querying Tagged Queries​

Overview

Block Structure

Code Blocks

Block Definition

Block Invocation

Block Execution

Block Parameters

Return Types

recordset (default)

void

Execution Mechanics

Default Strategy: CTE (Common Table Expression)

Fallback Strategy: Temporary Tables

Ephemeral Tables

Result Caching

Cache scope

Limitations

Packages

Public/Private Blocks

Importing Packages

Metadata Properties

Documentation

Script Metadata

`:run_condition`

Multiline String Literals

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

Tests

Defining Tests

Test Results

Running Tests

LLM Blocks

Defining an LLM Block

Output Schema

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

Expressions, Loops, and Conditions

Expressions

Conditions

Loops

Iterator Function

Constants

Package-level constants

Block-level constants

Supported data types

Public/private constants

Macros

Query Tags

Defining Query Tags

Platform-Specific Implementation

Querying Tagged Queries