Creating an ETL pipeline to translate raw data into reliable insights

Doctoral research, Carleton University, 2023. See the code on GitHub

Partway through the survey, the responses stopped looking human. Duplicate answers and identical timestamps were arriving in batches. A flood of spam was coming in through the social media ads I had used to recruit. I had six datasets from two survey modes to merge into one, and fraud had compromised a source.

So I designed a pipeline to pull trustworthy, actionable insight out of that mess. This case study follows the extract, transform, and load steps that turned raw responses into something a design team could confidently build on.

The urgency behind this work goes back to the research topic: Canada's population over 65 will soon be the largest it has ever been. Yet, most remote healthcare technology built for these adults leaves them out of the design process.

For my PhD, I asked what older adults actually need from remote healthcare technology, and what would earn their trust to use it. And findings about trust are only as trustworthy as the data underneath them.

How I set it up

I designed a Canada-wide survey to measure how comfortable people were letting remote healthcare technology collect their data. To reach older adults realistically, I ran it in two modes, online and on paper. Online-only recruitment skews toward the most tech-comfortable people; we could not afford our study to overrepresent this group.

I designed the survey and its goals with my supervisor, Sonia Chiasson, and our partners at the National Research Council's Aging in Place program, Heather Molyneaux and Helene Fournier. They helped sharpen the questions and reach older adults beyond the usual online panels. The Human-Centric Cybersecurity Partnership funded the work.

These two modes of recruitment led to six datasets, two from online panels and four from paper versions. These datasets did not mesh together seamlessly: each had different field names, the same answers coded in different ways, and various other quirks.

Before we could answer our research questions, the data had to become one clean structure we could stand behind.

Validation before cleaning

I built the pipeline in Python in separate stages so that I could trace data from start to finish. I put validation first, ahead of any cleaning. It was critical to check that the data was not compromised; otherwise, it would be incorporated into the analysis in a tidy and invisible way.

I designed the validation stage to check every response against explicit quality rules. It excluded anything that failed, and it wrote a log of every exclusion and why, so that anyone could review.

From there, I scripted processes to transform and load the data. The pipeline systematically integrated all six sources into a unified structure by aligning disparate field names and resolving differences in coding schemes across datasets.

Data normalization is a critical foundation for meaningful statistical comparison and for preventing misinterpretation. So, I programmed the data fields to ensure that only relevant variables were included. I converted raw codes into standardized, readable variable names so that the analysis was transparent and reproducible.

Finally, I split the data for hand-off to the next set of tools: the numeric responses to R and the open-text answers to NVivo.

What the data showed

All key survey takeaways are captured in our published work.

One important finding is that older adults were significantly less likely to say they would use remote healthcare technology. On the 5-point likelihood scale, adults 65 and older averaged 3.3, against 4.1 for adults 35 to 49.

I was sure to calculate effect sizes alongside p-values, so I could keep the differences large enough to act on and set aside the ones too small to matter in practice.

Where it went

The findings were ultimately incorporated into a design toolkit of 15 tools, which healthcare-technology experts reviewed, built to help teams design for what older adults actually need. The pipeline is what made those findings safe to build on.

What I carry forward

This project solidified the value of traceability in my work.

Every decision that informs an insight has to live somewhere a reader can find. This includes the responses I excluded, how I coded a scale, and which statistical differences could be trusted. This traceability allows collaborators, reviewers, or a version of me a year from now to trust the dataset without rebuilding.

Now, traceability is the first thing I reach for when I research and build anything.