An analytics engineer is reviewing a pull request. She explains the change to her colleague: "I replaced the hard-coded table name in line 12 with ref('stg_orders'). This tells dbt about the dependency so it builds the models in the correct order and resolves the correct database and schema at run time." What is the primary purpose of the ref() function in dbt?
ref(): the central dbt function that creates inter-model dependencies. When you write FROM {{ ref('stg_orders') }}, dbt: (1) resolves the correct database.schema.table name for the current target environment (dev vs. prod); (2) registers the dependency in the DAG (Directed Acyclic Graph) so models build in the right order. Without ref(), models would be isolated — dbt could not build the lineage graph or enforce build order. Contrast with source(), which references raw upstream tables defined in sources.yml rather than other dbt models. In conversation: "Always use ref() between models — hard-coding table names breaks the DAG and means your CI run can silently reference production data instead of the dev schema."
2 / 5
A data team is debating how to materialise their largest fact table. The lead engineer says: "A full table rebuild takes 45 minutes. We should switch to incremental — it only processes new or updated rows since the last run. The trade-off is that we need a reliable updated_at field and we have to handle late-arriving data carefully." Which statement best describes the incremental materialisation in dbt?
dbt materialisations — how dbt persists a model's results: table — drops and recreates the full table on every run. Safe, simple, slow for large datasets. view — creates a SQL view; no data stored; runs query fresh on each access. Fast to build, slow to query at scale. incremental — on first run, builds the full table; on subsequent runs, processes only new/changed rows (filtered by a condition like updated_at > max(updated_at)) and merges them in. Requires a reliable change-tracking column and careful handling of late-arriving data. ephemeral — inlined as a CTE into the downstream model; never materialised in the warehouse. Good for lightweight intermediate logic. In conversation: "We moved our events table from table to incremental and cut the nightly build from 40 minutes to 4 — but we had to add a full-refresh run at the weekend to catch any late data."
3 / 5
During an onboarding session, a senior analytics engineer explains the team's dbt project structure: "The first layer is staging. Staging models do one job: they select from a single raw source, rename and cast columns to our conventions, and apply no business logic whatsoever. Nothing downstream should ever query the raw tables directly — they always go through staging." Which of the following is not a typical responsibility of a staging layer model?
Staging layer rules (dbt Labs style guide): one staging model per source table; rename and cast only; no business logic; no joins to other models; always built as a view (cheap, always fresh). Joining models and applying business logic belongs in the intermediate layer (or marts layer). Typical dbt project layers: staging (stg_) — 1:1 with source tables; clean, typed, renamed. intermediate (int_) — joins, deduplication, business rules; usually ephemeral or view. marts (mart_ / fct_ / dim_) — domain-specific, business-facing tables optimised for BI tools; usually materialised as tables. mart = a subset of the warehouse designed for a particular team or domain (marketing mart, finance mart). In conversation: "If you catch yourself writing a JOIN in a staging model, stop — move it to intermediate. Staging should be painfully boring."
4 / 5
A new team member asks why there is a snapshots/ folder in the dbt project. The tech lead explains: "Snapshots capture the state of a mutable source table over time. Every time you run dbt snapshot, dbt checks for rows that have changed and records the old state with a valid_from / valid_to timestamp. It's how we implement Type 2 slowly changing dimensions without building custom pipeline code." What is the main problem a dbt snapshot solves?
dbt snapshot: solves the SCD Type 2 (Slowly Changing Dimension) problem. Source systems often only store the current state of a record — if a customer changes their plan, the old plan is overwritten. A snapshot adds two columns: dbt_valid_from and dbt_valid_to (null = current). On each run, dbt uses a strategy (timestamp or check) to detect changed rows and inserts a new record for the updated version. Related vocabulary: lineage graph — the DAG dbt computes from all ref() and source() calls; shown in dbt docs as an interactive graph. seed — a CSV file committed to the dbt project and loaded into the warehouse as a table; used for small static lookup data (country codes, category mappings). exposure — a declaration in schema.yml of an external consumer of dbt models (a dashboard, ML model, or application); appears in the lineage graph so teams can assess the downstream impact of changes. In conversation: "We added snapshots for the accounts table last quarter — finally we can answer 'what plan was this customer on in March?' without guessing."
5 / 5
During a data strategy review, the head of data says: "Right now, 'monthly active users' means three different things depending on which dashboard you look at. We're implementing a semantic layer so every tool — Tableau, Mode, our data app — queries a single defined metric, not raw SQL. The metric definition lives in one place; the tools just consume it." What is the core purpose of a semantic layer (also called a metrics layer)?
Semantic layer / metrics layer: a centralised service where business metrics are defined once (using code — e.g., dbt's MetricFlow or tools like Cube, LookML) and exposed to BI tools, notebooks, and APIs via a consistent query interface. Problems it solves: metric inconsistency — different teams computing "revenue" differently. Logic duplication — the same join or filter written in ten dashboards. Governance — metric definitions are version-controlled and auditable. Key concepts: metric — a quantifiable business measure with a defined formula, filters, and grain. dimension — an attribute used to slice a metric (date, region, plan). data contract — a formal agreement between data producers and consumers specifying schema, semantics, and quality guarantees; downstream analytics depends on contracts being stable. dbt documentation site — generated by dbt docs generate and dbt docs serve; includes the lineage graph, model descriptions, column definitions, test results, and exposures. Jinja template — dbt models are SQL files with Jinja2 templating; enables macros, conditionals, and loops inside SQL. schema test (generic test) — a reusable dbt test applied in schema.yml: not_null, unique, accepted_values, relationships. In conversation: "Before the semantic layer, 'churn rate' had five definitions. Now there is one — defined in code, tested, and consumed by every tool the same way."
These modules build the same on-the-job skills as Analytics Engineering Vocabulary
— work through them together for a fuller vocabulary set.
Data Warehousing— useful for Data warehousing & analytics (Data Science & ML)
Frequently Asked Questions
What does the "Analytics Engineering Vocabulary" vocabulary exercise cover?
This exercise tests real IT vocabulary related to analytics engineering vocabulary through 5 multiple-choice questions, each built from realistic workplace sentences rather than abstract definitions.
Is this vocabulary exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is completely free — no account, sign-up, or payment required.
How many questions does this exercise have?
This exercise has 5 questions. Each one shows a real-world sentence or scenario with multiple-choice options and an explanation once you answer.
What happens after I answer a question?
You'll see immediate feedback showing whether your answer was correct, along with a short explanation of why — then a button to move to the next question, and a full results screen at the end.
Can I retry the exercise if I get questions wrong?
Yes. Once you reach the results screen, click "Try again" to reset your answers and go through the exercise from the start as many times as you like.
Do I need to create an account to take this exercise?
No account is needed. Your answers are scored in your browser during the session — nothing is saved to a server, so you can jump straight in.
Is my progress saved if I leave the page?
No — progress within an exercise resets if you navigate away or reload. Each exercise is short enough to complete in a few minutes in one sitting.
Are these vocabulary exercises connected to other topics?
Yes — this module shares real-world context with 1 other vocabulary module. See "Related vocabulary" below to keep building a connected skill set.
How is this different from reading a glossary or blog article?
Exercises like this one are active recall drills — you have to choose the correct term or phrasing yourself, which builds retention faster than passively reading a definition.
Where can I find more vocabulary exercises?
Browse the full Vocabulary exercises hub for hundreds of modules covering Agile, DevOps, security, databases, architecture, and more — organised by IT role and skill.