Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
diogodasilva
Frequent Visitor

Idea on how to handle client database updates

GM friends.

 

I need some help on how to properly handle a client database and its updates given the current scenario:

 

Client database is extracted weekly from CRM expoerted to XLS file. 

Each weekly file will have: New Clients, Current Clients with information updates if any, but any clients who were churn are simply deleted from the database.

 

My challenge is to setup an ingestion routine for this files which will always keep adding and updating but never deleting churn clients (so I don't lose their history as there are other relationed tables with data from them) leaving me with:

 

- Keep old clients even if they are removed from weekly files

- Update current clients information if they have changed (address, phone, email for example)

- Add new clients

 

thanks in advance

 

1 REPLY 1
Adamboer
Responsive Resident
Responsive Resident

Based on your description, it seems like you are looking for a solution to handle updates to a client database extracted weekly from a CRM, where churned clients are deleted from the weekly files. You want to keep the history of churned clients and avoid deleting them from the database.

To accomplish this, you could consider implementing a data integration solution that can perform incremental updates on the client database. Here are some steps you could take:

  1. Create a separate table to store the weekly files as they are extracted from the CRM. This table will be used as the source for your data integration routine.

  2. Set up an ETL (Extract, Transform, Load) process to ingest the weekly files into your target client database. Use a merge statement to update existing records and insert new ones. You can use the primary key (such as client ID) to match existing records.

  3. To keep the churned clients' history, create a separate table to store the deleted clients. Before running the ETL process, perform a left outer join between the extracted weekly file and the target client database. The result will be a table with all the churned clients. Insert the churned clients into the deleted clients table, and exclude them from the merge statement in step 2.

  4. When you need to retrieve the client data, create a view that combines the target client database and the deleted clients table using a union statement. This view will include all clients, including churned clients.

By following these steps, you can ensure that your client database is always up to date with the latest information and that the history of churned clients is preserved.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors