CoginitiScript: The Power of Macros

Macros in CoginitiScript allow you to create reusable code snippets that get expanded inline during script execution. This guide shows you how to create and use macros to eliminate code duplication and maintain consistency across your SQL queries.

What You'll Learn

How to define a CoginitiScript macro
How to call macros with parameters
Best practices for macro usage
Common use cases for macros

Prerequisites

Basic understanding of CoginitiScript syntax
Familiarity with SQL

The Problem: Code Duplication

Imagine you frequently need to categorize countries into geographic regions using a CASE expression. Without macros, you might copy and paste the same logic across multiple queries:

SELECT
  CASE
    WHEN country IN ('US', 'CA') THEN 'NA'
    WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
    WHEN country IN ('AU') THEN country
    ELSE 'Other'
  END AS country_group,
  SUM(revenue) AS total_revenue
FROM
  fact_sales
GROUP BY
  country_group;

This approach has problems:

Code duplication: The same CASE expression appears in multiple places
Maintenance burden: Changes require updating every copy
Inconsistency risk: Manual copying can introduce variations

The Solution: CoginitiScript Macros

Step 1: Define the Macro

Create a macro that encapsulates the country grouping logic:

#+macro countryGroup(countryField)
#+meta {
  :doc "Given a name of the country column returns a CASE statement to get a country group."
}
#+begin
  CASE
    WHEN {{"{{ countryField }}"}} IN ('US', 'CA') THEN 'NA'
    WHEN {{"{{ countryField }}"}} IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
    WHEN {{"{{ countryField }}"}} IN ('AU') THEN {{"{{ countryField }}"}}
    ELSE 'Other'
  END
#+end

Key elements:

#+macro: Declares this as a macro definition
countryGroup(countryField): Macro name with parameter
#+meta: Optional metadata for documentation
#+begin...#+end: Contains the macro body
{{"{{ countryField }}"}}: Parameter interpolation

Step 2: Use the Macro

Call the macro in your SQL queries using interpolation syntax:

SELECT
  {{"{{ countryGroup(countryField=\"country\") }}"}} AS country_group,
  SUM(revenue) AS revenue
FROM
  fact_sales
GROUP BY
  country_group;

Step 3: See the Expansion

CoginitiScript expands the macro call before execution. The final SQL becomes:

SELECT
  CASE
    WHEN country IN ('US', 'CA') THEN 'NA'
    WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
    WHEN country IN ('AU') THEN country
    ELSE 'Other'
  END AS country_group,
  SUM(revenue) AS revenue
FROM
  fact_sales
GROUP BY
  country_group;

Advanced Usage Examples

Multi-Parameter Macro

Create more flexible macros with multiple parameters:

#+macro dateRange(dateColumn, startDate, endDate)
#+meta {
  :doc "Creates a date range filter condition."
}
#+begin
  {{"{{ dateColumn }}"}} BETWEEN '{{"{{ startDate }}"}}' AND '{{"{{ endDate }}"}}'
#+end

Usage:

SELECT * 
FROM orders 
WHERE {{"{{ dateRange(dateColumn=\"order_date\", startDate=\"2024-01-01\", endDate=\"2024-12-31\") }}"}};

Conditional Logic Macro

Include conditional logic within macros:

#+macro customerSegment(revenueColumn, includeVip)
#+meta {
  :doc "Categorizes customers by revenue, optionally including VIP tier."
}
#+begin
  CASE
    WHEN {{"{{ revenueColumn }}"}} >= 10000 THEN 'Enterprise'
    WHEN {{"{{ revenueColumn }}"}} >= 1000 THEN 'Business'
    #+if includeVip then
    WHEN {{"{{ revenueColumn }}"}} >= 500 THEN 'VIP'
    #+end
    ELSE 'Standard'
  END
#+end

Reusable Column Lists

Create macros for commonly used column sets:

#+macro customerColumns()
#+begin
  customer_id,
  first_name,
  last_name,
  email,
  phone,
  created_at
#+end

Usage:

SELECT {{"{{ customerColumns() }}"}}
FROM customers
WHERE active = true;

Best Practices

1. Use Descriptive Names

Choose clear, descriptive names for your macros:

-- Good
#+macro calculateTaxAmount(baseAmount, taxRate)

-- Avoid
#+macro calc(a, b)

2. Document Your Macros

Always include documentation in the metadata:

#+macro discountPrice(originalPrice, discountPercent)
#+meta {
  :doc "Calculates discounted price.
  
  Parameters:
  - originalPrice: The original price column name
  - discountPercent: Discount percentage (0-100)
  
  Returns: Expression that calculates discounted price"
}
#+begin
  {{"{{ originalPrice }}"}} * (1 - {{"{{ discountPercent }}"}} / 100.0)
#+end

3. Keep Macros Focused

Create single-purpose macros rather than complex, multi-purpose ones:

-- Good: Single purpose
#+macro formatCurrency(amount)
#+begin
  CONCAT('$', FORMAT({{"{{ amount }}"}}, 2))
#+end

-- Avoid: Too complex
#+macro formatAndCalculateTotal(amount, tax, discount, currency)

4. Test Macro Expansion

Always verify that your macro expands to valid SQL:

-- Test with simple query first
SELECT {{"{{ countryGroup(countryField=\"'US'\") }}"}} AS test;

5. Use Constants for Common Values

Combine macros with constants for maintainable code:

#+const
  northAmericaCodes = ['US', 'CA', 'MX'];
  europeCodes = ['GB', 'FR', 'DE', 'IT', 'ES'];
#+end

#+macro regionGroup(countryField)
#+begin
  CASE
    WHEN {{"{{ countryField }}"}} IN (
      #+for code : northAmericaCodes separator ", " do
        '{{"{{ code }}"}}'
      #+end
    ) THEN 'North America'
    WHEN {{"{{ countryField }}"}} IN (
      #+for code : europeCodes separator ", " do
        '{{"{{ code }}"}}'
      #+end
    ) THEN 'Europe'
    ELSE 'Other'
  END
#+end

Common Use Cases

1. Standardized Calculations

#+macro monthlyRevenue(revenueColumn, dateColumn)
#+begin
  SUM(CASE WHEN EXTRACT(MONTH FROM {{"{{ dateColumn }}"}}) = EXTRACT(MONTH FROM CURRENT_DATE) 
           THEN {{"{{ revenueColumn }}"}} ELSE 0 END)
#+end

2. Complex Filters

#+macro activeCustomerFilter(statusColumn, lastLoginColumn)
#+begin
  {{"{{ statusColumn }}"}} = 'active' 
  AND {{"{{ lastLoginColumn }}"}} >= CURRENT_DATE - INTERVAL '90 days'
#+end

3. Data Quality Checks

#+macro validateEmail(emailColumn)
#+begin
  {{"{{ emailColumn }}"}} IS NOT NULL 
  AND {{"{{ emailColumn }}"}} LIKE '%@%.%'
  AND LENGTH({{"{{ emailColumn }}"}}) > 5
#+end

Important Considerations

Context Awareness

Macros are expanded as-is without context validation. Ensure your macro content is appropriate for where it's used:

-- This macro is designed for SELECT clauses
#+macro fullName(firstCol, lastCol)
#+begin
  CONCAT({{"{{ firstCol }}"}}, ' ', {{"{{ lastCol }}"}})
#+end

-- Don't use it in WHERE clauses expecting boolean result
-- WHERE {{ fullName("first_name", "last_name") }} -- Wrong context!

Parameter Validation

CoginitiScript doesn't validate parameter types or values. You're responsible for:

Passing valid column names
Ensuring parameter values are appropriate
Verifying the expanded SQL is syntactically correct

Debugging Tips

When debugging macro issues:

Check parameter names: Ensure you're using the correct parameter names
Test expansion: Run a simple query to see the expanded SQL
Validate SQL: Verify the expanded SQL is syntactically correct
Use descriptive errors: Add comments to help identify issues

Troubleshooting

Common Issues

Issue: Macro not expanding

-- Wrong: Missing interpolation syntax
SELECT countryGroup(country="country") FROM sales;

-- Correct: Use interpolation syntax
SELECT {{"{{ countryGroup(country=\"country\") }}"}} FROM sales;

Issue: Parameter not substituted

-- Wrong: Parameter name mismatch
#+macro test(paramName)
#+begin
  {{"{{ wrongName }}"}}  -- Should be {{ paramName }}
#+end

Issue: Invalid SQL after expansion

-- This might expand to invalid SQL if not careful with quotes
#+macro filterByValue(column, value)
#+begin
  {{"{{ column }}"}} = {{"{{ value }}"}}  -- What if value needs quotes?
#+end

Next Steps

Explore CoginitiScript Reference for complete macro documentation
Learn about blocks and packages for larger code organization
Check out constants and expressions for more advanced features

Summary

Macros are a powerful CoginitiScript feature that help you:

Eliminate code duplication
Ensure consistency across queries
Simplify maintenance and updates
Create reusable SQL components

By following the patterns and best practices in this guide, you can create maintainable, reusable macros that improve your data workflows.

What You'll Learn​

Prerequisites​

The Problem: Code Duplication​

The Solution: CoginitiScript Macros​

Step 1: Define the Macro​

Step 2: Use the Macro​

Step 3: See the Expansion​

Advanced Usage Examples​

Multi-Parameter Macro​

Conditional Logic Macro​

Reusable Column Lists​

Best Practices​

1. Use Descriptive Names​

2. Document Your Macros​

3. Keep Macros Focused​

4. Test Macro Expansion​

5. Use Constants for Common Values​

Common Use Cases​

1. Standardized Calculations​

2. Complex Filters​

3. Data Quality Checks​

Important Considerations​

Context Awareness​

Parameter Validation​

Debugging Tips​

Troubleshooting​

Common Issues​

Next Steps​

Summary​