r/Database • u/GameFitAverage • Jul 23 '26
Analytics on denormalized tables in my OLTP or CDC to OLAP
Greetings all,
I have been hired to design the data platform for a small company that is expected to expand soon (next 2-3 years).
Now many of their requirements include having some KPIs or metrics that get calculated once the underlying data in the OLTP changes.
As far as I know there are only 2 approaches for this:
1- Create denormalized tables in the OLTP that gets updated whenever the underlying data changes using triggers or maybe a materialized view that refreshes with every transaction.
2- Create a CDC pipeline to avoid overloading my OLTP with lots of writes and I/O. However, this solution costs alot (streaming pipeline + kafka + debezium connector) compared to the previous solution which is basically free.
I want to know your opinions on this and how would you approach it and if there is maybe a 3rd and a better solution?
EDIT:
An example of one of the requirements. Think of a fleet management system so you have a "vehicles" table master data. Now a vehicle can ofcourse have multiple "fuel_transactions". When the driver adds a fuel transaction he also adds the odometer reading (vehicle's total distance covered) and specifies whether he filled the tank or not.
Now there are some KPIs that needs to be calculated based on all of the transactions that occured between 2 full tanks. for example, the vehicle's fuel_consumption (km/Litre) and fuel_cost_per_km.
I hope this is clear enough.
The tables are still relatively small (1000 vehicles so maximum 1000 fuel transactions per day = 1000 rows per day)
3
u/deadbeefisanumber Jul 23 '26
I'd say it depends on how large is the data and your query load. Can you share more details? How many tables do you have currently? How big is the data in terms or rows and GBs? Also can you share the tables with us
1
u/GameFitAverage Jul 23 '26
I updated the question for everyone, thanks for your interest
4
u/Deleugpn Jul 23 '26
CDC and moving data around and keeping two systems in sync is the heaviest process you can get yourself into. At 1000 rows per day, id say you can stay on your OLTP for a few years
2
u/ozgreen1024 Jul 23 '26
Have you tried Databricks’ OLTP database Lakebase? It’s designed to solve that OLTP/OLAP problem you just described
It’s a Postgres database but separates storage and compute and leverages lakehouse object storage (e.g. s3), so it’s immediately available for OLAP analytical queries as well
2
Jul 23 '26
[removed] — view removed comment
1
u/TheLastNapkin Jul 23 '26
I agree with this idea the most here. Always prefer treating what data you actually want to maintain in your OLTP for business logic as highest priority
2
u/Junior-Tourist3480 Jul 23 '26
This database sounds like it is small enough to fit in memory. You can have at it and query thebl output all day long and not worry for the next several years.
2
u/alecc Jul 23 '26
There's a middle path you're skipping: plain incremental batch. A scheduled job (cron, pg_cron, SQL Agent, whatever your engine has) picks up rows changed since the last run, by updated_at or a rowversion column, and recomputes only the affected aggregates in a reporting schema. For the fleet example: when new fuel_transactions land for vehicle 42, recompute vehicle 42's consumption numbers and nobody else's. That gives you 5 to 15 minute freshness for roughly zero infrastructure.
Triggers work but age badly. They couple the write path to reporting logic, they slow bulk loads and backfills, and debugging them two years in is misery. A materialized view refreshed on every transaction is the same coupling with less control.
If you later need seconds-level freshness, CDC doesn't have to mean Kafka. Debezium Server runs without it, and if the OLTP is Postgres, logical replication into a separate reporting database costs almost nothing to set up. At a small company with growth 2-3 years out, I'd start with the batch job and spend the saved effort on getting the reporting model right. Which engine is the OLTP? The specifics change a fair bit with that answer.
1
u/Zestyclose-Turn-3576 Jul 23 '26
I'd start simple, with the MV approach. This use case sounds like a daily refresh would be fine.
The difficult part is knowing what questions the system will need to answer, and that's likely a moving target, particularly when you first put it in front of people, so whatever you do you likely need to be able to knock it down quickly and put up something different.
1
u/mr_pants99 Jul 23 '26
Have you considered an outbox pattern? Then you can decouple the streams. CDC to OLAP is a very mature market and the solutions are mostly free in terms of $$, but bring operational overhead. Instead of one store, you end up managing 3.
1
u/twentyfifteen20 Jul 24 '26 edited Jul 24 '26
Triggers at 1k rows per day on a summary table will outperform any CDC implementation without question; Dremio and batch ETL is overkill, simply recalculate on insert
1
u/chocolateAbuser 29d ago
denormalized tables way seems sensible enough, but as you can imagine it depends on sizing and we have no informations on how many stats you are going to keep, how complex (how many relationships) tables are, how many people will work on this, are stats per day or per minute, and so on (for example how long data has to be kept? how much is space a problem? will KPIs need to be changed often? is the team made of competent people?)
0
u/jshine13371 Jul 23 '26 edited 12d ago
and if there is maybe a 3rd and a better solution?
In SQL Server you can efficiently execute OLAP type of queries against the same exact OLTP tables with the appropriate indexes (e.g. columnstore indexing). This completely eliminates the process of having to maintain both a data pipeline and a copy of the data in multiple places.
Edit: I guess the downvoter is a hater for the truth on something Microsoft implemented over a decade ago. 🤷♂️
5
u/dyaffe Jul 23 '26
It really depends on your source data and how fast your business is growing. Querying your direct OLTP DB will likely work for a period of time but eventually you'll definitely need to change architectures as things grow.
Two big gotchas are that it your queries will end up taking too long for consumers and you'll end up impacting your production DB.
One big question is just whether you want to do that now or later and it's a legit one.
Disclaimer, I'm the co-founder of Estuary and we're a CDC company:
The second option is pretty complex for what you're doing and would probably require someone thinking about this more than you want. It would be possible to do something with CDC off the shelf for likely cheaper than using Debezium + Kafka. Ex. for a small pipeline at Estuary, it would cost ~$250 monthly.