Tracking DBMS CDC

[demost11]

We're using Databricks to incrementally extract data from SQL Server tables into S3. The data contains a timestamp column. We need a place to store the maximum retrieved timestamp per table so it can retrieved during the next run.

Does Databricks contain (or easily connect to) any key-value stores, or have similar functionality? It could of course be tracked using a Delta Lake table but implementing a "frequent updates by primary key" pattern in a columnar storage system seems like a bad idea.

[Kaniz · Accepted solution]

Hi @demost11, Databricks provides several options for managing state or storing metadata. 

Databricks Secrets:

• Databricks allows you to create and manage secrets, which are key-value pairs storing secret material (such as API keys, passwords, etc.). Each secret scope can hold up to 1000 secrets, and the maximum allowed secret value size is 128 KB.
• You can create a secret scope and store your maximum retrieved timestamp as a secret value. Then, during subsequent runs, retrieve the timestamp from the secret scope.
• Here’s how you can create a secret scope and store a secret key:# Create a secret scope (if not already created) databricks secrets create-scope --scope my_scope # Store the timestamp as a secret databricks secrets put --scope my_scope --key max_timestamp --string-value "2024-03-06T11:44:58"
• Later, you can read the secret value using the same scope and key.

Custom Metadata Table:

• You mentioned that using a Delta Lake table might not be ideal due to frequent updates by the primary key. However, consider creating a separate metadata table specifically for tracking the maximum retrieved timestamp.
• This table can have a simple structure with columns like table_name, max_timestamp, and any other relevant information.
• During each extraction, update this metadata table with the latest timestamp for each table.
• While this approach involves maintaining an additional table, it provides flexibility and ease of querying.

External Key-Value Stores:

• If you prefer an external key-value store, you can use services like AWS SSM Parameter Store, AWS Secrets Manager, or HashiCorp Vault.
• Store the maximum retrieved timestamp as a parameter or secret in one of these services and retrieve it when needed.
• Note that this approach involves additional setup and integration with the chosen service.
•  Each option has its trade-offs, so consider factors like scalability, ease of access, and security w....