White Paper

1.2 The Strategic Role of Independent Validation

The concept of "Black Box" testing is central to QuerySurge’s value proposition in a Databricks environment. In software engineering, it is axiomatic that the developer who writes the code should not be the sole tester of that code. The same principle applies to data engineering. If a Data Engineer writes a PySpark transformation in Databricks to calculate "Net Sales," and then writes a unit test using the same logic to verify it, they have only proved that their code is consistent with itself, not that it is factually correct.

QuerySurge allows Quality Assurance (QA) professionals and Data Stewards to define validation rules that are completely decoupled from the implementation logic. Connecting via JDBC to both the upstream Source (e.g., an SAP ERP, a Mainframe, or a Kafka stream) and the downstream Target (Databricks Delta Tables), QuerySurge compares the data point-for-point. This approach catches logic errors, transformation bugs, and ingestion failures that internal checks might miss. Furthermore, by automating this process via APIs, organizations can move from reactive "fire-fighting" to proactive "DataOps," where quality is continuously monitored.

2.2 Connectivity Mechanics: The JDBC Bridge

The lifeline between QuerySurge and Databricks is the JDBC (Java Database Connectivity) standard. While Databricks is built on Apache Spark, it exposes a SQL interface that allows it to behave like a traditional relational database for external tools.

2.2.1 Driver Configuration

To enable connectivity, the specific Databricks JDBC Driver must be deployed to every QuerySurge Agent. The recommended driver is the DatabricksJDBC4x.jar.

• Driver Class: The configuration uses the class com.databricks.client.jdbc.Driver. It is imperative to avoid legacy Simba drivers (com.simba.spark...) to ensure compatibility with modern Databricks features like Photon execution engines and Unity Catalog.

2.2.2 The Connection String

The JDBC URL defines the parameters of the connection. A correctly configured URL ensures not only connectivity but also performance and security.

• Format: jdbc:databricks://<ServerHostname>:<Port>/<HTTPPath>;transportMode=http;SSL=1;AuthMech=<Type>;
• Server Hostname: This points to the Databricks Workspace control plane.
• HTTP Path: This routes the request to the specific Compute Cluster or SQL Warehouse. Using a dedicated SQL Warehouse for validation is often recommended over an interactive cluster to ensure resource isolation and consistent performance.

2.3 Security and Authentication Models

In enterprise environments, managing credentials securely is paramount. QuerySurge supports modern authentication flows that align with Azure and AWS security best practices.

2.3.1 Service Principal Authentication

For automated pipelines (CI/CD), relying on human user credentials (username/password) is fragile and insecure. Passwords expire, and employees leave organizations. QuerySurge supports Azure Active Directory (Entra ID) Service Principals.

• Implementation: The JDBC string utilizes AuthMech=11 (for Azure AD). The OAuth2ClientId and OAuth2Secret are passed as parameters.
• Benefit: This treats QuerySurge as a distinct "non-human identity" in the Databricks workspace. Administrators can grant this Service Principal read-only access to specific tables in the Unity Catalog, enforcing the principle of least privilege. The pipeline can then run autonomously without fear of token expiration breaking the build.

2.3.2 Encryption in Transit

Given that Agents may reside in a different VPC or on-premise network than the Databricks control plane, data privacy during transit is non-negotiable. The SSL=1 parameter in the connection string enforces TLS 1.2+ encryption for all data moving between Databricks and the QuerySurge Agent. This ensures that sensitive PII or financial data extracted for validation cannot be intercepted.

3.2 The Silver Layer: Validating Hygiene and Transformation

The Silver layer is where the messy raw data becomes "Enterprise Quality." Transformations include deduplication, handling missing values, standardizing formats (e.g., state codes), and joining disparate datasets. This complexity makes it the most error-prone layer.

3.2.1 Deduplication Verification

Bronze data often contains duplicates (e.g., the same log entry sent twice). The Silver layer is responsible for filtering these out.

• QuerySurge Approach: A "Duplicate Key" test validates that the uniqueness constraint holds true.
  • Target SQL:
  • Validation Logic: If this query returns any rows, the test fails. This confirms that the deduplication logic in the Databricks notebook (e.g., dropDuplicates()) functioned correctly.

3.2.2 Transformation Logic Testing

If the business rule states "If Customer Region is NULL, set to 'Unknown'", QuerySurge verifies this independently.

• Target SQL: SELECT COUNT(*) FROM silver_customers WHERE region IS NULL
• If the result > 0, the transformation failed.
• Joins: Silver tables often result from joining multiple Bronze tables (e.g., Orders + Customers). QuerySurge allows the QA engineer to write a standard SQL join between the Bronze tables and compare the output against the Silver table. This verifies that the JOIN logic (Left, Inner, Outer) in the ETL code was implemented correctly and didn't result in Cartesian products or data loss.

3.3 The Gold Layer: Validating Business Logic and Aggregation

The Gold layer powers dashboards and ML models. Data here is often denormalized and aggregated (e.g., "Monthly Sales by Store"). Validation here is about mathematical accuracy.

3.3.1 Aggregate Balancing

QuerySurge verifies that the sum of the parts equals the whole.

• Strategy: Compare the SUM(Sales_Amount) from the Silver detailed transactions against the Total_Sales in the Gold summary table.
• Target SQL:
• Query A (Silver): SELECT Store_ID, SUM(Amount) FROM silver_transactions GROUP BY Store_ID
• Query B (Gold): SELECT Store_ID, Total_Sales FROM gold_store_monthly_summary
• Comparison: QuerySurge joins these results on Store_ID and checks if the values match. This catches aggregation errors, grouping set errors, and rounding issues.

3.3.2 Regression Testing

When ETL code is refactored (e.g., optimizing a Spark job for performance), there is a risk of introducing subtle bugs. QuerySurge allows for "Regression Testing" by comparing the Gold layer output of the new pipeline against the Gold layer output of the old pipeline (or a backup). This ensures that performance optimizations do not come at the cost of data accuracy.

4.2 Handling JSON Strings with from_json

Often, data arrives in Databricks as a raw string column containing JSON. QuerySurge can parse this during the test execution.

• SQL Example:
• This allows QuerySurge to validate specific fields deep within a JSON document without requiring the data to be fully modeled in the database first. This is particularly useful for "Shift Left" testing, where QA wants to validate raw landing data immediately upon arrival.

4.3 Validating Schema Evolution

Databricks features like "Auto Loader" allow the schema to evolve automatically (adding new columns as they appear in the source). This poses a challenge for rigid testing tools. QuerySurge addresses this through Column Threshold testing.

• Configurable Strictness: A QuerySurge test can be configured to "Ignore New Columns." If the source has 5 extra columns that the target doesn't (or vice versa), the test can be set to issue a Warning rather than a Failure.
• Benefit: This prevents brittle tests that break every time a developer adds a non-critical field, while still enforcing the integrity of the core "Key Business Elements" (KBEs) that must match exactly.

5.2 The DevOps API

QuerySurge exposes a rich RESTful API (and a CLI wrapper) that allows external orchestration tools to drive the testing process. Key API capabilities include:

• runScenario: Triggers a pre-defined set of tests.
• getScenarioOutcome: Polls for the status (Pass/Fail) and detailed metrics.
• updateConnection: Dynamically changes the JDBC URL (e.g., pointing the tests from the "Dev" cluster to the "Staging" cluster during a promotion).

5.3 Integration Walkthrough: Azure DevOps

Azure DevOps is the standard CI/CD tool for many Azure Databricks shops. QuerySurge provides a native extension to simplify this integration.

5.3.1 Pipeline Implementation (YAML)

The integration typically involves three steps in the Azure Pipeline YAML:

1. Run Test Suite:
   This task initiates the validation job on the QuerySurge server. It is non-blocking (asynchronous).
2. Get Results:
   This task waits for the tests to finish and retrieves the outcome.
3. Gate Logic (Blocking):
   The task can be configured to "Fail the Build" if the QuerySurge result is "Failed." This effectively stops the deployment pipeline. If the tests fail, the new Databricks notebooks are not promoted to Production, or the data pipeline is halted.

5.4 Integration Walkthrough: Jenkins

For organizations using Jenkins, the integration is often achieved via the QuerySurge CLI or REST API within a Shell Script or Groovy script.

5.4.1 Jenkinsfile Logic

This script makes the Jenkins build status dependent on the data quality. It allows for "Nightly Build" concepts to be applied to data: every night, the pipeline runs, validates, and generates a report. If data is bad, the team wakes up to a red build and an automated Jira ticket.

6.2 Agent Scaling and Partitioning

For cases where row-level detail is required (e.g., regulatory audits), QuerySurge supports partitioning.

• Technique: Split the validation into chunks.
  • Test 1: Validate Jan 2023.
  • Test 2: Validate Feb 2023.
  • Test 3: Validate Mar 2023.
• Parallelism: These tests can be assigned to different Agents. If you have 10 Agents, you can run 10 months of validation in parallel, effectively achieving 10x throughput. This horizontal scaling allows QuerySurge to keep pace with the massive data volumes typical of Databricks environments.

7.2 Ready for Analytics

QuerySurge’s "Ready for Analytics" feature exposes its internal database to BI tools.

• The Data Quality Dashboard: Organizations can build a Power BI or Tableau dashboard that connects to QuerySurge.
• Metrics: This dashboard can display trends like "Data Reliability Score per Week," "Defect Rate by Source System," or "Validation Coverage."
• Strategic Value: This elevates Data Quality from a technical issue to a business metric. Executives can see a "Health Score" for their data assets alongside their financial KPIs.