Ingest data

You can ingest data into Materialize from various external systems:

Sources and clusters

Materialize ingests data from external systems using sources. For the sources, you need to associate a cluster to provide the compute resources needed to ingest data.

Snapshotting

Snapshotting is the initial sync of a table’s data. It reads from the upstream system and writes the data into Materialize’s storage. The initial snapshot is committed to storage atomically, with all records assigned the same ingestion timestamp.

When snapshotting occurs

When snapshotting occurs depends on the syntax.

• With the legacy CREATE SOURCE ... FOR <ALL TABLES|TABLES|SCHEMAS>, you run a single statement to create both the source and the tables that ingest data. Snapshotting begins when you run the statement. For an existing source, the legacy ALTER SOURCE ... ADD SUBSOURCE starts the snapshotting for the added table.

• With the source-versioning syntax, you create the source and its tables separately using CREATE SOURCE ... and CREATE TABLE ... FROM SOURCE. Snapshotting begins when you run CREATE TABLE ... FROM SOURCE.

Duration

The duration of the snapshotting operation depends on the volume of data in the initial snapshot and the size of the cluster where the source is hosted. Large upsert sources, in particular, can take hours to snapshot.

To reduce the operational burden of snapshotting on the upstream system and ensure you are only bringing in the volume of data that you need in Materialize, we recommend:

• If possible, running source creation operations during off-peak hours to minimize operational risk in both the upstream system and Materialize.

• Limiting the volume of data that is synced into Materialize on source creation. This will help speed up snapshotting, as well as make data exploration more lightweight. See Limit the volume of data for best practices.

• For upsert sources, overprovisioning the source cluster for snapshotting, then right-sizing once the snapshot is complete and you have a better grasp on the steady-state resource needs of your upsert source(s). See Best practices: Upsert sources.

Monitoring progress

While snapshotting is taking place, you can monitor the progress of the operation in the overview page for the source in the Materialize Console. Alternatively, you can manually keep track of using information from the system catalog. See Monitoring the snapshotting progress for guidance.

It’s also important to monitor CPU and memory utilization for the cluster hosting the source during snapshotting. If there are signs of resource exhaustion, you may need to resize the cluster.

Queries during snapshotting

Queries on a table that is snapshotting are blocked until its snapshot completes.

• With the legacy CREATE syntax:

  • None of the tables created as part of CREATE SOURCE ... FOR ... are queryable until they have all finished snapshotting.

  • When altering a source to add a new table (ALTER SOURCE ... ADD SUBSOURCE), only the new table snapshots. The source’s other tables remain queryable. However, ingestion for these tables is temporarily blocked, so they stop advancing until the snapshot completes.

• With the source-versioning CREATE TABLE FROM SOURCE syntax:

  • None of the tables created within a transaction block are queryable until all their snapshots complete.

  • When you create new tables from a source that already has tables, only the new tables snapshot. The source’s existing tables remain queryable. However, ingestion for the existing tables is temporarily blocked, so they stop advancing until the snapshots for the new tables complete.

Modifying an existing source

When you create additional tables for a source that already has tables ingesting data, ingestion for the existing tables is blocked while the new tables snapshot. The existing tables remain queryable, but they stop advancing until the new tables’ snapshots complete.

If possible, resize the cluster to speed up the snapshot, then right-size it once snapshotting completes.

Running/steady-state

Once snapshotting completes, Materialize transitions to Running state. During this state, Materialize continually ingests changes from the upstream system.

Queries during steady-state

Although Materialize is continually ingesting changes from the upstream system, depending on the volume of the upstream changes, Materialize may lag behind the upstream system. If the lag is significant, queries may block until Materialize has caught up sufficiently with the upstream system when using the default isolation level of strict serializability.

In the Materialize Console, you can see a source’s data freshness from the Data Explorer screen. Alternatively, you can run a query to monitor the lag. See Monitoring hydration/data freshness status.

Hydration

When a cluster is restarted (such as after resizing), certain objects on that cluster (such as sources, indexes, materialized views, and sinks) undergo hydration. Hydration refers to the reconstruction of in-memory state by reading data from Materialize’s storage layer; hydration does not require reading data from the upstream system.

Process

During hydration, data from Materialize’s storage layer is read to reconstruct the in-memory state of the object. As part of the hydration process:

• Internal data structures are re-created.

• Various processes are re-initiated. These processes may also require re-reading of their in-memory state.

Duration

For a source, the duration of its hydration depends on the type and the size of the source; e.g., large UPSERT sources can take hours to complete.

Queries during hydration

During hydration, queries usually block until the process has been completed.

Best practices

The following lists some general best practice guidelines as well as additional guidelines for upsert sources.

Scheduling

If possible, schedule creating new sources during off-peak hours to mitigate the impact of snapshotting on both the upstream system and the Materialize cluster.

Dedicate a cluster for the sources

If possible, dedicate a cluster just for sources. That is, avoid using the same cluster for sources and sinks/indexes/materialized views (and other compute objects).

Limit the volume of data

If possible, limit the volume of data that needs to be synced into Materialize on source creation. This will help speed up snapshotting as well as make data exploration more lightweight.

For example, when creating a PostgreSQL source, you may want to create a publication with specific tables rather than for all tables in the database.

Once the data is in Materialize, you can further reduce the size of the data maintained by your view definitions.

Upsert sources

In addition to the general best practices, the following additional best practices apply to upsert sources.

Use a larger cluster for upsert source snapshotting

When you create a new source, Materialize performs a one-time snapshotting operation to initially populate the source in Materialize. For upsert sources, snapshotting is a resource-intensive operation that can require a significant amount of CPU and memory.

Consider using a larger cluster size during snapshotting for upsert sources. Once the snapshotting operation is complete, you can downsize the cluster to align with the steady-state ingestion.

If the cluster hosting the source restarts during snapshotting (e.g., because it ran out of memory), you can scale up to a larger size to complete the operation.

ALTER CLUSTER <cluster_name> SET ( SIZE = <new_size> );

Once the initial snapshot has completed, you can resize the cluster for steady state.

Right-size the cluster for steady-state

Once the initial snapshot has completed, you can resize the cluster to align with the volume of changes being replicated from your upstream in steady-state.

ALTER CLUSTER <cluster_name> SET ( SIZE = <new_size> );

See also

• Monitoring data ingestion
• Troubleshooting data ingestion