Lily+Sangetsu read-only dedup pass: 0 true dups, 12,806 net-new (164 matches all cross-vendor SKU-string collisions); reproducible dedup.sql

e92745b32864a0dda408e80b053f7bbbc9742fa1 · 2026-06-30 07:10:23 -0700 · Steve

Files touched

A onboarding/sangetsu-lilycolor/dedup.sql

Diff

commit e92745b32864a0dda408e80b053f7bbbc9742fa1
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue Jun 30 07:10:23 2026 -0700

    Lily+Sangetsu read-only dedup pass: 0 true dups, 12,806 net-new (164 matches all cross-vendor SKU-string collisions); reproducible dedup.sql
---
 onboarding/sangetsu-lilycolor/dedup.sql | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)

diff --git a/onboarding/sangetsu-lilycolor/dedup.sql b/onboarding/sangetsu-lilycolor/dedup.sql
new file mode 100644
index 00000000..30870c79
--- /dev/null
+++ b/onboarding/sangetsu-lilycolor/dedup.sql
@@ -0,0 +1,22 @@
+\set ON_ERROR_STOP on
+CREATE TEMP TABLE staged(source text, mfr_sku text, norm text);
+\copy staged FROM '/tmp/staged-skus.tsv'
+CREATE TEMP TABLE reg_norm AS
+  SELECT DISTINCT upper(regexp_replace(mfr_sku,'[^A-Za-z0-9]','','g')) n, dw_sku, vendor_name
+  FROM dw_sku_registry WHERE mfr_sku IS NOT NULL AND mfr_sku<>'';
+CREATE TEMP TABLE shop_norm AS
+  SELECT DISTINCT upper(regexp_replace(coalesce(NULLIF(mfr_sku,''),NULLIF(sku,''),variant_sku),'[^A-Za-z0-9]','','g')) n, dw_sku, vendor
+  FROM shopify_products;
+CREATE INDEX ON reg_norm(n);
+CREATE INDEX ON shop_norm(n);
+ANALYZE reg_norm; ANALYZE shop_norm;
+\echo '=== SUMMARY (staged vs existing) ==='
+SELECT s.source,
+  count(*) AS staged,
+  count(*) FILTER (WHERE EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm)) AS in_registry,
+  count(*) FILTER (WHERE EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) AS in_shopify,
+  count(*) FILTER (WHERE NOT EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm)
+                   AND NOT EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) AS net_new
+FROM staged s GROUP BY s.source ORDER BY s.source;
+-- detail of every match -> file for flagging
+\copy (SELECT s.source, s.mfr_sku, s.norm, (SELECT r.dw_sku FROM reg_norm r WHERE r.n=s.norm LIMIT 1) reg_dw_sku, (SELECT r.vendor_name FROM reg_norm r WHERE r.n=s.norm LIMIT 1) reg_vendor, (SELECT sh.dw_sku FROM shop_norm sh WHERE sh.n=s.norm LIMIT 1) shop_dw_sku, (SELECT sh.vendor FROM shop_norm sh WHERE sh.n=s.norm LIMIT 1) shop_vendor FROM staged s WHERE EXISTS(SELECT 1 FROM reg_norm r WHERE r.n=s.norm) OR EXISTS(SELECT 1 FROM shop_norm sh WHERE sh.n=s.norm)) TO '/tmp/dedup-matches.tsv'

← fc112db4 auto-save: 2026-06-30T06:59:02 (6 files) — pending-approval/ · back to Designer Wallcoverings · Session wrap: RESUME-abc123A handoff + run-viewer.sh relaunc 391d1145 →