Fabric Data Factory Expression Language: Dynamic Pipelines with @pipeline(), @activity(), @formatDateTime(), Conditional Logic, and Every Expression You Need

Every production pipeline needs to be DYNAMIC. A pipeline that only works for one table, one date, or one file path is a prototype. A pipeline that accepts parameters, calculates dates, builds file paths dynamically, and makes decisions based on activity outputs — that is production-grade.

The expression language in Fabric Data Factory is what makes pipelines dynamic. It is the same expression language used in Azure Data Factory — if you know ADF expressions, you already know Fabric expressions. If you are new to both, this post teaches you everything from scratch.

Think of a static pipeline like a GPS with one hardcoded destination — it always drives to the same place. The expression language is like a GPS that accepts any address, calculates the best route based on current traffic, and adjusts in real-time. Same pipeline, infinite destinations.

For the complete deep-dive reference with 50+ examples, see our Azure Data Factory Expressions Guide — the syntax is identical in Fabric.

Table of Contents

• Expression Basics
• Where Expressions Are Used
• The @ Symbol and Syntax Rules
• Pipeline Functions
• @pipeline() — Pipeline Context
• @activity() — Activity Output
• @variables() — Pipeline Variables
• @item() — ForEach Current Item
• String Functions
• concat, replace, substring, split, trim, toLower, toUpper
• Date and Time Functions
• utcNow, formatDateTime, addDays, addHours, dayOfWeek, startOfMonth
• Logical Functions
• if, equals, and, or, not, greater, less, coalesce
• Conversion Functions
• int, float, string, bool, json, array
• Math Functions
• add, sub, mul, div, mod
• Collection Functions
• length, first, last, contains, union, intersection
• Dynamic Content Builder (UI)
• Real-World Expression Patterns
• Pattern 1: Yesterday’s Date for Incremental Load
• Pattern 2: Dynamic File Path with Date Partitioning
• Pattern 3: Conditional Full vs Incremental Load
• Pattern 4: Build SQL Query Dynamically
• Pattern 5: Loop Through Table List
• Pattern 6: Pass Notebook Parameters
• Pattern 7: Error Handling with Activity Status
• Pattern 8: Dynamic Email Subject with Run Details
• Fabric-Specific Expressions
• Workspace Context
• Lakehouse and Warehouse References
• ADF vs Fabric Expressions: What Changed
• Common Mistakes
• Interview Questions
• Wrapping Up

Expression Basics

Where Expressions Are Used

Expressions work in ANY text field with the "Add dynamic content" option:

✅ Copy Activity → Source query
✅ Copy Activity → File path
✅ Notebook Activity → Parameters
✅ Set Variable → Value
✅ If Condition → Expression
✅ ForEach → Items
✅ Web Activity → URL, headers, body
✅ Stored Procedure → Parameters
✅ Filter → Condition
✅ Schedule Trigger → Parameters passed to pipeline

The @ Symbol and Syntax Rules

Static value:    sales_2026.csv
Dynamic value:   @concat('sales_', formatDateTime(utcNow(), 'yyyy'), '.csv')
Result:          sales_2026.csv (but changes every year!)

Rules:
  1. Expressions start with @ 
  2. Functions use parentheses: @utcNow()
  3. Nest functions inside functions: @formatDateTime(utcNow(), 'yyyy-MM-dd')
  4. String literals use single quotes: 'hello'
  5. Access properties with dot notation: @pipeline().parameters.tableName
  6. Escape a literal @ with @@: email@@company.com

Pipeline Functions

@pipeline() — Pipeline Context

@pipeline().RunId              → "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
@pipeline().PipelineName       → "PL_Daily_ETL"
@pipeline().TriggerName        → "TR_Daily_6AM"
@pipeline().TriggerTime        → "2026-06-07T06:00:00.0000000Z"
@pipeline().TriggerType        → "ScheduleTrigger"
@pipeline().GroupId            → unique execution group ID

// Parameters (defined in pipeline settings)
@pipeline().parameters.tableName        → "customers"
@pipeline().parameters.loadDate         → "2026-06-07"
@pipeline().parameters.loadType         → "incremental"
@pipeline().parameters.sourceSchema     → "dbo"

@activity() — Activity Output

// Reference output from a previous activity
@activity('Copy_Customers').output.rowsCopied           → 15000
@activity('Copy_Customers').output.rowsRead             → 15200
@activity('Copy_Customers').output.dataWritten           → 4500000 (bytes)
@activity('Copy_Customers').output.copyDuration          → 45 (seconds)

// Lookup activity output
@activity('Get_Config').output.firstRow.watermark_date   → "2026-06-01"
@activity('Get_Config').output.value                     → array of rows
@activity('Get_Config').output.count                     → number of rows

// Notebook activity output (exit value)
@activity('Run_Notebook').output.result.exitValue        → "SUCCESS: 15000 rows"

// Check activity status
@activity('Copy_Customers').status                       → "Succeeded" or "Failed"
@activity('Copy_Customers').error.message                → error message (if failed)

@variables() — Pipeline Variables

// Read a pipeline variable
@variables('currentTable')            → "customers"
@variables('rowCount')                → 0

// Set with Set Variable activity, then read in subsequent activities
// Variables are pipeline-scoped — available to all activities in the pipeline

@item() — ForEach Current Item

// Inside a ForEach loop, @item() is the current element
ForEach items: ["customers", "orders", "products"]

Inside the loop:
  @item()                              → "customers" (first iteration)
  @item()                              → "orders" (second iteration)
  @item()                              → "products" (third iteration)

// For array of objects:
ForEach items: [{"table":"customers","schema":"dbo"}, {"table":"orders","schema":"sales"}]
  @item().table                        → "customers"
  @item().schema                       → "dbo"

String Functions

@concat('Hello', ' ', 'World')                 → "Hello World"
@concat('Files/', pipeline().parameters.folder, '/', formatDateTime(utcNow(), 'yyyy-MM-dd'), '/')
  → "Files/sales/2026-06-07/"

@replace('Hello World', 'World', 'Fabric')     → "Hello Fabric"
@substring('Hello World', 6, 5)                → "World"
@split('a,b,c,d', ',')                         → ["a","b","c","d"]
@trim('  hello  ')                             → "hello"
@toLower('HELLO')                              → "hello"
@toUpper('hello')                              → "HELLO"
@length('Hello')                               → 5
@indexOf('Hello World', 'World')               → 6
@startsWith('Hello', 'He')                     → true
@endsWith('Hello', 'lo')                       → true
@guid()                                        → random GUID string

// URL encoding
@uriComponent('hello world')                   → "hello%20world"
@uriComponentToString('hello%20world')         → "hello world"

// Base64
@base64('hello')                               → "aGVsbG8="
@base64ToString('aGVsbG8=')                    → "hello"

Date and Time Functions

// Current time
@utcNow()                                      → "2026-06-07T14:30:00.0000000Z"
@utcNow('yyyy-MM-dd')                          → "2026-06-07"
@utcNow('yyyyMMdd')                            → "20260607"

// Format any datetime
@formatDateTime(utcNow(), 'yyyy-MM-dd')        → "2026-06-07"
@formatDateTime(utcNow(), 'yyyy/MM/dd')        → "2026/06/07"
@formatDateTime(utcNow(), 'HH:mm:ss')          → "14:30:00"
@formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm')  → "2026-06-07 14:30"
@formatDateTime(utcNow(), 'yyyyMMddHHmmss')    → "20260607143000"

// Date arithmetic
@addDays(utcNow(), -1)                         → yesterday
@addDays(utcNow(), 7)                          → next week
@addHours(utcNow(), -6)                        → 6 hours ago
@addMinutes(utcNow(), 30)                      → 30 minutes from now

// Combine: yesterday's date formatted
@formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')  → "2026-06-06"

// Date parts
@dayOfWeek(utcNow())                           → 0 (Sunday) to 6 (Saturday)
@dayOfMonth(utcNow())                          → 7
@dayOfYear(utcNow())                           → 158

// Start/end of periods
@startOfDay(utcNow())                          → "2026-06-07T00:00:00.0000000Z"
@startOfMonth(utcNow())                        → "2026-06-01T00:00:00.0000000Z"
@startOfHour(utcNow())                         → "2026-06-07T14:00:00.0000000Z"

// Convert string to datetime
@formatDateTime('2026-06-07', 'yyyy-MM-dd')    → datetime object

// Ticks (for unique identifiers)
@ticks(utcNow())                               → 638913234000000000

Logical Functions

// if(condition, trueValue, falseValue)
@if(equals(pipeline().parameters.loadType, 'full'), 'overwrite', 'append')
  → "overwrite" if loadType is "full", else "append"

// Nested if (like CASE WHEN)
@if(greater(activity('Lookup').output.count, 1000), 'large',
  if(greater(activity('Lookup').output.count, 100), 'medium', 'small'))

// Comparison
@equals(pipeline().parameters.mode, 'full')    → true/false
@greater(activity('Lookup').output.count, 0)   → true if count > 0
@greaterOrEquals(variables('retryCount'), 3)   → true if retryCount >= 3
@less(dayOfWeek(utcNow()), 5)                  → true if weekday

// Boolean logic
@and(greater(variables('count'), 0), equals(pipeline().parameters.mode, 'full'))
@or(equals(variables('status'), 'failed'), greater(variables('retryCount'), 3))
@not(equals(pipeline().parameters.mode, 'test'))

// Null handling
@coalesce(pipeline().parameters.customDate, utcNow())
  → uses customDate if provided, otherwise utcNow()

@coalesce(activity('Lookup').output.firstRow.watermark, '1900-01-01')
  → uses watermark if exists, otherwise default date

Conversion Functions

@int('42')                                     → 42
@float('3.14')                                 → 3.14
@string(42)                                    → "42"
@bool('true')                                  → true
@json('{"key":"value"}')                       → object
@array('a')                                    → ["a"]
@createArray('a', 'b', 'c')                    → ["a", "b", "c"]

Math Functions

@add(10, 5)                                    → 15
@sub(10, 5)                                    → 5
@mul(10, 5)                                    → 50
@div(10, 3)                                    → 3
@mod(10, 3)                                    → 1

// Combine: calculate retry delay (exponential backoff)
@mul(30, pipeline().parameters.retryCount)      → 30, 60, 90 seconds

Collection Functions

@length(activity('Lookup').output.value)        → number of rows returned
@first(activity('Lookup').output.value)         → first row
@last(activity('Lookup').output.value)          → last row
@contains(createArray('a','b','c'), 'b')        → true
@empty(activity('Lookup').output.value)         → true if no rows

@union(createArray('a','b'), createArray('b','c'))        → ["a","b","c"]
@intersection(createArray('a','b'), createArray('b','c')) → ["b"]

Dynamic Content Builder (UI)

You do not have to type expressions manually. Fabric provides a visual builder:

1. Click on any text field in a pipeline activity
2. Click Add dynamic content (or click the text field — it auto-opens)
3. The Dynamic Content panel shows:
4. Parameters tab: all pipeline parameters
5. Variables tab: all pipeline variables
6. Activity outputs tab: outputs from completed activities
7. Functions tab: all available functions (string, date, math, etc.)
8. System variables tab: pipeline().RunId, etc.
9. Click to insert, combine in the expression box
10. Click OK

Real-World Expression Patterns

Pattern 1: Yesterday’s Date for Incremental Load

// Lookup activity query:
SELECT * FROM dbo.orders 
WHERE order_date = '@{formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')}'

// Resolves to:
SELECT * FROM dbo.orders WHERE order_date = '2026-06-06'

Pattern 2: Dynamic File Path with Date Partitioning

// Copy Activity → Destination file path:
@concat('Files/raw/',
        pipeline().parameters.tableName, '/',
        formatDateTime(utcNow(), 'yyyy'), '/',
        formatDateTime(utcNow(), 'MM'), '/',
        formatDateTime(utcNow(), 'dd'), '/',
        pipeline().parameters.tableName, '_',
        formatDateTime(utcNow(), 'yyyyMMddHHmmss'), '.parquet')

// Resolves to:
Files/raw/customers/2026/06/07/customers_20260607143000.parquet

Pattern 3: Conditional Full vs Incremental Load

// If Condition activity → Expression:
@equals(pipeline().parameters.loadType, 'full')

// True path: Copy Activity with no filter (full load)
// False path: Copy Activity with WHERE date > watermark (incremental)

// Inside the incremental Copy Activity source query:
SELECT * FROM @{pipeline().parameters.sourceSchema}.@{pipeline().parameters.tableName}
WHERE modified_date > '@{activity('Get_Watermark').output.firstRow.last_watermark}'

Pattern 4: Build SQL Query Dynamically

// Stored Procedure parameter:
@concat('EXEC dbo.usp_load_', pipeline().parameters.tableName,
        ' @load_date = ''', formatDateTime(utcNow(), 'yyyy-MM-dd'), '''',
        ', @mode = ''', pipeline().parameters.loadType, '''')

// Resolves to:
EXEC dbo.usp_load_customers @load_date = '2026-06-07', @mode = 'incremental'

Pattern 5: Loop Through Table List

// Lookup Activity returns:
[{"table":"customers","schema":"dbo"}, {"table":"orders","schema":"sales"}, ...]

// ForEach → Items: @activity('Get_Tables').output.value

// Inside ForEach → Copy Activity:
Source query: SELECT * FROM @{item().schema}.@{item().table}
Destination: @{concat('Tables/', item().table)}

Pattern 6: Pass Notebook Parameters

// Notebook Activity → Base parameters:
{
  "table_name": "@{pipeline().parameters.tableName}",
  "load_date": "@{formatDateTime(utcNow(), 'yyyy-MM-dd')}",
  "load_type": "@{pipeline().parameters.loadType}",
  "pipeline_run_id": "@{pipeline().RunId}"
}

// Notebook reads with:
table_name = mssparkutils.widgets.get("table_name")

Pattern 7: Error Handling with Activity Status

// After a Copy Activity → On Failure path → Set Variable:
Variable: errorMessage
Value: @concat('Pipeline ', pipeline().PipelineName,
               ' failed at activity Copy_', pipeline().parameters.tableName,
               '. Error: ', activity('Copy_Data').error.message,
               '. Run ID: ', pipeline().RunId)

// Then pass to a Teams/Email notification activity

Pattern 8: Dynamic Email Subject with Run Details

// Web Activity (Teams/Outlook) → Subject:
@concat(
  if(equals(activity('Copy_Data').status, 'Succeeded'), '✅', '❌'),
  ' Pipeline ', pipeline().PipelineName,
  ' | ', pipeline().parameters.tableName,
  ' | ', formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm'),
  ' | Rows: ', string(activity('Copy_Data').output.rowsCopied))

// Resolves to:
✅ Pipeline PL_Daily_ETL | customers | 2026-06-07 06:15 | Rows: 15000

Fabric-Specific Expressions

Differences from ADF

Feature	ADF	Fabric Data Factory
Expression syntax	Identical	Identical
@pipeline()	Same	Same
@activity()	Same	Same
@formatDateTime()	Same	Same
Linked Service references	@linkedService()	Uses Connections (different naming, same concept)
Integration Runtime	Referenced in expressions	Not applicable (Fabric manages compute)
Workspace context	Not available	Available via pipeline context
Trigger parameters	Same	Same

Bottom line: If you know ADF expressions, you know Fabric expressions. The syntax is identical. The only differences are infrastructure naming (Connections vs Linked Services).

Common Mistakes

1. Hardcoding dates instead of using expressions — WHERE date = '2026-06-07' works today, fails tomorrow. Use @formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd') for dynamic dates.

2. Not using coalesce for optional parameters — if a parameter might be null, @coalesce(pipeline().parameters.customDate, utcNow()) provides a safe default.

3. Forgetting single quotes in SQL expressions — string values in SQL need quotes. Use: '@{pipeline().parameters.tableName}' not @{pipeline().parameters.tableName}.

4. Not referencing activity output correctly — @activity('Copy1').output.rowsCopied requires the EXACT activity name. Rename an activity and all references break.

5. Using expressions in non-dynamic fields — not every field supports expressions. Look for the “Add dynamic content” link or the blue text box indicator.

6. Overcomplicating expressions — if your expression is 10 lines long, consider using a Lookup activity to fetch the value from a config table instead.

Interview Questions

Q: What is the expression language in Fabric Data Factory? A: A dynamic expression language for building parameterized pipelines. Functions include @pipeline() for context, @activity() for outputs, @formatDateTime() for dates, @if() for conditions, @concat() for strings. The syntax is identical to Azure Data Factory expressions. Expressions make pipelines dynamic — one pipeline handles any table, any date, any load type through parameters.

Q: How do you build an incremental load expression? A: Use a Lookup activity to get the last watermark date, then reference it in the Copy Activity source query: SELECT * FROM table WHERE modified_date > '@{activity('Get_Watermark').output.firstRow.last_watermark}'. Combine with @formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd') for date-based incremental loads.

Q: Are Fabric Data Factory expressions the same as ADF expressions? A: Yes — the syntax is identical. @pipeline(), @activity(), @formatDateTime(), @if(), @concat(), and all other functions work the same way. The only differences are infrastructure-level: Fabric uses Connections instead of Linked Services, and does not reference Integration Runtimes. Expression knowledge transfers directly between ADF and Fabric.

Wrapping Up

The expression language is what transforms a static pipeline into a production-grade, parameterized, dynamic pipeline. Master the eight patterns in this post — dynamic dates, file paths, conditional logic, SQL queries, loops, notebook parameters, error messages, and email subjects — and you can build any pipeline.

For the full 50+ function reference with advanced examples, see our Azure Data Factory Expressions Guide — every expression works identically in Fabric.

Related posts: – ADF Expressions Guide (Full Reference) – Fabric Data Factory & Pipelines – Triggers, Scheduling & Orchestration – Fabric Notebooks (mssparkutils)

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.