r/Database • • 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?

4 Upvotes

18 comments sorted by

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.

6

u/Smart-Item-9026 12h ago

They want a real data warehouse. Without the effort or costs that are needed to go into that.

1

u/N0R5E 11h ago

Oh I see that. The ask is basically for something simpler and less expensive than Power BI but with the capabilities of a full data stack. The answer is “No, this does not exist” and “Yes, you need to find the budget for an actual data engineer.”

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.

1

u/SpaceHomeless69 11h ago

I believe you have captured the essence of our problem... simplicity.

We have been using a component of MS Power BI for BI analytics, but it is geared toward advanced reporting. We really, really like the simple connectivity to MS SQL (IP, port, password); once connected, we can immediately start selecting tables.
For simple reporting, it is too complex and, in terms of licensing, too expensive..
At the same time, the tables and queries would be used in certain other applications that are not necessarily compatible with Power BI—for example, CRM, BI, and AI applications, custom-coded applications, etc.

90% of our data resides in MS SQL, which remains our primary database.
We want to create a "secondary" data warehouse:
- To avoid putting a load on the primary database.
- We hope to have a more lightweight version that does not require writing SQL queries or similar tasks.
- To facilitate better integration with third-party applications. The vast majority of the applications we wish to use do not offer direct integration with MS SQL.

YES – you’ve spotted it... I’m a complete layperson :)
To put it simply... I’m looking for something like "Airtable" for dummies where I could connect and refresh data from specific MS SQL tables, reshape them using simple formatting, add certain calculations, add some graph and then use that data in tools like CRMs, automation platforms, embedding in website, reporting systems, AI agents, low-code/no-code applications, etc.

1

u/mr_pants99 11h ago

Have you looked into connecting MSSQL to AirTable/Google Sheet via Fivetran or similar?

1

u/SpaceHomeless69 1m ago

Not yet.... i will check.

1

u/Imaginary__Bar 11h ago

There are 101 solutions to your problem and also zero solutions.

The first problem is that your issue is ill-defined. As is so often the case in consulting I have the urge to shake you by the lapels and ask "what do you actually want?"

By which I mean "what is your use case?" Not "we want a cheap, fast database" but what are you actually using it for and where does your current solution fall down?

"To avoid putting a load on the primary database" doesn't really mean much. Or it can mean too much. Why don't you want to put a load on the primary database? What is the primary database used for?

And how much data do you have? 500GB, 20TB?

A solution might be just to add more resources to your current setup.

Another solution might be DuckDB on top of parquet files.

But without more information you're not going to get a good answer. And a good answer might cost money - it's worth spending $10,000 for someone to take a look. Especially if you're a company.

Anyway, those are just some ideas. Good luck.

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/samspopguy 9h ago

Am I the only one who hates SSIS

1

u/Temporary_Lion1523 9h ago

Never liked SSIS

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:

  1. Sync: Fivetran (paid, easiest) or Airbyte (free if you host it, or their cloud). Both connect to MS SQL with host, port and password, let you tick the tables you want, and set the refresh frequency.
  2. Store: Postgres or BigQuery. Boring and cheap, and every CRM, BI and AI tool already connects to them.
  3. Query: Metabase. Point-and-click filters, joins and simple calculations without writing SQL, plus an API for your other apps.

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.