Is it sufficient to use a Slowly Changing Dimension Write Strategy to insert new records and update existing records? Or do we need to use it in conjunction with Database Diff Processor for the purpose?

Slowly Changing Dimension is not Centerprise specific and would work the way it generally does in dimension modeling. Using the Slowly Changing Dimension strategy along would suffice to update or insert records in a destination table. You can specify the relevant SCD Type against the fields you want to insert or update in Centerprise.

Note: In Astera Centerprise 9.0, the functionality of the Slowly Changing Dimension write strategy is fulfilled by the Dimension Loader object in the Data Warehouse section of the toolbox.