empes

Case study · 2025

Scaling enterprise data infrastructure: a 300+ source ingestion framework

A global pharmaceutical company had more than 300 data sources and over 70 TB of data. We built one framework that brings every source in the same way, on AWS, so a new source takes days instead of weeks.

Client
Global pharmaceutical
Status
Shipped
Services
Empes Synthesis, Empes Infrastructure
Scope
Data engineering, cloud infrastructure, automation

Problem

The company’s data lived in more than 300 different systems, among them Veeva, SAP, Salesforce, Oracle and PubMed. Bringing that data in was a manual job, and the manual processes could not scale. Data sat in silos, it did not always agree from one system to the next, and it was growing faster than traditional ETL methods could handle.

What the company needed was one central platform: automated, able to grow, and able to take in, process and serve more than 70 TB of data while keeping strict governance and a clear audit trail.

Approach

We did not start with 300 connectors. We started with a framework. Each source is described in metadata (how to reach it, what to take, how often, where it goes), and the framework does the rest the same way for every source.

The work came in two phases:

  1. Bring the data in. One standard way in for every source, whether it speaks API, SFTP or JDBC, landing in an AWS S3 data lake.
  2. Shape the data. Version-controlled transformations, written in dbt and run in Amazon Redshift, so every change to a number is reviewed and can be traced.
Three hundred pipelines, or one frameworkTwo columns. Before: a separate pipeline for every source. After: one framework that reads one record per source.One pipeline per sourceSource 1: its own pipelineSource 2: its own pipelineAnd so on, 300+ timesEvery fix has to be made in every pipeline.One frameworkOne metadata record per sourceShared loaders: API, SFTP, JDBCOne lake and one way of working for everysourceAdding a source is a new record, not a newproject.frameworkfirstThree hundred pipelines, or one frameworkTwo columns. Before: a separate pipeline for every source. After: one framework that reads one record per source.One pipeline per sourceSource 1: its own pipelineSource 2: its own pipelineAnd so on, 300+ timesEvery fix has to be made in every pipeline.framework firstOne frameworkOne metadata record per sourceShared loaders: API, SFTP, JDBCOne lake and one way of working for every sourceAdding a source is a new record, not a new project.
Read the diagram as text
  1. One pipeline per source: Source 1: its own pipeline, then Source 2: its own pipeline, then And so on, 300+ times. Every fix has to be made in every pipeline.
  2. framework first
  3. One framework: One metadata record per source, then Shared loaders: API, SFTP, JDBC, then One lake and one way of working for every source. Adding a source is a new record, not a new project.

The method is written up in full in the guide Data ingestion at scale: a metadata-driven framework for hundreds of sources.

Implementation

How data moves through the platformSix stages from top to bottom: sources, metadata records, the ingestion framework, the S3 lake in three tiers, dbt models in Redshift, and the analytics tools. Three bands run underneath all of it: orchestration, infrastructure as code, and CI/CD with AI bots.300+ sourcesVeeva, SAP, Salesforce, Oracle, PubMed and others, reached by API, SFTP orJDBCMetadataa description of each source, read by the frameworkIngestion frameworkone engine that onboards every source with minimal manual configurationS3 data lakeraw, then curated, then integrated, so the lineage of the data is cleardbt models in Redshiftmodular, testable SQL transformationsAnalyticsTableau, ThoughtSpot and Google Sheets, reading one source of truthUnderneath every stageArgo Workflows on Kubernetesorchestration: scalable, containerised processingAWS CDKinfrastructure as code: repeatable, version-controlled environmentsGitLab CI/CD with AI botsautomated deployments; bots for merge-request review, releases and incidenthandlingHow data moves through the platformSix stages from top to bottom: sources, metadata records, the ingestion framework, the S3 lake in three tiers, dbt models in Redshift, and the analytics tools. Three bands run underneath all of it: orchestration, infrastructure as code, and CI/CD with AI bots.300+ sourcesVeeva, SAP, Salesforce, Oracle, PubMed and others,reached by API, SFTP or JDBCMetadataa description of each source, read by the frameworkIngestion frameworkone engine that onboards every source with minimalmanual configurationS3 data lakeraw, then curated, then integrated, so the lineage ofthe data is cleardbt models in Redshiftmodular, testable SQL transformationsAnalyticsTableau, ThoughtSpot and Google Sheets, reading onesource of truthUnderneath every stageArgo Workflows on Kubernetesorchestration: scalable, containerised processingAWS CDKinfrastructure as code: repeatable,version-controlled environmentsGitLab CI/CD with AI botsautomated deployments; bots for merge-request review,releases and incident handling
Read from top to bottom. The three bands underneath support every stage.
Read the diagram as text
  1. 300+ sources: Veeva, SAP, Salesforce, Oracle, PubMed and others, reached by API, SFTP or JDBC
  2. Metadata: a description of each source, read by the framework
  3. Ingestion framework: one engine that onboards every source with minimal manual configuration
  4. S3 data lake: raw, then curated, then integrated, so the lineage of the data is clear
  5. dbt models in Redshift: modular, testable SQL transformations
  6. Analytics: Tableau, ThoughtSpot and Google Sheets, reading one source of truth
  7. Underneath every stage: Argo Workflows on Kubernetes: orchestration: scalable, containerised processing
  8. Underneath every stage: AWS CDK: infrastructure as code: repeatable, version-controlled environments
  9. Underneath every stage: GitLab CI/CD with AI bots: automated deployments; bots for merge-request review, releases and incident handling

What we built, and what each part gives the client:

  • A custom ingestion framework. A metadata-driven engine that onboards 300+ sources with minimal manual configuration.
  • A multi-tier data lake on AWS. Raw, curated and integrated layers in S3, so it is clear where the data came from.
  • Orchestration and compute. Argo Workflows and Kubernetes, for containerised processing that scales.
  • A transformation layer. dbt Cloud for modular, testable SQL inside Amazon Redshift.
  • Infrastructure as code. AWS CDK, so infrastructure is repeatable and every change is versioned.
  • CI/CD. GitLab for code and automated deployments, with AI bots for merge-request review, the release process and proactive incident handling.

Outcome

data sources onboarded and managed
300+
of enterprise data, with high availability
70+ TB
the time it takes to onboard a source
Weeks to days
  • Scale. More than 300 different data sources onboarded and managed.
  • Volume. A platform that handles more than 70 TB of enterprise data with high availability.
  • Speed. Onboarding a source went from weeks to days, because of the dedicated framework.
  • Trust. One source of truth for global analytics, used by teams in Tableau, ThoughtSpot and Google Sheets.

What this shows

Scale comes from better abstractions, not bigger servers. A dedicated ingestion framework, rather than individual pipelines, turned a 300-source bottleneck into a repeatable, automated system.

If you have more sources than your team can keep up with, book a call and we will look at whether a framework would pay for itself.

Stack
AWS, CDK, Python, dbt, Redshift, Argo Workflows, Kubernetes, GitLab CI/CD
Type
Strategy + implementation
  • data-engineering
  • aws
  • redshift
  • dbt
  • kubernetes
  • automation
  • enterprise-data
  • ingestion-framework

Book a 30-min scoping call

Bring one process. We will tell you whether automation changes it, and what it would take.

Book a 30-min scoping call hello@empes.it