Informatica IICS to BigQuery: pipeline patterns that survive the handoff.

Every IICS task that loads BigQuery should be safe to run twice. The standard way is a watermark table in BigQuery that records, per source, the last successfully loaded change marker and the run that wrote it. The mapping reads the watermark, extracts only newer rows, writes to a staging table, and merges into the target with a deterministic key. The watermark advances only after the merge commits.

A rerun after a failure then reloads the same window and produces the same result. Without this, a retried task duplicates rows and the duplicates are found weeks later in a finance report.

• Land each batch in a staging table named for the run, never directly in the target.
• Validate the batch before merging: row count against the extract, required columns present, keys unique, and control totals within tolerance.
• Merge with an explicit key and an explicit rule for late-arriving updates, and record how many rows were inserted, updated, and rejected.
• Keep rejected rows with a reason code in a quarantine table so data owners can fix the source instead of the pipeline patching around it.

A failed task should leave three things behind: a status row in a run-log table, an alert to a named group with the source and the step that failed, and no partial writes in the target. IICS task flows can branch on failure; use that branch to write the run-log status and to trigger the notification rather than leaving the failure in a console someone must remember to open.

The run-log table is the observability layer. From it, a simple Presentation view answers the questions people ask: did last night's load finish, how many rows moved, how long did it take, and is anything stuck. Publish that view in the same dashboard tool the business already uses. Reporting delay is a business metric, and when a modernization reduces it from a full day to a couple of hours, the run log is how that improvement is demonstrated.

• One page per pipeline: sources, schedule, watermark rule, merge key, expected volumes, and the person to call.
• The exact steps to rerun a failed window and to backfill a date range.
• The list of downstream consumers and how to tell them when a load is late.
• A recovery exercise performed once with the operating team before sign-off.

Ascendant Systems applies these patterns in fixed-scope implementations and in assessments of existing IICS estates that have grown fragile. The acceptance criteria are the patterns themselves: idempotent loads, validated merges, self-explaining failures, a readable run log, and a runbook the team has used.

Published 2026-09-25. Service: Data engineering. All insights.