Techniques, Software, and Tools

Author
Affiliations

Julien A. Raemy

docuteam SA

University of Bern

Published

January 19, 2025

Modified

November 9, 2026

The Data Pipeline

Working with open data rarely means downloading a file and being done. School of Data, the data literacy network started by the Open Knowledge Foundation, describes this work from beginning to end as the Data Pipeline, a sequence of seven steps (School of Data, 2021). School of Data presents it as a work in progress rather than a fixed method, and in practice you will often go back and forth between steps. This page goes through each step with typical techniques and tools, before you practise verifying and cleaning data in Exercise 7.

flowchart LR
  A[Define] --> B[Find] --> C[Get] --> D[Verify] --> E[Clean] --> F[Analyse] --> G[Present]
  style D stroke-width:3px
  style E stroke-width:3px

The Data Pipeline is one way of describing the work. Depending on the field, you will meet other names and groupings for the same activities:

  • ETL (Extract, Transform, Load): the classic term in data engineering. Data is extracted from its sources (Get), transformed into a consistent format (Verify and Clean), then loaded into a target system such as a database, data warehouse or portal.
  • ELT (Extract, Load, Transform): the same steps in a different order. Raw data is loaded into the target system first and transformed there, when needed. This approach has become common with cloud data warehouses and data lakes: the raw data is kept, and the transformations can be changed or rerun later without fetching the data again.
  • Data lifecycle models: research data management and digital curation describe a longer cycle that also includes planning, documentation, preservation and reuse, for example the research data lifecycle used by many universities.

Whatever the names, the questions stay the same: where does the data come from, what is changed and why, and where does it end up?

1. Define

Every data project starts with a question. Going from a broad theme (for example, air pollution) to a specific question (has bike sharing reduced air pollution in my city?) tells you what data you will need, and whether it is likely to be easy or hard to get.

2. Find

Datasets are found through portals, catalogues and registries such as opendata.swiss or re3data (see Open Data Platforms and Organisations), but also through search engines, by asking people, or through freedom of information requests. Good metadata matters most at this step: a dataset nobody can find will not be reused.

3. Get

There are three main ways to obtain data:

  • Download a file (CSV, JSON, XML…). This is the simplest, but large files quickly become cumbersome.
  • Call an API to retrieve exactly the records you need, often in real time. For example, swissparlpy is a Python module that gives access to the Swiss Parliament OData service. APIs come with rate limits and can change over time.
  • Scrape a website when no download or API exists, using libraries such as Beautiful Soup or frameworks like Scrapy. Scraping breaks whenever the website changes and raises legal and ethical questions: check the terms of use and robots.txt first.

4. Verify · Exercise 7, Part B

Having the data does not mean it is the data you need. Check its metadata, how it was collected, who produced it and whether the source is credible, and look for values that cannot be right. As School of Data puts it, “all data is bad, we just need to find out how bad it is” (School of Data, 2021). The Assessment, Data Quality, and Best Practices section presents frameworks for this step.

5. Clean · Exercise 7, Parts A to C

Data wrangling (or munging) turns raw data into a consistent, machine-readable format. Typical problems include the same value spelled in different ways, several values in one cell, mixed date formats and duplicated rows. Cleaning is part of data quality management: making sure data is accurate, complete and consistent.

When data from several sources is combined, the same thing is often named differently. Reconciliation matches local values (such as a person’s name) to an authority file and stores its persistent identifier, so your data becomes linked to other datasets.

  • OpenRefine is an open source tool for cleaning, transforming and reconciling messy data through a graphical interface. It runs locally in your browser, keeps a full history of every change, and supports CSV, TSV, JSON, XML and more. It was first released in 2010 as Freebase Gridworks, then renamed Google Refine.
  • pandas is a Python library that does the same in code. It takes more effort to learn, but scripts can be rerun and shared, which makes the cleaning reproducible.

6. Analyse

This is where you look for answers to the question you defined at the start, with spreadsheets, programming languages, visualisations or statistical software. Data mining looks for patterns, correlations and trends in large datasets; it requires computational resources and expertise, and it may raise privacy concerns. Tools such as RapidMiner and WEKA offer graphical interfaces for common methods such as clustering and classification.

7. Present

Finally, results have to be communicated. Presenting data means thinking about your audience, the question you set out to answer and the medium that best conveys your message, whether a chart, a map, an article or an interactive application. Working with designers and storytellers can help.

Publishing Open Data: Portal Software

The Data Pipeline describes the work of someone reusing data. On the other side, publishers rely on dedicated software to run open data portals, manage datasets and their metadata, and provide access via APIs.

Software What it does Used by
CKAN Open source data management system by the Open Knowledge Foundation, with a full API for publishing and discovering datasets (GitHub). Government portals such as data.gov.uk and data.gov
Piveau Metadata management and harmonisation based on Linked Data: stores metadata as RDF, exports DCAT(-AP) and checks quality with SHACL (Kirstein et al., 2020). Several European open data initiatives
EntryScape Information management with semantic integration and Linked Data (Ebner & Palmér, 2014). Swedish public bodies such as Skatteverket and Riksarkivet
uData Open data platform with a user-friendly interface for publishers and users (GitHub). data.gouv.fr and local governments
NotePublishing sensitive data: LOMAS

Not all data can be published openly. LOMAS, developed by the Data Science Competence Center of the Swiss Federal Statistical Office, lets authorised researchers run their code on sensitive datasets without ever seeing the data itself. Results are protected by Differential Privacy, which adds controlled noise to prevent re-identification (Aymon et al., 2024).

Back to top

References

Aymon, D., Lam, D.-T., Marti, L., Maury-Laribière, P., Choirat, C., & de Fondeville, R. (2024). Lomas: A Platform for Confidential Analysis of Private Data. https://doi.org/10.48550/ARXIV.2406.17087
Ebner, H., & Palmér, M. (2014). An information model for managing resources and their metadata. Semantic Web, 5(3), 237–255. https://doi.org/10.3233/SW-120090
Kirstein, F., Stefanidis, K., Dittwald, B., Dutkowski, S., Urbanek, S., & Hauswirth, M. (2020). Piveau: A Large-Scale Open Data Management Platform Based on Semantic Web Technologies. In A. Harth, S. Kirrane, A.-C. Ngonga Ngomo, H. Paulheim, A. Rula, A. L. Gentile, P. Haase, & M. Cochez (Eds.), The Semantic Web (Vol. 12123, pp. 648–664). Springer International Publishing. https://doi.org/10.1007/978-3-030-49461-2_38
School of Data. (2021). Methodology: The Data Pipeline. School of Data. https://web.archive.org/web/20210305220846/https://schoolofdata.org/methodology/

Reuse