4 ms·

this use case of postgres + CDC + iceberg feel like the wrong architecture. postgres is for relational data, ok CDC is meant to capture changes and process th

by slt2021 1y ago

this use case of postgres + CDC + iceberg feel like the wrong architecture. postgres is for relational data, ok CDC is meant to capture changes and process the changes only (in isolation from all previous changes), not to recover the snapshot of the original table by reimplementing the logic inside postgres of merge-on-read iceberg is columnar storage for large historical data for analytics, its not meant for relational data, and certainly not for realtime it looks like they need to use time-series oriented db, like timescale, influxdb, etc

nxm 1y ago
The goal is data replication into the data lake, and not in real-time. CDC is just a means to and end.
- slt2021 1y ago
  one problem with replicating mutation changes to the data lake is it may lose historical data. let's say e-shop customer changes his home address from WA state to CA, if you replicate this new address and delete old address in compaction process, all past transactions may now be associated with the new address, which can lead to wrong conclusions or distortions of past historical reports (WA sales from past month will not be attributed to CA sales)
datadrivenangel 1y ago
A table file format format like Iceberg is a relational tabular format. With a query engine that supports federation, we can write SELECT * FROM PG_Table or SELECT * FROM Iceberg_File just the same.
salmonellaeater 1y ago
It's the wrong architecture from a dependency management perspective. Directly importing a table into Iceberg allows analytics consumers to take dependencies on it. This means the Postgres database schema can't be changed without breaking those consumers. This is effectively a multi-tenant database with extra steps. This is not to say that this architecture isn't salvageable - if the only consumer of the Iceberg table copy is a e.g. view that downstream consumers must use, then it's easier to change the Postgres schema, as only the view must be adjusted. My experience with copying tables directly to a data warehouse using CDC, though, suggests it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time.
- code_biologist 1y ago
  Eh, as long as it isn't life or death, I think allowing direct consumption and explicitly agreeing that breakage is a consumer problem is better for most business use cases (less code, easier to maintain and evolve). If you make a breaking schema change and nobody complains, is it really breaking? I have spent way too much life maintaining consumer shield views and answering hairy schema translation questions for use cases so unimportant the downstream business user forgot they even had the view. Important downstream data consumers almost always have monitoring/alerting set up (if it's not important enough to have those, it's not important) and usually the business user cares about integrity enough to help data teams set up CI. Even in these cases, where the business user cares a lot, I've still found shield views to be of limited utility versus just letting the schema change hit the downstream system and letting them handle it as they see fit, as long as they're prepared for it. > it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time. IME, it feels wrong, but it mostly does end up saving time with few consequences. Worse is better.

nxm 1y ago

The goal is data replication into the data lake, and not in real-time. CDC is just a means to and end.

slt2021 1y ago
one problem with replicating mutation changes to the data lake is it may lose historical data. let's say e-shop customer changes his home address from WA state to CA, if you replicate this new address and delete old address in compaction process, all past transactions may now be associated with the new address, which can lead to wrong conclusions or distortions of past historical reports (WA sales from past month will not be attributed to CA sales)

datadrivenangel 1y ago

A table file format format like Iceberg is a relational tabular format. With a query engine that supports federation, we can write SELECT * FROM PG_Table or SELECT * FROM Iceberg_File just the same.

salmonellaeater 1y ago

It's the wrong architecture from a dependency management perspective. Directly importing a table into Iceberg allows analytics consumers to take dependencies on it. This means the Postgres database schema can't be changed without breaking those consumers. This is effectively a multi-tenant database with extra steps. This is not to say that this architecture isn't salvageable - if the only consumer of the Iceberg table copy is a e.g. view that downstream consumers must use, then it's easier to change the Postgres schema, as only the view must be adjusted. My experience with copying tables directly to a data warehouse using CDC, though, suggests it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time.

code_biologist 1y ago
Eh, as long as it isn't life or death, I think allowing direct consumption and explicitly agreeing that breakage is a consumer problem is better for most business use cases (less code, easier to maintain and evolve). If you make a breaking schema change and nobody complains, is it really breaking? I have spent way too much life maintaining consumer shield views and answering hairy schema translation questions for use cases so unimportant the downstream business user forgot they even had the view. Important downstream data consumers almost always have monitoring/alerting set up (if it's not important enough to have those, it's not important) and usually the business user cares about integrity enough to help data teams set up CI. Even in these cases, where the business user cares a lot, I've still found shield views to be of limited utility versus just letting the schema change hit the downstream system and letting them handle it as they see fit, as long as they're prepared for it. > it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time. IME, it feels wrong, but it mostly does end up saving time with few consequences. Worse is better.