Why Generic ETL Tools Struggle with Survey Data

Here is a question I have asked myself more than once: if modern data tools can connect to hundreds of systems and process billions of rows, why can a fairly modest survey file still cause so much trouble?

It is not because the tools are bad. Platforms such as Alteryx, Tableau Prep, EasyMorph and Airbyte are very capable at moving and transforming data.

The problem is that survey data is not simply a collection of rows and columns. Its meaning lives partly in the responses and partly in the structure around them. Most general-purpose ETL tools were never designed to understand that second part.

Reading the values is the easy bit

Let us take a simple example. A dataset contains a variable called Q12 and a respondent has the value 2.

What does that tell us?

Not much, unless we also know the question wording and that 2 means “Quite satisfied”. We may also need to know who was eligible to answer Q12, whether the options were rotated and whether the wording changed in the previous wave.

That context is what makes the response useful.

In an SPSS file, some of it can travel with the data as metadata. In a CSV, much of it may disappear. A general-purpose tool can ingest either format, but successfully reading the file is not the same as understanding the survey.

If the metadata is ignored, we are left joining the meaning back in from questionnaires, codebooks, spreadsheets and syntax files. Anyone who has inherited a tracker will know how quickly that becomes messy.

Survey data breaks some ordinary assumptions

Most business datasets are based around fairly clear events. An order has a customer, a date, a product and an amount. The relationships may be complicated, but the grain of the record is usually understandable.

Survey data likes to make life more interesting.

A question might allow one answer or several. It might only appear to a particular audience. It might repeat for every brand, product, employee or medical condition a respondent has mentioned. One analytical measure may depend on a derived universe, a net of several options and a statistical weight.

Some familiar survey structures include:

• Multi-response questions stored across several variables
• Looped question blocks with repeated units
• Routing that creates different bases for different questions
• Derived variables built from several answers
• Nets and scores defined in separate processing files
• Weighting variables that affect every reported result
• Question wording and answer options that change between waves

A generic ETL platform can be programmed to handle these structures. The issue is that the survey meaning has to be designed into the workflow each time.

The hidden problem is duplicated thinking

Imagine two teams processing similar surveys. Both need to convert a multi-response question, calculate a net and prepare weighted data for reporting.

If they work project by project, each team may build its own solution. The calculations might be slightly different. One might retain the metadata and the other might not. Six months later, neither team may remember why those choices were made.

This is where the cost really appears. It is not just the time spent running transformations. It is the repeated effort required to interpret the survey and rebuild the logic around it.

That creates fragile pipelines and makes quality assurance harder. A result can look perfectly reasonable even when the wrong base, weight or grouping has been applied.

Metadata needs to be part of the pipeline

In many traditional workflows, metadata sits alongside the data rather than participating in the processing.

The questionnaire explains what was asked. The codebook explains the codes. A syntax file contains some transformations. An Excel sheet holds the reporting groups. A colleague knows about the exception that was agreed with the client three waves ago.

We should treat that context as structured data in its own right.

Question text, answer options, variable roles, routing information, hierarchies and analytical groupings can be stored in a queryable codebook linked directly to the responses. Transformations can then work with the meaning of the survey instead of relying only on column names.

This makes the pipeline easier to understand and gives downstream tools much richer information to work with.

Reuse starts with a consistent destination

We often talk about building more connectors, but connecting to the source is only half the job.

If every connector produces a different structure, everything downstream remains source-specific. The more important step is to convert each source into a consistent survey data model.

Once that destination is predictable, common transformations can become reusable building blocks. We can define a reliable way to handle a loop, create a respondent attribute or apply a weighting specification, then reuse that logic across projects.

There will always be exceptions. Survey research is too varied for a single rigid template. But an exception should extend a stable foundation, not force us to rebuild the foundation every time.

This is a specialist layer, not a replacement for the modern stack

None of this means general-purpose ETL tools have no place in survey workflows. They remain extremely useful for wider integration, orchestration and transformation.

The missing piece is a survey-specific layer that understands the structure before the data reaches those tools.

Once responses and metadata have been standardised, survey data can sit alongside sales, finance, CRM and product data. It can feed warehouses, analytical APIs and BI platforms without every consumer having to rediscover how the questionnaire worked.

That is the bridge we need to build.

If you have not read the first article, Why Survey Data Is Still an Outlier in the Modern Data Stack explains how we arrived at this problem.

Next, we will look at the consistent destination itself: how dimensional modelling can make survey data easier to query and reuse.