Querying the Share - Blockworks Documentation

Once the share is mounted as a database in your account, it behaves like any other read-only database: ordinary SQL, your warehouses, your roles.

This page uses BLOCKWORKS as the local database name and clearly marked placeholder schema and view names. Substitute the names you actually see from the discovery queries below, which are the authoritative source for what your share contains.

Discover what you have

The share is self-describing. Start at the top and work down.

List the schemas

SHOW SCHEMAS IN DATABASE BLOCKWORKS;

List the views in a schema

SHOW VIEWS IN SCHEMA BLOCKWORKS.<schema>;

Inspect a view's columns

DESCRIBE VIEW BLOCKWORKS.<schema>.<view>;

INFORMATION_SCHEMA works too, and is easier to filter and join:

-- Every object in the share, with its schema and type.
SELECT table_schema,
       table_name,
       table_type,
       comment
FROM   BLOCKWORKS.INFORMATION_SCHEMA.TABLES
ORDER BY table_schema, table_name;

-- Every column on a single object, in order.
SELECT column_name,
       data_type,
       is_nullable,
       comment
FROM   BLOCKWORKS.INFORMATION_SCHEMA.COLUMNS
WHERE  table_schema = '<SCHEMA>'
  AND  table_name   = '<VIEW>'
ORDER BY ordinal_position;

-- Find every object whose name mentions a concept you care about.
SELECT table_schema, table_name
FROM   BLOCKWORKS.INFORMATION_SCHEMA.TABLES
WHERE  table_name ILIKE '%price%';

Column and object comments carry the same descriptions as the Data API catalog where they are populated. Read them before guessing at a column’s meaning.

Set your session

USE ROLE ANALYST;
USE WAREHOUSE ANALYTICS_WH;
USE DATABASE BLOCKWORKS;

Queries against the share run on your warehouse. Size it to the work: a small warehouse is fine for lookups and recent windows; scans across long histories will want more.

Query patterns

The examples below use placeholder names in angle brackets. They illustrate shape, not schema.

A point-in-time lookup

SELECT *
FROM   BLOCKWORKS.<schema>.<entity_view>
WHERE  symbol = 'BTC';

A time-bounded series

Timeseries-shaped objects carry a time column and a series key. Always bound the time range, because predicate pruning on the time column is what keeps these queries cheap.

SELECT   <time_column>,
         <metric_column>
FROM     BLOCKWORKS.<schema>.<timeseries_view>
WHERE    <series_key_column> = '<series_key>'
  AND    <time_column> >= DATEADD(day, -90, CURRENT_TIMESTAMP())
ORDER BY <time_column>;

An aggregation over a window

SELECT   DATE_TRUNC('month', <time_column>) AS month,
         AVG(<metric_column>)               AS avg_value,
         MAX(<metric_column>)               AS max_value
FROM     BLOCKWORKS.<schema>.<timeseries_view>
WHERE    <time_column> >= DATEADD(year, -1, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 1;

A period-over-period change

SELECT   <time_column>,
         <metric_column>,
         LAG(<metric_column>) OVER (ORDER BY <time_column>) AS previous_value,
         <metric_column>
           / NULLIF(LAG(<metric_column>) OVER (ORDER BY <time_column>), 0) - 1 AS pct_change
FROM     BLOCKWORKS.<schema>.<timeseries_view>
WHERE    <series_key_column> = '<series_key>'
  AND    <time_column> >= DATEADD(year, -1, CURRENT_TIMESTAMP())
ORDER BY <time_column>;

Joining entity metadata onto a series

Entity-shaped objects hold identity and metadata; timeseries-shaped objects hold history keyed by the same identifier. Joining them is the most common pattern in the share.

SELECT   e.name,
         e.symbol,
         t.<time_column>,
         t.<metric_column>
FROM     BLOCKWORKS.<schema>.<timeseries_view> AS t
JOIN     BLOCKWORKS.<schema>.<entity_view>     AS e
  ON     e.<id_column> = t.<series_key_column>
WHERE    t.<time_column> >= DATEADD(day, -30, CURRENT_TIMESTAMP());

Joining to your own tables

This is the reason to use the share rather than the API. Blockworks data and your data are in the same warehouse, so they join like any other two tables.

SELECT   p.position_id,
         p.quantity,
         m.<price_column>,
         p.quantity * m.<price_column> AS position_value
FROM     MY_DB.PORTFOLIO.POSITIONS       AS p
JOIN     BLOCKWORKS.<schema>.<entity_view> AS e
  ON     e.symbol = p.symbol
JOIN     BLOCKWORKS.<schema>.<latest_metrics_view> AS m
  ON     m.<id_column> = e.<id_column>;

Working efficiently

Bound the time column

Always constrain the time column on timeseries objects. An unbounded scan of full history is the single most common cause of a surprising bill.

Project only what you need

Select the columns you use rather than SELECT *. Columnar storage means narrow queries are genuinely cheaper.

Materialize repeated work

If several dashboards hit the same aggregate, build a table in your own database from the share and refresh it on a schedule.

Explore with LIMIT

When you first meet an object, look at it with a LIMIT before writing the real query. Snowflake still scans, so keep the predicate on.

Things to know

The share is read-only. You cannot write to it, add indexes, or alter its objects. Build derived tables in your own database.
Data updates in place. The provider refreshes the underlying models; you see the new data on your next query, with nothing to re-import.
Objects may be added over time. Re-run the discovery queries periodically rather than assuming a fixed inventory.
Definitions match the API. Metrics are modeled once and surfaced two ways, so a number in the share and the same number from the Data API are the same number.

Getting help

If you cannot find a dataset you expect to have, or a query is behaving unexpectedly, get in touch at blockworks.com/contact.

​Discover what you have

​Set your session

​Query patterns

​A point-in-time lookup

​A time-bounded series

​An aggregation over a window

​A period-over-period change

​Joining entity metadata onto a series

​Joining to your own tables

​Working efficiently

Bound the time column

Project only what you need

Materialize repeated work

Explore with LIMIT

​Things to know

​Getting help

Discover what you have

Set your session

Query patterns

A point-in-time lookup

A time-bounded series

An aggregation over a window

A period-over-period change

Joining entity metadata onto a series

Joining to your own tables

Working efficiently

Things to know

Getting help