Build a reliable blockchain-to-SQL pipeline

Begin with the consumer's question and the kind of data it reads. An append-only activity archive and a table of current positions have different update requirements. A reporting application and an operational alert also have different tolerances for provisional information.

Define freshness from the source event through to the query result. A fast stream does not establish how soon data becomes queryable after buffering, transformation and database work. Keep the timestamps for these stages distinguishable in your operating model.

A Substreams sink connects transformed output to a destination. Official and community-maintained sinks have different support arrangements, so establish who maintains the selected connector and who operates it in your environment.

The current Substreams documentation includes SQL sinks in the main CLI, with PostgreSQL and ClickHouse commands. Use the installation and sink documentation for your installed release; older tutorials may refer to a separate SQL binary.

The documented mapping options are relational mappings from annotated Protobuf and Database Changes mode through a db_out module. The former is currently described as insert-only, while the latter supports explicit inserts, updates and upserts.

Choose from the entity model, not only the shortest setup path. If a position changes after creation, explain where those changes are represented. If you choose an event archive, make clear which later process derives the current state.

Keep the schema, output module and sink configuration under the same release record. Record the versions actually deployed and retain links to their documentation. A supported database name does not mean all mapping modes and correction paths behave identically.

Durable progress should identify data that the destination has accepted. If a consumer records progress before a write succeeds, recovery can skip work. If it writes data without advancing progress, it can receive that work again. Both situations need an explicit design.

Assign one owner to each guarantee

• Durable checkpoint: the component that persists the resume position.
• Repeated delivery: the key or operation that gives a repeated input its intended effect.
• Write failures: the handling that preserves rejected records and stops silent loss.
• Recovery: the procedure that reconnects stored data with its last durable progress.

Substreams supplies cursors for resume behaviour. With a custom consumer, preserve the full cursor as required by the protocol. When using an existing sink, understand its progress storage and restore requirements rather than maintaining an unrelated application checkpoint.

For a transactional destination, consider committing related data and checkpoint changes together. For destinations or connectors without that atomic boundary, define how recovery detects a repeated batch and reconciles incomplete work. A transaction hash alone may be too coarse if one transaction produces several rows; derive identity from the actual output model.

A chain reorganization replaces previously accepted history. If your application consumes provisional data, identify every derived table and downstream consumer that needs to reflect that correction.

The SQL sink's documented Database Changes mode records reversible operations, handles undo signals and removes obsolete history after finality. It requires primary keys. The documentation also notes delayed reorganization handling in ClickHouse and gives less detail for the other modes. Confirm the behaviour of the specific mode and release you deploy.

Make the product decision explicit

Recovery and finality are related but different. Resuming a stream after a network interruption does not, by itself, reverse changes already written from an invalidated block. Keep the two responsibilities visible in the operating plan.

Include database history growth and cleanup in routine observation. A destination that retains reversible history needs enough capacity for that history alongside the application tables.

Observe the complete path before increasing concurrency. Distinguish extraction throughput, transformation time, queued output, database write time and queryable freshness. Increasing upstream delivery can enlarge a queue if database capacity stays unchanged.

PostgreSQL's bulk-loading guidance explains the overhead of individual commits and the role of COPY in large imports. Those techniques are relevant when you own the loading process; they are not instructions to bypass a sink's checkpoint or correction mechanism.

ClickHouse documents how fewer, larger inserts reduce part creation and merge pressure. Select batching behaviour together with the freshness requirement. An application that needs earlier visibility may need a different balance from a historical import.

Build the cost model from the workload. Separate the initial historical import from continuous operation and from future rebuilds. Include the destination and the team's maintenance work alongside the extraction service.

Ask the service provider how spending controls behave: an alert, a request limit and a hard billing cap are different mechanisms. Record the current plan and terms for the account being evaluated. Avoid building a budget from a historical anecdote about someone else's usage.

Assign an owner to lag, failed writes, checkpoint storage, schema changes and corrections. Provide enough context in each alert to distinguish an unavailable source from an overloaded destination. Keep the last known good configuration available for recovery.

For a discussion with StreamingFast, bring the required networks, historical scope, output shape, destination, freshness target and expected consumption pattern. This gives the team a concrete basis for assessing a deployment and its operating assumptions.