Data Profiling and Quality Measurement

Profiles tabular data and measures its quality. Discovers structural patterns, implicit types, mixed date formats and reusable findings about potential data issues; defines quality frameworks with user-declared dimensions, reusable metrics with explicit granularity and typed aggregation; keeps versioned histories and detects structural drift; writes self-contained HyperText Markup Language reports; and produces editable cleaning plans that apply only the actions the user selects, with an audit log. The general model follows Batini and Scannapieco (2016) . The package implements version 1.6 of the data quality management framework for digital government of the Uruguayan Agency for Electronic Government and Information and Knowledge Society, AGESIC (2020) < https://www.gub.uy/agencia-gobierno-electronico-sociedad-informacion-conocimiento/comunicacion/publicaciones/marco-trabajo-para-gestion-calidad-datos>; an optional taxonomy of the fifteen characteristics of the standard 25012 of the International Organization for Standardization and the International Electrotechnical Commission, ISO/IEC (2008) < https://www.iso.org/standard/35736.html>; and the four levels and nineteen principles of the national quality assurance framework of the United Nations (2019) < https://unstats.un.org/unsd/methodology/dataquality/>, as adapted for Latin America and the Caribbean by the Statistical Conference of the Americas of the Economic Commission for Latin America and the Caribbean, CEA-CEPAL (2022) < https://repositorio.cepal.org/handle/11362/47464>. AGESIC, CEA and CEPAL are the Spanish acronyms of these institutions.

lupa

Data profiling and quality measurement with evidence. Every result states its scope and what it could not measure.

🔎 What it does and who it is for

lupa examines a table or a database before you trust it. It finds what summary() misses:

• missing values disguised as "S/D" (sin dato, “no data”) or -99;
• the same value written in several ways;
• dates in mixed formats;
• repeated rows and columns;
• structure nobody declared.

It says which rows each problem is in and which checks it could not run. Then it helps turn what was confirmed into measurements, evaluate them, clean a copy with an editable plan and compare deliveries. It is for anyone who analyzes, produces or publishes tabular data and needs to know how far it can be trusted.

What sets it apart:

• No single score. Each contradiction comes with its type, severity and evidence.
• Every figure carries its scope: the rows it used and whether it sampled.
• What could not be measured is declared, with its reason. It never counts as zero.
• The input is never modified.

Installation

Install the released version from CRAN:

install.packages("lupa")

or the development version from GitHub:

# install.packages("pak")
pak::pak("sebollin/lupa")

It depends only on cli and data.table. Suggested packages enable specific capabilities:

• DBI with a driver (RSQLite, duckdb, RPostgres or odbc) to profile inside a database;
• stringdist for approximate text comparison;
• sf for geometries.

The API, the help pages, the guides and the printed output are in Spanish, and the public names are part of the contract: the outputs below appear exactly as lupa writes them.

⏱ In 30 s

datos_operativos ships with the package: 13 rows and 10 columns with planted defects. Profile it in one call and start with the findings of severity error:

library(lupa)
perfil <- perfilar(datos_operativos)
h <- hallazgos(perfil)
errores_primero <- order(h$severidad, decreasing = TRUE)
print(head(h[errores_primero, c("columna", "tipo_hallazgo", "severidad")], 8),
      row.names = FALSE)
#>         columna           tipo_hallazgo  severidad
#>  codigo_usuario   faltantes_disfrazados      error
#>    fecha_evento   faltantes_disfrazados      error
#>    fecha_evento   formatos_fecha_mixtos      error
#>           canal   faltantes_disfrazados      error
#>            <NA>        filas_duplicadas      error
#>  codigo_usuario       alta_cardinalidad sospechoso
#>    fecha_evento       alta_cardinalidad sospechoso
#>    fecha_evento tipo_declarado_distinto sospechoso

A columna of <NA> marks a finding about the whole table, such as the duplicate rows.

What could not be measured does not disappear. In this table 9 diagnostics were left unevaluated, each one counted in every column where it could not run: the outlier search, for instance, needs more values than the table has. cobertura(perfil) says which ones, in which column, why and how to resolve them:

as.data.frame(table(diagnostico = cobertura(perfil)$diagnostico), responseName = "columnas")
#>              diagnostico columnas
#> 1               outliers        4
#> 2            patron_raro        1
#> 3 proximidad_vocabulario        3
#> 4   resumen_cuantitativo        1

🗄️ Tables and databases

perfilar() profiles a data frame in memory. perfilar_dbi() profiles a table inside the database: the aggregates run as SQL on the engine, and only the rows that fit within the caps come into R. The same surface is measured against 6 real engines:

PostgreSQL and MariaDB are measured on every push (engines CI), and SQLite and DuckDB by the test suite on every CI platform; MySQL and SQL Server, against local containers before each release. Oracle is expected to work but has not been measured, and BigQuery is not documented. requisitos_motor() says what each engine needs. The versions, the drivers and the date of each run are in Motores y escala (Engines and scale).

What summary() does not show

summary() describes columns one at a time. lupa compares representations and cross-checks columns and rows. It returns its findings as a table: type, severity, evidence, counts, a suggestion and traceability down to the rows. A finding type that does not appear is a diagnostic that ran and found nothing; what did not run is in cobertura().

Among the outputs of that run are:

The full vocabulary, with what each finding type means, is in the article Alcance y cobertura (Scope and coverage).

How to read a profile

severidad (severity) is an ordered factor: ok < sospechoso < error.

• ok records an informative condition, not an approval: a column that looks like an identifier is ok.
• sospechoso (suspicious) asks for a look in context: alta_cardinalidad (high cardinality) in codigo_usuario suggests checking whether the column is free text, an identifier or a poorly normalized category.
• error means that a check crossed its declared criterion. It is the only level worth using as an automatic gate, and only after accepting that criterion.

With clave (key), each finding carries the values that identify its rows, so the case can be looked up in the source system without opening the table:

con_clave <- perfilar(datos_operativos, clave = "id_evento")
#> Warning: La clave declarada no es única: entre las 13 filas con la clave completa, 1 repite su
#> valor (2 filas participan en colisiones). La clave cumple la ausencia de nulos: no tiene
#> valores ausentes.
h <- hallazgos(con_clave)
fila <- which(h$tipo_hallazgo == "faltantes_disfrazados" &
                h$columna == "fecha_evento")
claves <- h$trazabilidad[[fila]]$claves
claves
#>   id_evento
#> 1    EVT011
datos_operativos[datos_operativos$id_evento %in% claves$id_evento,
                 c("id_evento", "fecha_evento")]
#>    id_evento fecha_evento
#> 13    EVT011         NULL

The warning, which says that the declared key is not unique, is part of the answer: datos_operativos has a duplicated row, so no key can be unique, and the profile records this as a clave_no_unica finding of severity error.

What could not be measured is recorded, never counted as zero. That is why an automated check that looks for error must also read cobertura(): zero errors with unevaluated diagnostics is not a clean profile.

Counts (rows, missing values, distinct values, exact duplicates and summaries) use every row; patterns, types and date formats are inferred from a systematic sample of up to 100,000 rows (muestra). The guide Leer un perfil (Reading a profile) explains each column of the findings, keys, disagreements between columns and the glossary.

Where it fits

To summarize and plot columns there are skimr and DataExplorer; to diagnose a table and write up its report, dlookr and dataReporter; to evaluate rules that someone has already written, validate, pointblank, assertr and dataquieR. lupa works on the stretch before the rule and on the one after it: it finds what no rule names yet, proposes what to measure without built-in thresholds and, once a rule is confirmed, measures it with its scope, cleans a copy and compares deliveries. It is meant to be used alongside those packages, not instead of them.

Function map

The reference documents every function.

Profiling a database: what comes into R

perfilar_dbi() returns two blocks: resumen_tabla, with the aggregates that the engine computes in SQL, and perfil_muestra, the row-level profile of what is read into R. By default the aggregates use the whole table and the row-level profile tries to bring in all the rows that fit within the caps: it is not a cheap sample.

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "eventos", datos_operativos)
en_la_base <- perfilar_dbi(con, "eventos")
names(en_la_base)
#> [1] "resumen_tabla"  "perfil_muestra"

Without orden_muestra, a muestra = n read gets the first rows the engine returns, in an order SQL does not guarantee; with orden_muestra the read can be reproduced. What was read into R is described in perfil_muestra$meta$origen_dbi$muestreo; resumen_tabla$meta$muestreo exists only with universo = "muestra_motor". There is no snapshot: resumen_tabla$meta$snapshot is FALSE, because each aggregate is measured in its own query and the table can change from one query to the next.

The tested engines are DuckDB, MariaDB, MySQL, PostgreSQL, SQL Server and SQLite. The step-by-step walkthrough is in Perfilar una base (Profiling a database).

⚠️ Limits to know before a large run

• Sample caps. What is read into R, or sampled within R, stops at 1,000,000 cells (max_celdas_muestra) and 512 MiB (max_bytes_muestra), and the cut is recorded in cobertura() together with the cap that triggered it (Motores y escala).
• Cost policy. In perfilar_dbi(), politica_costo = "todas" (the default) computes the mode and the median in every column; "por_cardinalidad" skips the mode where the proportion of distinct values reaches umbral_cardinalidad (0.5), and plan_perfilado_dbi() shows the queries before running them (Motores y escala).
• Approximate duplicates. They are off by default. detectar_duplicados_aproximados() runs an exact comparison of up to 50,000,000 pairs (max_pares). With the default strategy ("auto"), no sample and that cap, more than 10,000 rows take it to MinHash/LSH, which can miss pairs and says so (candidates, bands and the collision guarantee); with a lower max_pares or a sample it stays with the exact or sampled comparison, and estrategia = "lsh" forces it. estimar_costo() anticipates the switch (Escala y duplicados aproximados).
• Wide tables. From 100,000 cells up, an interactive session gets a notice of how long perfilar() may take; the estimate relies on an assumption built into the code (10,000 cells per second), not on a measurement of the machine; the dated measurements are in Evidencia y rendimiento.
• Millions of rows in memory. perfilar() and analizar() take minutes, not seconds: profile a sample or use perfilar_dbi() instead. The measured figures, with their dates, are in Evidencia y rendimiento (Evidence and performance).

📄 A report in a single file

analizar() gathers the profile, the distributions, the associations and the temporal analysis into one object, together with a proposed quality model, its dashboard and a cleaning plan; reportar() writes it all to a self-contained HTML file for sharing.

analisis <- analizar(datos_operativos)
reportar(analisis, archivo = "informe-lupa.html")

The proposal is only a suggestion: domains and patterns learned from the same delivery are proposed as inactive and are not measured until someone confirms them. guardar_analisis() and leer_analisis() save and restore the whole analysis. The guide is Explorar columnas con analizar() (Exploring columns with analizar()).

What it does not do

lupa does not turn the name of a quality framework into a measurement. A framework is a taxonomy: a factor is measured when the data provide evidence for it or when the requirement and the metric that measures it are declared. This is the coverage of the included frameworks:

The 13 CEPAL principles that are out of scope concern the statistical system and its institutional process, not the values in a table. ISO/IEC 25012 is fully covered once its requirements and metrics are declared. cobertura_analisis() reports which factors were measured; the absence of findings is not a claim that the data are good.

Nor does it certify a dataset or invent a global score: indice_calidad() computes an index only from the weights the project declares. And it does not modify the input: no analysis function alters the table it receives (not its values, types, names or attributes), not even a data.table. Cleaning returns a copy and leaves the original intact.

🧭 The learning path

The guides are meant to be read in this order, and all 12 are on the website. Guides 1 to 6 also ship with the package (vignette("lupa") opens the first one); 7 to 12 are on the website only.

1. Empezar en diez minutos (Getting started in ten minutes): profile a table, start with the errors, go from a finding to its rows with clave, read the coverage, save a report and clean a column in a copy.
2. Leer un perfil (Reading a profile): the severities, what each finding carries, the accessors, keys, contradictions between columns and the glossary.
3. Explorar columnas con analizar() (Exploring columns with analizar()): what analizar() builds in a single call; how to read patterns, types, dates, distributions, associations and time; and how to save and report the result.
4. Estructura no declarada (Undeclared structure): candidate keys, relationships between tables and dependencies between columns.
5. El vacío por diseño (Empty by design): empty cells that are correct, the signal that asks for them to be declared and everything else that can be declared to the package.
6. Limpiar con un plan (Cleaning with a plan): what the plan proposes, how it is applied to a copy and what it leaves uncovered.
7. Del hallazgo a la métrica (From finding to metric): from findings to a proposed set of metrics, which are measured, aggregated and evaluated against a threshold.
8. Marcos y modelos de calidad (Quality frameworks and models): a custom framework, metrics at three levels, the dashboard, an index with declared weights and the included frameworks.
9. Comparar entregas y seguir la calidad (Comparing deliveries and tracking quality): compare deliveries, tell a real change from a rounding difference and look for drift in a history.
10. Perfilar una base (Profiling a database): the two blocks of perfilar_dbi(), what each option brings into R and where the sampling metadata is kept.
11. Colecciones (Collections): profile the tables of a database, read what could not be measured and look for relationships between them.
12. Escala y duplicados aproximados (Scale and approximate duplicates): request the approximate comparison, estimate its cost, know when LSH takes over and what each cap trims.

For advanced users

The detailed reference lives in four articles on the website, in Spanish. Whatever changes the cost, the memory use or the meaning of a result is also in the help page of each function, which ships with the package (?perfilar, ?perfilar_dbi, ?plan_perfilado_dbi, ?detectar_duplicados_aproximados).

Each piece of evidence states how it is reproduced: whether it regenerates offline, needs a database engine or depends on a downloaded resource, with its date and checksum (Evidencia y rendimiento).

Citation, license and contributing

citation("lupa")

The conceptual references are Batini and Scannapieco (2016), the Data Quality Management Framework for Digital Government v1.6 by AGESIC, the CEA/CEPAL quality assurance framework (2022) and ISO/IEC 25012:2008.

The public API of lupa may change before 1.0; breaking changes are announced in NEWS, with a deprecation warning first whenever possible. The stable contracts are the declared units, the scope, the protection of personal data and the audit log; implementation details and timings may change as long as those contracts still hold.

Bugs, proposals and documentation fixes go to the issue tracker, and CONTRIBUTING.md explains how to contribute. lupa is released under the GPL-3; inst/COPYRIGHTS details the Apache-2.0 material translated from ftfy and the MIT data taken from naniar.