dbt mart best practices на собеседовании Data Engineer

Layered structure

Standard dbt project:

models/
  staging/      — raw cleanup, 1:1 с sources.
  intermediate/ — joins, transformations.
  marts/        — business-facing.

Staging models

stg_<source>__<table>.sql.

Purpose. Light cleanup от source.

• Rename columns standard.
• Cast types.
• Light filter (deleted=true).

SELECT
  id AS user_id,
  email,
  CAST(created_at AS TIMESTAMP) AS created_at
FROM {{ source('app', 'users') }}
WHERE deleted = FALSE

One model per source table. Не joins / aggs.

Intermediate

int_<purpose>.sql.

Purpose. Reusable joins / logic. Not exposed к downstream.

-- int_user_with_orders.sql
SELECT
  u.user_id,
  u.email,
  COUNT(o.id) AS orders_count
FROM {{ ref('stg_app__users') }} u
LEFT JOIN {{ ref('stg_app__orders') }} o ON u.user_id = o.user_id
GROUP BY 1, 2

DRY — reusable в multiple marts.

Marts

mart_<subject>__<grain>.sql или fct_<grain>.sql, dim_<entity>.sql.

Purpose. Business-facing. BI / dashboards / ML use.

-- fct_orders_daily.sql
SELECT
  DATE,
  country,
  SUM(revenue) AS revenue,
  COUNT(*) AS orders_count
FROM {{ ref('int_orders_enriched') }}
GROUP BY 1, 2

Naming conventions

sources:    raw_app.users
staging:    stg_app__users
intermediate: int_users_enriched
marts:
  - dim_*  — dimensions
  - fct_*  — facts
  - mart_* — wider

Predictable patterns help discovery.

Связанные темы

• dbt на собесе DE
• dbt sources и macros для DE
• DWH stages для DE
• Data mart design для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на dbt Labs best practices documentation.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.