Temporal Logic & History

Handling time is one of the hardest parts of data engineering. SqlCAD treats time as a first-class citizen to ensure Point-in-Time Correctness and simplify SCD Type 2 management.

Slow Changing Dimensions (SCD Type 2)

For DIM blocks in the Silver layer, SqlCAD automatically generates the SQL scripts needed to maintain full history. Type 2 logic is implicit for any DEFINE DIM.

Configuration

To define a dimension, simply use the DIM type:

DEFINE DIM Customers {
    primary_key: CustomerID,
    temporal: {
        valid_from_col: "valid_from", -- Optional, defaults to valid_from
        valid_to_col: "valid_to"      -- Optional, defaults to valid_to
    }
} AS ...

Generated Artifacts

When you run sqlcad build, it generates:

View: output/silver/customers.sql (The base transformation).
Load Script: output/silver/customers.load.sql.

The load script handles:

Identifying changes (comparing hash of columns).
Closing out old records (setting valid_to).
Inserting new versions (setting valid_from).

Point-in-Time (PIT) Joins

When joining a Fact table (e.g., Orders) to a historical Dimension (e.g., Customers), a simple equijoin (ON c.id = o.cust_id) is incorrect because it duplicates rows for every version of the customer.

You need to join to the version of the customer that was active at the time the order was placed.

Automated Handling

If you define a FACT (with an event_time_col) and join it to a DIM (which has history), SqlCAD automatically injects the PIT logic into the ON clause.

Definition:

DEFINE FACT Orders {
    temporal: { event_time_col: OrderDate }
}

When you write:

FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Generated SQL (Conceptual):

FROM Orders
JOIN Customers 
    ON Orders.CustomerID = Customers.CustomerID
    AND Orders.OrderDate >= Customers.valid_from
    AND (Orders.OrderDate < Customers.valid_to OR Customers.valid_to IS NULL)

This ensures that your historical reporting is accurate. An order from 2023 will typically match against the customer version active in 2023, preserving the state (like address or name) as it was then.

Feature Engineering

For Machine Learning use cases, refer to the Feature Engineering Guide. SqlCAD uses LATERAL JOINS to ensure features are calculated using only data available prior to the observation timestamp.

Introduction

Concepts

Guides