Lakehouse architecture

Oracle, SQL Server, MySQL and Postgres to Apache Iceberg: a practical ingestion guide

DataNature Connect extracts tables from Oracle, SQL Server, MySQL and PostgreSQL over JDBC and writes them as Apache Iceberg tables in a Bronze schema. Each table uses one of four load types: full, incremental on a watermark, full then incremental, or a one-off historical range. Good results depend on choosing the right bookmark column, partitioning only large tables, and reconciling row counts after backfills.

Published . Last updated

On this page

Most enterprise lakehouse programmes start with the same job: copy the core relational systems into open tables, reliably, every day. This guide covers how DataNature Connect does that for Oracle, SQL Server, MySQL and PostgreSQL, and the decisions that determine whether a pipeline stays correct over years of loads.

How does Connect move data from a database into Iceberg?

The path is: source database → JDBC extraction → ingestion runtime → Iceberg table at lakehouse.bronze_<destination>.<table>. Each deployed pipeline gets its own orchestrated workflow that decides which time window to read and when. Tables are read from configuration at each run, so adding a table takes effect on the next run without redeploying.

A run moves through fixed steps: initialize, prepare data, start connector, transfer data (one task per table), stop connector, finalize, complete. Final status is SUCCESS, FAILED or PARTIAL_FAIL.

Which sources are supported?

SourceStatusNotes
OracleAvailableNeeds a service name; supports YYYYMMDD text bookmarks; NUMBER lands as decimal(38,10) unless changed
Microsoft SQL ServerAvailableColumn-name case matters
MySQLAvailableZero dates become NULL
PostgreSQLAvailableNo special handling
SaaS, files, streaming, Finacle and othersPreviewVisible in the catalog but cannot be saved yet

Use a read-only database account, test the connection before saving, and agree a load window with the DBA for large tables.

Which load type should each table use?

Load typeHow it worksUse it for
FullTruncates and reloads the table each runSmall reference tables (branches, products, codes)
IncrementalReads forward from a watermark in fixed windowsLarge tables with a reliable change timestamp
Full → IncrementalBackfills history in chunks between two dates, then switches to incrementalLarge transactional tables with years of history
HistoricalOne-off bounded load in time chunks; never moves the watermarkFilling a gap or loading extra history later

The watermark only moves forward after a successful run; a failed run re-reads the same window. A window with no rows succeeds without skipping data that has not arrived yet.

How do you choose a good bookmark column?

The bookmark decides whether updates are captured. Pick a column that is set on insert, updated on every change, indexed on the source and never null for real rows, typically last_modified or updated_at.

Accepted types:

  • TIMESTAMP / DATETIME, including Oracle DATE and MySQL datetime (sub-day windows allowed)
  • DATE on PostgreSQL and SQL Server (day windows only)
  • Oracle text in YYYYMMDD format (day granularity; chunk and partition hours in multiples of 24)

Plain text, bare NUMBER and binary bookmarks are refused. If the bookmark is only set on insert, updates to existing rows will not be picked up; that is the main difference between watermark loads and log-based change data capture, which StreamHouse provides for hot-path tables.

When should you partition?

Only for large tables (tens of millions of rows or more), and set it before deploy because partitioning is fixed at table creation.

  • Month or year for large historical tables and backfills.
  • Day only for high-volume tables with small daily loads, never for multi-year backfills. Daily partitions on a long backfill have exhausted a 15 GB ingestion heap and produced many small files.
  • No partitioning for SQL Server tables with mixed-case partition columns; they currently land in a single null partition with no pruning.

How are source types handled?

  • Binary and LOB columns (BLOB, RAW, bytea, image, rowversion and similar) are dropped automatically; CLOB and NCLOB are kept as strings. To keep binary data, cast it in a source view.
  • All Bronze columns are nullable; source NOT NULL constraints are enforced later with dbt tests in Silver.
  • Column names are lowercased in Iceberg.
  • Numeric precision above 38 is unsupported; cast in a source view.
  • Leave "Use Avro logical types" on, or dates and decimals land as strings and repair tools stop working.

How do you schedule and orchestrate loads?

Pipelines never run on their own. Create a job in Data Engineering → Jobs:

  • Ingest only runs the pipeline on a schedule or on demand.
  • Ingest + Transform runs ingestion, then dbt, then an optional cube refresh, and takes over the pipeline's own schedule so data is not loaded twice.

Schedules support hourly to yearly presets or a cron expression with a time zone. Missed slots are not replayed. Each pipeline runs one at a time, and a reconciler settles run status every five minutes.

How do you know the data is complete?

A green run does not prove completeness. After any large backfill, compare row counts per month between Bronze and the source. Gaps fall into four classes: clean gaps (re-ingest and append), partial or duplicate ranges (delete and reload), self-healing windows (wait for the next run), and OK. Historical loads append without de-duplication, so never load overlapping ranges.

What patterns work in production?

  • Start with two or three tables and verify end to end before adding more.
  • Group tables by source schema or business domain, not one giant pipeline.
  • Name destinations after the business source (core_banking, crm), not the server.
  • Never let two pipelines write the same Bronze table.
  • Keep Bronze raw; clean, type and de-duplicate in dbt Silver.

Frequently asked questions

Does Connect use change data capture?

Connect uses watermark-based incremental loads, which suit scheduled batch. For log-based change data capture and second-level freshness, use StreamHouse, the real-time hot path: it captures inserts, updates and deletes from source logs and streams them through managed Kafka into the same Iceberg tables.

Can I add a table to a running pipeline?

Yes. Tables are read from configuration at each run, so a new table is picked up on the next run. Changing deploy-time settings such as partitioning or column types requires deleting and recreating the table.

Where do I see load status?

Pipeline status is in Atlas → Monitoring, job runs in the job detail view, and dbt runs in Atlas → Runs.

Sources

How does DataNature Atlas keep this secure and compliant?

Governance is part of the architecture, not a separate tool bolted on later. DataNature Atlas is the platform's governance module:

  • Knows your data: a catalog with automated PII detection and classification across Bronze, Silver and Gold.
  • Controls access: role-based grants plus attribute-based policies, row filters and column masks (null, redact, partial, hash, year only).
  • Governs AI: Voir agents are scoped principals; masks, row filters and PII guardrails apply to every tool call and answer.
  • Proves it: lineage from source to metric to decision, and an audit log of who or what accessed which data.
  • Stays local: Atlas runs inside your deployment, so policies, lineage and logs never leave your jurisdiction.

These controls support GDPR, PIPEDA, Quebec Law 25, CCPA, HIPAA and sector rules such as OSFI E-23. Read DataNature Atlas in depth or see security and compliance.

See it on your own data.

Book a demo

Demos and quotes are handled by Dlytica, the company behind DataNature.