Catalog: Reporting and Transactional Tables

This catalog provides a high-level description of available tables. Note: ‘SOURCE’ can include WebPT Billing and WebPT EMR. These tables contain different IDs for each clinic, patient, and therapist because they do come from different databases. SVW_CLINIC, SVW_PATIENT, and SVW_THERAPIST should allow you to map across EMR to Billing tables (regardless of the ‘SOURCE’ or ‘TYPE’). Note: SVW = Snowflake Virtual Warehouse.

Table Types

Informational: Informational tables from Snowflake.
Standard Extract: These are the usual datamart tables we use in our standard extract for members. Primary keys are sometimes a singular column or are meant to be a composite key. There are record_id's on each table that are unique to each record, but only for that table. The unique key examples given in this document are meant to help with updates by record. Updates to records are denoted by export_flags (1,2,3). 1 = New, 2 = Modify, 3 = Delete.
Other Reporting: Remaining WebPT EMR tables and other tables that support our Analytics application.
Transactional (Hidden): Most are marked with 'SVW_Revflow' and are tables from the Billing database. These tables are mostly normalized and contain a primary key, as well as an is_active flag. The is_active flag is important for reporting needs due to the fact most reporting in the application does not include inactive records. Transaction tables are available upon request.

Table Schema: INFORMATION_SCHEMA

TABLE_NAME	TYPE	SOURCE	COMMENT
TABLES	Informational	Snowflake Default	The tables defined in this database accessible to the current user's role.
COLUMNS	Informational	Snowflake Default	The columns of tables defined in this database accessible to the current user's role.
SCHEMATA	Informational	Snowflake Default	The schemas defined in this database accessible to the current user's role.
SEQUENCES	Informational	Snowflake Default	The sequences defined in this database accessible to the current user's role.
VIEWS	Informational	Snowflake Default	The views defined in this database accessible to the current user's role.
TABLE_PRIVILEGES	Informational	Snowflake Default	The privileges on tables defined in this database accessible to the current user's role.
USAGE_PRIVILEGES	Informational	Snowflake Default	The usage privileges on sequences defined in this database accessible to the current user's role.
DATABASES	Informational	Snowflake Default	The databases that are accessible to the current user's role.
REPLICATION_DATABASES	Informational	Snowflake Default	The databases for replication accessible to the current user's role.
FUNCTIONS	Informational	Snowflake Default	The user-defined functions defined in this database accessible to the current user's role.
PROCEDURES	Informational	Snowflake Default	The stored procedures defined in this database accessible to the current user's role.
OBJECT_PRIVILEGES	Informational	Snowflake Default	The privileges on all objects defined in this database accessible to the current user's role.
FILE_FORMATS	Informational	Snowflake Default	The file formats defined in this database accessible to the current user's role.
APPLICABLE_ROLES	Informational	Snowflake Default	The roles that can be applied to the current user.
ENABLED_ROLES	Informational	Snowflake Default	The roles that are enabled to the current user.
STAGES	Informational	Snowflake Default	Stages in this database that are accessible by the current user's role
REFERENTIAL_CONSTRAINTS	Informational	Snowflake Default	Referential Constraints in this database that are accessible to the current user
TABLE_CONSTRAINTS	Informational	Snowflake Default	Constraints defined on the tables in this database that are accessible to the current user
INFORMATION_SCHEMA_CATALOG_NAME	Informational	Snowflake Default	Identifies the database (or catalog, in SQL terminology) that contains the information_schema
LOAD_HISTORY	Informational	Snowflake Default	The loading information of the copy command

Table Schema: PUBLIC

View the complete table schema here.