Wikidata ETL Project

[panasenco]

My name is Aram Panasenco, and I work as a data warehouse architect at a cancer hospital.

In my job and in open source projects, I've seen ETL done horribly wrong in a dozen different ways. I've yet to see it done well, but maybe we can change that. 😄

These are things I'd like to see in any ETL project:

1. Configuration as code. At my job, we define Airflow DAG files and Bamboo YAML files. I see that LinkedPipes defines its pipelines as RDF. That's great! Is it possible to write the pipelines in Turtle by hand and use those files as the source of truth instead of a database? Or at least use Turtle files to store the pipeline definitions instead of a database?
2. All ETL logic in a single Git repository. This has several advantages:
   • Governance and transparency. No need to go hunting to find where an ETL process is defined. Developers and power users have complete visibility into every single ETL process. Pushing into master can be disabled, and a pull request review process instituted to make sure that any new ETL processes are reviewed.
   • Set automated deployment up once and it works for everyone. Once a new ETL process passes code review and is merged into master, it can be deployed and run automatically. We use Bamboo for this at my job.
   • Work towards disabling all other pipelines. Ideally, all automated ingest into Wikidata will be done by making a pull request in the central ETL Git repo. Whether that's possible to enforce is another question. 😄
3. Documentation and lineage. At a glance, LinkedPipes already seems to do this decently via its GUI. Is it possible for a LinkedPipes site to be public-facing and require login to modify the ETL processes? That way any Wikidata user can use the site to explore data pipelines, understand how the data was transformed and what tests are run, and generally gain confidence in the quality of the ETL process.
4. Automated testing and quality assurance. It should be possible to assert things about both the data (e.g. no duplicates, referential integrity, ad hoc cleanliness tests) AND the configuration itself (test that the configuration has data tests, lint the Turtle files, etc.).
5. Incremental loads. You guys already do this. In fact, it may not be possible to "truncate and load" Wikidata. 😆

I just want to use these points as a jumping off point for the discussion. LinkedPipes developers - How do you envision the ETL into Wikidata to work? What work has already been done?

[jakubklimek]

Thanks for your interest in LP-ETL. The principles/ideas you outline definitely make sense, and I would definitely like to see them implemented in LP-ETL. Having said that, we use LP-ETL as a tool in our projects, we do not develop it as a stand-alone product. Therefore, we add bigger features mostly when we have a project use case, and therefore resources, for that.

Specifically to your points:

1. Yes, LP-ETL pipelines are stored as RDF TriG files in a file system, there is no database in LP-ETL as it is not necessary. As to generating the pipeline files - this was the original idea behind storing the pipelines as RDF. And indeed, in some of our projects such as LinkedPipes Applications the pipelines are generated and run by LP-ETL. There was never the need to writing the RDF files by hand though and proper documentation of the RDF vocabulary used in the pipeline definition is missing. What you can always do though is to create a pipeline in our frontend, download it and take a look at which IRIs are used for what. Also, there are sample pipelines available as documentation of some of the components.
2. This could be done only theoretically, there is no support for this in LP-ETL itself. The pipeline files are, nevertheless, stored in a file system in a few folders (pipelines, templates), which could be made into Git repositories and synced manually. There is only a slight issue that the pipeline definitions use IRIs dereferencable to the LP-ETL instance. Therefore, those definitions would not be usable in another LP-ETL instance directly, without importing them, which rewrites the IRIs. This actually seems quite an interesting way to go as there is currently no support for versioning of the pipelines (i.e. integration with Git). The management of ETL processes in Wikidata is, of course, out of scope here.
3. This is not possible at the moment. There is no user management (we did not need it). We have either public instances where everyone can do anything they want or restricted instances behind Basic HTTP authentication done by nginx. We actually thought about filtering other than HTTP GET request to create such a "read-only" instance, but it never received sufficient priority.
4. This can be done e.g. as GitHub Actions on top of the Git repo with pipeline definitions for the pipeline configurations, and using existing, or creating additional components to be used for checking the data in the pipelines themselves.
5. Yes, this depends on how you create your pipeline. Currently, with our Wikidata pipelines, it is a bit non-trivial to do the diff and could be made simpler by creating additional components.

In scope of our Wikidata project we created the "desired state" of the Wikidata items and statements, and let the Wikidata loader component check each item and update it as necessary. Making diffs in RDF and SPARQL is not a trivial task, but it could be done that only the items to be updated are touched.

[panasenco]

Thanks for your detailed response!

Regarding point 3 (documentation and lineage), I thought of another alternative today. ETL metadata could be loaded into Wikidata as well in some standard way. For example, in the Wikidata cats query, we could add something like

?item wdt:{{ETL Type}} ?etl

which points to a graph containing information about how that particular item was ETLed in: The source, the transformations, etc. Maybe it could be done in such a way that the ETL process could be completely reconstructed from the metadata!

Then a 'well-behaved' ETL process would load both the data and the ETL metadata.