Models Data Warehouse · Querying PostHog Data · PostHog/skills · Skills Docs

Models Data Warehouse · Querying PostHog Data · PostHog/skills · Skills Docs

Column	Type	Nullable	Description
`id`	String	NOT NULL	Source UUID. Pass it as a query’s connection id to live-query a direct connection.
`team_id`	Integer	NOT NULL
`source_type`	String	NOT NULL	Source connector type, e.g. ‘Stripe’, ‘Postgres’, ‘Hubspot’.
`status`	String	NOT NULL	Legacy source-level status, deprecated in favour of per-schema status in source_schemas.status; may be stale.
`access_method`	String	NOT NULL	‘direct’ for a live-query connection (nothing is synced; its tables exist only when queried through the connection), or ‘warehouse’ for a source synced into PostHog.
`direct_query_enabled`	Integer	NOT NULL	1 if this synced source may also be live-queried through a direct connection, 0 otherwise. Meaningless for sources that are already access_method=’direct’.
`is_live_queryable`	Integer	NOT NULL	1 if this source can be live-queried by passing its id as a query’s connection id, 0 otherwise. Use `WHERE is_live_queryable = 1` to list every connection available for live queries.
`api_version`	String	NOT NULL	Vendor API version this source is pinned to (opaque vendor label); NULL resolves to the source type’s default version at sync time.
`prefix`	String	NOT NULL	Table-name prefix applied to all tables synced from this source.
`created_by_id`	Integer	NULL	User who created the source.
`created_at`	DateTime	NOT NULL	When the source was connected.
`updated_at`	DateTime	NOT NULL	When the source config was last updated.
`deleted`	Integer	NOT NULL	1 if the source has been deleted, 0 otherwise.
`deleted_at`	DateTime	NOT NULL	When the source was deleted; NULL if not deleted.

Column	Type	Nullable	Description
`id`	String	NOT NULL	Warehouse table UUID.
`team_id`	Integer	NOT NULL
`name`	String	NOT NULL	Warehouse table name (includes the source prefix).
`columns`	JSON	NOT NULL	JSON schema of the table’s columns.
`row_count`	Integer	NOT NULL	Approximate number of rows in the table.
`external_data_source_id`	String	NOT NULL	Source that produced this table; joins to data_warehouse_sources.id.
`created_at`	DateTime	NOT NULL	When the table was first synced.
`updated_at`	DateTime	NOT NULL	When the table metadata was last updated.
`deleted`	Integer	NOT NULL	1 if the table has been deleted, 0 otherwise.
`deleted_at`	DateTime	NOT NULL	When the table was deleted; NULL if not deleted.

{
  "id": {
    "hogql": "IntegerDatabaseField",
    "clickhouse": "Int64",
    "valid": true
  },
  "email": {
    "hogql": "StringDatabaseField",
    "clickhouse": "Nullable(String)",
    "valid": true
  },
  "created_at": {
    "hogql": "DateTimeDatabaseField",
    "clickhouse": "DateTime64(3)",
    "valid": true
  }
}

Column	Type	Nullable	Description
`id`	String	NOT NULL	Schema UUID.
`team_id`	Integer	NOT NULL
`name`	String	NOT NULL	Name of the table/endpoint in the external source.
`source_id`	String	NOT NULL	Parent source; joins to data_warehouse_sources.id.
`table_id`	String	NOT NULL	Resulting warehouse table; joins to data_warehouse_tables.id.
`should_sync`	Boolean	NOT NULL	Whether this table is enabled for syncing.
`status`	String	NOT NULL	Latest sync status for this table, e.g. Running, Completed, Error.
`sync_type`	String	NOT NULL	Sync strategy, e.g. ‘full_refresh’ or ‘incremental’.
`last_synced_at`	DateTime	NOT NULL	When this table last finished syncing.
`latest_error`	String	NOT NULL	Most recent sync error message, if any.
`created_at`	DateTime	NOT NULL	When the schema config was created.
`updated_at`	DateTime	NOT NULL	When the schema config was last updated.
`deleted`	Integer	NOT NULL	1 if the schema config has been deleted, 0 otherwise.
`deleted_at`	DateTime	NOT NULL	When it was deleted; NULL if not deleted.

Column	Type	Nullable	Description
`id`	String	NOT NULL	Sync job UUID.
`team_id`	Integer	NOT NULL
`pipeline_id`	String	NOT NULL	Source whose pipeline ran; joins to data_warehouse_sources.id.
`schema_id`	String	NOT NULL	Source schema being synced; joins to source_schemas.id.
`status`	String	NOT NULL	Job status, e.g. Running, Completed, Failed.
`rows_synced`	Integer	NOT NULL	Number of rows synced by this job.
`billable`	Boolean	NOT NULL	Whether the rows synced count toward billing.
`latest_error`	String	NOT NULL	Error message if the job failed.
`created_at`	DateTime	NOT NULL	When the job started.
`finished_at`	DateTime	NOT NULL	When the job finished; NULL while running.
`updated_at`	DateTime	NOT NULL	When the job row was last updated.

SELECT t.name, t.row_count, s.source_type
FROM system.data_warehouse_tables AS t
INNER JOIN system.data_warehouse_sources AS s ON t.external_data_source_id = s.id
WHERE NOT t.deleted AND s.source_type = 'Stripe'

SELECT
  s.source_type,
  s.prefix,
  count(t.id) AS table_count,
  sum(t.row_count) AS total_rows
FROM system.data_warehouse_sources AS s
LEFT JOIN system.data_warehouse_tables AS t ON t.external_data_source_id = s.id AND NOT t.deleted
WHERE NOT s.deleted
GROUP BY s.source_type, s.prefix
ORDER BY table_count DESC

SELECT
  j.status,
  j.rows_synced,
  j.created_at,
  j.finished_at,
  j.latest_error,
  s.source_type
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
ORDER BY j.created_at DESC
LIMIT 50

SELECT
  j.pipeline_id,
  j.latest_error,
  j.created_at,
  s.source_type,
  s.prefix
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
WHERE j.status = 'Failed'
  AND j.created_at >= now() - INTERVAL 7 DAY
ORDER BY j.created_at DESC

SELECT
  s.source_type,
  s.prefix,
  count(j.id) AS total_jobs,
  countIf(j.status = 'Completed') AS completed,
  countIf(j.status = 'Failed') AS failed,
  sum(j.rows_synced) AS total_rows_synced
FROM system.source_sync_jobs AS j
INNER JOIN system.data_warehouse_sources AS s ON j.pipeline_id = s.id
GROUP BY s.source_type, s.prefix
ORDER BY total_jobs DESC