# Chalk Catalog Components
source: https://docs.chalk.ai/docs/chalksql/chalk-catalog-components

## How features, resolvers, datasets, and other Chalk concepts are exposed to Chalk SQL.

The Chalk Catalog is an umbrella term encompassing all things Chalk. This page describes how various concepts within
Chalk are translated into the Catalog and made available to Chalk SQL.

Like traditional relational databases, the Catalog is organized under a three-tiered namespace system of
catalogs, schemas, and tables.

### The chalk catalog

Most concepts in Chalk will be exposed as tables under the chalk database catalog.

### chalk.datasets schema

The chalk.datasets schema contains a view for each named dataset created within the last 60 days,
and the view's name is the dataset's name. Note that if two offline queries reference the same named dataset,
only the latest dataset revision is available in this schema.

To reference dataset revisions that are not the most
recent or were created without a name, consider using the get_dataset_revision() table-valued function.

This schema is available in all Chalk deployments.

### chalk.historical_values schema

The chalk.historical_values schema contains a view for each feature, as well as a view for each feature namespace
within the deployment.

- For each feature, a view with the same name as the feature's fully-qualified name is available in the Catalog
(i.e. chalk.historical_values.user.name). This view contains information about all observations of the feature, with
the following schema:observation_id, string, non-nullable: The unique identifier for the observation of the feature value.pkey, (type of feature namespace's pkey), non-nullable: The value of the primary feature associated with this
observation.value, (type of feature namespace's value): The observed value of the feature.value_json, string, DEPRECATED: The observed value of the feature, as JSON, for backwards compatibility.observed_at, timestamp, non-nullable: The time at which the feature value was observed.inserted_at, timestamp, non-nullable: The time at which the feature value was inserted into the offline store.resolver_fqn, string, nullable: The resolver that computed this feature.deployment_id, string, nullable: The deployment ID where the observation was made.operation_kind, int8, nullable: The kind of operation that produced the observation.operation_id, string, nullable: The operation ID of the query that produced the observation.
- For each feature namespace, a view with the same name as the namespace is available in the Catalog
(i.e. chalk.historical_values.user). This view contains the freshest values of each feature for each primary key
at the current point in time, and has the following schema:__pkey__: (type of feature namespace's pkey), non-nullable: The value of the primary feature associated with this row<feature-name>, (type of feature), for each feature in the namespace: The freshest observed value of the feature for the primary key.<feature-name>.__observed_at__, timestamp, for each feature in the namespace: The time at which the freshest value was observed.

This schema is available in Chalk deployments with an
offline store
configured.

### chalk.query_log schema

The chalk.query_log schema consists of exactly one table, data, which is the query log table.
This table provides a list of query metadata for each operation ID, including which tables in the offline store
correspond to values queried in that operation ID. The data table has the following schema:

- operation_id, string, non-nullable: The unique identifier for the operation.
- environment_id, string, non-nullable: The environment ID where the operation was run.
- deployment_id, string: The deployment ID where the operation was run.
- operation_kind, string: The kind of operation that was run (inference, streaming, etc.).
- query_timestamp, timestamp: The value of now() that was used by the query.
- execution_started_at, timestamp: The time at which the operation started executing. Null if there were errors during planning.
- execution_finished_at, timestamp: The time at which the operation finished executing. Null if there were errors during execution.
- query_status, string: The status of the query (success, error, running, etc.).
- query_name, string: The query name for named queries, null otherwise.
- query_name_version, string: The version of the query for named queries, null otherwise.
- branch_name, string: The branch the query was run on, null if the query was not run on a branch.
- correlation_id, string: The correlation ID for the query, null if the query was not run with a correlation ID.
- value_tables: array of strings: The list of tables in the offline store that correspond to result values from this query.

This schema is available in Chalk deployments with an
offline store
configured.

### chalk.telemetry schema

The chalk.telemetry schema provides three environment-scoped tables:

- spans has one row per OpenTelemetry span. It includes query and resolver spans as well as OpenInference AI spans
such as AGENT, LLM, and TOOL spans.
- traces has one summary row per trace.
- sessions has one summary row per agent session — the multi-turn conversation a set of traces belongs to.

chalk.telemetry.spans includes these columns:

- Timing and identity: start_time, end_time, duration_ns, duration_ms, trace_id, span_id,
parent_span_id, is_root_span, and trace_state.
- OpenTelemetry metadata: span_name, span_kind, service_name, environment_id, scope_name, scope_version,
status_code, and status_message.
- Complete JSON payloads: resource_attributes_json, span_attributes_json, events_json, and links_json.
- AI input and output: openinference_span_kind, input_value, input_mime_type, output_value, and
output_mime_type.
- Agent and tool metadata: turn_iterations, tool_call_count, tool_name, tool_call_id, and session_id.
- LLM metadata: llm_provider, llm_model_name, llm_system, llm_finish_reason, and llm_cost.
- LLM tokens: llm_token_count_prompt, llm_token_count_cache_read, llm_token_count_cache_write,
llm_token_count_completion, llm_token_count_reasoning, and llm_token_count_total.

chalk.telemetry.traces includes start_time, end_time, duration_ns, duration_ms, trace_id, span_count,
root_span_id, root_parent_span_id, root_span_name, root_span_attributes_json,
root_resource_attributes_json, and environment_id.

chalk.telemetry.sessions includes session_id, start_time, end_time, duration_ns, duration_ms,
trace_count, span_count, first_trace_id, first_root_span_name, and environment_id. A session is idle
between turns, so its duration is the wall-clock span it covers rather than the sum of its traces' durations.
Traces with no session ID — anything that is not an agent conversation — are not counted as sessions. Join
sessions to spans on session_id to reach the individual spans of a conversation.

For example, this query returns recent LLM and tool-call spans:

```
SELECT
  start_time,
  trace_id,
  span_name,
  openinference_span_kind,
  llm_model_name,
  llm_token_count_total,
  tool_name
FROM "chalk.telemetry.spans"
WHERE start_time >= now()::timestamp - INTERVAL 1 DAY
  AND openinference_span_kind IN ('LLM', 'TOOL')
ORDER BY start_time DESC
LIMIT 100
```

To find the largest recent traces:

```
SELECT
  start_time,
  trace_id,
  root_span_name,
  span_count,
  duration_ms
FROM "chalk.telemetry.traces"
WHERE start_time >= now()::timestamp - INTERVAL 1 DAY
ORDER BY span_count DESC
LIMIT 100
```

To find the longest-running agent conversations:

```
SELECT
  session_id,
  start_time,
  first_root_span_name,
  trace_count,
  span_count,
  duration_ms
FROM "chalk.telemetry.sessions"
WHERE start_time >= now()::timestamp - INTERVAL 1 DAY
ORDER BY duration_ms DESC
LIMIT 100
```

This schema is available in Chalk deployments with managed telemetry configured. Results are
always restricted to the active environment.

### chalk.resolvers schema

The chalk.resolvers schema contains a view for each dataframe-returning resolver that
takes in no inputs. When querying this view, a resolver run will be triggered and have the resolver's outputs as the
view's contents.

This schema is available in all Chalk deployments.

### chalk.online_store schema

The chalk.online_store schema contains a fixed view keys, which contains different information depending on which
online store is configured for your environment.

- For Redis online stores, information about the keys' type (Scalar, HasMany, TimeSeries, etc.),
memory usage, TTL, and feature FQN are available in this view.
- For DynamoDB online stores, information about the keys' namespace and value are available in this view.

Additionally, for each feature namespace, a view with the same name (i.e. chalk.online_store.user) is available,
consisting of the following schema:

- __id__
- <feature_namespace>.<feature> for each feature
- <feature_namespace>.<feature>_ts for each feature

This schema is available in Chalk deployments with a Redis or DynamoDB online store configured.

### chalk.online_delta_writes schema

The chalk.online_delta_writes schema shows the digest state that online delta writes
(CHALK_ONLINE_DELTA_WRITES) keep for scheduled queries and scheduled aggregate backfills. Each run records a
digest of every row it writes to the online store, and the next run skips rows whose digest is unchanged.
Every schedule that writes to the online store appears here, with or without the setting enabled; runs made with
it disabled record no digests.

The schedules view has one row per schedule. A scheduled aggregate backfill has one row per resolver it
backfills, told apart by backfill_child. Its columns describe the schedule's two most recent runs:

- schedule_key, query_name, kind (scheduled_query or scheduled_aggregate_backfill), backfill_child
- current_run_id, previous_run_id
- retained_runs: runs whose digests are still in storage
- current_committed_attempts, current_uncommitted_attempts: shards or retries of the current run that
finished successfully, and those that recorded digests without finishing
- current_digest_files, current_digest_rows: the committed digests the next run compares against
- current_oldest_stored_at, current_latest_stored_at: the range of times the rows behind those digests were
last written to the online store
- previous_committed_attempts, previous_uncommitted_attempts, previous_digest_files, previous_digest_rows

The runs view has one row per retained run, with the schedule columns above plus run_id, is_current,
is_previous, committed_attempts, uncommitted_attempts and digest_files.

```
SELECT query_name, current_run_id, current_digest_rows, current_latest_stored_at
FROM chalk.online_delta_writes.schedules
```

### chalk.information_schema schema

The chalk.information_schema schema describes the catalog itself. Alongside the conventional
schemas (aliased as schemata), tables, functions and catalogs views, it exposes the
environment's models and external functions — the
targets you can invoke from F.catalog_call(...) — as models and compute_functions.

```
-- Which models can this environment call, and what do they return?
SELECT model_name, qualified_name, output_type FROM "chalk.information_schema.models";

-- The same for deployed external functions.
SELECT function_name, qualified_name, output_type FROM "chalk.information_schema.compute_functions";
```

Both views share a schema:

- model_name / function_name — the registered name.
- qualified_name — the name to pass to F.catalog_call, i.e. model.<name> or function.<name>.
- endpoint_uri — the gateway endpoint the call is routed to.
- method_name — the method invoked on the remote service.
- output_type — the SQL type the handler returns, or NULL when the deployment did not
register one.

These views list what the running deployment was told about. An environment whose catalog
snapshot predates per-entry kind information shows entries in neither view until its next
deploy.

### The offline_store catalog

The offline store is available as a catalog. Similar to data sources as catalogs, this catalog works as a direct
interface to querying the underlying database that powers it.

### Data Sources as Catalogs

Each data source that you register with Chalk will be automatically registered in the Chalk Catalog as
a database catalog. The catalog shares the same name as the data source and provides a more direct interface
into querying that data source.

Data sources that are not named will not be registered in the Chalk Catalog.

As an example, if you define a Postgres source and a BigQuery source in your code:

```
from chalk.sql import PostgreSQLSource
from chalk.sql import BigQuerySource
pg = PostgreSQLSource(name='pg_source')
bq = BigQuerySource(name='bq_source')
```

You can write a federated query in Chalk SQL to join a transactions table in pg with a users table in bq:

```
SELECT
  bq_users.user_id AS user_id,
  bq_users.full_name AS full_name,
  pg_transactions.transaction_id AS transaction_id,
  pg_transactions.amount AS transaction_amount
FROM "bq_source.my_dataset.users" AS bq_users
FULL OUTER JOIN "pg_source.public.transactions" AS pg_transactions
ON bq_users.user_id = pg_transactions.user_id
/* pg_source and bq_source are the name of the datasources
 and therefore also the name of their respective catalogs */
```

The following data source types will be registered to the Chalk Catalog, and will be able to be queried in Chalk SQL:

- PostgreSQL
- Spanner
- Athena
- MySQL
- ClickHouse
- BigQuery
- Redshift
- Snowflake
- Databricks