Four activity tables
Keep aggregate and software-version activity separate at daily and weekly grains.
| Table | Row grain | Series | Stable key | Values |
|---|---|---|---|---|
| ADOPTION_DAILY | Artifact × day | Aggregate | point_id | value, adjusted_value |
| ADOPTION_WEEKLY | Artifact × Monday–Sunday week | Aggregate | point_id | value, adjusted_value |
| ADOPTION_VERSIONS_DAILY | Artifact × software version × known day | Versions | point_id | value; no aggregate adjustment |
| ADOPTION_VERSIONS_WEEKLY | Artifact × software version × known week | Versions | point_id | value; no aggregate adjustment |
Facts retain source-native measures and canonical identities. A company publication is the consistency boundary; different companies may have different publication times.
Activity fields
Snowflake numeric columns use NUMBER(38,12); the public contract describes their logical decimal type.
| Field | Logical type | Nullable | Definition |
|---|---|---|---|
| point_id | STRING | No | Stable identity from artifact, optional version, grain, and period end; identical to MCP point identity. |
| company_id | STRING | No | Canonical company join key; ticker is a display attribute. |
| project_id / artifact_id | STRING | No | Published identities joining the project and artifact dictionaries. |
| source / entity_id / metric | STRING | No | Ecosystem, exact package/container/provider identity, and source measure. |
| period_start / period_end | DATE | No | Daily dates coincide; weekly dates describe Monday–Sunday, including a partial first version coverage week. |
| value | DECIMAL(38,12) | Aggregate: yes; versions: no | Cleaned activity. Preserve fractional observations and known zeroes; reject lossy conversion. |
| adjusted_value | DECIMAL(38,12) | Yes | Aggregate only. Published adjustment with fallback to value. Missing aggregate remains null. |
| version_id / version / kind | STRING | Versions only | Exact version identity and named/other/unattributed kind. Preserve residual members. |
Structured project attributes and event version lists use Snowflake VARIANT and nested values in the proposed S3 representation. first_observed, last_observed, and event date use DATE.
Metadata and joins
Deliver interpretation dictionaries with the same publication as the facts.
| Table | Key | Fields | Join convention |
|---|---|---|---|
| ADOPTION_PROJECTS | project_id | company_id, ticker, project_key, name, taxonomy, ownership, ai_profile | company_id + project_id |
| ADOPTION_ARTIFACTS | artifact_id | company_id, project_id, source, entity_id, metric | company_id + artifact_id |
| ADOPTION_VERSIONS | version_id | company_id, artifact_id, version, kind, first_observed, last_observed | company_id + artifact_id + version_id |
| ADOPTION_EVENTS | event_id | company_id, project_id, date, subject_key, source, metric, event_type, effect_type, maturity, direction, impact_percent, versions, label | Event context by project/source/metric/date; not a one-to-one point join |
Use company identity and the matching delivery publication when joining. Event joins may return multiple events per observation; aggregate event context before joining it to activity to avoid multiplying values. Taxonomy and ownership describe the delivered mapping, not an as-known-at-the-time corporate history.
Proposed warehouse views
Expose typed current views over publication-scoped staging tables.
ADOPTION_ACTIVITY
Union the four fact tables with grain (daily/weekly) and series_kind (aggregate/versions). Keep value, adjusted_value, version identity, and publication_id explicit. Aggregate rows have null version fields; version rows have null adjusted values. Select a series kind before totaling activity.
ADOPTION_ACTIVITY_ENRICHED
Join the activity view to the matching project, artifact, and version dictionaries to add project name, taxonomy, ownership, AI profile, version kind, and observation bounds. Do not join raw event rows into this view.
ADOPTION_DELIVERY_MANIFEST
One published record per company with publication_id, data_schema_version, source_committed_at, delivered_at, table row counts, coverage bounds, and immutable snapshot references. Publication metadata belongs to the delivery layer, separate from the source fact fields.
Publication and delivery plan
Make validated company publications visible together, then expose agreed consumer access.
| Stage | Design |
|---|---|
| 1. Contract | Agree covered companies, datasets, history, cadence, destination, and named consumer access. |
| 2. Validate | Expand one committed publication; check keys, dictionary joins, sparse version membership, numeric precision, and per-table counts. |
| 3. Stage | Write all facts and dictionaries with one company-scoped publication ID into immutable staging. |
| 4. Publish | Advance the company publication manifest only after every table passes checks. Snowflake consumers join the published manifest; S3 consumers pin the complete manifest. |
| 5. Operate | Retry idempotently, remove stale current rows on corrections, retain agreed snapshots, and monitor source-to-delivery lag. |
Snowflake: stage immutable publication rows, then switch the manifest pointer and current views through a validated DML transaction. Readers performing multiple queries pin the manifest publication IDs for their extract.
S3: deliver proposed Parquet tables and dictionaries under immutable publication paths, with a manifest containing object identities, checksums, row counts, and schema version. Publish the manifest last and retain the previous complete delivery on failure.
Research conventions
- Daily history is bounded to 730 days where source coverage supports it. Stored weekly history can extend further and is not rebuilt from the retained daily window.
- Missing aggregate observations remain null. An absent version point is unknown; known zeroes and residual/unattributed versions remain intact.
- Version activity is unadjusted. Compare its conservation to cleaned aggregate activity over compatible known coverage, never to adjusted totals.
- Weekly intervals are Monday–Sunday. An initial version week can have partial coverage; do not describe it as a complete observed week.
- Current views contain corrected maintained history. Preserve agreed dated snapshots and publication IDs for point-in-time research.
- Do not combine unlike source units or treat package activity as customers, paid seats, or revenue.