Max block size

[RobinL]

Should blocking rules allow a 'max block size' type parameter so that if you do block_on('first_name', 'surname', max_size=10_000). this would limit the size of any specific block to 10_000k records.

This could be used in situations of heavy skew and could be implemented with e.g. and first_name || surname not in (select first_name || surname from t where count() < group by first_name || surname ) type statement

[aymonwuolanne]

This would be pretty useful!

[aymonwuolanne]

Hey @RobinL, I've been playing around with a solution for this. For filtering out those large blocks I think the options are

• using not in like in your example, where if you have multiple columns you need to concatenate them somehow (maybe with a separator). Weirdly I couldn't get the syntax to work with this for a simple example in duckdb, even using having instead of where
• using not exists in a subquery
• counting the block sizes earlier in the pipeline and then filtering the large blocks from the input before you do the blocking for that rule

The second and third choices work, and as far as I can tell from a few tests in duckdb they get optimised to the exact same execution plan anyway. To me the third option feels simpler, so I went with that.

One challenge here is how you exclude the preceding blocking rules. If the first blocking rule was on first_name and surname with a max block size of 1000, and in the second blocking rule you filter out pairs that have the same first_name and surname, then you could be excluding pairs that were in blocks with a size over 1000.

This gave me the idea that you can just produce all the blocked id pairs without worrying about excluding pairs from preceding rules, and add a final step at the end to remove all duplicated pairs and assign the match key as the minimum match key for that pair of ids. This makes the blocking code a lot simpler, especially for exploding blocking rules. Performance-wise, I think this is actually a decent improvement in cases where there are exploding blocking rules, since these use an EXISTS statement to check whether ids are included in previous blocking rules, which usually involves the SQL engine doing some kind of join in the background. I think the worst-case performance would be situations with like 30 closely overlapping normal blocking rules. I don't think it'd be much slower at all but it's probably worth testing more thoroughly than I have so far. The only effect on the final blocked_id_pairs table should be that duplicates are removed (e.g. if unique_id is not actually unique across every row of the input), which shouldn't be an issue?

The basic idea after that would be to add some logic in block_using_rules_sqls that checks for a max_block_size, and if necessary it adds a special input table to the pipeline that has large blocks filtered out, then swaps out the table name that gets passed to the create_blocked_pairs_sql method.

[RobinL]

Hi @aymonwuolanne I think you're right to challenge the existing logic where, for each blocking rule, we add SQL that excludes preceding blocking rules.

This is a hangover from very early versions of Splink. The idea was to avoid the deduplication step. Essentially that we could then do a UNION ALL rather than a UNION (n.b. UNION has an implicit deduplication, whereas UNION ALL does not).

The idea was that I was worried that in Spark, the UNION requires a shuffle. So the entire rationale was based on avoiding a shuffle, which in Spark are expensive.

There are various reasons this may be less relevant:

• We recommend DuckDB over Spark for most users, and I doubt it improves performance much, if at all, in DuckDB
• I don't think I ever benchmarked this robustly, so it may not have much performance impact even in Spark
• When I wrote the logic, I wasn't anticipating exploding blocking rules, and as you mention, the approach is probably worse for performance in this case.

So in general I'd be supportive of the idea of simplifying everything and just generating all pairs and then deduping (within in a single CTE pipeline....we materialise the pairs, so we'd want to do that only after dedup)

It feels like potentially doing this may be a sensible first step, before adding the max block size functionality. What do you think?