Founded more than 200 years ago, Hamilton College is one of the nation’s oldest and most highly regarded liberal arts colleges, located in Clinton, New York. With an enrollment of slightly fewer than 2,000 students, it is distinguished by a rigorous open curriculum; a need-blind admission policy that ensures access to talented students with limited financial means; a deeply committed faculty who welcome close collaboration with students; and a focus on preparing students for lives of meaning, purpose and active citizenship.

Discovering Tableau

Hamilton knew it needed to redesign the way it managed and interacted with its giving data. The College selected Tableau Software to assist with this endeavor. Tableau immediately involved InterWorks to initiate the project. InterWorks’ Northeast Practice Lead Shoaib Khan was the first to contact the College, helping them determine an initial project to become familiar with Tableau. The connection between InterWorks and the College was strengthened when InterWorks Enablement Practice Lead and Hamilton alumna Carly Capitula joined the project. Capitula led with ad hoc Tableau training to help the College’s dashboard designer build visually engaging reports. The bulk of the project, however, would focus more on the architecture of the College’s data practice.

All About JSON

Hamilton wanted InterWorks to look first at a small slice of data as a testing ground. In the simplest terms, the College wanted to take JSON files generated by Colleague (its enterprise resource platform), store them in a central repository and have them readily available for analysis in Tableau. These files contained data on approximately 10 areas of interest, all related to giving. This data was packaged as an extract and then brought into a dashboard to help the College determine where financial gifts came from as well as whether target goals were met.

What started out as light scripting work eventually turned into a larger conversation around a more permanent solution. The Hamilton team, along with InterWorks Data Architect Alex Suarez and Data Engineer Holt Calder, realized that rather than writing custom ETL scripts for the JSON files that would need to be continually maintained, a more permanent and convenient solution existed.

Establishing the Right Data Architecture

The first part of this solution involved Matillion, an ETL tool that operates in the cloud. InterWorks helped Hamilton set up an AWS EC2 instance to host both Matillion and Tableau Server to give Hamilton’s data architecture a reliable, high-performing backbone. After setting up a few jobs in Matillion, the College and InterWorks were able to bring in flat JSON files from Amazon S3 and store them in Snowflake as plain variants. Because Snowflake handles JSON files well natively, not much pre-processing was needed to establish this new data pipeline. Data can stay in JSON format when an extract is generated for export to Tableau.

A diagram of the College’s current data infrastructure

For Hamilton, the biggest benefit of its new AWS-Matillion-Snowflake pipeline was the time and money savings provided compared to custom scripting. It saved the College and the InterWorks team countless development hours. Snowflake and Matillion also run far more efficiently than traditional data warehousing and ETL setups. Matillion only needs to be switched on for one hour a day versus the 24 hours it takes other tools. Some clever lambda functions, combined with Cloud Watch in AWS, made Matillion management simple, resulting in significant time savings. Given that Snowflake is a data warehouse that is also based in the cloud, the College can start with a small project and expand as its data efforts grow. That type of flexibility doesn’t exist with other data warehousing solutions.

What’s Next?

With its data pipeline firmly established, Hamilton can now focus on other areas of interest like data validation and governance. This is a huge step forward. As cleaner data comes in and is delivered to more relevant stakeholders via Tableau, the College’s ability to make more informed decisions surrounding its gifts will only increase. In turn, the College will be able to more effectively raise the funds necessary to ensure that it continues to fulfill the promises it makes to its students to study what they love, be who they are and find their future at an institution that is need-blind and meets their full financial needs.