In a EDW environment, how can this query be optimized for Clustered Index Seek ?
It looks like you don't have an index that satisfies the the predicate and contains the columns needed for the select.
Neither index you listed includes the Record_Status field so the optimizer can't use them to get to the records it needs. Thus it does a clustered Index Scan (remember that the Clustered Index is the full table) and searches the entire table for all records with a Record_Status = 1.
If you notice on the SELECT operator in the plan there is a little yellow exclamation point. If you hover your mouse over the operator it shows a Warning at the bottom:
Missing Index Found. Right Click to view.
If you right click on the SELECT and choose Missing Index Details, the missing index is as follows:
USE [EDW_Integration] GO CREATE NONCLUSTERED INDEX [*name index here*] ON [dbo].[INT_Company_Item] ([Record_Status]) INCLUDE ([Company_Item_ID],[Company_Item_SK]) GO
Change the name of the index to something meaningful and create it on the database and you should be good to go.