I am loading data from SQL Server on prem into Snowflake. I am able to use the COPY INTO command (https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html) OR the Spark connector (https://docs.snowflake.com/en/user-guide/spark-connector-overview.html#internal-data-transfer) via Azure Data Factory. I cannot tell which one is more performant (in dev they run in similar amounts of time) or which is "better" technically. Snowflake seems to push the COPY INTO process, but that requires an extra storage step since the source is SQL Server.
Does anyone know which technology is more suited to a daily ELT process and why?
as you’ve found out for yourself, neither solution is more performant than the other, given your particular set of circumstances.
Neither solution is technically better (how would you define “better” anyway?) - they are just different ways of achieving the same result