Get Timestamp

Overview

The Get timestamp transform makes it easier to create dynamic staging pipelines where it is possible to run the pipeline if the table are missing (first runtime).

This transform retrieves the maximum timestamp value from a specified column in a table. If the table or column doesn’t exist, it returns a default value, making it ideal for incremental load scenarios where you need to track the last processed timestamp.

Use Case

This transform is commonly used in incremental load patterns to determine which records to process in the next pipeline run.

Configuration Options

Option	Description
Transform name	Name of the step
Connection	Select predefined database connection
Schema name	The name of the Schema for the table to read data from
Table name	Name of the source table
Column name	Name of the table column with timestamp
Default value	Use this value if no value found (missing table of column)
Result fieldname	The name of the field to put the result of the sql

How It Works

The transform executes a SQL query to get the maximum value from the specified column:

SELECT MAX(column_name) FROM schema_name.table_name

If the table or column doesn’t exist, or if the query returns NULL, the transform returns the Default value instead. This makes the pipeline resilient to missing tables, which is useful for first-time runs.

Default Value

Common default values include:

• '1900-01-01' for date columns
• '1900-01-01 00:00:00' for datetime columns
• '1970-01-01' for Unix timestamp-based systems

Best Practices

Incremental Load Pattern

This transform is typically used in the following pattern:

1. Get Timestamp → Get last successful run timestamp (or default for first run)
2. Input Transform → Read data filtered by timestamp (e.g., WHERE modified_date > ${LAST_TIMESTAMP})
3. Transform → Process and transform the data
4. Output → Write to target table
5. Update Timestamp → Store new timestamp for next run (can be done in a separate step or workflow)

Default Value Strategy

Choose your default value carefully:

• Too old: May cause processing of too much historical data on first run
• Too recent: May miss data that should be processed
• NULL handling: Ensure your default value is compatible with your timestamp column type

Performance Considerations

• Index the timestamp column: Ensure the timestamp column used for filtering has an index
• Use appropriate data types: Use proper date/datetime types rather than strings
• Timezone awareness: Be aware of timezone issues when comparing timestamps

Common Use Cases

• Incremental data loads: Get last processed timestamp to load only new/changed records
• Staging pipelines: Track last successful staging run
• Data synchronization: Determine which records need to be synchronized
• Audit trails: Track when data was last processed

Related

• Transforms Overview - Overview of all transforms
• Staging Upsert Output - Upsert to staging tables