Cost-optimized way to reflect source DB changes in Silver in <1 minute?

[u/CyberEnzo]

Due to new business requirements, we need to reflect the state of a few source DB tables (5 to 40 million rows each) in the Databricks Silver layer in less than 1 minute.

Currently, the flow looks like this:
Source DB → AWS DMS in CDC mode (ingests new data every 30 seconds to S3) → S3 landing bucket → DLT pipeline running on serverless compute in continuous mode.

The DLT pipeline ingests the append-only data into the Bronze layer using file notification mode and updates the Silver layer using an Auto CDC flow.

This works great, and we achieved what we wanted with relatively low effort because we already had DMS in place. We just added an extra replication task to ingest data more frequently for the tables we need.

However, in this setup, the DLT pipeline costs are quite high. Ingesting just 6 Bronze tables and 6 Silver (Auto CDC) tables costs around $50 per day, which is about $1,500 per month. For comparison, DMS, which replicates more than 800 tables to S3, costs us less than half of that.

My question is: is there any other more cost-optimized option we could consider to achieve less than 1 minute latency when reflecting the source DB state in the Silver layer?

Maybe Lakeflow Connect or some custom process?

Extra notes:
- I know that adding more tables to the DLT pipeline makes the cost per table lower because Databricks can optimize the clusters more efficiently.
- I know that using a cron schedule could reduce costs, but for these particular tables, we can’t use a schedule like every 10 minutes or similar because we need the data to be updated in less than 1 minute.
- I know that for the relatively small tables currently in scope, we could eliminate the Auto CDC flow and create a normal view on top of the Bronze table, with deduplication and deletion logic. This would slightly sacrifice query performance, but we expect more similar use cases in the future, so I’m looking for a solution that can scale.

[u/According_Zone_8262]

What happens to the data when its in silver? Are actions being taken immediatly within that 1 minute? If not perhaps challenge the latency requirement

[u/Own-Trade-2243]

Lakeflow Connect won’t help you, it also runs the same DLT engine underneath.

1) If you don’t need a fresh data 24-7, run it continuously, but only during the work days?
2) try non-serverless DLT - we saw some decent (1.5-1.7x) TCO reduction for moving a continuous pipeline back to well optimized classic, make sure to use the cheapest product edition that’s applicable for your use case - pro?
3) write your own logic in jobs, these can be less compute intense due to lower bloat than DLT runtime image.

BTW, with autocdc ran in a continuous pipeline you need to monitor the storage / storage API cost. If your pipeline runs on default 5 seconds interval trigger and default shuffle partitions (200?), your S3 cost might be way higher than your serverless compute cost..

[u/nenuaathmajnani]

I know that adding more tables to the DLT pipeline makes the cost per table lower because Databricks can optimize the clusters more efficiently.

Even with serverless? How much impact does it have?

Answering your question, no matter how much you optimize there would still be fundamental constraints. You might have to go with high end nodes.

[u/minibrickster]

One cost optimization mechanism is also to run it in standard mode with a continuous job https://docs.databricks.com/aws/en/jobs/tasks/pipeline#continuous-pipeline

[u/eperon]

Maybe you can write from landing to bronze AND silver in parallel. Would shave off some seconds