Description
I have a problem where I'm tasked to successfully transform and repurpose data from one SQL server to another. Call the source $\text{src}$ and the target database $\text{tgt}$.
In order to assess the quality of the migration for a given field, I am given volumes only, namely
- $V_\text{src} = \#\{\text{rows in src for which property }P\text{ is true}\}$
- $V_\text{tgt} = \#\{\text{rows in tgt for which property }P\text{ is true}\}$
I was wondering if there was a set of metrics one often uses to report completion metrics from $0\%$ to $100\%$. The metric should be 0 if the target is very dissimilar from source, and 100 if it is perfeectperfect similarity.
An important side issue
Often you overshoot and you have $V_\text{src} \ll V_\text{tgt}$, so just reporting ratios yields that $\text{tgt}$ has $270\%$ more content than $\text{src}$.
In these situations I would like to assign property P a low score near 0, but not a negative score.
Approach
For now I have for a given error coefficient $\varepsilon$ that's (mostly) $-1\leq \varepsilon \leq 1$ (but can overshoot to values close to 2 or 3) the following rescaling functions:
$$\text{invLin}(\varepsilon) = \dfrac{1}{1+\varepsilon}\qquad {\color{blue}\checkmark}\quad\text{slow degrowth from }100\%\text{ to }0\%$$$$\text{invLin}(\varepsilon) = \dfrac{1}{1+\varepsilon}\qquad {\color{blue}\checkmark}\quad\text{slow decrease from }100\%\text{ to }0\%$$ However it gives huge error percentages like $500\%$ a high grade and doesn't penalize low error percentages enough to my taste. I came up with this second one:
$$\text{invLog}(\varepsilon) = 1-\log^{+}(1+\varepsilon)\qquad {\color{blue}\checkmark}\quad\text{quick degrowth from }100\%\text{ to }0\%$$$$\text{invLog}(\varepsilon) = 1-\log^{+}(1+\varepsilon)\qquad {\color{blue}\checkmark}\quad\text{quick decrease from }100\%\text{ to }0\%$$
You can see the two functions plotted for values of $100\lvert\varepsilon\rvert$ ranging from $0\%$ to $500\%$ with a zoom on the $1-100$ zone on the left part
Notation: $f^+$ is the positive part of a function $f^+(x)=\max(f(x),0)$
Addendum (Sample data)
I was told it was easier to guess with some sample data, here's an example:
| Condition $P$ | $V_{\text{src}}$ | $V_{\text{src}}$ |
|---|---|---|
| Sum Expected Amount (\$) | 1543385231 | 1543385217,9 |
| Sum Commited Amount (\$) | 83123640,62 | 83123640,62 |
| Sum Real Amount (\$) | 1246623860,05 | 203779813,48 |
| Sum Amount for Region 1 (\$) | 4898 | 26712 |
| Sum Amount for Region 2 (\$) | 205509 | 93393 |
| Sum Amount for Region 3 (\$) | 3818 | 1667 |
| Number of users with unlimited rights | 412390 | 1286545 |
| Number of users with limited rights | 100286613 | 376796 |
| Number of shared costs | 402 | 222 |
| Number of items created between 2019-2030 | 4 | 4 |
| Number of items created between 2020-2023 | 260 | 260 |