Skip to content

Instantly share code, notes, and snippets.

@ochaloup

ochaloup/1. fact_validators

Last active September 5, 2025 16:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ochaloup/0870e4f599396b24b247f54d06412287 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ochaloup/0870e4f599396b24b247f54d06412287 to your computer and use it in GitHub Desktop.
Download ZIP
Snowflake investigation on validator identity switching
Raw
1. fact_validators
SELECT * FROM solana.gov.fact_validators
WHERE vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY' and epoch >= 830
ORDER BY epoch DESC;
EPOCH NODE_PUBKEY VOTE_PUBKEY ACTIVE_STAKE
840 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 98717320
839 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 98717320
836 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
835 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
834 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
834 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
833 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
832 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
832 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
831 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
830 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
830 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
Raw
2. fact_vote_accounts
SELECT * FROM solana.gov.fact_vote_accounts
WHERE vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY' and epoch >= 830
ORDER BY epoch DESC;
EPOCH VOTE_PUBKEY NODE_PUBKEY AUTHORIZED_VOTER AUTHORIZED_WITHDRAWER
844 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
843 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
842 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
841 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
840 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
839 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
838 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
837 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
836 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
835 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
834 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
833 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
832 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
831 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
830 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
Raw
3. join
SELECT
FLOOR(r.block_id / 432000) as epoch,
r.pubkey,
v.vote_pubkey,
v.node_pubkey,
v.active_stake,
COUNT(r.pubkey) as reward_count,
SUM(r.REWARD_AMOUNT_SOL) as total_rewards
FROM solana.gov.fact_rewards_fee r
INNER JOIN solana.gov.fact_validators v
ON r.pubkey = v.node_pubkey
AND FLOOR(r.block_id / 432000) = v.epoch
WHERE
FLOOR(r.block_id / 432000) >= 830
AND v.epoch >= 830
AND v.vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY'
GROUP BY
FLOOR(r.block_id / 432000),
r.pubkey,
v.vote_pubkey,
v.node_pubkey,
v.active_stake,
v.epoch
ORDER BY FLOOR(r.block_id / 432000) DESC;
EPOCH PUBKEY VOTE_PUBKEY NODE_PUBKEY ACTIVE_STAKE REWARD_COUNT TOTAL_REWARDS
839 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 98717320 100 3.25904397
836 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 85485258112653 76 1.780778256
834 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 85485258112653 88 0.807421869
832 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 85485258112653 88 2.822731572
830 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk 85485258112653 112 2.765519092
Raw
4. curl get schedule
epoch=835
slot=$((epoch*432000)) # first slot in epoch
export RPC_URL=...
# epoch 835
vote account=oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY
identity=HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd
identity=5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF
curl $RPC_URL -s -X \
POST -H "Content-Type: application/json" -d '
{
"jsonrpc": "2.0",
"id": 1,
"method": "getLeaderSchedule",
"params": [
'$slot',
{
"commitment": "finalized",
"identity": "'$identity'"
}
]
}
'
# 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF
{"jsonrpc":"2.0","result":{"5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF":[10056,10057,10058,10059,32516,32517,32518,32519,58648,58649,58650,58651,70048,70049,70050,70051,72296,72297,72298,72299,73368,73369,73370,73371,79764,79765,79766,79767,90936,90937,90938,90939,105264,105265,105266,105267,143672,143673,143674,143675,203228,203229,203230,203231,210208,210209,210210,210211,221320,221321,221322,221323,261940,261941,261942,261943,336404,336405,336406,336407,353960,353961,353962,353963,363888,363889,363890,363891,366040,366041,366042,366043,380724,380725,380726,380727,381212,381213,381214,381215,414400,414401,414402,414403,430512,430513,430514,430515]},"id":1}
# HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd
{"jsonrpc":"2.0","result":null,"id":1}
curl $RPC_URL -s -X \
POST -H "Content-Type: application/json" -d '
{
"jsonrpc": "2.0",
"id": 1,
"method": "getLeaderSchedule",
"params": [
'$slot',
{
"commitment": "finalized"
}
]
}
'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment