Need help in reviewing a process improvement in databricks.

Hey folks,

I have used AI to refine the context.

I’m looking for some suggestions on designing a reliable Power BI refresh strategy for a metadata-driven data platform.

Context

I have around 10 upstream data sources. Their pipelines typically run between 8 PM and 7 AM the next business day.

The pipelines are orchestrated through an external scheduler, since some of the sources have dependencies on pipelines owned by other teams.

We also maintain an audit table that tracks the status of each pipeline ("In Progress", "Completed", "Failed") along with timestamps.

The goal is to ensure that Power BI provides a consistent view throughout business hours, even if some upstream sources are delayed.

Proposed approach

I’m thinking of having three Power BI refresh waves:

7:30 AM — First refresh (SLA is 9 AM)

- If all data sources have completed successfully → refresh Power BI with the latest data.

- If some sources are still pending → refresh Power BI using the previous business day's data.

8:30 AM — Second refresh

- If the pending data is now available → refresh Power BI with the new data.

- Our pipelines already handle versioning and will mark the newly arrived records for the current business day as active.

- If the data is still unavailable → do nothing.

5:30 PM — Final refresh

Once the business day is effectively over:

- If the missing data is now available → refresh Power BI.

- If it is still unavailable → leave it as-is and wait for the next cycle.

The challenge

My initial thought is to use the existing audit table as the control mechanism:

1. Check the completion status of all required upstream pipelines.

2. Identify which sources are missing.

3. Trigger/backfill only what is required.

4. Refresh Power BI once the required data is available.

The challenge is the very small execution window.

I essentially have around ~ 2 hours between the 7:00 AM and 8:30 AM waves, and although the actual data insertion/backfill is relatively quick, a significant portion of the time can be consumed by the external scheduler, Databricks handshake, cluster startup, etc.

What would you recommend?

Would you approach this differently?

Would love to hear how others have solved a similar data availability → fallback → late-arriving data → Power BI refresh problem.

Thanks!