CrateDB

[amotl]

About

We submitted a patch the other day that added CrateDB into the mix, supporting it as both a source and destination pipeline element. Another patch intended to cover CrateDB software testing more extensively by deriving from the PostgreSQL tests, but that patch was too invasive and never got merged.

References

• Support for CrateDB bruin-data/ingestr#238
• Tests: Adjust software tests for CrateDB bruin-data/ingestr#284
• [Tests] Enable software tests for CrateDB crate-workbench/ingestr#2
• Database result transformations for better PostgreSQL compatibility crate/dlt-cratedb#54

[amotl]

Problem

The invasiveness is mostly due to constructs like this, where CrateDB's TIMESTAMP values obviously deviate from PostgreSQL's DATETIME.

# Compensate for CrateDB types and insert order.
if dest_connection_url.startswith("cratedb://"):
    assert (1, "val1", 1640995200000) in res
    assert (2, "val2", 1643673600000) in res
else:
    assert res[0] == (1, "val1", as_datetime("2022-01-01"))
    assert res[1] == (2, "val2", as_datetime("2022-02-01"))

Proposal

Use a dlt normalizer to adjust and convert database results transparently.

[amotl]

Use a dlt normalizer to adjust and convert database results transparently.

@coderabbitai came up with a suggestion for that proposal that is using psycopg2's register_type API to patch serialization on the OID level for 1114, 1184. That's a smart approach, right?

• Database result transformations for better PostgreSQL compatibility crate/dlt-cratedb#54 (comment)

[coderabbitai]

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
gh api repos/crate/dlt-cratedb/issues/comments -q '.[] | select(.id==4905035298) | .body' 2>/dev/null || \
gh issue view 54 --repo crate/dlt-cratedb --comments | sed -n '1,200p'

Length of output: 263

---

🏁 Script executed:

#!/bin/bash
gh api repos/crate/dlt-cratedb/issues/54/comments -q '.[] | "\(.id): \(.body)"' 2>&1 | head -c 4000

Length of output: 4231

---

🏁 Script executed:

#!/bin/bash
gh api repos/crate/dlt-cratedb/issues/54/comments -q '.[] | select(.id==4905035298) | .body' 2>&1 | tail -c 6000

Length of output: 6245

---

@amotl Yes, it's a solid approach. Key points:

• register_type scoped to the single connection (self._conn) in CrateDbSqlClient.open_connection() avoids polluting other psycopg2 connections in the same process — important since dlt[postgres] is a shared dependency.
• It targets the actual root cause: CrateDB reports correct PostgreSQL OIDs (1114/1184) but serializes epoch-millisecond integers instead of ISO-8601 text, so psycopg2's built-in typecaster silently passes the raw int through.
• It's a read-path fix on the connection, not a dlt normalizer (normalizers only run on the load/ingest path, not on query results from execute_sql()/execute_query()), which matches your original proposal's intent without misusing the wrong extension point.
• UTC is safe to hardcode since CrateDB stores TIMESTAMP/TIMESTAMPTZ internally as UTC.

This should let the omniload test in this issue drop the CrateDB-specific branching and assert plain datetime values like the PostgreSQL path. I offered in the linked comment to open a PR against dlt-cratedb with a test extending tests/load/cratedb/test_cratedb_client.py — happy to kick that off if you'd like it filed as a follow-up here too.

[amotl]

We've refreshed the patch, and it looks much better today than on the previous attempt, thanks to improvements in CrateDB.

• PostgreSQL compatibility: AAUIW support for storing data using the Postgres DATE data type crate/crate#19414
• Tests: Add integration tests with CrateDB #192

/cc @coderabbitai

[coderabbitai]

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
gh issue view 192 --repo panodata/omniload 2>&1 | head -c 3000
echo "---"
gh issue view 19414 --repo crate/crate 2>&1 | head -c 3000

Length of output: 1839

---

🏁 Script executed:

#!/bin/bash
gh pr view 192 --repo panodata/omniload 2>&1 | head -c 3000

Length of output: 1699

---

@amotl Great news — DATE type support landing in CrateDB (crate/crate#19414, crate/crate#19588) removes the noise that made the earlier attempt too invasive, and #192 now shows a much cleaner integration test suite (87 passed, 71 skipped).

A few observations from #192:

• The remaining 71 skips due to DestinationSchemaTampered still point to gaps in dlt-cratedb beyond the DATE fix — worth tracking against DestinationSchemaTampered errors with merge and delete+insert strategies or refresh="drop_resources": Schema content was changed by a loader or by destination code crate/dlt-cratedb#14 and Fix CrateDB merge, delete-insert, and refresh="drop_resources" crashes crate/dlt-cratedb#51 as you noted.
• Combined with the register_type OID-level fix discussed earlier for TIMESTAMP/TIMESTAMPTZ serialization (Database result transformations for better PostgreSQL compatibility crate/dlt-cratedb#54), this should let the CrateDB-specific branching in the test assertions collapse to the same plain-datetime path used for PostgreSQL.

Let me know if you'd like me to look at any specific skipped test in #192 to help narrow down the remaining DestinationSchemaTampered cases.