Skip to main content

End to End Analysis Example

This page will guide you through an end-to-end analysis use case with Hubble and Google Looker Studio


  1. Make sure you have connected to Hubble by following the instructions in the Connecting page
  2. You have access to Google Looker Studio
  3. You have read and understand the general Best Practices for querying BigQuery data

Create a Dashboard Analyzing Soroban Metrics

Attach Data Sources to Looker Studio

  1. Select Create --> Data source
  1. Find and select the BigQuery Google Connector
  1. Find the desired tables that you want to connect. For this example you will want to add a data source for:
    1. crypto-stellar.crypto_stellar.contract_data

Create a New Report (Dashboard)

  1. Select Create --> Report
  1. Add your data sources from above
  1. Insert a Pie chart
  1. Choose contract_data as the Data source
  1. Choose closed_at as the Date Range Dimension and contract_durability as the Dimension
  1. You should now have a pie chart showing the percentage of Temporary VS Persistent Contract Data Durability

Use Custom SQL to Create a Chart

  1. In your report, click Add Data which will be near the bottom right of your window
  1. Select BigQuery and choose CUSTOM QUERY and select your desired Billing Project where the query will be charged
  1. Add the following query and click Add
-- Find the latest ledger sequence within Hubble.
-- This may be slightly behind the actual Stellar latest ledger
-- because Hubble is scheduled to run and insert data at 5 minute intervals
with latest_ledger_in_hubble as (
max(sequence) as latest_ledger_sequence
from `crypto-stellar.crypto_stellar.history_ledgers`

-- Find all the ttl that have expired
expired_ttl as (
, live_until_ledger_seq
-- Saving the date to aggregate on at the final step of the query
, date(closed_at) as ledger_date
from `crypto-stellar.crypto_stellar_dbt.ttl_current`
where true
-- Filter for expired entries only with the use of latest_ledger_sequence
and live_until_ledger_seq < (select latest_ledger_sequence from latest_ledger_in_hubble)

-- Aggregate based on the month and contract durability type
date_trunc(et.ledger_date, month) as month_agg
, cd.contract_durability
, count(1) as expired_entry_count
from expired_ttl as et
join `crypto-stellar.crypto_stellar_dbt.contract_data_current` as cd
on et.key_hash = cd.ledger_key_hash
where true
-- Optionally filter for a specific date/date range
and et.ledger_date between '2024-02-01' and '2024-06-30'
group by 1,2
order by 1 desc, 2
  1. Insert Column chart
  1. Select BigQuery Custom SQL as your Data source, month_agg as the Dimension, contract_durability as the Breakdown Dimension, and expired_entry_countas theMetric`
  1. You should now have a column chart (bar chart) showing the expired Soroban contract entries

Note that between 2024-02-01 and 2024-06-30 there aren't that many expired persistent entries

  1. Click +Add quick filter to apply a filter throughout the whole report
  1. Select contract_durability to filter by contract_durability values
  1. Select only ContractDataDurabilityPersistent and click Apply
  1. Your charts should now be filtered and show only ContractDataDurabilityPersistent data