Dynamic Time series using ADF data flow - rule based mapping

Question

Can i get expression for rule based mapping in data flow using select transform, my dataset is "allow schema drift" enabled, but i want to maintain new schema always don't want to keep old time series headers from mapping.

Let me know if this can be done.

Answer 1

@sankon437

Thanks for using MS Q&A platform and posting your query.

Absolutely, you can achieve dynamic time series processing with Azure Data Factory (ADF) data flow and rule-based mapping, even with schema drift enabled. Here's how:

Rule-Based Mapping for Dynamic Columns:

1. Enable Schema Drift: Ensure "Allow schema drift" is enabled for your source dataset in ADF. This allows handling new or missing columns in the incoming data.
2. Define Rule-Based Mapping: In your data flow, use the Select transformation. When mapping columns, choose "Rule-based mapping" instead of static mapping.
3. Wildcard Matching: Use wildcards like "*" in the rule expression to match any column name in the source data.

Example Expression:

Let's say your time series data has a timestamp column and multiple value columns with varying names (e.g., "Value_1", "Value_2"). You can use the following expression:

output(column) 
  = case when column.type == 'DateTime' then column.value 
       when substring(column.name, 1, 6) == 'Value_' then column.value 
       else null end

Explanation:

This expression checks each column:

• If it's a DateTime type, it's considered the timestamp and copied to the output.
• If the column name starts with "Value_", it's assumed to be a value column and copied to the output.
• Any other column is ignored (mapped to null).

For further reference, you can check out these resources:

• Microsoft Documentation on Rule-Based Mapping: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-select

Hope this helps. Do let us know if you any further queries.