Data Pipeline & Visualization Platform for a Scientific Publisher

Unifying fragmented data from PostgreSQL databases, flat files, and internal systems into Snowflake via AWS Glue, then building Tableau dashboards that gave editorial, production, and commercial teams a single source of truth for the first time.

Year

2025

Duration

5 months

Client

A Scientific Journal Publisher

Services

Data Engineering

Data Analysis & Visualization

Cloud Architecture

The Challenge

A scientific publisher had data scattered across multiple disconnected systems – article metadata and submission workflows lived in PostgreSQL, production schedules and cost tracking arrived as flat files from third-party vendors, and usage analytics, royalty calculations, and rights information sat in separate internal tools with no shared schema. Teams across editorial, production, and commercial had their own spreadsheets and manual extracts. Nobody trusted anyone else's numbers because everyone was pulling from different sources at different times. When leadership asked a question like 'what's the cost per published article by journal, and how does it correlate with readership?' – it took weeks of manual collation across teams to produce an answer, and even then the numbers rarely agreed. They needed a single, consolidated data platform that could ingest from all these sources, model the data consistently, and surface it through dashboards that teams could actually trust and use day-to-day.

Our Approach

Audited all existing data sources – PostgreSQL databases, vendor flat files (CSV/Excel), and internal system APIs – to map schemas, volumes, refresh cadences, and data quality issues
Designed an ELT pipeline using AWS Glue to extract from each source, land raw data in S3, and load into Snowflake with full lineage tracking
Built a dimensional data model in Snowflake that unified article lifecycle, production costs, usage metrics, and commercial data into a single consistent schema
Created a library of Tableau dashboards tailored to editorial, production, and commercial teams – each team sees the metrics that matter to them, but all pulling from the same underlying data
Implemented incremental loads and data quality checks in the pipeline so teams could trust that dashboards reflected the latest, validated data

The Process

Data Source Audit & Mapping

Catalogued every data source across the organization – PostgreSQL databases for submissions and article metadata, CSV and Excel files from production vendors, and REST APIs from internal rights and royalty systems. Documented schemas, data quality issues, update frequencies, and ownership. This audit surfaced duplicated fields, conflicting definitions, and gaps that had been causing cross-team disagreements for years.

AWS Glue Pipeline Development

Built AWS Glue ETL jobs to extract data from PostgreSQL (via JDBC), S3-landed flat files, and internal system APIs. Raw data lands in an S3 data lake with partitioning by source and ingestion date. Glue jobs handle schema evolution, deduplication, and type normalization before loading into Snowflake staging tables. All jobs are orchestrated on schedule with error alerting.

Snowflake Data Modelling

Designed a dimensional model in Snowflake with fact tables for article submissions, production events, usage metrics, and revenue – joined through conformed dimensions for journals, articles, authors, and time periods. This gave every team a shared vocabulary: an 'article' means the same thing whether you're in editorial or commercial.

Tableau Dashboard Design

Worked directly with editorial, production, and commercial stakeholders to understand their daily questions and reporting needs. Built a suite of Tableau dashboards: editorial gets submission-to-publication cycle times and acceptance rates; production gets vendor cost breakdowns and schedule adherence; commercial gets usage trends, revenue per journal, and readership correlations. All connected live to Snowflake.

Data Quality & Trust

Implemented automated data quality checks at every stage of the pipeline – row count validation, null checks on critical fields, cross-source reconciliation, and freshness monitoring. When a Glue job detects an anomaly, it flags the issue and halts the load rather than pushing bad data into dashboards. This was non-negotiable: the whole point was to build a platform teams could trust.

Rollout & Adoption

Rolled out dashboards team by team, starting with production (highest pain, quickest win) and expanding to editorial and commercial. Ran hands-on sessions with each team to walk through their dashboards, explain the underlying data model, and gather feedback that fed back into iterative improvements. Retired the old spreadsheet-based reporting workflows.

The Solution

We built an end-to-end data platform that finally gave the publisher a single source of truth. AWS Glue pipelines pull data from PostgreSQL databases, vendor flat files landing in S3, and internal system APIs – normalizing and loading everything into Snowflake on a scheduled cadence with full lineage tracking. In Snowflake, a clean dimensional model unifies article lifecycle data, production costs, usage analytics, and commercial metrics through conformed dimensions. On top of this, a suite of Tableau dashboards serves each team's specific needs: editorial tracks submission pipelines and review cycle times, production monitors vendor costs and schedule adherence, and commercial analyses readership trends against revenue. Every dashboard draws from the same Snowflake tables, so the numbers always agree. Automated data quality checks at every pipeline stage mean teams can trust what they see – no more reconciliation spreadsheets, no more weeks-long data requests.

Impact & Results

Data Sources Unified

60%

Faster Reporting

Teams on One Platform

~80%

Less Manual Data Work

PostgreSQL, flat files, and internal system data unified into a single Snowflake warehouse

AWS Glue pipelines with automated scheduling, error handling, and data lineage

Dimensional data model with conformed dimensions – one shared vocabulary across teams

Tailored Tableau dashboards for editorial, production, and commercial teams

Automated data quality checks halt bad data before it reaches dashboards

Eliminated weeks-long manual reporting cycles – answers now available in minutes

Retired spreadsheet-based reporting and cross-team data reconciliation

Explore other Case Studies

Education

AI-Powered Learning Platform for Higher Education

A personalized Socratic coaching platform for college students at scale, built with a multi-LLM architecture and real-time knowledge tracing.

Scalable to 2M+ students, multi-LLM support, real-time mastery tracking

Gaming

AI/ML-Driven Football Insights Platform

A real-time football analytics platform built from scratch on Microsoft Azure, delivering pre-game intelligence and live in-match insights powered by machine learning models processing match events as they happen.

Real-time in-match insights in <5s, 92% prediction accuracy, pre-game reports in minutes

Publishing & Media

Cloud Migration & Application Modernization for a Scientific Publisher

Migrating an acquired on-premise monolith to AWS microservices, re-architecting a data science module to absorb a costly Solr upgrade, and embedding with the operations team to drive adoption of an entirely new way of working.

50% faster DS execution, monolith to microservices, jQuery to React

Book a consultation View all case studies

Technology Stack

We've partnered with organizations across sectors, bringing industry-specific expertise to every engagement.

AWS

Snowflake

Tableau

PostgreSQL

Python