> ## Documentation Index
> Fetch the complete documentation index at: https://docs.blockworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying the Share

> Discover the schemas and views in the Blockworks Snowflake Datashare and query them with ordinary SQL.

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.

<Note>
  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.
</Note>

## Discover what you have

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

<Steps>
  <Step title="List the schemas">
    ```sql theme={null}
    SHOW SCHEMAS IN DATABASE BLOCKWORKS;
    ```
  </Step>

  <Step title="List the views in a schema">
    ```sql theme={null}
    SHOW VIEWS IN SCHEMA BLOCKWORKS.<schema>;
    ```
  </Step>

  <Step title="Inspect a view's columns">
    ```sql theme={null}
    DESCRIBE VIEW BLOCKWORKS.<schema>.<view>;
    ```
  </Step>
</Steps>

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

```sql theme={null}
-- 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;
```

```sql theme={null}
-- 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;
```

```sql theme={null}
-- 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%';
```

<Note>
  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.
</Note>

## Set your session

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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

<CardGroup cols={2}>
  <Card title="Bound the time column" icon="clock">
    Always constrain the time column on timeseries objects. An unbounded scan of full history is the
    single most common cause of a surprising bill.
  </Card>

  <Card title="Project only what you need" icon="columns">
    Select the columns you use rather than `SELECT *`. Columnar storage means narrow queries are
    genuinely cheaper.
  </Card>

  <Card title="Materialize repeated work" icon="stack-2">
    If several dashboards hit the same aggregate, build a table in your own database from the share
    and refresh it on a schedule.
  </Card>

  <Card title="Explore with LIMIT" icon="search">
    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.
  </Card>
</CardGroup>

## 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](/getting-started/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](https://blockworks.com/contact).