<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Shubham Baldava on Medium]]></title>
        <description><![CDATA[Stories by Shubham Baldava on Medium]]></description>
        <link>https://medium.com/@shubham_7842?source=rss-c97deb4f35de------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*1ykGl5xoxZkGjOJC5SosGA.jpeg</url>
            <title>Stories by Shubham Baldava on Medium</title>
            <link>https://medium.com/@shubham_7842?source=rss-c97deb4f35de------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 22:18:37 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@shubham_7842/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MySQL to Databricks: Follow One UPDATE Through Four Different Pipelines]]></title>
            <link>https://medium.com/@shubham_7842/mysql-to-databricks-follow-one-update-through-four-different-pipelines-92fb0bbc652d?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/92fb0bbc652d</guid>
            <category><![CDATA[unity-catalog]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Thu, 19 Nov 2026 22:05:32 GMT</pubDate>
            <atom:updated>2026-11-19T22:05:32.186Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qUun-PTPrcLuCH-q.png" /></figure><p>Three orders in a MySQL table are marked delivered. It is a single UPDATE statement, and MySQL notes it in the binary log (binlog) and moves on. What happens to it next depends on which MySQL to Databricks pipeline sits downstream.</p><p>I like that statement as a test. Inserts are easy, and every pipeline carries them. An update makes the pipeline find a row it already delivered and replace it, and the four routes below do that in four different ways.</p><p>I ran the fourth route myself, so it comes with numbers and a list of limits. For the other three I describe where each one fits and where it breaks.</p><h3>Route 1: dump files, then COPY INTO or Auto Loader</h3><p>A script runs mysqldump or SELECT ... INTO OUTFILE and drops the files in a cloud bucket. A Databricks job loads them with <a href="https://docs.databricks.com/gcp/en/ingestion/cloud-object-storage/copy-into/">COPY INTO</a> or <a href="https://docs.databricks.com/gcp/en/ingestion/cloud-object-storage/auto-loader/">Auto Loader</a>.</p><p><strong>What happens to the update:</strong> nothing, until the next export. Then the three orders arrive inside a full reload, or through a merge that you wrote yourself.</p><p><strong>Where it fits:</strong> a one-time migration, and tables where hours to daily freshness is fine.</p><p><strong>Where it breaks:</strong> on anything that changes during the day. You also keep three parts alive: the export script, the bucket and the Databricks job.</p><h3>Route 2: Debezium, Kafka and Structured Streaming</h3><p>Debezium reads the binary log into Kafka topics. A Databricks job reads the topics with <a href="https://docs.databricks.com/gcp/en/connect/streaming/kafka">Structured Streaming</a> and writes the tables.</p><p><strong>What happens to the update:</strong> it reaches Kafka within seconds as an event. Turning that event into a changed row in a table is merge logic, and the merge logic is yours.</p><p><strong>Where it fits:</strong> Kafka already runs in your company, and seconds matter.</p><p><strong>Where it breaks:</strong> on the list of things to operate. That list is Kafka, Kafka Connect, Debezium and a Databricks job that has to keep running.</p><h3>Route 3: Lakeflow Connect</h3><p><a href="https://docs.databricks.com/gcp/en/ingestion/lakeflow-connect/">Lakeflow Connect</a> is the ingestion service inside Databricks, and it lists MySQL among its managed change data capture (CDC) database connectors. It runs on Databricks serverless compute, so there is nothing to host.</p><p><strong>What happens to the update:</strong> Databricks applies it for you through CDC. The orders live in streaming tables governed by Unity Catalog.</p><p><strong>Where it fits:</strong> Databricks is the only reader, and you want Databricks to run the pipeline. For that case it is the simpler choice, and I would not argue anyone out of it.</p><p><strong>Where it stops being the obvious pick:</strong> when a second engine, such as DuckDB or open source Spark, needs the same orders. Writing the rows once in an open format avoids a second copy at that point.</p><h3>Route 4: the MySQL to Databricks path through open tables</h3><p>The fourth route writes each table once, as an Apache Iceberg table registered in Unity Catalog. Databricks sees a managed Iceberg table and queries it from the SQL editor or a notebook. Other engines reach the same files through Unity Catalog’s Iceberg representational state transfer (REST) endpoint.</p><p>I ran this with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0. The source was MySQL 8.4 and the workspace was on Google Cloud. Setup came down to three pieces:</p><ul><li><strong>MySQL:</strong> the binary log on in ROW format with binlog_row_metadata = FULL, and a user holding SELECT, REPLICATION SLAVE and REPLICATION CLIENT.</li><li><strong>Databricks:</strong> external data access enabled on the metastore, a catalog with managed storage, EXTERNAL USE SCHEMA granted to the writing principal, and a personal access token.</li><li><strong>OLake UI:</strong> a Docker Compose stack on localhost:8000 with a MySQL source, a Unity Catalog destination and one job.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VeL5RIyLLFTXe4VQ.png" /></figure><p>The job asks which engines will read the tables. I answered Databricks, DuckDB and Apache Spark, and Databricks narrows the delete formats to one: deletion vectors on Iceberg v3 tables. OLake Go put that format on both streams, and I chose nothing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*B86DxhQBQcob0TEQ.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>The first sync is the backfill.</strong> It saved the binlog position, copied 8 customers and 40 orders (48 records) in parallel chunks, and finished in 25 seconds with container start counted. No separate backfill job existed.</li><li><strong>Databricks could query straight away.</strong> I declared no external table and registered nothing. The schema was named mysql_to_databricks_shop, after the job and the source database.</li><li><strong>Then came the update.</strong> Along with 7 inserts and 2 customer updates, the three orders were set to delivered. The second sync read those 12 records, 563 bytes, from the binlog and took 22 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*tgZQlryHLdh4F5_T.png" /></figure><ul><li><strong>Here is what the update turned into.</strong> The orders table gained one data file with 8 rows: the 5 new orders and the new versions of the 3 updated ones. One deletion vector, a small bitmap attached to a data file, marked the 3 old positions as replaced. The equality delete count stayed at 0.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_Q72rxefJsRAjq_i.png" /></figure><ul><li><strong>Databricks showed the change on its next query, with no refresh.</strong> In customers, the person I had moved to Abu Dhabi was in Abu Dhabi. Each row carried an _op_type flag: r for snapshot rows, c for inserts and u for updates.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*26_Mbc8B8B2Vt7Fz.png" /></figure><ul><li><strong>DuckDB and Spark agreed.</strong> DuckDB 1.5.5 and Spark 4.0.1 with Iceberg 1.10.0 attached to the same catalog with the same token, and Databricks vended their storage credentials. An orders roll-up by status matched MySQL to the cent in all three engines: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.</li></ul><p>Writing the tables never touched a Databricks warehouse or cluster. Databricks compute was spent only on my queries, on a 2X-Small serverless SQL warehouse.</p><h3>Where route 4 stops</h3><ul><li><strong>It is yours to run.</strong> The whole thing is one Docker Compose stack, on your storage and your compute.</li><li><strong>Freshness is a schedule,</strong> down to one minute. If the update has to land within seconds, that is route 2.</li><li><strong>Databricks takes one delete format.</strong> A commit carrying equality deletes is rejected with Can not create a Path from a null string [ErrorCode: 2015], and a table written that way starts over from a full refresh.</li><li><strong>No primary key, no updates.</strong> Inserts still replicate, but my three orders would never have changed. Add a primary key or a unique index first.</li><li><strong>decimal becomes </strong><strong>DOUBLE.</strong> The roll-up above still matched, but round at query time when you need exact decimal arithmetic.</li><li><strong>The binlog is the replay window.</strong> MySQL deletes binlog files after binlog_expire_logs_seconds, and a job paused for longer needs a fresh snapshot. Seven days of binlog on a busy server can be tens of gigabytes.</li><li><strong>The tables are Iceberg, not Delta.</strong> Native Delta Lake features need a Delta table loaded downstream.</li><li><strong>The token runs out.</strong> Mine expired after seven days, and an expired one fails the next sync at the first catalog call.</li></ul><h3>How to choose a MySQL to Databricks route</h3><ul><li><strong>The table can be a day old, or you are migrating once:</strong> dump files, then COPY INTO or Auto Loader.</li><li><strong>The update must land within seconds, and Kafka is already running:</strong> Debezium, Kafka and Structured Streaming.</li><li><strong>Databricks is the only reader and should run the pipeline:</strong> Lakeflow Connect.</li><li><strong>Databricks reads the table today, and DuckDB or Spark reads it too:</strong> open tables in Unity Catalog, written once, for example by OLake Go.</li></ul><p>Go back to the three orders. Ask how soon their new status has to be visible, who is going to apply the change, and how many engines need to see it.</p><p>The first answer separates a dump from CDC. The second separates a Kafka stack from a tool that applies changes for you. The third separates tables one engine reads from tables several engines can.</p><p>If only Databricks will ever look at those orders, let Databricks run the ingestion. If a second engine is already asking, I would write the rows once in an open format.</p><p>The commands, the data type mapping and the troubleshooting notes from my run are in the <a href="https://olake.io/docs/integrations/mysql-to-databricks/">MySQL to Databricks guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=92fb0bbc652d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL to BigQuery: Datastream, Kafka, Load Jobs or Open Tables in Your Own Bucket]]></title>
            <link>https://medium.com/@shubham_7842/mysql-to-bigquery-datastream-kafka-load-jobs-or-open-tables-in-your-own-bucket-653c7bb5f13d?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/653c7bb5f13d</guid>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Thu, 19 Nov 2026 05:44:48 GMT</pubDate>
            <atom:updated>2026-11-19T05:44:48.562Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*my6bb9P8lcS1HlEG.png" /></figure><p>Getting MySQL to BigQuery is rarely blocked by the first copy. Any export can do that. What decides the pipeline is the second week: rows that change during the day, rows that get deleted, and a team that wants the same tables in Spark or a notebook.</p><p>The routes differ less in how fast they move rows than in where those rows end up. Some land in storage only BigQuery can read. One lands as files in your own bucket that BigQuery reads in place.</p><p>Here are five realistic routes, where each one fits, where each one breaks, and what happened when I ran the last one.</p><h3>1. Export and bq load</h3><p>mysqldump or SELECT ... INTO OUTFILE, a staging bucket, then bq load on a schedule.</p><p><strong>Where it fits:</strong> a one-time migration, or a small reference table that changes once a day.</p><p><strong>Where it breaks:</strong> it only knows full reloads. Freshness is hours to daily, and a row deleted in MySQL stays in BigQuery until the next full reload replaces the table.</p><h3>2. Datastream</h3><p><a href="https://docs.cloud.google.com/datastream/docs/overview">Datastream for MySQL</a> is Google’s managed change data capture (CDC) service. It reads the binlog and writes native BigQuery datasets, near real time by Google’s description.</p><p><strong>Where it fits:</strong> BigQuery is the only reader, and you would rather configure a connection profile and a stream than run anything.</p><p><strong>Where it breaks:</strong> the rows live in BigQuery storage, and BigQuery is the only engine that reads them. When Spark or DuckDB needs the same rows later, you are copying again.</p><h3>3. Debezium, Kafka and a BigQuery sink</h3><p>Debezium turns the binlog into Kafka topics, and a sink connector writes them to BigQuery tables.</p><p><strong>Where it fits:</strong> Kafka is already part of your platform, and changes need to arrive within seconds.</p><p><strong>Where it breaks:</strong> the operational load. Kafka, Kafka Connect, Debezium and a sink connector per table are four moving parts for what is, from the analyst’s side, one copy of two tables.</p><h3>4. Managed extract, load, transform (ELT)</h3><p>Fivetran, Estuary or Airbyte Cloud: point at MySQL, point at BigQuery, done.</p><p><strong>Where it fits:</strong> many sources under one bill, and nobody on the team wants to host a pipeline.</p><p><strong>Where it breaks:</strong> pricing per row, per GB or per connector, for a second copy inside BigQuery you then pay to store. Freshness is minutes, depending on the plan.</p><h3>5. Open tables in your own bucket</h3><p>The route most comparisons leave out is to stop loading BigQuery tables at all. Write the rows once as Apache Iceberg tables in a Cloud Storage bucket you own, register them in BigLake, Google Cloud’s Iceberg catalog, and let BigQuery read them in place. Spark and DuckDB can read the same files through the same catalog.</p><p>I ran this route with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) tool that reads the MySQL binlog. The setup comes down to three pieces:</p><ul><li><strong>MySQL 8.4</strong> with the binlog on, ROW format and binlog_row_metadata = FULL, plus a user with SELECT, REPLICATION SLAVE and REPLICATION CLIENT.</li><li><strong>Google Cloud:</strong> the BigLake API enabled, a bucket in us-central1, and a service account with BigLake Editor on the project and Storage Object User on the bucket. No catalog to create, since BigLake makes one named after the bucket on first contact.</li><li><strong>OLake UI</strong> from Docker Compose on localhost:8000, with a MySQL source, a BigLake destination and one job.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/770/0*iV6AZgIAKe8JC6KZ.png" /></figure><p>On the job I listed BigQuery, Spark and DuckDB as the engines that will read the tables. OLake Go then offered only the delete formats all three read, and I kept positional deletes. Deletion vectors were not offered, because BigQuery reads them only in preview.</p><p><strong>What the run showed:</strong></p><ul><li><strong>The snapshot</strong> copied 8 customers and 40 orders, 48 records, in 37 seconds, container start included. It recorded the binlog position before copying, so writes during the copy were not lost.</li><li><strong>BigQuery could query the tables at once</strong> by a four-part name, project, bucket, namespace and table. There was no dataset to create, no external table and no refresh.</li><li><strong>Twelve changes later,</strong> 7 inserts and 5 updates, the second sync read only those 12 records (563 bytes) and took 55 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mFWltnxMuZZDKrpF.png" /></figure><ul><li><strong>Each table got one new data file and one small positional delete file.</strong> For customers that was 4 rows in and 2 old versions retired, and the commit report in the sync log said exactly that.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kORaE0G5vSvaqhpN.png" /></figure><ul><li><strong>BigQuery showed the updates on the next query,</strong> one row per key: 10 customers, the tier and city changes applied, the new customers present.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Q-eLWZwQJGxIy2wm.png" /></figure><ul><li><strong>DuckDB 1.5.5 and Spark 4.0.1 returned the same rows,</strong> and an orders roll-up by status matched MySQL to the cent in all three engines.</li></ul><p><strong>Where it breaks:</strong></p><ul><li><strong>Speed per query.</strong> A catalog table is read from Parquet in the bucket every time. If every query has to run at native BigQuery speed, keep the open tables as the source and load a hot slice into a native table.</li><li><strong>Tables without a primary key.</strong> Inserts arrive, but updates and deletes have no row to match and are not applied.</li><li><strong>Decimals become doubles.</strong> decimal shows up as FLOAT64, so round at query time when exact decimal math matters.</li><li><strong>The binlog is your replay window.</strong> A job paused longer than binlog_expire_logs_seconds needs a fresh snapshot.</li><li><strong>Maintenance is yours.</strong> BigLake rejects a metadata.json above 1 MB, which a busy table reaches after a few thousand syncs, so snapshots need expiring and files need compacting.</li><li><strong>You run it.</strong> One Docker Compose stack, scheduled down to one minute, not sub-second streaming.</li></ul><h3>How to choose a MySQL to BigQuery route</h3><ul><li><strong>A one-off move, or a small table refreshed daily:</strong> export and bq load.</li><li><strong>BigQuery as the only reader, and a managed Google service preferred:</strong> Datastream.</li><li><strong>Changes in seconds, with Kafka already in production:</strong> Debezium and a BigQuery sink.</li><li><strong>Many sources, nothing to host, and budget for usage pricing:</strong> a managed ELT tool.</li><li><strong>Fresh MySQL data in BigQuery that Spark, DuckDB or a notebook can also read, in a bucket you own:</strong> open tables in BigLake, for example written by OLake Go.</li></ul><p>The deciding question is not which tool copies rows faster. It is whether anything besides BigQuery will ever read these tables. If the answer is no, take the shortest managed path; if it is yes or maybe, writing the data once in an open format is cheaper to decide now than to migrate to later.</p><p>The full setup, with the data type mapping, the DuckDB and Spark connections and the errors from the run, is in the <a href="https://olake.io/docs/integrations/mysql-to-bigquery/">MySQL to BigQuery guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=653c7bb5f13d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to DuckDB: Query It Live, Export a File, or Attach a Catalog]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-duckdb-query-it-live-export-a-file-or-attach-a-catalog-a523dc278714?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/a523dc278714</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[apache-polaris]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Tue, 17 Nov 2026 22:24:18 GMT</pubDate>
            <atom:updated>2026-11-17T22:24:18.660Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1sLGVXeC5eUrHdIY.png" /></figure><p>DuckDB can query a running Postgres database directly. That makes Postgres to DuckDB look like a problem with a one-line answer: load the postgres extension and write the query. For one analyst and one small table, that answer is right.</p><p>It gets shakier as more notebooks point at the production database, and as a second engine asks for the same rows. I count five realistic ways to do this, and I ran the fifth one recently. Below is where each one fits, where it breaks, and what that run showed.</p><h3>The postgres extension: no pipeline at all</h3><p>The <a href="https://duckdb.org/docs/stable/core_extensions/postgres/overview">DuckDB postgres extension</a> lets DuckDB log in to Postgres and read its tables. Nothing is copied. Each query reads the current row, live.</p><p><strong>It fits when:</strong> you need a one-off answer from a small table that your machine can reach directly. All it takes is a Postgres user DuckDB can log in with.</p><p><strong>It breaks when:</strong> the queries become heavy or frequent. Every one of them runs against the live database, so each notebook is one more client of production Postgres. Nothing is left behind for another engine to read, either.</p><h3>Dump and reload: a DuckDB file on a schedule</h3><p>pg_dump or COPY TO writes the tables out, and COPY loads them into a DuckDB file. A script and a scheduler do the work.</p><p><strong>It fits when:</strong> a snapshot from last night is fresh enough. For a small table, a script and a scheduler are hard to argue with.</p><p><strong>It breaks when:</strong> rows change during the day. This route does full reloads only, so freshness is hours to daily, and an update or a delete shows up with the next rebuild at the earliest. What you end up with is a file that somebody rebuilds and then copies around.</p><h3>Debezium, Kafka and a Parquet consumer</h3><p>Debezium reads the Postgres write-ahead log (WAL) into Kafka topics. A consumer turns those topics into Parquet files, and DuckDB reads the files.</p><p><strong>It fits when:</strong> Kafka is already part of your platform and changes have to land within seconds.</p><p><strong>It breaks when:</strong> you add up the parts. Kafka, Kafka Connect, Debezium and the consumer are all yours to operate. Updates and deletes only come out right through merge logic you write yourself.</p><h3>A managed ELT service, with a warehouse in the middle</h3><p>Hosted extract, load, transform (ELT) tools replicate Postgres into a warehouse, and DuckDB queries the warehouse.</p><p><strong>It fits when:</strong> you want nothing to host, and freshness in minutes is fine.</p><p><strong>It breaks when:</strong> the invoice arrives. These services price per row, per GB or per connector, and the warehouse tables are a second copy you pay to move and store. How updates and deletes are handled varies by tool.</p><h3>Open tables in a catalog that DuckDB attaches</h3><p>The fifth way writes the rows once, as Apache Iceberg tables in a bucket you own. A catalog holds the table pointers and the permissions. DuckDB attaches that catalog and queries the files in place, and other engines can do the same.</p><p>I ran it with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0. It reads the WAL through a replication slot, which is change data capture (CDC). The catalog was Apache Polaris 1.8.0, an open source Representational State Transfer (REST) catalog, with MinIO as the bucket.</p><p>Postgres 16, Polaris and MinIO ran in Docker on one laptop, and DuckDB 1.5.5 ran in a shell beside them. The setup came in three parts:</p><ul><li><strong>Postgres:</strong> wal_level = logical, a role with the REPLICATION attribute and SELECT on the tables, one publication and one pgoutput replication slot.</li><li><strong>Polaris:</strong> a catalog pointed at the bucket, plus a principal (a client id and a secret) holding CATALOG_MANAGE_CONTENT on it.</li><li><strong>OLake UI:</strong> a Docker Compose stack on localhost:8000, with a source form for the slot and the publication and a destination form for the Polaris address and the principal.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*Xr6cvRPwN5btEBa9.png" /></figure><p>On the job I listed DuckDB and Trino as the engines that would read the tables. OLake Go then offered the row-level delete formats both of them can read. The job ran with positional deletes, the format DuckDB documents.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5BZk7YZDkjTm9IUs.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>First sync.</strong> 8 customers and 40 orders, 48 records in all, were copied in 22 seconds including container start. The WAL position is recorded before the copy begins, so rows written during it are not lost.</li><li><strong>DuckDB setup.</strong> Two secrets and one ATTACH statement. Every table OLake Go had committed was listed, with no registration step and no load.</li><li><strong>Change sync.</strong> After 7 inserts and 5 updates in Postgres, the next run read just those 12 records, 594 bytes, from the slot. It took 12 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Wk2_aIZxyODVmJet.png" /></figure><ul><li><strong>Result in DuckDB.</strong> The next query showed the changes with no refresh. It returned 10 customers, one row per key, because DuckDB applies the delete files as it reads.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rsW7nZdhmLoosIay.png" /></figure><ul><li><strong>Other engines.</strong> ClickHouse 26.8 and Trino 484 read the same tables through the same Polaris endpoint and returned identical rows. An order roll-up by status matched Postgres to the cent in all three engines.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LjqrUBZ7oWj03uUM.png" /></figure><p>One detail belongs to the laptop setup. The store address Polaris vends resolves inside Docker but not on the laptop, so DuckDB got an S3 secret for MinIO and attached with credential vending off. On AWS S3 the vended address resolves everywhere and that step goes away.</p><h3>What the catalog route costs</h3><p>This route has trade-offs of its own, and some of them are specific to OLake Go.</p><ul><li><strong>You run it.</strong> The Compose stack, the Polaris server and the principal’s secret are yours to keep up and to rotate.</li><li><strong>Freshness is a schedule.</strong> Syncs can run as often as once a minute.</li><li><strong>Queries read Parquet over the network.</strong> For a table you hit all day, copy it into a local DuckDB table once and query that.</li><li><strong>Tables need a primary key.</strong> Without one, inserts replicate and updates and deletes are not applied, unless the table is set to REPLICA IDENTITY FULL.</li><li><strong>numeric becomes </strong><strong>DOUBLE.</strong> OLake Go stores decimals as doubles, so round at query time when exact decimal arithmetic matters.</li><li><strong>Equality deletes are undocumented in DuckDB.</strong> Keep the job on positional deletes or deletion vectors.</li><li><strong>The slot pins WAL.</strong> A job paused over a weekend on a busy server can hold tens of gigabytes on the Postgres side.</li><li><strong>Nothing compacts the tables.</strong> A CDC table gets a new small data file and delete file with each sync.</li><li><strong>DuckDB cannot write back.</strong> The attached catalog is read-only, and OLake Go is the writer.</li></ul><h3>How to choose a Postgres to DuckDB route</h3><ul><li><strong>One analyst, one small table, a one-off question:</strong> the postgres extension.</li><li><strong>A snapshot once a night is enough:</strong> COPY out of Postgres and COPY into a DuckDB file.</li><li><strong>Kafka is already running and seconds matter:</strong> Debezium with a Parquet consumer.</li><li><strong>Nothing to host, and usage pricing is in the budget:</strong> a managed ELT service with a warehouse.</li><li><strong>Fresh rows with production Postgres out of the query path, shared by DuckDB, ClickHouse, Trino or Spark, and the files in your bucket:</strong> Iceberg tables in a catalog such as Polaris, for example written by OLake Go.</li></ul><p>Before comparing tools, I would count the readers. If the answer is one person with a notebook, the extension or a nightly COPY is less to run than a catalog. If the answer is several notebooks, a job and a second engine, write the rows once and let each of them attach.</p><p>The commands from the run, the data type table and the troubleshooting notes are in the <a href="https://olake.io/docs/integrations/postgres-to-duckdb/">Postgres to DuckDB guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a523dc278714" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to ClickHouse: MergeTree Copies, or One Set of Tables Every Engine Reads]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-clickhouse-mergetree-copies-or-one-set-of-tables-every-engine-reads-4094c277dfef?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/4094c277dfef</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-polaris]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[clickhouse]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Sun, 15 Nov 2026 18:57:08 GMT</pubDate>
            <atom:updated>2026-11-15T18:57:08.018Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*l_hkETZ5n40YXvjq.png" /></figure><p>ClickHouse earns its speed inside MergeTree tables. So it is no surprise that nearly every Postgres to ClickHouse pipeline ends by filling one. The rows arrive, ClickHouse sorts and indexes them, and the dashboards are quick.</p><p>The price is easy to overlook. That copy belongs to ClickHouse, and no other engine can open it. When a DuckDB notebook or a Trino cluster wants the same orders, somebody builds a second pipeline and stores the rows a second time.</p><p>I recently ran a route that makes the opposite trade, and its numbers are further down. The four established paths come first, because for plenty of teams one of them is the right call.</p><h3>Path 1: ClickPipes for Postgres</h3><p><a href="https://clickhouse.com/docs/integrations/clickpipes/postgres">ClickPipes</a> is the change data capture (CDC) service inside ClickHouse Cloud. It follows Postgres through logical replication and applies updates and deletes into ReplacingMergeTree tables.</p><p><strong>Choose it when:</strong> you are already on ClickHouse Cloud, ClickHouse is the only reader, and you would rather host nothing.</p><p><strong>It stops fitting when:</strong> you run ClickHouse yourself, since ClickPipes exists only in the Cloud product and is billed per ClickPipe. It also stops fitting on the day another engine needs those rows, because they sit in native Cloud tables.</p><h3>Path 2: PeerDB on your own servers</h3><p><a href="https://docs.peerdb.io/">PeerDB</a> uses logical replication too, and it also writes ReplacingMergeTree tables. The difference is that it targets a ClickHouse you operate.</p><p><strong>Choose it when:</strong> you want CDC into native tables and ClickHouse Cloud is not an option.</p><p><strong>It stops fitting when:</strong> you count what has to stay up. Next to PeerDB itself there is Temporal, plus a Postgres database that holds PeerDB’s own state. The output is still something ClickHouse alone can read.</p><h3>Path 3: Debezium, Kafka and the ClickHouse Kafka engine</h3><p>Debezium turns the Postgres write-ahead log (WAL) into Kafka topics, and ClickHouse consumes those topics with its Kafka table engine.</p><p><strong>Choose it when:</strong> Kafka is already part of your platform and a change has to show up within seconds.</p><p><strong>It stops fitting when:</strong> Kafka is not there yet. You would stand up Kafka, Kafka Connect and Debezium, then add one materialized view for each table. Updates and deletes only work through merge logic that you write and keep correct.</p><h3>Path 4: a managed ELT service</h3><p>Fivetran, Estuary and Airbyte Cloud sell extract, load, transform (ELT) as a hosted service with ClickHouse as a destination.</p><p><strong>Choose it when:</strong> you have many sources, nobody to run pipelines, and freshness counted in minutes is acceptable.</p><p><strong>It stops fitting when:</strong> the volume climbs. These tools charge by the row, the gigabyte or the connector, and what you buy is a second copy that costs money to move and to keep. How an update or a delete is treated differs from one tool to the next.</p><h3>Path 5: open tables that ClickHouse attaches</h3><p>The fifth path never loads ClickHouse. The rows are written once as Apache Iceberg tables, the files go into a bucket you own, and a catalog keeps the pointers and the permissions. ClickHouse mounts that catalog as a database with its DataLakeCatalog engine and queries the files where they are.</p><p>I ran it with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0. The catalog was Apache Polaris 1.8.0, itself open source, in front of MinIO. Postgres 16 was the source and the ClickHouse 26.8 long-term support (LTS) release was the reader, all of it in Docker on one machine.</p><p>The setup had three parts:</p><ul><li><strong>Postgres:</strong> wal_level = logical, a role holding the REPLICATION attribute and SELECT on the tables, one publication and one pgoutput replication slot.</li><li><strong>Polaris:</strong> a catalog aimed at the bucket, and a principal (a client id with a secret) granted CATALOG_MANAGE_CONTENT on that catalog.</li><li><strong>OLake UI:</strong> a Docker Compose stack on localhost:8000, a source form with the slot and the publication, and a destination form with the Polaris address and the principal.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*OZsNlCKWYXvPZ1Tu.png" /></figure><p>On the job I named ClickHouse, DuckDB and Trino as the engines that would read the tables. OLake Go then settled on a row-level delete format that all three can open, which turned out to be equality deletes. I did not pick a format myself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*J0nMXZVEakmPdN9M.png" /></figure><p><strong>What I measured:</strong></p><ul><li><strong>First sync.</strong> 8 customers and 40 orders, 48 records in total, were copied in 14 seconds with the container start counted. The WAL position was saved before the copy began, so writes made during it were kept.</li><li><strong>ClickHouse setup.</strong> One setting, allow_database_iceberg, and a single CREATE DATABASE statement with the DataLakeCatalog engine. Both tables were listed at once, with no table definitions and no load step.</li><li><strong>Change sync.</strong> I made 7 inserts and 5 updates in Postgres. The next run pulled exactly those 12 records, 594 bytes, out of the replication slot and took 11 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*XYn0r7uk32BdAV8s.png" /></figure><ul><li><strong>Result in ClickHouse.</strong> The changes were visible on the following query, with no refresh in between. It returned 10 customers, a single row for each key, since ClickHouse applies the delete files as it reads.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wCoi2sE5PCOuLBg4.png" /></figure><ul><li><strong>Other engines.</strong> DuckDB 1.5.5 and Trino 484 attached the same Polaris endpoint and gave back identical rows. Order totals grouped by status agreed with Postgres down to the cent in each engine.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*jP9C2mN3ivwQou8q.png" /></figure><p>It did not go cleanly the first time. My opening ClickHouse query came back with a 404 even though the table list worked. The store address on the Polaris catalog had no trailing slash, and ClickHouse 26.8 turns that into a doubled object key.</p><h3>What path 5 costs you</h3><p>This path has a bill of its own, and part of it is specific to OLake Go.</p><ul><li><strong>You give up the primary index.</strong> A DataLakeCatalog query reads Parquet over the network. It will not keep up with a MergeTree table on a hot dashboard.</li><li><strong>You operate it.</strong> The Compose stack, the Polaris server and the principal’s secret are yours to run and to rotate.</li><li><strong>Syncs run on a schedule.</strong> One minute is the tightest interval, so changes do not stream continuously.</li><li><strong>Compaction is your job.</strong> Each sync leaves a small data file and a delete file per table, and nothing merges them for you.</li><li><strong>Keyless tables lose their updates.</strong> Without a primary key or REPLICA IDENTITY FULL, inserts replicate and updates and deletes are not applied.</li><li><strong>Decimals become doubles.</strong> Postgres numeric reaches ClickHouse as Float64. Round in the query whenever exact arithmetic matters.</li><li><strong>No deletion vectors on the 26.8 LTS release.</strong> It reads positional and equality deletes only.</li><li><strong>The slot holds WAL.</strong> Pause a job for a weekend on a busy server and tens of gigabytes of log can pile up on the Postgres side.</li><li><strong>ClickHouse cannot write back.</strong> The attached database is read-only.</li></ul><h3>How to choose a Postgres to ClickHouse path</h3><ul><li><strong>ClickHouse Cloud, a single reader, MergeTree speed wanted:</strong> ClickPipes.</li><li><strong>Self-hosted ClickHouse, a single reader, and you accept running Temporal:</strong> PeerDB.</li><li><strong>Kafka already in place, and seconds matter:</strong> Debezium with the Kafka engine.</li><li><strong>Many sources, no operators, and budget for usage pricing:</strong> a managed ELT service.</li><li><strong>ClickHouse today, with DuckDB, Trino or Spark on the same rows and the files in your bucket:</strong> Iceberg tables in a catalog such as Polaris, for example written by OLake Go.</li></ul><p>The last choice does not shut out MergeTree. The Iceberg tables can stay the shared source, with a hot slice loaded into a MergeTree table for the one dashboard that needs the speed.</p><p>Before comparing tools, I would settle who reads the data. If the answer is ClickHouse and only ClickHouse, a native pipeline is shorter and quicker to query. If a second engine is likely, write the rows once where each of them can reach the files.</p><p>The commands from my run, the data type mapping and the troubleshooting notes are in the <a href="https://olake.io/docs/integrations/postgres-to-clickhouse/">Postgres to ClickHouse guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4094c277dfef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to Redshift: DMS, Zero-ETL, COPY, or Tables That Athena Can Read Too]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-redshift-dms-zero-etl-copy-or-tables-that-athena-can-read-too-f90149c04215?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/f90149c04215</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[redshift]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Sat, 14 Nov 2026 19:49:11 GMT</pubDate>
            <atom:updated>2026-11-14T19:49:11.087Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5Yh8bt8buP4sOUzz.png" /></figure><p>AWS will hand you two pipelines of its own from Postgres to Redshift before you look at a single outside tool. Add the scheduled COPY most teams start with and the hosted connector vendors, and that makes four established routes.</p><p>I recently ran a fifth. It writes the rows once into a bucket and lets Redshift read them there, next to Athena and anything else that speaks the same table format.</p><p>None of the five is wrong. Two facts about your setup do most of the sorting: where the Postgres lives, and whether Redshift will be the only thing that ever reads these rows.</p><h3>Route 1: export to S3 and COPY on a schedule</h3><p>A script dumps each table, a staging bucket holds the files, and a scheduler runs COPY into Redshift.</p><p><strong>Good fit:</strong> a one-time migration, or reference tables that are fine when they are a day old.</p><p><strong>Gives out when:</strong> someone needs the row that changed an hour ago. Freshness is hours to daily, and an update or a delete in Postgres only arrives through a full reload or a merge you write and then maintain.</p><h3>Route 2: AWS Database Migration Service</h3><p><a href="https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html">AWS Database Migration Service (DMS)</a> does change data capture (CDC) through Postgres logical replication. Inserts, updates and deletes are applied to native Redshift tables.</p><p><strong>Good fit:</strong> Postgres that is not Aurora, a team that wants an AWS service to do the copying, and Redshift as the single reader.</p><p><strong>Gives out when:</strong> you count the parts. There is a replication instance and a task to look after, plus an S3 staging bucket, and the result is still tables only Redshift can read.</p><h3>Route 3: the Aurora zero-ETL integration</h3><p>ETL is extract, transform, load, and the <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.html">Aurora PostgreSQL zero-ETL integration</a> removes the pipeline from your side altogether. Changes land in native Redshift tables within seconds, updates and deletes included.</p><p><strong>Good fit:</strong> the source is Aurora PostgreSQL and Redshift is the only consumer. In that case it is the simpler choice, and nothing below should talk you out of it.</p><p><strong>Gives out when:</strong> either condition fails. The integration takes Aurora PostgreSQL as its source, and what it produces is readable by Redshift alone.</p><h3>Route 4: managed tools</h3><p>Fivetran, Estuary and Airbyte Cloud are hosted extract, load, transform (ELT) services with a Postgres connector and a Redshift destination.</p><p><strong>Good fit:</strong> many sources, a small team, and nobody who wants to host a pipeline. Freshness is minutes, depending on the plan.</p><p><strong>Gives out when:</strong> the invoice arrives. Pricing is per row, per GB or per connector, and what you buy is a second copy that you pay to move and to store. How updates and deletes are handled varies from tool to tool.</p><h3>Route 5: open tables in the Glue Data Catalog</h3><p>All four routes above end inside Redshift. This one ends in your own S3 bucket, as Apache Iceberg tables registered in the AWS Glue Data Catalog.</p><p>Redshift reaches them through an external schema, which is a single statement mapping a Glue database onto a schema name. Athena, Amazon Elastic MapReduce (EMR), Spark and DuckDB read the very same files through the same catalog.</p><p>I ran it with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0. The source was Postgres 16, and the reader was a Redshift Serverless workgroup at 8 Redshift Processing Units (RPU) in ap-southeast-1. The setup has three parts:</p><ul><li><strong>Postgres:</strong> wal_level = logical, a role with the REPLICATION attribute and SELECT on the tables, one publication and one pgoutput replication slot.</li><li><strong>AWS:</strong> one bucket, a scoped Identity and Access Management (IAM) user with Glue and S3 permissions for the writer, and an IAM role on the Redshift namespace that can read Glue and the bucket. No Glue database is created by hand.</li><li><strong>OLake UI:</strong> one Docker Compose stack on localhost:8000, a source form with the slot and publication, and a destination form with the S3 path, the region and the access key pair.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*dagwDnmtEbQjeg08.png" /></figure><p>One step was manual. Redshift is not in OLake Go’s target engine picker yet, so I listed Athena and DuckDB as the target engines and set the upsert type to Positional Deletes myself. That is the row-level delete format Redshift reads.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3EIWYNh54MyEnpjM.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>The first sync</strong> moved 8 customers and 40 orders, 48 records and 2.23 KB in all, in 19 seconds with the container start counted. The write-ahead log (WAL) position was saved before the copy began, so rows written during it were not dropped.</li><li><strong>Redshift needed one statement.</strong> A CREATE EXTERNAL SCHEMA ... FROM DATA CATALOG naming the Glue database postgres_to_redshift_shop_public made both tables queryable. No table was declared, and nothing was loaded.</li><li><strong>Then came 7 inserts and 5 updates in Postgres.</strong> The next sync pulled exactly those 12 records, 594 bytes, out of the replication slot and took 18 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ANxX_tdx38GM4A90.png" /></figure><ul><li><strong>The changes showed up on the following query,</strong> with no refresh in between. Redshift returned 10 customers, one row per key, because it applies the delete files as it reads.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zlkfsqPCQUU8Iiuo.png" /></figure><ul><li><strong>Athena and DuckDB 1.5.5 agreed.</strong> Both read the same Glue tables and returned identical rows. A roll-up of orders by status matched Postgres to the cent in all three engines.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZKF7Qrx1LuEp_Qw2.png" /></figure><h3>Where route 5 gives out</h3><p>This route has costs of its own, and a few belong to OLake Go specifically.</p><ul><li><strong>Queries are not native Redshift speed.</strong> An external schema is served by Redshift Spectrum, which reads Parquet from S3 every time.</li><li><strong>Somebody runs it.</strong> A single Docker Compose stack is small, but it is yours, and so is the access key you rotate.</li><li><strong>Freshness follows a schedule,</strong> as tight as one minute. It is not a continuous stream.</li><li><strong>The delete format is set by hand for now.</strong> Deletion vectors are not a way around it: Redshift reads them only on Serverless above 4 RPU or on RG-type clusters (<a href="https://docs.aws.amazon.com/redshift/latest/dg/iceberg-v3-features.html">Redshift Iceberg v3 features</a>), and Athena cannot read Iceberg v3 tables at all.</li><li><strong>A table without a primary key replicates inserts only,</strong> until you add a key or set REPLICA IDENTITY FULL.</li><li><strong>Decimals turn into doubles.</strong> A Postgres numeric column appears in Redshift as DOUBLE PRECISION, so round at query time when exact decimal arithmetic matters.</li><li><strong>Small files pile up.</strong> Every sync adds a data file and a delete file per table, and Glue has no built-in compaction for tables written by external engines.</li><li><strong>The slot holds WAL until it is read.</strong> A job paused over a weekend on a busy server can pin tens of gigabytes on the Postgres disk.</li><li><strong>Redshift cannot write to these tables.</strong> The external schema is read-only.</li></ul><h3>How to choose a Postgres to Redshift route</h3><ul><li><strong>One migration, or tables that can be a day old:</strong> export and COPY.</li><li><strong>Aurora PostgreSQL as the source, Redshift as the only reader:</strong> the zero-ETL integration.</li><li><strong>Any other Postgres, Redshift as the only reader, and a preference for AWS services:</strong> DMS.</li><li><strong>Many sources and no one to operate a pipeline, with budget for usage pricing:</strong> a managed ELT tool.</li><li><strong>Redshift now, and Athena, EMR, Spark or DuckDB on the same rows, with the files in your account:</strong> Iceberg tables on Glue, for example written by OLake Go.</li></ul><p>The last option does not rule out native tables. The Iceberg tables can stay the shared copy, with a hot slice loaded into a native Redshift table for the one dashboard that needs the speed.</p><p>What I would settle before picking a tool is the reader question. If Redshift is the only reader and always will be, take the shortest managed path your source allows. If a second engine is likely, write the data once where all of them can reach it.</p><p>The commands from my run, the full data type mapping and the troubleshooting notes are in the <a href="https://olake.io/docs/integrations/postgres-to-redshift/">Postgres to Redshift guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f90149c04215" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to Databricks: Who Runs the Pipeline, and Who Else Reads the Tables]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-databricks-who-runs-the-pipeline-and-who-else-reads-the-tables-67b9f6a9eea3?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/67b9f6a9eea3</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[unity-catalog]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Thu, 12 Nov 2026 22:42:36 GMT</pubDate>
            <atom:updated>2026-11-13T16:22:32.677Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*izOT33eiv0WBXary.png" /></figure><p>There is no single default way to get tables from Postgres to Databricks. There are at least four, and they all copy rows well enough on a good day.</p><p>What separates them rarely shows up on an architecture diagram. One part is who keeps the pipeline alive at night. The other is which engines can read the result once it lands.</p><p>The four routes below go from the one you script yourself to the one I ran. For that last one I have the numbers and the limits, and I give both.</p><h3>Route 1: export files, then COPY INTO or Auto Loader</h3><p>A script runs COPY on each table and writes the files to a cloud bucket. A Databricks job then loads them with <a href="https://docs.databricks.com/gcp/en/ingestion/cloud-object-storage/copy-into/">COPY INTO</a> or <a href="https://docs.databricks.com/gcp/en/ingestion/cloud-object-storage/auto-loader/">Auto Loader</a>.</p><p><strong>Where it fits:</strong> a one-time migration, or reference tables that are fine when they are a day old.</p><p><strong>Where it breaks:</strong> freshness is hours to daily. A row updated or deleted in Postgres only changes in Databricks through a full reload or a merge that you write and maintain. You also own three moving parts: the export script, the bucket and the job.</p><h3>Route 2: Debezium, Kafka and Structured Streaming</h3><p>Debezium reads the Postgres write-ahead log (WAL) into Kafka topics. A Databricks job reads those topics with <a href="https://docs.databricks.com/gcp/en/connect/streaming/kafka">Structured Streaming</a> and writes the tables.</p><p><strong>Where it fits:</strong> Kafka is already part of your platform, and changes have to show up within seconds.</p><p><strong>Where it breaks:</strong> on what you operate. The path is Kafka, Kafka Connect, Debezium and a running Databricks job. Updates and deletes do arrive, but applying them is merge logic you write yourself.</p><h3>Route 3: Lakeflow Connect</h3><p><a href="https://docs.databricks.com/gcp/en/ingestion/lakeflow-connect/">Lakeflow Connect</a> has a PostgreSQL connector that does change data capture (CDC) for you. It runs on Databricks serverless compute, so there is nothing to host. The rows land in streaming tables governed by Unity Catalog, with updates and deletes applied.</p><p><strong>Where it fits:</strong> Databricks is the only reader, and you want Databricks to run the pipeline. In that case it is the simpler choice, and I would pick it without a long debate.</p><p><strong>Where it breaks:</strong> it answers the question of who runs the pipeline, but not the question of who else reads the tables. The day a second engine needs the same rows, you are planning a second copy.</p><h3>Route 4: write open tables into Unity Catalog</h3><p>The fourth route writes the data once as Apache Iceberg tables, registered in Unity Catalog itself. Databricks treats them as managed Iceberg tables and queries them from the SQL editor or a notebook. Other engines reach the same files through Unity Catalog’s Iceberg representational state transfer (REST) endpoint.</p><p>I ran this with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0. The source was Postgres 16, and the workspace was on Google Cloud. The setup has three parts:</p><ul><li><strong>Postgres:</strong> wal_level = logical, a role with the REPLICATION attribute and SELECT on the tables, one publication and one pgoutput replication slot.</li><li><strong>Databricks:</strong> external data access turned on for the metastore, a catalog with managed storage, the EXTERNAL USE SCHEMA privilege for the writing principal, and a personal access token.</li><li><strong>OLake UI:</strong> one Docker Compose stack on localhost:8000, a source form holding the slot and publication, and a destination form holding the Unity Catalog endpoint, the catalog name and the token.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9g0h9JQZeLn6xXLZ.png" /></figure><p>One job setting carries most of the weight. I listed Databricks, DuckDB and Spark as the target query engines, and Databricks narrowed the delete formats to one: deletion vectors on Iceberg v3 tables. OLake Go applied that format to each stream on its own.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Tzs9gPkoKfDvJYEZ.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>The historical load</strong> moved 48 records, 8 customers and 40 orders, in 37 seconds with the container start counted. The WAL position was saved before the copy began, so nothing written during the copy was dropped and no separate backfill job existed.</li><li><strong>Databricks could query at once.</strong> There was no external table and no registration. The schema took its name from the job, the database and the source schema: postgres_to_databricks_shop_public.</li><li><strong>Then I made 7 inserts and 5 updates in Postgres.</strong> The next sync pulled exactly those 12 records, 594 bytes, off the replication slot and took 28 seconds.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*j7F54efv2w8b_h6B.png" /></figure><ul><li><strong>Each table got one data file and one deletion vector.</strong> For customers that was 4 rows in the file and 2 old row versions retired. The equality delete count stayed at 0, which matters on Databricks for a reason I come to below.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7gT9OLoblJydSqfm.png" /></figure><ul><li><strong>The changes showed up on the next query,</strong> with no refresh. A customer I had moved to Abu Dhabi was in Abu Dhabi, and the two new customers were there.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GbzzwJ32x6gVO_Cd.png" /></figure><ul><li><strong>Two more engines read the same tables.</strong> DuckDB 1.5.5 and Spark 4.0.1 with Iceberg 1.10.0 attached to Unity Catalog with a token and received vended storage credentials. Both returned the same 10 customers, and an orders roll-up by status matched Postgres to the cent in all three engines.</li></ul><p>No Kafka, no Spark job and no Databricks ingestion pipeline sat in that path. OLake Go does not use a Databricks warehouse to write, so Databricks compute was spent only on the queries.</p><h3>Where route 4 breaks</h3><p>It has limits, and a few of them are specific to Databricks.</p><ul><li><strong>You run it.</strong> One Docker Compose stack is small, but it is yours, on your storage and your compute.</li><li><strong>Freshness is a schedule,</strong> down to one minute. It is not a continuous stream.</li><li><strong>Databricks accepts one delete format.</strong> Under Unity Catalog it reads deletion vectors and rejects a commit that carries equality deletes, with Can not create a Path from a null string [ErrorCode: 2015]. A table written with another format has to start again from a full refresh.</li><li><strong>The tables are Iceberg, not Delta.</strong> If a pipeline needs native Delta Lake features on these rows, you load them into a Delta table downstream.</li><li><strong>Tables need a primary key.</strong> Without one, and without REPLICA IDENTITY FULL, inserts replicate and updates and deletes do not.</li><li><strong>Decimals become doubles.</strong> A Postgres numeric column shows up as DOUBLE, so round at query time when you need exact decimal arithmetic.</li><li><strong>The token expires.</strong> An expired personal access token fails the next sync at its first catalog call.</li><li><strong>The slot holds WAL.</strong> Pause the job for days and the slot holds days of WAL.</li></ul><p>One permission deserves its own warning. EXTERNAL USE SCHEMA is not part of ALL PRIVILEGES (<a href="https://docs.databricks.com/gcp/en/data-governance/unity-catalog/manage-privileges/privileges">Databricks: Unity Catalog privileges</a>), so even a catalog owner has to grant it explicitly before any outside engine gets a table.</p><h3>How to choose a Postgres to Databricks route</h3><ul><li><strong>A one-time move, or tables that can be a day old:</strong> export the files and load them with COPY INTO or Auto Loader.</li><li><strong>Changes within seconds, and Kafka is already running:</strong> Debezium, Kafka and Structured Streaming.</li><li><strong>Databricks is the only reader, and you want Databricks to run it:</strong> Lakeflow Connect.</li><li><strong>Databricks today, with DuckDB or Spark on the same rows, and no second copy:</strong> open tables in Unity Catalog, for example written by OLake Go.</li></ul><p>I would answer the two questions from the title in order. First, who do you want to be responsible for the pipeline: Databricks, a platform team that already runs Kafka, or you with a single container stack?</p><p>Then, will anything besides Databricks read these tables? If the honest answer is no, let Databricks run the ingestion. If it is yes, or “probably”, I would write the rows once in an open format instead of adding a second copy later.</p><p>Every command from my run, the full data type mapping and the troubleshooting notes are in the <a href="https://olake.io/docs/integrations/postgres-to-databricks/">Postgres to Databricks guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=67b9f6a9eea3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to BigQuery: Native Tables or Open Tables, and the Pipelines Behind Each]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-bigquery-native-tables-or-open-tables-and-the-pipelines-behind-each-8ffa02fe78f8?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/8ffa02fe78f8</guid>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 21:10:14 GMT</pubDate>
            <atom:updated>2026-11-13T16:17:16.828Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*nnZ46xpi2jEvjUMU.png" /></figure><p>Every Postgres to BigQuery pipeline ends in one of two places. Either the rows land in storage that BigQuery owns, or they land as files in a bucket that you own and BigQuery reads in place.</p><p>Tool comparisons usually open with latency. I think this fork decides more: who else can read the data, what you pay to keep it, and how much you operate.</p><p>I ran the second kind recently, and the results are further down. First come the four pipelines that land in native tables, because for plenty of teams one of them is the right call.</p><h3>Landing in native BigQuery tables</h3><p>Native tables are what BigQuery is tuned for. Queries against them are quicker than queries over files in a bucket, and nothing sits between the dashboard and the storage.</p><p>The trade is that BigQuery becomes the only reader. A second engine means a second copy.</p><h3>Export and bq load</h3><p>A script dumps the tables, a staging bucket holds the files, and a scheduler runs bq load.</p><p><strong>Where it fits:</strong> a single migration, or lookup tables that only need to be right once a day.</p><p><strong>Where it breaks:</strong> freshness is hours to daily. There are no updates and no deletes either, only full reloads or a merge that you write and then maintain.</p><h3>Datastream for BigQuery</h3><p><a href="https://docs.cloud.google.com/datastream/docs/overview">Datastream</a> is Google’s managed change data capture (CDC) service. You define a connection profile and a stream in the console and host nothing. Google describes the freshness as near real time, and updates and deletes are carried through.</p><p><strong>Where it fits:</strong> BigQuery is the only reader, and the team prefers a managed Google service. In that situation it is the simpler choice, and I would not talk anyone out of it.</p><p><strong>Where it breaks:</strong> the data lands in native BigQuery datasets, so nothing else reads it. The day Spark, DuckDB or a notebook needs the same rows, somebody is writing an export.</p><h3>Debezium, Kafka and a BigQuery sink connector</h3><p>Debezium turns the Postgres write-ahead log (WAL) into Kafka topics, and a sink connector writes those topics into BigQuery tables.</p><p><strong>Where it fits:</strong> Kafka is already part of the platform and changes have to arrive within seconds. The connector’s upsert mode takes care of updates and deletes.</p><p><strong>Where it breaks:</strong> on operations. The path is Kafka, Kafka Connect, Debezium and the sink connector, and the tables are only as current as the least healthy of the four.</p><h3>Managed tools: Fivetran, Estuary, Airbyte Cloud</h3><p>Hosted extract, load, transform (ELT) services with a Postgres connector and a BigQuery destination.</p><p><strong>Where it fits:</strong> many sources, a small team, and no wish to host a pipeline. Freshness is minutes, depending on the plan.</p><p><strong>Where it breaks:</strong> on the bill and on the copy. Pricing is per row, per GB or per connector, and the result is a second copy that you pay to move and to store. How updates and deletes are handled varies by tool.</p><h3>Landing in open tables in your own bucket</h3><p>The other branch writes the data once, as Apache Iceberg tables in Cloud Storage, and registers them in a BigLake catalog. BigLake is Google Cloud’s Iceberg representational state transfer (REST) catalog, and BigQuery treats it as a data source in the project.</p><p>That last part is what makes the route practical. There is no dataset to create and no external table to declare, and a table is queryable on the statement after it is committed.</p><p>I ran this with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) replication tool, at v1.1.0, against Postgres 16 and BigQuery in us-central1. The shape of the setup:</p><ul><li><strong>Postgres:</strong> wal_level = logical, a role with the REPLICATION attribute and SELECT on the tables, one publication and one pgoutput replication slot.</li><li><strong>Google Cloud:</strong> the BigLake API enabled, one bucket, and a service account with BigLake Editor on the project and Storage Object User on the bucket. There is no catalog to create, because BigLake makes one named after the bucket on first contact.</li><li><strong>OLake UI:</strong> one Docker Compose stack on localhost:8000, with a source form for the slot and publication and a destination form holding the BigLake endpoint, the bucket path and the service account JSON.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/0*HAM3R7dyRLN7h5Uy.png" /></figure><p>On the job I listed BigQuery, Spark and DuckDB as the target query engines. OLake Go then offered only the delete formats all three can read, and the run used positional deletes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_dYTmXjvNQBxYpli.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>The snapshot</strong> moved all 48 records, 8 customers plus 40 orders, in 35 seconds with the container start counted. The WAL position was saved before the copy began, so writes that happened during it were not dropped.</li><li><strong>BigQuery could query straight away.</strong> The table name has four parts: project, bucket, namespace and table. The namespace came from the job, the database and the schema: postgres_to_bigquery_shop_public.</li><li><strong>Then 7 inserts and 5 updates in Postgres.</strong> The next sync pulled exactly those 12 records, 594 bytes in total, and took 58 seconds. The bulk of that was the reader waiting on an idle slot, not moving data.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GDv6wZ4zb0xc6t9d.png" /></figure><ul><li><strong>Updates were visible on the following </strong><strong>SELECT,</strong> with no refresh step. BigQuery returned one row per key because it applies the positional deletes as it reads.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*u3KVMAPxvvSUdCsr.png" /></figure><ul><li><strong>Two more engines read the same files.</strong> DuckDB 1.5.5 and Spark 4.0.1 with Iceberg 1.10.0 attached to the same catalog and returned identical rows. An orders roll-up by status agreed with Postgres to the cent in all three.</li></ul><p>No export ran for any of that, and no second copy exists.</p><h3>Where the open-table route breaks</h3><p>It has real limits, and some of them are specific to OLake Go.</p><ul><li><strong>It is not native speed.</strong> A catalog table is read from Parquet in the bucket on every query. If a dashboard needs native performance, keep the Iceberg tables as the source and load the hot slice into a native table on a schedule.</li><li><strong>You run it.</strong> One Docker Compose stack is not much, but it is yours, along with the service account key you rotate.</li><li><strong>Freshness is a schedule,</strong> down to one minute. It is not a continuous stream.</li><li><strong>Tables need a primary key.</strong> Without one, inserts replicate but updates and deletes are not applied, unless you set REPLICA IDENTITY FULL.</li><li><strong>Decimals become doubles.</strong> A Postgres numeric column shows up as FLOAT64, so round at query time when exact decimal arithmetic matters. My order totals still matched to the cent.</li><li><strong>The tables need upkeep.</strong> Each sync adds a small data file and a delete file, and BigLake rejects a table’s metadata.json once it passes 1 MB. Snapshot expiry and compaction have to run on a schedule.</li><li><strong>The slot holds WAL.</strong> Postgres keeps the log until it is read, so a job paused over a weekend on a busy server can pin tens of gigabytes.</li><li><strong>Deletion vectors are out for now.</strong> BigQuery reads them only in preview, with a cap of 100,000 deletion vector entries per query (<a href="https://docs.cloud.google.com/lakehouse/docs/use-iceberg-v3-binary-deletion-vectors">Google’s page on them</a>).</li></ul><h3>How to choose a Postgres to BigQuery pipeline</h3><ul><li><strong>A single migration, or tables that are fine a day old:</strong> export and bq load.</li><li><strong>BigQuery is the only reader and you want Google to run it:</strong> Datastream.</li><li><strong>Changes within seconds, and Kafka is already there:</strong> Debezium with a sink connector.</li><li><strong>Many sources, nobody to run a pipeline, and room in the budget for usage pricing:</strong> a managed ELT tool.</li><li><strong>BigQuery today, and Spark, DuckDB, Trino or Flink on the same rows later, with the files in your bucket:</strong> open tables in a BigLake catalog, for example written by OLake Go.</li></ul><p>The question I would settle first is not about tools at all. Will BigQuery be the only thing that ever reads this data?</p><p>If yes, land it in native tables by whichever path your team can operate. If no, or if nobody can promise yes, write it once in an open format and let BigQuery be one reader among several.</p><p>Every command from my run, the data type mapping and the errors with their fixes are in the <a href="https://olake.io/docs/integrations/postgres-to-bigquery/">Postgres to BigQuery guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8ffa02fe78f8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL to Snowflake: How to Pick a Replication Path Before You Build One]]></title>
            <link>https://medium.com/@shubham_7842/mysql-to-snowflake-how-to-pick-a-replication-path-before-you-build-one-5a33a4859043?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/5a33a4859043</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 06:01:14 GMT</pubDate>
            <atom:updated>2026-11-13T16:19:48.716Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9rlAx0-TlTculMbX.png" /></figure><p>The hard part of MySQL to Snowflake replication is not the first copy. A dump and a COPY INTO handle that. The hard part is everything after: the row updated at noon, the order that was deleted, and the second team that wants the same tables in a different engine.</p><p>Three questions sort the options, and it helps to answer them before reading any vendor page:</p><ol><li>How fresh does the data need to be: daily, minutes or seconds?</li><li>Do updates and deletes in MySQL have to show up in Snowflake?</li><li>Will anything besides Snowflake ever read these tables?</li></ol><p>Below are five realistic paths, judged against those questions. The last one is the path I ran, with its numbers and its limits.</p><h3>Path 1: dump, upload, load</h3><p>mysqldump or SELECT ... INTO OUTFILE writes the files, and a scheduled COPY INTO loads them. You own the files and operate nothing beyond a script and a scheduler.</p><p><strong>Where it fits:</strong> a one-time migration, or reference tables where a daily refresh is enough.</p><p><strong>Where it breaks:</strong> on question 2. This path only does full reloads, so there is no such thing as an update or a delete, just the next complete copy. Freshness is hours to daily.</p><h3>Path 2: Debezium, Kafka and the Snowflake Kafka connector</h3><p>Debezium tails the binary log into Kafka topics, and the Snowflake Kafka connector loads those topics into tables. Changes arrive within seconds, and deletes come through with the connector’s tombstone handling.</p><p><strong>Where it fits:</strong> a company that already runs Kafka and has a real reason to want seconds.</p><p><strong>Where it breaks:</strong> on operations. The path is Kafka, Kafka Connect, Debezium and a schema registry, and each of them has to stay healthy for the tables to stay current.</p><h3>Path 3: a managed service</h3><p>Fivetran, Estuary and Airbyte Cloud sell MySQL change data capture (CDC) as a service. There is nothing to host, updates and deletes are handled, and freshness is in minutes, depending on the plan.</p><p><strong>Where it fits:</strong> many sources, a small team, and a budget that can absorb usage pricing.</p><p><strong>Where it breaks:</strong> on the bill, and on question 3. Pricing is per row, per GB or per connector. What lands is a second copy in Snowflake tables that you pay to move and to store.</p><h3>Path 4: Snowflake Openflow</h3><p>Snowflake’s <a href="https://docs.snowflake.com/en/user-guide/data-integration/openflow/connectors/mysql/about">Openflow connector for MySQL</a> reads the binlog and writes native Snowflake tables. Deletes arrive as soft deletes, alongside journal tables. It runs as an Openflow deployment in Snowpark Container Services or in your own AWS account, billed in Snowflake credits.</p><p><strong>Where it fits:</strong> Snowflake is the only reader, and you would like Snowflake to run the pipeline. In that case it is the simpler choice.</p><p><strong>Where it breaks:</strong> on question 3. The tables are native to Snowflake, so any other engine that needs the rows gets them through another copy.</p><h3>Path 5: write open tables once, into Snowflake’s own catalog</h3><p>This path changes what “loading Snowflake” means. Rather than loading rows into tables only Snowflake can open, a writer commits Apache Iceberg tables into Horizon, the catalog built into every Snowflake account. Snowflake manages them as its own, and other engines read the same files through the same catalog.</p><p>I ran it with <a href="https://github.com/datazip-inc/olake">OLake Go</a>, an open source (Apache 2.0) tool that reads the MySQL binlog directly, with no Kafka or Debezium in between. The setup had three parts:</p><ul><li><strong>MySQL 8.4</strong> with the binlog on in ROW format, binlog_row_metadata = FULL, and a user holding SELECT, REPLICATION SLAVE and REPLICATION CLIENT.</li><li><strong>A Snowflake database</strong> set to CATALOG = &#39;SNOWFLAKE&#39; and EXTERNAL_VOLUME = &#39;SNOWFLAKE_MANAGED&#39;, plus a role, a service user and a programmatic access token issued under a network policy. No bucket or IAM role on my side.</li><li><strong>OLake UI</strong> from its Docker Compose install, with one MySQL source, one Horizon destination and one job.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0HgEEiFRlmg17JhJ.png" /></figure><p>On the job I listed Snowflake, DuckDB and Apache Spark as the target query engines. From that list the tool resolved a delete format all three can read, which was positional deletes here. I never picked a format by hand.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GnYmEGqPG-0V-5jm.png" /></figure><p><strong>What the run showed:</strong></p><ul><li><strong>The first sync took 34 seconds,</strong> container start included, for 8 customers and 40 orders. The binlog position is saved before the copy begins, so writes made during the snapshot are replayed afterwards and there is no separate backfill job.</li><li><strong>The tables were queryable in Snowsight as soon as the sync committed.</strong> No CREATE ICEBERG TABLE, no external volume, no refresh. SHOW ICEBERG TABLES listed both as MANAGED on SNOWFLAKE_MANAGED storage.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*RwBKtdBmdNy_KDF9.png" /></figure><ul><li><strong>A change-only sync took 23 seconds.</strong> After 7 inserts and 5 updates in MySQL, the second sync read exactly those 12 records, 563 bytes, from the binlog. For customers that was one new data file with 4 rows and one small delete file retiring the 2 old row versions.</li><li><strong>Every row says how it arrived.</strong> An _op_type column holds r for snapshot rows, c for inserts, u for updates and d for deletes.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Q7htbPnFYBnGxu8y.png" /></figure><ul><li><strong>Two other engines read the same tables with no export.</strong> DuckDB 1.5.5 and Spark 3.5.6 with Iceberg 1.10.0 returned the same 10 customers as Snowflake. An orders roll-up by status matched MySQL to the cent in all three: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*J48xuQ4riNWzl3HJ.png" /></figure><p><strong>Where it breaks:</strong></p><ul><li><strong>You operate it.</strong> It is a single Docker Compose stack, but keeping it running is your job, not a vendor’s.</li><li><strong>Freshness is a schedule.</strong> Syncs can run as often as every minute, which is not sub-second streaming.</li><li><strong>decimal columns arrive as </strong><strong>FLOAT.</strong> The order totals above still matched, but round at query time when you need exact decimal arithmetic.</li><li><strong>Tables without a primary key only get inserts.</strong> Updates and deletes cannot be matched to a row and are not applied.</li><li><strong>The binlog is the replay window.</strong> MySQL deletes binlog files after binlog_expire_logs_seconds, and a job paused longer than that needs a fresh snapshot. Seven days of binlog on a busy server can be tens of gigabytes.</li><li><strong>The access token expires.</strong> Mine was issued for 30 days. An expired token fails the next sync at the catalog handshake, before any data moves.</li><li><strong>DuckDB cannot be the second engine on an Azure-hosted Snowflake account.</strong> It attaches REST catalogs backed by S3, S3 Tables or Google Cloud Storage only.</li><li><strong>Small files pile up.</strong> Snowflake expires old snapshots and compacts manifests on <a href="https://docs.snowflake.com/en/user-guide/tables-iceberg-manage">managed Iceberg tables</a>, but automatic data-file compaction is bundled only when Snowflake alone writes the table.</li></ul><h3>How to choose a MySQL to Snowflake path</h3><p>Go back to the three questions.</p><ul><li><strong>Daily is fine and deletes do not matter:</strong> dump and COPY INTO. Anything more is machinery you will not use.</li><li><strong>You need seconds and Kafka is already in the building:</strong> Debezium and the Snowflake Kafka connector.</li><li><strong>You have many sources, nobody to run a pipeline, and room for per-row or per-GB pricing:</strong> a managed ELT (extract, load, transform) service.</li><li><strong>Snowflake is the only reader, now and later:</strong> Openflow. Let Snowflake run it.</li><li><strong>Another engine reads the data, or might, or you want to own the files:</strong> open tables in Snowflake’s catalog, written once.</li></ul><p>Question 3 is the one teams tend to answer last, and it is the expensive one to get wrong. Freshness can be tuned after the fact by changing a schedule. Where the data lands, and who else can open it, is decided by the first design.</p><p>If Snowflake really is the only consumer, take the shortest path and do not look back. If a notebook, a Spark job or a second warehouse is already asking for the same rows, write them once in a format all of them can read.</p><p>The full setup, with every statement, the data type table and the troubleshooting list, is in the <a href="https://olake.io/docs/integrations/mysql-to-snowflake/">MySQL to Snowflake guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5a33a4859043" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres to Snowflake: 5 Ways to Move the Data, and Where Each One Breaks]]></title>
            <link>https://medium.com/@shubham_7842/postgres-to-snowflake-5-ways-to-move-the-data-and-where-each-one-breaks-01537657b529?source=rss-c97deb4f35de------2</link>
            <guid isPermaLink="false">https://medium.com/p/01537657b529</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <dc:creator><![CDATA[Shubham Baldava]]></dc:creator>
            <pubDate>Tue, 10 Nov 2026 09:43:40 GMT</pubDate>
            <atom:updated>2026-11-13T16:24:40.718Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7uO1q-SaC8LNTkJz.png" /></figure><p>Moving data from Postgres to Snowflake looks like a solved problem until you list what you actually need. Fresh data, not yesterday’s. Deletes that really disappear. A bill that doesn’t grow every time a second team wants the same tables.</p><p>There are five common ways to do it. Each one is the right answer for someone. Here is where each one fits, where it breaks, and the one I’d pick when Snowflake isn’t the only thing that will read the data.</p><h3>1. Export and COPY INTO</h3><p>Dump a table with COPY ... TO, push the file to S3, and run COPY INTO on a schedule.</p><p><strong>Where it fits:</strong> a one-time migration, or a small table refreshed nightly.</p><p><strong>Where it breaks:</strong> it can only do full reloads. A row deleted in Postgres stays in Snowflake until the next full reload, and freshness is however often your cron runs, usually hours. Every table needs its own script, and every schema change breaks one.</p><h3>2. Debezium, Kafka and the Snowflake Kafka connector</h3><p>Debezium reads the Postgres write-ahead log into Kafka topics, and the Snowflake Kafka connector loads them.</p><p><strong>Where it fits:</strong> you already run Kafka, and you need changes in seconds.</p><p><strong>Where it breaks:</strong> you now operate Kafka, Kafka Connect, Debezium and a schema registry. That is four systems to keep up when all you wanted was the tables in Snowflake.</p><h3>3. Managed ELT: Fivetran, Estuary, Airbyte Cloud</h3><p>Point-and-click CDC with nothing to host.</p><p><strong>Where it fits:</strong> teams that want many connectors under one bill and don’t want to run anything.</p><p><strong>Where it breaks:</strong> the bill. Pricing is per row, per GB or per connector, and the data lands as a second copy inside Snowflake that you pay to move and to store. Freshness depends on your plan, usually minutes.</p><h3>4. Snowflake Openflow</h3><p>Snowflake’s own CDC connector for Postgres, built on logical replication.</p><p><strong>Where it fits:</strong> Snowflake is the only consumer, and you want Snowflake to run the pipeline.</p><p><strong>Where it breaks:</strong> it is built for Snowflake as the destination. If Spark, DuckDB or another warehouse needs the same rows next quarter, you are back to copying.</p><h3>5. Write the data once in an open table format</h3><p>This is the option most comparisons skip. Instead of loading rows into tables only Snowflake can read, you write them once as open Apache Iceberg tables that Snowflake manages in its own catalog. Snowflake treats them as native managed tables. Every other engine can read the same files.</p><p>I ran this end to end with <a href="https://github.com/datazip-inc/olake">OLake</a>, an open source (Apache 2.0) Postgres CDC tool. The shape of it:</p><ul><li>Postgres with wal_level=logical, a replication user, one publication and one replication slot.</li><li>In Snowflake, a database set to Snowflake’s own catalog and storage (CATALOG = &#39;SNOWFLAKE&#39;, EXTERNAL_VOLUME = &#39;SNOWFLAKE_MANAGED&#39;), a role, a service user and a programmatic access token under a network policy.</li><li>OLake running from one Docker Compose file, a source form with the slot and publication, and a destination form pointing at Snowflake.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KWPg5At-IhX6__pT.png" /></figure><p>On the job, you list which engines will read the tables. OLake then picks an update and delete format that every listed engine can read. That one setting is what makes updates visible in Snowflake.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hQhKX_FhKRPLUZTs.png" /></figure><p><strong>What happened in the run:</strong></p><ul><li><strong>The first sync</strong> copied 8 customers and 40 orders in 41 seconds, container start included. It recorded the write-ahead log position before copying, so nothing written during the copy was lost.</li><li><strong>The tables were in Snowflake immediately,</strong> with no CREATE TABLE, no stage and no refresh. SHOW ICEBERG TABLES listed them as MANAGED.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iRYP7-XUFgxqsCK5.png" /></figure><ul><li><strong>12 changes later</strong> (7 inserts and 5 updates), the second sync read only those changes from the replication slot and finished in 23 seconds. Each row came back with an _op_type flag: c for new rows, u for updated ones. A deleted row comes back flagged d.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*DKzkB9YABeETqjvC.png" /></figure><ul><li><strong>The same tables were readable from DuckDB and Spark</strong> through the same catalog, with no export. All three engines returned the same rows, and an orders roll-up matched Postgres to the cent.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*RqmOzNTV2l_EuRTG.png" /></figure><p><strong>Where it breaks:</strong></p><ul><li><strong>You run it yourself.</strong> It’s one Docker Compose stack, but it’s yours to keep up.</li><li><strong>Freshness is a schedule, down to one minute,</strong> not sub-second streaming.</li><li><strong>numeric arrives as </strong><strong>FLOAT,</strong> so round at query time if you need exact cents.</li><li><strong>The replication slot holds WAL until it’s read.</strong> Set max_slot_wal_keep_size before you pause a pipeline for a week.</li></ul><h3>How to choose a Postgres to Snowflake method</h3><ul><li><strong>A one-off move of a few tables</strong>: Export and COPY INTO</li><li><strong>Changes in seconds, and Kafka is already running</strong>: Debezium and Kafka</li><li><strong>Many sources, nothing to run, and budget for per-row pricing</strong>: Managed ELT</li><li><strong>Snowflake as the only reader, run by Snowflake</strong>: Openflow</li><li><strong>Fresh data in Snowflake that other engines can also read, without paying for a second copy</strong>: Open tables, for example with OLake</li></ul><p>For most teams the real question isn’t “how do I get Postgres into Snowflake”. It’s “will anything other than Snowflake ever need this data?” If the answer is no, take the shortest path. If it’s yes, or you’re not sure, writing the data once in an open format is the cheaper decision to make now than later.</p><p>The full walkthrough, with every command, the data type mapping and the errors you’ll hit, is in the <a href="https://olake.io/docs/integrations/postgres-to-snowflake/">Postgres to Snowflake guide on olake.io</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=01537657b529" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>