Dedupe / conform: one row per key.

-- 1. Rows that fail a check are set aside with the reason, not dropped.
INSERT INTO silver.customers_quarantine (customer_id_raw, signup_date_raw, reason, _source_file)
SELECT b.customer_id, b.signup_date,
       CASE WHEN b.customer_id IS NULL THEN 'null key' ELSE 'signup_date cannot be cast' END,
       b._source_file
FROM bronze.customers AS b
WHERE b.customer_id IS NULL
   OR (b.signup_date IS NOT NULL AND TRY_CAST(b.signup_date AS DATE) IS NULL);

-- 2. One row per key: latest update wins; a total order makes the result repeatable.
INSERT INTO silver.customers (customer_id, country_code, signup_date, updated_at)
SELECT customer_id, country_code, signup_date, updated_at
FROM (
  SELECT
    CAST(b.customer_id AS BIGINT)         AS customer_id,
    UPPER(TRIM(b.country_code))           AS country_code,
    TRY_CAST(b.signup_date AS DATE)       AS signup_date,
    TRY_CAST(b.updated_at AS TIMESTAMP)   AS updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY b.customer_id
      ORDER BY TRY_CAST(b.updated_at AS TIMESTAMP) DESC, b._source_file DESC, b._row_number DESC
    ) AS rn
  FROM bronze.customers AS b
  WHERE b.customer_id IS NOT NULL
    AND (b.signup_date IS NULL OR TRY_CAST(b.signup_date AS DATE) IS NOT NULL)
) AS ranked
WHERE rn = 1;