Maintaining Database Consistency Through Automated Validation in New Relic

This Part 2 guide covers automated database consistency validation. Part 1 addressed the setup of Python environments, secure connections to MySQL and Snowflake, and initial row count checks.

The system now uses these connections to run validation checks. New Relic logs the results. Alerts and dashboards provide real-time monitoring of data consistency.

Execute Row Count Validation

The validation process queries row counts from both databases to verify consistency. This function connects to Snowflake, retrieves the count, and returns the result:

def snowflake_validation(table_name):
    connection = snowflake.connector.connect(
        user=USER,
        account=ACCOUNT,
        database=DATABASE,
        schema=SCHEMA,
        role=ROLE,
        warehouse=WAREHOUSE,
        private_key=private_key_bytes
    )
    query = f"SELECT COUNT(*) AS row_count FROM {DATABASE}.{SCHEMA}.{table_name.upper()};"
    cursor = connection.cursor()
    cursor.execute(query)
    return cursor.fetchone()[0]  # Row count

Create similar functions for MySQL and Snowflake to compare row counts automatically.

Log Results to New Relic

The New Relic Python agent logs custom events, such as row count comparisons, for real-time monitoring. Record these events to visualize consistency checks, configure alerts, and track discrepancies in the New Relic dashboard. The example shows how to implement this:

newrelic.agent.record_custom_event(
    "RowCountComparison",
    {
        "table_name": table,
        "mysql_row_count": mysql_row_count,
        "snowflake_row_count": snowflake_row_count,
        "result": result_match
    },
    application=application
)

This configuration displays the data in the New Relic APM dashboard.

Access Data in New Relic

After login:

1. Go to APM > Events > Custom Events.
2. Run the query: `SELECT * FROM RowCountComparison SINCE 1 hour ago`. This shows recent row count validations.

Configure Alerts and Dashboards

1. Alerts: Create a policy in Alerts & AI to trigger notifications when discrepancies occur. This enables rapid response to consistency issues.
2. Dashboards: Use New Relic NRQL queries to visualize row count comparisons over time. This tracks consistency across databases.

Conclusion

Automated database consistency validation with New Relic maintains data reliability. Secure connections, real-time alerts, and proactive monitoring ensure data accuracy across platforms.

This concludes the two-part guide on database consistency validation. Future posts will expand on the Data Pipeline Best Practices and Solutions series. Explore how data validation functions within a full data engineering stack.

Review our GCP data engineering services.