Elastic Search - Is it the appropriate DB for an Analytics team?

Question

I am a member of an Analytics team that recently moved it's Data Warehouse into Elastic Search. The DW is accessed through Dremio.

However, I am having second thoughts regarding whether Elastic Search is the appropriate DB for an Analytics team that performs a lot of day-to-day Analytics. I would prefer we kept our DW in one of BigQuery/Snowflake/Redshift and use "dbt" tool for transforming data and writing it back into the DB.

I can't find a "dbt"-like tool to perform quick data transformations after reading from Elastic Search and Dremio is not mature enough tool for that. I would like to solicit your thoughts on Elastic Search and whether is an appropriate DB for day-to-day analytics.

I appreciate your responses.

Edit: I work at an online retailer. Our data is not "big data" in any sense. In the order of a few thousand orders per day. Most of our work is responding to inquiries from various teams/departments. Some of these questions go beyond a simple query. We have to build customized data marts that involve multiple steps in between. As a result, we need a tool that would allow us to transform data quickly and put the result set into a database. One such tool is "dbt" but it doesn't support Elastic Search. So the question is whether there is an appropriate tool for this job or Elastic Search is not appropriate for our use case.

Answer 1

Taking into account

Our data is not "big data" in any sense.

most likely ElasticSearch is not appropriate choice. Only reason to use ES is a lot of search-like queries with 'contains' filtering over text-type fields and only if dataset is too large for fast-enough handling of these queries by SQL-compatible DB.

It looks like PostgreSQL can do the job. If you're looking for columnar-DB for lighting-fast OLAP queries (aggregations) you can check open-source ClickHouse.

Finally, Dremio is not the only BI tool that can work with ElasticSearch (or PostgreSQL, ClickHouse etc). Some BI tool allows you to use 'painless' scripts for dimensions/measures and you can calculate a lot of things directly in ES queries.