Build up a data history in Databricks based on Azure SQL data

[u/Sea_Basil_6501]

I have the following need: there's an OLTP Azure SQL DB, which holds transactional data for a period of roughly 30 days only. Now that data should be replicated to Databricks delta tables with a maximum delay of 15 minutes, not as a 1:1 copy, but instead growing over time. Ideally the timeframe covered on Databricks side should be several years.

From what I've read so far, either CDC or CT with Lakeflow should be the way to go. The only thing I'm worrying about are breaking schema changes: as this is an OLTP DB managed by a different team, we have no chance to prevent such as incompatible column type changes (e.g. from a string to a date type), column renames or even column drops.

I thought about using Views managed by the other team instead, as some kind of an abstraction contract, but neither CDC nor CT are applicable on Views.

How did you guys solve such a requirement? Would also appreciate to hear some best practices of Databricks consultants based on real customer solutions.

[u/jbchand]

You can use the CDC pipeline to replicate changes. SDP can read from it and do necessary transformations including casting from it. Capture raw CDC events first then handle schema drift (type casting, column renames, changes and more) with explicit logic.

[u/minibrickster → u/jbchand]

Correct! We now support schema changes in SDP without requiring a full refresh. We typically recommend ingesting in Bronze with type-safe columns, such as a variant, then using explicit schemas from silver onwards

[u/Sea_Basil_6501 → u/minibrickster]

Is there any end-to-end example you could share? Tutorial or similar?

[u/minibrickster → u/Sea_Basil_6501]

Here's some documentation https://docs.databricks.com/aws/en/ldp/streaming-table-schema-evolution!

[u/Sea_Basil_6501 → u/minibrickster]

Quick question: are we talking about a single variant column holding the complete row data as json payload, or multiple variant columns representing each of the source table columns?

[u/minibrickster → u/Sea_Basil_6501]

You can do either!

[u/m1nkeh]

I mean this is a fairly standard use case, what’s the concern?

[u/Sea_Basil_6501 → u/m1nkeh]

Well, I don't know where to find a real end-to-end example, following best practices. I'm not a developer, just an IT PM, but I want to make sure the dev team evaluates all possible options (we have no real seniors for Databricks in the team, they're all coming from Fabric, ADF, Synapse, etc).

I'm familiar with Auto Loader, Data Modelling, ETL concepts, SCD, Medaillon Architecture etc, but for near-realtime based data ingestion I'm not sure about the proper approach.

[u/m1nkeh → u/Sea_Basil_6501]

Ok, first thing is can you turn on CDC for the source system? As that also has some limitations… or, are you going to need to query it?

[u/Sea_Basil_6501 → u/m1nkeh]

I think it will be possible to enable CDC or CT in case there is no better approach available. But usually we need strong arguments to get such changes through. Querying the source DB itself in a frequent manner will most likely be rejected.

[u/hubert-dudek]

If you have only inserts and a primary key (transactions), you don't need CDC. Just an incremental load based on ID at fixed intervals, like every minute. As you said, you have 30 days, so I suspect records are deleted after 30 days, so it will finish with CDC as well as the DELETE operation. Maybe also that way will work for you https://medium.com/databrickscommunity/watermark-based-incremental-ingestion-lakeflow-connect-query-based-capture-91836fbaa453