Databricks Community

Navinkumar_K · yesterday

Hi everyone,

I'd appreciate some advice from anyone who has worked on a similar setup.

Current setup:
- One Databricks Job with 2 serverless Lakeflow pipelines and around 60 Gold outputs
- Pipeline 1 reads Curated tables and builds Materialized Views
- Pipeline 2 reads from both Pipeline 1's MVs and Curated tables
- Runtime is about 40 minutes and cost is about $20–25 per run

Proposed change:
- Remove the MVs and write physical Delta tables using overwrite, reading Curated directly
- Keep the dependencies defined as it is in existing Pipeline 1 & Pipeline 2
- Stay on serverless

What I've checked so far:
- The event log (planning_information) shows COMPLETE_RECOMPUTE for our MVs, so they aren't refreshing incrementally today
- Serverless doesn't support cache()/persist(), so shared logic can't be reused in memory if each Gold table reads Curated directly
- The pipeline currently handles dependencies and runs independent flows in parallel automatically

In a different project, a similar MV-to-Delta change ended up increasing both runtime and cost, so I want to be careful here.

My questions:
1. Since our MVs already do a full recompute, is there any real performance or cost benefit in moving to Delta overwrite?
2. Has anyone seen runtime or cost go up after a change like this? If so, what was the main cause?
3. Would you recommend keeping the pipeline and instead looking at incremental refresh (row tracking, change data feed on sources) or Standard performance mode?

Any experience or pointers would be really helpful. Thanks in advance!

anuj_lathi · yesterday

Short answer: if your MVs are already doing a full recompute, swapping them for overwrite Delta tables won't give you a real win, and it's likely to cost you more. I'd keep the pipelines, find out why the MVs aren't going incremental, and benchmark before rewriting 60 outputs.

1. Is there a benefit to Delta overwrite?

A full MV recompute and a CREATE OR REPLACE-style overwrite do essentially the same work: read the sources, run the query, and write the full result. You'd be giving up what the pipeline gives you for free and getting no new compute savings in return.

Shared intermediate results. Pipeline 2 reads Pipeline 1's MVs, so that logic is computed once and read many times. If each Gold table reads Curated directly, every one of them re-executes the shared joins, aggregations and dedups. You're right that serverless has no cache()/persist(), so there's nothing in memory to compensate. The only way to share work on serverless is to materialize it, and that's what your Pipeline 1 MVs already do.
Dependency and parallelism handling. The pipeline builds the DAG from table references and runs independent flows concurrently [1]. With plain overwrites you rebuild that yourself as job tasks or notebook orchestration, usually with less parallelism and extra per-task startup and scheduling overhead.
Write amplification. Overwriting rewrites every file on every run. An MV that is eligible for incremental refresh can avoid that, and you'd lose the option.

2. Why cost and runtime usually go up after this change

From what I've seen, it's almost always one of these:

Duplicated upstream logic. The shared MV layer disappears and every downstream table recomputes it. This is the most common cause.
Lost concurrency. The orchestration that replaces the pipeline DAG ends up more serial, so wall-clock time rises and you pay for the serverless compute while it waits.
Per-task overhead. Many small tasks each pay startup, planning and commit costs that the pipeline amortized.
Full rewrites of large outputs that would otherwise have been incremental.

3. What I'd do instead

a) Find out why the MVs recompute. You already have planning_information in the event log. Look at the reason it gives for each MV, because it tells you what blocks incremental refresh. Typical culprits are:

Source tables without row tracking or deletion vectors enabled.
Query constructs or non-deterministic expressions that the incremental planner can't handle.
A cost model decision that a full recompute is cheaper, which is common when most of the source changes every run.

Row tracking and deletion vectors on the Curated tables are the first things I'd check:

ALTER TABLE catalog.schema.curated_table SET TBLPROPERTIES (
  delta.enableRowTracking = true,
  delta.enableDeletionVectors = true
);

I wouldn't spend time on change data feed for this. Incremental MV refresh doesn't hinge on CDF on the sources. Row tracking is the property to focus on.

Also be realistic about the workload. If your Curated tables are fully rewritten each load, or most rows change daily, a full recompute may be the right plan and incremental won't help.

b) Find where the 40 minutes goes. With ~60 outputs it's rarely evenly spread. Usually a handful of flows dominate. Use the event log and the query profile to find the top 5–10 and optimize those: join strategy, skew, over-wide scans, file layout on Curated. That's far higher-leverage than changing the write mechanism for all 60.

c) Try Standard performance mode. If the pipeline is triggered and a few minutes of extra startup latency is acceptable for your SLA, Standard mode is the cheapest lever to test, since it trades startup time for lower cost. Performance-optimized is for when latency matters. For a 40-minute batch job, the extra startup is likely noise.

d) Benchmark with real numbers. Don't compare on gut feel. Run both variants on a representative workload and compare DBU usage from system.billing.usage. Note that billing data can lag by up to 24 hours [2].

SELECT usage_date, sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_metadata.dlt_pipeline_id IN ('<pipeline_1_id>', '<pipeline_2_id>')
GROUP BY usage_date, sku_name
ORDER BY usage_date;

Recommendation

Stay on the pipelines. Fix the incremental eligibility on the MVs where the data shape allows it, tune the few most expensive flows, and test Standard mode. Only move a specific table to a physical Delta table if you've shown that table is cheaper that way, for example when it has a unique write pattern like a MERGE. A wholesale MV-to-overwrite migration is the pattern most likely to repeat what you saw in your other project.