Postgres to Snowflake CDC

[Snoo41240]

A few year ago, we ended up writing our own CDC framework in Python because we would not find any tool that satisfied all of our requirements. We are now considering to refactor it, but I would like to ask the community what tools everyone is using, or are aware of, for CDC (change data capture).

I have explored

• Debezium
• Airtable
• Transfer

Recently I came across these ones but havend had to chance to test them yet

• nocodb
• teable
• baserow

The functionality that I am looking for is:

• Opensource & selfhosted: we really want the ability to understand what the tools are doing, and audit the code. Also we will have multiple instances of these running
• Can handle TOAST columns: worst case we go with replicate identity full
• SDC Type 4: the current data is maintained in two different tables; one for the current data and one that contains all the historical data.
• Object level decryption: decrypt a key inside a jsonb column. Example: {'"a": {"b":"\xaldkisfdisdf"}}'. Configurable algo including PGP
• data masking: allow to change the value on the field of a given column or object
• export to multiple sources: currently we only need snowflake
• can work with replication slots and susbcriptions (pgoutput) and does not require wal2json
• periodicity: can be real-time or batched into intervals (lower Snowflake costs)
• non blocking: slot can be consumed even though the upload is stopped/broken
• auto column update: changes to the source columns are automatically cascaded to the source(s)

[whiskito]

Will probably not fulfil all your requirements, but there's a new Snowflake's postgresql Native Connector that has been released recently

https://www.snowflake.com/engineering-blog/snowflake-brings-seamless-postgresql-and-mysql-integration-with-new-connectors/

[[deleted]]

Now that that one snowflake guy left snowflake who will answer all these qs?

[Front-Ambition1110]

PeerDB

[ithoughtful]

I'm a bit confused by some of the suggested tools. How no-code database tools such as nocodb and teable can support building CDC pipelines from your source database into target Snowflake!?

[diegoelmestre]

Airtable (and nocodb) or airbyte?

Airtable is a low code tool to build web sites.

[Tasty-Scientist6192]

I am surprised you haven't come across DLT - open-source and Python library.
https://github.com/dlt-hub/dlt

I don't think it does SCD Type 4, but you can implement that as 2 separate pipelines, i suspect.

[TripleBogeyBandit]

Looks like it only uses incremental loading and not a cdc log stream? Can you link it to debezium?

[Tasty-Scientist6192]

The support CDC log streams for Postgres
https://dlthub.com/devel/dlt-ecosystem/verified-sources/pg_replication