Inconsistent type inference

[fleupold]

We have two table with the exact scheme in Postgres:

solver_slippage=> \d+ batch_data_gnosis_2024_12
                                            Table "public.batch_data_gnosis_2024_12"
        Column        |       Type       | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 environment          | text             |           |          |         | extended |             |              | 
 auction_id           | bigint           |           |          |         | plain    |             |              | 
 block_number         | bigint           |           |          |         | plain    |             |              | 
 block_deadline       | bigint           |           |          |         | plain    |             |              | 
 tx_hash              | text             |           |          |         | extended |             |              | 
 solver               | text             |           |          |         | extended |             |              | 
 execution_cost       | double precision |           |          |         | plain    |             |              | 
 surplus              | double precision |           |          |         | plain    |             |              | 
 protocol_fee         | double precision |           |          |         | plain    |             |              | 
 network_fee          | double precision |           |          |         | plain    |             |              | 
 uncapped_payment_eth | double precision |           |          |         | plain    |             |              | 
 capped_payment       | double precision |           |          |         | plain    |             |              | 
 winning_score        | double precision |           |          |         | plain    |             |              | 
 reference_score      | double precision |           |          |         | plain    |             |              | 
Access method: heap

solver_slippage=> \d+ batch_data_ethereum_2024_12
                                           Table "public.batch_data_ethereum_2024_12"
        Column        |       Type       | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------------------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 environment          | text             |           |          |         | extended |             |              | 
 auction_id           | bigint           |           |          |         | plain    |             |              | 
 block_number         | bigint           |           |          |         | plain    |             |              | 
 block_deadline       | bigint           |           |          |         | plain    |             |              | 
 tx_hash              | text             |           |          |         | extended |             |              | 
 solver               | text             |           |          |         | extended |             |              | 
 execution_cost       | double precision |           |          |         | plain    |             |              | 
 surplus              | double precision |           |          |         | plain    |             |              | 
 protocol_fee         | double precision |           |          |         | plain    |             |              | 
 network_fee          | double precision |           |          |         | plain    |             |              | 
 uncapped_payment_eth | double precision |           |          |         | plain    |             |              | 
 capped_payment       | double precision |           |          |         | plain    |             |              | 
 winning_score        | double precision |           |          |         | plain    |             |              | 
 reference_score      | double precision |           |          |         | plain    |             |              | 
Access method: heap

which both get synced to dune with the same dune-sync config file (select * from <table>) in the same way.

Yet the dune tables (dataset_batch_data_gnosis_2024_12, dataset_batch_data_ethereum_2024_12) result in different schemas:

Note, in particular the different data type for execution_cost (varchar vs double).

This makes it impossible to later on union those tables together in a convenient way.

Would it be possible to use the underlying SQL datatypes to infer what types the upload should have?

[bh2smith]

The data types on CSV upload are fully in the hands of Dune. Note that we only provide a CSV and they have their own internal type inferences. It would probably make more sense to reach out to them (because I don't believe we have any control over this).

I can ping them on the issue...

One potential work around here is to explicitly post large integers as strings.

[bh2smith]

So, Dune responded quick with something that looks very appropriate:

Instead of using the CSV upload endpoint, you could use the create table + insert endpoints. They give you more control over the schema

https://docs.dune.com/api-reference/tables/endpoint/create

We will look into this ASAP.

[harisang]

So, Dune responded quick with something that looks very appropriate:

Instead of using the CSV upload endpoint, you could use the create table + insert endpoints. They give you more control over the schema

https://docs.dune.com/api-reference/tables/endpoint/create

We will look into this ASAP.

Thanks for looking into this! One thing to note is that the csv upload endpoint is free, while the create+insert consumes credits (not sure how many, though)

[bh2smith]

Thanks for pointing this out @harisang. Makes sense that the "good endpoint" is the one being charged for. So there are a few options:

Leave it as is

Would require some awkward manual data manipulation on the end user side. In the case described above, why not just put the data being unioned into the same place. I suppose this would require an internal union as is probably the point of #120

Implement the properly typed routes using create/insert

This would give the guarantees that one would expect and probably makes the most sense.

Support both

Given that we already have upload csv, it could remain. One would only consider using this to save on costs - which seems realistic.

I would propose that we proceed with the implementation of table/{create,insert} since this should directly resolve the issue posted here.

[fleupold]

Let's please leave the existing csv upload as well. It lends itself nicely to the "sync" use case (which otherwise requires a delete and create)

[bh2smith]

This feature has some delicate repercussions that need to be carefully evaluated and may require some coordination with Dune. Moving this to Milestone 4, we hope to post a grant proposal for continued development in the following days. This is expected to include all issues tagged with M4. Please feel free to add anything that appears to be missing.

Note that the current state of this feature is in Draft State at #129.