dlt loading and Singer state-protocol adapters¶
dlt route and configuration¶
Prior validation ran dlt 1.31.0 extract/normalize/load with the official SQLAlchemy destination. Binary hex encoding is an explicit adapter. Incremental merge generates SQL; the historical cross-database same-name correlated DELETE report is matrixone#29876. Passing append does not establish merge/delete support.
Complete the shared connection and install dlt[sqlalchemy] pymysql. This example uses a new dataset doc_dlt_demo, which the destination may implement as a separate database requiring privileges, with a dedicated local pipeline-state directory. Preserve state during recovery.
from decimal import Decimal
import dlt
from dlt.destinations import sqlalchemy
from mo_common import engine
e = engine()
try:
uri = e.url.render_as_string(hide_password=False)
pipeline = dlt.pipeline(pipeline_name="doc_dlt_demo",pipelines_dir="dlt-state",
destination=sqlalchemy(credentials=uri),dataset_name="doc_dlt_demo")
rows = [{"id":601,"amount":Decimal("12345678901234567890.123456789012345678"),
"payload_hex":"00ff80","label":"dlt😀"}]
columns = {"id":{"data_type":"bigint","nullable":False},
"amount":{"data_type":"decimal","precision":38,"scale":18},
"payload_hex":{"data_type":"text"},"label":{"data_type":"text"}}
info = pipeline.run(rows,table_name="records",write_disposition="append",columns=columns)
print(info)
finally:
e.dispose()
dlt merge, delete and schema¶
After checking loaded packages/failed jobs, read Decimal, decoded UNHEX(payload_hex) bytes and label through a fresh SQL connection. Replaying this append can duplicate rows and supplies no CDC/delete semantics. For merge, specify primary_key, dedup ordering and hard_delete columns, then verify updates, deletes and complete-batch reads; load_info success is insufficient.
Qualify wide dates, Decimal precision, raw binary and generated destination DDL separately. Check schema updates and resume from old state for new fields. Configure remote TLS through supported SQLAlchemy destination credentials and qualify it separately: reusing this local URL does not inherit all shared engine connect_args.
Singer: tap, target and state contract¶
Prior validation used real Singer SDK/protocol with an explicit custom SQL target. It does not certify every third-party Singer target. Record tap/SDK/target versions, run catalog discovery, then save selected streams, key_properties and replication_key.
The three JSON lines below illustrate SCHEMA, RECORD and STATE. Preserve 64-bit ID/decimal as text and binary as hex. deleted is a custom contract the target must apply; a standard Singer RECORD does not guarantee deletion.
{"type":"SCHEMA","stream":"lake_events","key_properties":["event_seq"],"schema":{"type":"object","properties":{"event_seq":{"type":"integer"},"id":{"type":"string"},"amount_text":{"type":["string","null"]},"binary_hex":{"type":["string","null"]},"deleted":{"type":"boolean"}}}}
{"type":"RECORD","stream":"lake_events","record":{"event_seq":1,"id":"9007199254740993","amount_text":"0.000000000000000001","binary_hex":"00ff80","deleted":false}}
{"type":"STATE","value":{"event_seq":1}}
Target and resume acceptance¶
A custom target first validates schema, decoding and parameter binding, then applies SQL effects and an event-deduplication receipt in one transaction. A business key differs from an event ID: replay must not repeat increments, and the same ID with different content should fail rather than be silently ignored. Emit the corresponding STATE only after durable SQL commit. A crash before upstream records STATE can still replay, so test fresh-process resume from saved state.
Retain tap input, target-generated SQL, commit receipts, STATE output, before/after oracles and failures. Exercise failure before commit, after commit but before STATE, schema changes and bad-record isolation. The presence of STATE in a protocol is not cross-system exactly-once.