Functions activity

With the Functions activity, you can apply property-level transformations to individual records in a stream pipeline. Each function operates on one or multiple input properties and produces a derived property without altering source values. The Functions activity is designed for simple, deterministic transformations that standardize fields before data is written to a destination. The Functions activity is typically used to normalize unsupported source-system formats.

Use the Functions activity to apply these transformations in the pipeline:

  • Standardize date and datetime values that are stored in integer or non standard formats.
  • Generate derived string properties such as composite keys.
  • Prepare data for destinations, such as SQL-based systems, that require standard formats.

Applying those transformations removes the need to implement equivalent logic in destination systems and ensures consistent values across consumers.

These typical use cases are supported by the Functions activity:

  • Creating composite identifiers from multiple properties.
  • Converting integer-based dates to standard ISO date formats.
  • Converting epoch-based or formatted datetime values to the UTC ISO 8601 format.

The Functions activity is intended for targeted property-level transformations.

Tip: Use the Functions activity for lightweight data shaping rather than complex business logic.

These functions are supported by the Functions activity:

  • Concatenate
  • Convert to Date time
  • Convert to Date

Concatenate

The Concatenate function combines multiple input properties and constant values into a single string. You can use the Concatenate function to build composite business keys, readable identifiers, and technical keys for downstream systems. You can also apply the Concatenate function to derive single-column keys from multi-column primary keys. In relational databases such as SQL Server, generating a single, deterministic key reduces index complexity, simplifies join operations, and improves query performance in analytical and reporting workloads.

Typical use cases for the Concatenate function are constructing composite primary keys, creating business identifiers, and adding prefixes and delimiters to values.

You can use an output of the Concatenate function as a stable identifier in analytical or relational destinations.

Creating a composite key by combining a company and item numbers

Input:

  • Company: Company, for example, 100
  • ItemNumber: Item, for example, ABC123

Function parameters: Company, "-", ItemNumber

Output: CompositeItemKey: 100-ABC123

Convert to Date time

The Convert to Date time (Standard) function normalizes integer or string datetime inputs into the UTC ISO 8601 format (YYYY-MM-DDTHH:mm:ssZ). The Convert to Date time (Standard) function is suitable for these sources and targets:

  • Source systems that store datetime values as epoch-based integers or non-standard strings
  • Destinations, such as SQL Server-based targets, that don't support integer or string datetime values during data loading.

By converting values in the pipeline, no destination-side parsing or conversion logic is required. The resulting ISO-compliant UTC timestamp can be consumed directly by SQL Server and analytical systems that expect ISO-formatted timestamps.

Typical use cases for the Convert to Date time (Standard) function are converting epoch timestamps. normalizing event timestamps, and maintaining consistent timezone handling.

Converting an M3 datetime value stored as an epoch value into the UTC ISO 8601 format

Input: TransactionTimestamp: 1708089600000 (epoch milliseconds)

Function parameters: TransactionTimestamp, "epoch-millis"

Output: TransactionTimestampUTC: 2024-02-16T00:00:00Z

Convert to Date

The Convert to Date (Standard) function normalizes integer or string date values into the ISO date format (YYYY-MM-DD). The Convert to Date (Standard) function is useful when source systems store dates as integers. Some destinations, such as SQL Server, don't ingest integer-based date formats. Performing the conversion within a pipeline ensures compatibility and removes the need for custom transformation logic in a destination.

The resulting value can be loaded directly into SQL Server or other relational databases without additional transformations.

Typical use cases for the Convert to Date (Standard) function are normalizing legacy date formats, preparing data for SQL-based destinations, and ensuring consistent date handling across pipelines.

Converting a date stored as an integer into a standard date

Input: OrderDate: 20250211 (integer, format: yyyyMMdd)

Function parameters: OrderDate, "yyyyMMdd"

Output: OrderDateISO: 2025-02-11

Output properties and validation

Each function writes its result to a new property. The source fields are left unchanged. The output property type is determined by the selected function and can't be overwritten.

The function editor provides these options:

  • Auto-complete and parameter suggestions
  • Tooltips and inline guidance
  • Real-time validation with error and warning messages

You can't activate pipelines if any configured function is invalid or incomplete.

Function performance and error handling

You can define multiple functions within a single Functions activity. Functions are evaluated sequentially. If a transformation fails at runtime, affected records are routed to the Transformation error category in the Replay Queue. In the Replay Queue, you can review, correct, replay, or discard the affected records.

See Replay Queue.

This table shows status indicators of functions within the Functions activity:

Status Description
Valid function icon Valid function that is ready to run.
Invalid or incomplete function icon Invalid function because of configuration errors or incomplete function because of missing required parameters.