Protobi

← All help posts

View post

# Generate SQL Dynamically with Protobi EJS and SQL
This article explains the standard Protobi pattern for building reusable SQL data processes with EJS templates and JSON properties.
## Overview

Protobi supports a standard way to build SQL data processes using two files:

- an .ejs file that defines the SQL structure
  - a .properties.json file that defines tables, columns, types, substitutions, and reusable logic

This pattern helps you avoid repetitive handwritten SQL. Instead of rewriting the same casting, derived fields, and net logic across multiple queries, you define the transformation once and let Protobi generate the SQL consistently.

Use this pattern for new SQL-based data processes whenever the output structure is repeatable and the logic can be expressed declaratively.

## What this pattern does
This framework helps you:

- generate consistent `SELECT` clauses from shared definitions
  - cast columns to the correct SQL types
  - define derived fields in one place
  - generate net variables such as `any` and `sum`
  - apply table-specific overrides without duplicating templates
  - keep SQL structure separate from transformation logic

This makes projects easier to maintain, easier to review, and easier for AI or other analysts to understand.
## When to use this pattern
Use this pattern when:

- multiple source tables need to be stacked into one output table
  - several tables share the same output schema
  - you want explicit column order and type definitions
  - you want derived fields and net variables defined in a reusable way
  - you want a process other analysts can quickly understand and extend

This pattern is especially useful for recurring YTD/EOY reporting workflows, survey trend stacks, and standardized data-prep pipelines.

## Core Idea

Define your data transformation **once** in JSON properties, then use EJS templates with `Protobi` methods to generate SQL.

The framework handles:
- Column type casting
- Substitution expressions
- Net variable generation (sum, any transforms)
- Table-specific overrides
- Reusable SQL generation through Protobi helper methods

This gives you a declarative pattern instead of repeating imperative SQL logic.
## Standard File Structure

Every SQL data process should use two files under: **Project Settings > Data > [Table key] > fn**
```text
  project_name.ejs
  project_name.properties.json
```
  Use the files like this:

- **project_name.ejs**
    Defines the SQL structure, such as `CREATE TABLE, UNION ALL, INSERT INTO, filters, and joins`.
  - **project_name.properties.json**
    Defines source tables, output columns, SQL types, substitutions, transform objects, and optional constants.

Keeping structure and definitions separate makes the process easier to debug, extend, and reuse.

## Minimal Working Pattern

A typical SQL data process starts with a small `.properties.json` file and a matching `.ejs` template.

The properties file defines the source tables, output columns, types, and substitutions. The EJS template uses those definitions to generate SQL through Protobi helper
  methods.

### example.properties.json

```json
  {
      "tables": {
          "2025-EOY": "data_20251231",
          "2025-YTD": "data_20250401"
      },
      "columns": [
          "id",
          "quarter",
          "sample_main",
          "bo_eoyfile"
      ],
      "types": {
          "id": "VARCHAR",
          "quarter": "VARCHAR",
          "sample_main": "INT",
          "bo_eoyfile": "INT",
          "_else_": "VARCHAR"
      },
      "substitutions": {
          "general": {
              "sample_main": "1",
              "bo_eoyfile": "${key.includes('EOY') ? 1 : 0}"
          }
      }
  }
  ```

### example.ejs

```ejs
  <%
   let params = table.properties
   let Protobi = require('../util/process_utils_sql')
  %>

DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>_temp CASCADE;

CREATE TABLE <%=project.schema_name%>.<%=tableKey%>_temp
  AS
  <% let first = true; %>
  <% for (let key in params.tables) { %>
  <%= !first ? ' UNION ALL \\n' : '' %>
  SELECT
      <%=Protobi.expand_fields(params, {key: key}) %>
  FROM <%=project.schema_name%>.<%=params.tables[key]%>
  <% first = false %>
  <% } %>
  ;

DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>;
  ALTER TABLE <%=project.schema_name%>.<%=tableKey%>_temp
      RENAME TO <%=tableKey%>;
  ```

This example shows the standard pattern in its simplest form:

- `tables` defines the source tables
  - `columns` defines the output schema and order
  - `types` defines SQL casts
  - `substitutions` defines derived values
  - `Protobi.expand_fields()` generates the `SELECT` clause from the shared configuration

## Properties JSON Structure

The Protobi convention is to define the following attributes in `properties.json`:

```json
{
    "tables": {},        // Source table mappings
    "columns": [],       // Column list in desired order
    "types": {},         // SQL type definitions
    "substitutions": {}, // Expression substitutions and transforms.
    "constants": {}      // Custom data structures for templates   
}
```
Any of the sections are optional, depending on your template needs. A project may also add additional attributes for custom processing.
The benefit of staying with this pattern is the predefined functions such as `Protobi.expand_fields()` and `Protobi.define_fields()` in EJS templates to recognize these attributes and other analysts can more quickly understand the project structure.

## Section Definitions

### 1. `tables` - Source Table Mappings

The `tables` section maps logical names to actual SQL table references.

**Simple table reference:**

```json
  "tables": {
      "2024-EOY": "data_20241231",
      "2025-EOY": "data_20251231"
  }
  ```

**Subquery reference:**

```json
  "tables": {
      "2024-YTD": "(SELECT a1.*, a2.new_column FROM schema.table_a a1 LEFT JOIN schema.table_b a2 USING (id)) subq"
  }
  ```

**Usage in templates:**

- keys are used to iterate over tables in loops
  - values are used as `FROM` clause sources

### 2. `columns` - Column List

The `columns` section is an ordered array defining all output columns, including:

- physical columns from source tables
  - calculated columns defined in substitutions
  - net variables defined as transform objects

```json
  "columns": [
      "id",
      "label",
      "derived_region",
      "net_metric",
      "fixed_flag"
  ]
  ```

In this example:

- `id` and `label` are physical columns
  - `derived_region` is a calculated field
  - `net_metric` is a net variable
  - `fixed_flag` is a constant value set in substitutions

Order matters. Columns appear in `SELECT` and `CREATE` statements in this order.

### 3. `types` - SQL Type Definitions

The `types` section defines SQL types using exact matching or prefix matching.

```json
  "types": {
      "id": "INT",
      "bo_": "INT",
      "dt_": "DATE",
      "rl_": "FLOAT",
      "st_": "VARCHAR",
      "_else_": "VARCHAR"
  }
  ```

In this example:

- `id` is an exact match for `id`
  - `bo_` applies to `bo_*` columns
  - `dt_` applies to `dt_*` columns
  - `rl_` applies to `rl_*` columns
  - `st_` applies to `st_*` columns
  - `_else_` is the default fallback

**Type resolution order:**

1. exact field name match: `types[field]`
  2. prefix match: `types[prefix]`, where `prefix = field.split("_")[0]`
  3. default fallback: `types["_else_"]`

### 4. `substitutions` - Expression Substitutions

The `substitutions` section defines how columns are calculated or transformed.

#### Structure

```json
  "substitutions": {
      "general": {},
      "table_key_1": {},
      "table_key_2": {}
  }
  ```

- `general` applies to all tables
  - table-specific entries override substitutions for a specific table

#### Types of substitutions

**A. Simple column reference**

No substitution entry is needed.

```json
  // Omitted fields use the column name as-is with type casting
  ```

Result: `fieldname::TYPE`

**B. Literal value**

```json
  "sample_main": "1"
  ```

Result: `1::INT AS sample_main`

**C. SQL expression**

```json
  "derived_region2": "CASE WHEN hcountry = 10 THEN 2 ELSE derived_region END"
  ```

Result: `CASE WHEN hcountry = 10 ... ::INT AS derived_region2`

**D. Expression with template interpolation**

```json
  "year": "${year}",
  "key": "'${key}'",
  "bo_eoyfile": "${key.includes('EOY') ? 1 : 0}"
  ```

This uses lodash `_.template()` to substitute variables from context.

**E. Net variable (transform object)**

```json
  "net_metric": {
      "transform": "any",
      "base": "Q9r",
      "name": "net_metric",
      "vals": [1],
      "subs": [1, 2]
  }
  ```

This generates complex `CASE` statements automatically.

**F. NULL value**

```json
  "missing_field": null
  ```

Result: `NULL::TYPE AS missing_field`

#### Table-Specific Overrides

Override general substitutions for specific tables:

```json
  "substitutions": {
      "general": {
          "bo_admitflag": "bo_admitflag"
      },
      "2024-YTD": {
          "bo_admitflag": "CASE WHEN bo_admitflag = 1 AND dt_admitdate <= '2024-04-01' THEN 1 ELSE 0 END"
      }
  }
  ```

When processing table `"2024-YTD"`, the table-specific substitution takes precedence.

### 5. Net Variables (Transform Objects)

Net variables are transform objects placed in `substitutions.general`.

#### Transform Types

**A. `transform: "any"` - OR logic with NULL handling**

```json
  "net_metric": {
      "transform": "any",
      "base": "Q9r",
      "name": "net_metric",
      "vals": [1],
      "subs": [1, 2]
  }
  ```

**Generated SQL:**

```sql
  CASE WHEN Q9r1::INT IN (1) OR Q9r2::INT IN (1)
       THEN 1
       WHEN Q9r1 IS NULL AND Q9r2 IS NULL
       THEN NULL
       ELSE 0
  END::INT AS net_metric
  ```

**Use case:** At least one of the sub-columns matches the value.

**B. `transform: "sum"` - Count matching values**

```json
  "net_sum": {
      "transform": "sum",
      "base": "Q9r",
      "name": "net_sum",
      "null": "TP9r97 IS NULL AND TP9r1 IS NULL",
      "vals": [1],
      "subs": [1, 2, 3, 4, 5]
  }
  ```

**Generated SQL:**

```sql
  CASE WHEN Q9r97 IS NULL AND Q9r1 IS NULL
       THEN NULL
       ELSE (
        (CASE WHEN Q9r1::INT IN (1) THEN 1 ELSE 0 END)
      + (CASE WHEN Q9r2::INT IN (1) THEN 1 ELSE 0 END)
      + (CASE WHEN Q9r3::INT IN (1) THEN 1 ELSE 0 END)
      + (CASE WHEN Q9r4::INT IN (1) THEN 1 ELSE 0 END)
      + (CASE WHEN Q9r5::INT IN (1) THEN 1 ELSE 0 END)
  ) END::INT AS net_sum
  ```

**Use case:** Count how many sub-columns match the value.

#### Transform Object Properties

| Property | Required | Description |
  |----------|----------|-------------|
  | `transform` | Yes | Transform type: `"any"` or `"sum"` |
  | `base` | Yes | Base column name (e.g. `"Q9r"`) |
  | `name` | Yes | Output column name |
  | `vals` | Yes | Array of values to match in the `IN` clause |
  | `subs` | Yes | Array of suffixes (e.g. `[1, 2] → Q9r1, Q9r2`) |
  | `null` | No | Custom NULL condition (for `"sum"` only) |

### 6. `constants` - Custom Data Structures

Use `constants` for any custom data your template needs.

**Example:**

```json
  "constants": {
      "ytd": {
          "2024-EOY": "'2024-12-31'",
          "2024-YTD": "'2023-12-04'",
          "2025-EOY": "'2025-12-31'"
      },
      "defaults": {
          "2024": "'2023-09-01'",
          "2025": "'2024-09-01'"
      }
  }
  ```

**Usage in substitutions:**

```json
  "dt_current": "${ytd[key]}",
  "dt_default": "${defaults[year]}"
  ```

## EJS Template Patterns

### Required Setup

```ejs
<%
 let params = table.properties
 let Protobi = require('../util/process_utils_sql')  // For SQL generation
 // let ProcessUtils = require('../util/process_utils')  // For V3 JS data processing (if needed)
%>
```

**Note:** There are two utility modules:
- **`process_utils_sql.js`** - SQL generation functions for EJS templates (use this for standard pattern)
- **`process_utils.js`** - JavaScript data processing for V3 (sum, left_join, stack_rows, etc.)

### Core Protobi Methods

#### `Protobi.expand_fields(params, options)`

Generates SELECT clause with all columns, substitutions, and type casting.

**Parameters:**
- `params` - The properties object
- `options` - Context object with `key` (table key) and any other variables for template interpolation

**Returns:** Comma-separated column list for SELECT

**Example:**
```ejs
SELECT
    <%=Protobi.expand_fields(params, {key: tableKey, year: 2024}) %>
FROM schema.table
```

#### `Protobi.define_fields(params)`

Generates column definitions for CREATE TABLE statements.

**Parameters:**
- `params` - The properties object

**Returns:** Comma-separated column definitions with types

**Example:**
```ejs
CREATE TABLE schema.table (
    <%=Protobi.define_fields(params) %>
);
```

### Pattern 1: UNION ALL (Simple Stacking)

**Use when:** Simple vertical stacking of tables with same structure.

```ejs
<%
 let params = table.properties
 let Protobi = require(''../util/process_utils_sql')
%>

DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>_temp CASCADE;

CREATE TABLE <%=project.schema_name%>.<%=tableKey%>_temp
AS
<% let first = true; %>
<% for (let key in params.tables) { %>
<%= !first ? ' UNION ALL \n' : ''%>
SELECT
    <%=Protobi.expand_fields(params, {key: key}) %>
FROM <%=project.schema_name%>.<%=params.tables[key]%>
<% first = false %>
<% } %>
;

DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>;
ALTER TABLE <%=project.schema_name%>.<%=tableKey%>_temp
    RENAME TO <%=tableKey%>;
```

**Advantages:**
- Simplest pattern
- Single SQL statement
- Vertica optimizes UNION ALL well

**Best for:**
- Straightforward data stacking
- Tables with similar structure
- When all logic fits in substitutions

### Pattern 2: CREATE + INSERT INTO

**Use when:** Need flexibility, debugging, or incremental updates.

```ejs
<%
 let params = table.properties
 let Protobi = require('../util/process_utils')
%>

DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>_temp;

-- Step 1: Create table with full schema
CREATE TABLE <%=project.schema_name%>.<%=tableKey%>_temp (
    <%=Protobi.define_fields(params) %>
);

-- Step 2: Insert from each source table
<% for (let key in params.tables) { %>
    INSERT INTO <%=project.schema_name%>.<%=tableKey%>_temp
    SELECT <%=Protobi.expand_fields(params, {key: key}) %>
    FROM <%=project.schema_name%>.<%=params.tables[key]%>;

<% } %>

-- Step 3: Replace final table
DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>;
ALTER TABLE <%=project.schema_name%>.<%=tableKey%>_temp
    RENAME TO <%=tableKey%>;
```

**Advantages:**
- Schema defined upfront (clearer)
- Each INSERT independent (can test/run separately)
- Better for incremental updates
- Easier debugging

**Best for:**
- Complex transformations
- Different logic per source table
- Incremental data loading
- When you need to verify each step

### Pattern Selection Guide

| Factor | UNION ALL | CREATE + INSERT |
|--------|-----------|-----------------|
| Simplicity | ✓ Simpler | More verbose |
| Performance | ✓ Single statement | Multiple statements |
| Debugging | Harder | ✓ Step by step |
| Flexibility | Limited | ✓ Very flexible |
| Schema clarity | Implicit | ✓ Explicit |
| Incremental updates | Not suitable | ✓ Ideal |

## Complete Working Example

### Scenario
Stack two survey tables with calculated fields and net variables.

### properties.json

```json
{
    "tables": {
        "data_202512": "data_202512",
        "data_202509": "data_202509"
    },
    "columns": [
        "cmbid",
        "quarter",
        "hcountry",
        "country_region",
        "weight",
        "WEIGHT_TOKYOAUG",
        "WEIGHT_INDIAAUG",
        "TP18r99",
        "tp18r1",
        "global",
        "country_region2",
        "cweight",
        "tp18r1a",
        "TP9r_net_others",
        "TP9r_net1",
        "TP9r_net2",
        "sample_main",
        "sample_main_plus"
    ],
    "types": {
        "cmbid": "VARCHAR",
        "quarter": "VARCHAR",
        "hcountry": "INT",
        "country_region": "INT",
        "country_region2": "INT",
        "weight": "FLOAT",
        "WEIGHT_TOKYOAUG": "FLOAT",
        "WEIGHT_INDIAAUG": "FLOAT",
        "cweight": "FLOAT",
        "TP18r99": "INT",
        "tp18r1": "VARCHAR",
        "tp18r1a": "VARCHAR",
        "TP9r_net_others": "INT",
        "TP9r_net1": "INT",
        "TP9r_net2": "INT",
        "global": "INT",
        "sample_main": "INT",
        "sample_main_plus": "INT",
        "_else_": "VARCHAR"
    },
    "substitutions": {
        "general": {
            "country_region2": "CASE WHEN hcountry = 10 THEN 2 ELSE country_region END",
            "cweight": "COALESCE(weight::FLOAT, WEIGHT_TOKYOAUG::FLOAT, WEIGHT_INDIAAUG::FLOAT)",
            "tp18r1a": "CASE WHEN TP18r99 = 1 THEN '-1' ELSE tp18r1 END",
            "sample_main": "1",
            "sample_main_plus": "1",
            "TP9r_net_others": {
                "base": "TP9r",
                "name": "TP9r_net_others",
                "transform": "sum",
                "null": "TP9r97 IS NULL AND TP9r1 IS NULL",
                "vals": [1],
                "subs": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 96, 97]
            },
            "TP9r_net1": {
                "transform": "any",
                "base": "TP9r",
                "name": "TP9r_net1",
                "vals": [1],
                "subs": [1, 2]
            },
            "TP9r_net2": {
                "transform": "any",
                "base": "TP9r",
                "name": "TP9r_net2",
                "vals": [1],
                "subs": [96, 97]
            }
        }
    }
}
```

### template.ejs (UNION ALL Pattern)

```ejs
<%
 let params = table.properties
 let Protobi = require(''../util/process_utils_sql")
%>

DROP TABLE IF EXISTS cmbinfo_general.data_global CASCADE;

CREATE TABLE cmbinfo_general.data_global
AS
<% let first = true; %>
<% for (let tableKey in params.tables) { %>
<%= !first ? ' UNION ALL \n' : ''%>
SELECT
    <%=Protobi.expand_fields(params, {key: tableKey}) %>
FROM cmbinfo_general.<%=params.tables[tableKey]%>
WHERE global = 1
<% first = false %>
<% } %>
;
```

### Generated SQL (excerpt)

```sql
DROP TABLE IF EXISTS cmbinfo_general.data_global CASCADE;

CREATE TABLE cmbinfo_general.data_global
AS
SELECT
    cmbid::VARCHAR,
    quarter::VARCHAR,
    hcountry::INT,
    country_region::INT,
    weight::FLOAT,
    WEIGHT_TOKYOAUG::FLOAT,
    WEIGHT_INDIAAUG::FLOAT,
    TP18r99::INT,
    tp18r1::VARCHAR,
    global::INT,
    CASE WHEN hcountry = 10 THEN 2 ELSE country_region END::INT AS country_region2,
    COALESCE(weight::FLOAT, WEIGHT_TOKYOAUG::FLOAT, WEIGHT_INDIAAUG::FLOAT)::FLOAT AS cweight,
    CASE WHEN TP18r99 = 1 THEN '-1' ELSE tp18r1 END::VARCHAR AS tp18r1a,
    CASE WHEN TP9r97 IS NULL AND TP9r1 IS NULL THEN NULL ELSE (
      (CASE WHEN TP9r1::INT IN (1) THEN 1 ELSE 0 END)
    + (CASE WHEN TP9r2::INT IN (1) THEN 1 ELSE 0 END)
    + ... [17 more]
) END::INT AS TP9r_net_others,
    CASE WHEN TP9r1::INT IN (1) OR TP9r2::INT IN (1) THEN 1 WHEN TP9r1 IS NULL AND TP9r2 IS NULL THEN NULL ELSE 0 END::INT AS TP9r_net1,
    CASE WHEN TP9r96::INT IN (1) OR TP9r97::INT IN (1) THEN 1 WHEN TP9r96 IS NULL AND TP9r97 IS NULL THEN NULL ELSE 0 END::INT AS TP9r_net2,
    1::INT AS sample_main,
    1::INT AS sample_main_plus
FROM cmbinfo_general.data_202512
WHERE global = 1
 UNION ALL
SELECT
    cmbid::VARCHAR,
    quarter::VARCHAR,
    ... [same columns]
FROM cmbinfo_general.data_202509
WHERE global = 1
;
```

## Best Practices

### 1. Column Naming

- Use consistent prefixes (`bo_`, `dt_`, `rl_`, `st_`) for type grouping
- Name net variables with `_net` suffix for clarity
- Use descriptive names for calculated fields

### 2. Type Definitions

- Always define `_else_` as fallback
- Use prefix matching for column families
- Be explicit for exceptions

### 3. Substitutions

- Keep `general` for common logic
- Use table-specific only when truly different
- Document complex expressions with comments

### 4. Net Variables

- Place in `substitutions.general` for consistency
- Include `name` property (same as column name)
- Use descriptive null conditions for `sum` transforms

### 5. Template Organization

```ejs
<%
// 1. Setup
let params = table.properties
let Protobi = require(''../util/process_utils_sql')

// 2. Optional: Additional calculations or context
let source_tables = Object.keys(params.tables)
%>

-- 3. Drop existing objects
DROP TABLE IF EXISTS...

-- 4. Create new table
CREATE TABLE...

-- 5. Optional: Create projections, views, etc.
```

### 6. Version Control

- Keep `.ejs` and `.properties.json` together
- Document any breaking changes in properties structure
- Include generated SQL in commits for review

### 7. Testing

1. Test with small data first
2. Verify column count matches: `SELECT COUNT(*) FROM v_catalog.columns WHERE...`
3. Check data types: `SELECT * FROM v_catalog.columns WHERE table_name = '...'`
4. Validate net variable logic with known data

## Common Patterns

### YTD/EOY Date Handling

```json
"constants": {
    "ytd": {
        "2024-EOY": "'2024-11-22'",
        "2024-YTD": "'2023-12-04'"
    }
},
"substitutions": {
    "general": {
        "dt_current": "${ytd[key]}"
    }
}
```

### Conditional Flags

```json
"substitutions": {
    "general": {
        "bo_eoyfile": "${key.includes('EOY') ? 1 : 0}",
        "bo_ytdfile": "${key.includes('YTD') ? 1 : 0}"
    }
}
```

### Multi-way COALESCE

```json
"substitutions": {
    "general": {
        "rl_score": "COALESCE(rl_sat, rl_act, rl_gpa * 100, 0)"
    }
}
```

### Table-Specific Edge Cases

```json
"substitutions": {
    "general": {
        "bo_flag": "bo_flag"
    },
    "2023-YTD": {
        "bo_flag": "CASE WHEN dt_date <= '2023-06-01' THEN 1 ELSE bo_flag END"
    }
}
```

## Troubleshooting

### Issue: "Column count mismatch in UNION ALL"

**Cause:** Tables have different column sets, or substitutions produce different columns per table.

**Solution:** Ensure `columns` array is comprehensive and substitutions define all columns for all tables (use `null` for missing columns in specific tables).

### Issue: "Type mismatch in UNION ALL"

**Cause:** Same column has different types in different SELECT statements.

**Solution:**
1. Check `types` definitions are consistent
2. Verify substitutions don't override types inadvertently
3. Use explicit `::TYPE` casts in complex substitutions

### Issue: "Net variable not generating SQL"

**Cause:** Transform object not recognized.

**Solution:**
1. Ensure field is in `columns` array
2. Check transform object is in `substitutions.general[field]`
3. Verify `transform` property is "any" or "sum"
4. Confirm all required properties present (base, name, vals, subs)

### Issue: "Template interpolation not working"

**Cause:** Missing context variables in `expand_fields()` options.

**Solution:**
```ejs
<%
  // Pass all needed variables in options
%>
 <%=Protobi.expand_fields(params, {key: tableKey, year: tableYear, ...}) %>

```

## Migration Guide

### From Custom `sub()` Function

**Before:**
```ejs
<% function sub(spec) { ... } %>
<%=sub(params.nets["BB16r_net1"]) %>
```

**After:**
```json
"substitutions": {
    "general": {
        "BB16r_net1": {
            "transform": "any",
            "base": "BB16r",
            "name": "BB16r_net1",
            "vals": [1],
            "subs": [1, 2, 3]
        }
    }
}
```

```ejs
<%=Protobi.expand_fields(params, {key: tableKey}) %>
```

### From `SELECT *`

**Before:**
```sql
SELECT *, calculated_field, net_variable
```

**After:**
```json
"columns": ["col1", "col2", "col3", "calculated_field", "net_variable"]
```

All columns must be explicit in UNION ALL contexts.

## Reference

### Protobi Methods API

#### `expand_fields(params, options)`
- **Purpose:** Generate SELECT clause
- **Returns:** String (comma-separated columns with expressions and types)
- **Context:** Table key in `options.key` for table-specific substitutions

#### `define_fields(params)`
- **Purpose:** Generate CREATE TABLE column definitions
- **Returns:** String (comma-separated column names and types)
- **Note:** Ignores substitutions, uses only types

#### `generate_net_sql(spec, args)`
- **Purpose:** Generate CASE statements for net variables
- **Called by:** `expand_fields()` automatically when transform object detected
- **Supports:** "any" and "sum" transforms

### Properties Schema

```typescript
interface Properties {
    tables: { [key: string]: string }
    columns: string[]
    types: { [field: string]: string }
    substitutions: {
        general?: { [field: string]: string | TransformObject }
        [tableKey: string]: { [field: string]: string | TransformObject }
    }
    constants?: any
}

interface TransformObject {
    transform: "any" | "sum"
    base: string
    name: string
    vals: any[]
    subs: (string | number)[]
    null?: string  // Only for "sum"
}
```
## AI Guidance
When AI helps write Protobi SQL data processes, it follows this standard pattern whenever possible.
In this pattern, AI:
  - keeps SQL structure in `.ejs`
  - keeps table definitions, columns, types, substitutions, and constants in `.properties.json`
  - uses `Protobi.expand_fields()` to generate `SELECT` clauses
  - uses `Protobi.define_fields()` to generate column definitions for `CREATE TABLE` statements
  - avoids repetitive handwritten SQL when this pattern fits the problem
  - preserves explicit column order, type definitions, and table-specific overrides

## Summary

This standard pattern provides:
- **Declarative configuration** over imperative code
- **Reusable utilities** across all projects
- **Type safety** through explicit definitions
- **Maintainability** with clear separation of structure and logic
- **Flexibility** with two proven template patterns

Follow this guide for all new SQL data process development to ensure consistency and leverage the full power of the Protobi framework.

Publishing

Date

Status

Slug edit

Content

Thumbnail

Categories Manage

Tools

AI Database

Publishing

Content

Tools

Danger zone