title: Azure Synapse - Incremental Data Load
body: <p>We load data from on-prem database servers to <em>Azure Data Lake Storage Gen2</em> using <em>Azure Data Factory</em> and <em>Databricks</em> store them as parquet files. Every run, we get only get the new and modified data from last run and UPSERT into existing parquet files using databricks <em><strong>merge</strong></em> statement.</p>
  <p>Now we are trying to move this data from parquet files <em>Azure Synapse</em>. Ideally, I would like to do this.</p>
  <ul>
  <li>Read incremental load data into a external table. (CETAS or COPY
  INTO)</li>
  <li>Use above as staging table.</li>
  <li>Merge staging table with production       table.</li>
  </ul>
  <p>The problem is merge statement is not available in Azure Syanpse. Here is the solution Microsoft <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-as-select-azure-sql-data-warehouse?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&amp;bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&amp;view=azure-sqldw-latest#l-use-ctas-to-simplify-merge-statements" rel="nofollow noreferrer">suggests</a> for incremental load</p>
  <pre class="lang-sql prettyprint-override"><code>    CREATE TABLE dbo.[DimProduct_upsert]
      WITH
      (   DISTRIBUTION = HASH([ProductKey])
      ,   CLUSTERED INDEX ([ProductKey])
      )
      AS
      -- New rows and new versions of rows
      SELECT      s.[ProductKey]
      ,           s.[EnglishProductName]
      ,           s.[Color]
      FROM      dbo.[stg_DimProduct] AS s
      UNION ALL  
      -- Keep rows that are not being touched
      SELECT      p.[ProductKey]
      ,           p.[EnglishProductName]
      ,           p.[Color]
      FROM      dbo.[DimProduct] AS p
      WHERE NOT EXISTS
      (   SELECT  *
          FROM    [dbo].[stg_DimProduct] s
          WHERE   s.[ProductKey] = p.[ProductKey]
      )
      ;
      
      RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
      RENAME OBJECT dbo.[DimProduct_upsert]  TO [DimProduct];
  </code></pre>
  <p>Basically dropping and re-creating the production table with CTAS. Will work fine with small dimenstion tables, but i'm apprehensive about large fact tables with 100's of millions of rows with indexes. Any suggestions on what would be the best way to do incremental loads for really large fact tables. Thanks!</p>