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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors