Idea on how to handle client database updates

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.