Forum Discussion
Unstructured Source - Architecture in Fabric
Dears,
With the help of experts from this forum, I was able to desgin an architecture for structured sources
I will past here the diagram for Raw and Bronze layers. As those are the ones I would like to discuss
As you can see from above , I have multiple diferente data sources, which are first ingested into a RAW layer (which has its own workspace) and after, into the Bronze Layer (tough, in bronze, there is no real ingestion, its only enforcement of schemas and using shortcuts from the tables in raw.)
The question to this thread is : I would like to design an architure for unstructured sources
I am thinking In having one dedicated raw zone for it (workspace), and one dedicated bronze zone as well for this unstructured sources. This, as the ingestion is very different from structured, and the people consuming this infromation, will probably , also be very different from the structured infromation
Having that said, I was thinking in something like this:
The source is the sharepoint, which has multiple different files (PDFs, Excel Files, etc...)
Strategy ,
1) As the sharepoint has a lot of files, I was thinking in not bringing the files (binary as-is) from sharepoint into the raw lakehouse. Instead, I would like to extract only its content (the content of each file) in sharepoint and its metadata.
2) Probably this makes sense to be done via a notebook, which would be triggered by a fabric pipeline. As things like copy tasks and gen2 don't offer the same flexibility
3) Maybe, to do this, the notebook, depending on the document type , would call a python library which has the ability to extract both, the content and the metadata information.
4) Can I do incremental loads ? meaning pull only what was not previously extracted documents and documents which have changed since last push? If so, where do I get this information from? from the file or from the sharepoint itself?
5) What should I collect to the raw and bronze in terms of content and metadata? shall I use chunks? and if so, how can I do that ? And which metadata shall I bring ? meaning : When the file was extracted, what is the type of document extracted, its location in sharepoint, its size, etc..
Can someone help please to define the strategy and the structure of the tables in the raw lakehouse?
Thanks a lot,
Pedro
With the files being available in the Lakehouse using the shortcut, you can now easliy access them, without the need to authenticate in a notebook or using other items. The authentication is already done by the shortcut connection.
To extract the data, you can now use a pipeline and a copy data activity using Lakehouse connection to the files using the Excel file format. But, ofcource, when you want to use a notebook, it's now very easy to access the content of an Excel file, using the following code.import pandas as pd lh_path = "/lakehouse/default/Files/MyFolder/data_file.xlsx" df = pd.read_excel(f"file:{lh_path}", sheet_name=0, engine="openpyxl") dispay(df) # sdf = spark.createDataFrame(df) # display(sdf)To get the metadata of files from the shortcut you can use the following code. You don't need the SharePoint file information. The path of the file is already unique. Store this information in a table and you can do an anti-join to get the files from the folder that you do not have in your table yet or where the last_modified has changed.
path = "/lakehouse/default/Files/MyFolder" files = notebookutils.fs.ls(f"file:{path}") excel_metadata = [ { "file_name": f.name, "path": f.path, "size_bytes": f.size, "last_modified": f.modifyTime } for f in files if f.name.lower().endswith(".xlsx") ] excel_metadataHope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
6 Replies
Hi fabricpribeiro,
Have you seen the new feature that you can create a shortcut to a SharePoint folder? This might help you to connect to Excel files on SharePoint in an easy way, without the need to write complicated notebooks the extract the data from the Excel files into the raw layer.
In any situation, you could use the create date and last modified date of the files to see if a file is new or modified. You need to store this file information into your bronze table or add the file information to a logging table. From the table you can check by filename if the file exists or has been modified since the last run.
Saving the complete data of an Excel file into the bronze layer as a snapshot by run_datetime would be my advise. But you could choose to deduplicate data in the Bronze lakehouse, meaning that a specific record from a specific file only exists once.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
Thank you very much nielsvdc for the reply
Unfortunately there are many things not clear for me on it
1) You say I can use a shortcut to sharepoint. That is great, I was not aware of that, so in this case all the files will appear automatically on the file area of my lakehouse. But that is just a part.
2) I want to extract, the content and metadata of each file. For example, so that AI can use later the document content to do things
3) You stated : "In any situation, you could use the create date and last modified date of the files to see if a file is new or modified. You need to store this file information into your bronze table"
Do you mean that I would need to write a notebook, which would loop through the files (via this new funcionality shortcut that you mentioned) and that notebook would extract the content of the file into a table?
Like the strategy below?
The core idea
DOCUMENTS becomes an “extraction runs / versions” log (append-only)One row per document version.
DOCUMENTS TABLE
document_id (stable per file path or SharePoint item id)
document_version (changes when content changes: ETag is best; otherwise hash)
source_last_modified_utc
ingested_at_utc
extraction_run_id
extraction_status, error_message
other metadata (path, size, etc.)
DOCUMENT_TEXT_CHUNKS TABLE is append-only and versioned
Each extraction writes a full set of chunks for that document_id + document_version.
DOCUMENT_TEXT_CHUNKS
document_id
document_version
extraction_run_id
chunk_index (0..N within that version)
page_number (PDF) / sheet_name (Excel)
chunk_text, content_hash
ingested_at_utc
plus path + status fields
Important: you’re not “updating” chunks; you’re writing a new version’s chunks.
How to detect “document changed” (so you only append when needed)
Best → use SharePoint metadata like ETag / Version / Modified time (if you can capture it via shortcut).
If you can’t easily get ETag via shortcut, use a content-based approach:document_version = md5(file_bytes) (most reliable, but reads entire file)
or document_version = md5(file_size + last_modified) (cheaper, but less strict)
?
With the files being available in the Lakehouse using the shortcut, you can now easliy access them, without the need to authenticate in a notebook or using other items. The authentication is already done by the shortcut connection.
To extract the data, you can now use a pipeline and a copy data activity using Lakehouse connection to the files using the Excel file format. But, ofcource, when you want to use a notebook, it's now very easy to access the content of an Excel file, using the following code.import pandas as pd lh_path = "/lakehouse/default/Files/MyFolder/data_file.xlsx" df = pd.read_excel(f"file:{lh_path}", sheet_name=0, engine="openpyxl") dispay(df) # sdf = spark.createDataFrame(df) # display(sdf)To get the metadata of files from the shortcut you can use the following code. You don't need the SharePoint file information. The path of the file is already unique. Store this information in a table and you can do an anti-join to get the files from the folder that you do not have in your table yet or where the last_modified has changed.
path = "/lakehouse/default/Files/MyFolder" files = notebookutils.fs.ls(f"file:{path}") excel_metadata = [ { "file_name": f.name, "path": f.path, "size_bytes": f.size, "last_modified": f.modifyTime } for f in files if f.name.lower().endswith(".xlsx") ] excel_metadataHope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
Hi fabricpribeiro ,
If you have the opportunity to review nielsvdc response, you'll see that it meets your requirements. Please review it and let us know if you need any additional assistance.
Thank you nielsvdc , for your helpful input.
Hi fabricpribeiro ,
May I know if your issue is resolved, or if you still need any additional details. Please let us know if we can help further.
Thanks.
