Getting data in

Most data never needs to be loaded. A catalog connects Thunderduck to where the data already lives, and queries read it there. Copy data only when you want to own it in your lake: for speed, for cost, or to stop paying the source system.

Query in place

SELECT o.id, o.total, c.segment
FROM   sales_pg.public.orders  o
JOIN   bq_crm.crm.customers    c ON c.id = o.customer_id
WHERE  o.created_at >= current_date - 7;

Filters are pushed down into PostgreSQL, MySQL and BigQuery, so only matching rows cross the wire, along with GROUP BY, ORDER BY and LIMIT when the remote table is alone in its SELECT. A filter is pushed when it compares a column with constants:

current_date is evaluated once, in UTC, when the query is planned, and sent to the source as a plain date, so it means the same day on every source. For rolling windows on timestamp columns, compare against a date (created_at >= current_date - 1), which is pushed, rather than against now(), which is not.

Iceberg reads skip files using partition values and min/max statistics. ClickHouse catalogs are read-only.

Copy once

CREATE TABLE lake.default.orders AS
SELECT * FROM sales_pg.public.orders;

From files, create the table from the files' own schema, then load in batches, one folder per statement:

CREATE TABLE lake.default.events AS
SELECT * FROM read_parquet('raw_files/events/*.parquet') LIMIT 0;
 
INSERT INTO lake.default.events
SELECT * FROM read_parquet('raw_files/events/2026-01/*.parquet');

Every statement runs as its own job, sized from its own row estimate, so batches keep each job small and a failed batch is simply re-run. Run Compact from the LakeOps page after a large load.

For large BigQuery tables, export to Parquet first; see Migrating from BigQuery.

Keep it up to date

Thunderduck syncs on a schedule; there is no change-data-capture (CDC) stream yet. The pattern is incremental: on each run, copy only the rows newer than what you already have.

• One statement: a scheduled job.

  INSERT INTO lake.default.orders
  SELECT * FROM sales_pg.public.orders
  WHERE  created_at >= current_date - 1
    AND  created_at <  current_date;

• Several dependent steps, tests, freshness alerts: a pipeline, with dbt incremental models in your own Git repository.

Filter the source with constants or current_date arithmetic, as above (see the table in Query in place). A filter that depends on another table (WHERE id > (SELECT max(id) FROM lake…)) cannot be pushed down, so the source would be scanned in full. When you need a high-water mark, read it in a first statement and write it into the second as a literal.

Writing back to your own systems

PostgreSQL, MySQL, BigQuery and Iceberg catalogs are writable unless the catalog is set to read-only or your role grants only read. A scheduled INSERT is how you deliver results into your own database:

INSERT INTO reporting_pg.public.daily_revenue
SELECT current_date - 1 AS day, sum(total) AS revenue
FROM   lake.default.orders
WHERE  created_at >= current_date - 1 AND created_at < current_date;

Writing plain files (Parquet or CSV) into your own bucket is not available yet. To land data in your S3 as Parquet today, write it to an Iceberg table, which stores its data as Parquet files.