DEV Community: Excel Goodies

Fix hot-shuffle anti-patterns to cut Databricks waste

Excel Goodies — Wed, 21 Oct 2026 20:00:03 +0000

Start with the anti-pattern: repeated wide shuffles caused by repartitioning or doing joins followed immediately by groupBy on different keys. That’s the cheap, invisible way a cluster will chew CPU, network and spill to disk for minutes at a time.

A few concrete things that stop the bleeding fast: prefer a broadcast join when one side is small, pre-partition data on the join/group key so work stays local, and avoid unnecessary repartition()/coalesce() calls that reshuffle everything without reason. Also check spark.sql.shuffle.partitions — the default is often far higher than your actual parallelism needs.

How I debug this in practice: open the Spark UI, look at the stages with the biggest Shuffle Read/Write and the highest task spill. If a small number of partitions handle most bytes, you’ve got skew or a hot-shuffle hotspot. If many tasks are short but network reads are huge, it’s a repartitioning problem.

Quick checklist you can run through:

try a broadcast join or lower spark.sql.autoBroadcastJoinThreshold for small tables
set spark.sql.shuffle.partitions to match actual parallelism and enable adaptive execution (spark.sql.adaptive.enabled)
pre-partition on stable keys and avoid redundant repartition()/repartitionByRange()
cache only when it prevents repeated expensive shuffles, and unpersist promptly

If you want hands-on exercises and walkthroughs for cluster sizing, Spark configs and shuffle debugging, the Databricks training covers these topics with real notebooks and examples.

Hybrid storage modes silently slowing dashboards

Excel Goodies — Wed, 21 Oct 2026 18:56:24 +0000

If you mix DirectQuery and Import tables on the same visual, Power BI can end up firing repeated queries to the remote source to resolve filters and relationships — and that’s usually the real cause of a sluggish dashboard. Practical fixes that helped me on a Chennai retail project:

Keep DirectQuery tables out of visuals that combine Import facts; use Dual on small lookup dimensions so filters flow without forcing the fact table remote.
Build a small Import aggregate table for summary visuals (weekly/monthly) and point slicers to Import lookups.
Audit relationships: a chain that crosses storage modes often changes where joins/filters are executed.

These simple patterns make visuals predictable and avoid surprise round-trips. If you want to go deeper on storage modes and query plans, consider the Power BI batch in Chennai (the Power BI batch in Chennai).

CDC into Fabric: preserve order and avoid duplicates

Excel Goodies — Wed, 21 Oct 2026 18:45:53 +0000

Concrete technique: treat CDC as an ordered append-only stream and carry the source transaction identity (LSN/tx_id) plus a per-transaction operation sequence (op_seq) for every row. Make your staging + merge logic explicitly sequence-aware so you only apply a row if its (tx_id, op_seq) is newer than what’s already in the target.

What I do in practice — simple, reliable, and survives retries:

Capture: store every change as an append-only row with pk, tx_id (LSN), op_seq, is_delete, and raw payload. Include a source timestamp but don’t rely on it for ordering.
Stage: ingest into a small staging table and collapse duplicates within the same tx by keeping the max(op_seq) per (pk, tx_id). This removes noise from ingestion retries.
Apply (idempotent MERGE): MERGE into the target using pk; in the WHEN MATCHED branch only update/delete when incoming.tx_id > target.tx_id OR (incoming.tx_id = target.tx_id AND incoming.op_seq > target.op_seq). Insert when no match. Treat deletes as a distinct op applied the same way.

Two practical extras that saved me from painful outages:

Maintain a per-partition processed watermark (highest tx_id applied). It lets you ignore older batches deterministically and speeds incremental runs.
Persist the applied (tx_id, op_seq) on the target row so subsequent merges are cheap comparisons, not full payload diffs.

The mistake I see most often: relying on last_updated timestamps or naïve upserts that don’t check sequence. That works until a retry or out-of-order delivery creates duplicates, resurrected rows, or lost deletes.

If you want to go deeper on sequencing patterns and example MERGE snippets for Fabric, the Fabric Data Engineering training (the Fabric Data Engineering training) covers these workflows.