Idea on how to handle client database updates

[diogodasilva]

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

[Adamboer]

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.