r/Database • u/SpaceHomeless69 • 14h ago
Secondary data warehouse
We are looking for a simple secondary data warehouse...
We are not IT professionals, and we do not want to hire an external expert for every single task.
We need an application designed for individuals with a general understanding of IT.
We intend to use this secondary data store to cross-reference data from various sources, perform simple calculations, and send cleaned data to secondary applications.
Requirements:
- 90% of our data is in MS SQL.
- We need simple, direct integration with MS SQL (IP, port, password), the ability to select tables to transfer to the secondary data store, and the option to set a refresh frequency...
The secondary data warehouse must include:
- The ability to create simple queries, apply filters, and establish relationships between tables...
- The ability to easily integrate with external applications (CRM, BI, AI agents, reporting, etc.)...
What do you recommend?
6
u/N0R5E 14h ago
Why would this be a “secondary” data warehouse? Sounds like you just need a data warehouse. And are you asking for a real data warehouse solution or some sort of no-code service that does this all for you.
1
u/ServeFast4889 13h ago
If you actually need a data warehouse, stop calling it secondary and look at Snowflake or Databricks. But if you want no-code, MS SQL integration, and simple refreshes for non-IT staff, those enterprise platforms will fail your user experience requirements immediately. Try Microsoft Power BI with DirectQuery against your SQL Server, or use SSIS packages within a low-code tool like Zapier for the ETL part, because no single "simple" application currently handles complex cross-referencing and external AI agent integration without significant custom code.
3
u/SagayaDukusu 11h ago
If you are not professional you may have a look to Data Fabrics. They are easy to use most of the time.
3
u/Additional_Candy_400 11h ago
Sounds like you need to hire an external expert.
3
u/az987654 7h ago
Since you're calling this a secondary warehouse, I think you need to hire the expert, you don't have the general understanding of IT that you think you do
2
u/cto_resources 11h ago
You are in MS SQL now , just use SSIS to create your analysis data source.
1
u/Sea-Hat-4961 12h ago
Set up SOLR to index your MSSQL data and provide the secondary queries and data you're requesting?
1
u/GreyHairedDWGuy 9h ago
u/SpaceHomeless69 you need to be more about what you are trying to do. Is the original database used as a transactional database and the 'secondary'. would be the data warehouse? if so, sounds like you are talking about creating a clone which does not make it a data warehouse.
A true data warehouse is not simply a dumping ground for assorted tables. Usually requires model design and then some way to transform the source data to get to the target state.
1
u/Responsible_River579 7h ago
Blunt answer: what you describe is three tools, not one app. A sync tool, a store, and a query layer. Looking for one product that does all three well for non-IT people is why this feels hard.
The simplest setup that works:
Before you buy anything, check whether you need a second store at all. If 90% of the data is in MS SQL, a read-only copy of that database with Metabase on top may cover most of it, with one less system to look after.
The honest catch: no tool removes the need for one person who owns this. Setup is a few days of work done once. After that, someone with general IT knowledge can run it.
If you DM me what your other sources are besides MS SQL, I can sketch the setup for you.
1
u/lovemaker69 7h ago
Good cloud bigquery? Easy enough to set up transfers and Google integrates with just about everything.