sql server – Create a dimension table that changes slowly from Transaction

I receive transaction data from a file, it contains a history of repeated data. Currently, I am trying to find dimensions that change slowly in the table with Business Key in ProductId. There are many attributes: ProductTitle, Category, this is a sample table, they actually have around 10 more attributes. How do I create a slow change product dimension query that will be inserted into a table? Should I use the lead / lag function? Is there any better way? I tried to consult below.

Looking for a form of optimized performance, if I have 10 columns, I'm not sure if I want to use the group, partition by in 10 columns, etc.

Current repeated data:

create dbo.product table
(
ProductId int,
Product title varchar (55),
ProductCategory varchar (255),
Date dateload
)

Insert in dbo.Product
values
(1, & # 39 ;, & # 39; ABCD & # 39 ;, & # 39; 3/4/2018 & # 39;)
, (1, & # 39 ;, & # 39; ABCD & # 39 ;, & # 39; 3/5/2018 & # 39;)
, (1, & # 39 ;, & # 39; ABCD & # 39 ;, & # 39; 3/6/2018 & # 39;)
, (1, & # 39 ;, & # 39; XYZ & # 39 ;, & # 39; 3/7/2018 & # 39;)
, (1, & # 39 ;, & # 39; XYZ & # 39 ;, & # 39; 3/8/2018 & # 39;)
, (1, & # 39 ;, & # 39; XYZ & # 39 ;, & # 39; 3/9/2018 & # 39;)
, (1, & # 39; Table-Dinner & # 39 ;, & # 39; GHI & # 39 ;, & # 39; 3/10/2018 & # 39;)
, (1, & # 39; Table-Dinner & # 39 ;, & # 39; GHI & # 39 ;, & # 39; 3/11/2018 & # 39;)
.... more data with ProductId = 2,3,4, etc.

Expected performance:

create table #ProductHistory
(
Productid int,
Product title varchar (55),
ProductCategory varchar (255),
Start date and time
Weekend time
)

(1, & # 39 ;, & # 39; ABCD & # 39 ;, & # 39; 3/4/2018 & # 39 ;, & # 39; 3/6/2018 & # 39;)
, (1, & # 39 ;, & # 39; XYZ & # 39 ;, & # 39; 3/7/2018 & # 39 ;, & # 39; 3/9/2018 & # 39;)
, (1, & # 39; Table-Dinner & # 39 ;, & # 39; GHI & # 39 ;, & # 39; 3/10/2018 & # 39 ;, & # 39; 12/31/9999 & # 39;)

Attempt to consult
(seems to be inefficient, especially when you have 10 columns of attributes)

select
Product ID
, Title of the product
, Product category
, min (BegEffdatetime) as BeginDate
, Max (EndEffDatetime) as EndDate
since
(
select
Product ID
, Title of the product
, Product category
, LoadDate as BeginDate
, ISNULL (Lead (LoadDate) Over (Partition by productid order by LoadDate asc), & # 39; 12 / 31/9999 & # 39;) as EndDate
of the product
aa
group by ProductId, ProductTitle, Productcategory