DevOps et observabilité

Testing a Production Data Platform: Unit Tests, Data Quality and CI/CD Gates

A practical architecture for testing production data platforms: unit and transformation tests, data-quality checks, SQL Server and PySpark testing, Microsoft Fabric pipelines and notebooks, integration tests, deployment validation and the CI/CD quality gates that decide whether a change reaches production.

Par JaviPublié 38 min de lectureAvancé

Dans cet article

A pipeline that passes every unit test can still load yesterday’s orders twice. A deployment that succeeds can still drop the column a report depends on. A notebook that runs green can still produce a table where 4% of customer IDs are null. Data platforms fail in ways that application test suites were never designed to catch, and this guide answers one question:

How do we prove that a data platform works correctly before a change reaches production, and keep proving it afterwards?

Two principles run through the whole guide.

The first: testing code is not the same thing as testing data. Code tests ask whether the logic is right for inputs you chose. Data tests ask whether the data that actually arrived, and the data you produced from it, is acceptable. A production platform needs both, and they live in different places in the delivery process.

The second: a deployment pipeline should not only answer “Can this artifact deploy?” It should answer “Should this change be allowed into production?” A green build proves that the code compiles and the definitions are valid. It says nothing about whether the change double-counts revenue, breaks a downstream consumer, widens access to sensitive columns or makes the nightly load two hours slower.

A production data platform earns confidence in layers, each catching a class of defect the others miss:

Layer The question it answers
Code correctness Does each function, procedure and rule do what it claims for known inputs?
Transformation correctness Does the input-to-output behaviour hold for joins, aggregations, deduplication and late data?
Data quality Is the data that arrived, and the data we produced, complete, unique, valid, fresh and plausible?
Integration correctness Do the pieces work together against real storage, real engines and real connections?
Deployment correctness Did the right version land in the right environment with the right configuration?
Security validation Can the right identities reach the right data, and nobody else?
Performance validation Is the change still fast and cheap enough at realistic volume?
Production smoke checks Does the release actually work where it now runs?

Where this guide sits: Designing a Production Data Platform frames testing as one of the platform’s architecture decisions; this guide is the deep dive. It links out to the security, recovery, observability and deployment guides rather than repeating them.

Testing code is not testing data

Most software-testing advice assumes that the input is under your control. You write a fixture, call the function and compare the result. Data platforms add a second, independent source of failure: the data itself. A source system changes a status code, a partner sends the same file twice, an upstream team renames a column, daylight-saving time shifts a batch window. None of these is a code defect, and none of them shows up in a unit test.

  • Code testsIs the logic right?
    1. Fixed inputA fixture you wrote, with chosen edge cases
    2. Run the logicFunction, transformation or procedure
    3. DeterministicCompare to expectedExact output, same result every run
    4. Pass or fail
  • Data testsIs the data right?
    1. Real or representative dataWhatever actually arrived
    2. Run checksRules on rows, columns, tables and time
    3. ThresholdsCompare to a rule or baselineNulls, duplicates, freshness, volume, reconciliation
    4. Pass, warn or fail
Two kinds of evidence. Code tests fix the input and check the logic; data tests take the input as it comes and check the result against a rule or a baseline. Read each column top to bottom: the steps don't correspond one to one.

The consequence for architecture is that data tests can’t all run before deployment. Some run in CI against fixtures, some run in a test environment against representative data, and many run in production on every load, because that is the only place the real data exists. A testing strategy that ends at the release pipeline has tested the code and assumed the data.

The running example

The examples use a generic orders platform:

  • Sources: an operational SQL Server database read through change data capture, and nightly partner files with order adjustments.
  • Lakehouse: raw landing files, then Bronze and Silver Delta tables built by PySpark notebooks: deduplicated orders, conformed customers, currency conversion.
  • Warehouse: a Gold star schema loaded by T-SQL stored procedures, including a MERGE into fact_orders.
  • Orchestration: Fabric pipelines that run the notebooks and procedures, with an execution log and watermarks for incremental loads.
  • Serving: a semantic model and reports for finance, and a data API for a few internal applications.
  • Delivery: item definitions in Git, pull requests into main, and promotion through Dev, Test and Prod workspaces.

The finance team reconciles revenue monthly against the ERP, so a silent double-load is not a cosmetic bug: it is a restatement.

The testing layers

Each layer below has a purpose, typical examples and a boundary: what it can’t prove. The boundaries matter more than the layers, because most testing failures come from asking a layer to prove something it can’t.

1. Static validation

Purpose: catch obvious defects before anything runs.

Examples: Python linting and formatting, type checks on library code, notebook and pipeline definition files that parse, T-SQL that builds in a database project, configuration files that match their schema, parameter files with every required environment value, dependency declarations that resolve.

A SQL database project build is a particularly strong static check: Microsoft documents that the build validates references between objects and the T-SQL syntax for a target platform, and fails when a view references a column that doesn’t exist (SQL database projects). That turns a whole class of deployment failures into pull-request failures.

What it can’t prove: anything about behaviour. Valid syntax that computes the wrong total passes.

2. Unit testing

Purpose: verify individual pieces of logic in isolation.

Examples: a Python function that parses a partner file name into a business date; a PySpark function that standardises currency codes; a T-SQL function that maps order status codes; the rule that decides whether an order is late.

Unit tests should be small, deterministic and fast.

Small means a handful of rows. Deterministic means the same result on every run: no current timestamp, no random sampling, no network. Fast means the whole suite runs in minutes on a developer’s machine or a CI agent, so it can run on every pull request.

What it can’t prove: that the data that arrives looks like your fixtures, that the code works against real storage, that a join produces the right cardinality at volume, or that two correct functions combine correctly. A unit test proves the logic, under the assumptions you wrote into the fixture.

3. Transformation testing

Purpose: verify the input-to-output behaviour of a transformation step as a whole.

This is a data-specific layer that general testing advice tends to skip. A transformation is usually several unit-tested pieces composed with joins, filters and aggregations, and most data defects live in the composition: a left join that should have been inner, a filter applied before deduplication instead of after, an aggregation at the wrong grain.

Verify:

  • input → output correctness for a small, deliberately designed input table;
  • join cardinality: one-to-one stays one-to-one, unmatched keys go where the design says;
  • aggregations at the intended grain;
  • derived columns, including their null behaviour;
  • filters, and which side of a join they apply to;
  • deduplication: which duplicate wins, and why;
  • late-arriving data: a fact older than the watermark, a fact whose dimension hasn’t arrived yet;
  • null handling in keys, measures and join columns.

Why it deserves its own layer even when the functions have unit tests: each function can be correct and the pipeline still wrong. “Standardise currency” and “sum by customer” are both right; applying the sum before the currency conversion is the bug.

What it can’t prove: that real data has the shape the input fixture assumed.

4. Data-quality testing

Purpose: verify properties of actual or representative data.

Examples: required keys are not null; business keys are unique; amounts are inside valid ranges; every order references a known customer; the newest event is recent enough; today’s row count is plausible against history; the target reconciles with the source.

A transformation can be technically correct while the resulting data is still wrong.

The transformation did exactly what it was written to do. The source sent a duplicate file, or a new status code, or a currency that the reference table doesn’t know. Only a check on the data catches that, and only where the data is. That is why data-quality tests run in three places: against fixtures in CI (to test the rules themselves), against representative data in a test environment, and against production data on every load.

What it can’t prove: that values are accurate. A check can show that an amount is positive and that totals reconcile with the source; it can’t show that the source was right. Accuracy usually needs an authoritative external reference, covered in the data-quality framework below.

5. Integration testing

Purpose: verify that components work together with real dependencies.

Examples:

  • Source → Pipeline → Lakehouse: the pipeline reads a test source and lands files where the notebook expects them.
  • Notebook → Delta table: the notebook writes, merges and evolves a real Delta table.
  • Warehouse → semantic model: the model refreshes against the Gold schema, and its relationships resolve.
  • API → ingestion → transformation: a client handles pagination, throttling and partial failure from a real (test) endpoint.
  • Git → deployment pipeline → workspace: the deployed items bind to the right Lakehouse, connections and variables.

Where mocks stop being sufficient: a mock encodes your assumption about a dependency. It is the right tool for unit tests and the wrong one for proving the assumption. Mocks stop being enough when the behaviour you care about belongs to the dependency: Delta transaction semantics, Warehouse type coercion, a connector’s paging, permissions, network paths, a capacity’s concurrency limits. Those need a real (non-production) instance.

What it can’t prove: the full business outcome, or behaviour at production volume.

6. End-to-end testing

Purpose: verify a complete business flow, from source to the number someone looks at.

Example: insert a known set of orders and adjustments in the test source, run the full chain, and check that the finance report shows the expected revenue for the test customer.

End-to-end tests are slower, more expensive and harder to diagnose: when one fails, a dozen components are suspects. Use them sparingly, for a few critical business flows. They still matter because they are the only test that proves the pieces were connected in the right order with the right configuration, and the only test whose result a business owner can read.

7. Security testing

Purpose: prove that access works as designed: the right identities can reach the right data, and nobody else can.

Covers: identities (users, groups, service principals, workspace identities); workspace roles; item permissions; row-level security (RLS); object- and column-level security (OLS); secrets and how they are retrieved; tenant isolation where several customers share tables; what a deployment changed about access.

The design itself belongs in Microsoft Fabric Security Architecture, and the Fabric Security Lab shows how to prove each control by signing in as a test identity. From a testing point of view, the important properties are:

  • Test both directions. A positive test (the analyst sees their region) and a negative test (the analyst does not see other regions). Security failures are usually missing negatives.
  • Test with non-personal identities. A test that runs as a workspace admin proves nothing about RLS.
  • Treat security changes as regressions. A deployment that broadens access should fail or require approval, even if nothing functional changed.

8. Performance testing

Purpose: verify latency, throughput, query runtime, Spark runtime, pipeline duration, concurrency and capacity impact at a realistic volume.

The core idea is baseline versus regression: you rarely know the “right” runtime for a load, but you know what it was last week. A performance test compares a change against a recorded baseline under comparable conditions, covered in performance testing below.

9. Recovery testing

Purpose: verify that the platform behaves correctly when things go wrong and are retried.

Covers: reruns of a failed load, automatic retries, idempotency (running the same window twice gives the same result), replay of a past window, restart from a checkpoint or watermark, and restore or rebuild of an item. Recovery and Disaster Recovery Architecture for Microsoft Fabric covers the recovery design; this guide covers the tests that prove it, in recovery testing.

10. Post-deployment validation

Purpose: prove that the release works where it now runs. These are smoke tests: quick, shallow, read-mostly checks that run immediately after a deployment and stop the rollout when they fail.

Examples:

  • the deployed items exist, at the expected version;
  • connections resolve and authenticate;
  • a small query succeeds against each serving endpoint;
  • the expected schema exists (tables, columns, types);
  • the service identities can read and write what they need;
  • a critical pipeline starts (and, where safe, completes a small run);
  • row counts of key tables fall within expected bounds.

Smoke tests don’t replace integration tests; they verify the deployment, not the logic. A good smoke test runs in minutes, changes no business data and gives an unambiguous yes or no.

A testing model for data platforms

The classic software test pyramid says: many fast unit tests at the bottom, fewer integration tests, very few end-to-end tests at the top. The proportions are sound. The shape doesn’t fit data platforms perfectly, for three reasons:

  1. Data quality is not a level; it is cross-cutting. The same rule (“order ID is unique”) is tested against fixtures in CI, against representative data in Test, and against real data in production on every load.
  2. Much of the logic lives in engines, not in your code. SQL, Spark and Delta semantics are only fully tested against the engine, which pushes more weight into integration tests than a typical application has.
  3. Production is a test environment for data. Production data-quality checks and smoke tests are not optional extras; they are the only tests that see the real input.
  1. Production validationAfter every release and every load
    • Smoke tests
    • Production data-quality checks
    • Monitoring signals
  2. End-to-endFew, slow, business-readable
    • Critical business flows
  3. Security and performanceRealistic environment and volume
    • Access tests
    • Performance regression
  4. Integration and recoveryReal engines and storage, test data
    • Pipeline runs
    • Notebook to Delta
    • Rerun and replay
  5. Data quality and contractsCross-cutting, from CI to production
    • Schema contract
    • Critical keys
    • Reconciliation
    • Freshness and volume
  6. Transformation testsInput to output, small tables
    • Joins
    • Aggregations
    • Deduplication
    • Late data
  7. Unit testsSmall, deterministic, fast
    • Python
    • PySpark
    • T-SQL
  8. Static checksSeconds, no execution
    • Lint and format
    • Build
    • Config validation
Data platform testing layers. Cheap, fast and numerous at the bottom; slow, expensive and few at the top. Data quality is cross-cutting: the same rules run in CI, in Test and in production.

The rule of thumb that survives from the pyramid: push every test as low as it can go. If a deduplication rule can be proven with five rows in a unit test, don’t wait for an end-to-end run to find out it is wrong. Reserve the expensive layers for the things only they can prove.

The testing matrix

The matrix is the reference version of the model: what each layer proves, where it runs, and whether it usually blocks a deployment.

Layer What it proves Example Where it runs Blocks deployment?
Static checks Code and definitions are well formed Lint, notebook parses, database project builds Developer machine, pull request Yes
Unit tests Logic is correct for known inputs Status-mapping function, currency standardisation Pull request, no shared infrastructure Yes
Transformation tests Input → output behaviour of a step Five-row orders table → expected Silver output Pull request, local Spark or a small SQL instance Yes
Schema tests Structure matches the declared contract fact_orders has the required columns and types Pull request (definitions), Test (deployed objects) Yes for breaking changes
Data-quality tests Data satisfies its rules No null customer_id, no duplicate order_id, fresh within two hours Test environment, then production on every load Severe rules yes; warnings no
Integration tests Components work together Pipeline lands a file; notebook merges into Delta Test environment Yes
Security tests Access matches the design Analyst sees one region, not the others Test environment Critical failures yes
Performance regression No significant slowdown versus baseline Gold load runtime within agreed tolerance Test or performance environment Policy-based
Deployment validation The right version and configuration landed Items exist, bindings and variables point to Test Each target environment, right after deploy Yes (stops promotion)
Production smoke tests The release works in production Small query, pipeline start, row-count bounds Production, right after deploy Triggers rollback or stop

Mandatory versus advisory gates. Not every check has to block. A mandatory gate stops the pipeline and needs a code change or an explicit, recorded override. An advisory gate reports, creates a ticket or notifies the owner, and lets the release continue. A new rule usually starts advisory while its threshold is calibrated, and becomes mandatory once it has proven itself. What matters is that the classification is a deliberate decision with an owner, not an accident of which tests happened to be wired to exit 1.

CI/CD quality gates

This is where testing becomes architecture. The release pipeline is a sequence of gates, each one cheaper than the next, each one producing evidence that the next decision relies on.

  1. Pull requestMinutes, no shared infrastructure
    • Static checks
    • Unit tests
    • Transformation tests
    • Schema and contract tests
  2. merge to main
    BuildOne versioned artifact for every environment
    • Python wheel
    • Database project .dacpac
    • Item definitions
  3. deploy
    Test environmentReal engines, representative data
    • Deploy to Test
    • Integration tests
    • Data-quality checks
    • Security checks
    • Performance regression
  4. evidence
    Release decisionEvidence, policy and approval
    • Gate results
    • Approval or policy gate
  5. approved
    ProductionDeploy, verify, observe
    • Deploy production
    • Smoke tests
    • Monitor
CI/CD quality gates for a data platform. A change earns its way to production: cheap checks on every pull request, one versioned artifact, behaviour proven in Test, a release decision made on evidence, then validation in production.

The gates, one by one

Developer change. Work happens in a feature branch connected to a development workspace or a local environment (Git Integration in Microsoft Fabric covers the branch-to-workspace model). The developer runs static checks and unit tests locally before pushing; the pipeline repeats them, because local runs are not evidence.

Static checks. Lint, format, type checks, definition parsing, configuration schema validation, and the database project build. These fail in seconds and should never be skipped.

Unit tests. Python and PySpark logic with pytest, T-SQL logic with a SQL test approach (below). They need no Fabric capacity and no shared database, so they can run on every push.

Transformation tests. Small input tables, expected outputs, a local Spark session or a disposable SQL instance. These are where most data defects should be caught.

Schema and contract tests. Compare the declared output schemas against the contract: required columns, types and nullability; additions allowed; removals and renames flagged as breaking. A breaking change fails here unless the pull request carries an explicit, reviewed contract version change.

Build. Produce one versioned artifact per deployable unit: a wheel for shared Python code, a .dacpac for the Warehouse schema, the item definitions for Fabric. The same artifact goes to Test and Prod; rebuilding per environment means production runs something that was never tested.

Deploy to Test. Deploy with the same mechanism and the same non-personal identity that production uses: a deployment pipeline, a Git-based flow, or a scripted tool such as fabric-cicd (Microsoft Fabric CI/CD with fabric-cicd). Environment differences come from configuration (variable libraries, deployment rules, parameter files), never from editing items by hand. The deployment validation step runs immediately: items exist, bindings point to Test, not Dev.

Integration tests. Run pipelines and notebooks against test sources and representative data; verify Delta writes, Warehouse loads and model refreshes.

Data-quality checks. Run the same rule set that production will use, against the Test output. Here the rules themselves are being tested too: a rule that fires on clean representative data is mis-calibrated.

Security checks. Sign in (or query) as test identities for each role; verify positive and negative access; diff effective permissions against the previous release.

Performance regression. For changes that touch heavy loads or serving queries, compare runtime and resource use against the baseline. Not every pull request needs this; changes to hot paths do.

Approval or policy gate. A person or a policy decides with the evidence in front of them: test results, data-quality results, performance comparison, schema-change summary. Approval without evidence is a ritual.

Deploy production. Same artifact, same mechanism, production configuration.

Smoke tests. The post-deployment checks from layer 10. A failure stops the rollout and triggers the rollback procedure.

Monitor. The release hands over to monitoring: run status, durations, data-quality results and freshness on the next scheduled loads. A release is not done until the first production load after it has passed its checks.

What should block a deployment

The table below is a default, not a law. Each organisation sets its own policy by blast radius, regulatory exposure and how reversible the change is.

Failure Default recommendation
Syntax, lint or build failure Block
Unit or transformation test failure Block
Breaking schema change (removal, rename, type narrowing) Block unless explicitly approved with a contract version change
Additive schema change Allow; notify consumers
Reconciliation failure on critical data Block
Severe data-quality rule failure (null or duplicate critical key) Block
Minor data-quality warning Warn and investigate
Critical security test failure (access broadened, isolation broken) Block
Small performance regression within tolerance Policy-based: record and allow
Major performance regression Block, or require explicit approval
Integration test failure Block
Missing monitoring or execution-log telemetry for a new workload Usually block the production release
Deployment validation failure in Test Block promotion
Production smoke-test failure Stop the rollout and roll back or fix forward

These are Javi Builds architectural recommendations, not universal rules or Microsoft requirements. Two patterns help keep them honest:

  • Overrides are explicit and recorded. A blocked gate can be overridden by a named owner with a reason, and the override is visible in the release record.
  • Advisory checks have owners. A warning that nobody reads is not a control. Every advisory check routes to someone who decides whether it becomes mandatory, is recalibrated or is deleted.

Test data strategy

The data you test with decides what your tests can prove. There is no single right dataset: each kind answers different questions and carries different risks.

    1. Unit and transformationSmall deterministic fixtures
    2. best for
      Proves logic and edge cases
    3. limitation
      Misses real-world shape
    1. IntegrationSynthetic data
    2. best for
      Proves volume and flows
    3. limitation
      Only the shapes you imagined
    1. Integration, performanceRepresentative datasets
    2. best for
      Proves realistic behaviour
    3. limitation
      Must be curated and refreshed
    1. Performance and UATSanitized production-derived
    2. best for
      Proves real distributions
    3. limitation
      Masking must be proven
    1. Avoid outside productionUnrestricted production data
    2. best for
      Production checks only
    3. limitation
      Privacy and access risk
Test data, by what it proves. Each row is a kind of test data, the layer it serves best, and its main limitation. Use the smallest dataset that proves the behaviour, and move up only when a smaller one can't.

Small deterministic fixtures. A few rows, written by hand, each one there for a reason: the duplicate, the null key, the late fact. They are the backbone of unit and transformation tests. Keep them in the repository, next to the tests, so a reviewer can read the input and the expected output in the same pull request.

Synthetic data. Generated data, useful for volume, concurrency and integration flows where the exact values don’t matter. Its limitation is that it contains only the patterns its generator knows about. Synthetic data rarely contains the malformed date that breaks production.

Representative test datasets. A curated, versioned dataset that resembles production in shape (key cardinality, skew, null rates, category distribution) without being production. It is the workhorse for integration and performance testing. Curate it deliberately, record what it represents, and refresh it when production’s shape changes.

Production-derived sanitized data. Production data with sensitive values masked, tokenized or removed. It gives real distributions, which matters for performance and for rules such as volume and distribution checks. It also carries the most risk: masking can be incomplete, quasi-identifiers can re-identify people, and a copy outlives its purpose. Treat the sanitization pipeline itself as a tested, reviewed component, apply the same classification and access controls as the source until masking is proven, and record retention.

Production data. Unrestricted production data in development or test is a security and privacy problem before it is a testing choice: development workspaces usually have broader access, weaker monitoring and more copies. Most organisations’ data-protection obligations don’t make an exception for “it was only for testing”. Production data belongs in production, where production data-quality checks and smoke tests already see it.

Principles that hold for all of them:

  • Use the smallest dataset that proves the behaviour. Five rows that contain the duplicate prove deduplication better than five million rows that might.
  • Include edge cases deliberately. If it isn’t in the fixture, it isn’t tested.
  • Be deterministic where possible. Fixed dates instead of now(), fixed seeds for generators, explicit ordering where order matters.
  • Be privacy-safe by default. Test data that never contained personal data never needs masking.
  • Make it reproducible. Versioned in Git or in a known, immutable location, so a failing test can be rerun on the same input.

Designing edge cases

Testing only the happy path proves very little in data engineering.

Happy-path data is the data that never causes incidents. Production incidents come from the rows nobody designed for. A useful habit: for every transformation, walk this list and decide, explicitly, what the correct behaviour is. The decision is often more valuable than the test, because it surfaces a requirement nobody had written down.

Edge case Why it breaks pipelines What to decide and test
Zero rows Aggregations return nothing, a MAX returns null, a watermark moves to null Does the load succeed, skip or alert? Does the watermark stay put?
One row Window functions, ranking and averages behave differently Output is still correct and typed
Duplicate key Joins multiply rows; MERGE fails or picks one arbitrarily Which row wins, by which rule?
Null key Nulls don’t join; they are silently dropped or grouped together Reject, quarantine or map to an “unknown” member?
Missing dimension A fact references a customer that hasn’t arrived yet Inferred member, hold, or late re-link?
Late-arriving fact A fact older than the watermark is skipped Is the lookback window wide enough? Is it reprocessed?
Unexpected category A new status code falls through a CASE Fail, map to “Other”, or alert?
Extremely large value Overflow, or a plausible-looking but wrong total Type width, range rule, outlier warning
Negative value Refunds and corrections are real; so are sign errors Allowed for this measure, or a defect?
Timezone boundary An event at 23:30 local time lands in tomorrow’s UTC partition Which clock defines the business day?
DST transition A local hour happens twice, or not at all Store UTC; test the 23- and 25-hour days
Malformed date Parsing returns null instead of failing Null rate rule on parsed columns
Schema change A new, renamed or retyped column at the source Contract check before load; evolution policy
Duplicated file The partner resends yesterday’s file File-level deduplication by name and hash
Replayed batch A rerun processes the same window again Idempotent write: the second run changes nothing

The last three rows are where data testing and recovery testing meet: a pipeline that handles a duplicated file and a replayed batch correctly is a pipeline that can be rerun safely during an incident.

PySpark testing

PySpark transformations are the easiest part of a Fabric Lakehouse platform to test well, and the most often left untested, usually because the logic lives inline in notebook cells. The first architectural move is to put transformation logic in functions that take and return DataFrames, in a module or package the notebook imports. The notebook then handles parameters, reading and writing; the functions hold the logic and can be tested anywhere Spark runs, including a CI agent with a local Spark session.

Use the smallest DataFrame that proves the transformation.

This section is the short version. PySpark DataFrame Unit Testing with pytest is the full guide: schema assertions, nulls, duplicates and deterministic windows, joins, timestamps, floating-point tolerances and the boundary with Delta integration tests, with a test suite that was run end to end.

A compact example

The transformation: total order amount per customer, ignoring rows with a null customer.

Input:

customer_id amount
1 100
1 50
2 75

Expected output:

customer_id total
1 150
2 75

The function under test:

# orders/transforms.py
from pyspark.sql import DataFrame, functions as F


def total_by_customer(orders: DataFrame) -> DataFrame:
    """Total amount per customer. Rows without a customer are excluded."""
    return (
        orders.where(F.col("customer_id").isNotNull())
        .groupBy("customer_id")
        .agg(F.sum("amount").alias("total"))
    )

A session-scoped SparkSession fixture, shared by every test through conftest.py:

# tests/conftest.py
import pytest
from pyspark.sql import SparkSession


@pytest.fixture(scope="session")
def spark():
    session = (
        SparkSession.builder.master("local[1]")
        .appName("unit-tests")
        .config("spark.sql.shuffle.partitions", "1")   # tiny data: avoid 200 empty tasks
        .config("spark.sql.session.timeZone", "UTC")   # deterministic timestamps
        .getOrCreate()
    )
    yield session
    session.stop()

The tests, comparing DataFrames with assertDataFrameEqual from pyspark.testing (available since Spark 3.5):

# tests/test_transforms.py
from decimal import Decimal

from pyspark.sql.types import DecimalType, LongType, StructField, StructType
from pyspark.testing import assertDataFrameEqual, assertSchemaEqual

from orders.transforms import total_by_customer

ORDERS = StructType([
    StructField("customer_id", LongType(), True),
    StructField("amount", DecimalType(18, 2), True),
])


def test_totals_per_customer(spark):
    orders = spark.createDataFrame(
        [(1, Decimal("100")), (1, Decimal("50")), (2, Decimal("75"))], ORDERS
    )
    expected = spark.createDataFrame(
        [(1, Decimal("150")), (2, Decimal("75"))], "customer_id long, total decimal(28,2)"
    )

    actual = total_by_customer(orders)

    assertSchemaEqual(actual.schema, expected.schema)
    assertDataFrameEqual(actual, expected)  # row order is ignored by default


def test_null_customer_is_excluded(spark):
    orders = spark.createDataFrame([(None, Decimal("10")), (3, Decimal("5"))], ORDERS)
    assert total_by_customer(orders).collect() == [(3, Decimal("5"))]


def test_empty_input_returns_empty_output(spark):
    orders = spark.createDataFrame([], ORDERS)
    assert total_by_customer(orders).count() == 0

The example is illustrative and hasn’t been run as published; check the pyspark.testing signature for your Spark version (assertDataFrameEqual). It shows the shape: an explicit schema, three rows that each have a purpose, an expected DataFrame, and separate tests for the null and empty cases. Note that SUM over a decimal(18,2) widens the precision; the schema assertion makes that visible instead of letting a downstream table silently change type.

What to cover in PySpark tests

Concern What to test, and how
Deterministic schemas Always pass an explicit schema to createDataFrame. Inferred schemas change with the data (an all-null column has no type) and hide type bugs
Input/output comparison assertDataFrameEqual compares rows regardless of order by default; set checkRowOrder=True only when order is part of the contract
Schema comparison assertSchemaEqual, or compare df.schema to the contract’s StructType, including nullability where it matters
Null behaviour A null in the key, the measure and the join column, each in its own row
Joins An unmatched key on each side; a duplicate key on the right side (does the left row multiply?)
Aggregation The grain: assert one output row per key, and totals on a known input
Deduplication Two rows with the same key and different timestamps: assert which one survives
Timestamps Fix the session time zone; include a value near midnight and a DST transition date
Floating-point values Use decimals for money. For genuine floats, compare with a tolerance (rtol, atol in assertDataFrameEqual) rather than exact equality
SparkSession fixtures One session per test run (scope="session"), a single local core and one shuffle partition: startup dominates small tests
Delta write behaviour An integration test, not a unit test: needs Delta, so run it in a Fabric test workspace or with Delta configured locally
Idempotency Run the write or MERGE twice with the same input and assert the table is identical after the second run

Python testing for pipeline code

Not all pipeline logic is Spark. Ingestion clients, file-name parsers, configuration loaders, orchestration helpers and small transformations in pure Python or pandas carry as much risk, and they are the cheapest code on the platform to test. The tool is pytest; what follows is the data-engineering subset that pays off.

Fixtures set up what a test needs and clean it up afterwards. Code after yield in a fixture is teardown, and fixtures in conftest.py are shared by every test in the directory (pytest fixtures). Use them for Spark sessions, test configuration and temporary storage.

Parameterization turns an edge-case table into tests. One test function, one row per case:

import pytest
from datetime import date

from ingest.files import business_date_from_name


@pytest.mark.parametrize(
    ("file_name", "expected"),
    [
        ("orders_20261108.csv", date(2026, 11, 8)),
        ("orders_20261108_v2.csv", date(2026, 11, 8)),   # resent file
        ("ORDERS_20261108.CSV", date(2026, 11, 8)),      # partner changed case
    ],
)
def test_business_date_from_name(file_name, expected):
    assert business_date_from_name(file_name) == expected


@pytest.mark.parametrize("file_name", ["orders.csv", "orders_2026-11-08.csv", "orders_20261341.csv"])
def test_malformed_names_are_rejected(file_name):
    with pytest.raises(ValueError):
        business_date_from_name(file_name)

Exceptions are behaviour. pytest.raises proves that a malformed input fails loudly instead of returning None and loading a null date.

Temporary files. pytest’s built-in tmp_path fixture gives each test an empty directory, so file-ingestion code can be tested against real files without touching shared storage.

Mocks for API clients. An ingestion client’s own logic (pagination, retry on throttling, stopping on the last page, refusing a partial response) is unit-testable if the HTTP call is injected or patched. Mock the transport, not your own code, and keep one integration test against a real test endpoint, because the mock only encodes what you believe the API does.

Database dependencies. Separate the SQL text and the decision logic from the connection. The logic gets unit tests; the query gets an integration test against a disposable database or a test Warehouse.

Test organisation. Mirror the package structure (src/ingest/files.py → tests/ingest/test_files.py), keep fixtures in conftest.py, and mark slow or infrastructure-dependent tests (@pytest.mark.integration) so the pull-request run can exclude them and the Test-environment run can include them.

Coverage. Line coverage shows what was never executed, which is useful. It doesn’t show which edge cases were considered, which is what matters. Treat a coverage drop on a pull request as a prompt for review, not a quality score.

SQL Server testing

T-SQL carries a lot of a data platform’s business logic: stored procedures that load facts, views that define business measures, functions that classify, MERGE statements that upsert. It deserves the same discipline as Python, and the SQL tooling supports it better than many teams realise.

What to test

Object What a test should prove
Stored procedures Given known rows in the input tables, the target tables contain the expected rows afterwards; error paths raise the expected error
Views The view returns the expected rows and columns for a known input, including joins that should not multiply rows
Functions Each input class maps to the expected output, including NULL and boundary values
ETL SQL Watermark selection, late-arriving rows, deduplication logic
MERGE / upsert logic Insert, update, no-op and (if designed) delete cases; a duplicate source key fails or is handled deterministically; running twice changes nothing
Constraints The constraints you rely on exist and reject what they should
Schema deployment The database project builds; the generated deployment script contains no unintended drops
Query regressions Critical queries keep their runtime and plan shape within tolerance

Setup, teardown and isolation

A database test has three parts: arrange known rows, act by running the object, assert on the result. The hard part is isolation: tests must not depend on each other’s data, and must leave nothing behind.

On SQL Server, the simplest isolation is a transaction that is always rolled back:

Requête SQL: Rollback-only test of usp_load_fact_ordersT-SQL
-- Test: usp_load_fact_orders inserts new orders and updates changed ones, once.
BEGIN TRANSACTION;

    -- Arrange: known staging rows and an existing fact row
    DELETE FROM stg.orders;
    INSERT INTO stg.orders (order_id, customer_id, amount, modified_at)
    VALUES (1001, 1, 100.00, '2026-11-08T10:00:00'),   -- new
           (1002, 2,  80.00, '2026-11-08T11:00:00');   -- changed

    DELETE FROM dbo.fact_orders WHERE order_id IN (1001, 1002);
    INSERT INTO dbo.fact_orders (order_id, customer_id, amount)
    VALUES (1002, 2, 75.00);

    -- Act: run the load twice to prove it is idempotent
    EXEC dbo.usp_load_fact_orders;
    EXEC dbo.usp_load_fact_orders;

    -- Assert
    IF (SELECT COUNT(*) FROM dbo.fact_orders WHERE order_id IN (1001, 1002)) <> 2
        THROW 50001, 'Expected exactly two fact rows after two runs.', 1;
    IF (SELECT amount FROM dbo.fact_orders WHERE order_id = 1002) <> 80.00
        THROW 50002, 'Changed order was not updated.', 1;

ROLLBACK TRANSACTION;
T-SQL24 lignesAffichage en lecture seule. Aucune connexion à une base de données.

This is an illustrative pattern, not a framework. It breaks down when the procedure under test manages its own transactions (a ROLLBACK inside it ends the outer transaction too), and a failing THROW leaves the transaction open until the session ends, so a test runner needs a TRY…CATCH that rolls back. That bookkeeping is exactly what a test framework does for you.

tSQLt is the established open-source unit-testing framework for SQL Server. Tests are written in T-SQL, grouped in test classes, and run automatically inside transactions so they stay independent and need little cleanup (tSQLt). Its most useful features for data work are isolation procedures such as tSQLt.FakeTable (replace a table with an empty, constraint-free copy, so a test inserts only the columns it cares about) and tSQLt.SpyProcedure (replace a called procedure), and assertions such as tSQLt.AssertEqualsTable (compare an expected table with an actual one) (tSQLt user guide). It is installed into the database under test, so it belongs in development and CI databases, never in production. Check its platform support for your target before adopting it: tSQLt documents SQL Server support, and doesn’t present itself as a tool for Fabric Warehouse.

Database projects give the schema side of SQL testing a home. The project builds into a .dacpac, the build validates syntax and references, and the publish step calculates the difference with the target and generates a deployment script (SQL database projects). Two gates follow directly: build on every pull request, and review the generated script before production, looking for drops and table rebuilds. The BlockOnPossibleDataLoss deployment option, kept on, turns a destructive change into a failed deployment instead of lost data; Deploy a Fabric Solution with Git, Deployment Pipelines and Database Projects shows that block happening on purpose.

Query regressions are tested against a baseline: runtime, logical reads and plan shape for the critical queries, before and after the change, on the same data. How to Find SQL Server Performance Regressions with Query Store covers the SQL Server mechanics.

Future Javi Builds guides in this area: a hands-on SQL Server database-testing walkthrough (transaction isolation, tSQLt, database-project build gates), and a deeper look at testing MERGE and incremental SQL loads.

Microsoft Fabric testing

Fabric doesn’t ship a single “test runner” for a workspace, so a Fabric testing strategy is assembled from three things: testable code outside the items, runs of the items in a test workspace, and checks on the data and the deployment. Each item type has a different testing surface. Testing Microsoft Fabric Pipelines and Notebooks is the implementation guide for this section: parameter and expression tests, controlled failures, idempotent reruns, CI-triggered runs and post-promotion checks.

Pipelines

Pipeline logic is mostly configuration: parameters, expressions, conditions, dependencies and retry policies. It is tested by running the pipeline, so it is integration testing by nature.

  • Parameters and expressions: run the pipeline in Test with explicit parameter values, including boundary values (an empty date range, a single day, a month-end), and assert on what it wrote or logged.
  • Branching: for each If Condition or Switch, a test run that takes each branch. Untested branches are where failure-handling bugs live.
  • Dependencies: a child activity that should not run after a parent failure doesn’t. Test it by making the parent fail on purpose (a missing file, an invalid parameter).
  • Retries and failure paths: the failure path (notification, execution-log entry, watermark not advanced) is part of the design and deserves its own test run.
  • Idempotency: run the same window twice and compare the target tables.

Runs can be started from a CI pipeline through the Fabric REST API’s on-demand item job endpoint, which returns a location to poll for status (Run on demand item job); the test then queries the outputs and the execution log. Keep environment-specific values in a variable library, which pipelines, notebooks, Dataflow Gen2 and other items can consume, so the item under test is the same definition that reaches production (variable library overview). Microsoft Fabric Invoke Pipeline Activity and Monitoring Microsoft Fabric Pipelines and Notebooks in Production cover the parent-child and run-status mechanics a test harness depends on.

Notebooks

  • Transformation logic: move it into functions in a library (a module in the repository, packaged as a wheel and attached through an environment’s custom libraries) and test it with pytest and a local Spark session, as in the PySpark section. The notebook becomes thin.
  • Parameter handling: the parameters cell defines defaults; a test run overrides them. Test missing, empty and invalid parameters, and assert that the notebook fails clearly instead of processing everything. Microsoft Fabric Notebook Parameters covers the mechanics.
  • Spark output: an integration run in a test workspace, against a test Lakehouse, with assertions on the resulting tables. A test-harness notebook can run the notebook under test with parameters and read its exit value, using notebookutils.notebook.run (NotebookUtils).

Lakehouse

  • Schema: after a run, compare each table’s schema to its contract (columns, types, nullability).
  • Delta tables: MERGE correctness and idempotency (run twice), schema evolution behaving as designed, no unexpected overwrite of history.
  • Row counts: plausible against the input and against history, not just “greater than zero”.
  • Partition behaviour: the partitions written are the ones the window covers; nothing outside the window was rewritten.
  • Duplicate prevention: business-key uniqueness after the run, and after a rerun.

Two enforcement mechanisms exist inside the Lakehouse itself. Delta Lake supports NOT NULL and CHECK constraints that reject writes violating them (Delta Lake constraints). Materialized lake views can declare row-level constraints that either fail the refresh or drop violating rows (ON MISMATCH FAIL or DROP, with FAIL the default), and column-level data-quality suites with FAIL or WARN actions, where a failing FAIL check blocks the dependent views downstream (data quality in materialized lake views). That is a mandatory-versus-advisory gate expressed in the platform itself. Materialized Lake Views in Microsoft Fabric covers when they fit.

Warehouse

  • Schema: managed as a database project, built on every pull request, with the generated script reviewed.
  • SQL transformations: procedures and views tested with known input rows in a test Warehouse, using the same arrange-act-assert shape as SQL Server.
  • Constraints and expectations: this one matters. Fabric Warehouse supports PRIMARY KEY and UNIQUE only as NONCLUSTERED and NOT ENFORCED, and FOREIGN KEY only as NOT ENFORCED (table constraints). Declared keys help the optimizer and document intent; they don’t stop duplicates. Uniqueness and referential integrity in a Warehouse are therefore data-quality tests you have to run, not guarantees you get.
  • Queries: the critical serving queries, with a baseline runtime, as performance regression tests.

For isolation, prefer a disposable test Warehouse or a test schema that the run creates and drops, rather than relying on SQL Server-style transaction rollback patterns; check the current transaction support on Microsoft Learn before designing around it.

Dataflow Gen2

Dataflow Gen2 is low-code, so its logic is not unit-testable the way a function is. Test it at its edges: run it in Test against known inputs, assert on the destination’s schema, row counts and data-quality rules, and keep environment values in a variable library rather than in the dataflow. Treat its output exactly like any other load: it gets the same contract and data-quality checks.

Eventstream and Eventhouse

Streaming changes the testing model: there is no “after the batch”. Tests move toward replaying a recorded event sample into a test stream, checking that late and out-of-order events are handled, and continuous checks on freshness, volume and schema in the Eventhouse. Microsoft Fabric Real-Time Intelligence: Eventstream, Eventhouse and KQL introduces the components; streaming-specific testing deserves its own guide.

Deployment pipelines

  • Environment promotion: after every promotion, deployment validation runs: items exist, at the expected version, bound to the target stage’s Lakehouse and connections.
  • Deployment rules: rules change data sources, parameters and notebook default Lakehouses per stage, are set on the target stage and only apply on the next deployment (deployment rules). The test is that, after deploying to Test, nothing still points at Dev.
  • Post-deployment checks: the smoke tests from layer 10, run against each stage.

Deploy a Fabric Solution with Git, Deployment Pipelines and Database Projects is the hands-on lab for this flow, including two deliberate deployment failures; Microsoft Fabric CI/CD with fabric-cicd covers the scripted alternative and what to validate after deployment; the Fabric CI/CD Deployment Checklist is the release-day reference.

A data-quality framework

“Data quality” is too broad to test. Break it into dimensions, give each critical table a rule per relevant dimension, and give each rule a severity and an owner.

Dimension The question Example rule
Completeness Are required values present? customer_id is never null in fact_orders
Uniqueness Are business keys unique? One row per order_id
Validity Are values inside valid domains? status in the known set; amount ≥ 0 unless it’s a refund
Consistency Do related representations agree? Order total equals the sum of its lines; the Silver and Gold counts agree for the same window
Accuracy Can the value be reconciled with an authoritative source? Monthly revenue matches the ERP’s closing figure within agreed tolerance
Freshness Is the data recent enough? Newest event_timestamp within two hours of the run
Referential integrity Do relationships resolve? Every fact_orders.customer_key exists in dim_customer
Volume Is row volume plausible? Today’s rows within the range seen on the same weekday in recent weeks
Distribution Has the shape changed unexpectedly? Share of orders per country, null rate of email, average order value stable versus baseline

Accuracy often can’t be proven from the dataset itself. Every other dimension can be checked by looking at the data and its history. Accuracy needs something outside: the source system, a control total from the ERP, a sample checked by a business owner. That is why reconciliation is the bridge between data quality and business trust, and why it usually has a business owner, not only an engineering one.

Data-quality checks in practice

Rules are only useful if they run on every load and produce a recorded result. The lifecycle matters as much as the SQL. Data Quality Checks for Data Pipelines turns this section into a working design: row rules with quarantine and reason codes, dataset checks that hold a batch, baseline thresholds and a results table, with tested PySpark code.

  1. Load completesInto a staging or unpublished state
  2. Run checks
    • Completeness
    • Uniqueness
    • Freshness
    • Reconciliation
    • valid
      PublishMake the data visible to consumers
    • warning
      Publish and alertOwner investigates; rule may be recalibrated
    • invalid
      Hold or quarantineConsumers keep the last good version
  3. Record evidenceRule, result, counts, run ID and owner
The data-quality lifecycle on every load. Checks run after each load and before the data is published. Severity decides the path: severe failures hold the data, warnings publish with an alert, and every outcome is recorded as evidence for monitoring and audits.

Each check below is written as a query that returns the number of violations: zero passes. The patterns are illustrative and engine-neutral where possible.

Null rate on a required key. Rule: null customer_id must be 0.

Requête SQL: Data-quality checks in practiceT-SQL
SELECT COUNT(*) AS violations
FROM silver.orders
WHERE customer_id IS NULL;
T-SQL3 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Duplicate business key. Rule: duplicate order_id must be 0.

Requête SQL: Data-quality checks in practice (2)T-SQL
SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM silver.orders
    GROUP BY order_id
    HAVING COUNT(*) > 1
) AS duplicates;
T-SQL7 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Freshness. Rule: MAX(event_timestamp) must be within X minutes of the run. The threshold comes from the freshness the consumers were promised, not from a default.

Requête SQL: Data-quality checks in practice (3)T-SQL
SELECT CASE
         WHEN MAX(event_timestamp) >= DATEADD(MINUTE, -120, SYSUTCDATETIME()) THEN 0
         ELSE 1
       END AS violations
FROM silver.order_events;
T-SQL5 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Range. Rule: amount >= 0 for sales, with refunds modelled explicitly.

Requête SQL: Data-quality checks in practice (4)T-SQL
SELECT COUNT(*) AS violations
FROM silver.orders
WHERE amount < 0 AND order_type <> 'REFUND';
T-SQL3 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Reconciliation. Rule: source count equals target count for the window, when that is the right rule.

Requête SQL: Data-quality checks in practice (5)T-SQL
-- Both counts come from the same, closed window, captured by the run.
SELECT ABS(s.row_count - t.row_count) AS violations
FROM (SELECT COUNT(*) AS row_count FROM bronze.orders WHERE batch_id = @batch_id) AS s
CROSS JOIN (SELECT COUNT(*) AS row_count FROM silver.orders WHERE batch_id = @batch_id) AS t;
T-SQL4 lignesAffichage en lecture seule. Aucune connexion à une base de données.

The same rules in PySpark, as a small reusable function that returns a result instead of raising, so the caller decides what is severe:

from pyspark.sql import DataFrame, functions as F


def quality_results(df: DataFrame, key: str, required: list[str]) -> dict[str, int]:
    results = {f"null_{col}": df.where(F.col(col).isNull()).count() for col in required}
    results[f"duplicate_{key}"] = (
        df.groupBy(key).count().where(F.col("count") > 1).count()
    )
    return results

Why exact equality is sometimes the wrong rule

Source count equals target count is a good rule for a straight copy and a misleading one almost everywhere else:

  • Filters and deduplication remove rows on purpose: the right rule is target = source − rejected − duplicates, with each term logged.
  • Moving sources keep changing while you count: count a closed window captured by the run (a batch ID, a CDC position), not “everything now”.
  • Late-arriving data makes yesterday’s count change today: reconcile with a lookback, or reconcile again later.
  • Aggregated targets have fewer rows by design: reconcile measures (sums of amounts), not rows.
  • Floating-point totals differ in the last digits: use decimals for money, and a tolerance for genuine floats.

A rule that fires every day because it is mis-specified teaches people to ignore it. A wrong rule is worse than no rule.

Schema and data-contract testing

Two related but different things:

A schema check asks: does the structure match? Required columns present, types as declared, nullability as declared. It is mechanical and belongs in CI and after every deployment.

A data contract asks: what does the producer promise consumers? It includes the schema, plus semantics (what amount means, in which currency, gross or net), keys and grain, freshness and delivery schedule, quality rules, and how changes are versioned and announced. The contract is an agreement between owners; the schema check is one of its automated tests.

Change Typical classification Why
New nullable column Non-breaking Existing consumers ignore it
New required column Breaking for producers that write the table Old writers fail or write nulls
Column removed Breaking Consumers that select it fail
Column renamed Breaking Effectively a removal plus an addition
Type widened (int → bigint) Usually non-breaking Check consumers with fixed types
Type narrowed or changed meaning Breaking Values may no longer fit, or no longer mean the same thing
Nullability relaxed Breaking for consumers that assumed non-null Joins and filters change behaviour
Same name, new meaning (gross → net) Breaking, and invisible to schema checks Only the contract and a reconciliation catch it

The last row is why a schema check is not enough: the most dangerous contract break is semantic, and it passes every structural test. Semantic changes need review by the producer and the consumers, a version change in the contract, and often a reconciliation test that would have caught the shift.

Responsibilities. The producer owns the contract, its tests and its versioning; it must not break consumers without a versioned change and notice. Consumers own their assumptions: they declare which contract version they depend on and test the parts they use. In CI/CD, the producer’s pipeline runs the contract tests on every change, so a breaking change fails in the pull request, where it is cheap, instead of in a consumer’s report on Monday morning.

A schema contract check in PySpark is a comparison with a declared StructType:

from pyspark.sql.types import DecimalType, LongType, StringType, StructField, StructType
from pyspark.testing import assertSchemaEqual

FACT_ORDERS_V2 = StructType([
    StructField("order_id", LongType(), False),
    StructField("customer_id", LongType(), False),
    StructField("amount", DecimalType(18, 2), False),
    StructField("currency", StringType(), False),
])


def test_fact_orders_contract(spark):
    actual = spark.read.table("gold.fact_orders").schema   # in a Test workspace run
    assertSchemaEqual(actual, FACT_ORDERS_V2)

Regression testing

A regression is something that used to work and no longer does. Data platforms have four kinds, and each needs a different comparison.

Regression What changed Example How to detect
Functional Output changed unexpectedly A refactored join drops orders with no shipping address Golden-dataset comparison: run old and new logic on the same input, diff the outputs
Data Quality or distribution changed unexpectedly A source change doubles the null rate of email Data-quality results and distribution metrics compared with recent history
Performance Runtime or resource use degraded A new window function adds a full shuffle; the load is 40% slower Baseline comparison on the same data and capacity
Security Permissions broadened unexpectedly A deployment adds a group to a workspace role; RLS is bypassed through a new item Effective-permission diff against the previous release; negative access tests

The golden-dataset technique is the workhorse for functional regressions in data platforms: a fixed input, the output from the current production version stored as the expected result, and every change compared against it. Differences are either bugs or intended changes; intended ones update the golden output in the same reviewed pull request.

Performance testing

There are no universal performance thresholds for data workloads. A two-hour load is fine for a monthly report and a disaster for an hourly one. What you can test reliably is change against a baseline:

Baseline → Change → Re-run → Compare → Decide.

  1. Baseline. Record the metrics for the current version on a known dataset and environment: SQL query runtime (p50 and p95 over several runs, not one), logical reads, Spark job duration and shuffle volume, pipeline end-to-end duration, file throughput, capacity consumption, and behaviour under expected concurrency.
  2. Change. Deploy the candidate to the same environment.
  3. Re-run. Same data, same capacity, comparable time of day, several runs. A single run measures noise.
  4. Compare. Per metric, against the baseline and a tolerance agreed in advance.
  5. Decide. Within tolerance: pass and record. Beyond it: block, or approve explicitly with a reason (a deliberate trade, such as more correctness for more runtime).

Shared capacities make this harder: a Fabric capacity serving other workloads produces variable timings, so either test on a dedicated test capacity or record concurrent load alongside the results. The baseline also ages: refresh it when data volume grows, or the comparison drifts. For diagnosing what you find, see Troubleshooting Microsoft Fabric Performance in Production, How to Make Microsoft Fabric Notebooks Faster and How to Find SQL Server Performance Regressions with Query Store.

Recovery testing

Recovery testing proves that the platform can be rerun, retried, replayed and rebuilt without corrupting data. The tests are concrete:

Test How What passes
Rerun Run the same window twice The target is identical after the second run
Retry mid-run Fail an activity halfway (a forced error), let the retry policy act No partial writes left behind; final state correct
Replay Reprocess a past window from retained raw data Output matches the original run, or differs only where a fix intended it
Checkpoint restart Stop a load after some partitions; restart Resumes from the watermark or checkpoint; nothing missed, nothing duplicated
Restore and rebuild Rebuild an item or table from Git and retained data in an empty workspace Data-quality and reconciliation checks pass on the rebuilt output

The incremental-load mechanics behind these tests (bounded watermarks, safe advancement, MERGE, periodic reconciliation) are in Incremental Loads in Microsoft Fabric. The platform-wide view (drills, RTO measurement, recovery order) is in the recovery testing section of the disaster-recovery guide. The point to carry over: a rerun test belongs in the pull-request or Test-environment suite of every incremental pipeline, because it is the test that decides whether the next incident is a rerun or a restatement. Testing Incremental Loads and Idempotency turns the table above into a tested suite: same batch twice, late and out-of-order rows, partial failure, batch-partition and replay invariants.

Testing and observability

Testing answers:

Is this change acceptable before release?

Observability answers:

Is the system behaving correctly after release?

They overlap more in data platforms than in applications, because production data-quality checks are both: they are tests (rules with pass and fail) and they are signals (results recorded and alerted on). Three connections make the two work together:

  • The same rules, two places. A rule defined once runs in the Test environment as a gate and in production on every load as a monitor.
  • Tests produce telemetry. Gate results, data-quality results, performance comparisons and smoke tests write to the same execution log as production runs, with the release version, so an incident can be traced back to the release that introduced it.
  • Monitoring is part of the release. A new workload without run logging, duration baselines and data-quality alerts is not production-ready, which is why “missing monitoring telemetry” is in the blocking table.

Monitoring a Data Platform: What Should You Actually Measure? and Designing an Execution Log for Microsoft Fabric Pipelines and Notebooks cover the observability side.

QA, testing and who owns quality

“QA” means different things in different organisations: a separate team that tests manually before release, a quality-assurance process, or simply “testing”. For data platforms, a manual QA phase at the end of the release can’t be the main control: it can’t see production data, it can’t run on every load, and it doesn’t scale with the number of tables. That’s why this pillar is called Testing & Quality, not QA.

Quality is still owned by people. A workable split:

Owner Owns
Developers and data engineers Unit and transformation tests for the code they write; edge cases; idempotency tests
Data engineers (as producers) Contracts, schema tests and data-quality rules for the tables they publish
Platform team The test environments, the CI/CD gates, deployment validation, smoke tests and the shared test tooling
Automated CI/CD gates Enforcing the mandatory checks, consistently, on every change
Business and data owners The meaning of the rules, thresholds and reconciliation targets; acceptance for changes that alter business numbers; the decision to publish or hold data that fails

How much testing does a pipeline need?

Not every pipeline deserves the full suite. A sandbox exploration doesn’t; a load that feeds regulated financial reporting does. Use these questions to decide, per pipeline or per change:

  1. What business failure are we protecting against? Name it: a restated revenue figure, a customer seeing another customer’s data, a missed regulatory deadline.
  2. What is the blast radius? One report, a department, every consumer of a shared table, external customers.
  3. Can the test run locally? If yes, it belongs in the pull request.
  4. Does it require Fabric or SQL infrastructure? Then it belongs in the Test environment, and the environment needs to exist and be maintained.
  5. Does it require production-like data? Then someone owns a representative or sanitized dataset.
  6. Is it deterministic? A non-deterministic test can only be advisory until it is fixed.
  7. How long does it take? Minutes run on every change; hours run nightly or before release.
  8. What evidence does it produce? A result someone can read in the release record, not a log line nobody keeps.
  9. Should failure block deployment? Decide explicitly: mandatory or advisory.
  10. Who owns the rule? A rule without an owner will be disabled the first time it is inconvenient.
  11. How is the test maintained? Fixtures and golden outputs change with the logic; budget for it.
  12. What happens when it becomes flaky? Quarantine it with an owner and a deadline, fix the cause (usually shared state, time or ordering), or delete it. Never leave it failing and ignored.

Anti-patterns

Anti-pattern Why it fails
Only testing happy paths Incidents come from duplicates, nulls and late data, not from the rows you designed for
Relying entirely on end-to-end tests Slow, expensive and hard to diagnose; they find problems late and say little about the cause
Testing with enormous datasets unnecessarily Slow feedback and hidden edge cases; five purposeful rows beat five million incidental ones
Running production data freely in development Privacy and access risk, with copies that outlive their purpose
Treating row-count equality as universal quality Counts can match while values are wrong, and legitimately differ after filters and deduplication
Ignoring late-arriving data Watermark logic silently skips facts; counts drift after the fact
No test cleanup Leftover rows make the next run pass or fail for the wrong reason
Flaky tests accepted permanently People learn to rerun until green, and the real failure gets rerun too
Performance tests without baselines A number without a comparison is not a test
Tests that never block anything If no failure can stop a release, the suite is a report, not a gate
CI that validates the build but not data behaviour Proves the artifact can deploy, not that it should
Manually verifying every release Doesn’t scale, isn’t repeatable, and stops happening under deadline pressure

Javi Builds recommendation, not a Microsoft requirement. For a production data pipeline that business decisions depend on, this is the minimum I’d want before calling it production-ready:

  • Syntax and build validation: lint, definitions parse, database project builds.
  • Unit tests for transformation logic: with explicit null, duplicate and empty-input cases.
  • Schema check: output tables match their declared contract.
  • Critical-key checks: null and duplicate checks on business keys, on every load.
  • Incremental and rerun test: the same window twice leaves the same result.
  • Integration test: the pipeline runs end to end in a test workspace against test data.
  • Security access test: one positive and one negative check per role that reads the output.
  • Deployment smoke test: items exist, bindings are correct, a small query succeeds.
  • Basic performance baseline: the load’s duration recorded, with a tolerance.
  • Monitoring verification: run logging, failure alerts and freshness alerts exist and fire.

Start there, then add data-quality depth (reconciliation, distribution) and performance regression for the pipelines with the largest blast radius.

Testing maturity model

Teams don’t reach production confidence in one step, and they don’t need the same level for every workload. The levels below describe common stages; a platform usually has workloads at different levels at the same time, and that is fine if it is deliberate.

  1. 1Level 0ManualSomeone looks at the results after a run
  2. 2Level 1Basic automatedUnit tests and simple validation on pull requests
  3. 3Level 2Pipeline qualitySchema, data-quality and integration checks in Test
  4. 4Level 3CI/CD gatesAutomated, mandatory gates block bad releases
  5. 5Level 4Production confidenceRegression, performance, security and post-deploy validation
Data platform testing maturity. Common stages, not a mandatory ladder. Each level adds a kind of evidence; the right level per workload depends on its blast radius. Level 3 is the turning point, where tests start deciding releases instead of reporting on them.

Moving up a level is usually a platform investment (a test workspace, a representative dataset, a gate in the release pipeline) more than a test-writing effort. That’s why it belongs in the architecture, not only in each team’s backlog.

Recommendation

For a production data platform on Microsoft Fabric and SQL Server:

  1. Separate code tests from data tests, and plan where each runs: code tests in the pull request, data tests in Test and in production on every load.
  2. Make logic testable first: transformation logic in functions, Warehouse schema in a database project, configuration in variable libraries. Untestable structure is the most common reason tests don’t exist.
  3. Build the gates in order of cost: static checks, unit and transformation tests, schema contracts, one versioned artifact, Test-environment integration and data-quality checks, an evidence-based release decision, production smoke tests.
  4. Decide mandatory versus advisory deliberately, with an owner for every rule.
  5. Test the unhappy paths: duplicates, nulls, late data, reruns and replays.
  6. Treat Fabric Warehouse keys as documentation, not enforcement, and test uniqueness and referential integrity as data quality.
  7. Compare performance with baselines, never with universal thresholds.
  8. Connect testing to observability: the same rules gate releases and monitor loads, and their results are recorded with the release version.

The question every gate should help answer is not “did it deploy?” but “should it be in production?”

Sources

Product behaviour checked against Microsoft Learn, the Apache Spark, pytest, Delta Lake and tSQLt documentation in November 2026. Code samples are illustrative and haven’t been run as published; check the linked pages before relying on a specific API, setting or limit. The architecture guidance (layers, gates, blocking defaults, minimum suite, maturity levels) is Javi Builds guidance, not a Microsoft requirement.

Étiquettes

  • Testing
  • CI/CD
  • Pipelines de données
  • Microsoft Fabric
  • PySpark