Snowflake
Cloud Delivery is in limited availability. To request access, reach out to your sales or account manager.
Overview
Coin Metrics exposes Cloud Delivery datasets as tables in Snowflake through Secure Data Sharing. You mount a read-only database in your own Snowflake account and query it with SQL. No data is copied into your account and you are not charged Snowflake storage for it. You pay only for the compute you use to run queries.
The tables are Snowflake external tables over the same Parquet objects described on the Amazon S3 page. That means you never handle S3 paths or AWS credentials, but it also shapes query performance, which is covered under Limits and notes.
For which datasets exist and how far back they go, see Cloud Delivery.
Get access
What to provide
Send your account manager your Snowflake organization name and account name. You can find both by running the following in your account.
SELECT CURRENT_ORGANIZATION_NAME(), CURRENT_ACCOUNT_NAME();Coin Metrics adds your account to a share. Shares are curated per customer, so you see the tables covered by your entitlements rather than every table published.
Mount the share
Once you have been added, confirm the share is visible.
SHOW SHARES;Create a database from it. You choose the database name, so pick whatever fits your naming conventions.
Grant your reader roles access to it.
Tables live in the EXTERNAL schema, so a fully qualified name looks like coin_metrics.EXTERNAL.market_trades. Verify the mount worked.
Shares are region-bound. A direct share works when your account is in the same cloud region as the provider account. If yours is elsewhere, your account manager will arrange a listing that handles cross-region fulfillment.
Marketplace listings
Coin Metrics also publishes Cloud Delivery datasets as private listings. A listing is the better route when you need cross-region or cross-cloud delivery, or when you prefer to discover and request the data through the Snowflake interface rather than by exchanging account identifiers. Ask your account manager which route suits you.
Understand the layout
Ten tables are published in the EXTERNAL schema.
market_trades
Executed trades
market_candles
OHLCV bars
market_quotes
Best bid and ask
market_orderbooks_snapshots
Point-in-time order book state
market_orderbooks_updates
Incremental order book changes
reference_rates
Coin Metrics reference rates
reference_data_markets
Market catalog
reference_data_assets
Asset catalog
reference_data_exchanges
Exchange catalog
reference_data_pairs
Pair catalog
Partition columns
Every market dataset carries partition columns in addition to its data columns. These are derived from the underlying file layout rather than stored in the file body, and filtering on them is what keeps queries fast.
market_trades
market_type, exchange, dt
market_candles
market_type, exchange, frequency, dt
market_quotes
market_type, exchange, granularity, dt
market_orderbooks_snapshots
market_type, exchange, depth, granularity, dt
market_orderbooks_updates
market_type, exchange, depth, granularity, dt
reference_rates
frequency, dt
Reference data tables
None
dt is a DATE holding the UTC date of the data. market_type is one of spot, future, or option. exchange is the normalized exchange identifier, lowercase with non-alphanumeric characters replaced by underscores, so binance.us appears as binance_us.
Data columns
Column names and meanings match the API fields documented on the dataset pages linked from Cloud Delivery, except for the omissions listed under Schema differences. Two further differences are worth knowing.
Types are native, not strings. The API returns prices and amounts as JSON strings to preserve precision. Here they are FLOAT, and timestamps are TIMESTAMP_NTZ in UTC rather than ISO-8601 strings. If you need exact decimal arithmetic, cast to NUMBER with an explicit scale.
Order book sides are semi-structured. In market_orderbooks_snapshots and market_orderbooks_updates, asks and bids are VARIANT columns holding arrays of price and size objects. Use LATERAL FLATTEN to expand them, as shown below. The updates table additionally carries a type column that the snapshots table does not.
Read the data
Trades for one market and day
Daily candles over a date range
Reference rates
Metric columns are named after the metric, for example ReferenceRateUSD. Snowflake identifiers are case-insensitive unless quoted, so you can write them in any case.
Flattening an order book
Expanding the asks array gives one row per price level.
Joining to the market catalog
Querying efficiently
External tables read from object storage at query time, so pruning matters more than it would on a native table.
Always filter on
dt. It is the highest-leverage filter available. An unfiltered query scans every day of history.Filter on
exchangeandmarket_typetoo. Each one eliminates a large share of the files before any are opened.Filter on
frequencyfor candles. Eight frequencies share the table, so omitting it reads roughly eight times more data than you need.Compare
dtto date literals, not expressions. Wrapping the column in a function can prevent partition elimination.Materialize hot queries. If you run the same aggregation repeatedly, create a native table or materialized view from the result rather than rescanning the external table each time.
Limits and notes
The tables are read-only. External tables do not support
INSERT,UPDATE,DELETE, orMERGE. To modify data, copy it into your own table first withCREATE TABLE ... AS SELECT.Queries are slower than on native tables. Every query reads Parquet from object storage. Pruning is at file level rather than micro-partition level, and there is no automatic clustering. For repeated heavy analytics, copy the slice you need into a native table.
You pay for compute. Snowflake storage is not charged for shared data, but every query runs on your warehouse and is billed to you.
No Time Travel on shared external tables. If you need historical snapshots of the data as it stood, materialize your own copies.
Reference data has no history. The four catalog tables reflect the current state and are overwritten daily. They have no
dtcolumn.Shares cannot be re-shared. Snowflake does not permit a consumer to re-share a database created from a share. If another account in your organization needs access, ask your account manager to add it to the share.
Related
Cloud Delivery: datasets, coverage, and the update schedule.
Amazon S3: the same data as files in object storage.
Last updated
Was this helpful?