The 'recordid' column is only an IDENTITY column in Table C.  Casting the 'recordid' to INT int Table D has no effect:

SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d

ON...

Table C:  recordid and promotion are INT

Table D:  recordid is VARCHAR(50)

Here's the results from SHOWPLAN_TEXT:

StmtText

SELECT c.* FROM Customers c INNER JOIN customers_from_dedupe d

ON c.recordid = d.recordid

WHERE promotion = 3465

(1 row(s) affected)

StmtText

 ...

Sorry....the 'promotion' column is only in table 'c' (customers).