Principle:MaterializeInc Materialize Multi Backend Data Ingestion

Knowledge Sources	MaterializeInc_Materialize
Domains	Testing, Data_Ingestion
Last Updated	2026-02-08 00:00 GMT

Overview

A data generation and ingestion abstraction that decouples workload definitions from backend-specific execution, enabling the same test workloads to run against Kafka, PostgreSQL, MySQL, SQL Server, and direct SQL backends.

Description

The Multi-Backend Data Ingestion principle establishes a layered separation between what data operations to perform and how to perform them on each backend system. In the Materialize codebase, this principle is realized through a four-tier architecture:

1. Abstract data model: Row, RowList, and Transaction form a backend-agnostic representation of data mutations. A Row carries typed field values and an Operation enum (INSERT, UPSERT, DELETE) without any knowledge of how the operation will be executed.
2. Workload definitions: Definition subclasses (Insert, Upsert, Delete) and TransactionDef generate streams of Transactions from schema metadata. Workload classes compose these definitions into repeatable test scenarios (e.g., "continuously upsert a single key" or "bulk insert then bulk delete"). None of these classes reference any specific database or messaging system.
3. Type abstraction: Each DataType subclass encapsulates both value generation (random and deterministic) and backend-specific type name resolution. The name(backend) method maps a single logical type to the correct DDL syntax for each backend (e.g., Long maps to bigint in Materialize/PostgreSQL, long in Avro, integer in JSON). Backend-specific exclusion sets (DATA_TYPES_FOR_AVRO, DATA_TYPES_FOR_MYSQL, DATA_TYPES_FOR_SQL_SERVER) handle types that are unsupported on certain backends.
4. Executor polymorphism: A common Executor base class defines the create() and run(transaction) interface. Each subclass (KafkaExecutor, PgExecutor, MySqlExecutor, SqlServerExecutor, KafkaRoundtripExecutor) translates the abstract Transaction into backend-native operations -- Avro-serialized Kafka messages, PostgreSQL DML with ON CONFLICT, MySQL DML with ON DUPLICATE KEY UPDATE, SQL Server MERGE statements, or Materialize table writes sunk through Kafka and read back.

This separation means that adding a new test workload requires zero backend knowledge, and adding a new backend executor requires zero workload knowledge.

Usage

Apply this principle when:

• Designing cross-backend test suites: Any test that must verify consistent behavior across multiple ingestion paths should define operations in terms of the abstract data model (Row, Transaction) rather than backend-specific SQL or serialization formats.
• Adding new ingestion backends: Implement a new Executor subclass that translates the existing Transaction/Row abstraction into the new backend's protocol. All existing workloads will immediately be testable against the new backend.
• Adding new test scenarios: Define a new Workload subclass with a cycle of TransactionDefs. All existing executor backends will run the scenario without modification.
• Injecting infrastructure disruptions: Use specialized TransactionDef subclasses (RestartMz, ZeroDowntimeDeploy) to interleave failure modes with data operations, verifying that ingestion correctness is maintained through restarts and rolling upgrades.

Theoretical Basis

The Backend Abstraction Pattern

The framework implements a variant of the Strategy pattern combined with the Template Method pattern. The core insight is that data ingestion into Materialize always follows the same logical flow regardless of source system:

1. Schema creation: Define a table structure with typed key and value columns.
2. Source wiring: Connect Materialize to the external data source (Kafka topic, PostgreSQL publication, MySQL connection, SQL Server CDC table).
3. Data mutation: Execute a sequence of INSERT, UPSERT, and DELETE operations against the source system.
4. Verification: Compare the Materialize view of the data against a ground truth.

Each Executor subclass implements steps 1-3 in a backend-specific way, while the execute_workload() orchestrator handles step 4 uniformly. The Backend enum (AVRO, JSON, POSTGRES, MYSQL, SQL_SERVER, MATERIALIZE) serves as the discriminant for type-name resolution, enabling the same Field definition to produce correct DDL on any target.

Key Design Decisions

Deterministic reproducibility: DataType methods accept a random.Random instance rather than using global randomness. Combined with sorted type lists (sorted(list(all_subclasses(DataType)), key=repr)), this ensures that a given seed produces identical test runs.

Boundary value testing: Each DataType's random_value() method has a 10% chance (or 1% for temporal types) of generating extreme boundary values (MIN/MAX integers, epoch dates, max intervals), improving edge case coverage without requiring dedicated boundary tests.

In-query vs. raw values: The in_query parameter on random_value() and numeric_value() controls whether values are returned as raw Python objects (for Kafka/Avro serialization) or as SQL-cast string literals (e.g., 42::int, TIMESTAMP '2024-01-01') suitable for direct inclusion in SQL statements.

Ground truth via PostgreSQL: The PgExecutor always runs as a baseline alongside other executors. After the workload completes, the PostgreSQL table state is queried and compared against each Materialize source table using REAL_TIME_RECENCY to ensure fresh reads. This provides a single, trusted reference point for correctness.

Composable disruptions: RestartMz and ZeroDowntimeDeploy are modeled as TransactionDef subclasses that yield None instead of a Transaction. This allows disruptions to be inserted into any workload cycle alongside data operations without special-casing the generation loop.

Related Pages

• Implementation:MaterializeInc_Materialize_Data_Ingest_Framework