Primary Project dbt docs Sisense Epics OKRs GitLab Unfiltered YouTube Playlist
🌎📚💻🏁🛠🔍👑📊
Many of the sections on this page will have emojis just below the heading. For more information about what they convey read the documentation page.
The Data Team uses these channels on Slack:
The Data Team's Google Calendar is the SSOT for meetings. It also includes relevant events in the data space. Anyone can add events to it. Many of the events on this calendar, including Monthly Key Reviews, do not require attendance and are FYI events. When creating an event for the entire Data Team, it might be helpful to consult the Time Blackout sheet.
The Data Team has the following recurring meetings:
The team honors Meeting Tuesday. We aim to consolidate all of our meetings into Tuesday, since most team members identify more strongly with the Maker's Schedule over the Manager's Schedule.
We Data
The Data Team is a part of the Finance organization within GitLab, but we serve the entire company. We do this by maintaining a data warehouse where information from all business systems are stored and managed for analysis.
Our charter and goals are as follows:
The Data Team at GitLab is working to establish a world-class data analytics and engineering function by utilizing the tools of DevOps in combination with the core values of GitLab. We believe that data teams have much to learn from DevOps. We will work to model good software development best practices and integrate them into our data management and analytics.
A typical data team has members who fall along a spectrum of skills and focus. For now, the data function at GitLab has Data Engineers and Data Analysts; eventually, the team will include Data Scientists. Review the team organization section section to see the make up of the team.
Data Engineers are essentially software engineers who have a particular focus on data movement and orchestration. The transition to DevOps is typically easier for them because much of their work is done using the command line and scripting languages such as bash and python. One challenge in particular are data pipelines. Most pipelines are not well tested, data movement is not typically idempotent, and auditability of history is challenging.
Data Analysts are further from DevOps practices than Data Engineers. Most analysts use SQL for their analytics and queries, with Python or R. In the past, data queries and transformations may have been done by custom tooling or software written by other companies. These tools and approaches share similar traits in that they're likely not version controlled, there are probably few tests around them, and they are difficult to maintain at scale.
Data Scientists are probably furthest from integrating DevOps practices into their work. Much of their work is done in tools like Jupyter Notebooks or R Studio. Those who do machine learning create models that are not typically version controlled. Data management and accessibility is also a concern.
We will work closely with the data and analytics communities to find solutions to these challenges. Some of the solutions may be cultural in nature, and we aim to be a model for other organizations of how a world-class Data and Analytics team can utilize the best of DevOps for all Data Operations.
Some of our beliefs are:
The Data Analyst Team operates in a hub and spoke model, where some analysts are part of the central data team (hub) while others are embedded (spoke) or distributed (spoke) throughout the organization.
Central - those in this role report to and have their priorities set by the Data team. They currently support those in the Distributed role, cover ad-hoc requests, and support all functional groups (business units).
Embedded - those in this role report to the data team but their priorities are set by their functional groups (business units).
Distributed - those in this role report to and have their priorities set by their functional groups (business units). However, they work closely with those in the Central role to align on data initiatives and for assistance on the technology stack.
All roles mentioned above have their MRs and dashboards reviews by members in the Data team. Both Embedded and Distributed data analyst or data engineer tend to be subject matter experts (SME) for a particular business unit.
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Manager, Data | @kathleentam | Central | @wzabaglio |
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Junior Data Analyst, Engineering | @ken_aguilar | Embedded | @kathleentam |
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Data Analyst, Finance | @iweeks | Embedded | @wwright |
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Marketing Operations Manager | @rkohnke | Distributed | @rkohnke |
| Junior Data Analyst, Marketing | @jeanpeguero | Embedded | @rkohnke |
Board: Marketing Board
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Sr. Data Analyst, Growth | @mpeychet | Embedded | Primary (DRI): @sfwgitlab; Secondary: @timhey, @jstava, @s_awezec, @mkarampalas |
| Data Analyst, Growth | @eli_kastelein | Embedded | Primary (DRI): @sfwgitlab; Secondary: @timhey, @jstava, @s_awezec, @mkarampalas |
Board: Growth Board
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Senior Sales Analytics Analyst | @JMahdi | Distributed | @mbenza |
| Senior Sales Analytics Analyst | @mvilain | Distributed | @mbenza |
| Senior Sales Analytics Analyst | @DavidMack | Distributed | @mbenza |
| Data Analyst, Sales | @derekatwood | Embedded | @mbenza |
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Senior Data Analyst, People Team | @Pluthra | Embedded | Interim @kathleentam |
Board: People Board
| Role | Team Member | Type | Prioritization Owners |
|---|---|---|---|
| Data Analyst, Product & Product Strategy | Click Here to Apply! | Embedded | TBD, Interim @kathleentam |
Though Data Engineers are sometimes given assignments to better support business functions no members of the the data engineering team are embedded or distributed. This allows them to focus on our data platform with an appropriate development cadence.
| Role | Team Member | Prioritization Owners |
|---|---|---|
| Manager, Data | @jjstark | @wzabaglio |
| Staff Data Engineer | @tayloramurphy | @jjstark |
| Senior Data Engineer | @m_walker | @jjstark |
| Data Engineer | to be hired | @jjstark |
Analysis usually begins with a question. A stakeholder will ask a question of the data team by creating an issue in the Data Team project using the appropriate template. The analyst assigned to the project may schedule a discussion with the stakeholder(s) to further understand the needs of the analysis, though the preference is always for async communication. This meeting will allow for analysts to understand the overall goals of the analysis, not just the singular question being asked, and should be recorded. All findings should be documented in the issue. Analysts looking for some place to start the discussion can start by asking:
An analyst will then update the issue to reflect their understanding of the project at hand. This may mean turning an existing issue into a meta issue or an epic. Stakeholders are encouraged to engage on the appropriate issues. The issue then becomes the SSOT for the status of the project, indicating the milestone to which it has been assigned and the analyst working on it, among other things. The issue should always contain information on the project's status, including any blockers that can help explain its prioritization. Barring any confidentiality concerns, the issue is also where the final project will be delivered, after peer/technical review. When satisfied, the analyst will close the issue. If the stakeholder would like to request a change after the issue has been closed, s/he should create a new issue and link to the closed issue.
The Data Team can be found in the #data channel on slack.
The data team's priorities come from our OKRs. We do our best to service as many of the requests from the organization as possible. You know that work has started on a request when it has been assigned to a milestone. Please communicate in the issue about any pressing priorities or timelines that may affect the data team's prioritization decisions. Please do not DM a member of the data team asking for an update on your request. Please keep the communication in the issue.
The data team, like the rest of GitLab, works hard to document as much as possible. We believe this framework for types of documentation from Divio is quite valuable. For the most part, what's captured in the handbook are tutorials, how-to guides, and explanations, while reference documentation lives within in the primary analytics project. We have aspirations to tag our documentation with the appropriate function as well as clearly articulate the assumed audiences for each piece of documentation.
Data Team OKRs are derived from the higher level BizOps/Finance OKRs as well as the needs of the team. At the beginning of a FQ, the team will outline all actions that are required to succeed with our KRs and in helping other teams measure the success of their KRs. The best way to do that is via a team brain dump session in which everyone lays out all the steps they anticipate for each of the relevant actions. This is a great time for the team to raise any blockers or concerns they foresee. These should be recorded for future reference.
These OKRs drive ~60% of the work that the central data team does in a given quarter. The remaining time is divided between urgent issues that come up and ad hoc/exploratory analyses. Specialty data analysts (who have the title "Data Analyst, Specialty") should have a similar break down of planned work to responsive work, but their priorities are set by their specialty manager.
The data team currently works in two-week intervals, called milestones. Milestones start on Tuesdays and end on Mondays. This discourages last-minute merging on Fridays and allows the team to have milestone planning meetings at the top of the milestone.
Milestones may be three weeks long if they cover a major holiday or if the majority of the team is on vacation or at Contribute. As work is assigned to a person and a milestone, it gets a weight assigned to it.
Milestone planning should take into consideration:
The milestone planning is owned by the Manager, Data.
The timeline for milestone planning is as follows:
| Day | Current Milestone | Next Milestone |
|---|---|---|
| 0 - 1st Tuesday | Milestone Start Roll Milestone |
- |
| 6 - 1st Monday | - | Groom new issues for planning |
| 7 - 2nd Tuesday | Midpoint Any issues that are at risk of slipping from the milestone must be raised by the assignee |
Milestone Review and Planning Meeting Discuss: What we learned from the last milestone. Priorities for new milestone. What's coming down the pike. Issues are pointed by relevant team (Engineering or Analytics) Note: Pointing is done without knowledge of who may pick up the task. |
| 10 - 2nd Friday | The last day to submit MRs for review MRs must include documentation and testing to be ready to merge No MRs are to be merged on Fridays |
Milestone is roughly final Milestone Planner distributes issues to team members, with the appropriate considerations and preferences |
| 13 - 2nd Monday | Last day of Milestone Ready MRs can be merged |
The short-term goal of this process is to improve our ability to plan and estimate work through better understanding of our velocity. In order to successfully evaluate how we're performing against the plan, any issues not raised at the T+7 mark should not be moved until the next milestone begins.
The work of the data team generally falls into the following categories:
During the milestone planning process, we point issues. Then we pull into the milestone the issues expected to be completed in the timeframe. Points are a good measure of consistency, as milestone over milestone should share an average. Then issues are prioritized according to these categories.
Issues are not assigned to individual members of the team, except where necessary, until someone is ready to work on it. Work is not assigned and then managed into a milestone. Every person works on the top priority issue for their job type. As that issue is completed, they can pick up the next highest priority issue. People will likely be working on no more than 2 issues at a time.
Given the power of the Ivy Lee method, this allows the team to collectively work on priorities as opposed to creating a backlog for any given person. As a tradeoff, this also means that every time a central analyst is introduced to a new data source their velocity may temporarily decrease as they come up to speed; the overall benefit to the organization that any analyst can pick up any issue will compensate for this, though. Learn how the product managers groom issues.
Data Engineers will work on Infrastructure issues.
Data Analysts, Central and sometimes Data Engineers work on general Analytics issues.
Data Analysts,
There is a demo of what this proposal would look like in a board.
This approach has many benefits, including:
There are three general types of issues:
Not all issues will fall into one of these buckets but 85% should.
Some issues may need a discovery period to understand requirements, gather feedback, or explore the work that needs to be done. Discovery issues are usually 2 points.
Introducing a new data source requires a heavy lift of understanding that new data source, mapping field names to logic, documenting those, and understanding what issues are being delivered. Usually introducing a new data source is coupled with replicating an existing dashboard from the other data source. This helps verify that numbers are accurate and the original data source and the data team's analysis are using the same definitions.
This umbrella term helps capture:
It is the responsibility of the assignee to be clear on what the scope of their issue is. A well-defined issue has a clearly outlined problem statement. Complex or new issues may also include an outline (not all encompassing list) of what steps need to be taken. If an issue is not well-scoped as its assigned, it is the responsibility of the assignee to understand how to scope that issue properly and approach the appropriate team members for guidance early in the milestone.
Issue pointing captures the complexity of an issue, not the time it takes to complete an issue. That is why pointing is independent of who the issue assignee is.
| Weight | Description |
|---|---|
| Null | Meta and Discussions that don't result in an MR |
| 0 | Should not be used. |
| 1 | The simplest possible change including documentation changes. We are confident there will be no side effects. |
| 2 | A simple change (minimal code changes), where we understand all of the requirements. |
| 3 | A typical change, with understood requirements but some complicating factors |
| 5 | A more complex change. Requirements are probably understood or there might be dependencies outside the data-team. |
| 8 | A complex change, that will involve much of the codebase or will require lots of input from others to determine the requirements. |
| 13 | A significant change that has dependencies and we likely still don't understand all of the requirements. It's unlikely we would commit to this in a milestone, and the preference would be to further clarify requirements and/or break into smaller Issues. |
Think of each of these groups of labels as ways of bucketing the work done. All issues should get the following classes of labels assigned to them:
Optional labels that are useful to communicate state or other priority
Members of the data team use Geekbot for our daily standups.
These are posted in #data-daily.
When Geekbot asks, "What are you planning on working on today? Any blockers?" try answering with specific details, so that teammates can proactively unblock you.
Instead of "working on Salesforce stuff", consider "Adding Opportunity Owners for the sfdc_opportunity_xf model`."
There is no pressure to respond to Geekbot as soon as it messages you.
Give responses to Geekbot that truly communicate to your team what you're working on that day, so that your team can help you understand if some priority has shifted or there is additional context you may need.
Ideally, your workflow should be as follows:
WIP: label.
cc @user in a comment.WIP: label, mark the branch for deletion, mark squash commits, and assign to the project's maintainer. Ensure that the attached issue is appropriately labeled and pointed.Other tips:
To facilitate an easier workflow for analysts and to abstract away some of the complexity around handling dbt and its dependencies locally, the main analytics repo now supports using dbt from within a Docker container.
There are commands within the Makefile to facilitate this, and if at any time you have questions about the various make commands and what they do, just use make help to get a handy list of the commands and what each of them does.
Before your initial run (and whenever the containers get updated) make sure to run the following commands:
make update-containersmake cleanupThese commands will ensure you get the newest versions of the containers and generally clean up your local Docker environment.
dbt:DBT_PROFILE_PATH environment variable set. This should be set if you've used the onboarding_script.sh, but if not, you can set it in your .bashrc or .zshrc by adding export DBT_PROFILE_PATH="/<your_root_dir/.dbt/" to the file or simply running the same command in your local terminal sessiondbt container and run commands from a shell inside of it, use make dbt-imagedbt needs to run, including your local profiles.yml and repo files
GIT_BRANCH, KUBECONFIG, GOOGLE_APPLICATION_CREDENTIALS, etc.). Unless you are developing on Airflow this is ok and expected.make dbt-docs and then visit localhost:8081 in a web-browser. Note that this requires the docs profile to be configured in your profiles.ymldbt container, run any dbt commands as you normally wouldWe encourage everyone to record videos and post to GitLab Unfiltered. The handbook page on YouTube does an excellent job of telling why we should be doing this. If you're uploading a video for the data team, be sure to do the following extra steps:
data as a video tagWe 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.
| Stage | Tool |
|---|---|
| Extraction | Stitch, Fivetran, and Custom |
| Loading | Stitch, Fivetran, and Custom |
| Orchestration | Airflow |
| Storage | Snowflake |
| Transformations | dbt and Python scripts |
| Analysis | Sisense For Cloud Data Teams |
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.
| Data Source | Pipeline | Replication Frequency | Quality Rating |
|---|---|---|---|
| BambooHR | Airflow | 12 hour intervals for all time | 1 |
| CloudSQL Postgres | Stitch | 2 | |
| Customer DB | Postgres_Pipeline | 2 | |
| Gitter | not updated | ||
| GitLab.com | Postgres_Pipeline | 6 hour intervals | 2 |
| Greenhouse | Airflow (custom script) | Once per day | 1 |
| License DB | Postgres_Pipeline | 6 hour intervals | 2 |
| Marketo | not updated | ||
| Netsuite | Fivetran | 6 hour intervals - Backfilled from January 1, 2013 | 2 |
| Salesforce (SFDC) | Stitch | 1 hour intervals - Backfilled from January 1, 2013 | 2 |
| SheetLoad | SheetLoad | 24 hours | 1 |
| Snowplow | Snowpipe | Continuously loaded | 2 |
| Version DB | Postgres_Pipeline | 6 hour intervals | 2 |
| Zendesk | Stitch | 1 hour intervals - Backfilled from January 1, 2013 | 2 |
| Zuora | Stitch | 30 minute intervals - Backfilled from January 1, 2013 | 2 |
This is the lag between real-time and the analysis displayed in the data visualization tool.
| Data Source | SLO |
|---|---|
| BambooHR | 1 day |
| Clearbit | None |
| Airflow DB | 9 hours |
| CI Stats DB | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| Customer DB | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| DiscoverOrg | None |
| GitLab.com | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| GitLab Profiler DB | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| Greenhouse | 2 days |
| License DB | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| Marketo | None |
| Netsuite | 1 day |
| Salesforce (SFDC) | 1 day |
| SheetLoad | 2 days |
| Snowplow | 1 day |
| Version DB | None - Owned by GitLab.com Infrastructure Team, intermittently unavailable |
| Zendesk | 1 day |
| Zuora | 1 day |
Process for adding a new data source:
gitlab-com/gl-securityTo add new fields to the BambooHR extract:
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.
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(https://gitlab.com/gitlab-data/analytics/tree/master/extract/sheetload).
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.
We strongly encourage you to consider the source of the data when you want to move it into a spreadsheet. 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 to get the data pulled automatically. However, if the spreadsheet is the SSOT for this data, then we can get it into the warehouse and modeled appropriately via dbt.
We do understand, though, that there are instances where a one-off analysis is needed based on some data in a spreadsheet and that you might need to join this to some other data already in the warehouse. We offer a "Boneyard" schema where you can upload the spreadsheet and it will be available for querying within Sisense. We call it 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.
SheetLoad is designed to make the table in the database a mirror image of what is in the sheet from which it is loading. Whenever SheetLoad detects a change in the source sheet it will forcefully 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.
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 and 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.)
Refer to the Snowplow Infrastructure page for more information on our setup.
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.
We currently use Snowflake as our data warehouse.
To gain access to the data warehouse:
Goal: Mitigate risk of people having access to sensitive data.
We currently use Meltano's Permission Bot in dry mode to help manage our user, roles, and permissions for Snowflake. Documentation on the permission bot is in the Meltano docs. Our configuration file for our Snowflake instance is stored in this roles.yml file.
There are four things that we need to manage:
There are currently four primary analyst roles in Snowflake:
Analysts are assigned to relevant roles and are explicitly granted access to the schemas they need.
Two notes of the permission bot:
Common errors that are encountered:
We currently use two databases- raw and analytics. The former is for EL'ed data; the latter is for data that is ready for analysis (or getting there).
All database clones are wiped away every week.
analytics, analytics_staging, and analytics_sensitive.
analytics schema.jdoe_scratch_analytics and jdoe_scratch_staging) are also in the analytics database. The dev schemas could have sensitive information, as they iterate through it. These are not accessible within the GitLab Sisense workspace.analytics_sensitive schema, it must also be added to any relevant roles in the roles.yml file detailed above.Managing Roles for Snowflake
Here are the proper steps for provisioning a new user and user role:
securityadmin roleCreate... button
JSMITH - This is the GitLab default of first letter of first name and full last name.JSMITH) with SYSADMIN as the parent role (this is the default and it grants role to sysadmin)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_s for small)
admin - This is for permission bot and other admin tasksairflow_testing_l - For testing Airflow locallyanalyst_* - These are for Data Analysts to use when querying the database or modeling dataengineer_* - These are for Data Engineers and the Manager to use when querying the database or modeling datafivetran_warehouse - This is exclusively for Fivetran to usegitlab_postgres - This is for extraction jobs that pull from GitLab internal Postgres databasesloading - This is for our Extract and Load jobsmerge_request_* - These are scoped to GitLab CI for dbt jobs within a merge requestreporting - This is for the BI tool for queryingstitch - This is exclusively for Stitch to usetarget_snowflake - This is for the Meltano team to test their Snowflake loadertransforming_* - These are for production dbt jobsAll 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).
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.
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 below 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.
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.
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.For an extra layer of robustness, we backup data from the warehouse into GCS (Google Cloud Storage). We execute the jobs using a 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.
Please see the data analyst onboarding issue template for details on getting started with dbt.
At times, we rely on dbt packages for some data transformation. Package management is built-in to dbt. A full list of packages available are on the dbt Hub site.
_xf dbt model should be a BEAM* table, which means it follows the business event analysis & model structure and answers the who, what, where, when, how many, why, and how question combinations that measure the business.accounts instead of accts.run or test. Specifying models can save you a lot of time by only running/testing the models that you think are relevant. However, there is a risk that you'll forget to specify an important upstream dependency so it's a good idea to understand the syntax thoroughly.dbt seed - a command that loads data from csv files into our data warehouse. Because these csv files are located in our dbt repository, they are version controlled and code reviewable. Thus, dbt seed is appropriate for loading static data which changes infrequently. A csv file that’s up to ~1k lines long and less than a few kilobytes is probably a good candidate for use with the dbt seed command.source table - (can also be called raw table) table coming directly from data source as configured by the manifest. It is stored directly in a schema that indicates its original data source, e.g. sfdcbase models- the only dbt models that reference the source table; base models have minimal transformational logic (usually limited to filtering out rows with data integrity issues or actively flagged not for analysis and renaming columns for easier analysis); can be found in the analytics_staging schema; is used in ref statements by end-user modelsend-user models - dbt models used for analysis. The final version of a model will likely be indicated with an _xf suffix when it’s goal is to be a BEAM* table. It should follow the business event analysis & model structure and answer the who, what, where, when, how many, why, and how question combinations that measure the business. End user models are found in the analytics schema.materalized='ephemeral' is one option which essentially treats the model like a CTE. However, if you need to materialize it as a table for performance reasons, consider setting the schema config to temporary. This will build the table to a temporary schema which is then dropped at the end of the run. This way you get the performance benefits of a table but it won't be available for querying by end users. Note that you cannot use temporary if there are view models downstream. Also, you will not be able to test the model because it won't exist.Schema References (aka What goes where)
| Purpose | Production | Dev | Config |
|---|---|---|---|
| For querying & analysis | analytics | emilie_scratch_analytics | None |
| For querying & analysis staging | analytics_staging | emilie_scratch_staging | {{ config({ "schema": "staging"}) }} |
| For querying & analysis but SENSITIVE | analytics_sensitive | emilie_scratch_analytics_sensitive | {{ config({ "schema": "sensitive"}) }} |
| Intermediate tables that are dropped | analytics_temporary | emilie_scratch_temporary | {{ config({ "schema": "temporary"}) }} |
dbt snapshotSnapshots are a way to take point-in-time copies of source tables. dbt has excellent documentation on how the snapshots work. Snapshots are stored in the snapshots folder of our dbt project. We have organized the different snapshots by data source for easy discovery.
The following is an example of how we implement a snapshot:
{% snapshot sfdc_opportunity_snapshots %}
{{
config(
target_database='RAW',
target_schema='snapshots',
unique_key='id',
strategy='timestamp',
updated_at='systemmodstamp',
)
}}
SELECT *
FROM {{ source('salesforce', 'opportunity') }}
{% endsnapshot %}
Key items to note:
RAW database is hard-coded. We aim to set this as an environment variable pending some upstream bugs gitlab-data/analytics/2299snapshots schema is hard-coded and should not change{source_name}_{source_table_name}_snapshots for your file nameupdated_at field, always prefer using timestamp as a strategy (over check). Please find documentation about strategy hereSnapshots are tested manually by a maintainer of the Data Team project before merging.
As stated above, RAW database and snapshots schemas are hard-coded in the config dictionary of dbt snapshots. That means, once these snapshot tables are created in the RAW we need to make them available in the ANALYTICS data warehouse in order to be able to query them downstream (with Sisense or for further _xf dbt models).
Base models for snapshots are available in the folder /models/snapshots of our dbt project. Key items to note:
{source_name}_{source_table_name}_snapshots as our naming convention. But dbt doesn't like when it finds duplicated file names in its projects. In order to avoid this scenario (the snapshot and the snapshot base model having the same name), we found this clean and simple workaround:
_base. Ex: we have a gitlab_dotcom_members_snapshots snapshot file here and a base model of this snapshot here named gitlab_dotcom_members_snapshots_base._base suffix and keep the table name cleandbt_scd_id as a primary key to your snapshot base model and rename it to something more explicit (documentation about snapshot meta-fields can be found here)dbt_valid_from and dbt_valid_to to your query~/.dbt/ folder there should be a profiles.ymlfile that looks like this sample profileSNOWFLAKE_TRANSFORM_WAREHOUSE as the variable name to identify the warehouse. The environment variable can be set in the .bashrc or .zshrc file as follows:
export SNOWFLAKE_TRANSFORM_WAREHOUSE="ANALYST_XS"--vars '{warehouse_name: analyst_xl}' to the dbt commandIf you're interested in contributing to dbt, here's our recommended way of setting up your local environment to make it easy.
Create a virtual environment (venv) for dbt following these commands
cd ~
mkdir .venv # This should be in your root "~" directory
python -m venv .venv/dbt
source ~/.venv/dbt/bin/activate
pip install dbt
alias dbt!="source ~/.venv/dbt/bin/activate" to your .bashrc or .zshrc to make it easy to start the virtual environment(dbt) at the start of the command promptpip install -r editable_requirements.txt. This will ensure when you run dbt locally in your venv you're using the code on your machine.which dbt to ensure it's pointing to the venvWhen you're ready to submit your code for a PR, ensure you've signed their CLA.
We use Sisense as our Data Visualization and Business Intelligence tool. To request access, please follow submit an access request.
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.
The data team has a triager who is responsible for:
dbt run and dbt test failuresWorkflow::Triage labelHaving a dedicated triager on the team helps address the bystander affect. This is not an on-call position. This role just names a clear owner. Through clear ownership, we create room for everyone else on the team to spend most of the day around deep work. The triager is encouraged to plan their day for the kind of work that can be accomplished successfully with this additional demand on time.
The triager is not expected to know the answer to all the questions. They should pull in other team members who have more knowledge in the subject matter area to pass on the conversation after understanding the issue. Document any issues you stumble upon and learn about to help disseminate knowledge amongst all team members.
dbt run and dbt test failures are triaged.
dbt run and dbt test produce failures.
Many of the these failures are due to upstream data quality problems that need to be addressed.
dbt run failures indicate the model did not run and maintained its previous state and data composition. These failures are not common and it is not feasible to maintain a list of common dbt run failures. Please follow the general checklist located at the top of the below referenced README file to debug dbt run errors.dbt test failures indicate the model ran, but there are some records in the model that have data quality issues and require research and follow-up. Please follow the checklists in the below referenced README file to debug dbt test failures. Some tests have a severity setting of warn and not fail. If warn is configured, then dbt will log a warning for any failing tests, but the job will still be pass (assuming no failures). This configuration is useful for tests in which a failure does not imply that action is required. Warnings do not need to be triaged.dbt test failures are documented in the dbt tests README.
If you stumble upon one that isn't documented, be sure to document it to make it easier to address in the future.
The end result is not necessarily the resolution of the failing model in dbt run and dbt test;
however, the model should be taken to a running state and the problems documented in an issue.
This is important because:
triage label so that the next person on triage duty can easily find if the failure is new or not, and avoid issue duplication. Any created issues should also be posted to the dbt-runs slack channel by the triaging team member for quick review by the following triager.Triage schedule The data team has implemented a triagle schedule that takes advantage of folks native timezones. The Data Team's Google Calendar is the source of truth for the triage schedule. A team member who is off, on vacation, or working on a high priority project is responsible for finding coverage and communicating to the team who is taking over their coverage; this should be updated on the Data Team's Google Calendar.
| UTC day | Team member |
|---|---|
| Monday | @derekatwood / @jeanpeguero |
| Tuesday | @jjstark |
| Wednesday | @mpeychet / @kathleentam |
| Thursday | @iweeks / @kathleentam |
| Friday | @eli_kastelein / @kathleentam |
All GitLab data team members can, and are encouraged to, perform code review on merge requests of colleagues and community contributors. If you want to review merge requests, you can wait until someone assigns you one, but you are also more than welcome to browse the list of open merge requests and leave any feedback or questions you may have.
Note that while all team members can review all merge requests, the ability to accept merge requests is restricted to maintainers.
Code ownership is a feature of GitLab that links a project member to specific folders and files in a project. It is meant to answer the questions "who can I ask about this code?" and "who should review changes to this code?".
Becoming a code owner is part of the journey to becoming a project maintainer. If you are the sole creator of a file, say a new dbt model set, you will be the de facto code owner for those files. If you wish to expand your ownership purview, follow these steps:
A maintainer in any of the data team projects is not synonymous with any job title. Here, the data team takes from the precedent set forward by the engineering division on the responsibilities of a maintainer. Every data team project has at least one maintainer, but most have multiple, and some projects (like Analytics) have separate maintainers for dbt and orchestration.
We have guidelines for maintainership, but no concrete rules. Maintainers should have an advanced understanding of the GitLab Data projects codebases. Prior to applying for maintainership of a project, a person should gain a good feel for the codebase, expertise in one or more domains, and deep understanding of our coding standards. You're probably ready to become a maintainer when both of these statements feel true:
If those subjective requirements are satisfied, this is the process to add yourself as a maintainer:
