Multi-Brand Automotive Group: 426.8M Rows from Four Dealer Systems into Amazon Redshift

One Trusted View Across a Multi-Brand Dealer Network

Our enterprise customer runs a multi-brand automotive dealership business across Saudi Arabia, covering new-vehicle sales and aftersales for several international brands. All of its operational data lives in the Autoline dealer-management platform, split across four servers. Together, they hold 426.8 million rows across 10,581 tables and grow by about 3 GB every day.

The Challenge:

Analytics and reporting ran on legacy Oracle data warehouses spread across several instances. This created three connected problems:

• No single view of the business. Each server held its own brands, branches, and divisions, and the same logical table existed as many physical copies. Group-wide sales, stock, or aftersales reports needed manual merging of inconsistent extracts.

• Data that could not be fully trusted. The Oracle warehouses had drifted from Autoline, the true source of record. At first reconciliation, the average source-to-target row-count difference was 8.14%.

• A platform that could not scale. The Oracle estate limited query performance as data grew by about 1.1 TB a year. Pushing heavier reports onto Autoline itself risked slowing live dealership transactions.

An AWS-Native Analytics Platform Built Around a Hard Constraint

mumba replaced the legacy Oracle warehouses with an AWS-native analytics platform. Instead of migrating the drifted warehouses as they were, the team pointed extraction directly at Autoline, the operational source of truth.

One constraint decided the architecture: Autoline is reachable only through a proprietary ODBC driver that runs on Windows. This ruled out AWS Glue, AWS DMS, Amazon MWAA, AWS Lambda, and Amazon EMR, which all run on Linux or lack a native connector.

The platform has five layers:

• Source: four on-premises Autoline servers, reached over VPN and never exposed to the public internet.

• Ingestion: a memory-optimized Windows Amazon EC2 host that runs four server-specific extraction agents and Apache Airflow for orchestration.

• Raw storage: a versioned Amazon S3 landing layer with partitioned, compressed Parquet files, so a failed warehouse load never forces a new query on the live system.

• Warehouse: Amazon Redshift RA3 nodes with a staging schema and a production schema. Managed storage grows independently of compute, so data growth needs no node change.

• Analytics: Amazon QuickSight dashboards connected directly to Redshift, with serverless scaling and no BI server to manage.

AWS Secrets Manager, AWS IAM, and Amazon CloudWatch support every layer, so no credentials live in code and every host and service is monitored.

Configuration-Driven, Wave-Based Delivery

Building a separate pipeline for each of 10,581 physical tables was not maintainable. Instead, one configuration table in Redshift defines each source table, target table, load type, and date column. Shared Airflow DAG builders read it at run time and generate a four-step chain for each active configuration: export, prepare, load, and publish.

• Wave-based migration: the largest and most complex server went first to prove the pattern. The other three servers followed with configuration changes only, on the same host and cluster.

• Separate sign-off per server: each server had its own checklist, agreed with business owners up front, so issues on one server did not delay the others.

• Duplicate-free incremental loads: key-aware MERGE in Redshift makes change data capture idempotent, so overlap windows and task retries never create duplicate rows.

• No partial data for users: staging data is published to production in a single transaction, so QuickSight users never see a half-finished load.

• Daily reconciliation: an audit DAG runs every morning after the nightly loads. It compares source and target row counts for every active configuration and checks for duplicate business keys.

• Safe overnight window: loads run between 22:00 and 08:00 local time, with bounded extraction that keeps analytical load off the live dealership system.

Key Takeaways from the Oracle-to-Redshift Migration

• Do not trust the legacy warehouse by default. The Oracle warehouses had drifted 8.14% from Autoline. Migrating them as they were would have moved the errors into Redshift. The Assess phase now includes a source-versus-legacy reconciliation in week one.

• Prove connectivity before you compare services. The Windows-only ODBC driver ruled out the usual AWS ingestion services. Discovery now starts with a connectivity test that confirms the driver, operating system, and network path.

• Plan for missing and inconsistent tables. Some expected branch and division tables did not exist, and naming varied by server. Controlled-skip logic now logs and skips a missing table while the rest of the run continues.

• Memory, not CPU, is the first limit. As volumes passed 426 million rows, CloudWatch showed memory pressure on the ingestion host. The team capped concurrency, staggered schedules, and added a one-step vertical scaling path to the runbook.

• CDC alone is not enough. Some source records changed without updating their date column. Repair utilities and the daily reconciliation audit now catch and fix these gaps, and every CDC design includes a backfill plan from the start.

• Agree on operational ownership early. Escalation contacts, deployment accounts, and backup ownership are now part of each wave's sign-off checklist, not a handover task.

The customer moved from fragmented Oracle warehouses and IT-produced extracts to one governed Amazon Redshift warehouse with self-service QuickSight dashboards across brands, branches, and divisions. Autoline is once again the single source of truth, and reports now match it to within 1%.

Because the platform is configuration-driven, each new brand or server adds configuration rather than infrastructure. The customer can grow its dealer network while keeping cost and effort low and analytical load off its live dealership system.