Populating a fact table with surrogate keys from dimension tables involves connecting the dimension tables to the fact table through foreign key relationships and inserting the corresponding surrogate keys into the fact table.

Let’s start with some constraints:

SQL
CREATE TABLE Dim.Customer (
    DimCustomer_SK INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DimCustomer_BK INT NOT NULL UNIQUE, -- e.g. CustomerNumber
    CustomerName VARCHAR(255)
);

"""
Notes about: UNIQUE

Enforcing a UNIQUE constraint on our BusinessKey (=BK) not only to ensure that we have a unique key.

The SQL engine will also create a unique index, this can speed up queries.

I omit that for our SurrogateKey (=SK), assuming that this will be an auto-increment field, then it is implicitly unique, because auto-increment fields generate a unique value for every record. In such a case, explictly addind UNIQUE would be redundant.
"""

Reinforce the relationships between the tables:

SQL
CREATE TABLE Dim.Customer (
    DimCustomer_SK INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DimCustomer_BK INT NOT NULL UNIQUE, -- e.g. CustomerNumber
    CustomerName VARCHAR(255)
);

CREATE TABLE Fact.Sales (
    DimCustomer_SK INT REFERENCES Dim.Customer(DimCustomer_SK),
    DimCustomer_BK INT, -- e.g. CustomerNumber
    Amount DECIMAL,
    Quantity INT
);

"""
DimCustomer_SK INT REFERENCES Dim.Customer(DimCustomer_SK) 

enforces that the values in Fact.Sales.DimCustomer_SK must match values in Dim.Customer.DimCustomer_SK. 

This means that for any value entered in Fact.Sales.DimCustomer_SK, there must be a corresponding value in the Dim.Customer.DimCustomer_SK.

This is used for ensuring validity of the data in the Fact.Sales.DimCustomer_SK column as it guarantees that an actual corresponding record exists in the Dim.Customer table. 

If you try to enter a DimCustomer_SK in Fact.Sales that does not exist in Dim.Customer.DimCustomer_SK, the DBMS would throw an error.
"""

Load DimTable:

SQL
INSERT INTO Dim.Customer (DimCustomer_BK, CustomerName)
SELECT 
    CI.CustomerNumber,   -- Assuming CustomerNumber is unique 
    CI.CustomerName      
FROM 
    CustomerInfo CI      -- Table from source system
WHERE 
    NOT EXISTS (         -- Check for duplicate
        SELECT 1
        FROM Dim.Customer DC
        WHERE DC.DimCustomer_BK = CI.CustomerNumber
    );

Note:
NOT EXISTS Subquery: This condition is crucial for dimension tables to avoid inserting duplicate entries. It checks if the customer already exists in the dimension table based on the business key (CustomerNumber). If not, it proceeds with the insert. This helps maintain the uniqueness of entries in the dimension table.

Load FactTable:

SQL
INSERT INTO Fact.Sales (DimCustomer_SK, DimCustomer_BK, Amount, Quantity)
SELECT 
    DC.DimCustomer_SK,   -- Surrogate key from dimension table
    T.CustomerNumber,    -- Business key from transactional data
    T.SaleAmount,        -- Sale amount from transactional data
    T.SaleQuantity       -- Sale quantity from transactional data
FROM 
    Transactions T
JOIN 
    Dim.Customer DC 
ON 
    T.CustomerNumber = DC.DimCustomer_BK;