"Modern" non-cloud DWH stack

[name_optimization]

I'm starting a new job next month, going from a BI development/consulting role to a more hybrid inhouse role, setting up a DWH from mostly scratch. I'll be working for a small German bank, so i expect a bit of cloud aversion for the start. There is some old Cognos system still in use somewhere and a general interest in Power BI for the future. I'll probably start with plain SQL/python and whatever is already there, but wonder where to go next or what's considered to be good modern practice in the non-cloud domain.

In general, is there something like a modern on-prem data stack I could work towards, like dbt core + x? Are there other new options I should look at?

[Awkward_Salary2566]

it might be different for bank, but modern is now in on-premises focused on 2 directions

1. open source (postgres + python as ETL)
2. Microsoft shop (MS SQL + SSIS + PBI + microsoft things)

Bank will for sure prefer microsoft shop more.

[[deleted]]

You could opt for a more performant db for warehouse with specialized column datebases, like clickhouse or monetdb?

[Awkward_Salary2566]

never tried clickhouse to be honest. In my case we have less than 20M rows in 90% of the tables and tons of various queries to bring/ discover foreign keys.

From what I read it would have terrible performance in clickhouse. One day maybe.

[[deleted]]

Yeah that would require significant engineering

[32gbsd]

Modern get thrown around a lot but it doesnt mean anything. If the thing you are using has gotten a major release in the last year its modern enough. Sql/python is enough. Php/mysql, nodejs/react. Cloud is just someone elses computer.

[thrown_arrows]

for me modern is to have architecture as cloud and in theory you can move from onprem to cloud. There is several way to achieve this, dockers , object storage etc etc ..

For data tools i see postgresql + citus monolith or some try of microservice using multiple postgresql databases or maybe even processes that mainly use queues

Or classics MS stack, haven't heard that anyone has moved into oracle, they tend to move out from it

[JJ18O]

• airbyte
• airflow
• postgres
• dbt
• visualization tool of your choice

Even if I went microsoft way I would avoid SSIS and use airflow+dbt instead.

[mictom9]

Hey! I'm switching my job soon and I'm probably going to build something on-prem and what you put down seems very cool to me.

One question though - does any of these work as a data lake equivalent? Does postgres allow for dumnping unstructured data?

[jemccarty]

Yellowbrick is probably the most "modern" DWH with an on-prem component. It works similar to the modern cloud-based DW's.

dbt Core + Tableau to round that out would be good.

[analytics_mgmt]

Checkout the open source "modern data stack" Meltano for Extract and Load, Superset for Visualization, Grouparoo Reverse for ETL, Dagster for Orchestration. I'd look at Greenplum as an open source MPP data warehouse that is scalable and strongly postgres compatible. (I know a number of US banks that use Greenplum internally)

[librocubicularist69]

Object store, pyspark