Streaming with Medalion Architchture and star schema Help

[g96g]

What are the best practices for implementing non-stop streaming in a Medallion Architecture with a Star Schema?

Use Case:

We have operational data and need to enable near real-time reporting in Power BI, with a maximum latency of 3 minutes. No Delta live tables.

Key Questions:

1. How should we curate dimensions and facts when transitioning data from Silver to Gold using Structured Streaming?

2. Could you provide examples or proven approaches for fact-dimension joins in a streaming context?

3. How can we use CDC in here?

In case of more questions and clarification happy to answer your questions

[Rjdudley]

Why not DLTs?  This is kind of an ideal use case.

[MadhuB]

@g96g 

I've setup a near real-time (30-minute latency) streaming solution that ingests data from SQL Server into Delta Lake.

1. Changes in the source SQL Server tables are captured using Change Data Capture (CDC) and written to CSV files in a data lake.
2. A streaming process then reads these CSV files as they arrive and applies the changes to Delta tables.
3. The delta (changes) is determined by comparing timestamps against a logging table.
4. The entire process, from CDC extraction to streaming updates, is orchestrated using Databricks Workflows.

I recommend DLT as the best solution for your use case, else use the below traditional approach.

1. Change Data Feed
2. Structured Streaming
3. 3-minute micro-batches