title: How can I efficiently prevent duplicated rows in my facts table?
body: <p>I have built a Data Factory pipeline which ETL the data from a Data Lake into a Datawarehouse. I chose the SCD type 1 for my dimensions. </p>
  
  <p>My pipeline contains the following activities:</p>
  
  <ol>
  <li>[Stored Procedure] Clear staging tables;</li>
  <li>[Stored Procedure] Get the timestamp of the last successful update;</li>
  <li>[U-SQL] Extract the dimension data from the filtered files (the ones that have been modified since the last successful update) in Azure Data Lake, transform it and output it in a csv file;</li>
  <li>[Copy Data] Load the csv into a SQL datawarehouse staging dimension table;</li>
  <li>[Stored Procedure] Merge the data from the staging table into the production table;</li>
  <li>[U-SQL] Extract the fact data from the files (the ones that have been modified since the last successful update) in Azure Data Lake, transform it and output it in a csv file;</li>
  <li>[Copy Data] Load the csv into a SQL datawarehouse fact table;</li>
  <li>[Stored Procedure] Update the timestamp of the successful update.</li>
  </ol>
  
  <p>The problem with this pipeline is that I end up with duplicated fact entries in my warehouse if the run the pipeline twice.</p>
  
  <p><strong>Question</strong> </p>
  
  <p>How can I efficiently prevent duplicated rows in my facts table, considering all <a href="https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features" rel="nofollow noreferrer">the unsupported features</a> in Azure SQL Data Warehouse?</p>
  
  <p><strong>Update</strong></p>
  
  <p>I have read another piece of information regarding the indexing (and the statistics) of a warehouse and how it must be rebuilt after an update.</p>
  
  <p>Considering that, the simplest thing that I thought of was to apply the same principle to the facts as the one I am using for the Dimensions. I can load all the new facts in a staging table, but then use an index on the fact table to include only the facts that do not exist (the facts can't be updated right now).</p>