Self-service / SQL

Query the warehouse

Write SQL against the gold tables every dashboard reads. The engine is DuckDB compiled to WebAssembly: it runs in your browser, on your own copy of the synthetic warehouse, so a query never reaches a server. The database is read-only, file and network access are switched off, and a query that runs too long is stopped. Start from a worked example or from the SQL behind any governed metric, in DuckDB, PostgreSQL and T-SQL, and read each table's contract in the schema browser before you use a column. To explore without SQL, use the metric explorer.

Synthetic data for demonstration. Every study, site, subject and vendor is fictional. Designed to these controls. Not a validated system and not a compliance claim.

Engine

The engine is DuckDB compiled to WebAssembly. It downloads once (about 7 MB compressed), runs in its own worker in this tab, and holds your own copy of the gold tables. Nothing you type leaves the browser, and the database refuses files, the network and every change.

Query

Saved query

Every query not closed at the data cut, with its age in days, its rank by age within its study, the number open in the study and its site's median age. Closed studies are left out, as the certified open-query metrics leave them out.

Shows: window functions, star-schema joins. Reads: gold.fct_query, gold.dim_study, gold.dim_site, gold.dim_date.

SQL (DuckDB)

Start the engine to run queries. You can read and edit the SQL now.

Result

Rows appear here once the engine is running.

Schema

The gold layer the engine loads: every table and column with the description, owner and classification its data contract records.

gold.dim_studydimension · 8 rows

Conformed study dimension.

Grain: One row per study.
Primary key: study_key
Owner, steward: Portfolio Management Lead; Portfolio Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.dim_study
Column	Type and meaning
study_key	integer, not null · internalSurrogate key.
study_id	string, not null · internalProtocol id, the natural key.
study_title	string, not null · internalShort title.
therapeutic_area	string, not null · internalTherapeutic area.
product_type	string, not null · internalInvestigational product type.
phase	string, not null · internalDevelopment phase or device study stage.
regulatory_path	string, not null · internalIND or IDE.
operating_model	string, not null · internalSponsor or CRO.
cro_vendor_id	string · internalVendor id of the CRO.
study_status	string, not null · internalLifecycle status at the data cut.
start_on	date, not null · internalDate the protocol was finalised.
planned_enrollment	integer, not null · internalEnrollment target.
is_randomized	boolean, not null · internalWhether subjects are randomised.
arm_count	integer, not null · internalNumber of treatment arms.
has_cec_adjudication	boolean, not null · internalWhether a clinical events committee adjudicates events.
study_data_product_id	string · internalStudy Data Pipeline product id.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.dim_sitedimension · 265 rows

Conformed site dimension, slowly changing type 2 on site status and principal investigator.

Grain: One row per site version: a new row each time the status or the principal investigator changes.
Primary key: site_key
Owner, steward: Clinical Operations Lead; Clinical Operations Data Steward
Classification: confidential
Contract: v1.1.0

Columns of gold.dim_site
Column	Type and meaning
site_key	integer, not null · internalSurrogate key of the version.
site_id	string, not null · internalSite id, the natural key.
version_number	integer, not null · internalVersion of the site, from 1.
study_id	string, not null · internalProtocol id.
site_number	string, not null · internalSite number within the study.
site_name	string, not null · internalFictional site name.
country_code	string, not null · internalISO 3166-1 alpha-2 country code.
planned_enrollment	integer, not null · internalEnrollment target for the site.
selected_on	date · internalDate the site was selected (start-up stage 1).
contracted_on	date · internalDate the clinical trial agreement was executed (stage 2).
irb_approved_on	date · internalDate the IRB or ethics committee approved the study at the site (stage 3).
initiated_on	date · internalDate of the site initiation visit (stage 4).
activated_on	date · internalActivation date (stage 5).
first_subject_in_on	date · internalDate the site enrolled its first subject (stage 6).
site_status	string, not null · internalStatus during the version: start-up, active or closed.
principal_investigator_id	string · confidentialFictional id of the principal investigator during the version, or null when none is on file.
valid_from	date, not null · internalFirst day the version is in force (inclusive).
valid_to	date, not null · internalDay the next version takes over (exclusive), or 9999-12-31.
is_current	boolean, not null · internalWhether this is the latest version.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.dim_countrydimension · 12 rows

Conformed country dimension.

Grain: One row per country with a site.
Primary key: country_key
Owner, steward: Clinical Operations Lead; Clinical Operations Data Steward
Classification: public
Contract: v1.0.0

Columns of gold.dim_country
Column	Type and meaning
country_key	integer, not null · publicSurrogate key.
country_code	string, not null · publicISO 3166-1 alpha-2 country code.
country_name	string, not null · publicEnglish short name.
region	string, not null · publicOperational region.

gold.dim_datedimension · 1,827 rows

Conformed calendar dimension, whole years around every fact date.

Grain: One row per calendar day.
Primary key: date_key
Owner, steward: Data Platform Lead; Platform Data Steward
Classification: public
Contract: v1.0.0

Columns of gold.dim_date
Column	Type and meaning
date_key	integer, not null · publicSmart key, YYYYMMDD.
calendar_date	date, not null · publicThe day.
year_number	integer, not null · publicCalendar year.
quarter_number	integer, not null · publicCalendar quarter, 1 to 4.
month_number	integer, not null · publicCalendar month, 1 to 12.
month_name	string, not null · publicEnglish month name.
day_of_month	integer, not null · publicDay of the month.
iso_day_of_week	integer, not null · publicISO 8601 day of the week, Monday 1 to Sunday 7.
year_month	string, not null · publicYear and month, for example 2026-09.
year_quarter	string, not null · publicYear and quarter, for example 2026-Q3.
is_month_end	boolean, not null · publicWhether the day is the last of its month.

gold.dim_vendordimension · 8 rows

Conformed vendor dimension, with key 0 for spend that has no vendor (investigator grants).

Grain: One row per vendor, plus the not-applicable member.
Primary key: vendor_key
Owner, steward: Clinical Outsourcing Lead; Vendor Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.dim_vendor
Column	Type and meaning
vendor_key	integer, not null · internalSurrogate key; 0 is the not-applicable member.
vendor_id	string, not null · internalVendor id, or N/A.
vendor_name	string, not null · internalFictional vendor name.
vendor_service	string, not null · internalService the vendor provides.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.dim_milestonedimension · 9 rows

Conformed milestone dimension: the milestone kinds in lifecycle order.

Grain: One row per milestone kind.
Primary key: milestone_key
Owner, steward: Portfolio Management Lead; Portfolio Data Steward
Classification: public
Contract: v1.0.0

Columns of gold.dim_milestone
Column	Type and meaning
milestone_key	integer, not null · publicSurrogate key.
milestone_kind	string, not null · publicMilestone kind.
milestone_name	string, not null · publicNeutral display name.
milestone_sequence	integer, not null · publicPosition in the study lifecycle, from 1.

gold.dim_monitoring_plandimension · 8 rows

Monitoring plan dimension: the cadence and report deadline each study's plan sets, which monitoring is measured against.

Grain: One row per monitoring plan version.
Primary key: monitoring_plan_key
Owner, steward: Clinical Monitoring Lead; Clinical Operations Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.dim_monitoring_plan
Column	Type and meaning
monitoring_plan_key	integer, not null · internalSurrogate key.
monitoring_plan_id	string, not null · internalMonitoring plan id.
study_id	string, not null · internalProtocol id.
plan_version	string, not null · internalPlan version.
effective_date_key	integer, not null · internalDate the plan took effect.
interim_interval_days	integer, not null · internalPlanned days from one monitoring visit at a site to the next interim visit.
visit_window_days	integer, not null · internalDays an interim visit may fall after its planned date and still be on time.
report_due_days	integer, not null · internalDays from a visit by which its report must be final.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_enrollmentfact · 3,727 rows

Subject enrollment events: screened, enrolled, completed and discontinued.

Grain: One row per subject per enrollment event.
Primary key: subject_id, event_type
Owner, steward: Clinical Operations Lead; Clinical Operations Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.1.0

Columns of gold.fct_enrollment
Column	Type and meaning
subject_id	string, not null · confidentialSubject id (degenerate dimension).
event_type	string, not null · internalscreened, enrolled, completed or discontinued.
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force on the event date.
country_key	integer, not null · internalCountry of the site.
event_date_key	integer, not null · internalDate of the event.
subject_status	string, not null · internalSubject status at the data cut.
screen_failure_reason	string · internalWhy screening failed; null unless the subject failed screening (degenerate dimension).
event_count	integer, not null · internalAlways 1: the additive measure.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_visitfact · 7,727 rows

Subject visits against the schedule, with entry timeliness.

Grain: One row per subject visit record.
Primary key: visit_id
Owner, steward: Clinical Data Management Lead; Clinical Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.1.0

Columns of gold.fct_visit
Column	Type and meaning
visit_id	string, not null · confidentialVisit record id (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
planned_visit_id	string, not null · internalPlanned visit id.
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force on the visit date, or the planned date of a missed visit.
country_key	integer, not null · internalCountry of the site.
visit_date_key	integer · internalVisit date, or null for a missed visit.
planned_date_key	integer, not null · internalPlanned date: enrollment plus the planned day.
visit_status	string, not null · internalentered, pending-entry or missed.
day_offset	integer · internalActual minus planned day.
is_out_of_window	boolean, not null · internalWhether the visit fell outside its window.
entry_lag_days	integer · internalDays from the visit to data entry.
entry_hour	integer · internalHour of the day, 0 to 23 in UTC, the visit's data was entered.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_queryfact · 3,559 rows

Data queries with their resolution times.

Grain: One row per query.
Primary key: query_id
Owner, steward: Clinical Data Management Lead; Clinical Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.1.0

Columns of gold.fct_query
Column	Type and meaning
query_id	string, not null · internalQuery id (degenerate dimension).
visit_id	string, not null · confidentialVisit record id (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force when the query opened.
country_key	integer, not null · internalCountry of the site.
opened_date_key	integer, not null · internalUTC date the query opened.
answered_date_key	integer · internalUTC date the site answered.
closed_date_key	integer · internalUTC date the query closed.
queried_field	string, not null · internalQueried field, as DOMAIN.VARIABLE.
queried_form	string, not null · internalCRF form of the queried field: its CDASH domain, the part of queried_field before the dot.
query_origin	string, not null · internaledit-check or manual.
query_status	string, not null · internalState at the data cut.
minutes_to_answer	integer · internalMinutes from opening to the site's answer.
minutes_to_close	integer · internalMinutes from opening to closing.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_deviationfact · 329 rows

Protocol deviations with their time to identification.

Grain: One row per protocol deviation.
Primary key: deviation_id
Owner, steward: Clinical Quality Lead; Clinical Quality Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.0.0

Columns of gold.fct_deviation
Column	Type and meaning
deviation_id	string, not null · internalDeviation id (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force when the deviation occurred.
country_key	integer, not null · internalCountry of the site.
occurred_date_key	integer, not null · internalDate the deviation occurred.
identified_date_key	integer, not null · internalDate the deviation was identified.
deviation_category	string, not null · internalDeviation category.
importance	string, not null · internalimportant or not-important.
is_important	boolean, not null · internalWhether the deviation is important per ICH E3.
days_to_identify	integer, not null · internalDays from occurrence to identification.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_cec_adjudicationfact · 39 rows

Events sent to the clinical events committee, with when each was reported and adjudicated (#2881).

Grain: One row per event sent for adjudication.
Primary key: event_id
Owner, steward: Medical Safety Lead; Safety Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.0.0

Columns of gold.fct_cec_adjudication
Column	Type and meaning
event_id	string, not null · internalEvent id (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force when the event was reported.
country_key	integer, not null · internalCountry of the site.
occurred_date_key	integer, not null · internalDate the event occurred.
reported_date_key	integer, not null · internalDate the site reported the event.
adjudicated_date_key	integer · internalDate the committee adjudicated the event, or null while it waits.
days_to_adjudicate	integer · internalDays from the site's report to adjudication.
site_device_related	boolean, not null · internalWhether the site judged the event device-related.
cec_device_related	boolean · internalWhether the committee judged the event device-related.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_adverse_eventfact · 2,084 rows

Adverse events with their onset, report and seriousness, for safety reporting and central monitoring (#2956).

Grain: One row per adverse event.
Primary key: ae_id
Owner, steward: Medical Safety Lead; Safety Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.0.0

Columns of gold.fct_adverse_event
Column	Type and meaning
ae_id	string, not null · internalAdverse event id (degenerate dimension).
visit_id	string, not null · confidentialVisit record id the event was reported at (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force when the site reported the event.
country_key	integer, not null · internalCountry of the site.
onset_date_key	integer, not null · internalDate the event began.
reported_date_key	integer, not null · internalDate the site reported the event.
days_to_report	integer, not null · internalDays from onset to the site's report.
ae_term	string, not null · confidentialReported term.
is_serious	boolean, not null · internalWhether the event is serious (ICH E2A).
severity	string, not null · internalSeverity: mild, moderate or severe.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_vital_signfact · 6,959 rows

Vital signs by visit, one column per sign, for central monitoring of how sites record them (#2957).

Grain: One row per visit with vital signs.
Primary key: visit_id
Owner, steward: Clinical Data Management Lead; Clinical Data Steward
Classification: confidential, describes study subjects (synthetic)
Contract: v1.0.0

Columns of gold.fct_vital_sign
Column	Type and meaning
visit_id	string, not null · confidentialVisit record id the signs were taken at (degenerate dimension).
subject_id	string, not null · confidentialSubject id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force on the day of the visit.
country_key	integer, not null · internalCountry of the site.
measured_date_key	integer, not null · internalDate the signs were taken.
systolic_bp_mmhg	integer · confidentialSystolic blood pressure (VSTESTCD SYSBP), in mmHg; null when not recorded.
diastolic_bp_mmhg	integer · confidentialDiastolic blood pressure (VSTESTCD DIABP), in mmHg; null when not recorded.
pulse_bpm	integer · confidentialPulse rate (VSTESTCD PULSE), in beats/min; null when not recorded.
weight_kg	decimal · confidentialWeight (VSTESTCD WEIGHT), in kg; null when not recorded.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_monitoring_visitfact · 1,123 rows

Monitoring visits with source data verification and report timeliness.

Grain: One row per monitoring visit.
Primary key: monitoring_visit_id
Owner, steward: Clinical Monitoring Lead; Clinical Operations Data Steward
Classification: internal
Contract: v1.1.0

Columns of gold.fct_monitoring_visit
Column	Type and meaning
monitoring_visit_id	string, not null · internalMonitoring visit id (degenerate dimension).
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force on the visit date.
country_key	integer, not null · internalCountry of the site.
visit_date_key	integer, not null · internalVisit date.
report_final_date_key	integer · internalDate the report was finalised.
monitoring_plan_key	integer, not null · internalMonitoring plan in force on the visit date.
report_due_date_key	integer, not null · internalDate the report is due: the visit date plus the plan's report deadline.
visit_type	string, not null · internalSIV, IMV or COV.
visit_mode	string, not null · internalOn-site or remote.
sdv_pages_due	integer, not null · internalCRF pages due for source data verification.
sdv_pages_verified	integer, not null · internalCRF pages source-verified.
action_items	integer, not null · internalAction items raised.
days_to_final_report	integer · internalDays from the visit to the final report.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_planned_monitoring_visitfact · 1,077 rows

Interim monitoring visits the monitoring plan schedules at each active site, each against the visit that met it.

Grain: One row per planned interim monitoring visit: one per interim visit made, plus the next one due at each site still being monitored.
Primary key: planned_visit_id
Owner, steward: Clinical Monitoring Lead; Clinical Operations Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.fct_planned_monitoring_visit
Column	Type and meaning
planned_visit_id	string, not null · internalPlanned visit id (degenerate dimension): the site id and its sequence.
monitoring_plan_key	integer, not null · internalMonitoring plan the visit is scheduled under.
study_key	integer, not null · internalStudy.
site_key	integer, not null · internalSite version in force on the planned date.
country_key	integer, not null · internalCountry of the site.
planned_date_key	integer, not null · internalPlanned date: the previous monitoring visit at the site plus the plan's interval.
due_date_key	integer, not null · internalLast on-time date: the planned date plus the plan's visit window.
monitoring_visit_id	string · internalInterim visit that met the planned visit, or null while it is outstanding.
actual_date_key	integer · internalDate of the visit that met it, or null while it is outstanding.
days_late	integer · internalDays the visit fell after its due date, 0 when on time, or null while it is outstanding.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_milestonefact · 72 rows

Study milestones, planned against actual.

Grain: One row per study and milestone kind.
Primary key: study_key, milestone_key
Owner, steward: Portfolio Management Lead; Portfolio Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.fct_milestone
Column	Type and meaning
study_key	integer, not null · internalStudy.
milestone_key	integer, not null · internalMilestone kind.
milestone_label	string, not null · internalLabel in the study's own vocabulary.
planned_date_key	integer, not null · internalPlanned date.
actual_date_key	integer · internalActual date, or null when not reached.
variance_days	integer · internalActual minus planned date, in days.
is_reached	boolean, not null · internalWhether the milestone was reached by the data cut.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_spendfact · 1,089 rows

Budget against actual spend, with each quarter spread evenly over its three months.

Grain: One row per study, vendor and calendar month.
Primary key: study_key, vendor_key, month_date_key
Owner, steward: Clinical Finance Lead; Finance Data Steward
Classification: confidential
Contract: v1.0.0

Columns of gold.fct_spend
Column	Type and meaning
study_key	integer, not null · internalStudy.
vendor_key	integer, not null · internalVendor; 0 for investigator grants.
month_date_key	integer, not null · internalFirst day of the month.
budget_usd	integer, not null · confidentialBudgeted amount, whole US dollars.
actual_usd	integer, not null · confidentialActual spend, whole US dollars.
variance_usd	integer, not null · confidentialActual minus budget, whole US dollars.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_spend_forecastfact · 48 rows

Budget, actual spend and forecast at completion per study and vendor at the data cut. The forecast is the actual spend to date plus the planned budget still to come, scaled by the vendor's spend-to-budget ratio to date.

Grain: One row per study and vendor at the data cut.
Primary key: study_key, vendor_key
Owner, steward: Clinical Finance Lead; Finance Data Steward
Classification: confidential
Contract: v1.0.0

Columns of gold.fct_spend_forecast
Column	Type and meaning
study_key	integer, not null · internalStudy.
vendor_key	integer, not null · internalVendor; 0 for investigator grants.
as_of_date_key	integer, not null · internalThe data cut the forecast was made at.
budget_to_date_usd	integer, not null · confidentialBudget for every quarter booked up to the data cut, whole US dollars.
actual_to_date_usd	integer, not null · confidentialActual spend for every quarter booked up to the data cut, whole US dollars.
remaining_budget_usd	integer, not null · confidentialPlanned budget for the quarters after the data cut, whole US dollars; 0 once the study's final report is in.
budget_at_completion_usd	integer, not null · confidentialBudget to date plus the remaining budget, whole US dollars.
forecast_at_completion_usd	integer, not null · confidentialActual spend to date plus the remaining budget times actual over budget to date, rounded to whole US dollars; the remaining budget as planned when nothing was budgeted to date.
variance_at_completion_usd	integer, not null · confidentialForecast minus budget at completion, whole US dollars; above 0 is a forecast overrun.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.

gold.fct_kri_snapshotfact · 7,944 rows

Month-end key risk indicator snapshots per site, cumulative from activation to the snapshot date.

Grain: One row per site, month-end snapshot date and key risk indicator.
Primary key: site_key, snapshot_date_key, kri_id
Owner, steward: Clinical Quality Lead; Clinical Quality Data Steward
Classification: internal
Contract: v1.0.0

Columns of gold.fct_kri_snapshot
Column	Type and meaning
site_key	integer, not null · internalSite version in force on the snapshot date.
snapshot_date_key	integer, not null · internalMonth-end snapshot date.
kri_id	string, not null · internalKey risk indicator: query-rate, missed-visit-rate, important-deviation-rate or entry-lag-days.
study_key	integer, not null · internalStudy.
country_key	integer, not null · internalCountry of the site.
numerator	integer, not null · internalNumerator, cumulative to the snapshot date.
denominator	integer, not null · internalDenominator, cumulative to the snapshot date.
kri_value	decimal · internalNumerator over denominator, to 4 places; null when the denominator is 0.
is_synthetic	boolean, not null · publicAlways true: the row describes synthetic data, nothing real.