DWH Orchestration — Case Study · Golubovsky Data Engineering

The problem

Ten pipelines, no single picture

Each reporting pipeline ran on its own schedule, with no guarantee the data it depended on had actually landed first. If one report was late or silently failed, everything built on top of it kept running anyway — quietly working with stale or broken data.

Every pipeline ran independently, with no guarantee upstream data had actually landed first.
A failed pipeline didn't stop the ones depending on it — stale or broken data could cascade downstream unnoticed.
Ten-plus separate jobs meant ten-plus separate alert streams — real failures got lost in the noise.
No single place to see whether last night's full refresh actually succeeded.

What I built

One governed run instead of ten independent jobs

I replaced a set of independently scheduled pipelines with a single master orchestration that runs the whole nightly warehouse refresh as one chain. It starts with a CRM-style sync — automatically choosing between a lighter daily sync and a deeper periodic reload — then works through the rest of the pipelines in a controlled, one-at-a-time order.

Each step only starts once the previous one has actually finished, and a failure stops the chain right there instead of letting bad data cascade forward. One start notification and one end-of-run summary replace what used to be separate, noisy alerts per pipeline.

Run startsAutomated overnight trigger — no manual kickoff

Start notificationAnnounces that the night's refresh has begun and which sync mode was chosen — a light daily sync, or a deeper periodic reload.

1CRM Sync Contacts, deals & leads refreshed first

CRM SyncPulls fresh CRM data. Runs first because every downstream pipeline reads from it.

Most nights

Daily syncLighter, incremental pull of what changed

Periodically

Deep reloadA fuller catch-up — a safety net

chosen automatically — no manual trigger

2Currency Rate Sync Refreshes exchange-rate data

Currency Rate SyncKeeps pricing- and discount-related pipelines working off today's rate rather than yesterday's.

3Lead Work Density Scores manager & team effort

Lead Work DensityOne of the analytics pipelines featured elsewhere on this site — runs here once source leads are synced.

4Sales Funnel Rebuilds the funnel by stage & channel

Sales FunnelReads the same lead & deal data the sync step just refreshed.

5RFM — Vehicle Service Recency-Frequency-Monetary tiers

RFM — Vehicle ServiceShares the same compute cluster as the pipelines around it, so it's scheduled here rather than run in parallel.

6RFM — Vehicle Sales Same engine, sales customers

RFM — Vehicle SalesKept right after its service-side counterpart for a consistent nightly cadence.

7Vehicle Registration Matching Matches registrations to the client base

Vehicle Registration MatchingSurfaces missed sales & service opportunities from public registry data.

8Registration History Enrichment Adds historical registration context

Registration History EnrichmentBuilds on the matching step just before it.

9Lead Registration Audit Flags late or mismatched leads

Lead Registration AuditA data-quality / anti-fraud pass — runs late enough to see a full night's activity.

10Discount Eligibility Recalculates discount eligibility

Discount EligibilityWants the freshest currency rate and customer tiers computed earlier in the run.

11Financial Reporting Extracts Final extracts for finance

Financial Reporting ExtractsRuns last, once every upstream table for the night is trustworthy.

Run summaryOne consolidated message — not eleven separate ones

Run summaryLists every pipeline with a pass/fail mark and how long the whole run took.

A schematic view of one nightly run — hover any step for details. Pipeline names are representative; the production chain also covers additional dealer- and brand-specific jobs not shown here.

The result

One summary, not eleven alerts

One message lists the status of each pipeline and shows where the run stopped if a step failed.

Nightly Run Summary overnight · automated

CRM Sync✓ completed

Currency Rate Sync✓ completed

Lead Work Density✓ completed

Sales Funnel✓ completed

RFM — Vehicle Service✓ completed

RFM — Vehicle Sales✓ completed

Vehicle Registration Matching✓ completed

Registration History Enrichment✗ failed — chain stopped

Lead Registration Auditskipped

Discount Eligibilityskipped

Financial Reporting Extractsskipped

Total run time ‹NN› min (add real number)

An example run illustrating the stop-the-line behavior — on a healthy night, every row shows completed.

Built to be fast & affordable

Managing the nightly workload

Lighter daily sync, heavier periodic reload — pulls only what changed most nights, with a fuller catch-up on a set cadence, instead of a full reload every single night.
Strictly one pipeline at a time on a shared compute cluster — avoids the resource contention that silently stalls or crashes jobs when several heavy Spark jobs compete for the same resources.
One orchestration to operate — a single master run replaces ten-plus independent schedules, so there's one place to monitor, not ten.
Runs unattended overnight — reports are ready before the business day starts, with no manual kickoff needed.

Built to be trusted

What happens when a step fails

Fails safely, reports clearly

Stop-the-line on failure — if one pipeline breaks, everything that depends on it holds rather than running on stale or broken data.
No overlapping runs — the next night's refresh only starts once the previous one is fully finished, avoiding race conditions.
One clear summary — every pipeline's pass/fail status and total run time, in a single place, instead of noisy per-pipeline chatter.
Automatic fallback logic — a lighter daily sync most nights, a deeper reload on a set cadence, with nobody needing to remember to trigger it.

The outcome

A shared schedule and run history

Before

Ten independently scheduled reports
A stalled pipeline could feed stale data downstream
Ten-plus separate alert streams, real failures buried
No single view of whether the refresh worked

After

One governed nightly run, in a safe order
Failures stop the chain before bad data spreads
One consolidated summary, not a flood of alerts
A clear, at-a-glance record of every pipeline's status

Quantified results (hours saved per week, faster time-to-report) — add real numbers.

Stack & role

How it's built

Sole data engineer — orchestration design, branch logic, dependency ordering and monitoring.

Apache AirflowPySparkPostgreSQLSpark SQLAutomated run alerts

Running ten reports on ten different schedules?

We can review your pipeline dependencies and set up a shared schedule with failure handling and a run summary.

Discuss your project →

One schedule for the nightly warehouse update