Download PrepIQ CoreAnalyst DBT LBC Ultimate Exam and more Exams Technology in PDF only on Docsity!
Question 1. In the ELT workflow, what is the primary role of dbt? A) Extract data from source systems B) Load raw data into the warehouse C) Transform data already loaded in the warehouse D) Visualize data for end-users Answer: C Explanation: dbt operates after data has been extracted and loaded; it focuses on transforming that data using SQL and tests. Question 2. Which dbt file defines the relationship between models and the database schema? A) dbt_project.yml B) profiles.yml C) seeds.yml D) packages.yml Answer: A Explanation: dbt_project.yml contains model configurations, folder structures, and default schemas for models. Question 3. Which of the following is NOT a dbt resource type? A) Source B) Seed C) Snapshot D) Pipeline Answer: D Explanation: Pipeline is not a dbt resource; dbt resources include sources, models, seeds, snapshots, and tests. Question 4. How do you reference a source table inside a dbt model? A) {{ ref('my_source') }}
B) {{ source('schema_name', 'table_name') }} C) SELECT * FROM my_source D) {{ config(source='my_source') }} Answer: B Explanation: The source() macro takes the source name and table name to reference a source defined in sources.yml. Question 5. Which CLI command compiles dbt models without executing them? A) dbt run B) dbt test C) dbt compile D) dbt docs generate Answer: C Explanation: dbt compile parses and renders the SQL for all models, storing the compiled files without running them. Question 6. In a typical dbt project, where should staging models be placed? A) models/staging/ B) models/intermediate/ C) models/marts/ D) models/raw/ Answer: A Explanation: The staging layer holds raw-to-clean transformations and is conventionally stored under models/staging. Question 7. What naming convention is recommended for dbt columns? A) camelCase B) PascalCase C) snake_case D) kebab-case Answer: C
Question 11. When would you prefer an incremental materialization over a table materialization? A) When the model is small and recomputation is cheap B) When the source data never changes C) When processing large tables where only new rows are added D) When you need a temporary result set for a single query Answer: C Explanation: Incremental models process only new or changed rows, reducing runtime for large, append-heavy tables. Question 12. In dbt, what does the is_incremental() macro return? A) True if the model is materialized as a view B) True if the current run is an incremental run C) The number of rows inserted in the current run D) The name of the incremental key column Answer: B Explanation: is_incremental() evaluates to true only when the model is being run in incremental mode. Question 13. Which Jinja syntax correctly loops over a list of columns? A) {% for col in columns %} {{ col }} {% endfor %} B) {{ for col in columns }} {{ col }} {{ endfor }} C) {% loop col in columns %} {{ col }} {% endloop %} D) {{ loop columns as col }} {{ col }} {{ endloop }} Answer: A Explanation: The standard Jinja for-loop uses {% for … %} and {% endfor %} tags. Question 14. How can you pass a variable from the CLI to a dbt model? A) dbt run --vars '{"my_var": "value"}' B) dbt run --set my_var=value C) dbt run -v my_var=value
D) dbt run --config my_var=value Answer: A Explanation: The --vars flag accepts a JSON string of variables that can be accessed via {{ var('my_var') }}. **Question 15. Which dbt command generates documentation and a lineage graph? ** A) dbt run B) dbt test C) dbt docs generate D) dbt compile Answer: C Explanation: dbt docs generate builds the documentation site, including model descriptions and the lineage graph. Question 16. What is the purpose of the tags attribute in a model’s .yml file? A) To define column data types B) To apply runtime configurations C) To group models for selective execution or documentation D) To set the model’s schema name Answer: C Explanation: Tags allow you to label models and later run or document only those with specific tags. Question 17. In Kimball dimensional modeling, which table stores transactional metrics? A) Dimension table B) Fact table C) Bridge table D) Lookup table Answer: B
**Question 21. What does idempotent mean in the context of dbt transformations? ** A) The transformation runs only once per day B) Running the transformation multiple times yields the same result without side effects C) The model automatically retries on failure D) The model is materialized as an incremental table Answer: B Explanation: Idempotent transformations produce consistent results regardless of how many times they are executed. Question 22. Which of the following best describes a Data Vault Hub? A) Stores descriptive attributes of an entity B) Captures the unique business keys of an entity C) Links multiple hubs together D) Holds historical changes to a hub’s attributes Answer: B Explanation: Hubs contain the unique business keys and a surrogate key, serving as the central anchor for a Data Vault model. Question 23. When would you choose a Data Vault architecture over a star schema? A) When you need simple, flat reporting tables B) When the source systems have highly volatile schema and you need auditability C) When you have only a few dimensions D) When you want to avoid any historical tracking Answer: B Explanation: Data Vault excels in environments with frequent source changes and a need for full audit trails.
Question 24. Which dbt command runs only the models that have changed since the last successful run? A) dbt run --select state:modified B) dbt run --full-refresh C) dbt run --exclude state:modified D) dbt run --vars '{"incremental": true}' Answer: A Explanation: The state:modified selector runs models whose code or dependencies have changed. Question 25. In dbt, what is the effect of setting materialized: view in a model’s config block? A) The model will be stored as a physical table B) The model will be compiled but not executed C) The model will be created as a database view each run D) The model will be cached in memory only Answer: C Explanation: Setting materialized to view tells dbt to create a database view that reflects the model’s SELECT statement. Question 26. Which of the following is a recommended practice for naming dbt models? A) Prefix all models with tbl_ B) Use singular nouns for model names C) Include the layer name as a folder, not in the file name D) Use uppercase letters for readability Answer: C Explanation: Organizing models into folders (staging, intermediate, marts) provides clear separation without polluting file names. Question 27. How can you test source freshness in dbt? A) By adding a test block under the source definition with freshness:
Answer: B Explanation: The dbt-built models act as the authoritative, cleaned, and tested data layer for downstream consumption. Question 31. Which dbt configuration option disables the automatic creation of a schema for a model? A) schema: false B) persist_docs: false C) enabled: false D) alias: null Answer: C Explanation: Setting enabled: false prevents the model from being compiled or materialized, effectively disabling it. Question 32. In a BI tool like Looker, what is the primary purpose of a view file (LookML)? A) To define ETL pipelines B) To store raw data extracts C) To map dbt models into dimensions and measures for exploration D) To schedule dbt runs automatically Answer: C Explanation: LookML view files expose dbt-generated tables as dimensions, measures, and filters for end-users. Question 33. Which of the following statements about dbt seeds is true? A) Seeds are automatically refreshed on every dbt run B) Seeds must be stored in CSV format in the data/ directory C) Seeds can only be loaded into Snowflake warehouses D) Seeds support incremental loading by default Answer: B Explanation: Seeds are CSV files placed in the data/ folder and are loaded as static tables when dbt seed runs.
Question 34. How does dbt handle column-level documentation? A) By adding comments directly in the SQL file B) By defining description: under the column in the schema.yml file C) By creating a separate markdown file per column D) By using the doc() macro inside the model SQL Answer: B Explanation: Column descriptions are stored in the schema.yml file under the columns: block. Question 35. Which of the following best describes a “bridge table” in dimensional modeling? A) A table that stores slowly changing dimensions B) A table that resolves many-to-many relationships between facts and dimensions C) A table that holds aggregated metrics for reporting D) A table that contains raw source data Answer: B Explanation: Bridge tables (or associative tables) break many-to-many relationships into two one-to-many joins. Question 36. When using dbt Cloud Scheduler, what must you configure to ensure the job runs with the correct profile? A) The Git branch only B) The target environment (dev, prod) in the job settings C) The profile name in the dbt_project.yml file only D) The number of worker threads Answer: B Explanation: In dbt Cloud, you select the target (dev, prod, etc.) so the job uses the appropriate profile credentials. Question 37. Which of the following is a valid way to limit a dbt model to a specific warehouse in Snowflake?
D) It allows the table to be materialized as a view Answer: B Explanation: Partitioning enables BigQuery to scan only relevant partitions, reducing query latency and cost. Question 41. Which dbt test type validates that a foreign key column only contains values present in a referenced primary key column? A) not_null B) unique C) relationships D) accepted_values Answer: C Explanation: The relationships test checks referential integrity between two columns. Question 42. In a Data Vault model, which component stores time-variant descriptive attributes? A) Hub B) Link C) Satellite D) Bridge Answer: C Explanation: Satellites attach descriptive, historical data to hubs or links. Question 43. Which of the following is a recommended practice for handling test failures in CI pipelines? A) Ignoring failures and proceeding to deployment B) Automatically merging the branch regardless of failures C) Failing the pipeline and requiring a developer to fix the issue before merge D) Running tests only on the master branch Answer: C
Explanation: CI should stop on test failures to maintain data quality; developers must resolve issues before merging. Question 44. Which dbt command generates a compiled SQL file for a single model without executing it? A) dbt compile --select model_name B) dbt run --select model_name --dry-run C) dbt test --select model_name --compile-only D) dbt docs generate --select model_name Answer: A Explanation: dbt compile --select renders the SQL for the specified model and writes it to the target directory. Question 45. What does the persist_docs configuration option control? A) Whether model documentation is stored in the warehouse as table comments B) Whether docs are exported to a PDF file C) Whether dbt keeps a copy of the source files after compilation D) Whether dbt automatically creates a Git tag for each run Answer: A Explanation: persist_docs writes model and column descriptions to the database as comments. Question 46. Which of the following best describes a “semantic layer” in modern BI architectures? A) The raw data ingestion pipeline B) A set of business-logic definitions that translate raw tables into user-friendly objects C) The physical storage engine of the warehouse D) The network layer connecting BI tools to the database Answer: B Explanation: The semantic layer abstracts technical tables into dimensions, measures, and calculated fields for end-users.
Question 50. Which of the following is NOT a typical component of a CI/CD pipeline for dbt? A) Linting of dbt files (e.g., dbt-utils lint) B) Running dbt tests C) Deploying raw source files to the warehouse D) Generating dbt documentation and publishing it Answer: C Explanation: CI/CD pipelines focus on code, tests, and documentation; raw source loading is handled separately. Question 51. In a star schema, which attribute is most likely to be a surrogate key? A) Customer natural ID (e.g., email) B) Transaction timestamp C) Auto-incremented integer in the dimension table D) Product SKU Answer: C Explanation: Surrogate keys are artificial, typically auto-incremented integers used to join facts to dimensions. Question 52. Which dbt macro would you use to dynamically generate a list of date partitions for an incremental model? A) {{ generate_date_partitions(start_date, end_date) }} B) {{ date_spine(start_date, end_date, interval='day') }} C) {{ make_date_range(start_date, end_date) }} D) {{ partition_by_date(start_date, end_date) }} Answer: B Explanation: The date_spine macro (often provided by dbt-utils) creates a table of dates for partitioning. Question 53. What does the --vars flag allow you to do in a dbt run? A) Override profile credentials
B) Pass custom variables that can be accessed via the var() function C) Change the target schema name on the fly D) Enable debug logging for the run Answer: B Explanation: --vars passes a JSON object of variables for use within models, tests, or macros. Question 54. Which of the following is a correct way to define a source freshness threshold of 24 hours? A) freshness: { warn_after: { count: 24, period: hour } } B) freshness: { error_after: { count: 1, period: day } } C) freshness: { warn_after: { count: 1, period: day } } D) freshness: { error_after: { count: 24, period: hour } } Answer: C Explanation: warn_after with 1 day triggers a warning if the source is older than 24 hours. Question 55. In dbt, which configuration option controls the number of threads used during a run? A) threads: in profiles.yml under the target B) max_threads: in dbt_project.yml C) concurrency: in the dbt CLI command D) parallel: in the model config block Answer: A Explanation: The threads setting in profiles.yml defines how many parallel connections dbt can open. Question 56. Which of the following best describes a “bridge table” used to model many-to-many relationships? A) A table that stores aggregated metrics B) A table that contains only surrogate keys linking two dimension tables C) A table that holds slowly changing attributes
Explanation: Snowflake clustering on the date column helps prune micro-partitions, speeding up date-range queries. Question 60. Which of the following is a key advantage of using dbt’s ephemeral materialization? A) It stores the result as a permanent table B) It creates a view that can be queried by downstream models C) It inlines the model’s SQL into dependent models, avoiding extra objects in the warehouse D) It automatically partitions the data by day Answer: C Explanation: Ephemeral models are compiled into CTEs within downstream models, leaving no separate objects. Question 61. In a CI workflow, which Git strategy is recommended for collaborating on dbt projects? A) Direct commits to main without pull requests B) Feature branches with pull requests reviewed before merging into dev C) Rebase all branches onto master daily without testing D) Using a single branch for all environments (dev, prod) Answer: B Explanation: Feature branches and PR reviews enforce code quality and allow testing before integration. Question 62. Which dbt test would you use to ensure a column only contains values from a predefined list? A) unique B) not_null C) accepted_values D) relationships Answer: C
Explanation: accepted_values validates that column entries belong to a specified set. Question 63. What is the primary purpose of a “seed” file in dbt? A) To store large fact tables for analytics B) To provide static reference data that can be version-controlled C) To define incremental loading logic D) To hold raw source data extracted from APIs Answer: B Explanation: Seeds are small, static CSV files used for reference data (e.g., lookup tables) and are versioned with the dbt repo. Question 64. Which of the following is NOT a valid dbt materialization? A) view B) table C) incremental D) materialized_view Answer: D Explanation: materialized_view is not a built-in dbt materialization; the valid ones are view, table, incremental, and ephemeral. Question 65. In a Data Vault model, how are many-to-many relationships typically represented? A) Multiple hubs with a single link B) A link table that references multiple hub surrogate keys C) A satellite attached to a hub D) A bridge table outside the Data Vault structure Answer: B Explanation: Links can contain multiple foreign keys, allowing representation of many-to-many relationships between hubs.
