Reconciliation Policy

A Reconciliation policy compares data in two assets, a source and a sink, to confirm that data stays complete and accurate when it's copied, moved, or transformed. For example, you can confirm that every order in an operational database reached the data warehouse with the same values.

When to use a Reconciliation policy

Use a Reconciliation policy when you need to:

• Confirm that an ETL job loaded all records into the target system.
• Verify that a data migration to a new system is complete.
• Check that replication between a production database and an analytics database is up to date.
• Validate that aggregation jobs produce the expected totals.
• Compare data between cloud and on-premises systems.

How reconciliation works

Each policy compares a source asset with a sink asset by using one of two match types:

• Data Equality: ADOC joins the source and sink on the columns you mark as join columns, and then compares the values in each pair of columns you map. Use it when specific fields must match.
• Row Count Match: ADOC compares the total number of rows in the source and the sink. Use it when you only need to confirm data volume, not content.

Each time the policy runs, ADOC calculates a score and compares it with the policy's Success Threshold and Warning Threshold to determine whether the run passes, shows a warning, or fails.

Create a Reconciliation policy

You can create a Reconciliation policy from the Manage Policies page or from an asset's details page.

Start from Manage Policies

1. In the left navigation menu, go to Data Observability Cloud > Governance > Policies.
2. Select Add Policy, and then select Reconciliation.
3. In Select Primary Asset, select the source asset. With the Left Outer Join type, ADOC keeps every record from the primary asset in the comparison, even when the sink has no matching record.
4. Select the sink asset.

The Create Reconciliation Policy page opens.

Start from an asset

• In the asset's Overview tab, select Actions > Add Reconciliation Policy.
• In the asset's Policies tab, select Add Policy > Reconciliation.

The Create Reconciliation Policy page has eight steps. The following sections describe each step in order.

Step 1: Policy details

1. In Select the Reconciliation Match Type, select Data Equality or Row Count Match.
2. In Source Data Processing Engine and Sink Data Processing Engine, select the engine that processes each asset.
   1. Spark: Runs the job on the ADOC Spark engine. Use Spark for file-based sources and for Kafka and Google Pub/Sub topics.
   2. Pushdown: Runs the query in the source database. Use Pushdown for SQL-based sources to reduce data movement and resource use.

You can select a different engine for each asset, for example Spark for the source and Pushdown for the sink.

Step 2 and step 3: Source and sink preprocessing (optional)

Use preprocessing to limit or reshape the data that ADOC compares. Configure the source in Source Asset Preprocessing and the sink in Sink Asset Preprocessing. In Source Sub Type or Sink Sub Type, select one of the following:

• Column Based: ADOC compares the asset's columns. Optionally, enter a filter in Source Asset SQL Filter or Sink Asset SQL Filter, for example region = 'APAC'.
• Custom SQL: ADOC runs your SQL query and compares the query results instead of the asset. Rules then use the columns that the query returns.
• Filters and queries must use valid column names from the selected asset. Queries on Kafka and Google Pub/Sub topics must use Spark SQL syntax.

When to use custom SQL

• The source and sink have different schemas.
• You need to compare aggregated values, such as daily or monthly totals per customer.
• You need to apply business logic or transformations before the comparison.
• The data volume is too large for a row-level comparison.

Example: A usage system stores raw usage events, and a billing system stores monthly billed usage per customer. Write a source query that totals usage by customer and month, and compare it with the billing data. Differences can reveal missing usage records, pipeline delays, or rounding errors.

Supported sources for custom SQL

• SQL databases
• File-based sources
• Kafka and Google Pub/Sub topics (Spark engine only)

For a Kafka or Google Pub/Sub topic, ADOC reads the topic data with Spark and runs your query on it. In the query, you can select, reorder, filter, transform, and aggregate columns to match the structure of the other asset.

Step 4: Configure rules

Data Equality rules

Each rule maps a source column to a sink column and compares their values.

1. Select Add Rule.
2. In Source Column, select the source column.
3. In Operator, select how ADOC compares the values. For more information, see the operator table that follows.
4. In Target Column, select the sink column.
5. Under Selections, select Join column if ADOC should use this column pair to join the source and sink. Select Ignore null values? to exclude null values from the comparison.
6. Optionally, in Rule Name, enter a name for the rule.
7. Select Add.

To map columns automatically, select Fetch Mapping. ADOC suggests rules for the columns. Select Accept All to add the suggested rules, or Reject All to discard them.

Configured rules appear in a list that shows each rule's Name, Rule, Is Join Column?, Ignore Null Values?, Combine Good & Bad Records?, Tags, Description, and Weight.

Allow a tolerance with Absolute Change or Percentage Change

Use Absolute Change or Percentage Change when values can differ slightly between systems, for example because of rounding, and those differences shouldn't count as failures. Each operator defines a range around the source value. The sink value matches if it falls within the range.

• Lower Bound: How far the sink value can fall below the source value.
• Upper Bound: How far the sink value can rise above the source value.

Set at least one bound to a value greater than 0. An empty bound is treated as 0, so you can allow a tolerance on one side only. For an exact match, use Equal instead.

Example: The source value is 200, the Lower Bound is 5, and the Upper Bound is 10. With Absolute Change, sink values from 195 to 210 match. With Percentage Change, sink values from 190 to 220 match.

Choose a join type

In Join Type, select how ADOC joins the source and sink rows before it compares them.

Set the policy thresholds

For both match types, set the thresholds that determine the result of each run:

• Success Threshold (0-100): The minimum score required for the policy to pass.
• Warning Threshold (Optional: 0-100): A lower limit. Scores between the warning threshold and the success threshold show a Warning.

Row Count Match policies have no column rules. You set only the thresholds.

Step 5: Schedule and incremental check (optional)

To process only new data in each run, select Run this policy incrementally, and then configure Incremental Strategy for Source Asset and, if needed, Incremental Strategy for Sink Asset:

1. In Select Incremental Strategy, select id, datetime, or partition.
2. In Auto Increment Column, select a column whose value increases with each new record, such as an order ID or a timestamp.
3. In Initial Offset, enter the starting value for the first run.

If the asset already has an incremental strategy, ADOC uses it as the default. To restore it, select Reset to Default.

Incremental support depends on the match type and engine:

• Data Equality: Supported with the Spark engine. The sink strategy is optional.
• Row Count Match: Supported with Spark, Pushdown, or a different engine for each asset. If you run the policy incrementally, configure a strategy for both assets.

To run the policy on a schedule, select Schedule this policy execution, select a Time zone, and add one or more schedules. ADOC shows the next execution time. Schedule the policy to run after your data loads finish.

Step 6: Alerts and notifications (optional)

• Severity: Select Critical, High, Medium, or Low.
• Notification Channels: Select where ADOC sends alerts, such as email, Slack, Microsoft Teams, ServiceNow, or a webhook.
• Notify On Success and Notify On Warning: Select to also receive notifications when a run passes or shows a warning.
• When do you want to be Re-Notified?: Select how often ADOC sends notifications while the policy stays in violation: never, after a number of failed runs, or every time.

Step 7: Advanced policy configuration (optional)

The available settings depend on the engine you selected.

Settings for the Spark engine:

• Resource Strategy: Select Small, Medium, Large, or Custom. Each option shows its driver and executor resources. Select Custom to define your own limits.
• Auto-retry with higher strategy: If a job fails because it runs out of memory, ADOC restarts it with the next resource tier, for example from Small to Medium.
• Additional Spark Configuration: Enter Spark settings as key and value pairs.
• Yunikorn Queue Name: Optionally, enter a queue to override the default Yunikorn queue for this job.
• Persistence Folder Prefix: Enter the folder prefix where ADOC stores persisted records.
• Bad Records and Good Records: Select Persist to store all records, or Sample Only to store a sample.
• Persisted Columns: ADOC includes the columns that the policy uses. You can add other source and sink columns to the persisted data, but you can't remove columns that the policy uses.

Settings for all engines:

• Data Plane: Select the data plane that runs the policy.
• Run timeout in minutes and Total timeout in minutes: Set the time limits for the job.
• Delay In Minutes: Set the delay time in minutes. For Row Count Match policies, ADOC counts the source rows, waits for the delay, and then counts the sink rows. Use a delay when data takes time to reach the sink after it lands in the source.
• Treat zero rows as success: Select to mark a run as successful when it scans zero rows, instead of showing an error. This applies to both incremental and full runs.

Step 8: Summary

1. Review the Configuration Summary.
2. Enter a Name and Description for the policy.
3. Optionally, select Add Policy Group, and add tags.
4. Select Include in Overall Reliability Score calculation? to include this policy's result in the asset's overall reliability score. If you clear it, the policy doesn't affect the score.
5. Select Save Policy.

Run a Reconciliation policy

1. Go to the Manage Policies page.
2. Find the policy, and then select the Play icon.
3. Select an execution type:
   • All Data: Compares the entire dataset.
   • Incremental: Compares only new data, based on the incremental strategy.
   • Selective: Compares data within an ID or date range that you specify.

For a Data Equality policy, you can apply incremental strategies to both the source and sink during a selective run. Policies that use custom SQL also support scheduled, selective, and incremental runs, subject to the same engine limits.

View results

To view results, select the policy name on the Manage Policies page. The Execution Details page lists each run in Execution History. For the selected run, it shows:

• Quality Score and Rules Passed: The run's score and the number of rules that passed.
• # Source Rows Scanned, # Sink Rows Scanned, and # Diff in Rows Scanned: The number of rows ADOC read from each asset and the difference between them.
• Execution Type, Start Time, and End Time: How and when the run happened.
• Sub Type, Source Engine, and Sink Engine: The preprocessing type and engine used for each asset.

Example: Reconcile a nightly orders load

Your nightly ETL job copies orders from an operational database to the data warehouse. Users report that some orders are missing from their morning reports. To catch the problem after each load:

1. Create a Reconciliation policy with the operational orders table as the source and the warehouse orders table as the sink.
2. In preprocessing, add filters so that both assets include only the last seven days of orders.
3. Select Data Equality. Map order_id to order_id as a join column, and add Equal rules for order_total and status.
4. Set Success Threshold to 100 and Warning Threshold to 98.
5. Schedule the policy to run 30 minutes after the ETL job usually finishes, and send Critical alerts to the team's Slack channel.

Best practices

• Start with Row Count Match: Confirm data volume first, and then add Data Equality rules for business-critical fields.
• Schedule after data loads finish: Running during a load reports differences that aren't real.
• Set realistic thresholds: Use 100 for financial and compliance data, 99 to 100 for customer master data, 95 to 98 for analytical data, and 90 to 95 for log and event data.
• Use Pushdown where possible: It runs in the source database and uses fewer resources.
• Document your filters: Use the policy Description to explain which data the filters exclude and why.
• Watch run times: Large comparisons might need higher timeouts or a larger resource strategy.

Common mistakes to avoid

• Mapping columns that have different names or meanings in the source and sink. Check each rule's source and target columns.
• Ignoring time zone differences in timestamp columns.
• Setting a 100 success threshold when timing differences between the systems are expected.
• Including soft-deleted records in one asset but not the other.
• Not deciding how to handle null values. Use Ignore null values? where nulls are expected.
• Comparing floating-point values with Equal. Use Absolute Change or Percentage Change to allow for rounding.