estimate_u_using_random_sampling results in error "unique_id" not found

[Anders Alexandersson (aalexandersson)]

I'm converting my code from 3.9.15 to 4.0.7, and in 4.0.7 estimate_u_using_random_sampling results in error "unique_id" not found. I'm using the default DuckDB, and I'm on Windows 11.

This code worked in Splink 3.9.15 on simulated data:

# unique_id_column_name = "simulant_id"
linker = DuckDBLinker([df1, df2], settings, 
     input_table_aliases=["df1_large", "df2_small"])
linker.estimate_u_using_random_sampling(max_pairs=1e9, seed=123) 

This code fails in Splink 4.0.7 on real data:

# unique_id_column_name = "pid"
linker = Linker([A_cleaned, B_cleaned], settings, db_api)
linker.training.estimate_u_using_random_sampling(max_pairs=1e9, seed=123) 

Is this an issue in Splink 4.0.7 or am I making a user error?

Here is the long error output:

splink\internals\estimate_u.py:143, in estimate_u_values(linker, max_pairs, seed)
    136 sql = f"""
    137 select *
    138 from {table_to_sample_from}
    139 {training_linker._random_sample_sql(proportion, sample_size, seed)}
    140 """
    142 pipeline.enqueue_sql(sql, "__splink__df_concat_sample")
--> 143 df_sample = db_api.sql_pipeline_to_splink_dataframe(pipeline)
    145 pipeline = CTEPipeline(input_dataframes=[df_sample])
    147 if linker._sql_dialect.sql_dialect_str == "duckdb" and max_pairs > 1e4:

File V:\Testing\Splink_2025\.venv\Lib\site-packages\splink\internals\database_api.py:200, in DatabaseAPI.sql_pipeline_to_splink_dataframe(self, pipeline, use_cache)
    197     sql_gen = pipeline.generate_cte_pipeline_sql()
    198     output_tablename_templated = pipeline.output_table_name
--> 200     splink_dataframe = self.sql_to_splink_dataframe_checking_cache(
    201         sql_gen,
    202         output_tablename_templated,
    203         use_cache,
    204     )
    205 else:
    206     # In debug mode, we do not pipeline the sql and print the
    207     # results of each part of the pipeline
    208     for cte in pipeline.ctes_pipeline():

File V:\Testing\Splink_2025\.venv\Lib\site-packages\splink\internals\database_api.py:171, in DatabaseAPI.sql_to_splink_dataframe_checking_cache(self, sql, output_tablename_templated, use_cache)
    168         print(df_pd)  # noqa: T201
    170 else:
--> 171     splink_dataframe = self._sql_to_splink_dataframe(
    172         sql, output_tablename_templated, table_name_hash
    173     )
    175 splink_dataframe.created_by_splink = True
    176 splink_dataframe.sql_used_to_create = sql

File V:\Testing\Splink_2025\.venv\Lib\site-packages\splink\internals\database_api.py:93, in DatabaseAPI._sql_to_splink_dataframe(self, sql, templated_name, physical_name)
     85 """
     86 Create a table in the backend using some given sql
     87 
   (...)
     90 Returns a SplinkDataFrame which also uses templated_name
     91 """
     92 sql = self._setup_for_execute_sql(sql, physical_name)
---> 93 spark_df = self._log_and_run_sql_execution(sql, templated_name, physical_name)
     94 output_df = self._cleanup_for_execute_sql(
     95     spark_df, templated_name, physical_name
     96 )
     97 self._intermediate_table_cache.executed_queries.append(output_df)

File V:\Testing\Splink_2025\.venv\Lib\site-packages\splink\internals\database_api.py:75, in DatabaseAPI._log_and_run_sql_execution(self, final_sql, templated_name, physical_name)
     72 except Exception:
     73     pass
---> 75 raise SplinkException(
     76     f"Error executing the following sql for table "
     77     f"`{templated_name}`({physical_name}):\n{final_sql}"
     78     f"\n\nError was: {e}"
     79 ) from e

SplinkException: Error executing the following sql for table `__splink__df_concat_sample`(__splink__df_concat_sample_607344abc):
CREATE TABLE __splink__df_concat_sample_607344abc AS
WITH __splink__df_concat AS (
  SELECT
    '__splink__input_table_0' AS source_dataset,
    "pid",
    "fname",
    "lname",
    "ssn",
    "dob",
    "sex",
    "race",
    "address",
    "city",
    "state",
    "zip",
    "maiden",
    "mi",
    "ssn_orig",
    "stnum",
    "dob_orig",
    "age",
    "fname_orig",
    "fname_dm",
    "lname_dm",
    "stname",
    RAND() AS __splink_salt
  FROM __splink__input_table_0
  UNION ALL
  SELECT
    '__splink__input_table_1' AS source_dataset,
    "pid",
    "fname",
    "lname",
    "ssn",
    "dob",
    "sex",
    "race",
    "address",
    "city",
    "state",
    "zip",
    "maiden",
    "mi",
    "ssn_orig",
    "stnum",
    "dob_orig",
    "age",
    "fname_orig",
    "fname_dm",
    "lname_dm",
    "stname",
    RAND() AS __splink_salt
  FROM __splink__input_table_1
)
SELECT
  *
FROM (
  SELECT
    *
  FROM __splink__df_concat
  ORDER BY
    unique_id NULLS LAST
) TABLESAMPLE BERNOULLI (30.358296733021987 PERCENT) SEED (123)

Error was: Binder Error: Referenced column "unique_id" not found in FROM clause!
Candidate bindings: "fname_dm", "lname_dm", "fname_orig", "source_dataset", "ssn_orig"

LINE 19:     from (select * from __splink__df_concat order by unique_id)
                                                              ^

[Robin Linacre (RobinL)]

Thanks for the report, looks like this is a bug caused by this recent PR:
https://github.com/moj-analytical-services/splink/pull/2642/files
Which hardcoded unique_id rather than using the unique id column name provided by the user.

If you downgrade to 4.0.6 it should work. We will endeavour to fix asap! - i created an issue here
#2657