Code Blocks

CoginitiScript was designed to encourage good software engineering practices. One of the guiding principles of high-quality software is the DRY principle - Don’t Repeat Yourself, which basically means that you should never write the same code twice. You should reuse and build upon existing code as much as possible.

“Code Blocks” are the most basic building blocks that allow code reuse. Packages (see below) are the next step into code reusability. They help you organize related CoginitiScript source files together into a single unit, making them modular, reusable, and maintainable.


Block Definition

SQL block which calculates total sales amount per each store:

#+src sql SalesPerStore()
  SELECT AS store_name,
    SUM(s.quantity * s.price) AS total_sales_amount
    fact_sales AS s
  INNER JOIN dim_store AS s
    ON = s.store_id

To invoke block of code from another block, reference it via ${block-name(args)} in a context where you would normally reference a database table.

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


Block Execution

When you run whole script anonymous blocks executed immediately. Execution of named block happens when they are referenced using ${block-name(args)} syntax.

Following block will be executed immediately.

#+src sql

But this block will not be executed until you call it via ${selectOne()}.

#+src sql selectOne()

SELECT * FROM ${selectOne()}; -- this is a line where actual execution of block happens.

When your cursor inside the block and you execute it by run at cursor the block is executed, even if it is named. It is done for better development experience, because when you are working on a certain block it is handy to run it in this way and see the result.


What happens under the hood

When CoginitiScript execute a block which has been called from another block it creates a temporary table with results of the last SELECT statement from the block and then use the name of that temporary table in a place where block has been referenced.

For example, given CoginitiScript code:

#+src sql selectOne()
  SELECT 1 AS one;

SELECT * FROM ${selectOne()};

Will be executed on database platform as following SQL script:

SELECT 1 AS one;

SELECT * FROM _random_name_;

Please note, that your block SQL code is not limited to just single SELECT statement. It could have complex scripts with several statements, with steps where you create your own temporary tables, etc. The only requirement that you should follow if you want to reference this block is to have SELECT statement which produces result as your last statement in the block SQL.
#+src sql foo()
  DROP TABLE IF EXIST tmp_table_name;
  SELECT * FROM ...;

  -- any other transformation logic

  -- SELECT statement to produce final results

SELECT * FROM ${foo()} WHERE name = 'foo';
In future versions CoginitiScript might not use temporary tables for all cases when block is referenced. For blocks which has single SELECT statements in it, it might just embed it as a subquery into the block from which it has been referenced.


In the current version of CoginitiScript it has following limitations:

  • BigQuery platform is not supported (support will be added in next release)
  • Database user used to execute CoginitiScript should have permissions to create temporary tables in the given database / schema.
  • On Snowflake platform it is required to have public schema on the given database with permissions for user to create temporary tables in it.
  • On Snowflake platform is it required to have explicit column aliases specified on SELECT statement in block which is referenced from other places due to limitation of the platform itself which is not allow unnamed columns in queries when creating temporary tables.
  • Explain plan cannot be executed for statement with block reference
  • Named block cannot be called from execute to file statement
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request