I've been doing a lot of reading on Data Warehouse architecture as we are thinking about reforming our current setup feeding our BI. The 'maturity' of the Data Warehouse is quite low, despite being rapidly deployed.
I'm still confused over a "Data Warehouse" vs a "Data Mart."
What we currently do:
Go to source systems. Use an ETL process to insert "cleaned + transformed" data into data tables. We named these tables thinks like surveymonkey.FactSurvey. IE, the schemas are named after the data sources to aid in comprehensibility or 'what is this, where did it come from'. Tables are labeled as fact or dimension tables.
Currently we have 13-14 of these 'schema-sources' each with say 8-9 tables in one giant database.
How do we integrate data? Conformed dimensions, of course. Either dimensions are "cleaned/ conformed" via logic in the ETL scripts, or they are "cleaned" post-load in views atop the tables.
In my view, this giant database is a "data warehouse" - it feeds our business intelligence OLAP cubes.
What exactly is a data mart, and what is the purpose? I thought we were already following the Kimball approach -- fact and dimension tables. ETL from source to database. A series of 'views' denormalize the data to feed into the cubes. But isn't it the Kimball approach that says "well a data warehouse is simply all the data marts combined together" -- that sounds kind of like what we're doing. Dump all the transformed source data into one database. Views join table within source schema and between them (integrating business data).
I guess I'm fairly lost at what defines a "data mart" in the logic here.
Maybe I should post a separate question here, but then does one divide "data marts" via schema, or different databases?
I'm not sure the logic of dividing a Data Warehouse into separate Data Marts -- they all feed ONE BI application we are using. This BI application imports ALL data once a day into itself. If Finance uses their Finance data 10,000x more often than Marketing queries the Marketing table, that is completely irrelevant to our database structure as they aren't directly querying/ hitting our Data Warehouse whatsoever.
And what is the logic of separating Data Marts via Department or Function, generally? Is it a performance issue or a UX issue? If performance, then Department shouldn't be the divisor, IO load or usage should be ...
Does anyone know what the difference is here in terms of architecture and use cases? I've read a lot online, but honestly, it seems like most authors are waxing philosophical and not talking about specific tables or brass tacks. Maybe it would be easier if the literal architecture/ db/ schema structure were explained.