Pursuant

MEV Built an AWS Data Warehouse and Automated Reporting Pipeline for Pursuant

AWS-Based Reporting Warehouse

Built a scalable data foundation using S3, Glue, Redshift Spectrum, and Tableau.

Automated ETL Pipeline API target

Replaced manual reporting prep with scheduled, repeatable data transformation workflows.

Multi-Tenant Data Architecture

Prepared to onboard additional client data with less custom rework.

MEV Built an AWS Data Warehouse and Automated Reporting Pipeline for Pursuant

Industry

Fundraising and marketing

Nonprofit donor engagement

Data analytics and reporting

Provided Services

Data warehouse development

ETL pipeline automation

AWS cloud engineering

Data modeling

Tech Stack

Python / Apache Spark / Apache Parquet / AWS DMS / AWS Redshift Spectrum / AWS Glue / AWS S3 / AWS EventBridge / AWS Lambda / AWS CloudFormation / Tableau

Project duration

6 months

[ client & product overview/ ]

Pursuant is a fundraising and marketing agency based in Dallas, Texas. The company works with nonprofit organizations to improve donor engagement through analytics, direct response services, and campaign performance insights.

Pursuant’s account managers relied on internal reports to understand campaign effectiveness and guide client decisions. These reports used client data such as donor demographics, donation amounts, donation frequency, and campaign response rates.

The company had already started moving toward a scalable AWS environment, but its existing reporting setup still depended on manual data preparation from an on-premise SQL database. That slowed down analysts and limited the company’s ability to onboard more client data into a consistent reporting framework.

Executive Summary

MEV partnered with Pursuant to modernize its reporting infrastructure by building a scalable data warehouse and automated ETL pipeline on AWS. The goal was to reduce manual data preparation, make reporting more reliable, and create a foundation that could support additional clients without large client-by-client rework.

The project started with a proof of concept delivered within the first month. MEV used the POC to validate that the proposed architecture could scale, work with different data types, and generate reporting-ready insights with less manual effort. After that, the team built an MVP of the data warehouse using a star schema approach and incorporated a multi-tenant architecture to simplify future client onboarding.

The solution connected Microsoft SQL Server, AWS S3, AWS Glue, Redshift Spectrum, and Tableau into one repeatable reporting data flow.

As a result, Pursuant gained:

01:

A scalable reporting foundation

The new warehouse and pipeline made it easier to generate internal reports for additional clients.

02:

Less manual data preparation

Automated data transformation reduced dependence on manual analyst and development team work.

03:

A multi-tenant architecture for future growth

The system was designed to reduce large client-to-client adaptations as Pursuant expanded reporting coverage.

04:

A path toward new client-facing data products

The new foundation positioned Pursuant to explore a self-service portal for client access to data and reports.

Jobs MEV Solved in This Project

Pursuant needed to move reporting workflows away from manual SQL data preparation and toward a repeatable AWS-based foundation that could support more clients, cleaner reporting, and future data products.

01:

Move Microsoft SQL Server reporting data to AWS

Built a repeatable data flow from Microsoft SQL Server to AWS S3 using AWS DMS and Parquet-based storage.

02:

Build a reporting-ready data warehouse

Transformed operational data into star schema models with fact and dimension tables.

03:

Automate ETL pipeline execution

Used AWS Glue, EventBridge, SNS, and Lambda to reduce manual pipeline steps.

04:

Connect warehouse outputs to Tableau

Exposed transformed AWS data to Tableau through Redshift Spectrum.

05:

Prepare the warehouse for multi-tenant reporting

Designed the architecture to support additional Pursuant clients with less custom rework.

Build a Scalable Data Warehouse Foundation

MEV helps companies design data warehouses, automate ETL pipelines, and prepare operational data for reliable reporting and future growth.

Book a Free Consultation

Tap the button

[ how we did it/ ]

Solution & Implementation

01:

Proof of Concept

MEV started with a proof of concept to validate the technical direction before committing to a full implementation. The goal was to prove that the proposed warehouse and pipeline architecture could scale, support different data types, and generate reporting insights with minimal manual effort.

Key steps included:

Architecture validation: MEV tested the core data flow from SQL Server into AWS-based storage and transformation services.
Scalability check: The team validated that the architecture could support future data models and additional client data.
Reporting feasibility: The POC confirmed that transformed warehouse data could be made available for reporting and analytics.
Fast delivery: The POC was completed within the first month, giving Pursuant a working direction early in the engagement.

02:

Data Warehouse and Data Pipeline MVP

After the POC, MEV built the warehouse MVP using a star schema approach and automated the pipeline around AWS services.

Key steps included:

Data export from Microsoft SQL Server: AWS DMS exported full data snapshots from the source MS SQL database into AWS S3. The technical overview notes that DMS was used to export snapshots into flat files and could also support ongoing replication.
Parquet-based data storage: Data snapshots were stored in S3 as compressed Parquet files, keeping the source-layer data close to the original database structure.
Schema creation with AWS Glue Crawler: Glue Crawlers scanned exported files and created schemas in AWS Glue Catalog.
Transformation with AWS Glue Jobs: Glue Jobs transformed relational OLTP data into a data warehouse format based on star schema models. The technical documentation recommended one job per fact or dimension output table.
Conformed data layer in S3: Transformed data was written into a conformed S3 zone, optimized and prepared for use by BI tools.
Reporting access through Redshift Spectrum and Tableau: Redshift Spectrum acted as the query layer between transformed S3 data and Tableau, allowing Tableau to access reporting-ready data.
Multi-tenant architecture: MEV incorporated a multi-tenant structure to reduce large client-to-client adaptations as Pursuant expanded the warehouse to additional clients.

03:

Sandbox Testing with Real Client Data

MEV onboarded data from three Pursuant clients into a sandbox environment to test the solution without affecting live data.

Key steps included:

Controlled validation: Client data was tested in a separate environment to avoid interference with production reporting.
Data model verification: The team validated warehouse structures and transformations before handoff.
Reporting readiness checks: Transformed data could be explored through SQL, with the technical guide referencing Athena as an optional way to verify schema and data.
Risk reduction before rollout: Sandbox testing helped confirm that the approach worked before Pursuant expanded usage.

04:

Pipeline Automation

MEV also designed the pipeline automation logic around AWS EventBridge, SNS, and Lambda. The automation listened to status changes from DMS tasks and Glue Crawlers, then triggered the next pipeline steps through Lambda functions.

Key steps included:

Scheduled pipeline start: EventBridge triggered the Lambda function responsible for starting DMS import tasks. The source documentation describes the initial EventBridge rule running every Tuesday at 7 AM UTC.
DMS-to-crawler handoff: DMS event subscriptions posted task status events to SNS, which passed the event to a Lambda function that triggered the relevant Glue Crawler.
Crawler-to-job handoff: Glue Crawler state change events were captured by EventBridge. When a crawler succeeded, another Lambda function triggered the relevant Glue Jobs for that client.
Automatic continuation after manual runs: Because the pipeline was event-based, manually running a DMS task or crawler could still trigger the following steps automatically.

Event-driven pipeline automation on AWS

Status-change events from DMS tasks and Glue crawlers trigger Lambda functions — each data source advances through the pipeline on its own, with no orchestration server and no global wait.

[ results/ ]

Pursuant gained an AWS-based reporting data warehouse with automated ETL workflows, making data preparation easier and supporting internal reporting and future client onboarding.

01:

On-Time, Under-Budget Delivery

MEV completed the project in six months and slightly under budget. The source attributes this to correct staffing levels and efficient project execution.

02:

Stronger Reporting Framework

Pursuant gained a framework that business and account executives could use to drive insights and support better decision-making.

03:

Scalable Foundation for Additional Clients

The new data warehouse and pipeline created a scalable, automated solution that could support reporting for additional clients more easily.

04:

Future Product Opportunities

The architecture gave Pursuant a foundation for possible future projects, including a self-service portal where clients could access data and reports.

05:

Cleaner Technical Ownership After Handoff

MEV provided documentation so Pursuant’s team could understand, manage, and maintain the new warehouse and pipeline after the engagement.

Tim Thornburg

AVP Database and Solutions Development at Pursuant

The MEV team are technology experts, everyone from the primary developer to the senior engineer and project manager. When we had to make tough technical decisions, they always presented options to my team and explained the implications. I always felt they had our best interests in mind.

The MEV team were excellent communicators, even though English is not their native language. They were very thorough, very complete, and easy to work with. And they did an amazing job of handing off the project to my team.

[ portfolio/ ]

Related Case Studies

Daiichi-Sankyo

pharma

From data salad to a sales solution

Inflammatix

healthcare

HealthCare Diagnostic Platform Achieves 64.8% Savings with AWS Cost Optimization for RDS & S3

Pharmacy Claims Platform

healthcare

Building A Snowflake-Based Pharmacy Claims Platform For Real-Time Drug Coverage Lookup

View All

[ contact us/ ]

No Sales Pitch — We’re Here to Dig Into Your Challenges

Yeah, it’s a boring form. The call though, will make a lot of sense.

The Ball’s In Our Court Now

We’ll get back to you right after reading your message. Thank You!

Oops! Something went wrong while submitting the form.

Or just Schedule a Call with our CEO

Tap the button and schedule a call

Preferences

Privacy is important to us, so you have the option of disabling certain types of storage that may not be necessary for the basic functioning of the website. Blocking categories may impact your experience on the website. More information

Accept all cookies