# 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.

Source: https://datanature.ai/blog/oracle-sql-server-to-iceberg-ingestion-guide
Publisher: Dlytica Inc. (DataNature is a Dlytica company)
Updated: 2026-11-05

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?

| Source | Status | Notes |
|---|---|---|
| Oracle | Available | Needs a service name; supports `YYYYMMDD` text bookmarks; `NUMBER` lands as `decimal(38,10)` unless changed |
| Microsoft SQL Server | Available | Column-name case matters |
| MySQL | Available | Zero dates become `NULL` |
| PostgreSQL | Available | No special handling |
| SaaS, files, streaming, Finacle and others | Preview | Visible 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 type | How it works | Use it for |
|---|---|---|
| Full | Truncates and reloads the table each run | Small reference tables (branches, products, codes) |
| Incremental | Reads forward from a watermark in fixed windows | Large tables with a reliable change timestamp |
| Full → Incremental | Backfills history in chunks between two dates, then switches to incremental | Large transactional tables with years of history |
| Historical | One-off bounded load in time chunks; never moves the watermark | Filling 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](/platform/atlas) → Monitoring, job runs in the job detail view, and dbt runs in Atlas → Runs.

## Sources

- [DataNature documentation: ingestion (Connect)](https://docs.datanature.ai/docs/developers/data-management/data-engineering/ingestion)
- [DataNature documentation: jobs overview](https://docs.datanature.ai/docs/developers/data-management/data-engineering/datanature-jobs/jobs-overview)
- [DataNature documentation: limits and known issues](https://docs.datanature.ai/docs/developers/resources/limits-known-issues)

## How does DataNature Atlas keep this secure and compliant?

Governance is part of the architecture, not a separate tool bolted on later. [DataNature Atlas](/platform/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](/blog/datanature-atlas-governance-rbac-abac-pii-masking) or see [security and compliance](/security).
