---

Working with SCD Type 2 Tables

If your data source is an SCD (Slowly Changing Dimensions) Type 2 table, you can add special settings in PuppyGraph's schema to make it easier to query data at specific points in time using Cypher.

Setting Up SCD2 Information in Schema

SCD2 Configuration

Before adding SCD2 settings, make sure the relevant columns are already mapped as attributes in the schema.

In your PuppyGraph node or edge definition, include an scd2Config section like this:

"scd2Config": {
    "effectiveTime": "start_time",
    "expirationTime": "end_time",
    "currentExpirationTimeValue": "9999-12-31",
    "currentFlag": "is_current",
    "currentFlagValue": "Y"
}

Here's what each setting means:

effectiveTime: The attribute name for the column that stores when the record becomes active. A NULL value means the record has been active since the beginning.
expirationTime: The attribute name for the column that stores when the record expires
currentExpirationTimeValue: The value in the expiration time column that marks a current record. Omit it if current records store NULL there.
currentFlag: The attribute name for the column that marks whether a record is current
currentFlagValue: The value that indicates a record is currently active

effectiveTime, expirationTime, and currentFlag each name an attribute of the node or edge, not a source column. When a source column is renamed through mappedField, use the targetFieldName here. currentExpirationTimeValue and currentFlagValue are literal values compared against those attributes.

Note: If your expiration time column is a string column that uses the string 'null' to represent current records, set "currentExpirationTimeValue": "null". Do not use "null" for a NULL in a date or timestamp column; omit the setting instead.

How queries use these settings:

A point-in-time query (SnapShotTime: '<date>') returns the records whose effective time is at or before the date and whose expiration time is after it. A NULL expiration time is handled according to currentExpirationTimeValue:
- Without currentExpirationTimeValue, a NULL expiration time means the record has not expired, so the record is returned for every date at or after its effective time. This applies whether or not currentFlag is set.
- With currentExpirationTimeValue, that value marks current records, and a record whose expiration time is NULL is never returned by a point-in-time query.
A NULL effective time means the record has been active since the beginning, whatever the configuration. Such a record is returned for every date before it expires, by the rules above. So a record with both times NULL is returned for every date when currentExpirationTimeValue is omitted, and never when it is set.

In PuppyGraph 1.13.0 and earlier, a NULL effective or expiration time never matched a point-in-time query, whatever the configuration. Storing a far-future value such as 9999-12-31 in the expiration column, the earlier workaround, still works.
A 'current' query uses the current flag when currentFlag is set: it returns records whose flag is true, or equal to currentFlagValue if that is set. Without a current flag, it returns records whose expiration time equals currentExpirationTimeValue, or is NULL when currentExpirationTimeValue is omitted. When a current flag is set, currentExpirationTimeValue is not used.

You can use these configuration combinations:

Time range only: Set effectiveTime and expirationTime (plus currentExpirationTimeValue if needed). Both time fields must be the same data type.
Current flag only: Set currentFlag (plus currentFlagValue if needed).
Both time range and current flag: Use all relevant settings.

Complete Schema Example

Here's a full example showing how SCD2 configuration fits into node and edge definitions:

Suppose the data source table definitions are:

CREATE TABLE modern.person (
  id          String,
  name        String,
  age         Int,
  _start      TimeStamp,
  _end        TimeStamp,
  is_current  Boolean
);

CREATE TABLE modern.knows (
  id              String,
  from_id         String,
  to_id           String,
  weight          Double,
  _start          TimeStamp,
  _end            TimeStamp,
  is_current_str  String
)

We can create PuppyGraph schema with SCD2 config. In the node, mappedField renames the _start, _end, and is_current source columns to start_time, end_time, and current_flag. In the edge, it renames _start, _end, and is_current_str to start_time, end_time, and current_flag_str. Fields that keep their source name, such as id and name, are listed too, because mappedField is the complete field list. Each scd2Config refers to its own attribute names. Current records store 9999-12-31 in _end. Storing NULL there would also work, because neither element sets currentExpirationTimeValue. Both elements set a current flag, which 'current' queries use, so currentExpirationTimeValue is not needed:

{
  "catalog": [
    {
      "name": "my_catalog"
    }
  ],
  "node": [
    {
      "label": "Person",
      "id": [{ "name": "id", "type": "STRING" }],
      "attribute": [
        { "name": "name", "type": "STRING" },
        { "name": "age", "type": "INT" },
        { "name": "start_time", "type": "DATETIME" },
        { "name": "end_time", "type": "DATETIME" },
        { "name": "current_flag", "type": "BOOLEAN" }
      ],
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "my_catalog",
          "schema": "modern",
          "table": "person",
          "mappedField": [
            { "sourceFieldName": "id", "targetFieldName": "id" },
            { "sourceFieldName": "name", "targetFieldName": "name" },
            { "sourceFieldName": "age", "targetFieldName": "age" },
            { "sourceFieldName": "_start", "targetFieldName": "start_time" },
            { "sourceFieldName": "_end", "targetFieldName": "end_time" },
            { "sourceFieldName": "is_current", "targetFieldName": "current_flag" }
          ]
        }
      },
      "scd2Config": {
        "effectiveTime": "start_time",
        "expirationTime": "end_time",
        "currentFlag": "current_flag"
      }
    }
  ],
  "edge": [
    {
      "label": "KNOWS",
      "fromNodeLabel": "Person",
      "toNodeLabel": "Person",
      "id": [{ "name": "id", "type": "STRING" }],
      "fromKey": [{ "name": "from_id", "type": "STRING" }],
      "toKey": [{ "name": "to_id", "type": "STRING" }],
      "attribute": [
        { "name": "from_id", "type": "STRING" },
        { "name": "to_id", "type": "STRING" },
        { "name": "weight", "type": "DOUBLE" },
        { "name": "start_time", "type": "DATETIME" },
        { "name": "end_time", "type": "DATETIME" },
        { "name": "current_flag_str", "type": "STRING" }
      ],
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "my_catalog",
          "schema": "modern",
          "table": "knows",
          "mappedField": [
            { "sourceFieldName": "id", "targetFieldName": "id" },
            { "sourceFieldName": "from_id", "targetFieldName": "from_id" },
            { "sourceFieldName": "to_id", "targetFieldName": "to_id" },
            { "sourceFieldName": "weight", "targetFieldName": "weight" },
            { "sourceFieldName": "_start", "targetFieldName": "start_time" },
            { "sourceFieldName": "_end", "targetFieldName": "end_time" },
            {
              "sourceFieldName": "is_current_str",
              "targetFieldName": "current_flag_str"
            }
          ]
        }
      },
      "scd2Config": {
        "effectiveTime": "start_time",
        "expirationTime": "end_time",
        "currentFlag": "current_flag_str",
        "currentFlagValue": "Y"
      }
    }
  ]
}

Fill in the connection details for my_catalog, or drop the catalog[] block entirely and reference an already-registered catalog by name. See Managing the Graph for why a catalog[] entry that differs from an already-registered catalog is rejected by default.

Querying SCD2 Tables with Cypher

To query data at a specific point in time, use the SnapShotTime option in your query (not available if configured as current flag only):

USING {SnapShotTime: '2025-01-01'} MATCH (n) RETURN n

For current data, use the keyword 'current':

USING {SnapShotTime: 'current'} MATCH (n) RETURN n

Format guidelines:

If your time columns use DATE or DATETIME types, provide dates as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss
If your time columns use numeric types (like timestamps), provide the numeric value directly
Type conversion is not automatic, and type casting is not allowed here either, so match the format to your column's data type