The number of nodes in each Snowflake warehouse is obfuscated but not really a secret
https://stackoverflow.com/questions/58973007/what-are-the-specifications-of-a-snowflake-server
one part states the server EC2 instance type as "warehouseServerType" : "c5d.2xlarge",
https://select.dev/posts/snowflake-warehouse-sizing
it’s fairly well known that on AWS each node (except for 5XL and 6XL warehouses) is a c5d.2xlarge EC2 instance, with 16GB of RAM and a 200GB SSD.
https://medium.com/snowflake/how-many-nodes-are-in-a-snowflake-virtual-warehouse-4452eed1979d
Each increase in warehouse size will double the number of nodes available to the query, starting with 1 node for an X-Small warehouse and scaling up to 512 nodes for a 6X-Large warehouse.
However they now have Graviton (ARM) in the mix
Snowflake reduced its carbon footprint per Snowflake virtual warehouse credit by an estimated 57% by transitioning workloads from x86 to Graviton-based instances.
You can compare the sizes used by Fivetran in their benchmark to see that the above is roughly correct. See How did we configure the warehouses?. https://www.fivetran.com/blog/warehouse-benchmark
With respect to Databricks SQL (data warehouse product), the Fivetran blog was already outdated when published and is very out of date now. Expect Databricks SQL to be at least as fast or faster than the others.