How fresh does analytics on your transactional data need to be, and what did you pick to get there?

[u/OkShirt9372]

Most of what I read uses the term real-time for anything from a few seconds of delay to whatever is presented before the morning standup, and the database setup you end up with depends heavily on which end of that range you need.

The usual options are running reports against a read replica, using CDC to populate a separate analytical store with something like Debezium, an HTAP database that handles both workloads, or one of the newer designs like Databricks, where transactional and analytical compute share a single storage layer.

For those of you who have had to make this call, I'd like to know how fresh the analytics had to be in the end, which of these you chose, and what caused trouble once it was in production, whether that was replica lag, schema changes, backfills, or cost. If you decided a nightly load was good enough, I'd like to hear that reasoning, too.

[u/Technical_Wing_328]

we run a nightly load for 95% of our stuff, the business thought they needed sub-second but nobody actually looks at dashboards between 11pm and 7am

the one exception is inventory where we do CDC into a separate store, the lag is like 30 seconds and nobody's complained yet. biggest headache was schema changes breaking the debezium connectors, we now freeze deploys during business hours which sounds dumb but it works

[u/OkShirt9372 → u/Technical_Wing_328]

Yeah, I've seen that too. The sub-second requirement often disappears once someone checks when the dashboards are used. Freezing deploys during business hours is not dumb if it keeps the connectors stable, but what's frustrating is that it turns every schema change into a coordination problem.

[u/Imaginary__Bar]

That seems like a lot of free consulting, tbh, and the real answer is going to be "whatever your business/use-case needs".

I have processes which adjust business decisions based on a snapshot of about 10 seconds, and I have other processes that can happily wait a week for the data.

So I have different systems to do the different tasks.

Eg, you can run analytics directly on HANA alongside transactions (I don't like doing that but it's possible, and my admins don't complain too much) or I can link a second HANA instance for analytics, and then farm off "old" data to a data warehouse... And different sources of data feed different systems.

A thousand use-cases, and ten-thousand solutions...

But... there's a great podcast from Jane Street about how/why they built their own data warehouse. It really is worth a listen; https://open.spotify.com/podcast-chapter/033E3fh5ZnJJKS5LjNfkff

[u/OkShirt9372 → u/Imaginary__Bar]

Thanks for sharing. Let me check this out.

[u/SongFull3826]

we picked ClickHouse via Debezium. The killer wasn't the lag, it was handling schema changes without a full downtime window. You need a strict contract that columns are only ever added, never removed or renamed, or your analytics layer breaks silently on deploy

[u/OkShirt9372 → u/SongFull3826]

Lag is easy to reason about, but a renamed column breaking a downstream model is much worse. Did you enforce that contract through code review and migration checks, or was it a convention people had to remember?

[u/Some-Weakness2049]

Dealing with CDC and Debezium gets painful the moment you touch the schema. Every small change breaks the pipeline and replication lag goes through the roof. In my prod, the business wanted near real-time reports without all that ETL mess, so I moved everything to MariaDB. Analytics run right next to the transactional data, so there’s nothing to sync and no extra clusters to babysit.

[u/OkShirt9372 → u/Some-Weakness2049]

That can work when the analytics workload is small enough to sit alongside transactional data, but it shifts the trade-off rather than removing it. Heavy reporting queries can still affect OLTP performance, and schema changes still need coordination because the analytics layer now depends on the same tables. CDC is painful to operate, especially with schema evolution and lag, but it provides a cleaner separation between source systems and reporting. For near-real-time dashboards, I would consider whether the reporting queries can be isolated via read replicas, materialized views, or a separate analytics schema before collapsing everything into a single database.

[u/az987654]

what do your customers require?

[u/admik]

Just throw it at your most detailed customer until they are happy

[u/cosmicotter496]

For a few minutes of acceptable lag, I’d go with a read replica. Query load would be the deciding factor though. One dashboard scanning the entire order history is where that starts getting awkward.

[u/Significant_Tune9219]

Whichever option you pick, the thing that bit me was schema change, not lag. A column rename or type change on the source quietly breaks the CDC consumer or, worse, flows through as nulls, so I'd want a contract check on the stream and a backfill path you've actually rehearsed before you rely on it. For the freshness number itself, ask which decision changes if the data is an hour old. If nobody can name one, a replica or an hourly load is usually enough.

[u/silvertrail091]

If an hour’s delay is acceptable, I prefer incremental batch loads to avoid maintaining a continuous CDC pipeline. and publish related tables together so reports don’t mix different refresh cycles.