Data Team Platform

1. You are here:
2. Business Technology
3. Data Team
4. Data Team Platform

Maintained by:

E

On this page

• Quick Links
• Our Data Stack
• Extract and Load
  • Adding new Data Sources and Fields
  • Data Team Access to Data Sources
    • DataSiren
  • Using SheetLoad
    • Considerations
      • Loading into Snowflake
      • Preparing for SheetLoad
      • Modeling
      • Boneyard
      • Certificates
  • Qualtrics Data Push / Qualtrics SheetLoad
  • Snowplow Infrastructure
  • Data Source Overviews
• Orchestration
• Data Warehouse
  • Warehouse Access
  • Snowflake Permissions Paradigm
    • User Roles
    • Functional Roles
    • Object Roles
    • Examples
  • Compute Resources
  • Data Storage
    • Raw
    • Prep
    • Prod
    • Folder Structure in Analytics Project
    • Static
  • Timezones
  • Snapshots
    • dbt
    • Other uses
    • Language
  • Backups
• Transformation
• Trusted Data Framework
  • Key Terms
  • Trusted Data Components
    • Virtuous Test Cycle
    • Test Cases Expressed As SQL and YAML
    • Trusted Data Schema
    • Schema To Golden Record Coverage
    • Trusted Data Dashboard
    • Test Run
• Data Pump
  • Adding a Data Pump
• Visualization
  • Meta Analyses for the Data Team
• Security
  • Passwords
  • Software User Provisioning

Quick Links

• Sisense (Periscope)
• dbt Guide
• Enterprise Data Warehouse
• Data Infrastructure
• SQL Style Guide
• Python Guide
• Permifrost
• Snowplow
• Data CI Jobs
• Sisense Style Guide

Our Data Stack

We use GitLab to operate and manage the analytics function. Everything starts with an issue. Changes are implemented via merge requests, including changes to our pipelines, extraction, loading, transformations, and parts of our analytics.

Extract and Load

We currently use Stitch and Fivetran for most of our data sources. These are off-the-shelf ELT tools that remove the responsibility of building, maintaining, or orchestrating the movement of data from some data sources into our Snowflake data warehouse. We run a full-refresh of all of our Stitch/Fivetran data sources at the same time that we rotate our security credentials (approx every 90 days). Prior to running a full refresh we will drop all of the tables.

For source ownership please see the Tech Stack Applications sheet (internal only).

Key

• Pipeline: The technology we use to replicate data.
• RF (Replication Frequency): How often we load new and updated data.
• Raw Schema: The schema in the RAW database where data is stored.
• Prep Schema: The schema in the PREP database where source models are materialized.
• Audience: The primary users of the data.
• SLO: Service Level Objective. Our SLO is the time between real-time and the analysis displayed in the data visualization tool
• x indicates undefined or not run

Adding new Data Sources and Fields

Process for adding a new data source:

• Create a new issue in the Analytics project requesting for the data source to be added:
  • Document what tables and fields are required
  • Document the questions that this data will help answer
• Create an issue in the Security project and cross-link to the Analytics issue.
  • Tag the Security team gitlab-com/gl-security

To add new fields to the BambooHR extract:

• Create a new issue in the Analytics project using the BambooHR template
• Gain approval from a Data Team Manager and the Compensation and Benefits Manager
• Once approved, assign to the Compensation and Benefits Manager and a Data Engineer who will verify the extract

Data Team Access to Data Sources

In order to integrate new data sources into the data warehouse, specific members of the Data team will need admin-level access to data sources, both in the UI and through the API. We need this admin-level access through the API in order to pull all the data needed to build the appropriately analyses and through the UI to compare the results of prepared analyses to the UI.

Sensitive data sources can be limited to no less than 1 data engineer and 1 data analyst having access to build the require reporting. In some cases, it may only be 2 data engineers. We will likely request an additional account for the automated extraction process.

Sensitive data is locked down through the security paradigms listed below; Sisense will never have access to sensitive data, as Sisense does not have access to any data by default. Sisense's access is always explicitly granted.

DataSiren

To ensure that the data team has a complete picture of where sensitive data is in the data warehouse, as well as make sure Sisense does not have access to sensitive data, a periodic scan of the data warehouse is made using dbt along with the internally-developed library of tools created as datasiren. This scan is currently executed weekly. The fine-grained results are stored in Snowflake in the PREP.DATASIREN schema and are not available in Periscope because of sensitivity reasons. High-level results have been made available in Periscope, including the simple dashboard found here.

Using SheetLoad

SheetLoad is the process by which a Google Sheets and CSVs from GCS or S3 can be ingested into the data warehouse.

Technical documentation on usage of SheetLoad can be found in the readme in the data team project.

If you want to import a Google Sheet or CSV into the warehouse, please make an issue in the data team project using the "CSV or GSheets Data Upload" issue template. This template has detailed instructions depending on the type of data you want to import and what you may want to do with it.

Considerations

SheetLoad should primarily be used for data whose canonical source is a spreadsheet - i.e. Sales quotas. If there is a source of this data that is not a spreadsheet, you should at least make an issue for a new data source to get the data pulled automatically. However, if the spreadsheet is the SSOT for this data, then SheetLoad is the appropriate mechanism for getting it into the warehouse.

Loading into Snowflake

SheetLoad is designed to make the table in the database an exact copy of the sheet from which it is loading. Whenever SheetLoad detects a change in the source sheet, it will drop the database table and recreate it in the image of the updated spreadsheet. This means that if columns are added, changed, etc. it will all be reflected in the database. Changes are detected within 24 hours.

Preparing for SheetLoad

Except for where absolutely not possible, it is best that the SheetLoad sheet import from the original Google Sheet directly using the importrange function. This allows you to leave the upstream sheet alone while enabling you to format the SheetLoad version to be plain text. Any additional data type conversions or data cleanup can happen in the base dbt models. (This does not apply to the Boneyard.)

Some additional considerations for preparing the data for loading:

• format all numeric columns to have no commas or other symbols - 1000.46 instead of $1,000.46
• use simple headers where possible - user_name instead of GitLab - User Name
• use blank cells to indicate no data. blank cells are stored as null in the database.

Modeling

Before regular SheetLoad data can be accessible by Sisense, it has to be modeled via dbt. A minimum of 2 models will be made for the sheet: a source model and a staging model. These will be made by a member of the data team once you've made an issue in the data team project.

Boneyard

The boneyard schema is where data can be uploaded from a spreadsheet and it will be directly available for querying within Sisense. However, this is for instances where a one-off analysis is needed and/or you need to join this to data already in the warehouse. It is called Boneyard to highlight that this data is relevant only for an ad hoc/one off use case and will become stale within a relatively short period of time. We will periodically remove stale data from the boneyard schema.

Certificates

If you are adding Certificates to SheetLoad, refer to the instructions in the People Group page

Qualtrics Data Push / Qualtrics SheetLoad

The Qualtrics data push process, also known in code as Qualtrics SheetLoad, enables emails to be uploaded to Qualtrics from the data warehouse without having to be downloaded onto a team member's machine first. This process shares its name with SheetLoad because it looks through Google Sheets for files with names starting with qualtrics_mailing_list. For each of the files it finds with an id column as the first column, it uploads that file to Snowflake. The resulting table is then joined with the GitLab user table to retrieve email addresses. The result is then uploaded to Qualtrics as a new mailing list.

During the process, the Google Sheet is updated to reflect the process' status. The first column's name is set to processing when the process begins, and then is set to processed when the mailing list and contacts have been uploaded to Qualtrics. Changing the column name informs the requester of the process' status, assists in debugging, and ensures that a mailing list is only created once for each spreadsheet.

The end user experience is described on the UX Qualtrics page.

Snowplow Infrastructure

Refer to the Snowplow Infrastructure page for more information on our setup.

Data Source Overviews

• Customer Success Dashboards
• Netsuite
  • Netsuite and Campaign Data
• Version (pings)
  • Note that up until October 2019, the data team referred to the entire version data source as "pings". However, usage ping is only one subset of the version data source which is why we now use "version" or "version app" to refer to the version.gitlab.com data source and "usage data" or "usage pings" or "pings" to refer to the specific usage data feature of the version data source.
• Salesforce
• Zendesk

Orchestration

We use Airflow on Kubernetes for our orchestration. Our specific setup/implementation can be found here. Also see the Data Infrastructure page for more information.

Data Warehouse

We currently use Snowflake as our data warehouse. The Enterprise Data Warehouse (EDW) is the single source of truth for GitLab's corporate data, performance analytics, and enterprise-wide data such as Key Performance Indicators. The EDW supports GitLab’s data-driven initiatives by providing all teams a common platform and framework for reporting, dashboarding, and analytics. With the exception of point-to-point application integrations all current and future data projects will be driven from the EDW. As a recipient of data from a variety of GitLab source systems, the EDW will also help inform and drive Data Quality best-practices, measures, and remediation to help ensure all decisions are made using the best data possible.

Warehouse Access

To gain access to the data warehouse:

• Create an issue in the access requests project documenting the level of access required.
• Do not request a shared account - each account must be tied to a user.
• We loosely follow the paradigm explained in this blog post around permissioning users.

Snowflake Permissions Paradigm

We use Permifrost to help manage permissions for Snowflake. Our configuration file for our Snowflake instance is stored in this roles.yml file. Also available is our handbook page on Permifrost.

We follow this general strategy for role management:

• Every user has an associated user role
• Functional roles exist to represent common privilege sets (analyst_finance, data_manager, product_manager)
• Logical groups of data have their own object roles
• Object roles are assigned primarily to functional roles
• Higher privilege roles (accountadmin, securityadmin, useradmin, sysadmin) are assigned directly to users
• Service accounts have an identically named role
• Additional roles can be assigned either to the service account role or the service account itself, depending on usage and needs
• Individual privileges can be granted at the granularity of the table & view
• Warehouse usage can be granted to any role as needed, but granting to functional roles is recommended

User Roles

Every user will have their own user role that should match their user name. Object level permissions (database, schemas, tables) in Snowflake can only be granted to roles. Roles can be granted to users or to other roles. We strive to have all privileges flow through the user role so that a user only has to use one role to interact with the database. Exceptions are privileged roles such as accountadmin, securityadmin, useradmin, and sysadmin. These roles grant higher access and should be intentionally selected when using.

Functional Roles

Functional roles represent a group of privileges and role grants that typically map to a job family. The major exception is the analyst roles. There are several variants of the analyst role which map to different areas of the organization. These include analyst_core, analyst_finance, analyst_people, and more. Analysts are assigned to relevant roles and are explicitly granted access to the schemas they need.

Functional roles can be created at any time. It makes the most sense when there are multiple people who have very similar job families and permissions.

Object Roles

Object roles are for managing access to a set of data. Typically these represent all of the data for a given source. The zuora object role is an example. This role grants access to the raw Zuora data coming from Stitch, and also to the source models in the prep.zuora schema. When a user needs access to Zuora data, granting the zuora role to that user's user role is the easiest solution. If for some reason access to the object role doesn't make sense, individual privileges can be granted at the granularity of a table.

Examples

This is an example role hierarchy for an Data Analyst, Core:

This is an example role hierarchy for an Data Engineer and Account Administrator:

This is an example role hierarchy for a Security Operations Engineer:

Compute Resources

Compute resources in Snowflake are known as "warehouses". To better track and monitor our credit consumption, we have created several warehouses depending on who is accessing the warehouse. The names of the warehouse are appended with their size (analyst_xs for extra small)

If you're running into query time limits consider using a larger warehouse.

Data Storage

We use three primary databases: raw, prep, and prod. The raw database is where data is first loaded into Snowflake; the other databases are for data that is ready for analysis (or getting there).

There is a snowflake database, which contains information about the entire GitLab instance. This includes all tables, views, queries, users, etc.

There is a covid19 database, which is a shared database managed through the Snowflake Data Exchange.

There is a testing_db database, which is used for testing Permifrost.

All databases not defined in our roles.yml Permifrost file are removed on a weekly basis.

Raw

This database is not available to query in Sisense. No dbt models exist for this data.

• Raw may contain sensitive data, so permissions need to be carefully controlled
• Data is stored in different schemas based on the source
• User access can be controlled by schema and tables

Prep

This database is not available to query in Sisense.

• Source models are built in logical schemas corresponding to the data source (i.e. sfdc, zuora)
• PREPARATION - this is the default schema where dbt models are built
• SENSITIVE

Prod

This database and all schemas and tables in it are queryable by Sisense.

With the exception of public, and boneyard, all schemas are controlled by dbt. See the dbt guide for more information.

Folder Structure in Analytics Project

The table below shows a mapping of how models stored within folders in the models/ directory in the analytics project will be materialized in the data warehouse.

The source of truth for this is in the dbt_project.yml configuration file.

Static

For data warehouse use cases that require us to store data for our users without updating it automatically with dbt we use the STATIC database. This also allows for analysts and other users to create their own data resources (tables, views, temporary tables). There is a sensitive schema for sensitive data within the static database. If your use case for static requires the use or storage of sensitive data please create an issue for the data engineers.

Timezones

All timestamp data in the warehouse should be stored in UTC. The default timezone for a Snowflake sessions is PT, but we have overridden this so that UTC is the default. This means that when current_timestamp() is queried, the result is returned in UTC.

Stitch explicitly converts timestamps to UTC. Fivetran does this as well (confirmed via support chat).

The only exception to this rule is the use of pacific time to create date_id in fact tables, which should always be created by the get_date_pt_id dbt macro and labeled with the _pt_id suffix.

Snapshots

We use the term snapshots in multiple places throughout the data team handbook and the term can be confusing depending on the context. Snapshots as defined by the dictionary is "a record of the contents of a storage location or data file at a given time". We strive to use this definition whenever we use the word.

dbt

The most common usage is in reference to dbt snapshots. When dbt snapshots is run, it takes the state of the data based on a query specified by the user and updates a table that contains the full history of the state of the data. It has valid_to and valid_from fields indicating the time period for which that particular snapshot is valid. See the dbt snapshots section in our dbt guide for more technical information.

The tables generated and maintained by dbt snapshots are the raw historical snapshot tables. We will build downstream models on top of these raw historical snapshots for further querying. The snapshots folder is where we store the dbt models. One common model we may build is one that generate a single entry (i.e. a single snapshot) for a given day; this is useful when there are multiple snapshots taken in a 24 hour period. We also will build models to return the most current snapshot from the raw historical table.

Other uses

Our Greenhouse data can be thought of as a snapshot. We get a daily database dump provided by Greenhouse that we load into Snowflake. If we start taking dbt snapshots of these tables then we would be creating historical snapshots of the Greenhouse data.

The extracts we do for some yaml files and for BambooHR can also be thought of as snapshots. This extraction works by taking the full file/table and storing it in its own, timestamped row in the warehouse. This means we have historical snapshots for these files/tables but these are not the same kind of snapshot as dbt. We'd have to do additional transformations to get the same valid_to and valid_from behavior.

Language

• Snapshot - The state of data at a specific point in time
• Take a snapshot - Run the job that takes the state of the data currently and stores it. Can be used in the dbt context. Not recommended to reference our yaml extract jobs - these would be "run the extract".
• Historical snapshots - A table that contains data for a given source table at multiple points in time. Most commonly used to reference dbt-generated snapshot tables. Can also be used to reference the yaml extract tables.
• Latest snapshot - The most current state of the data we have stored. For dbt snapshots these are the records that have null for the valid_to. For BambooHR and yaml extracts these correspond to the last time the extraction job was run. For Greenhouse raw, this represents the data as it is in the warehouse. Were we to start taking snapshots of the Greenhouse data the speaker would have to clarify if they mean the raw table or the latest record in the historical snapshots table.

Backups

For an extra layer of robustness, we backup data from the warehouse into GCS (Google Cloud Storage). We execute the jobs using dbt's run-operation capabilities. Currently, we backup all of our snapshots daily and retain them for a period of 1 month. We implemented the basic instructions outlined in this Calogica blog post.

Transformation

We use dbt for all of our transformations. See our dbt guide for more details on why and how we use this tool.

Trusted Data Framework

Data Customers expect Data Teams to provide data they can trust to make their important decisions. And Data Teams need to be confident in the quality of data they deliver. But this is a hard problem to solve: the Enterprise Data Platform is complex and involves multiple stages of data processing and transformation, with tens to hundreds of developers and end-users actively changing and querying data 24 hours a day. The Trusted Data Framework (TDF) supports these quality and trust needs by defining a standard framework for data testing and monitoring across data processing stages, accessible by technical teams and business teams. Implemented as a stand-alone module separate from existing data processing technology, the TDF fulfills the need for an independent data monitoring solution.

• Enable everyone to contribute to trusted data, not just analysts and engineers
• Enable data validations from top to bottom and across all stages of data processing
• Validate data from source system data pipelines
• Validate data transforms into dimensional models
• Validate critical company data
• Deployable independently from central data processing technology

Key Terms

• Assertion or Test Case - An individual test and the smallest unit of a test that can be performed. In TDF the test case is expressed either as a SQL statement or via a YAML configuration within SQL-compilation tool, dbt.
• Data Schema - The tables, columns, views, and other structural elements that make up a data subject area, create using SQL Data Definition Language (DDL).
• Golden Data - Golden data is a data constant from a single field or a group of fields important to the business.
• Monitoring - Tracking the results of tests cases to help ensure data is ready for use.

Trusted Data Components

The primary elements of the TDF include:

1. A Virtuous Test Cycle that embeds quality as a normal part of daily data development, ranging from new data solutions to break-fix issue resolution.
2. Test Cases Expressed As SQL and YAML which can be developed by anyone.
3. The Trusted Data Schema saves test results for monitoring and alerting, and long-term analysis towards the path of developing wisdom around business processes and data platform performance.
4. Schema-to-Golden Record Coverage to provide broad coverage of the data warehouse domain, ranging from schema to critical "Golden" data.
5. The Trusted Data Dashboard, a business-friendly dashboard to visualize overall test coverage, successes, and failures.
6. The Test Run is when a Test Cases are executed.

Virtuous Test Cycle

The TDF embraces business users as the most important participant in establishing trusted data and uses a simple and accessible testing model. With SQL and YAML as a test agent, a broad group of people can contribute test cases. The test format is straightforward with simple PASS/FAIL results and just four test case types. Adoption grows quickly as TDF demonstrates value:

• Data Customers and Business Users learn the testing framework and create tests themselves
• Teams embrace testing as a valuable activity to include at all times, not as a last-minute activity
• The Data Team learns to add new tests as part of production-down retrospectives to more rapidly identify issues before they become large problems
• Teams develop operational rythms to continually develop new tests and expand test coverage

Over time, it is not uncommon to develop hundreds of tests cases which are run on a daily basis, continually validating data quality.

Test Cases Expressed As SQL and YAML

SQL is the universal language in databases and nearly everyone who works with data has some level of SQL competency. However, not everyone may be familiar with SQL and we don't want that to limit who can contribute. We use dbt to support the TDF which enables the defining of tests via SQL and YAML.

Trusted Data Schema

With all tests being run via dbt, storing tests results is simple. We store the results of every test run in the data warehouse. Storing test results enables a variety of valuable features, including:

• data visualization and pattern analysis test results (total tests run by date, PASS/FAIL rate by subject area, and so on)
• measurement of test coverage over a data subject or schema (number of tests by area)
• measurement of system quality improvements over time (an increase in the PASS rate)
• development of an alerting system based on test result

These test results are parsed and are available for querying in Sisense.

The schema we store all test results is: WORKSPACE_DATA.
Note: This schema only containts views.

Schema To Golden Record Coverage

The Data Warehouse environment can change quickly and the TDF supports predictability, stability, and quality with test coverage of the areas in the Data Warehouse that are most likely to change:

1. Schema tests to validate the integrity of a schema
2. Column Value tests to determine if the data value in a column matches pre-defined thresholds or literals
3. Rowcount tests to determine if the number of rows in a table over a pre-defined period of time match pre-defined thresholds or literals
4. Golden Data tests to determine if pre-defined high-value data exists in a table

The implementation details of these tests are documented in our dbt guide.

Trusted Data Dashboard

The Trusted Data Dashboard in Sisense can be found here

Test Run

More to come.

Data Pump

In order to make it easy for anyone to send data from Snowflake to other applications in the GitLab tech stack we have partnered with the Enterprise Applications Integration Engineering team to create this data integration framework, which we are calling Data Pump. The Data Pump Airflow DAG, which runs the pump, is is set to run once daily at 05:00 UTC.

Adding a Data Pump

Step 1: Create a data model using dbt in /marts/pumps (or /marts/pumps_sensitive if the model contains RED or ORANGE Data), following our SQL and dbt style and documentation standards. Create an MR using dbt model changes template. Once this is merged and appears in Snowflake in PROD.PUMPS or PROD.PUMPS_SENSITIVE you are ready for steps two and three.

Step 2: Add Model to pumps.yml using the 'Pump Changes' MR template with the following attributes:

• model - the name of the model in dbt and snowflake
• timestamp_column - the name of the column that should be used to batch the data (or null if there is none and the table is small)
• sensitive - True if this model contains sensitive data and is in the pumps_sensitive directory and schema
• owner - your (or the business DRI's) gitlab handle

Step 3: Create an integration issue in the integrations project using the 'New Data Pump' issue template so that the Integration team can map and integrate the data into the target application.

Visualization

We use Sisense as our Data Visualization and Business Intelligence tool. To request access, please follow submit an access request.

Meta Analyses for the Data Team

• Sisense Usage! 📈
• Sisense Account Optimization 💪
• Sisense Account Maintenance 🗑️
• dbt Event Logging
• Snowflake Spend ️❄

Security

Passwords

Per GitLab's password policy, we rotate service accounts that authenticate only via passwords every 90 days. A record of systems changed and where those passwords were updated is kept in this Google Sheet.

We also rotate Snowflake user passwords the first Sunday of every 3rd month of the year (January, April, July, October) via the Snowflake Password Reset DAG.

Software User Provisioning

The data team is responsible for provisioning users within the tools managed by the Data Team. This includes tools like Fivetran, Stitch, and Snowflake.

For Snowflake, we have a robust process documented in the Snowflake Permissions Paradigm section of this page.

For other tools, add users via the UI and in the appropriate Google Group if one exists.