Ingesting data (Connect)

Data Engineering → Connect ("DataNature Connect") copies tables from operational databases into the lakehouse bronze layer as Iceberg tables.

Connect needs three tools enabled: the ingestion runtime, orchestration and the query engine. If a prerequisite banner appears, ask an admin to enable them under Applications.

How it works

Source DB ──(JDBC extract)──► Ingestion runtime ──► Iceberg table  lakehouse.bronze_<dest>.<table>
                                     ▲
                 Orchestration (one workflow per pipeline) decides what window to read, and when

• Each deployed pipeline gets its own workflow in orchestration. Its tables are read from configuration at run time, so adding or removing a table on a deployed pipeline takes effect on the next run, with no redeploy.
• Every run is broken into steps: Initialize run → Prepare data → Start connector → Transfer data (one per table) → Stop connector → Finalize → Complete.

Supported sources

Source	Status	Notes
Oracle	✅ Available	Connection needs a Service name. Supports YYYYMMDD text bookmarks.
Microsoft SQL Server	✅ Available	Watch column-name case. See Rules learned the hard way.
MySQL	✅ Available	Zero dates (0000-00-00) become NULL automatically
PostgreSQL	✅ Available
Everything else in Connectors (SaaS, files, streaming, Finacle, …)	🔜 Preview	Listed for roadmap visibility only. You can't save a connection.

Tables and views are both supported. Views get a view chip in the table picker. Materialized views are listed too.

Set up sources and destinations

1. Sources → New connection. Choose the database type and fill in host, port, database (or service name), user and password. Always click Test connection before saving.
   • Use a read-only source account.
   • Ingestion runs heavy SELECTs. Agree a load window with the source DBA for large tables.
2. Destinations. A destination is a lakehouse namespace with a friendly name.
   • The bronze schema is named after the destination, not the source schema. Destination railway → bronze_railway.<table>.
   • One destination per deployed pipeline. A second pipeline can't deploy into a namespace a deployed pipeline already owns (destination_namespace_in_use).

Create a pipeline (6-step wizard)

Pipelines → + New pipeline

Step	What you set	Tips
1. Basics	Pipeline Name, Description	Use a descriptive name like crm_orders_bronze. Names must be unique per connection.
2. Connection	Source connection	The SQL dialect is picked automatically
3. Schema	Source schema	If discovery returns nothing, type the schema name
4. Tables	Tables, and per table the load type, size and bookmark/window/range fields	See Load types.
5. Tuning	Fetch size, batch sizes, compression, concurrency, decimal defaults	Leave the defaults unless you know why. See Rules learned the hard way.
6. Review	Summary + Destination	A destination is required before deploy

Creation is all-or-nothing. If any table is invalid, nothing is created and the error names the table.

Deploy

Click Deploy (rocket icon) on a Draft pipeline. The progress steps are:

1. cluster health check
2. allocate port
3. render & upload template
4. create Iceberg tables
5. enable services
6. publish the workflow

Things to know about deploy:

• ⚠️ Deploy fixes the table shape. Partitioning, column types, excluded columns and tuning are applied when the Iceberg tables are created. Changing them later means dropping the table and redeploying. Get partitioning right before you deploy.
• "Deployed" does not mean "runnable yet". The workflow takes a minute or two to reach orchestration. If you trigger too early, you'll see "still being deployed".
• If deploy fails, use Retry deploy. Retrying is safe: it recovers a half-finished deploy.
• There is no Redeploy button. To change something deploy-time (partition spec, tuning), Delete (Force) the pipeline and recreate it. Run history is kept.

Load types

Set per table in step 4.

Wizard label	What each run does	Required fields	Use for
Full load	Truncates and reloads the whole table	none	Small reference tables (under a few million rows)
Incremental	Reads forward from a watermark by a fixed window	Bookmark column, Partition hours, Window days/hours, Start watermark	Append-heavy tables with a reliable timestamp or date column
Full → Incremental	First backfills history in chunks (Backfill start → end), then switches to incremental automatically	Bookmark column, backfill start/end, window	Large tables where you need history and ongoing loads (the usual choice)
Historical (date range)	One-off bounded range, split into time chunks. Never schedules, never moves a watermark.	Date column, Start date, End date, Chunk hours	Reloading or adding a specific period

Table size. Mark tables with tens of millions of rows or more as Large. Only Large tables expose Partition columns. Full load can't be Large.

How the watermark behaves

• The watermark only moves forward after a successful run. A failed run re-reads the same window next time. That is deliberate and safe.
• A run whose window finds no rows succeeds without moving the watermark past data that isn't there yet.
• Window days + hours can't both be 0.

Ranges are half-open and whole-day

• All ranges are [start, end). The end date is exclusive. To load all of 2025, set start 2025-01-01 and end 2026-01-01.
• Backfill and historical bounds must be midnight. The engine works in whole days.
• ⚠️ Never set an end date in the future. Days that haven't happened yet are recorded as loaded (empty).
• The Full → Incremental backfill range is fixed when the table is created. For more history later, use the table's Historical load dialog.

Historical loads append, with no de-duplication

⚠️ Loading a range that overlaps data already loaded duplicates every row in the overlap. The Historical load dialog shows Already loaded coverage and warns before you continue ("Ingest anyway (accept duplicates)" / "Pick a different range"). Check coverage first, every time.

Choosing a bookmark (watermark) column

Source column type	Behaviour
TIMESTAMP / DATETIME	Fully flexible. Windows can be sub-day.
Oracle DATE, MySQL datetime	Treated as timestamp (they carry a time part). The day lock doesn't apply.
DATE (Postgres / MSSQL)	Partition hours and window hours lock to 24 h / 0 h; only window days is editable
Text holding YYYYMMDD (Oracle only)	Tick "It stores a date as YYYYMMDD text — read it as a date". The wizard samples one value to check it. Day granularity is forced: chunk and partition hours must be multiples of 24. The bronze column is created as a real date.
Other text, bare NUMBER, binary	Refused

Choose a column that:

• is set on insert and updated on change, e.g. last_modified, not created_date, if rows get updated;
• is indexed on the source. Every run filters on it;
• is never NULL for real rows. NULL-bookmark rows are never picked up incrementally.

If your table is updated in place and has no reliable modified-time column, incremental won't capture updates. Use Full load (small tables) or plan a dbt dedup and merge in silver.

Partitioning bronze tables

Set in Edit table config → Partition columns + granularity (Large tables only) before deploy.

Choice	When
None (default)	Most tables. Simple, and no small-file problem.
Month	Large tables (tens of millions of rows or more) queried by date range
Year	Very long histories with sparse data
Day	⚠️ Only for very high-volume tables whose daily loads are small. Never on big historical or backfill loads.
Non-date column	Partitions by value. Only for low-cardinality columns (e.g. region).

Why not day()? A multi-year backfill writes each batch into hundreds of daily partitions at once. The writer holds an open file per partition. This has exhausted a 15 GB ingestion heap in minutes, crashing ingestion for everyone, and it leaves thousands of tiny files that make later queries slow. More memory doesn't fix it; coarser partitioning does.

Rules learned the hard way

1. Binary / LOB columns are dropped automatically.
   • Covers BLOB, RAW, LONG RAW, bytea, MySQL blob, SQL Server binary/image/rowversion.
   • The generated query selects named columns instead of *.
   • A table that is all binary, or whose bookmark is binary, is refused.
   • To keep one, expose it through a source view that casts it (e.g. to hex or base64 text).
   • CLOB/NCLOB are kept as strings.
2. Bronze columns are always nullable. Source NOT NULL constraints aren't trusted: legacy rows break them, and that used to crash loads. Enforce non-null in silver with dbt tests.
3. Column names become lower-case in Iceberg. Query bronze_x.orders.customer_id, not CustomerId.
4. ⚠️ Avoid CamelCase or upper-case partition columns. A known issue with mixed-case source columns (common on SQL Server, sometimes Oracle) means the partition spec doesn't bind. All rows land in one NULL partition. The data is correct, but partition pruning doesn't work. For those tables, prefer no partitioning. See Limits & Known Issues.
5. Numbers.
   • A bare Oracle NUMBER lands as decimal(38,10) (adjust in Tuning before deploy).
   • Declared precision above 38 isn't supported and may fail the load. Cast it in a source view.
   • Oracle DATE lands as timestamp.
6. Leave Tuning alone.
   • Output Batch Size must be 0. A value of 1 once turned a 350K-row table into billions of rows through a retry loop; the platform now forces 0.
   • Keep Concurrent Tasks = 1 unless the source DBA agrees. More tasks means more source load and a bigger blast radius on failure.
   • Keep Use Avro Logical Types = True. False writes dates and decimals as strings and disables the Delete + Reload repair.
7. One table, one owner. Don't let two pipelines write the same bronze table. Type drift between them has caused write failures.
8. ⚠️ Renaming a table on a deployed pipeline leaves the old Iceberg table behind (orphaned), silently. Clean it up or ask an admin.
9. Deleting and re-creating a pipeline or table with the same name: check the watermark and backfill state afterwards.
10. Views on Oracle: the parallel hint doesn't apply to views, so large views extract more slowly. A complex view may make the YYYYMMDD check time out ("unverified"). You can still tick the box if you're sure.

Running and scheduling

Pipelines don't run by themselves. A deployed pipeline with no schedule never loads data. "Why is my data stale?" is almost always this.

Schedule and trigger pipelines from Data Engineering → Jobs (see Jobs Overview):

• Ingest only job: sets the pipeline's own schedule, or use Trigger now.
• Ingest + Transform job: ingestion, then dbt, then optional cube refresh. It takes over the pipeline's own schedule so data isn't loaded twice.

What a trigger can tell you

Message	Meaning	Do
Triggered … for X	Run started	Watch Runs & logs
X is still being deployed — its DAG has not reached Airflow yet	Workflow not published or synced yet	Wait 1–2 min and retry. If it persists for over 10 min, contact your admin.
X has a DAG that failed to parse. Re-deploy it — waiting will not help.	Generated workflow is broken	Force delete and recreate; contact your admin if it recurs
X already has a run in flight	One run per pipeline at a time	Wait
Resumed N backfill request(s)	Pending backfill chunks were resumed	Normal
Nothing to run	No table has work due	Normal

Why one trigger makes two runs

For Full → Incremental tables that are still backfilling, one trigger produces:

• the pipeline's own run, whose Transfer data step shows no work due, and
• a separate backfill run that does the history chunks.

Run badges show backfill / scheduled / manual. This is expected.

Monitoring and verifying loads

• View runs & logs on the pipeline, or Atlas → Monitoring. Final statuses are SUCCESS, FAILED and PARTIAL_FAIL.
• A background reconciler settles runs every 5 minutes. A chunk sitting at submitted for a few minutes is not a hang. Don't re-trigger repeatedly.
• Backfill progress: the table chip shows queued / backfilling N/M / backfilled / failed. The N/M chunks count in the Historical load dialog is the source of truth.

Reconciliation

On a deployed pipeline, Reconciliation compares rows read from the source with rows landed, per batch.

Class	Meaning	Repair
ok / repaired	Matches	none
clean gap	A window landed nothing	Reingest (append)
partial / duplicate	Window partly landed or doubled	Delete + Reload (deletes the window, then reloads it)
self-healing / unsettled	Still in progress	Wait

⚠️ "Green run, wrong counts"

A successful run means the step finished. It doesn't guarantee your table is complete. After any large backfill, verify:

-- rows per month landed in bronze
SELECT date_trunc('month', <bookmark_col>) AS m, count(*)
FROM lakehouse.bronze_<dest>.<table>
GROUP BY 1 ORDER BY 1;

Compare this with the same query on the source. If they differ, use Reconciliation rather than re-running the whole backfill.

Good patterns

• Start small. Deploy with 2–3 tables, verify end to end, then add the rest. Tables added to a deployed pipeline take effect on the next run.
• Big history: Full → Incremental with monthly (or no) partitioning. Backfill one large table at a time.
• Group tables by source and domain. Use one pipeline per source schema or subject area, not one giant pipeline.
• Name destinations after the business source (crm, core_banking), not the server.
• Keep bronze raw. Do cleaning, typing, dedup and business logic in dbt silver.