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 / 14
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 / 14
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 / 14
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 / 14
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."
6 / 14
Sarah, a junior analytics engineer, is drafting a PR description for a change to her dbt model. She writes: 'This update introduces a new column, customer_segment, calculated directly within the model using a SQL function. This avoids redundant data transformations and improves query performance.' What does Sarah primarily aim to achieve with this change?
Sarah is focusing on query optimization. Calculating the customer_segment within the dbt model directly avoids redundant SQL queries that would have otherwise been needed to derive this value. This improves performance by reducing data transfer and processing overhead. The incorrect options misinterpret her intention or introduce irrelevant concerns like complexity or data quality.
7 / 14
During a Slack conversation about optimizing their dbt warehouse, David, an analytics engineer, says: 'We're using dbt's `materialized` setting for our fact tables. This ensures that the table is always up-to-date with the latest data, but it comes at the cost of increased build times.' What does David primarily mean by 'materialized' in this context?
David is referring to the automatic population of the materialized fact table. The `materialized` setting in dbt instructs it to create a physical copy of the data from the source tables whenever the model is built. This guarantees that the table always contains the most recent data, though at the expense of increased build times due to the full refresh.
8 / 14
Maria, a senior analytics engineer, is reviewing a code review comment: 'The dbt model uses `select *` which should be avoided. It's best to explicitly define the columns you need to improve performance and reduce data transfer.' What is the primary reason Maria suggests avoiding using `select *` in dbt?
Maria highlights the impact on query efficiency. Using `select *` retrieves all columns from a table, even if they are not needed for the query, leading to increased data transfer and potentially slower execution times. Explicitly defining required columns minimizes these inefficiencies.
9 / 14
During a standup meeting, Ben, an analytics engineer, says: 'We're using dbt's `incremental models` to process our customer data. This allows us to only update the table with new or modified records since the last run.' What is the core benefit of using incremental models in dbt?
Ben's statement focuses on the efficiency of data processing. Incremental models are designed to significantly reduce build times by only processing new or updated records since the last run, rather than rebuilding the entire table with every change. This dramatically improves performance and reduces resource consumption.
10 / 14
Liam (a junior analytics engineer) is explaining a new dbt model to his team. He says: 'We're using `dbt_starrocks` macro here. It allows us to directly write data to StarRocks, bypassing the staging layer for this specific query.' Which of the following best describes the primary benefit of using dbt_starrocks?
The dbt_starrocks macro is designed for optimized performance when directly writing to StarRocks. This bypasses the staging layer and utilizes StarRocks' query engine, resulting in significantly faster execution times. Option A is incorrect because schema evolution is typically managed through dbt's metadata management features, not the macro itself. Options C and D misrepresent the macro's function – it doesn't enforce best practices or eliminate transformation needs.
11 / 14
Chloe (a senior analytics engineer) is discussing a performance issue with her team. She states: 'We've noticed that our daily fact table rebuilds are taking an unusually long time – over an hour. We need to investigate if the current materialized view approach is still optimal given the volume of updates.' What does Chloe primarily suggest investigating?
Chloe's statement highlights a potential bottleneck: long fact table rebuild times. The core issue is the *materialized view approach* itself – its efficiency can degrade with high update volumes. Partitioning and the Snowflake adapter are secondary considerations; the fundamental question is whether the materialized view is still the best strategy for rebuilding the table. Incremental models address this but don't change the underlying rebuild process.
12 / 14
David (an analytics engineer) is writing a PR description for a change to a dbt model. He writes: 'This commit adds a new `customer_segment` column calculated using the `case` function within the model. This allows us to enrich our customer data with segment information.' What's the *most* important consideration David should address in subsequent documentation?
While SQL syntax and schema details are important, David's primary responsibility is to explain *why* the segmentation logic exists. The source – where does this data come from? – directly impacts report accuracy and downstream analysis. Simply stating it's a calculated column isn't sufficient; users need to understand its origin and potential biases or limitations.
13 / 14
Maria (a data architect) is explaining the concept of 'snapshots' to a new team member. She says: 'Snapshots capture the state of a mutable source table at a specific point in time. Every time you run `dbt snapshot`, dbt checks for rows that have been added or changed since the last snapshot.' What is the *primary* purpose of using snapshots?
The core purpose of snapshots is to track *changes* in the source tables. This historical record is invaluable for debugging data discrepancies, understanding data drift over time (for auditing), and recreating specific states for testing or reporting. While snapshots contribute to consistency, their primary function isn't updating staging or optimizing queries.
14 / 14
Ben (an analytics engineer) is in a Slack channel discussing optimization strategies. He says: 'We're using dbt's `ref()` function to establish dependencies between models. This helps dbt understand the order of execution and avoid unnecessary rebuilds.' Which aspect does Ben's statement primarily address?
The ref() function is critical for dbt's dependency resolution. By explicitly defining relationships between models, dbt can intelligently determine the optimal execution order to minimize rebuild times and prevent redundant operations. It focuses directly on query optimization through dependency management.
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 14 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 14 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.