Scheduled sync

Every night, copy the orders and customers tables of the operational database into the reporting database, then refresh the aggregates the dashboards read.

---

What you need

• Two SQL connectors: ops-db (source, read-only account) and reporting (target).
• On reporting, a write query daily_totals_refresh that rebuilds the aggregates (REFRESH MATERIALIZED VIEW …, or DELETE + INSERT … SELECT …).

---

The job

+ New job: id nightly-reporting-sync, tag etl, Schedule (cron) 0 2 * * *, Timezone Europe/Paris. Then three steps:

#	Type	Settings
1	sql_copy	Source ops-db · public · orders; target reporting · staging · orders; mode overwrite.
2	sql_copy	Same for customers.
3	sql_query	Connector reporting, query daily_totals_refresh.

overwrite writes into <table>__new and swaps it with the target in one transaction: the dashboards never see a half-filled table, and a failed copy leaves yesterday's data in place.

Type coercion

sql_copy applies the JD Edwards conversion rules by default (type_coercion = "jde", decimal_mode = "truncate"). Between two ordinary databases, set type_coercion = "none" in jobs.toml so that values are copied as they are.

jobs.toml

[[jobs]]
id = "nightly-reporting-sync"
schedule = "0 2 * * *"
timezone = "Europe/Paris"
tags = ["etl"]

[jobs.retry]
attempts = 3
backoff = "exponential"
base_seconds = 60

[[jobs.steps]]
type = "sql_copy"
name = "copy-orders"
source = { connector = "ops-db", schema = "public", table = "orders" }
target = { connector = "reporting", schema = "staging", table = "orders" }
mode = "overwrite"
type_coercion = "none"

[[jobs.steps]]
type = "sql_copy"
name = "copy-customers"
source = { connector = "ops-db", schema = "public", table = "customers" }
target = { connector = "reporting", schema = "staging", table = "customers" }
mode = "overwrite"
type_coercion = "none"

[[jobs.steps]]
type = "sql_query"
name = "refresh-totals"
connector = "reporting"
query = "daily_totals_refresh"

---

Be warned

In Liberty Next → Build → Notifications, a rule on A job event: job nightly-reporting-sync, events failure and missed, channel email, recipient role:ops, subject {JOB}: {STATE}, body {ERROR}. See Notifications.

---

Check it

▶ Run now, then open the run: each step shows its rows; DEBUG in Run with parameters shows the queries. Once it is right, leave the schedule on.