Dynamic Date Functions

This guide explains how to use dynamic date functions such as CURRENT_DATE() and CURRENT_TIMESTAMP() Snowflake and Databricks. These functions let you generate dates and timestamps at query execution time, so they always resolve to the current value instead of a hard-coded string.

Parameters with Dynamic Date Functions

You can define parameters that use built-in date functions. These automatically resolve to the current date or timestamp when a job runs.

• Snowflake: CURRENT_DATE() or CURRENT_TIMESTAMP()
• Databricks: current_date() or current_timestamp()

For example, setting a parameter to CURRENT_DATE() in Snowflake will populate with today’s date whenever the job executes.

Transform Columns

You can use these functions directly in column transforms. The values update each time the node runs, ensuring your data is always fresh.

• Snowflake example:

  CAST(CURRENT_TIMESTAMP AS TIMESTAMP)

• Databricks example:

  cast(current_timestamp() as timestamp)

These are commonly used for system fields like CREATED_AT or UPDATED_AT.

Incremental Loading

Dynamic date expressions are useful for incremental data loads. Instead of hard-coding a static date, you can use relative calculations:

• Snowflake:

  CURRENT_DATE() - 1

• Databricks:

  date_sub(current_date(), 1)

This approach ensures that loads always capture data relative to the current runtime.

Limitations

Some testing utilities only support static date strings and won’t accept dynamic date functions. For example, testUtils.test_missing_dates requires a fixed value rather than CURRENT_DATE().

Platform-Specific Rules

When working across Snowflake and Databricks projects in Coalesce, follow these rules:

• Snowflake
  • Use uppercase functions: CURRENT_DATE(), CURRENT_TIMESTAMP().
  • Relative calculations can be performed directly, such as CURRENT_DATE() - 7.
• Databricks
  • Use lowercase function names: current_date(), current_timestamp().
  • For date math, use functions like date_add() or date_sub().
    • Example: date_add(current_date(), 7)
  • Wrap functions in cast() when needed for type enforcement.

Common Use Cases

Dynamic date functions are typically used in the following scenarios:

• Audit Columns: Automatically populate CREATED_AT or UPDATED_AT columns with the current timestamp whenever a record is inserted or updated.
• Incremental Loads: Filter new or changed data using relative date functions like CURRENT_DATE() - 1 (Snowflake) or date_sub(current_date(), 1) (Databricks).
• Partitioning: Assign data to daily or monthly partitions using the current date, which ensures new data is routed correctly without manual updates.
• System Metadata: Track job execution times or record processing windows by inserting CURRENT_TIMESTAMP() into logging tables.