Secure Data Pipeline for a Global Research Organization

The situation

A global research nonprofit ran field surveys across five independent survey platform instances. Each instance had its own survey designs, encryption settings, and field structures. Researchers needed access to the aggregated data for analysis, but the data contained participant PII that could not simply be dumped into a shared analytical environment. The org had no pipeline to pull from multiple instances consistently, no mechanism to separate sensitive fields from analytical ones, and no transformation layer that research teams could trust and query without risk.

The core tension was practical: make data accessible to analysts while keeping participant data protected. Without a structured separation between those two concerns, the safest option was restricting access entirely, which meant researchers waited days for data extracts prepared manually by the data team.

What we built

CorrDyn built a data engineering pipeline that extracts encrypted survey data from all five platform instances, decrypting fields using instance-specific keys and normalizing the output into a consistent schema. Extraction logic adapts to per-instance configuration so that new surveys do not require manual field mapping each time.

At the transformation layer, we implemented SQLMesh in MotherDuck to govern how data moves from raw extracts to analytical models. PII fields are identified at extraction time based on the survey schema and routed to a restricted store with access controls separate from the main warehouse. The analytical layer receives de-identified data built from governed SQLMesh models, giving researchers a stable, well-defined dataset to query without touching participant records. SQLMesh virtual environments also let the team test transformation changes against production data without affecting live datasets, which matters for a nonprofit where warehouse costs are tied directly to grant budgets.

What changed

Research teams gained direct analytical access to survey data across all five instances without waiting for manual extracts. PII is separated by design rather than by policy, reducing the operational burden of managing access controls across a distributed research staff. The SQLMesh transformation layer gives the data team a governed foundation to extend as new survey programs are added.