EDW

• Before creating a temp table, delete the table if already exists (IF OBJECT_ID('TEMP.sg_ALL_TXN') IS NOT NULL DROP TABLE TEMP.sg_ALL_TXN)

• While creating table, use distribution (CREATE TABLE TEMP.sg_ALL_TXN WITH (DISTRIBUTION = HASH(CUSTOMER_ID),HEAP) AS DL)

• Use table with nolock (WITH(NOLOCK))

• Use outz filter for optimization (INNER JOIN FACT_LOAN FL WITH(NOLOCK) ON DL.APPL_ID = FL.APPL_ID AND DL.OUTZ = 20991231120000 AND FL.OUTZ = 20991231120000)

• Partition by: https://www.sqlshack.com/sql-partition-by-clause-overview/

• Different methods to remove duplicate rows: https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

• Date related: https://www.sqlshack.com/sql-convert-date-functions-and-formats/

• Update and take care of performance: https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/

• While loop in SQL: https://www.sqlshack.com/sql-while-loop-with-simple-examples/

• Join in SQL: https://www.sqlshack.com/learn-sql-join-multiple-tables/

• SQL Variables: https://www.sqlshack.com/sql-variables-basics-and-usage/

• with(NOLOCK) best practices: https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/

References

• https://www.altexsoft.com/blog/enterprise-data-warehouse-concepts/

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture