Best Practices for Managing SQL Dialects in DBT Across Different Databases

Hello, dbt community!

I’m currently working on a project where I’m using Snowflake as my data warehouse and writing SQL queries in my dbt models based on Snowflake’s SQL dialect. However, I might need to switch to MS SQL Server (MSSQL) in the near future.

I understand that different databases have their own SQL dialects, so my Snowflake SQL queries may not work directly in MSSQL. My questions are:

  1. What’s the best approach to handle this transition in dbt? Will I need to manually update all my SQL queries when moving from Snowflake to MSSQL?
  2. Are there any dbt macros or features that can help abstract the differences between SQL dialects to avoid rewriting queries when changing databases?
  3. Has anyone else navigated this transition between databases in dbt, and what were your key challenges or solutions?

Looking forward to your insights