Lookup and Get Metadata Activities in ADF

Most activities in Azure Data Factory do something to data. Two of them only look. The Lookup activity reads a small amount of data and hands it to the pipeline. The Get Metadata activity reads facts about data: whether a file exists, how big it is, what a folder contains. Between them they supply nearly everything a pipeline needs to decide what to do next, which makes them the foundation of every dynamic design.

The JSON below is written from Microsoft's documentation and has not been deployed.

Lookup: read a few rows

Lookup runs a query or stored procedure, or reads a file, through a dataset, and returns the result to the pipeline. It has three limits, all documented (Microsoft Learn: Lookup activity):

• Up to 5000 rows. If the result has more, only the first 5000 are returned, with no error.
• Up to 4 MB of output. Beyond that the activity fails.
• A query must return exactly one result set.

Those limits say what Lookup is for: configuration and control values, not data. A table list, a watermark, a row count, a setting.

One row or many

A single property, firstRowOnly, changes the shape of the output.

One row: reading a watermark.

{
  "name": "GetOldWatermark",
  "type": "Lookup",
  "typeProperties": {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": "SELECT WatermarkValue FROM etl.watermark WHERE TableName = 'Orders'"
    },
    "dataset": { "referenceName": "ControlDb", "type": "DatasetReference" },
    "firstRowOnly": true
  }
}

The value is then @activity('GetOldWatermark').output.firstRow.WatermarkValue. Incremental data loading uses exactly this.

Many rows: a list to loop over.

{
  "name": "GetTableList",
  "type": "Lookup",
  "typeProperties": {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": "SELECT SchemaName, TableName FROM etl.tables_to_load WHERE IsActive = 1"
    },
    "dataset": { "referenceName": "ControlDb", "type": "DatasetReference" },
    "firstRowOnly": false
  }
}

A ForEach then takes @activity('GetTableList').output.value as its items, and inside the loop @item().TableName is one row's column. This is the core of metadata-driven pipelines: the list of work lives in a table, and adding a table to the load is an insert, not a deployment.

Lookup pitfalls

• The default returns one row. Forgetting to set firstRowOnly to false and then looping over a single row is the commonest Lookup mistake.
• Silent truncation at 5000 rows. A control table that grows past the limit loses rows without any failure. Check output.count against what you expect, or page the query.
• Use column names exactly as the query returns them, including their case.
• An empty result. With firstRowOnly true and no rows, there is no firstRow to read, and the next expression fails. Guard with a query that always returns a row, or test before reading.

Get Metadata: read facts about data

Get Metadata takes a dataset and a list of fields, and returns those facts (Microsoft Learn: Get Metadata activity).

The returned metadata is limited to 4 MB.

{
  "name": "ListIncomingFiles",
  "type": "GetMetadata",
  "typeProperties": {
    "dataset": { "referenceName": "IncomingFolder", "type": "DatasetReference" },
    "fieldList": [ "exists", "childItems" ]
  }
}

What it is used for

Does the file exist? Request exists. The detail that matters: if exists is in the field list, the activity does not fail when the object is missing; it returns exists: false. Without it, a missing object fails the activity. So for a file that may legitimately not have arrived, ask for exists and branch on @activity('ListIncomingFiles').output.exists in an If Condition.

What is in this folder? Request childItems and pass @activity('ListIncomingFiles').output.childItems to a ForEach. Each item has a name and a type.

Has it changed? Compare lastModified with the last processed time, or contentMD5 with a stored hash, to skip files already handled.

Is it the shape I expect? Compare columnCount or structure with an expected value before loading, and stop if a source file has gained or lost a column.

Get Metadata pitfalls

• Child items are not recursive. childItems lists one level; items in subfolders are not included. To walk a tree you need a pipeline that calls itself for each subfolder, or a different approach.
• No wildcards. The documentation states that wildcard filters on folders and files are not supported. To select by pattern, list everything and narrow the array with a Filter activity, for example keeping items whose name ends in .csv.
• Folders are in the list too. Filter on type equal to File before looping.
• Large folders. A folder with a very large number of files can exceed the 4 MB output limit. Partition folders by date so each listing is small.

The two together

A typical file-ingestion pipeline uses both:

1. Get Metadata lists the incoming folder.
2. Filter keeps the CSV files.
3. Lookup reads the list of files already processed from a control table.
4. ForEach over the remaining files calls a child pipeline that copies one file and records it.

And a typical database pipeline uses Lookup twice: once for the table list, once per table for its watermark.

Related activities

Two control activities overlap with these and are sometimes the better choice:

• Validation makes a pipeline wait until a dataset exists or meets criteria, or a timeout is reached. For "wait for the file to arrive", it replaces a Get Metadata inside an Until loop.
• Filter applies an expression to an array and returns the matching items. It is what makes Get Metadata's output usable.

When not to use them

• To move data. A Lookup that returns thousands of rows for a ForEach to insert one at a time is a slow, expensive Copy activity.
• For large lists. Past a few thousand items, drive the work from a query inside the Copy source or a child pipeline per batch.
• For secrets. Lookup output appears in run history. Read secrets from Key Vault, and mark output as secure where a value is sensitive.

For looping over what these return, see control flow in ADF; for the syntax of reading activity outputs, ADF expressions and functions.

Azure Data Factory and dynamic pipeline design are covered in the Data Engineering course.

Sources

• Lookup activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Get Metadata activity in Azure Data Factory or Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• ForEach activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Pipelines and activities in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn