Dynamic Content in Azure Data Factory: Expressions and Functions

Dynamic content in Azure Data Factory allows you to use expressions and functions to create flexible, adaptive pipelines. Instead of hardcoding values, you can dynamically generate paths, queries, and configurations at runtime.

Expression Syntax Basics

// Basic expression format
{
    "value": "@pipeline().parameters.myParameter",
    "type": "Expression"
}

// Using functions
{
    "value": "@concat('prefix_', pipeline().parameters.tableName, '_suffix')",
    "type": "Expression"
}

// Conditional expression
{
    "value": "@if(equals(pipeline().parameters.environment, 'prod'), 'production-server', 'dev-server')",
    "type": "Expression"
}

String Functions

{
    "activities": [
        {
            "name": "DynamicStringExamples",
            "type": "SetVariable",
            "typeProperties": {
                "variableName": "result",
                "value": {
                    "value": "@concat(
                        'Processing file: ',
                        toUpper(pipeline().parameters.fileName),
                        ' at ',
                        formatDateTime(utcnow(), 'yyyy-MM-dd HH:mm:ss')
                    )",
                    "type": "Expression"
                }
            }
        }
    ]
}

Common string functions:

• concat() - Concatenate strings
• substring() - Extract substring
• replace() - Replace text
• split() - Split into array
• trim(), toLower(), toUpper() - String manipulation
• contains(), startsWith(), endsWith() - String testing

Date and Time Functions

{
    "name": "DateTimeExamples",
    "properties": {
        "activities": [
            {
                "name": "SetDateVariables",
                "type": "SetVariable",
                "typeProperties": {
                    "variableName": "datePath",
                    "value": {
                        "value": "@concat(
                            'data/',
                            formatDateTime(utcnow(), 'yyyy'),
                            '/',
                            formatDateTime(utcnow(), 'MM'),
                            '/',
                            formatDateTime(utcnow(), 'dd')
                        )",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "CalculateDateRange",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "SetDateVariables",
                        "dependencyConditions": ["Succeeded"]
                    }
                ],
                "typeProperties": {
                    "variableName": "dateRange",
                    "value": {
                        "value": "@json(concat('{\"startDate\": \"', formatDateTime(adddays(utcnow(), -7), 'yyyy-MM-dd'), '\", \"endDate\": \"', formatDateTime(utcnow(), 'yyyy-MM-dd'), '\"}'))",
                        "type": "Expression"
                    }
                }
            }
        ]
    }
}

Collection Functions

{
    "name": "CollectionExamples",
    "type": "ForEach",
    "typeProperties": {
        "items": {
            "value": "@split(pipeline().parameters.tableList, ',')",
            "type": "Expression"
        },
        "activities": [
            {
                "name": "ProcessTable",
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "AzureSqlSource",
                        "sqlReaderQuery": {
                            "value": "@concat('SELECT * FROM ', item())",
                            "type": "Expression"
                        }
                    }
                }
            }
        ]
    }
}

// Array manipulation examples
{
    "firstItem": "@first(activity('Lookup').output.value)",
    "lastItem": "@last(activity('Lookup').output.value)",
    "itemCount": "@length(activity('Lookup').output.value)",
    "filteredItems": "@filter(activity('Lookup').output.value, item => item.isActive == true)",
    "containsValue": "@contains(activity('Lookup').output.value, 'searchString')"
}

Conditional Logic

{
    "name": "ConditionalCopy",
    "type": "Copy",
    "typeProperties": {
        "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": {
                "value": "@if(
                    equals(pipeline().parameters.loadType, 'Full'),
                    concat('SELECT * FROM ', pipeline().parameters.tableName),
                    concat(
                        'SELECT * FROM ',
                        pipeline().parameters.tableName,
                        ' WHERE ModifiedDate > ''',
                        pipeline().parameters.lastLoadDate,
                        ''''
                    )
                )",
                "type": "Expression"
            }
        },
        "sink": {
            "type": "AzureSqlSink",
            "preCopyScript": {
                "value": "@if(
                    equals(pipeline().parameters.loadType, 'Full'),
                    concat('TRUNCATE TABLE staging.', pipeline().parameters.tableName),
                    ''
                )",
                "type": "Expression"
            }
        }
    }
}

System Variables

{
    "name": "UseSystemVariables",
    "properties": {
        "activities": [
            {
                "name": "LogPipelineInfo",
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "sp_LogPipelineRun",
                    "storedProcedureParameters": {
                        "PipelineName": {
                            "value": "@pipeline().Pipeline",
                            "type": "String"
                        },
                        "RunId": {
                            "value": "@pipeline().RunId",
                            "type": "String"
                        },
                        "TriggerName": {
                            "value": "@pipeline().TriggerName",
                            "type": "String"
                        },
                        "TriggerTime": {
                            "value": "@pipeline().TriggerTime",
                            "type": "DateTime"
                        },
                        "FactoryName": {
                            "value": "@pipeline().DataFactory",
                            "type": "String"
                        },
                        "GroupId": {
                            "value": "@pipeline().GroupId",
                            "type": "String"
                        }
                    }
                }
            }
        ]
    }
}

Building Dynamic Queries

{
    "name": "DynamicQueryBuilder",
    "properties": {
        "parameters": {
            "columns": { "type": "Array" },
            "tableName": { "type": "String" },
            "filterColumn": { "type": "String" },
            "filterValue": { "type": "String" },
            "orderByColumn": { "type": "String" }
        },
        "activities": [
            {
                "name": "BuildAndExecuteQuery",
                "type": "Lookup",
                "typeProperties": {
                    "source": {
                        "type": "AzureSqlSource",
                        "sqlReaderQuery": {
                            "value": "@concat(
                                'SELECT ',
                                if(
                                    empty(pipeline().parameters.columns),
                                    '*',
                                    join(pipeline().parameters.columns, ', ')
                                ),
                                ' FROM ',
                                pipeline().parameters.tableName,
                                if(
                                    empty(pipeline().parameters.filterValue),
                                    '',
                                    concat(
                                        ' WHERE ',
                                        pipeline().parameters.filterColumn,
                                        ' = ''',
                                        pipeline().parameters.filterValue,
                                        ''''
                                    )
                                ),
                                if(
                                    empty(pipeline().parameters.orderByColumn),
                                    '',
                                    concat(' ORDER BY ', pipeline().parameters.orderByColumn)
                                )
                            )",
                            "type": "Expression"
                        }
                    },
                    "dataset": {
                        "referenceName": "GenericSqlDataset",
                        "type": "DatasetReference"
                    }
                }
            }
        ]
    }
}

JSON Manipulation

{
    "name": "JsonManipulation",
    "properties": {
        "activities": [
            {
                "name": "ParseJsonConfig",
                "type": "SetVariable",
                "typeProperties": {
                    "variableName": "config",
                    "value": {
                        "value": "@json(pipeline().parameters.configJson)",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "ExtractValue",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ParseJsonConfig",
                        "dependencyConditions": ["Succeeded"]
                    }
                ],
                "typeProperties": {
                    "variableName": "extractedValue",
                    "value": {
                        "value": "@variables('config').settings.targetTable",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "BuildJsonOutput",
                "type": "SetVariable",
                "typeProperties": {
                    "variableName": "outputJson",
                    "value": {
                        "value": "@string(json(concat(
                            '{',
                            '\"status\": \"success\",',
                            '\"processedAt\": \"', utcnow(), '\",',
                            '\"recordCount\": ', activity('CopyData').output.rowsCopied,
                            '}'
                        )))",
                        "type": "Expression"
                    }
                }
            }
        ]
    }
}

Common Expression Patterns

# Python helper to generate ADF expressions
class ADFExpressionBuilder:
    @staticmethod
    def date_partition_path(base_path, date_param='utcnow()'):
        """Generate date-partitioned path expression"""
        return f"@concat('{base_path}/', formatDateTime({date_param}, 'yyyy/MM/dd'))"

    @staticmethod
    def incremental_query(table_name, watermark_column, last_watermark_param):
        """Generate incremental load query"""
        return f"""@concat(
            'SELECT * FROM {table_name} WHERE {watermark_column} > ''',
            {last_watermark_param},
            ''''
        )"""

    @staticmethod
    def safe_table_name(schema_param, table_param):
        """Generate safe table reference"""
        return f"@concat('[', {schema_param}, '].[', {table_param}, ']')"

    @staticmethod
    def error_handling_message(activity_name):
        """Generate error message expression"""
        return f"""@concat(
            'Activity {activity_name} failed. ',
            'Error: ', activity('{activity_name}').error.message,
            ' | Code: ', activity('{activity_name}').error.errorCode
        )"""

Best Practices

1. Use variables for complex expressions: Break down into readable parts
2. Validate inputs: Check for null/empty before using
3. Handle edge cases: Use coalesce() and if() for defaults
4. Test expressions: Use the expression builder in ADF UI
5. Document complex logic: Add comments in pipeline descriptions

Dynamic content transforms Azure Data Factory from a simple copy tool into a powerful, flexible data integration platform capable of handling complex, data-driven workflows.