Success metric of database migration using row counts

Question

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 perfect 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 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 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

Answer 1

Would this work for you?

$$100 \times \bigg(1 - \frac{|V_{src} - V_{tgt}|}{V_{src} + V_{tgt}}\bigg)$$

The fraction part is the ratio of the absolute difference in values to the combined total, which gives $0$ if the values are the same and near $1$ if one value is $0$ and the other $>0$. This is then adjusted so a match is $1$ and and a big difference is near $0$ and scaled to the range $0 - 100$.

For example, for your "Number of users with unlimited rights" this gives:

$$100 \times \bigg(1 - \frac{|412390 - 1286545|}{412390 + 1286545}\bigg) = 48.55$$

and for "Number of users with limited rights" this gives:

$$100 \times \bigg(1 - \frac{|100286613 - 376796|}{100286613 + 376796}\bigg) = 0.75$$