I'm using ADF for reading data from data lake and storing the data to a cosmos db. I'm figuring out whether I should use azure Cosmos DB Table API or SQL API (Core API). How do these two APIs, namely the Azure Cosmos DB (Table API) and Azure Cosmos DB (SQL API), compare in terms of cost and performance? Can we use SQL queries with where conditions for reading data from Azure Cosmos DB (Table API) & Azure Cosmos DB (SQL API)?
For cost and performance, This is entirely dependent on your use case. Are you able to elaborate on what you're trying to do?
You can't use SQL queries for the Table API, but you can use LINQ if you're developing in C#. If you're not, check out the documentation on the ways you can query data in the Table API:
https://learn.microsoft.com/en-us/rest/api/storageservices/querying-tables-and-entities https://learn.microsoft.com/en-us/azure/cosmos-db/tutorial-query-table