Cloud-Native Graphical and Programmatic Cross-Database Access to Experiment Metadata with Trino, Superset and Ibis

Abstract

LUX-ZEPLIN (LZ) is the world’s most sensitive dark matter direct-detection experiment, acquiring over a petabyte of data per year. LZ also retains several terabytes of user-facing metadata describing detector conditions and data-processing environments needed for reproducible data selection and processing. These metadata live in six heterogeneous databases (SQL and NoSQL) and have historically been accessed through seven separate programmatic and graphical interfaces that connect to unique subsets of these databases. This fragmented nature has led to major problems for both users and maintainers. First, maintenance overhead grew disproportionately: for example, our custom python-based GUI for browsing databases grew to thousands of lines of non-reusable code, requiring near-daily manual restarts to recover from silent failures reported to us by users. Second, our programmatic python interfaces lacked expressiveness, pushing users toward ad-hoc scripts to filter/reshape results and making cross-database joins unnecessarily tedious. More recently, the Genesis Mission and the advent of the American Science Cloud from the US Department of Energy has indicated strong institutional interest in implementing open-source tools and services from the data engineering ecosystem in order to enhance compatibility with AI-based workflows. Recognizing these factors and the aforementioned problems, we consolidated access around three complimentary tools: Trino, a distributed SQL query engine which provides a single SQL facade across databases; Superset, a graphical dashboard builder which operates directly on Trino; and Ibis, a library to create composable SQL queries with dataframe-like python API. In this talk I will detail the benefits we recouped from deploying these tools, along with practical challenges encountered along the way, such as reconciling Trino's SQL interface with schemaless MongoDB sources. Topics covered include a showcase of how we were able to immediately deprecate one database browser with Superset and Trino, consolidating a key dashboard and cutting worst-case load times from 12s to 3–4s. We also reduced a previously expensive categorical lookup from 40s to 100ms by materializing a pre-aggregated table with Ibis. I will also discuss using Ibis to improve transparency and expressiveness over our previous python interfaces, while keeping a smaller code footprint. Finally, I will touch on the advantages of our shift to a GitOps workflow. Helm cut deployment time for these services from multiple hours of manual configuration via a GUI to a single reproducible command, while Ibis-defined data flows now refresh derived datasets via scheduled CI/CD pipelines instead of requiring manual input.