Calculated Models

Calculated Models are the brain of the Gojjam engine. They allow you to use SQL logic to dynamically determine request parameters—such as pagination tokens, date filters, or offsets—before the engine makes an API call.

The Concept: SQL-Driven Orchestration

Most data tools require you to write complex Python loops to handle "Next Page" or incremental cursor logic. In Gojjam, you simply write a SQL query. Gojjam executes this query against your State Metadata, calculates the next value, and injects it directly into your source request (either into the URL or the JSON body).

---

How it Works

When a source is configured with a cursor, Gojjam follows this internal processing loop:

1. State Retrieval: Gojjam fetches the last_state from your metadata storage layer (e.g., PostgreSQL or DuckDB).
2. Logic Execution: Gojjam executes your Calculated Model SQL. This query evaluates the current state and computes the next tracking value.
3. Injection: The output of the SQL query is injected seamlessly into the outbound API request.
4. Extraction: The API is called, and data is streamed into the engine.
5. State Update: Upon a successful batch extraction, the new tracking value is saved as the current state.

---

Example: Time-Based Pagination

If you are extracting posts from an API that expects a Unix timestamp and you want to fetch data one day at a time, your calculated model looks like this:

File: calculated_models/posts_paginator.sql

-- This model calculates the next day's timestamp (adding 24 hours in seconds)
SELECT
    created_at + 86400 AS created_at
FROM posts_paginator;

What is posts_paginator in the FROM clause? Inside the Calculated Model context, Gojjam creates a virtual table named after your calculated_model_name. This table contains a single row representing the current state of your tracking columns.

---

Configuration

In your gojjam_ingest_sources.yml, configure the cursor block directly inside the data source that requires pagination:

version: "1.0"
sources:
  - name: "users_api"
    type: "http"
    endpoint: "https://api.example.com/v1/posts"
    http_method: "POST"

    # Define the dynamic loop logic for this specific source
    cursor:
      cursor_type: "SYNC" # Use 'SYNC' for continuous looping until the response is empty
      state: "STATEFUL"
      calculated_model_name: "posts_paginator"
      calculated_model_folder_path: "./calculated_models"
      calculated_model_column_names:
        - name: "created_at"
          initial_value: 1715817600 # Starting point if no run history exists
      value_location:
        type: "BODY"
        location:
          timestamps: created_at # Maps the SQL column 'created_at' to the JSON key 'timestamps'

---

Cursor Attributes Reference

Attribute	Description
cursor_type	Defines the execution strategy. Use SYNC for continuous loops (useful for deep pagination until the API payload is empty) or INC for incremental batch loads (runs the SQL query exactly once per pipeline execution).
state	Determines whether the engine tracks historical runs. Set to STATEFUL to enable metadata tracking across pipeline runs, or STATELESS if you want configurations to reset on every execution.
calculated_model_name	The name of your dynamic SQL file (excluding the .sql extension). Gojjam uses this name to generate the internal virtual table referenced in your SQL FROM clause.
calculated_model_folder_path	The local relative directory path where your calculated model SQL files are stored (e.g., ./calculated_models).
calculated_model_column_names	A structural list of tracking columns computed by your SQL query. Each item requires a name (matching your SQL select alias) and an initial_value to use as a fallback if no execution history exists in the metadata database.
value_location	Specifies where the calculated values should be injected into the outbound HTTP request. Supports configurations like BODY, QUERY_PARAMS, or HEADERS.
location	A key-value mapping that binds your calculated SQL column names to the exact parameter names expected by the external API endpoint (e.g., mapping a created_at column to a timestamps JSON key).

---

Sync vs. Inc Modes

Calculated models behave differently based on the cursor_type:

1. INC (Incremental)

The engine runs the calculated model once per execution window.

• Use Case: Standard daily batch jobs where you only need to fetch new or modified records since the last scheduled run.
• Flow: Calculate --> Extract --> Update State --> Terminate.

2. SYNC (Synchronous Loop)

The engine enters a continuous execution loop. It updates the calculated model values and extracts records repeatedly in the same run.

• Use Case: Backfilling deep historical datasets or iterating through millions of records where you must sequentially follow a "Next Page" token or sliding timestamp window.
• Flow: Calculate --> Extract --> Update State --> Repeat (until the API returns an empty payload).

---

Why Use SQL for This?

1. Transparency: Anyone who knows basic SQL can inspect, audit, or understand exactly how the project's pagination logic behaves.
2. Flexibility: You can leverage advanced SQL functions (like DATE_TRUNC, native CASE statements, or mathematical operators) to handle non-standard, erratic API behaviors.