Skip to content

13. DBT Access Deployment Security And Data Flow

Jump to bottom
jmesi edited this page Aug 8, 2024 · 1 revision

DBT Access, Deployment, Security and Data Flow

DBT (data build tool), is an open-source data transformation tool that enables data teams to transform data using analytics engineering best practices. With built-in dependency management, version control, testing capabilities, and automatic documentation generation, dbt streamlines the entire data transformation process. It brings best practices from software engineering to the realm of data analytics, promoting collaboration, reliability, and efficiency in data workflows. It communicates with databases through adapters.

Deployment Options

  • DBT Cloud - DBT Cloud is a SaaS product that allows you to run DBT in the cloud. It is a hosted service that runs your DBT jobs on a schedule, and provides a web-based interface for monitoring and managing your DBT runs. It also provides a web-based IDE for writing and editing DBT projects.

  • DBT Core CLI - The dbt command line interface (CLI) is the primary way that you interact with dbt projects. The CLI is a Python application that can be installed with pip. It is the same application that is used by dbt Cloud to run your jobs.

Access

  • DBT Core CLI - The only way to access the DBT CLI is from the environment in which it is installed. This is typically a developer's laptop or SSH-ing into a server it's been deployed to. The DBT CLI is not accessible from the internet.

  • DBT Cloud - DBT Cloud offers two types of access control flavors:

    • License-based Access Controls: User are configured with account-wide license types. These licenses control the specific parts of the dbt Cloud application that a given user can access. A user's license type controls the features in dbt Cloud that the user is able to access. dbt Cloud's three license types are:

      • Developer — User may be granted any permissions.

      • Read-Only — User has read-only permissions applied to all dbt Cloud resources regardless of the role-based permissions that the user is assigned.

      • IT — User has Security Admin and Billing Admin permissions applied regardless of the role-based permissions that the user is assigned.

    • Role-based Access Control (RBAC): Users are assigned to groups that have specific permissions on specific projects or the entire account. A user may be a member of multiple groups, and those groups may have permissions on multiple projects.

  • See the dbt cloud user access documentation for more information.

Architecture and Data Flow

DBT Architecture Diagram

  • The dbt Cloud application is comprised of a set of static components as well as a set of dynamic components. The static components are constantly running to serve highly available dbt Cloud functionality, for example, the dbt Cloud web application. The dynamic components are created ad-hoc to fill background jobs or a user request to use the IDE

Static Application Components​

  • API gateway: The API gateway is the entry point for all client requests to dbt Cloud. The API gateway serves static content and contains logic for routing requests within the dbt Cloud application.

  • App: The app is the dbt Cloud application server. It consists of a Django application that serves dbt Cloud REST API requests.

  • Scheduler: The scheduler is a continuously running process that orchestrates background jobs in dbt Cloud. It consists of two components: the scheduler container, which provisions dynamic resources just-in-time, and the background cleanup container, which performs maintenance tasks on the dbt Cloud database, including flushing logs from dbt runs out into the object store.

Dynamic Application Components​

  • dbt run: A "run" in dbt Cloud represents a series of background invocations of dbt that are triggered either on a cron scheduler, manually by a user, or via dbt Cloud's API.

  • dbt develop: This server is capable of serving dbt IDE requests for a single user. dbt Cloud will create one for each user actively using the dbt IDE.

Application Critical Components​

In addition to the application components, there are a few critical dependencies of the application components that are required in order for the dbt Cloud application to function.

  • PostgreSQL database: dbt Cloud uses a PostgreSQL database as its backend. This can be a cloud-hosted database, for example, AWS RDS, Azure Database, Google Cloud SQL (recommended for production deployments); or, it can be embedded into the dbt Cloud Kubernetes appliance (not recommended for production deployments).

  • Object Storage: dbt Cloud requires an S3-compatible Object Storage system for persisting run logs and artifacts.

  • Storage Volumes: dbt Cloud requires a Kubernetes storage provider capable of creating dynamic persistent volumes that can be mounted to multiple containers in R/W mode.

Data Warehouse Interaction​

dbt Cloud's primary role is as a data processor, not a data store. The dbt Cloud application lets users deploy SQL to the warehouse for transformation. However, it is possible for users to dispatch SQL that returns customer data into the dbt Cloud application. This data never persists and will only exist in memory on the instance in question. In order to properly lock down customer data, it is critical that proper data warehouse permissions are applied to prevent improper access or storage of sensitive data.

For a more detailed breakdown of the dbt Cloud apps, download the advanced architecture guide PDF.

Connecting DBT Cloud to PostgreSQL

Required Fields

  • Host Name: The host name of the PostgreSQL database. This can be an IP address or a domain name.

  • Port: The port on which the PostgreSQL database is listening. The default port for PostgreSQL is 5432.

  • Database Name: The name of the database to connect to.

    Development Credentials

    DBT recommends using your personal development credentials to connect to the database during development. You will thereafter be able to provide production credentials separately when ready to deploy to production.

    • Username: The username to use when connecting to the database.

    • Password: The password to use when connecting to the database.

    • Schema: The schema to use during development. Should be unique to your personal development environment and not shared with other users.

Optional Fields

SSH Tunnel Settings

If your database is not accessible from the public internet, you can configure an SSH tunnel to connect to it. This is typically used for databases that are hosted on-premise or in a private cloud.

  • SSH Host Name: The host name of the SSH server to connect to. This can be an IP address or a domain name.

  • SSH Port: The port on which the SSH server is listening. The default port for SSH is 22.

  • SSH User Name: The username to use when connecting to the SSH server.

An RSA SSH Public key is generated for the connection:

  • The private key is stored in the dbt Cloud database and is not accessible to users.

  • There is no option to provide your own private key.

  • There key is not passphrase protected.

  • The key algorithm is RSA. There is no support for other key algorithms (e.g ED25519)

  • The key size is 4096 bits.

  • The only tunneling method supported is SSH Key Authentication. There is no support for other tunneling methods (e.g. password authentication).

  • You can copy this public key to the bastion server to authorize dbt Cloud to connect to your database via the bastion server.

N/B: DBT Cloud provides a list of it's static IP's for whitelisting. You can whitelist these IP's to allow dbt Cloud to connect to your database directly.

Security checklist (DBT Cloud)

  • DBT Cloud is currently hosted and managed by dbt labs.

    • It is hosted in the United States by default but you can request to have your services hosted in EMEA or APAC regions.

    • The deployment is hosted on AWS.

    • You can also request to have a single tenant deployment of DBT Cloud. It is also hosted and managed by dbt labs, on either AWS or Azure.

      • This can have tighter security controls than the default multi-tenant deployment e.g a dedicated VPC, dedicated AWS/Azure account, firewall rules to limit ingress traffic or hosting in specific zones.

  1. Secure Database Connection

    • What are the requirements to integrate the solution with the target data source(s)?

      • Security credentials e.g. hostname, username, and password with optional security(SSH) and configuration(schemas) toggling.

        • Optional SSH tunneling

        • Optional whitelisting of IP addresses

    • What are the controls put in place to ensure the connection between the solution and data source(s) is secure?

      • All communications occur over HTTPS (attempts to connect via HTTP are redirected to HTTPS). DBT Cloud encrypts in transit using TLS 1.2 protocol.

      • AWS PrivateLink to allow supported data platforms on AWS to communicate with dbt Cloud without the traffic traversing the public internet

      • OAUTH integration for supported data platforms (e.g Snowflake, and BigQuery)

    • Is the connection to the solution’s console secure and encrypted? What are the security and encryption standards used?

      • Yes. HTTPS and TLS 1.2

    • How are secrets to the data source(s) managed by the solution? What are the controls put in place to prevent unauthorized access to the secrets?

      • DBT Cloud utilizes AES-256 to encrypt all data at rest on dbt cloud

      • Data centers hosted by AWS (protected by electronic security, intrusion detection systems, and 24/7/365 human staff)

    • Does the solution enable/support credential rotation of the data source?

      • Yes. Credentials can be rotated via the UI. This is however not automated.

  2. Data Protection

    • What type of data is stored on the cloud solution?

      • dbt Cloud account information including job definitions, database connection information, users, etc. Cloud account information does not include any raw data from your warehouse.

      • Logs associated with jobs and interactive queries you’ve run.

      • Your dbt “assets” which include things like run_results.json and manifest.json.

    • How does the solution handle confidential e.g. PII data obtained from the sources?

      • Option to change data residency from default US to EU (data will be protected by GDPR).

      • No special handling of PII data. User is responsible for ensuring that the data is handled in a secure manner.

      • N/B: DBT Cloud does not store any data from the sources. It only stores metadata and credentials.

    • Does the solution require privileged access to the database? What kind of privileges/permissions does the solution require?

      • For DBT cloud to be able to run smoothly without encountering permission issues, such as creating schemas, reading existing data, and accessing the information schema, provide the following permissions to the database role:
        • grant usage on database and schema(s) to user
        • grant select on all tables and views required to user
        • create schema
        • create table
        • create view

    • How is the data in the cloud protected from external access i.e. maintainer of the cloud solution? What level of access does the maintainer require to provide support?

      • dbt Labs has set up the backend to be fully automated and is not accessible by dbt Labs personnel unless a problem occurs to the tool, and then only the personnel capable of diagnosing and fixing a problem would have access.

      • dbt Cloud’s data centers are hosted using Amazon Web Services, where they are protected by electronic security, intrusion detection systems, and 24/7/365 human staff.

      • dbt Cloud uses actively maintained, long-term-supported operating systems that are kept up to date with the latest security patches.

      • dbt Cloud uses a dedicated firewall and private network to prevent unauthorized network access.

      • We limit access to sensitive data to those with a business reason for access.

      • N/B: Data remains in your data warehouse – It is not uploaded or downloaded. Your query is created on the frontend in your browser (it is not cached by dbt), behind your firewalls and VPN, and is not accessed by any of dbt labs employees unless you screen share with their support team

  3. Identity and access management

    • What are the authentication methods that the solution supports?

      • Basic authentication (username and password)

      • Optional SSO integration

      • RBAC

    • How does the solution manage user privileges/permissions? Does the solution support user roles and is there a mechanism to determine privileged users from non-privileged ones?

    • How does the solution handle project scope such that a user has access to the project he/she is working on?

      • Supports project level access controls via RBAC and license-based access controls.
      • Users segregated into groups that have specific permissions on specific projects or the entire account.

    • Does the solution conform to best-practice around Password management for basic authentication?

      • Minimum password length of 8 characters.

        • Yes. Passwords must be at least 9 characters long.

      • Require at least three of these categories: uppercase, lowercase, numeric and special characters.

        • Yes. Password must contain between 9 and 255 characters and include at least one uppercase, lowercase, numeric, and special character.

      • Password expiry period of 45 days.

        • No. Passwords do not expire.

    • Does the solution support MFA as an additional security control on authentication?

      • No. MFA is not supported yet.

  4. Architecture

    • Can the solution support data sources that have limited access e.g. private/internal database?

      • Yes:

        • Through an ssh tunnel via a bastion host
        • Through AWS PrivateLink
        • Through OAUTH integration (Snowflake, and BigQuery)
        • Through private VPC (single tenant deployment)

    • For public data sources, what controls are used to protect/restrict access to the data sources?

      • Secure connection (Enforced HTTPS and TLS 1.2)

  5. Disaster Recovery

    • Does the maintainer have a disaster recovery process and/or procedure in the event of an undesirable outcome?

      • Yes

    • What features does the solution have on managing disaster recovery at an application level e.g. backups etc?

      • dbt Cloud is primarily hosted in AWS, and deployed across multiple AZ’s (availability zones) in a region.

      • Retention of backups are a minimum of seven (7) days.

      • Staff is remotely distributed across the US providing support to customers globally. Distributed workforce allows us to provide support virtually from anywhere and reduce the impact of support interruption in a geographic location.

      • Business Continuity, Disaster Recovery, and Incident Response Plans are tested annually via tabletop exercises.

  6. Monitoring and logging

    • Does the solution support audit logs as a monitoring mechanism?.

      • No. Only logs available are for job runs (dbt build etc). No publicly available audit logs for infrastructure.

  7. Security Documentation

    • Does the maintainer have publicly shared their security policies? If not can this be shared on request?

Security, Access, Deployment, And Data Flows

Clone this wiki locally