ELT interviews should assess how a candidate turns loaded source data into dependable analytical interfaces inside a destination platform. Recruiters need to distinguish basic SQL fluency from the ability to design a transformation graph, choose materializations and explain what happens when a shared rule or an incremental target changes. Those decisions determine whether a team can evolve its reporting models without losing control of cost, compatibility or the meaning of historical results.
These 100+ ELT Interview Questions and Answers cover 120 questions across beginner, intermediate and experienced levels. The focus is destination-first transformation engineering: model boundaries, compilation, dependency selection, incremental recomputation, isolated development and applied release state. dbt examples use the v1.11 SQL-project workflow as a baseline with a compatible adapter; Snowflake examples are explicitly platform-specific. Confirm the deployed versions and feature support before applying product-specific behavior.
How can recruiters tell whether an ELT candidate understands the data behind a successful build?
Ask the candidate to change a shared classification model and explain which downstream outputs need to be rebuilt, which historical rows may remain stale and how CI would exercise the incremental path. Follow up by asking where each reference resolves and which evidence proves the consumer sees the intended result. This reveals practical judgment about state and dependencies that a polished query alone cannot demonstrate.
Remote technical interviews can involve AI-assisted cheating, deepfakes, impersonation or unauthorized assistance. Sherlock AI can support interview-integrity workflows alongside structured follow-up questions and human review. Explain permitted tools clearly and interpret integrity signals in context rather than treating one signal as conclusive proof of misconduct.
ELT Interview Questions
Choose a sequence that matches the role’s responsibility for analytical models, releases and destination operations. Ask for a concrete model graph before moving into product-specific details.
ELT Interview Questions for Beginners
These questions establish model boundaries, dependencies and the difference between a definition and its stored output.
What does ELT mean in an analytical data platform?
Why separate loaded source data from consumer-facing models?
What does a SQL model represent in an ELT project?
What is a transformation dependency graph?
How do landing, staging and business-model layers differ?
Why is an explicit column projection useful at a model boundary?
What is the difference between a relation name and a model name?
What does dbt's ref function contribute beyond inserting a table name?
How does declaring a source differ from defining a model?
What is a materialization?
When is a view a useful model output?
When is a table materialization useful?
What is an ephemeral model in dbt?
How is an incremental model different from a table rebuilt on every run?
How does a managed materialized result differ from a scheduled table build?
What happens when a transformation project is compiled?
Why distinguish project parsing from model execution?
What role does configuration play in ELT behavior?
Why isolate development output schemas?
How should a model document its output contract?
How does a schema contract differ from a data test?
What is a model-level unit test useful for?
What should a data test return when checking for invalid model rows?
Why should transformations be versioned separately from loaded data?
What is a transformation manifest useful for?
How do execution results differ from project metadata?
Why should a model have a clear owner?
What makes a transformation suitable for reuse?
Why avoid using one enormous SQL model for the entire ELT workflow?
What is the difference between a model dependency and an ingestion-readiness signal?
How should loaded semi-structured data become a stable analytical interface?
Why is query-time interpretation sometimes useful in ELT?
What is a model selection expression used for?
Why can a transformation succeed while a consumer still sees old data?
What does full refresh mean for an incremental model?
Why should a transformation job use its own database identity?
How do source freshness and model freshness differ?
What should a small ELT development workflow include?
How should an analyst consume a managed model?
What makes an ELT design understandable in an interview?
ELT Interview Questions for Intermediate
These questions examine compiled SQL, incremental paths, environment binding and selective development workflows.
How can a hardcoded relation create an invisible dependency?
What happens when a dbt ref is hidden inside an execution-only branch?
How do you review generated SQL from a macro-heavy model?
When should a shared transformation become a persisted intermediate model?
How can an ephemeral model increase downstream work?
What must be true for dbt's is_incremental branch to run?
Why is an incremental model's unique key not a uniqueness guarantee?
How do source filters and target match predicates differ in incremental builds?
Why can a lookup change invalidate an incremental fact model?
How should a newly added derived column be populated in an incremental model?
When can an incremental model need a full rebuild after a SQL change?
How do you assess whether a window calculation is incrementally maintainable?
How do you test incremental behavior against a full-build reference?
What should you inspect before changing a model from view to table?
How can model aliases and custom schema rules undermine isolation?
Why should environment-specific row limits be used carefully?
How does adapter dispatch affect a reusable macro?
How do you keep generated SQL parameters safe and understandable?
Why can querying warehouse metadata during compilation be risky?
How do you handle a model contract that passes while numeric precision is wrong?
What are the limits of applying a model contract across materializations?
How can an indirect test selection include an unbuilt model?
How do ancestor and descendant selectors support different tasks?
Why should a CI job preview its resolved model selection?
How do state comparison and deferral solve different CI problems?
When does dbt defer an unselected model reference?
Why can a relationship test behave unexpectedly with deferral?
Why keep the comparison manifest outside the current output directory?
What does cloning an incremental target add to CI coverage?
How do you review a package upgrade in an ELT project?
What is the difference between declaring lineage and observing executed reads?
How do warehouse-native tasks differ from model definitions?
What does a Snowflake dynamic table's target lag mean?
How does Snowflake's ADAPTIVE refresh differ from a permanently full refresh?
Why is AUTO refresh not the same as ADAPTIVE refresh?
How do you choose between native refresh and an externally scheduled transformation?
How do you troubleshoot a model that reads the wrong environment?
How should a model expose rejected type conversions?
How do you prevent an exploratory model from becoming an unsupported production dependency?
What should an ELT pull request explain besides its SQL diff?
ELT Interview Questions for Experienced
These questions explore transitive change impact, applied state, selective releases and native refresh guarantees.
How would you determine the rebuild impact of a shared model change?
How can a full refresh of an upstream model leave downstream data inconsistent?
How do you reason about an incremental transformation with nonlocal dependencies?
How would you design CI evidence for both model logic and materialization behavior?
How would you validate a state-based CI job itself?
Why is state:modified not a complete detector of data-impacting change?
How would you secure and validate a manifest used for deferral?
How do logical state and applied state diverge during deployment?
How would you make a selective release compatible with unchanged consumers?
What are the trade-offs of version-pinning a model reference?
How do you retire a versioned model interface without relying only on the project graph?
How should model access rules relate to database permissions?
How do you avoid mixed-version results during a multi-model release?
Why can rolling back SQL code fail to restore an incremental model?
How would you validate that a full rebuild is actually feasible?
How do you evaluate a custom materialization before adopting it?
How can pre-hooks and post-hooks escape a model's apparent boundary?
How would you diagnose a transformation graph that became expensive after adding one shared model?
How would you set a refresh budget across a dependency graph?
What consistency boundary does a Snowflake dynamic-table pipeline provide?
How can a refresh boundary trade consistency for independent scheduling?
How would you investigate an ADAPTIVE dynamic table that reinitialized unexpectedly?
Why can a native incremental refresh become ineligible after an upstream change?
How do you evaluate a full-refresh node upstream of a native incremental node?
How do scheduled-task privileges affect reproducibility?
How would you combine native refresh with explicit data-quality gates?
What does a reproducible compiled artifact need besides a commit hash?
How would you migrate a macro implementation between adapters?
How do you prevent model configuration from becoming an unreviewable policy layer?
How would you detect stale documentation generated from the wrong artifacts?
How do you assess whether a model boundary is too broad for independent ownership?
How would you support two legitimate interpretations of the same loaded data?
How do you handle a shared source change that affects only some models?
How would you test a model that intentionally depends on the current date?
How do time-dependent expressions challenge incremental maintenance?
How would you validate a deployment that changes only grants or policies?
How do you decide whether a transformation belongs inside the destination engine?
How would you assess an AI-generated model dependency graph?
What evidence shows that a destination-first transformation release is ready?
What distinguishes a mature ELT transformation practice?
ELT Interview Questions and Answers
The answers distinguish general transformation reasoning from framework and warehouse behavior. SQL fixtures make selected dependencies visible; production guarantees still depend on the actual adapter, engine and model contract.
ELT Interview Questions and Answers for Beginners
Look for clear explanations of where work runs, what is persisted and which interface consumers rely on.
1. What does ELT mean in an analytical data platform?
ELT means extract, load and transform: data is delivered to a destination before the relevant analytical transformations run there. The defining question is where the transformation executes, not whether a vendor uses the ELT label.
For example, loaded order records can feed warehouse SQL models for both revenue reporting and operational analysis. Ingestion may still validate or remove prohibited fields before loading. ELT is therefore an architectural pattern rather than a promise to copy every source field unchanged or a replacement for data governance.
2. Why separate loaded source data from consumer-facing models?
The loaded representation preserves a source-oriented boundary, while consumer models express deliberate analytical meaning. Separating them lets several consumers use the same authorized input without forcing the ingestion layer to implement every reporting interpretation.
A payment feed might expose processor statuses, while a finance model maps them into recognized business categories. Keep the mapping explicit and versioned. Consumers should not depend casually on internal landing columns, because connector changes can otherwise become breaking changes across many reports.
3. What does a SQL model represent in an ELT project?
A SQL model defines a reusable transformation and the dataset it is intended to expose. Its identity includes its inputs, output columns, row meaning and materialization behavior, not just the text of one query.
A model named customer_activity could join several loaded sources and publish one row per customer. Other models should depend on that defined interface rather than duplicate its logic. A model is not automatically a physical table: the execution tool may create a view, store results or inline its query into another model.
4. What is a transformation dependency graph?
It is a directed graph showing which models require the outputs of other resources. It supports build ordering, impact analysis and selection of the work needed for a change.
If revenue_summary reads recognized_revenue, the latter is a dependency even when both files happen to be in the same folder. The graph must reflect actual reads; a manually hardcoded relation can hide a dependency from the tool. A correct graph describes ordering relationships, but does not by itself establish that upstream data is complete or current.
5. How do landing, staging and business-model layers differ?
Landing exposes the loaded source representation; staging typically gives one source a consistent typed and named interface; business models combine that interface into concepts useful to consumers. These are organizational roles, not mandatory product features.
For instance, staging can rename a processor field and cast an amount, while a business model applies the organization’s revenue policy. Keep source cleanup separate from cross-source interpretation when that makes ownership clearer. Avoid creating layers merely to satisfy a diagram when they add no meaningful boundary.
6. Why is an explicit column projection useful at a model boundary?
It makes the published schema deliberate and limits accidental propagation of new or sensitive source fields. Consumers can see which columns the model promises rather than inheriting every upstream change through a wildcard.
A source adding an internal token should not cause that token to appear in an analyst-facing table. Explicit projection also gives a controlled place for naming and type conversion. It does not replace permissions or detect all semantic changes, but it reduces the surface on which uncontrolled evolution can affect downstream models.
7. What is the difference between a relation name and a model name?
A model name identifies a resource in the transformation project; a relation name identifies the actual database object used for an execution environment. Database, schema, alias and quoting rules can make the two differ.
The same orders model might resolve to an isolated developer schema or a production schema. Avoid treating a file name as proof of where a command will write. Inspect the resolved relation and execution identity when reviewing a deployment or diagnosing an unexpected object.
8. What does dbt's ref function contribute beyond inserting a table name?
A ref call identifies a project resource, resolves its relation for the current context and records a dependency. It connects logical model composition with the environment-specific object that the query should read.
Using ref for a staging model lets the same downstream SQL run in development and production without hardcoded schema edits. A literal production table name may still produce valid SQL, but it can bypass the intended graph and isolation. The referenced resource must exist in the project’s declared dependency context.
dbt SQL with Jinja — a model that reads a declared staging model:
This assumes stg_orders exists in the project and publishes the four referenced columns. dbt resolves the relation before the database executes the SQL; the template is not a standalone SQL statement.
9. How does declaring a source differ from defining a model?
A source declaration describes data that is already supplied to the transformation project, while a model defines a transformation the project manages. The declaration can provide names, documentation and checks without claiming ownership of the ingestion process.
A dbt source call can resolve a loaded orders table and record its relationship to downstream models. It does not create the connector or prove that the table contains the latest delivery. Keep the ingestion owner and the transformation owner explicit so a source problem is not mistaken for a model defect.
10. What is a materialization?
A materialization is the strategy used to turn a model definition into an executable or persisted result. It determines when work happens, what database object exists and how later runs update that object.
A view defers much of the work to readers, while a table stores the output of a build. An incremental strategy maintains state between builds. Choose the strategy according to reuse, freshness and recomputation needs; changing it can alter operational behavior even when the model’s SELECT statement remains unchanged.
Strategy | Where repeated work occurs | State to manage |
|---|---|---|
View | When consumers query it | Definition and underlying input state |
Table | When the build refreshes it | Stored result and refresh lifecycle |
Incremental | During selected updates | Target state and recomputation coverage |
Ephemeral | Inside dependent compiled queries | No standalone output relation |
Native managed result | According to the engine’s refresh rules | Eligibility, refresh state and platform limits |
11. When is a view a useful model output?
A view is useful for a lightweight, reusable interface whose query should read the current state of its underlying relations. It can centralize naming and simple transformations without storing another copy of the result.
A staging view may be appropriate for straightforward source projection. However, several expensive views stacked together can move substantial work into every consumer query. A view is current relative to what it reads, which may itself be a stale table; it does not make the entire upstream graph fresh.
12. When is a table materialization useful?
A table materialization is useful when storing a computed result avoids repeatedly performing expensive work or provides a stable output between builds. Its freshness follows the build process, not changes to its source tables automatically.
A heavily reused business model may justify a scheduled rebuild so dashboards query a prepared result. Compare build cost, storage and acceptable staleness with the cost of repeated queries. A successful table build also needs the intended grants and validation before consumers rely on it.
13. What is an ephemeral model in dbt?
An ephemeral model is a reusable SQL definition that dbt incorporates into dependent queries rather than creating a standalone database relation. It can organize a small transformation without adding a persistent object.
The trade-off appears when many consumers expand the same logic: work can be repeated and compiled queries can become harder to inspect. It also cannot be queried independently like a table for ordinary debugging. Use it when that lack of persistence is intentional, not simply to minimize the number of visible warehouse objects.
14. How is an incremental model different from a table rebuilt on every run?
An incremental model preserves an existing target and applies a selected transformation result through a supported update strategy. A full table rebuild derives the target again from the complete defined input.
The incremental design must explain why its selected inputs are sufficient to maintain the intended output. A small source change can affect many derived rows, so lower input volume does not automatically mean a simple row append is correct. Keep the full-build definition understandable so initialization and recovery have a clear reference.
15. How does a managed materialized result differ from a scheduled table build?
A managed materialized result delegates some refresh behavior to the destination engine, while a scheduled table build is updated by the external transformation job. Their supported query shapes, refresh policies and consistency behavior can differ.
A warehouse-native dynamic table may maintain a declared query toward a freshness target. That does not make every query eligible for efficient incremental refresh. Evaluate the specific engine feature and inspect actual refresh behavior rather than treating all persisted query results as interchangeable.
16. What happens when a transformation project is compiled?
Compilation resolves templates, configuration and logical references into executable statements for the target platform. It makes generated SQL inspectable, but does not by itself prove that the statements will succeed or produce the intended data.
A macro can expand a concise source file into a large query with a different relation name than expected. Review that output when debugging. Some compilation workflows can query the database through macros, so compilation should not be assumed to be offline or free of side effects merely because models are not being materialized.
17. Why distinguish project parsing from model execution?
Parsing discovers resources and dependencies; execution applies the chosen commands to those resources. Logic that expects query results during graph discovery can fail because no result is available at that stage.
In dbt, the execute flag separates parse-time context from execution-time compilation, but it is also true during dbt compile. Guarding a database call only with that flag does not restrict it to a model build. Design macros to behave correctly in every command context in which they are used.
18. What role does configuration play in ELT behavior?
Configuration can determine materialization, target naming, incremental strategy, grants and other behavior outside the visible SELECT. It is part of the effective program and should be reviewed with the model code.
A project-level rule can make every model in a folder incremental even when an individual SQL file contains no such setting. Inspect the resolved configuration when behavior surprises you. Keep important defaults understandable and avoid relying on reviewers to reconstruct a long chain of conflicting overrides.
19. Why isolate development output schemas?
Isolation keeps one developer’s or CI job’s writes from replacing another environment’s models. It allows realistic transformation work while making the intended write destination explicit.
A dedicated schema per pull request is useful only if naming rules and permissions consistently enforce that separation. A hardcoded output relation or shared hook can still escape it. Verify the effective target and use a development identity with appropriate privileges rather than relying solely on a schema prefix convention.
20. How should a model document its output contract?
Document the meaning of one row, column definitions, expected relationships, intended consumers and the behavior of important edge cases. Include freshness and ownership information that explains how the result should be used.
For a customer-level model, say whether inactive customers remain and whether amounts are lifetime or period-specific. Column types alone cannot answer those questions. Keep documentation close to the managed model and validate the parts that can be tested so the contract remains more than an explanatory comment.
21. How does a schema contract differ from a data test?
A schema contract checks the shape and supported structural properties of a model interface; a data test checks assertions about the rows or values. They address different failure modes and often belong together.
A model can expose the promised integer identifier column while containing duplicate identifiers. Conversely, a uniqueness test can pass after a column is renamed in a way that breaks consumers. Check the materialization and adapter’s enforcement support before assuming that a declared constraint is enforced by the database.
22. What is a model-level unit test useful for?
A unit test supplies controlled inputs and expected outputs to exercise a transformation rule independently of the full production population. It helps expose logic errors before a model is materialized into its usual environment.
For a classification model, include a boundary value and a missing optional attribute rather than only typical rows. The fixture should make the intended rule easy to inspect. A unit test cannot establish production permissions, adapter DDL behavior or performance on realistic volumes, so those need separate evidence.
23. What should a data test return when checking for invalid model rows?
In the common SQL data-test pattern, the query returns rows that violate the assertion. An empty result indicates that no violations were found in the tested population under that query.
For a model publishing one row per account, a grouped query can return keys whose count exceeds one. The test’s scope and null treatment must match the contract. A zero-row result can be misleading if the model itself is unexpectedly empty, so combine focused assertions with a suitable population expectation.
SQL — a self-contained duplicate-key assertion fixture:
The fixture returns account 1 with a count of 2. A production assertion should query the managed model and define null-key handling separately; this example deliberately contains a violation.
24. Why should transformations be versioned separately from loaded data?
The same retained input can support different interpretations as business rules evolve. Versioning the transformation makes those interpretations identifiable without requiring the connector to encode every analytical change.
A customer segmentation rule can be revised while the loaded source record remains unchanged. To reproduce a particular output, record both the logic version and the input state it read. Versioning code alone does not freeze a mutable source relation or an external reference table.
25. What is a transformation manifest useful for?
A manifest records the project’s resources, configuration and dependency relationships in a machine-readable form. It can support documentation, impact analysis and comparison between project states.
In dbt, manifest.json describes more than the nodes selected in a particular run. Do not confuse that inventory with proof that every model was successfully built. Pair it with execution results and database evidence, and use the manifest schema version expected by the tool consuming it.
26. How do execution results differ from project metadata?
Execution results describe what happened in an invocation, while project metadata describes what the project defines. A model can be present in the graph without having run, or can fail despite compiling correctly.
When diagnosing a missing output, inspect selection, status and the resolved relation rather than searching only the manifest. Preserve the relationship between artifacts and their invocation. Combining a manifest from one commit with results from another can create an apparently coherent but inaccurate operational record.
27. Why should a model have a clear owner?
An owner can decide what the model means, which changes are acceptable and how consumer problems are resolved. Shared use makes this more important because a local-looking SQL edit can affect several teams.
A revenue model may need an engineering maintainer and a business definition owner with different responsibilities. Record those roles and the supported interface. Ownership should lead to a usable review and support path, not merely a name in a documentation field that nobody monitors.
28. What makes a transformation suitable for reuse?
It has a coherent purpose, a stable interface and semantics that genuinely match the needs of several consumers. Reuse is valuable when it prevents inconsistent business logic, not when it forces unrelated use cases through one oversized model.
A shared normalized currency code may be broadly reusable, while two reports can legitimately use different revenue recognition rules. Separate those meanings instead of hiding a choice behind a parameter nobody understands. Measure reuse by clarity and consistency, not only by the number of downstream references.
29. Why avoid using one enormous SQL model for the entire ELT workflow?
A single large model can obscure reusable boundaries, make diagnosis difficult and force all changes through one build unit. Smaller coherent models can expose intermediate contracts and let the graph describe meaningful dependencies.
Splitting every CTE into a persisted table is not automatically better; it can add storage, refresh and coordination overhead. Choose boundaries around ownership, reuse, testing or materialization needs. The goal is a graph that people can reason about, not the largest possible model count.
30. What is the difference between a model dependency and an ingestion-readiness signal?
A model dependency states that one transformation reads another resource. An ingestion-readiness signal states that the required source population is available under an agreed delivery contract.
A source table can exist and be queryable while its newest batch is incomplete. Building a perfectly ordered model graph over that table still yields incomplete output. Connect the transformation run to appropriate source readiness evidence rather than treating the existence of a declared source as proof that it is ready.
31. How should loaded semi-structured data become a stable analytical interface?
Create an explicit projection that extracts the fields, types and repeated structures required by the model’s consumers. Preserve enough source context to investigate unexpected shapes without exposing the entire payload indiscriminately.
An array of order lines may deserve its own child model rather than being flattened into a customer table. Decide what missing, null and empty values mean at that boundary. The destination’s ability to store a flexible payload does not remove the need for a predictable consumer contract.
32. Why is query-time interpretation sometimes useful in ELT?
It allows a new analytical question to use already loaded authorized data without changing the ingestion path first. A view or exploratory model can test an interpretation before the team commits to a persistent output.
This flexibility is useful for investigating a new product grouping. It becomes risky when undocumented exploratory logic quietly becomes a critical report dependency. Promote useful interpretations into managed models with ownership and tests when their consumers begin relying on them.
33. What is a model selection expression used for?
It specifies which project resources a command should act on, often using names, paths, tags or dependency relationships. Selection is part of the run’s behavior and should be inspectable before execution.
Selecting one changed model does not necessarily rebuild all consumers affected by that change. Conversely, an expanded selector can include much more work than its short text suggests. Preview the resolved set when the scope matters, especially for a costly rebuild or an isolated CI job.
34. Why can a transformation succeed while a consumer still sees old data?
The job may have built a different relation, updated only an intermediate layer or left a downstream stored model unchanged. Successful execution and consumer-visible freshness are separate observations.
Check the complete path from the built model to the object the consumer queries, including aliases and environment bindings. A new staging table does not refresh an independently materialized summary automatically unless the workflow includes that step. Diagnose the dependency boundary before rerunning the whole project.
35. What does full refresh mean for an incremental model?
It means rebuilding the model’s target from its full-build definition rather than applying the normal incremental path. The command and configuration behavior depend on the transformation tool and adapter.
A full refresh can apply revised logic to old rows, but only if the required historical input is still available. It can also be expensive and affect downstream models. Treat it as a planned state transition with an explicit scope, not a universal repair button for every incremental defect.
36. Why should a transformation job use its own database identity?
A dedicated identity makes the job’s read and write permissions explicit and separates automated behavior from a developer’s broader interactive access. It also gives operational ownership a stable basis.
A model that works in an administrator’s worksheet may fail under the scheduled role. Test the actual job identity’s ability to read sources, create outputs and apply required grants. Least privilege must account for the materialization’s DDL operations, not just permission to run the model’s SELECT.
37. How do source freshness and model freshness differ?
Source freshness concerns how current the loaded input is; model freshness concerns the state represented in a transformed output. Either boundary can lag independently.
A source can be current while a stored business model has not rebuilt, or a model can rebuild promptly over a delayed source. Track both when interpreting a consumer-facing result. A recent model completion timestamp is not evidence that the business events it contains are recent.
38. What should a small ELT development workflow include?
It should resolve the intended environment, inspect the relevant dependencies, compile or validate the model, build the necessary scope and check the resulting contract. Keep the work isolated and retain enough evidence to explain the result.
For a staging change, examine its downstream consumers before deciding that a local test is sufficient. Include a realistic integration check for adapter-specific behavior. A short workflow can be reliable when its boundaries are clear; it does not need every production model to run after every edit.
39. How should an analyst consume a managed model?
Use its documented interface and understand its row meaning, freshness and supported changes. Prefer stable published models over internal intermediate relations whose structure may change without a consumer compatibility promise.
If a required field is missing, discuss whether it belongs in the shared interface or a separate model rather than copying private transformation SQL into a dashboard. This keeps ownership and impact visible. Managed models improve consistency only when consumers and maintainers respect the same boundary.
40. What makes an ELT design understandable in an interview?
A clear design identifies the loaded source boundary, the transformation graph, the materialization choices and the consumer interfaces. It explains where state is stored and what must be rebuilt when a rule changes.
A candidate should walk through a specific change, such as adding a new classification, and show which models and checks are affected. Naming many tools is less useful than explaining those consequences. The strongest answer also states which behavior comes from the destination engine and which comes from the transformation framework.
ELT Interview Questions and Answers for Intermediate
Strong answers identify the exact boundary that must be checked and avoid treating configuration as a guarantee.
41. How can a hardcoded relation create an invisible dependency?
The SQL engine can read the relation successfully while the transformation tool has no declared edge connecting the reader to the producing model. Build order, impact analysis and environment substitution can then disagree with the query’s actual behavior.
Replace the literal with the appropriate project reference when the relation is managed by that project. If it is external, declare that boundary explicitly. Verify the resulting graph and compiled relation name; simply renaming the SQL file does not repair the hidden relationship.
42. What happens when a dbt ref is hidden inside an execution-only branch?
The parser may not discover the dependency because the branch is not evaluated during graph construction. Later compilation can encounter a reference that was absent from the parsed graph.
Declare the dependency in a parse-visible way, such as the documented SQL dependency comment containing the ref, while keeping database-result logic appropriately guarded. A Jinja comment is removed and does not serve the same purpose. The declaration should match a real dependency rather than adding arbitrary edges to silence an error.
43. How do you review generated SQL from a macro-heavy model?
Compare the generated statement with the intended input relations, projections, predicates and output contract. Trace surprising fragments back to the macro and configuration that produced them instead of reviewing only the compact source template.
A loop may generate dozens of conditional aggregates or quote an identifier incorrectly for the adapter. Check representative parameter combinations and keep generated artifacts associated with the commit and environment. A readable template is not evidence that every expansion is syntactically or semantically correct.
44. When should a shared transformation become a persisted intermediate model?
Persist it when reuse, debugging, validation or execution cost justifies a durable boundary. The choice should account for how often the intermediate changes and how many consumers would otherwise repeat its work.
An expensive normalization used by several marts may be a good candidate, while a trivial rename usually is not. Persisting adds freshness and lifecycle obligations, so include the intermediate in the build graph and operational ownership. Avoid judging the decision only by one consumer’s isolated query time.
45. How can an ephemeral model increase downstream work?
Its SQL is incorporated into dependent statements, so several consumers can each execute the transformation rather than share one stored result. Complex nested ephemeral models can also expand the size and complexity of compiled SQL.
Inspect representative compiled queries and their plans before deciding whether to persist a boundary. The optimizer may eliminate or reorganize some work, so repeated text does not prove identical physical execution. Measure the actual workload and retain the logical interface when changing the materialization.
46. What must be true for dbt's is_incremental branch to run?
The model must be configured as incremental, its target must already exist as a table and the invocation must not take the full-refresh path. The predicate describes the current build context, not whether the source contains new rows.
Both branches of the model must produce valid SQL. A fresh CI schema usually exercises initial creation, which can miss errors in the incremental branch. Test an existing target as well as first creation, and inspect the effective configuration rather than assuming the file’s name determines the path.
Context | is_incremental result | Required check |
|---|---|---|
Target does not exist as a table | False | Initial creation SQL and complete input definition |
Existing table, incremental config, no full refresh | True | Update selection and target strategy |
Full-refresh path | False | Reconstruction logic and historical input availability |
47. Why is an incremental model's unique key not a uniqueness guarantee?
The configured key tells a supported incremental strategy how to match candidate rows with the target. It does not automatically prove that the model emits one non-null row per key or that the warehouse enforces a unique constraint.
Validate the key contract separately and understand the selected strategy’s behavior when duplicates occur. A merge strategy and an append strategy can use configuration differently. Review the adapter’s generated statements rather than assuming that adding a unique_key setting repairs an ambiguous model.
48. How do source filters and target match predicates differ in incremental builds?
A source filter decides which rows are recomputed; a target match predicate limits which existing rows participate in the update operation. Reducing either set can improve performance, but each changes a different part of the correctness contract.
If an old target row is excluded from matching while a corrected source row with the same key is included, the operation may treat it as new. Prove that the target restriction covers every possible match for the selected source population. A recent-date predicate is not safe merely because most activity is recent.
SQL — demonstrate an unsafe target-match restriction:
The existing key is real, but the additional target predicate hides it: matched_target is null. A merge that inserts unmatched rows can therefore create another key 1, depending on the engine and constraints. This fixture demonstrates the match logic, not a portable MERGE implementation.
49. Why can a lookup change invalidate an incremental fact model?
A derived row can depend on both its main source record and a lookup or classification model. If the incremental filter watches only the main record, a changed lookup can leave existing derived values inconsistent with a full rebuild.
For example, reclassifying a product can affect every retained transaction using that product even when no transaction changed. Define an invalidation strategy for those dependencies, such as recomputing affected keys or a broader rebuild. The relevant change set follows the transformation’s dependencies, not only its largest input table.
50. How should a newly added derived column be populated in an incremental model?
Decide how historical rows will receive the value in addition to changing the output schema. Adding a column through schema-change configuration does not necessarily recompute existing rows.
A new customer category may remain null for old records until they are selected again. Choose an explicit historical rebuild or backfill that uses the intended rule and input state. Validate both new and retained rows after deployment; seeing the column in the catalog proves only that the schema changed.
51. When can an incremental model need a full rebuild after a SQL change?
A full rebuild may be necessary when the new logic changes outputs for rows outside the normal incremental selection. Examples include a changed classification, a revised join or a calculation whose dependency spans historical records.
Estimate the affected population from the rule change instead of defaulting to either “always rebuild” or “never rebuild.” If a bounded recomputation is valid, document why it covers all affected rows. Also inspect downstream stored models, which may retain results derived from the old interpretation.
52. How do you assess whether a window calculation is incrementally maintainable?
Identify the full set of rows that can influence each output and determine how a new or changed input alters that set. A ranking or cumulative calculation often depends on more than the newly selected rows.
Computing a customer’s lifetime rank only over today’s records produces a different meaning from ranking their complete history. The incremental algorithm may need retained context or recomputation of the affected partition. Test it against the full query on deliberately chosen changes, not just on an initial append-only sample.
SQL — compare full-history and filtered-input ranking:
Event 11 has lifetime position 2 but filtered-input position 1. The filter changes the window’s input, so appending the latter result would not preserve the full model’s meaning.
53. How do you test incremental behavior against a full-build reference?
Start from a controlled initial input, build the target, apply a deliberate input change and run the incremental path. Compare the resulting target with the full model evaluated over the same final input state.
Include changes to secondary dependencies and historical values where the model promises to support them. Compare identities and derived columns rather than only counts. This differential approach can expose incomplete recomputation, but its conclusion remains limited to the tested cases and the production engine semantics represented by the test.
54. What should you inspect before changing a model from view to table?
Inspect freshness expectations, downstream reads, grants, build ordering and the operations the adapter uses to replace the relation. The logical columns may remain the same while consumers begin reading a stored snapshot rather than the current underlying data.
Confirm how the table will be refreshed and whether existing permissions survive the transition. Test the actual materialization change in an appropriate environment. A configuration-only diff can have more operational impact than a large SQL formatting change.
55. How can model aliases and custom schema rules undermine isolation?
They can direct a logically separate model to an unexpected or shared physical relation. If development naming discards the user-specific schema component, several developers may overwrite the same output.
Inspect the resolved database, schema and identifier under representative targets. A readable alias is useful, but it must remain unique within the intended namespace. Test hooks and custom materializations too, since they may construct relation names outside the standard naming functions.
56. Why should environment-specific row limits be used carefully?
They can make development faster while changing the relationships and distributions that the model is supposed to preserve. Limiting one input independently can produce unmatched references or hide high-volume and historical cases.
A sampled order table combined with a full customer table may be useful for one test but not for validating all relationship assumptions. Label the scope and keep deterministic fixtures for the intended logic. Do not treat a passing reduced-data build as proof that the production population or incremental branch is correct.
57. How does adapter dispatch affect a reusable macro?
Dispatch chooses the implementation appropriate to the adapter and configured namespace search order. It allows one logical macro interface to generate platform-specific SQL.
The same macro call can therefore compile differently after an adapter or package change. Review the selected implementation and test its null, type and quoting behavior on the target engine. A default implementation is a fallback, not proof that every supported database has identical semantics.
58. How do you keep generated SQL parameters safe and understandable?
Distinguish values, identifiers and SQL fragments, and define which forms a macro accepts. Quote or validate them using the appropriate adapter mechanisms instead of concatenating arbitrary user-controlled text into a statement.
A parameter selecting one of several approved measure columns can be checked against an allowlist. A parameter containing an unrestricted predicate is a much broader interface. Document those boundaries and review the compiled result; templating is code generation, not automatic database parameter binding.
59. Why can querying warehouse metadata during compilation be risky?
It makes compilation depend on a live database state and the caller’s permissions, and may add cost or side effects through macro logic. The same commit can compile differently if columns or reference values change between invocations.
Prefer explicit project inputs when a stable interface is required. If metadata-driven generation is necessary, define the expected state and retain the generated artifact for review. In dbt, execute being true does not mean the command is building models, so compilation-time queries require deliberate handling.
60. How do you handle a model contract that passes while numeric precision is wrong?
Inspect the actual declared and created type, including precision and scale, and test values near the business boundaries. A schema contract’s type comparison may be less granular than the domain requires.
The documented dbt contract comparison does not compare all size, precision and scale details. Explicitly declare the intended numeric type and add a focused assertion or integration check. A column described broadly as numeric can still round or reject values in a way that violates the consumer’s expectation.
61. What are the limits of applying a model contract across materializations?
Contract support depends on the resource type, materialization and platform. The declared syntax does not mean every constraint is supported or enforced everywhere.
The documented dbt SQL-model contract feature supports specified table, view and incremental cases, with views lacking constraint support and incremental models requiring compatible schema-change settings. Ephemeral and materialized-view models are not covered by that feature. Check the actual combination before standardizing a project-wide contract rule.
62. How can an indirect test selection include an unbuilt model?
A test can depend on several models even when only one was selected for the build. An inclusive indirect-selection mode may select that test because one parent is present, leaving another relation unavailable or stale.
Preview the selected tests and their dependencies when building a subset. In dbt, eager, cautious and buildable modes make different choices about those parents. Choose the mode that matches the environment’s available relations, rather than suppressing a missing-relation failure without understanding which assertion was intended to run.
63. How do ancestor and descendant selectors support different tasks?
Ancestor expansion includes the inputs needed to build a resource, while descendant expansion includes consumers potentially affected by a change. They answer different questions about the graph.
For a changed staging model, downstream expansion can identify affected marts, but those marts may also depend on other branches. Preview the full buildable set or provide a deliberate deferral strategy. A selection expression that captures impact is not always sufficient to build that impact set in an empty schema.
Shell commands — preview dbt model selections before a build:
These commands assume the named models exist in a configured dbt project. The first includes ancestors, the second descendants, and the third the selected resource’s descendants plus the ancestors needed by those descendants. Listing resources does not build them.
64. Why should a CI job preview its resolved model selection?
The preview makes hidden scope changes visible before the job creates or replaces relations. Tags, path changes, macro dependencies and graph expansion can make the selected set differ from a reviewer’s intuition.
Store or inspect the resolved resource list for a representative change, including tests. An empty selection can indicate a broken comparison baseline rather than “nothing to test.” A very large selection may be correct after a shared macro change, but it deserves an explicit cost and coverage decision.
65. How do state comparison and deferral solve different CI problems?
State comparison identifies logical resources that differ from a saved project state; deferral chooses where eligible unselected references resolve. One controls selection, while the other supplies an environment binding for dependencies not built locally.
A job can use state comparison without reading production relations, or defer references without selecting only modified models. Keep those decisions explicit. In dbt, separate state and defer-state manifests can be useful when the logical comparison baseline differs from the applied environment being read.
Mechanism | Question it answers | What it does not establish |
|---|---|---|
State comparison | Which logical resources differ from the baseline? | Whether every data-impacting external change is detected |
Deferral | Where should an eligible unselected reference resolve? | That all referenced populations are temporally consistent |
Cloning | What target state is available locally for testing? | That first-build and production permission paths are covered |
66. When does dbt defer an unselected model reference?
With deferral enabled and a state manifest supplied, dbt can resolve an unselected resource to the state relation when the local relation does not exist. Favor-state changes that preference for eligible unselected resources; selected resources still use their selected build context.
A leftover local table can therefore alter the default binding between two otherwise similar CI runs. Inspect resolved references when results differ. Ephemeral models are not deferred as standalone relations because they are expanded into dependent SQL.
67. Why can a relationship test behave unexpectedly with deferral?
Its parents may resolve to different environments, so the test compares populations that were never intended to represent one consistent dataset. A limited development model joined to a full production parent can yield misleading success or failure.
Decide whether the test should use coherent cloned inputs, a broader local build or a deliberately scoped fixture. Do not simply disable the test because it fails in CI. The failure may reveal an environment-binding problem rather than a defect in the model’s business logic.
68. Why keep the comparison manifest outside the current output directory?
The current invocation can overwrite its generated manifest during parsing. If the saved baseline shares that location, the job may lose the state it intended to compare against.
Store an immutable baseline in a separate path and write current artifacts elsewhere. Record the baseline’s provenance and verify it exists before selection. A path named target is not an archival strategy; the job should distinguish previous applied evidence from artifacts it is about to generate.
69. What does cloning an incremental target add to CI coverage?
A real table clone can create the pre-existing target state needed to exercise the incremental path instead of initial creation. It can also make a representative test cheaper than rebuilding the entire historical model.
Check adapter support and permissions: dbt clone can use pointer views where zero-copy cloning is unavailable, which does not provide the same table-state behavior. Test first creation separately when it matters. A clone-based run is useful evidence for a branch, not proof of every production concurrency or permission condition.
70. How do you review a package upgrade in an ELT project?
Identify which macros, materializations or tests change and inspect their effect on the project’s compiled SQL and selected resources. The package version is part of the transformation behavior even when model files are unchanged.
Use a controlled dependency update and representative fixtures on the target adapter. Retain the resolved package set with the build evidence. Treat generated SQL changes as a real release surface, especially when an upgrade alters relation naming, incremental operations or constraint handling.
71. What is the difference between declaring lineage and observing executed reads?
Declared lineage describes the dependencies the project knows about; observed reads describe what an execution actually accessed. They can diverge when macros construct names dynamically or external procedures read additional relations.
Use declared metadata for ordinary graph management and execution evidence to investigate important gaps. A warehouse query history can show a read absent from the model graph, but it may not recover every business-level relationship. Reconcile the two where the release or access risk justifies it.
72. How do warehouse-native tasks differ from model definitions?
A task specifies when and under which execution context work runs, while a model defines a transformation or output. A task can execute SQL or procedures, but it does not automatically provide a model contract or complete dependency metadata.
In Snowflake, tasks can be scheduled or triggered and arranged into graphs. Keep their scheduling and privilege configuration versioned with the transformation they execute. A correctly created task still needs appropriate testing and activation before it becomes a functioning refresh workflow.
73. What does a Snowflake dynamic table's target lag mean?
It is a target for staleness relative to the relevant upstream data, not a fixed refresh interval. Snowflake schedules refresh work toward that target, but actual lag can exceed it when the workload cannot keep up.
A ten-minute target therefore does not mean “run exactly every ten minutes” or guarantee every query sees data under ten minutes old. Monitor actual refresh and lag evidence. Choose the target with the graph’s dependencies and workload in mind rather than copying the same value onto every node.
Snowflake SQL — illustrative dynamic-table definition:
This requires an existing authorized landing.orders relation, destination schema and warehouse, appropriate privileges and support for the declared refresh mode. It creates a managed object and can initiate refresh work; use an isolated approved environment when trying it. The target lag remains a best-effort staleness target.
74. How does Snowflake's ADAPTIVE refresh differ from a permanently full refresh?
ADAPTIVE normally uses incremental refresh but can reinitialize when the engine estimates that incremental work would be substantially more expensive. A FULL refresh re-evaluates the complete definition as its refresh strategy.
The current ADAPTIVE feature still requires an incrementalizable query and is not a way to make an unsupported definition incremental. Inspect refresh history to understand when reinitialization occurred. Budget for those larger operations rather than assuming an incremental-looking workload never rebuilds.
75. Why is AUTO refresh not the same as ADAPTIVE refresh?
AUTO resolves to INCREMENTAL or FULL once at creation; it does not resolve to ADAPTIVE or reconsider the choice on every refresh. ADAPTIVE explicitly provides incremental operation with possible reinitialization. These are different contracts, not interchangeable names for ongoing automatic optimization.
Inspect the resolved mode and reason for a created dynamic table, and check current transition rules before changing it. A definition or account feature can affect eligibility. Document the selected behavior in the deployment evidence rather than inferring it from the SQL’s default setting.
76. How do you choose between native refresh and an externally scheduled transformation?
Compare query eligibility, refresh semantics, required procedural work, test gates, observability and operational ownership. Native refresh can reduce scheduling work, while an external job may provide more explicit control over multi-step validation and release.
A declarative summary can suit a dynamic table, whereas a workflow involving custom publication checks may need another boundary. Prototype the difficult behavior, such as a definition change or a large recomputation. The choice should follow the required guarantees rather than a preference for fewer scheduler boxes.
77. How do you troubleshoot a model that reads the wrong environment?
Inspect the compiled relation names and the effective target, source configuration, aliases and deferral state. Then compare them with the actual objects and permissions used by the invocation.
A stale development relation can prevent default deferral, while a hardcoded source database can bypass target isolation entirely. Correct the narrow binding rule and rerun the relevant checks. Rebuilding more models without explaining the binding can hide the problem temporarily and leave the next developer exposed to it.
78. How should a model expose rejected type conversions?
Make the distinction between a genuine source null and a failed conversion observable at the typed boundary. Preserve an appropriate error indicator or controlled exception path instead of silently turning every failure into an ordinary missing value.
A warehouse-safe cast can keep a query running, but a growing failure rate may mean the source contract changed. Publish only the intended consumer representation and retain diagnostic evidence under the correct access policy. The goal is a stable interface with visible quality consequences, not merely a query that never errors.
79. How do you prevent an exploratory model from becoming an unsupported production dependency?
Give exploratory outputs a clear namespace and lifecycle, and define a promotion process when consumers begin relying on them. Promotion should establish ownership, tests, documentation and an appropriate materialization.
A dashboard author may discover a convenient development table and start using it without telling its creator. Monitor or review important consumers and provide stable published alternatives. Deleting an apparently temporary object without checking dependencies can be as disruptive as leaving every experiment indefinitely.
80. What should an ELT pull request explain besides its SQL diff?
It should explain the changed model contract, affected graph scope, materialization or state consequences, validation evidence and required historical recomputation. Include environment and adapter assumptions where they affect behavior.
A new predicate may be a small text change but alter every downstream total or require an incremental rebuild. State that explicitly and identify consumer compatibility concerns. Reviewers should be able to judge the resulting data behavior without reconstructing the author’s entire development session.
ELT Interview Questions and Answers for Experienced
Evaluate how the candidate supports a claim with evidence across code, target state and consumer behavior.
81. How would you determine the rebuild impact of a shared model change?
Start with the declared descendants, then classify how each consumes the changed output and whether it persists previously derived values. The graph identifies potential impact; the transformation semantics determine which historical rows need recomputation.
A renamed display column may require interface changes, while a revised classification can invalidate retained aggregates. Include consumers outside the managed project through documented dependencies or observed usage. Do not equate “selected downstream” with “fully repaired”: an incremental descendant may still process only its usual narrow input set.
Python 3 — compute a conservative descendant set:
This fixture computes reachability, including the changed resource. It does not decide how much history an incremental descendant must recompute or discover undeclared external consumers. Those require semantic analysis and additional dependency evidence.
82. How can a full refresh of an upstream model leave downstream data inconsistent?
A downstream stored model can retain values computed from the previous upstream interpretation. Rebuilding the upstream relation does not automatically rewrite every dependent target, especially when those targets use incremental filtering.
A revised customer classification may update the full dimension while an incremental sales model keeps old copied categories. Plan the descendant recomputation scope explicitly and compare the final graph with the intended full-build meaning. The release is about compatible data states across interfaces, not merely successful execution of the first changed node.
83. How do you reason about an incremental transformation with nonlocal dependencies?
Describe the dependency set of each output and how input changes propagate through it. When a result depends on an entire group, rank or historical interval, the affected output set can be much larger than the changed input rows.
A rolling calculation may require earlier context and later recomputation around a changed record. Build an invalidation rule from those dependencies and test it against the full query. If the affected scope cannot be bounded reliably, a broader rebuild may be the more defensible implementation despite its cost.
84. How would you design CI evidence for both model logic and materialization behavior?
Use controlled fixtures for the SQL rule and representative warehouse tests for relation creation, schema changes, grants and incremental state. Keep the evidence separate so a passing logic test is not mistaken for adapter-level validation.
For an incremental model, exercise initial creation, an existing target and a changed schema under the actual adapter. Inspect generated statements and final relations as well as exit status. The test environment should have permissions similar enough to production to reveal the operations the materialization actually requires.
85. How would you validate a state-based CI job itself?
Test the selection policy with deliberate project changes: a new model, a shared macro edit, a changed dependency and an altered configuration. Check that the resolved build and test sets match independently reasoned expectations.
Also test the missing or incompatible baseline case and ensure it fails visibly rather than declaring an empty success. State comparison is infrastructure whose mistakes can remove coverage from many future changes. Retain its versioned policy and representative selection fixtures alongside the transformation project.
86. Why is state:modified not a complete detector of data-impacting change?
It compares supported project metadata, not every external fact that can alter output. Loaded data, external reference values and some variable-driven behavior can change without a corresponding detectable model-file change.
The documented dbt caveats also distinguish changes to macros, metadata and seeds, so the selector’s exact semantics matter. Combine logical state selection with an explicit release policy for external dependencies. A model omitted from a modified set is not proven unaffected under every possible environment change.
87. How would you secure and validate a manifest used for deferral?
Treat it as an artifact that influences which relations the job reads, with provenance tied to an approved project and applied environment. Validate its source, expected project identity, schema compatibility and relation namespaces before using it.
A manifest from an untrusted branch could point dependencies at unintended objects even if the current model SQL looks harmless. Restrict the job identity’s access as an additional boundary. Artifact integrity and database permissions address different risks; neither should be used as a substitute for the other.
88. How do logical state and applied state diverge during deployment?
Logical state describes the project definition, while applied state describes what has actually been built successfully in the destination. A partially failed release can leave the database containing a mixture of old and new relations.
Comparing against the new commit while deferring to relations that never built can produce incorrect assumptions. Preserve an applied-state record supported by execution and database evidence, and choose comparison and deferral baselines deliberately. Do not promote a manifest merely because parsing completed.
89. How would you make a selective release compatible with unchanged consumers?
Identify the published interfaces and ensure the changed models remain compatible with the consumers that will not be rebuilt in the release. If compatibility cannot be maintained, introduce a versioned interface or coordinate the necessary consumer transition.
A renamed column can break a view immediately, while a semantic change may silently alter a downstream calculation. Validate both structural and meaning-level expectations. Selective deployment saves work only when the boundary between changed and unchanged resources is deliberately supported.
90. What are the trade-offs of version-pinning a model reference?
Pinning makes a consumer’s dependency on a particular interface explicit and prevents an automatic move to a newer version. It also creates an obligation to maintain, migrate and eventually retire the older version.
In dbt, a version argument on ref selects that declared model version, while an unversioned reference follows the configured latest version. Neither policy is universally best. Use pinned references for compatibility-sensitive consumers and maintain a visible migration plan so versioning does not become permanent duplication without ownership.
91. How do you retire a versioned model interface without relying only on the project graph?
Combine declared references with external usage evidence and the consumer ownership record. Reports, notebooks or applications may query the physical relation directly and therefore be absent from the transformation graph.
Announce the replacement and deprecation boundary, help consumers migrate and verify the relevant usage window. An infrequent month-end consumer may not appear in a short query-history sample. Preserve required evidence and use the agreed retirement process rather than treating zero declared children as proof that removal is safe.
92. How should model access rules relate to database permissions?
Project-level access rules define supported references and collaboration boundaries; database permissions determine what an execution identity can actually read or change. They are complementary controls with different enforcement surfaces.
A model marked private within a framework may still be queryable by a broadly privileged database role. Conversely, a valid public project reference can fail because the job lacks database access. Review both when publishing an interface across teams, and test the consumer’s real identity rather than only the author’s local build.
93. How do you avoid mixed-version results during a multi-model release?
Define the consumer-visible compatibility boundary and plan how the related outputs become available within the destination’s actual guarantees. Sequential model builds do not automatically form one transaction across the whole graph.
Compatible staged changes can allow old and new consumers to coexist; a versioned publication layer may be needed for tightly coupled changes. Test the visibility between individual builds, not only the final state. Do not claim graph-wide atomicity because each model replacement is atomic on its own.
Release approach | Useful property | Compatibility concern |
|---|---|---|
Compatible staged change | Old and new consumers can coexist | Must preserve the old interface during transition |
Versioned consumer interface | Consumers choose an explicit published version | Requires migration and retirement ownership |
Sequential in-place builds | Simple operational sequence | Intermediate graph states may be visible |
94. Why can rolling back SQL code fail to restore an incremental model?
The target retains data and schema state created by earlier runs, so reverting the source file does not reverse those durable changes. The old code may also be unable to interpret a target altered by the new materialization.
Plan whether recovery requires a compatible forward fix, a restored target or a controlled rebuild from retained inputs. Include downstream stored outputs in that plan. A Git rollback is a code operation; data recovery needs its own evidence and a supported state transition.
95. How would you validate that a full rebuild is actually feasible?
Check input retention, access, reference-data availability, execution cost and the behavior of the full-build branch. An incremental model can operate successfully for months while its original reconstruction path becomes impractical or incomplete.
Run a representative rebuild exercise before relying on it as the only recovery plan. If historical source records have been removed, document what can and cannot be reconstructed. The word “full refresh” does not restore unavailable inputs or earlier business definitions automatically.
96. How do you evaluate a custom materialization before adopting it?
Review the complete lifecycle: initialization, replacement, incremental updates, cleanup, grants, transaction boundaries and failure behavior. Its contract should be explicit enough that model authors understand what their configuration causes.
Test adapter-specific DDL and failures between intermediate operations in an isolated environment. A custom materialization that works for one happy-path model can fail on existing views, schema changes or restricted roles. Keep ownership and upgrade compatibility clear, since it becomes shared infrastructure for every model using it.
97. How can pre-hooks and post-hooks escape a model's apparent boundary?
They can execute additional statements outside the visible SELECT and may have different transaction behavior depending on the adapter. A model that appears to create one table can also modify grants, audit tables or unrelated relations through hooks.
Review hooks as executable release code and inspect their resolved targets. Test what happens when the model fails after a pre-hook or when a post-hook fails after the model is available. Do not assume one framework command provides a single rollback boundary for all of those operations.
98. How would you diagnose a transformation graph that became expensive after adding one shared model?
Trace how the new boundary changes repeated computation, persistence and refresh frequency across its consumers. An ephemeral definition may be expanded many times, while a persisted table can introduce expensive rebuilds even for consumers that rarely need new results.
Compare representative execution evidence before and after the change, including compiled queries and actual model invocation counts. The issue may be the graph’s work-sharing design rather than warehouse size. Choose a targeted materialization or scheduling adjustment and verify that freshness and interfaces remain correct.
99. How would you set a refresh budget across a dependency graph?
Identify which consumer outputs need which freshness and how intermediate refreshes contribute to those outcomes. Allocate work according to those dependencies rather than assigning the same aggressive cadence to every model.
A slowly changing reference model may not need to rebuild whenever a high-frequency event summary does. Conversely, a critical upstream delay can make several fast downstream jobs useless. Evaluate total recomputation and the actual consumer-visible age, keeping native refresh targets distinct from guaranteed delivery times.
100. What consistency boundary does a Snowflake dynamic-table pipeline provide?
Within a pipeline refresh, Snowflake coordinates inputs at a data timestamp and publishes each refresh atomically. This supports a consistent transformation result rather than exposing a partially refreshed upstream relation.
That refresh guarantee should not be expanded into a claim that every independent ad hoc query across several tables sees one pipeline-wide snapshot. Check the documented boundary when a consumer joins outputs with different refresh histories. Native refresh consistency and consumer query consistency are related but separate questions.
101. How can a refresh boundary trade consistency for independent scheduling?
A boundary can separate refresh coordination so parts of a graph operate with more scheduling independence. The trade-off is that downstream work may consume an upstream committed state without sharing the same coordinated refresh timestamp across the former boundary.
Use such a feature only after identifying which temporal relationships the consumer requires. An independent slow branch may improve operational flexibility, but a report that assumes aligned inputs may need additional controls. Verify the specific platform’s boundary semantics instead of inferring them from a diagram of dependencies.
102. How would you investigate an ADAPTIVE dynamic table that reinitialized unexpectedly?
Inspect refresh history for the action and reason, then relate it to upstream change volume and the query’s eligible operators. ADAPTIVE is allowed to choose reinitialization when its heuristics judge incremental maintenance more expensive.
A bulk replacement upstream can explain a large rebuild even when the definition did not change. Compare the measured cost and completion behavior with the intended workload rather than assuming the feature malfunctioned. Preserve the distinction between an expected adaptive decision and a refresh failure requiring recovery.
103. Why can a native incremental refresh become ineligible after an upstream change?
Eligibility depends on the effective query and its dependencies, not only the dynamic table’s original text. An upstream view change can introduce a construct that the selected refresh mode does not support.
For Snowflake AUTO, the resolved mode is chosen at creation rather than reconsidered automatically on every refresh. A later incompatibility can therefore cause failures instead of a silent switch to full refresh. Include dependency changes in compatibility testing and inspect the current documented mode-transition procedure before changing the object.
104. How do you evaluate a full-refresh node upstream of a native incremental node?
Check the engine’s supported composition rules and the information it needs to track meaningful changes across that boundary. A logically valid SQL dependency is not proof that every combination of managed refresh modes is allowed.
Current Snowflake documentation restricts an INCREMENTAL or ADAPTIVE node downstream of a FULL node unless the upstream provides a system-derived unique key or a frozen region. Validate the actual definition and feature support. Do not generalize that condition to other warehouses or replace it with a blanket claim that the combination always works or never works.
105. How do scheduled-task privileges affect reproducibility?
The task’s execution identity and session context influence which objects and functions the transformation can use. An interactive run under a developer’s role can succeed while the scheduled task fails or sees a different authorized population.
Record the relevant role and configuration in deployment evidence and test through the intended task execution path. In Snowflake, task security and compute privileges depend on the chosen task configuration. Avoid assuming that the person who resumes a task supplies all of the privileges used by its body.
106. How would you combine native refresh with explicit data-quality gates?
Separate the maintenance of a candidate result from the decision that a consumer may rely on it when the use case needs a gate. Determine whether the native feature offers the required validation behavior or whether a controlled publication layer must provide it.
A continuously refreshed table can be structurally current while violating a business invariant. A downstream approved snapshot or versioned consumer interface may be appropriate for stricter reporting. Make the resulting freshness trade-off visible instead of treating a successful refresh as a complete quality approval.
107. What does a reproducible compiled artifact need besides a commit hash?
It needs the effective configuration, dependency versions, adapter and engine context, and any inputs used during code generation. If compilation consults live metadata or reference values, those inputs also affect the generated statements.
Store enough provenance to explain why two runs of the same source produced different SQL. Avoid recording secrets while retaining non-sensitive configuration identity. A commit hash is useful, but it cannot explain changes caused by a package update or a different destination namespace on its own.
108. How would you migrate a macro implementation between adapters?
Define the macro’s semantic contract and test each adapter implementation against that contract using representative inputs. Syntax equivalence is insufficient when types, null behavior, quoting or date operations differ.
Compile the models that use the macro and inspect whether the dispatch path is the expected one. Keep engine-specific differences explicit when a portable implementation cannot preserve the same meaning. The goal is an equivalent published model interface, not identical generated SQL text on every database.
109. How do you prevent model configuration from becoming an unreviewable policy layer?
Keep defaults limited, document precedence and make the resolved configuration visible for important resources. Use explicit local settings when a consequential exception would otherwise be hidden in a broad folder rule.
A project-wide materialization change can alter hundreds of targets without touching their model files. Review the affected resource set and deployment implications as part of that configuration diff. Centralization is useful when it reduces repetition, but harmful when it makes effective behavior difficult to discover.
110. How would you detect stale documentation generated from the wrong artifacts?
Compare the documentation’s project and invocation provenance with the deployed model definitions and relevant catalog state. A successful documentation build can still combine metadata from a different commit or environment.
Check representative relation names, columns and ownership after a release. Keep generation inputs traceable and avoid overwriting production documentation with a developer’s partial run. Documentation is a consumer interface too; it should describe the supported applied system rather than merely the last artifact somebody generated.
111. How do you assess whether a model boundary is too broad for independent ownership?
Look for unrelated consumers, conflicting change cadences and frequent exceptions that require several teams to approve every modification. Those signals can indicate that one model combines multiple contracts that should evolve separately.
Split only along meaningful semantic or operational boundaries, with explicit shared dependencies where needed. Creating separate copies of the same business rule can replace coordination problems with inconsistency. Evaluate the resulting graph and ownership process together rather than treating file size as the sole design criterion.
112. How would you support two legitimate interpretations of the same loaded data?
Publish separate, clearly named models or versioned interfaces with documented semantics and owners. Keep the shared source representation and common normalization reusable without pretending the interpretations are interchangeable.
A finance-approved revenue view and an operational bookings view can both be correct for different questions. Define the differences in timing and included activity, and test each contract. Avoid a single vaguely named measure whose meaning changes according to an undocumented consumer parameter.
113. How do you handle a shared source change that affects only some models?
Map which models actually use the changed fields or semantics, then assess their descendants and published interfaces. Dataset-level lineage gives a conservative starting point; column-level evidence and code review can narrow the impact when trustworthy.
A new optional field may require no consumer change, while a unit change in an existing field can invalidate a small but critical branch. Record why unaffected branches are excluded from the release scope. Do not rely on the absence of a SQL-file diff to infer semantic compatibility.
114. How would you test a model that intentionally depends on the current date?
Make the effective date a controlled input where practical and test the boundary behavior explicitly. Otherwise, the same fixture can produce different results depending on when the job runs or which session timezone applies.
An eligibility model should distinguish “as of the requested business date” from “as of the execution clock.” Record the chosen interpretation and compile-time versus query-time evaluation. This also helps explain whether a stored model must refresh when time advances even if no source rows change.
115. How do time-dependent expressions challenge incremental maintenance?
An output can change because time passes, even when its input records remain unchanged. A source-change-only selection may therefore leave expired eligibility or aging categories stale.
Identify the rows whose derived status crosses a time boundary and schedule their recomputation, or choose a query-time representation when appropriate. Test a clock advance with identical source data against the full definition. The incremental change set must include changes in relevant context, not only database writes.
SQL — fixed-date fixture for a time-dependent model:
This fixture defines expiration as exclusive and uses consistently formatted ISO dates. Account 1 changes from active to inactive without any subscription-row update. A source-write-only incremental filter would miss that context change; a real implementation should use the destination’s appropriate date types and business timezone.
116. How would you validate a deployment that changes only grants or policies?
Verify the resulting consumer and job access under the intended identities, even if the model’s rows and SQL are unchanged. Access behavior is part of the published interface and can fail independently of data correctness.
Check whether replacement materializations preserve or reapply the relevant controls. Use representative read attempts and the platform’s policy metadata through authorized test identities. A successful build under the deployment role does not prove that the downstream analyst can query the result or sees the intended scope.
117. How do you decide whether a transformation belongs inside the destination engine?
Assess whether the engine can express and operate the transformation with the required semantics, governance and cost. Destination execution is attractive when it reuses loaded data and avoids unnecessary movement, but not every dependency or algorithm fits that boundary.
A transformation requiring an external service introduces network, authorization and failure behavior that a SQL model alone may conceal. Compare a controlled external stage with the native alternative and preserve a clear model interface. ELT is a design choice, not a rule that all computation must remain in SQL.
118. How would you assess an AI-generated model dependency graph?
Compare the proposed edges and model boundaries with the actual compiled reads and business contracts. A plausible graph can omit dynamic dependencies, confuse source ownership or create circular relationships hidden by attractive naming.
Ask for a concrete change-impact walkthrough and verify it with small fixtures or selection evidence. Review any generated macros and configuration with the same care as handwritten code. The graph is useful only if it predicts how the real project builds and changes, not because its diagram looks complete.
119. What evidence shows that a destination-first transformation release is ready?
The evidence should connect the approved model definitions to the resolved graph, target relations, tests, materialization behavior and consumer compatibility. It should also explain required recomputation and the applied state left by the release.
For a selective incremental change, include why the chosen scope covers historical effects and how unchanged dependencies are bound. Retain honest limits where production-scale or platform-specific behavior was not exercised. A green job is one observation; readiness requires the relevant contract and state transitions to be understood.
Approved logical definitions, dependency versions and resolved target relations.
Evidence for first-build, incremental and schema-transition paths that apply to the change.
A justified descendant recomputation scope and consumer compatibility decision.
Applied-state evidence and a recovery plan that includes persistent data, not only source code.
120. What distinguishes a mature ELT transformation practice?
It combines clear source boundaries, coherent model interfaces, explicit materialization choices and controlled development and release state. Teams can explain how a change propagates through the graph and which evidence supports the resulting data.
They use compilation artifacts, tests and destination behavior together without confusing one for another. Reuse, ownership and native features simplify work when their limits are understood. Maturity is the ability to evolve analytical meaning safely and economically, rather than the number of models or the sophistication of the tool vocabulary.
ELT Documentation and Further Reading
Use these primary references to verify model configuration, selection semantics and destination-native refresh behavior. Product support and adapter behavior vary; match the documentation to the deployed engine and release track.
Similar Posts
