Move and transform Dynamics CRM data from bronze to silver

[eyeballkid]

Hi,

I hope this is the an appropriate place to post this question as I'm new to Fabric. I am exploring how to create a Lakehouse using the medallion architecture so we can report on our Dynamics365 CRM (and other) data.  So far I have created a Bronze lakehouse and created a shortcut to some CRM data that has been extracted to ADLS Gen2 storage using Synapse link so that it appears in the files folder of the lakehouse:

The next step that I would like to achieve is to create a Delta table in the Silver lakehouse that contains the business relevant fields and leaves behind the superfluous system fields etc. that can be used for querying and ultimately onwards to the Gold Lakehouse.

It looks like the csv files don't have any header information so what would be the best way to create a Delta table that has column headers in the Silver Lakehouse?

Many Thanks!

[SudhavaniKolla3 · Accepted solution]

Hi, so we have 2 options todo this.

1 way is creating one lake link/Synapse link in Dataverse to bring D365 CRM data to Fabric raw lakehouse(via shortcut, which means no physical movent of data) in the form of tables with all headters, so that you can apply next transfomations on top of data and push to silver(no need to combine headers with data, since all your data will come with headers as table).

2 way is after comming data to Fabrci raw lakwhouse via ADLS gen2 as CSV files, which has no header(your current approach), you need to use notebook(pyspark code) to combine that header(json format) and data file(CSV format) by writing some pyspak code, so that it will form as dataframe with headers and push that dataframe data to silver layer as table.

by following any of these 2 ways, you can able to achieve the required output.

please let me know if you have any queries.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-nuoc-msft]

Hi @eyeballkid 

Thank you very much Stinkys for your prompt reply.

I think this tutorial might be helpful for you:

Organize a Fabric lakehouse using medallion architecture design - Training | Microsoft Learn

In this exercise, you can learn how to transform data and load it into a silver Delta table

Query and report on data in your Fabric lakehouse - Training | Microsoft Learn

If your question involves Synapse, you can also ask it in the relevant forum. There will be someone there to help you with more professional help:

Synapse forums - Microsoft Fabric Community

Hope to help you!

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stinkys]

The headers need to come from somewhere so I would go back to your source. Either rework how you are generating your CSV's or use another method to get the data, such as on-premise gateway for direct SQL connection or using a cloud connector to the database. Then make sure you have everything you need in bronze before stepping to silver.