Leadership ran on yesterday’s numbers. Now nothing on the dashboard is more than fifteen minutes old.
An enterprise manufacturer had CRM and ERP data it could not act on: reports 12 to 24 hours behind, account identifiers that did not match across the two systems, and reconciliation done by hand every week. We built a medallion pipeline in Airflow that ingests, validates and publishes both systems into one analytics layer, on a schedule nobody has to run.
- Client
- An enterprise manufacturer, multi-division
- Sector
- Manufacturing
- Platforms
- Data Engineering
- Engagement
- Ongoing engagement
- Cadence
- Every 15 minutes, with no manual step in the cycle.
- Study
- 02 of five
One sync window, before and after
Struck through: the way it used to workWhy it stayed that way.
Six constraintsModern enterprises don't have a shortage of data. They have a coordination problem. CRM platforms capture customer relationships. ERP systems manage inventory, orders, and shipments. Finance tracks invoices. Sales sets goals by product and division. But these systems rarely talk to each other — and when they do, it's through brittle, manual exports and spreadsheet-driven reconciliation that breaks the moment volume increases.
- 01
Analytics dashboards reflected data that was 12–24 hours behind operational reality, making it impossible to act on live business conditions.
- 02
Customer records in the CRM used different identifiers than those in the ERP, meaning shipment and order data couldn't be reliably attributed to the correct accounts. Teams spent hours per week manually reconciling discrepancies.
- 03
Raw records from source systems contained inconsistencies — missing fields, incorrect types, unformatted strings, duplicate rows — that propagated silently into downstream reports.
- 04
Sales goals set at the product and division level had no automated connection to shipped quantities. KPI tracking required manual extraction and formula work in spreadsheets.
- 05
The business operated across multiple divisions, each with distinct data semantics, product lines, and reporting requirements.
- 06
Any failure in existing data flows caused complete data loss for that sync window, with no recovery path short of a manual re-pull.
What we ruled out first.
Seven tools evaluated, none of them wholeBefore engaging CloudAlgo, the business evaluated several standard enterprise integration platforms. Each had fundamental limitations that ruled it out. Off-the-shelf tools either handle extraction OR transformation — rarely both with the nuance required for business-specific rules, multi-system account resolution, and division-level data semantics. Stitching together three or four tools creates its own integration burden, operational overhead, and failure surface. CloudAlgo built what the tools couldn't provide: a unified, end-to-end pipeline with business logic embedded at every layer.
DoesExcellent pre-built connectors; zero-config replication
Rules it outPure EL — no transformation logic. Business rules, formula evaluation, and multi-division enrichment are not supported. All logic still lives in spreadsheets.
DoesFast setup; affordable entry point
Rules it outSame limitation as Fivetran. Replicates data as-is. Data quality enforcement and derived field calculation require a separate transformation layer the tool doesn't provide.
DoesSQL-native transformations; version control
Rules it outOnly the "T" in ETL. Still requires a loading mechanism, orchestration, and a separate validation framework. Not a pipeline — a component.
DoesLarge connector library; enterprise support
Rules it outExtremely heavyweight. Licensing costs are prohibitive. Built for API-centric integrations, not high-volume batch data pipelines with complex state management.
DoesNative Azure integration; visual pipeline builder
Rules it outVendor lock-in to Microsoft cloud. Limited support for formula-based field derivation and schema-level validation. Customisation requires significant DevOps overhead.
DoesFeature-rich; handles complex transformations
Rules it outOn-premise orientation; steep learning curve; expensive licensing. Overengineered for this use case and slow to adapt to schema changes.
DoesTight CRM integration; no extra infrastructure
Rules it outNo concept of a data warehouse layer. Cannot transform, validate, or route data to external systems at scale. API rate limits become a bottleneck immediately.
What we built instead.
Four layers, each with a contractCloudAlgo designed and implemented a multi-stage medallion architecture — a proven data engineering pattern where raw data is progressively refined through Bronze, Silver, and Gold layers before reaching analytics consumers. Each layer has a clear contract: what comes in, what transformations are applied, and what comes out. The entire system runs on Apache Airflow with Celery-based distributed execution, deployed to a managed cloud environment with PostgreSQL as the warehouse layer and Redis for real-time coordination between pipeline stages.
- 01
Nothing runs until every table for the division has arrived
API → Redis stateA dedicated orchestration DAG receives table-level payloads via API and uses Redis-backed state coordination to track which tables have arrived for a given sync window. Only when all expected tables for a division are confirmed does the downstream pipeline trigger — eliminating the partial-data problem that caused reporting inconsistencies. Configurable timeout and retry handling ensure no sync window is silently skipped.
- 02
What arrived is kept exactly as it arrived
Staging → BronzeRaw data lands in the Bronze layer with minimal transformation — the goal is a clean, complete, denormalised record of what arrived. Records are processed in configurable batch sizes using executemany semantics so individual row failures don't abort the entire batch. A custom formula evaluation engine handles concatenation, unit conversion (tons ↔ pounds), date part extraction, and duration calculations — all driven by JSON configuration, not hardcoded logic. Business analysts can update derivation rules without touching Python.
- 03
Every record is validated, deduplicated and typed before it moves
Bronze → SilverThe Silver pipeline is where raw data becomes trusted data. Every record passes through Cerberus schema validation (type checking, required field enforcement, value constraints), duplicate detection, column normalisation (uppercase, trimming, type coercion, null handling), and upsert writes. New records are inserted; existing records are updated on conflict, making the pipeline idempotent and safe to re-run. Anything downstream can trust that Silver data is structurally valid, deduplicated, and correctly typed.
- 04
Analytics schemas refresh every fifteen minutes
Silver → GoldThe Gold layer exposes analytics-optimised schemas at 15-minute cadence. A Table Sync DAG maps Silver columns to Gold schema names with idempotent upserts. An Account Relationship DAG solves the hardest cross-system problem — linking CRM account identifiers to ERP records across 4 destination tables using functional indexes on TRIM()+LOWER() columns, cutting query time by 75–90%. A KPI Calculation DAG joins shipped quantity data against annual and prior-year sales goals at product and division level, giving sales leadership a live view of performance vs. plan.
OrchestrationApache Airflow 2.6.1 with CeleryExecutor
Distributed ProcessingCelery 5.3.1 + Redis
Data WarehousePostgreSQL (Staging / Bronze / Silver / Gold schemas)
Schema ValidationCerberus
Formula EvaluationCustom Python engine (Sympy + pandas)
Account MatchingLevenshtein distance + functional index optimisation
DeploymentDocker + Heroku (managed cloud)
NotificationsMailgun (structured HTML email)
MonitoringPapertrail (log aggregation) + Librato (metrics)
LanguagePython 3.x
Engineering notes.
Why it still runs at year twoThe pipeline is configuration, not code
Every pipeline stage — table definitions, column mappings, validation schemas, formula rules, relationship joins — is driven by JSON configuration files. Adding a new table or modifying a transformation does not require code changes. This makes the system maintainable by data engineers who didn't write it and adaptable to schema evolution without pipeline downtime.
A bad row costs a row, not the batch
Batch processing uses executemany with per-row error isolation. A single bad record is logged and skipped — it doesn't abort the batch. Failed rows are counted, reported in email notifications, and surfaced in the Airflow task log for investigation. The pipeline always completes; it never silently swallows failures.
An index took the linkage query from hours to minutes
An early version of the Account Relationship DAG used ILIKE pattern matching for account lookup — readable, but unindexable. As data volumes grew, this stage became a multi-hour bottleneck. CloudAlgo created functional indexes on TRIM(source_account_id) and TRIM(LOWER(division)) columns, then rewrote queries using identical semantics that were now index-scannable. Execution time dropped from 1–2 hours to 10–30 minutes — a 75–90% reduction with no change to output correctness.
Every stage reports in the same line
Every pipeline stage emits structured email notifications in a consistent format: [ENVIRONMENT] [STATUS] PIPELINE — DIVISION — RECORDS_PROCESSED / RECORDS_FAILED. Operations teams see at a glance what ran, whether it succeeded, what division it processed, and how many records were affected — without opening Airflow. Partial failures surface immediately, not after someone notices a dashboard anomaly.
What it settled.
Three figures, still trueEnd-to-end data freshness (was 12–24 hours)
Faster account linkage query (1–2 hrs → 10–30 min)
Manual interventions required per sync cycle
What this one shows
Four things you can hold us to.
A case study is only worth reading if it generalises. These are the parts of this build that would show up again on yours.
- We build to the real requirement, not the template.Off-the-shelf tools failed here not because they're bad tools, but because the problem demanded business logic embedded in the pipeline — formula evaluation, cross-system account resolution, division-aware routing, schema validation with specific rules per table. We designed a system where all of that logic is first-class, not bolted on.
- We engineer for the second year, not just the launch.Config-driven architecture, functional indexes, fault-tolerant batching, standardised observability — none of these are features you need on day one. They're the features that keep a pipeline running reliably at year two when data volumes have doubled and the original engineers have moved on.
- We treat performance as a correctness requirement.A pipeline that takes two hours to run every 15 minutes isn't a pipeline — it's a liability. Performance optimisation isn't a luxury phase; it's part of building something production-worthy.
- We leave teams capable of owning what we build.JSON-driven configuration, documented schemas, standardised notification formats, and clean DAG separation mean the team inheriting this system can understand, extend, and debug it without re-engaging us for every change.