Pika Protocol v4 added for optimism #7427

PatelPrinci · 2025-01-07T06:21:38Z

Thank you for contributing to Spellbook 🪄

Please open the PR in draft and mark as ready when you want to request a review.

Description:

[...]

quick links for more information:

0xRobin · 2025-01-07T08:33:52Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+		WHEN productId = UINT256 '1' OR productId = UINT256 '16' THEN 'ETH'
+		WHEN productId = UINT256 '2' OR productId = UINT256 '17' THEN 'BTC'
+		WHEN productId = UINT256 '3' OR productId = UINT256 '18' THEN 'LINK'
+		WHEN productId = UINT256 '4' OR productId = UINT256 '19' THEN 'SNX'
+		WHEN productId = UINT256 '5' OR productId = UINT256 '20' THEN 'SOL'
+		WHEN productId = UINT256 '6' OR productId = UINT256 '21' THEN 'AVAX'
+		WHEN productId = UINT256 '7' OR productId = UINT256 '22'  THEN 'MATIC'
+		WHEN productId = UINT256 '8' THEN 'LUNA'
+		WHEN productId = UINT256 '9' OR productId = UINT256 '23' THEN 'AAVE'
+		WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE'
+		WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS'
+		WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI'
+		ELSE CONCAT ('product_id_', CAST(productId as VARCHAR))
+		END AS virtual_asset
+
+		,CASE
+		WHEN productId = UINT256 '1' OR productId = UINT256 '16' THEN 'ETH'
+		WHEN productId = UINT256 '2' OR productId = UINT256 '17' THEN 'BTC'
+		WHEN productId = UINT256 '3' OR productId = UINT256 '18' THEN 'LINK'
+		WHEN productId = UINT256 '4' OR productId = UINT256 '19' THEN 'SNX'
+		WHEN productId = UINT256 '5' OR productId = UINT256 '20' THEN 'SOL'
+		WHEN productId = UINT256 '6' OR productId = UINT256 '21' THEN 'AVAX'
+		WHEN productId = UINT256 '7' OR productId = UINT256 '22'  THEN 'MATIC'


Is there no way of getting these productId <> token symbols from onchain data?
If not I would make a separate model containing the static mapping and then join it in here.

Is there no way of getting these productId <> token symbols from onchain data? If not I would make a separate model containing the static mapping and then join it in here.

No, the productId and token symbols are not coming from on-chain data. They are part of a static mapping defined within the protocol's architecture, which cannot be queried directly on-chain.

I agree with your suggestion—it makes sense to create a separate model containing this static mapping and join it here for better maintainability and clarity. This will also simplify any future updates if new mappings are introduced.

Hosuke

We may need to add some seed for testing pika_v4_optimism_perpetual_trades, you can add the seed here:
https://github.com/duneanalytics/spellbook/blob/d09fa392c8e07e6443cf26c4e59c97f1c7e41e8d/dbt_subprojects/hourly_spellbook/seeds/_sector/perpetual/trades/perpetual_trades_seed.csv

Hosuke · 2025-01-07T09:05:31Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+    post_hook='{{ expose_spells(\'["optimism"]\',
+                                "project",
+                                "pika",
+                                \'["msilb7", "drethereum", "rplust","princi"]\') }}'


We can apply the new post_hook format here.
i.e.

spellbook/dbt_subprojects/dex/models/_projects/lifi/lifi_trades.sql

Line 4 in d09fa39

post_hook='{{ expose_spells(blockchains = \'["fantom", "optimism"]\',

Hosuke · 2025-01-07T09:06:45Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+		,'4' AS version
+	FROM {{ source('pika_protocol_v4_optimism', 'PikaPerpV4_evt_NewPosition') }}
+	{% if is_incremental() %}
+	WHERE evt_block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)


We can use incremental_predicate for incremental filters:

Suggested change

WHERE evt_block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)

WHERE {{incremental_predicate('evt_block_time')}}

Hosuke · 2025-01-07T09:07:03Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+		,'4' AS version
+	FROM {{ source('pika_protocol_v4_optimism', 'PikaPerpV4_evt_ClosePosition') }}
+	{% if is_incremental() %}
+	WHERE evt_block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)


Same as above.

Hosuke · 2025-01-07T09:07:14Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+	AND tx.block_time >= DATE '{{project_start_date}}'
+	{% endif %}
+	{% if is_incremental() %}
+	AND tx.block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)


Same as above.

…ncremental filters

…redicate for increamental filters

Hosuke · 2025-01-10T13:36:12Z

Please apply the incremental_predicate in incremental filters.

Hosuke · 2025-01-18T06:07:03Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+	AND tx.block_time >= DATE '{{project_start_date}}'
+	{% endif %}
+	{% if is_incremental() %}
+	AND tx.block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)


Please fix the incremental filter.

Hosuke · 2025-01-18T11:53:26Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+	unique_key = ['block_date', 'blockchain', 'project', 'version', 'tx_hash', 'evt_index'],
+    post_hook='{{ expose_spells(blockchains = \'["optimism"]\',


Please be consistent in tab or space in all PRs. We prefer spaces.

Hosuke · 2025-01-18T11:54:05Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+		WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE'
+		WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS'
+		WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI'
+		ELSE CONCAT ('product_id_', CAST(productId as VARCHAR))


Maybe

Suggested change

ELSE CONCAT ('product_id_', CAST(productId as VARCHAR))

'product_id_' || productId

Hosuke · 2025-01-18T11:57:29Z

...ts/hourly_spellbook/models/_sector/perpetual/projects/pika/optimism/pika_optimism_schema.yml

+    data_tests:
+      - dbt_utils.unique_combination_of_columns:


Suggested change

data_tests:

- dbt_utils.unique_combination_of_columns:

data_tests:

- check_perpetuals_seed:

blockchain: optimism

project: Pika

version: 4

- dbt_utils.unique_combination_of_columns:

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

Hosuke · 2025-01-18T13:25:36Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+            WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE'
+            WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS'
+            WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI'
+            ELSE 'product_id_' || productId


Suggested change

ELSE 'product_id_' || productId

ELSE 'product_id_' || CAST(productId AS VARCHAR)

Hosuke · 2025-01-18T13:25:58Z

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql

+            WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE'
+            WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS'
+            WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI'
+            ELSE 'product_id_' || productId


Suggested change

ELSE 'product_id_' || productId

ELSE 'product_id_' || CAST(productId AS VARCHAR)

Pika Protocol v4 added for optimism

4aa160b

github-actions bot marked this pull request as draft January 7, 2025 06:21

github-actions bot added WIP work in progress dbt: hourly covers the hourly dbt subproject labels Jan 7, 2025

PatelPrinci marked this pull request as ready for review January 7, 2025 06:31

github-actions bot added ready-for-review this PR development is complete, please review and removed WIP work in progress labels Jan 7, 2025

Merge branch 'main' into pika-protocol-v4-optimism

bfbcd20

0xRobin assigned 0xRobin and Hosuke Jan 7, 2025

0xRobin requested review from Hosuke and 0xRobin January 7, 2025 08:32

0xRobin reviewed Jan 7, 2025

View reviewed changes

Hosuke requested changes Jan 7, 2025

View reviewed changes

Hosuke added in review Assignee is currently reviewing the PR and removed ready-for-review this PR development is complete, please review labels Jan 7, 2025

PatelPrinci added 10 commits January 7, 2025 14:59

changed post_hook format and replaced the incremental_predicate for i…

2a2f5a9

…ncremental filters

Applied the new post_hook in the config and changed the incremental_p…

5baece0

…redicate for increamental filters

Seed File Data added for V4

5733c18

Fixed valid DATE literal issue

53390ef

Fixing evt block time time column issue

aef0807

adding previous version of incremental filter

93150e6

adding previous version of incremental filter

f5acf5b

Merge branch 'main' into pika-protocol-v4-optimism

758fb23

nothing changed

d4f8a83

Merge branch 'main' into pika-protocol-v4-optimism

6fee002

PatelPrinci added 3 commits January 16, 2025 15:26

added incremental predicate

cd8eab6

added incremental filters

7908520

removed unwanted character

9f7007c

Hosuke requested changes Jan 18, 2025

View reviewed changes

PatelPrinci added 2 commits January 18, 2025 12:01

added incremental filter in last

7377220

Merge branch 'main' into pika-protocol-v4-optimism

d5e6af4

Hosuke reviewed Jan 18, 2025

View reviewed changes

Hosuke requested changes Jan 18, 2025

View reviewed changes

PatelPrinci added 2 commits January 18, 2025 17:40

fix space indentication error

6169aec

fix space indentication error

39a9598

Hosuke reviewed Jan 18, 2025

View reviewed changes

...llbook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql Show resolved Hide resolved

fix product id issue

1c59300

Hosuke requested changes Jan 18, 2025

View reviewed changes

added cast to else part of product id case

79f5b12

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Pika Protocol v4 added for optimism #7427

Pika Protocol v4 added for optimism #7427

PatelPrinci commented Jan 7, 2025

0xRobin Jan 7, 2025

PatelPrinci Jan 7, 2025

Hosuke left a comment •

edited

Loading

Hosuke Jan 7, 2025

Hosuke Jan 7, 2025

Hosuke Jan 7, 2025

Hosuke Jan 7, 2025

Hosuke commented Jan 10, 2025

Hosuke Jan 18, 2025

Hosuke Jan 18, 2025 •

edited

Loading

Hosuke Jan 18, 2025

Hosuke Jan 18, 2025

Hosuke Jan 18, 2025

Hosuke Jan 18, 2025

	WHERE evt_block_time >= DATE_TRUNC('DAY', NOW() - INTERVAL '7' Day)
	WHERE {{incremental_predicate('evt_block_time')}}

		unique_key = ['block_date', 'blockchain', 'project', 'version', 'tx_hash', 'evt_index'],
		post_hook='{{ expose_spells(blockchains = \'["optimism"]\',

	ELSE CONCAT ('product_id_', CAST(productId as VARCHAR))
	'product_id_' \|\| productId

	ELSE 'product_id_' \|\| productId
	ELSE 'product_id_' \|\| CAST(productId AS VARCHAR)

Pika Protocol v4 added for optimism #7427

Are you sure you want to change the base?

Pika Protocol v4 added for optimism #7427

Conversation

PatelPrinci commented Jan 7, 2025

Thank you for contributing to Spellbook 🪄

Description:

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Hosuke left a comment • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Hosuke commented Jan 10, 2025

Choose a reason for hiding this comment

Hosuke Jan 18, 2025 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Hosuke left a comment •

edited

Loading

Hosuke Jan 18, 2025 •

edited

Loading