ETL pipeline optimization on Databricks

Problem

Critical ETL pipelines had excessive runtimes — up to nearly 17 minutes per run —, inflating DBU consumption, busting processing windows and preventing data delivery SLAs from being met.

Architecture

flowchart TB
  subgraph Before
    A1[Full table scan] --> A2[Joins with shuffle]
    A2 --> A3[Accumulated small files]
    A3 --> A4[Runtime up to 17 minutes]
  end
  subgraph After
    B1[Incremental reads] --> B2[BroadcastJoin]
    B2 --> B3[CLUSTER BY + OPTIMIZE]
    B3 --> B4[Runtime under 1 to 4 minutes]
  end
  A4 -.optimization.-> B1
  classDef bad fill:#1a1018,stroke:#ef4444,color:#e2e8f0
  classDef good fill:#0d1525,stroke:#10b981,color:#e2e8f0
  class A1,A2,A3,A4 bad
  class B1,B2,B3,B4 good

Solution

• BroadcastJoin: broadcasting small tables eliminated unnecessary shuffles in joins.
• CLUSTER BY: physical data reorganization to maximize data skipping on future reads.
• OPTIMIZE: small file compaction that was degrading read and write performance.
• Incremental reads: replaced full scans with reading only new rows, reducing the volume processed per run.

Results

• fato_notafiscal: 20 min → 4 min (-80%)
• fato_ordemvenda: 5m 46s → under 1 min (-83%)
• fato_remessa: 9m 31s → under 1 min (-90%)
• notafiscalremessa: 4m 55s → under 1 min (-80%)
• fato_financeiroareceber: 10m 2s → under 1 min (-90%)
• fato_partidascontabeis: 16m 59s → under 1 min (-94%)

Stack