Attestation buildup

Show all code

Attestation buildup analysis showing how validator attestations accumulate over time. Per EIP-7045 (Deneb), attestations can be included through the end of the next epoch, giving a variable window of 32-64 slots depending on position within the epoch.

Show code

display_sql("attestation_buildup", target_date)

WITH first_inclusions AS (
    -- Find the first block where each validator's attestation was included
    SELECT
        slot,
        epoch,
        slot_start_date_time,
        validator,
        min(block_slot) AS first_block_slot
    FROM default.canonical_beacon_elaborated_attestation
    ARRAY JOIN validators AS validator
    WHERE meta_network_name = 'mainnet'
      AND slot_start_date_time >= '2026-01-29' AND slot_start_date_time < '2026-01-29'::date + INTERVAL 1 DAY
      AND block_slot - slot BETWEEN 1 AND 64
    GROUP BY slot, epoch, slot_start_date_time, validator
),

attestation_counts AS (
    -- Count validators at each inclusion delay (based on first inclusion only)
    SELECT
        slot,
        epoch,
        slot_start_date_time,
        first_block_slot - slot AS inclusion_delay,
        count() AS validators_at_delay
    FROM first_inclusions
    GROUP BY slot, epoch, slot_start_date_time, inclusion_delay
),

running_totals AS (
    SELECT
        slot,
        epoch,
        slot_start_date_time,
        inclusion_delay,
        validators_at_delay,
        sum(validators_at_delay) OVER (PARTITION BY slot ORDER BY inclusion_delay) AS cumulative_validators,
        sum(validators_at_delay) OVER (PARTITION BY slot) AS total_validators
    FROM attestation_counts
),

blobs AS (
    SELECT
        slot,
        count(DISTINCT blob_index) AS blob_count
    FROM default.canonical_beacon_blob_sidecar
    WHERE meta_network_name = 'mainnet'
      AND slot_start_date_time >= '2026-01-29' AND slot_start_date_time < '2026-01-29'::date + INTERVAL 1 DAY
    GROUP BY slot
),

block_sizes AS (
    SELECT
        slot,
        min(message_size) AS block_size_bytes,
        min(propagation_slot_start_diff) AS block_first_seen_ms
    FROM default.libp2p_gossipsub_beacon_block
    WHERE meta_network_name = 'mainnet'
      AND slot_start_date_time >= '2026-01-29' AND slot_start_date_time < '2026-01-29'::date + INTERVAL 1 DAY
    GROUP BY slot
)

SELECT
    r.slot AS slot,
    r.epoch AS epoch,
    r.slot_start_date_time AS time,
    r.inclusion_delay AS inclusion_delay,
    r.validators_at_delay AS validators_at_delay,
    r.cumulative_validators AS cumulative_validators,
    r.total_validators AS total_validators,
    round(r.cumulative_validators * 100.0 / nullif(r.total_validators, 0), 4) AS cumulative_pct,
    coalesce(b.blob_count, 0) AS blob_count,
    coalesce(bs.block_size_bytes, 0) AS block_size_bytes,
    coalesce(bs.block_first_seen_ms, 0) AS block_first_seen_ms
FROM running_totals r
GLOBAL LEFT JOIN blobs b ON r.slot = b.slot
GLOBAL LEFT JOIN block_sizes bs ON r.slot = bs.slot
ORDER BY r.slot, r.inclusion_delay

Show code

df = load_parquet("attestation_buildup", target_date)

# Create a dense grid: fill missing delays with previous cumulative value
slots = df["slot"].unique()
max_delay = 64  # EIP-7045 extended inclusion window

# Pivot to get cumulative_pct for each (slot, delay)
df_pivot = df.pivot(index="slot", columns="inclusion_delay", values="cumulative_pct")

# Forward fill missing delays (if no attestations at delay N, use value from delay N-1)
df_pivot = df_pivot.reindex(columns=range(1, max_delay + 1)).ffill(axis=1).fillna(0)

# Get slot metadata (including block size)
slot_meta = df.drop_duplicates("slot").set_index("slot")[
    ["epoch", "time", "total_validators", "blob_count", "block_size_bytes", "block_first_seen_ms"]
]

# Convert block size to KB for readability
slot_meta["block_size_kb"] = slot_meta["block_size_bytes"] / 1024

print(f"Loaded {len(slots):,} slots")
print(f"Block size range: {slot_meta['block_size_kb'].min():.1f} - {slot_meta['block_size_kb'].max():.1f} KB")
df_pivot.head()

Loaded 7,200 slots
Block size range: 0.0 - 328.8 KB

inclusion_delay	1	2	3	4	5	6	7	8	9	10	...	55	56	57	58	59	60	61	62	63	64
slot
13568399	99.5854	99.7072	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	...	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0
13568400	99.8029	99.9901	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	...	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0
13568401	99.9901	100.0000	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	...	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0
13568402	100.0000	100.0000	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	...	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0
13568403	99.9934	100.0000	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	...	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0	100.0

5 rows × 64 columns

Attestation buildup heatmap¶

Each row is a slot, each column is the inclusion delay (1-64 per EIP-7045). Color intensity shows cumulative percentage of attestations included by that delay. Bright = fast inclusion, dark = slow.

Show code

# Sample slots for readable heatmap (every Nth slot)
sample_step = max(1, len(df_pivot) // 200)
df_sample = df_pivot.iloc[::sample_step]

# Get time labels for y-axis
y_labels = [slot_meta.loc[s, "time"].strftime("%H:%M") if s in slot_meta.index else str(s) for s in df_sample.index]
slot_labels = list(df_sample.index)

fig = go.Figure(
    data=go.Heatmap(
        z=df_sample.values,
        x=[str(d) for d in df_sample.columns],
        y=y_labels,
        customdata=[[s] * len(df_sample.columns) for s in slot_labels],
        colorscale="Viridis",
        zmin=0,
        zmax=100,
        colorbar=dict(title="Cumulative %", ticksuffix="%"),
        hovertemplate="<b>Slot:</b> %{customdata}<br><b>Time:</b> %{y}<br><b>Delay:</b> %{x} slots<br><b>Included:</b> %{z:.1f}%<extra></extra>",
    )
)

fig.update_layout(
    title="Attestation inclusion by delay",
    xaxis_title="Inclusion delay (slots)",
    yaxis_title="Time",
    yaxis=dict(autorange="reversed"),
    height=800,
    margin=dict(l=80, r=30, t=50, b=60),
)
fig.show()

CDF distribution at key delays¶

Histogram showing the distribution of cumulative attestation percentage at delays 1, 2, 4, and 8 slots. Most slots should cluster near 100% for delay 1.

Show code

key_delays = [1, 2, 4, 8]

fig = make_subplots(
    rows=2, cols=2,
    subplot_titles=[f"Delay = {d} slot{'s' if d > 1 else ''}" for d in key_delays],
    horizontal_spacing=0.1,
    vertical_spacing=0.12,
)

for i, delay in enumerate(key_delays):
    row, col = divmod(i, 2)
    values = df_pivot[delay].dropna()
    
    fig.add_trace(
        go.Histogram(
            x=values,
            nbinsx=50,
            name=f"Delay {delay}",
            marker_color=px.colors.sequential.Viridis[i * 2 + 2],
            hovertemplate="%{x:.1f}%: %{y} slots<extra></extra>",
        ),
        row=row + 1, col=col + 1,
    )
    
    # Add median line
    median = values.median()
    fig.add_vline(
        x=median, line_dash="dash", line_color="red",
        annotation_text=f"median: {median:.1f}%",
        annotation_position="top right",
        row=row + 1, col=col + 1,
    )

fig.update_xaxes(title_text="Cumulative %", range=[0, 105])
fig.update_yaxes(title_text="Slot count")
fig.update_layout(
    title="Distribution of attestation inclusion rates",
    height=600,
    showlegend=False,
    margin=dict(l=60, r=30, t=80, b=60),
)
fig.show()

Average CDF curve¶

Mean attestation buildup curve across all slots with percentile bands (5th-95th). Shows typical attestation propagation dynamics.

Show code

delays = list(range(1, 65))
mean_curve = df_pivot.mean()
p5 = df_pivot.quantile(0.05)
p25 = df_pivot.quantile(0.25)
p75 = df_pivot.quantile(0.75)
p95 = df_pivot.quantile(0.95)

fig = go.Figure()

# 5-95 percentile band
fig.add_trace(go.Scatter(
    x=delays + delays[::-1],
    y=list(p95) + list(p5)[::-1],
    fill="toself",
    fillcolor="rgba(99, 110, 250, 0.15)",
    line=dict(color="rgba(255,255,255,0)"),
    name="5th-95th percentile",
    hoverinfo="skip",
))

# 25-75 percentile band
fig.add_trace(go.Scatter(
    x=delays + delays[::-1],
    y=list(p75) + list(p25)[::-1],
    fill="toself",
    fillcolor="rgba(99, 110, 250, 0.3)",
    line=dict(color="rgba(255,255,255,0)"),
    name="25th-75th percentile",
    hoverinfo="skip",
))

# Mean curve
fig.add_trace(go.Scatter(
    x=delays,
    y=mean_curve,
    mode="lines+markers",
    name="Mean",
    line=dict(color="#636EFA", width=3),
    marker=dict(size=4),
    hovertemplate="Delay %{x}: %{y:.2f}%<extra></extra>",
))

fig.update_layout(
    title="Attestation buildup CDF (mean with percentile bands)",
    xaxis_title="Inclusion delay (slots)",
    yaxis_title="Cumulative attestations (%)",
    xaxis=dict(tickmode="linear", dtick=4),
    yaxis=dict(range=[0, 105]),
    height=500,
    legend=dict(yanchor="bottom", y=0.02, xanchor="right", x=0.98),
    margin=dict(l=60, r=30, t=50, b=60),
)
fig.show()

Blob count correlation¶

Box plot showing attestation inclusion at delay=1 grouped by blob count. Tests whether slots with more blobs experience slower attestation propagation.

Show code

# Merge delay-1 inclusion rate with blob count
df_corr = pd.DataFrame({
    "slot": df_pivot.index,
    "pct_at_delay_1": df_pivot[1].values,
})
df_corr = df_corr.merge(slot_meta[["blob_count"]], left_on="slot", right_index=True)

fig = go.Figure()

unique_blobs = sorted(df_corr["blob_count"].unique())
n_colors = len(unique_blobs)
colors = px.colors.sample_colorscale("Viridis", [i / max(1, n_colors - 1) for i in range(n_colors)])

for i, blob_count in enumerate(unique_blobs):
    subset = df_corr[df_corr["blob_count"] == blob_count]
    fig.add_trace(go.Box(
        y=subset["pct_at_delay_1"],
        name=str(blob_count),
        boxpoints="outliers",
        marker_color=colors[i],
        hovertemplate="%{y:.1f}%<extra></extra>",
    ))

fig.update_layout(
    title="Attestation inclusion at delay=1 by blob count",
    xaxis_title="Blob count",
    yaxis_title="Cumulative % at delay 1",
    yaxis=dict(range=[0, 105]),
    showlegend=False,
    height=500,
    margin=dict(l=60, r=30, t=50, b=80),
)
fig.add_annotation(
    text="Box: 25th-75th percentile. Line: median. Whiskers: min/max excluding outliers.",
    xref="paper", yref="paper", x=0.5, y=-0.15,
    showarrow=False, font=dict(size=10, color="gray"),
)
fig.show()

Block size correlation¶

Scatter plot showing relationship between compressed block size (KB) and attestation inclusion at delay=1. Larger blocks may take longer to propagate, affecting attestation timing.

Show code

# Extend df_corr with block size data for correlation analysis
df_block = df_corr.merge(
    slot_meta[["block_size_kb", "block_first_seen_ms"]], 
    left_on="slot", right_index=True
)

# Filter out slots with missing block size data
df_block = df_block[df_block["block_size_kb"] > 0]

# Scatter plot: block size vs attestation inclusion
fig = px.scatter(
    df_block,
    x="block_size_kb",
    y="pct_at_delay_1",
    color="blob_count",
    color_continuous_scale="Viridis",
    opacity=0.5,
    hover_data={"slot": True, "block_size_kb": ":.1f", "pct_at_delay_1": ":.1f", "blob_count": True},
    labels={
        "block_size_kb": "Block size (KB)",
        "pct_at_delay_1": "% included at delay 1",
        "blob_count": "Blobs",
    },
)
fig.update_traces(marker=dict(size=4))

corr = df_block["block_size_kb"].corr(df_block["pct_at_delay_1"])

fig.update_layout(
    title=f"Block size vs attestation inclusion (r = {corr:.3f})",
    height=500,
    margin=dict(l=60, r=30, t=50, b=60),
)
fig.show()

print(f"Correlation (block size vs inclusion at delay 1): {corr:.4f}")

Correlation (block size vs inclusion at delay 1): 0.0118

Block size bins¶

Box plot showing attestation inclusion grouped by block size bins. Reveals whether larger blocks systematically experience slower attestation propagation.

Show code

# Create block size bins
df_block["size_bin"] = pd.cut(
    df_block["block_size_kb"],
    bins=[0, 40, 50, 60, 70, 80, 100, 150],
    labels=["<40", "40-50", "50-60", "60-70", "70-80", "80-100", ">100"]
)

fig = go.Figure()

colors = px.colors.sequential.Plasma
for i, bin_label in enumerate(["<40", "40-50", "50-60", "60-70", "70-80", "80-100", ">100"]):
    subset = df_block[df_block["size_bin"] == bin_label]
    if len(subset) > 0:
        fig.add_trace(go.Box(
            y=subset["pct_at_delay_1"],
            name=f"{bin_label} KB",
            boxpoints="outliers",
            marker_color=colors[i],
            hovertemplate="%{y:.1f}%<extra></extra>",
        ))

fig.update_layout(
    title="Attestation inclusion at delay=1 by block size",
    xaxis_title="Block size (KB)",
    yaxis_title="Cumulative % at delay 1",
    yaxis=dict(range=[0, 105]),
    showlegend=False,
    height=500,
    margin=dict(l=60, r=30, t=50, b=80),
)
fig.add_annotation(
    text="Box: 25th-75th percentile. Line: median. Whiskers: min/max excluding outliers.",
    xref="paper", yref="paper", x=0.5, y=-0.15,
    showarrow=False, font=dict(size=10, color="gray"),
)
fig.show()

# Print stats per bin
print("\nStats by block size bin:")
print(df_block.groupby("size_bin")["pct_at_delay_1"].agg(["count", "mean", "median"]).round(2))

Stats by block size bin:
          count   mean  median
size_bin                      
<40         840  99.35   99.99
40-50       424  99.20   99.99
50-60       731  99.50   99.99
60-70       985  99.50   99.99
70-80      1053  99.34   99.99
80-100     1450  99.50   99.98
>100       1347  99.59   99.97

/tmp/ipykernel_3013/416202978.py:40: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

Block propagation time correlation¶

Scatter plot showing relationship between block first-seen time (ms after slot start) and attestation inclusion. Blocks that propagate later leave less time for attestations.

Show code

# Filter to reasonable propagation times (< 12 seconds = 1 slot)
df_prop = df_block[df_block["block_first_seen_ms"] < 12000].copy()

fig = px.scatter(
    df_prop,
    x="block_first_seen_ms",
    y="pct_at_delay_1",
    color="block_size_kb",
    color_continuous_scale="Plasma",
    opacity=0.5,
    hover_data={"slot": True, "block_first_seen_ms": True, "pct_at_delay_1": ":.1f", "block_size_kb": ":.1f"},
    labels={
        "block_first_seen_ms": "Block first seen (ms after slot start)",
        "pct_at_delay_1": "% included at delay 1",
        "block_size_kb": "Size (KB)",
    },
)
fig.update_traces(marker=dict(size=4))

# Add correlation coefficient
corr = df_prop["block_first_seen_ms"].corr(df_prop["pct_at_delay_1"])

fig.update_layout(
    title=f"Block propagation time vs attestation inclusion (r = {corr:.3f})",
    height=500,
    margin=dict(l=60, r=30, t=50, b=60),
)
fig.show()

print(f"Correlation (propagation time vs inclusion): {corr:.4f}")

Correlation (propagation time vs inclusion): -0.0185

Epoch-level aggregation¶

Heatmap showing mean attestation inclusion rate per epoch at each delay. Reveals temporal trends in network health.

Show code

# Add epoch to pivot data
df_with_epoch = df_pivot.copy()
df_with_epoch["epoch"] = df_with_epoch.index.map(lambda s: slot_meta.loc[s, "epoch"] if s in slot_meta.index else None)
df_with_epoch = df_with_epoch.dropna(subset=["epoch"])

# Aggregate by epoch
epoch_agg = df_with_epoch.groupby("epoch")[list(range(1, 65))].mean()

# Get epoch times for labels
epoch_times = df.drop_duplicates("epoch").set_index("epoch")["time"].to_dict()
y_labels = [epoch_times.get(e, pd.Timestamp("1970-01-01")).strftime("%H:%M") for e in epoch_agg.index]

fig = go.Figure(
    data=go.Heatmap(
        z=epoch_agg.values,
        x=[str(d) for d in epoch_agg.columns],
        y=y_labels,
        colorscale="Viridis",
        zmin=0,
        zmax=100,
        colorbar=dict(title="Mean %", ticksuffix="%"),
        hovertemplate="<b>Epoch time:</b> %{y}<br><b>Delay:</b> %{x} slots<br><b>Mean:</b> %{z:.1f}%<extra></extra>",
    )
)

fig.update_layout(
    title="Mean attestation inclusion by epoch",
    xaxis_title="Inclusion delay (slots)",
    yaxis_title="Epoch time",
    yaxis=dict(autorange="reversed"),
    height=600,
    margin=dict(l=80, r=30, t=50, b=60),
)
fig.show()

Slow slots analysis¶

Scatter plot highlighting slots with unusually slow attestation inclusion (<90% at delay 1). Size indicates block size, color indicates blob count.

Show code

# Identify slow slots
threshold = 90
df_analysis = df_corr.merge(
    slot_meta[["time", "total_validators", "block_size_kb", "block_first_seen_ms"]], 
    left_on="slot", right_index=True
)
slow_slots = df_analysis[df_analysis["pct_at_delay_1"] < threshold].copy()

print(f"Slots with <{threshold}% inclusion at delay 1: {len(slow_slots):,} ({100*len(slow_slots)/len(df_analysis):.1f}%)")

if len(slow_slots) > 0:
    fig = px.scatter(
        slow_slots,
        x="time",
        y="pct_at_delay_1",
        size="block_size_kb",
        color="blob_count",
        color_continuous_scale="Viridis",
        hover_data={"slot": True, "total_validators": True, "blob_count": True, "block_size_kb": ":.1f"},
        labels={
            "time": "Time",
            "pct_at_delay_1": "% at delay 1",
            "blob_count": "Blobs",
            "block_size_kb": "Block KB",
        },
    )
    fig.update_traces(
        hovertemplate="<b>Slot:</b> %{customdata[0]}<br><b>Time:</b> %{x}<br><b>Included:</b> %{y:.1f}%<br><b>Block size:</b> %{customdata[3]:.1f} KB<br><b>Blobs:</b> %{customdata[2]}<extra></extra>",
    )
    fig.update_layout(
        title=f"Slow attestation slots (<{threshold}% at delay 1) - size = block size",
        yaxis=dict(range=[0, threshold + 5]),
        height=500,
        margin=dict(l=60, r=30, t=50, b=60),
    )
    fig.show()
else:
    print("No slow slots found.")

Slots with <90% inclusion at delay 1: 31 (0.4%)

Time series: delay-1 inclusion rate¶

Rolling average of attestation inclusion at delay=1 over time. Shows network performance trends throughout the day.

Show code

df_ts = df_analysis.sort_values("time").copy()
df_ts["rolling_mean"] = df_ts["pct_at_delay_1"].rolling(window=32, min_periods=1).mean()

fig = go.Figure()

# Individual points (subsampled)
sample_step = max(1, len(df_ts) // 500)
df_sample = df_ts.iloc[::sample_step]

fig.add_trace(go.Scatter(
    x=df_sample["time"],
    y=df_sample["pct_at_delay_1"],
    customdata=df_sample["slot"],
    mode="markers",
    marker=dict(size=3, color="#636EFA", opacity=0.3),
    name="Per-slot",
    hovertemplate="<b>Slot:</b> %{customdata}<br><b>Time:</b> %{x}<br><b>Included:</b> %{y:.1f}%<extra></extra>",
))

# Rolling average
fig.add_trace(go.Scatter(
    x=df_ts["time"],
    y=df_ts["rolling_mean"],
    mode="lines",
    line=dict(color="#EF553B", width=2),
    name="32-slot rolling avg",
    hovertemplate="%{x}<br>%{y:.1f}%<extra></extra>",
))

fig.update_layout(
    title="Attestation inclusion at delay=1 over time",
    xaxis_title="Time",
    yaxis_title="Cumulative % at delay 1",
    yaxis=dict(range=[0, 105]),
    legend=dict(yanchor="bottom", y=0.02, xanchor="right", x=0.98),
    height=500,
    margin=dict(l=60, r=30, t=50, b=60),
)
fig.show()

Summary statistics¶

Show code

# CDF summary by delay
key_delays = [1, 2, 4, 8, 16, 32, 64]
summary = pd.DataFrame({
    "Delay": key_delays,
    "Mean %": [df_pivot[d].mean() for d in key_delays],
    "Median %": [df_pivot[d].median() for d in key_delays],
    "5th pct": [df_pivot[d].quantile(0.05) for d in key_delays],
    "95th pct": [df_pivot[d].quantile(0.95) for d in key_delays],
})
print("Attestation inclusion by delay:")
display(summary.round(2))

# Correlation summary
print("\nCorrelation with delay-1 inclusion rate:")
df_filtered = df_block[df_block["block_first_seen_ms"] < 12000]
corr_df = pd.DataFrame({
    "Factor": ["Blob count", "Block size (KB)", "Block first seen (ms)"],
    "Correlation": [
        df_block["blob_count"].corr(df_block["pct_at_delay_1"]),
        df_block["block_size_kb"].corr(df_block["pct_at_delay_1"]),
        df_filtered["block_first_seen_ms"].corr(df_filtered["pct_at_delay_1"]),
    ],
})
display(corr_df.round(4))

Attestation inclusion by delay:

	Delay	Mean %	Median %	5th pct	95th pct
0	1	99.46	99.98	99.32	100.0
1	2	99.95	100.00	99.82	100.0
2	4	99.99	100.00	99.96	100.0
3	8	100.00	100.00	99.99	100.0
4	16	100.00	100.00	100.00	100.0
5	32	100.00	100.00	100.00	100.0
6	64	100.00	100.00	100.00	100.0

Correlation with delay-1 inclusion rate:

	Factor	Correlation
0	Blob count	-0.0079
1	Block size (KB)	0.0118
2	Block first seen (ms)	-0.0185