# Data cloud ## How it works Swarmia sends your data in the form of a Parquet file to your storage bucket. Currently Amazon S3 is supported. This data can then be pulled to your data warehouse of choice, such as Snowflake. The full dataset will be refreshed every 8 hours to ensure recent enough data when the exported files are pulled. ## Exported data Currently, pull requests, commits, deployments, teams, and related data are being exported, split into multiple files: * `pull_requests.parquet` * `deployments.parquet` * `authors.parquet` * `pull_request_reviews.parquet` * `pull_request_review_requests.parquet` * `pull_request_comments.parquet` * `commits.parquet` * `teams.parquet` Files are written under a version prefix (for example `v0/`) with **lowercase** names such as `v0/pull_requests.parquet` (not uppercase paths). The pull request data contain: * Author information * Contributing teams * Investment categories * [Pull request cycle time components](https://help.swarmia.com/pull-request-cycle-time): cycle time, progress time, time in review, and time in merge * Timestamps of pull request events: first review time, first and last commit time, etc... * Pull request comments by authors, review requests, when pull request was reviewed and by who The deployments data contain: * List of deployed PRs in the deployment, plus involved teams and authors * Deployment information: version, environment, application * Change failure: `is_change_failure` (boolean) and optional `change_failure_source`; use this for change-failure rate (CFR) style metrics * DORA-style timing fields where available: [Time to deploy](https://help.swarmia.com/time-to-deploy), [Change lead time](https://help.swarmia.com/change-lead-time); optional recovery timing may be present depending on export The commits data contain: * Commit hash * Author information * Commit message * Associated pull requests, i.e. which ones the commit participated * Committed and (if available) authored dates * Parent commits ## Getting started The setup requires you to have an Amazon S3 storage bucket to which Swarmia can push data. ### **Set up the integration** 1. Navigate to [Data Cloud settings](https://app.swarmia.com/settings/data-cloud) 2. Insert your S3 bucket credentials 3. Save the settings. After this, you can test the integration from the "Export" button in the UI. ### **Considerations for the S3 bucket** Swarmia requires the following permissions on an S3 bucket and folder to be able to upload the exports: * `s3:GetBucketLocation` * `s3:PutObject` An example of AWS IAM Policy for the export bucket, where `bucket name` is the name of the bucket you provided use in the Data Cloud settings. ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:PutObject", ], "Resource": "arn:aws:s3:::/*" }, { "Effect": "Allow", "Action": [ "s3:GetBucketLocation" ], "Resource": "arn:aws:s3:::", "Condition": { "StringLike": { "s3:prefix": [ "*" ] } } } ] } ``` If you will access the data directly from the warehouse you most likely will need additional permissions. The following IAM Policy is the one recommended by Snowflake, which works then in both directions: Swarmia to upload and Snowflake to extract data from the same bucket. ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:PutObject", "s3:GetObject", "s3:GetObjectVersion", "s3:DeleteObject", "s3:DeleteObjectVersion" ], "Resource": "arn:aws:s3:::/*" }, { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:GetBucketLocation" ], "Resource": "arn:aws:s3:::", "Condition": { "StringLike": { "s3:prefix": [ "*" ] } } } ] } ``` ### Loading data to data warehouse You can load data into various warehouse systems using their native S3 connection methods. Here's a list of some commonly used systems with links to their respective documentations: * BigQuery, by using [BigQuery Data Transfer Service for Amazon S3](https://cloud.google.com/bigquery/docs/s3-transfer) * AWS Redshift, by [copying data from S3](https://docs.aws.amazon.com/redshift/latest/dg/tutorial-loading-data.html) with [parquet format](https://aws.amazon.com/about-aws/whats-new/2018/06/amazon-redshift-can-now-copy-from-parquet-and-orc-file-formats/) * Snowflake, with [bulk loading from S3](https://docs.snowflake.com/en/user-guide/data-load-s3) **Example: Loading data into Snowflake** The following SQL template can get you started with Swarmia Data Cloud and Snowflake. Use this template to create necessary Snowflake stages, file formats, and data tables to start making analytics based on Swarmia data. **Parquet lists and Snowflake types:** In Parquet, repeated string fields are logical arrays, but `COPY INTO` with `MATCH_BY_COLUMN_NAME` often loads them in a form Snowflake treats as semi-structured data rather than native `ARRAY`. Declaring those columns as `VARIANT` avoids cast failures; you can still use `LATERAL FLATTEN` or cast to `ARRAY` where needed. Make sure to set up the access from Snowflake to the S3 bucket by following [Snowflake's official instructions](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration). ``` ------------------------------------------------------------------------------------------- -- Create the integration to AWS S3 bucket and the Snowflake stages -- Create the storage integration for the AWS S3 bucket you have configured with Swarmia Data Cloud. -- To create the role follow the Snowflake instructions in -- https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration#step-2-create-the-iam-role-in-aws ------------------------------------------------------------------------------------------- CREATE OR REPLACE STORAGE INTEGRATION swarmia_data_cloud_s3 TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam:::role/' STORAGE_ALLOWED_LOCATIONS = ('s3://'); -- Describe our Integration DESCRIBE INTEGRATION swarmia_data_cloud_s3; SHOW STORAGE INTEGRATIONS; -- Create the parquet format for Swarmia data CREATE OR REPLACE FILE FORMAT swarmia_parquet TYPE = parquet; -- Create the stage for the Swarmia exported data CREATE OR REPLACE STAGE swarmia_data_cloud_s3_stage URL = 's3:///' STORAGE_INTEGRATION = swarmia_data_cloud_s3 -- created in previous step FILE_FORMAT = swarmia_parquet; -- View our Stages SHOW STAGES; DESCRIBE STAGE swarmia_data_cloud_s3_stage; LIST @swarmia_data_cloud_s3_stage; --- -- Create the target tables (list-like Parquet columns as VARIANT; see note above) --- -- Create table for pull requests CREATE OR REPLACE TABLE swarmia_pull_requests ( id text not null, github_number int not null, title text not null, description text, author_id text not null, author_external_identifier text not null, github_repository_full_name text not null, pr_status text not null, owner_team_ids variant, owner_team_names variant, investment_category_ids variant, investment_category_names variant, labels variant, is_draft boolean not null, is_excluded boolean not null, github_created_at timestamp not null, github_updated_at timestamp not null, last_closed_or_merged_at timestamp, first_commit_at timestamp, last_commit_at timestamp, opened_at timestamp, first_review_request_at timestamp, first_reviewed_at timestamp, first_approved_review_at timestamp, last_approved_review_at timestamp, cycle_time_started_at timestamp not null, cycle_time_ended_at timestamp, additions bigint, deletions bigint, cycle_time_seconds bigint, progress_time_seconds bigint, review_time_seconds bigint, merge_time_seconds bigint, linked_swarmia_issue_id text, is_revert boolean, changed_files variant ); CREATE OR REPLACE TABLE swarmia_deployments ( id text not null, pull_request_ids variant, deployment_environment text not null, deployed_at timestamp not null, fixes_deployment_id text, fixes_version text, deployment_version text not null, deployment_app_name text not null, involved_team_ids variant, involved_team_names variant, involved_author_ids variant, time_to_recovery_seconds int, change_lead_time_seconds int, time_to_deploy_seconds int, is_change_failure boolean not null, change_failure_source text ); CREATE OR REPLACE TABLE swarmia_authors ( id text not null, name text, associated_emails variant ); CREATE OR REPLACE TABLE swarmia_teams ( id text not null, parent_team_id text, name text not null, created_at timestamp not null, deleted_at timestamp, member_author_ids variant ); CREATE OR REPLACE TABLE swarmia_pull_request_comments ( id text not null, pull_request_id text not null, source_id text not null, body_text text not null, created_at timestamp not null, author_id text not null, author_is_bot boolean not null ); CREATE OR REPLACE TABLE swarmia_pull_request_review_requests ( id text not null, pull_request_id text not null, requested_by_author_id text not null, requested_from_author_id text, requested_from_team_name text, source_id text not null, requested_at timestamp not null ); CREATE OR REPLACE TABLE swarmia_pull_request_reviews ( id text not null, pull_request_id text not null, author_id text not null, author_external_identifier text not null, source_id text not null, state text not null, submitted_at timestamp not null, github_repository_full_name text not null, source_repository_full_name text not null, pull_request_review_request_id text ); CREATE OR REPLACE TABLE swarmia_commits ( id text not null, git_hash text not null, author_id text not null, message text not null, associated_pull_request_ids variant, committed_date timestamp not null, authored_date timestamp, parents variant ); ------------------------------------------------------------------------------------------- -- Load data into the Snowflake tables ------------------------------------------------------------------------------------------- -- Remove any old data the table has to prevent duplicates TRUNCATE TABLE swarmia_pull_requests; TRUNCATE TABLE swarmia_deployments; TRUNCATE TABLE swarmia_authors; TRUNCATE TABLE swarmia_teams; TRUNCATE TABLE swarmia_pull_request_comments; TRUNCATE TABLE swarmia_pull_request_review_requests; TRUNCATE TABLE swarmia_pull_request_reviews; TRUNCATE TABLE swarmia_commits; -- Copy the whole dataset into the target tables COPY INTO swarmia_pull_requests FROM @swarmia_data_cloud_s3_stage/v0/pull_requests.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_deployments FROM @swarmia_data_cloud_s3_stage/v0/deployments.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_authors FROM @swarmia_data_cloud_s3_stage/v0/authors.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_pull_request_comments FROM @swarmia_data_cloud_s3_stage/v0/pull_request_comments.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_pull_request_review_requests FROM @swarmia_data_cloud_s3_stage/v0/pull_request_review_requests.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_pull_request_reviews FROM @swarmia_data_cloud_s3_stage/v0/pull_request_reviews.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_commits FROM @swarmia_data_cloud_s3_stage/v0/commits.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; COPY INTO swarmia_teams FROM @swarmia_data_cloud_s3_stage/v0/teams.parquet MATCH_BY_COLUMN_NAME='CASE_INSENSITIVE'; ------------------------------------------------------------------------------------------- -- Query the data! ------------------------------------------------------------------------------------------- SELECT COUNT(*) FROM swarmia_pull_requests; SELECT COUNT(*) FROM swarmia_commits; SELECT * FROM swarmia_deployments; -- Monthly average change lead time for 'Engineers' team pull requests -- created since 2023 that have been deployed into production environment. -- With VARIANT list columns, cast to ARRAY for ARRAY_CONTAINS where needed. SELECT ROUND(AVG(d.change_lead_time_seconds)) AS change_lead_time_seconds, DATE_TRUNC('month', p.github_created_at) AS month FROM swarmia_deployments d JOIN swarmia_pull_requests p ON ARRAY_CONTAINS(p.id::variant, d.pull_request_ids::array) WHERE ARRAY_CONTAINS('Engineers'::variant, p.owner_team_names::array) AND d.deployment_environment = 'production' AND p.github_created_at >= '2023-01-01'::timestamp GROUP BY 2 ORDER BY 2 DESC; ``` ### Parquet schemas All export schemas are defined as follows. Repeated UTF8 fields are Parquet’s representation of string lists; in Snowflake, treat them as `VARIANT` when loading (see above). **pull\_request** | **field** | **type** | **required** | **description** | | ------------------------------ | ----------------- | ------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | id | UTF8 | Y | UUID | | github\_number | INT64 | Y | GitHub number, like 5532 | | author\_id | UTF8 | Y | Created by, UUID | | title | UTF8 | Y | Pull request title | | description | UTF8 | N | Pull request description | | author\_external\_identifier | UTF8 | Y | Created by nickname | | github\_repository\_full\_name | UTF8 | Y | Repository's name | | pr\_status | UTF8 | Y | One of the following: OPEN, CLOSED, MERGED | | owner\_team\_ids | Array, UTF8 | Y | Who owns the pull request | | owner\_team\_names | Array, UTF8 | Y | Team names of the | | investment\_category\_ids | Array, UTF8 | Y | In which investment categories pull request belongs to | | investment\_category\_names | Array, UTF8 | Y | Linked investment categories names | | labels | Array, UTF8 | Y | Attached labels to pull request | | is\_draft | BOOLEAN | Y | Whether the PR is currently marked as draft in GitHub. If the PR was originally opened as draft, but has since been marked ready for review, this will be `false`. | | is\_excluded | BOOLEAN | Y | Whether the PR is currently excluded from metrics and alerts in Swarmia. This includes both rule-based exclusions and manual exclusions. | | github\_created\_at | TIMESTAMP\_MICROS | Y | When the PR was created on GitHub. This is unaffected by whether the PR was opened as draft or not. | | github\_updated\_at | TIMESTAMP\_MICROS | Y | Last time the PR data was updated in GitHub. This includes pushing code, reviews, comments, etc. | | last\_closed\_or\_merged\_at | TIMESTAMP\_MICROS | N | Most recent `ClosedEvent` for the PR, which covers both regular closing and merge actions. `null` if the PR hasn’t been closed or merged yet. If a closed PR is reopened, this goes back to `null`. | | first\_commit\_at | TIMESTAMP\_MICROS | N | The authored date (falling back to committed date) of the first commit associated with the PR. | | last\_commit\_at | TIMESTAMP\_MICROS | N | The authored date (falling back to committed date) of the last commit associated with the PR. | | opened\_at | TIMESTAMP\_MICROS | N | When the PR was created on GitHub. This is unaffected by whether the PR was opened as draft or not. | | first\_review\_request\_at | TIMESTAMP\_MICROS | N | Time of the first `ReviewRequestedEvent` for the PR. | | first\_reviewed\_at | TIMESTAMP\_MICROS | N | Time of the first `PullRequestReview` for the PR. This excludes reviews by bots, and comments by the author themselves. | | first\_approved\_review\_at | TIMESTAMP\_MICROS | N | Time of the first `PullRequestReview` for the PR, with state `approved`. This excludes reviews by bots. | | last\_approved\_review\_at | TIMESTAMP\_MICROS | N | Time of the last `PullRequestReview` for the PR, with state `approved`. | | cycle\_time\_started\_at | TIMESTAMP\_MICROS | Y | Time of the first commit associated with the PR, or time when the PR was created on GitHub, whichever came earlier. | | cycle\_time\_ended\_at | TIMESTAMP\_MICROS | N | Time when the PR was merged or closed. `null` if the PR is still open. | | additions | INT64 | N | How many lines were added | | deletions | INT64 | N | How many lines were deleted | | cycle\_time\_seconds | INT64 | N | Amount of seconds between start and end of cycle time. If the PR is still open, the current time is considered the end. | | progress\_time\_seconds | INT64 | N |

Amount of seconds between start and end of in progress time. Start is the same as cycle time start time. End is defined as, in order of preference:

  • Start of review time
  • Start of merge time
  • Cycle time end time
  • Current time
| | review\_time\_seconds | INT64 | N |

Amount of seconds between start and end of review time. Start is defined as:

  • For PRs without a ReadyForReviewEvent or ReviewRequestedEvent, and which don’t have approving reviews, review time is not defined

  • Otherwise, start is, in order of preference:

    • Time of first ReviewRequestedEvent
    • Time of latest ReadyForReviewEvent
    • Time when PR was opened

End is defined as, in order of preference:

  • Start of merge time
  • End of cycle time
  • Current time
| | merge\_time\_seconds | INT64 | N |

Amount of seconds between start and end of merge time. Start is defined by, in order of precedence:

  • For merged and approved PRs, the time of the last approval
  • For merged PRs with review requested, but never given, merge time is not defined
  • For merged PRs without reviews requested, time of last commit, or PR open time, whichever comes last
    End is the same as the end of cycle time. If the PR is still open, the current time is considered the end.
| | linked\_swarmia\_issue\_id | UTF8 | N | Linked swarmia issue | | is\_revert | Boolean | N | Did the pull request revert existing pull request | | changed\_files | Array, UTF8 | Y | Paths of changed files (repeated UTF8 in Parquet; use VARIANT or ARRAY in Snowflake) | **pull\_request\_review\_requests** | **field** | **type** | **required** | **description** | | --------------------------- | ----------------- | ------------ | ------------------------------ | | id | UTF8 | Y | UUID | | pull\_request\_id | UTF8 | Y | Linked pull request, UUID | | requested\_by\_author\_id | UTF8 | Y | Who requested review | | requested\_from\_author\_id | UTF8 | N | From whom review was requested | | requested\_from\_team\_name | UTF8 | N | Team whom review was requested | | source\_id | UTF8 | Y | e.g GitHub's ID | | requested\_at | TIMESTAMP\_MICROS | Y | When review was requested | **pull\_request\_reviews** | **field** | **type** | **required** | **description** | | ---------------------------------- | ----------------- | ------------ | -------------------------------------------------------------- | | id | UTF8 | Y | UUID | | pull\_request\_id | UTF8 | Y | Linked pull request, UUID | | author\_id | UTF8 | Y | Reviewed by, UUID | | author\_external\_identifier | UTF8 | Y | Reviewed by's nickname | | source\_id | UTF8 | Y | e.g GitHub's ID | | state | UTF8 | Y | One of the following : approved, changes\_requested, commented | | submitted\_at | TIMESTAMP\_MICROS | Y | When review was submitted | | github\_repository\_full\_name | UTF8 | Y | Deprecated, repository name | | source\_repository\_full\_name | UTF8 | Y | Repository name | | pull\_request\_review\_request\_id | UTF8 | N | Review request linked to review, UUID | **pull\_request\_comments** | **field** | **type** | **required** | **description** | | ----------------- | ----------------- | ------------ | ---------------------------- | | id | UTF8 | Y | UUID | | pull\_request\_id | UTF8 | Y | Linked pull request, UUID | | source\_id | UTF8 | Y | e.g GitHub's ID | | body\_text | UTF8 | Y | Comment content | | created\_at | TIMESTAMP\_MICROS | Y | When the comment was posted | | author\_id | UTF8 | Y | Who posted the comment, UUID | | author\_is\_bot | BOOLEAN | Y | Is the author a bot | **deployments** | **field** | **type** | **required** | **description** | | --------------------------- | ----------------- | ------------ | ---------------------------------------------------------------- | | id | UTF8 | Y | UUID | | pull\_request\_ids | Array, UTF8 | Y | Pull requests included in the deployment | | deployment\_environment | UTF8 | Y | Environment name (for example production) | | deployed\_at | TIMESTAMP\_MICROS | Y | When the deployment completed | | fixes\_deployment\_id | UTF8 | N | If set, deployment that this one fixes (hotfix / rollback chain) | | fixes\_version | UTF8 | N | Version identifier of the fixed deployment | | deployment\_version | UTF8 | Y | Version label for this deployment | | deployment\_app\_name | UTF8 | Y | Application name | | involved\_team\_ids | Array, UTF8 | Y | Team ids involved | | involved\_team\_names | Array, UTF8 | Y | Team names involved | | involved\_author\_ids | Array, UTF8 | Y | Author ids involved | | time\_to\_recovery\_seconds | INT64 | N | Recovery time when applicable | | change\_lead\_time\_seconds | INT64 | N | [Change lead time](https://help.swarmia.com/change-lead-time) | | time\_to\_deploy\_seconds | INT64 | N | [Time to deploy](https://help.swarmia.com/time-to-deploy) | | is\_change\_failure | BOOLEAN | Y | Prefer this for CFR-style filters (failed deployment / incident) | | change\_failure\_source | UTF8 | N | Optional classification of the failure | **authors** | **field** | **type** | **required** | **description** | | ------------------ | ----------- | ------------ | ---------------------------- | | id | UTF8 | Y | UUID | | name | UTF8 | N | Author's name | | associated\_emails | Array, UTF8 | Y | Author's all email addresses | **teams** | **field** | **type** | **required** | **description** | | ------------------- | ----------------- | ------------ | ------------------------------ | | id | UTF8 | Y | Team UUID | | parent\_team\_id | UTF8 | N | Parent team, if any | | name | UTF8 | Y | Team name | | created\_at | TIMESTAMP\_MICROS | Y | When the team was created | | deleted\_at | TIMESTAMP\_MICROS | N | When the team was deleted | | member\_author\_ids | Array, UTF8 | Y | Swarmia author ids in the team | **commits** | **field** | **type** | **required** | **description** | | ------------------------------ | ----------------- | ------------ | --------------------------------------------------------------------------------------------------------------------------- | | id | UTF8 | Y | UUID | | git\_hash | UTF8 | Y | Commit's SHA-1 git hash | | author\_id | UTF8 | Y | Who authored the commit, UUID | | message | UTF8 | Y | Commit message | | associated\_pull\_request\_ids | Array, UTF8 | N | Pull requests the commit was part of | | committed\_date | TIMESTAMP\_MICROS | Y | Commit "last updated" date | | authored\_date | TIMESTAMP\_MICROS | N | Original authored date | | parents | Array, UTF8 | N | Zero or more parent commit git hashes. Ordinary commits have one, root commits zero, and merge commits two or more parents. | --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://help.swarmia.com/settings/integrations/data-export/data-cloud.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.