alan-turing-institute / alan-turing-institute/sqlsynthgen
Improve writing configs for src stats
- Dominant language
- Python
- Stars
- 12
- Forks
- 4
- PR merge metrics
- No merged PRs in 30d
Description
I often find myself writing very repetitive source stats queries. I see at least three reasons for this:
1) Wanting similar aggregate data for many columns and many tables, like distributions of values in categorical columns. There's a whole `src-stats` block for each such column, and they look very alike.
1) Having joint precursor results in many src-stats queries, like prefiltered tables with some joins, that are then the inputs into the interesting part of the query.
1) Limitations of smartnoise-sql.
The last point is perhaps non-obvious, so I'll elaborate.
## How do limitations of snsql cause many src-stats queries to be written.
Say I'm working on medical data, and I want to query for how many times each patient, on average, gets prescribed each possible medication, and at what dose. I may write a src-stats entry like this:
```
query: >
SELECT count(*) AS num, avg(dose) AS avg_dose, drug_name, person_id
FROM prescriptions
GROUP BY drug_name, person_id
dp-query: >
SELECT SUM(num) AS num, AVG(avg_dose) AS avg_dose, drug_name
FROM query_result
GROUP BY drug_name
epsilon: 0.2
delta: 0.00001
snsql-metadata:
max_ids: 30
person_id:
type: int
private_id: true
num:
type: int
lower: 0
upper: 100
avg_dose:
type: float
lower: 0
upper: 1000
drug_name:
type: string
```
This is a very typical src-stats query: Do a count or an average over some variable (`drug_name`), but have the first version also group by over `person_id` and the dp-query to then aggregate over the `person_ids`.
I choose `max_ids: 30`, because let's say there are 30 different possible drugs, and thus 30 different possible rows a person can have in the result of `query`. I choose 100 for the max `num` because we assume no one gets any one drug prescribed more than 100 times, and 1000 for the max for `avg_dose`, because we assume no drug has typical doses higher than that.
Notice a couple of issues here:
1. We know that even if there are 30 rows for one person in the result of `query`, they are for different drugs, and thus don't cumulate under the `dp-query`. However, smartnoise-sql does not know this. It assumes that there could be 30 rows all for the same person and drug. This means that in the total aggregate `num` count for that drug, the maximum contribution from one person could be 30*100, whereas in reality it's only 100. This results in snsql applying 30x more noise than it has to.
2. Different drugs can have wildly different typical doses. For many the dose may always be either 1 or 2, counting e.g. pills. For some other, it might be 300, counting milligrams. We have to set the bounds on `avg_dose` according to the latter, but the same amount of noise will be applied to the former too, which may completely drown out the signal. A similar thing can happen for `num`.
Both of these make it hard to give reasonable privacy parameters to this query, that wouldn't result in excessive noise in some cases. The solution is simple: Write separate src-stats queries for all the drugs. Then you can set the upper bound of `avg_dose` individually for each drug, and snsql also doesn't over count the maximum impact a single person can have on `num`. But no one wants to populate their `config.yaml` with 30 such src-stats blocks, that would be identical except for `WHERE drug_name = blahblah` line.
## What to do?
I would like some way to template the src-stats queries, or to write python code that generates them. I am very wary of ruining the simplicity and transparency of the `src-stats` blocks in the current config format though. I think the simplicity and transparency must take priority, but if we can figure out a way to keep them while still adding more flexibility, that would greatly simplify the writing of more advanced configs. Done right, it could even increase transparency for advanced configs. I'm currently writing a config for a medical data schema, and even though my fidelity remains quite crude, my `config.yaml` is more than 2200 lines, are it's mostly made of very repetitive src-stats blocks. You can say it's yaml and thus human readable, but show me the human who will actually read that.
Contributor guide
Research direction
Start by reviewing the repetitive src-stats blocks in the config.yaml example and the SQL query and dp-query patterns described in the issue. Then inspect how the repository currently loads and represents these configs; done means a documented, transparent way to generate or template repeated blocks without obscuring their resulting queries.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- data-engineering, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100