If you’ve worked with pipelines in Microsoft Fabric, you’ve probably tried this at some point:
You have a file in your Lakehouse, and you want to read it using a Lookup activity.
Seems simple enough.
But then it fails.
Or worse, it behaves inconsistently.
I ran into this while building a real pipeline, and it took longer than expected to understand what was actually going on.
This post explains:
- Why Lookup activity struggles with Lakehouse files
- What’s really happening behind the scenes
- How to fix it properly
The Expectation
Coming from Azure Data Factory, you would expect:
- Lookup can read files
- You point it to a path
- It returns data
Something like:
Files/folder/file.json
The Reality in Fabric
In Fabric, Lookup activity is not designed to directly read Lakehouse files in the same way.
It works reliably with:
- SQL endpoints
- Structured sources
- Tables
But when it comes to:
Lakehouse Files (Parquet, JSON, CSV)
It either:
- Fails
- Returns unexpected results
- Or requires workarounds
Why this happens
The core reason is:
Lakehouse Files are not exposed like a traditional queryable source for Lookup
Fabric separates:
- Files layer
- Tables layer
Lookup works best with queryable datasets, not raw file storage.
So when you try to use:
Files/your-folder/file
Lookup does not handle it the same way as a SQL query.
What actually works (Solutions)
1. Use Lakehouse Tables instead of Files
Instead of reading directly from files:
- Load the file into a table first.
- Then use Lookup on the table.
Example flow
Files → Notebook → Table → Lookup
Why this works
Tables are queryable through SQL endpoints, which Lookup supports properly.
2. Use Notebook instead of Lookup
If you need flexibility, use a notebook to read the file:
df = spark.read.parquet(“Files/path/file.parquet”)
Then:
- Process data
- Return values
- Store results in a table or parameter
When to use this
- Complex logic
- File-based operations
- JSON parsing
3. Use Copy Activity instead of Lookup
If your goal is to move or inspect data:
Copy activity handles files much better than Lookup.
4. Store control data in tables
Instead of storing config in files, store it in a small table.
Then use Lookup safely.
What not to do
Avoid relying on Lookup for:
- Reading JSON files directly
- Parsing Parquet files
- Accessing Lakehouse file paths dynamically
It may work in some cases, but it is not reliable.
Key takeaway
The issue is not that Lookup is broken.
It is that:
It is not designed for file-based access in Lakehouse
Once you shift to:
- Files for storage
- Tables for querying
things become much simpler.
Final thoughts
This is one of those small Fabric quirks that can slow you down if you don’t know about it.
Once you understand the separation between files and tables, the solution becomes straightforward.
If you are building Fabric pipelines
Use:
- Tables for Lookup
- Notebooks for file handling
- Pipelines for orchestration
Trying to mix these responsibilities usually leads to issues.
Ization 
Leave a comment