Query endpoint on Azure sql or databricks?

[Ruby8376]

Hi 

Currently all data reauired resides in Az sql database. We have a project in which we need to query on demand this data in Salesforce data cloud to be further used for reporting in CRMA dashboard.

do we need to move this data from az sql to delta lake (databricks) and create query edpoint there then via streaming apis data it can be queried? Or can be done by keeping data in az sql?

[Ruby8376]

@-werners- @Anonymous @Retired_mod  please help

[-werners-]

It depends.  If Salesforce Data Cloud has a connector for AZ SQL (being a native one or odbc/jdbc), you can query directly.  MS also has something like OData.  AFAIK AZ SQL does not have a query API, only for DB-management purposes.
If all above is not possible, you need either to move the data somewhere else, which Salesforce can query (can be anything that is supported by Salesforce, so could be databricks/delta lake if that is supported, but also something else), or expose the AZ Sql data by another tool which does not copy the data but makes it available, something like Presto/Trino and other data fabric tools.

If you decide to move the data to databricks, a serverless sql is probably the answer.  If you want to use an API, there is a query api which can be used to run queries on databricks.