Analytics Engineer interview questions

A layered dbt project gives each model one clear responsibility and limits coupling between raw sources and consumers.

• Staging models rename, cast, and lightly standardize one source relation without embedding business aggregates.
• Intermediate models express reusable joins or transformations that are too specific to expose directly to BI.
• Mart models publish business-grain facts and dimensions with stable names, tests, documentation, and contracts.
• Consumers should depend on marts rather than source-shaped staging models so upstream schema changes remain contained.

Choose materialization from query cost, rebuild frequency, downstream latency, and the need for a physical relation.

• A view suits lightweight logic that should always reflect current upstream data but shifts compute to every consumer query.
• A table suits expensive transformations when full rebuild cost is acceptable and fast downstream reads matter.
• An incremental model limits processing to changed data but requires a reliable change boundary and idempotent update logic.
• An ephemeral model inlines reusable SQL as a CTE, avoiding storage while potentially producing large compiled queries.

An ephemeral model is a poor choice when inlining obscures lineage, duplicates expensive work, or creates an unwieldy compiled query.

• Every dependent model receives the ephemeral SQL as a CTE, so shared expensive logic can be recomputed many times.
• Deep chains can exceed warehouse query complexity limits and make execution plans harder to interpret.
• Ephemeral models cannot be queried directly for validation or consumed by tools that require a physical relation.
• A view or table is preferable when reuse, observability, or independent access outweighs avoiding storage.

Jinja renders SQL and builds the graph before the resulting statement runs in the warehouse.

• Functions such as ref, source, var, and config are resolved while dbt compiles the model.
• A run_query call needs an active warehouse connection and should be guarded with execute when parsing must remain safe.
• SQL expressions in the rendered output are evaluated by the warehouse, not by Jinja.
• Confusing the phases can produce environment-dependent manifests or macros that fail during parse-only commands.

A good dbt macro centralizes a stable transformation pattern while keeping its inputs, output SQL, and side effects explicit.

• It should remove meaningful duplication, not hide a short expression that is clearer inline.
• Parameters should represent business or adapter variation without accepting arbitrary fragments that are hard to validate.
• The compiled SQL must remain readable because reviewers and warehouse optimizers operate on the rendered statement.
• Macro behavior should be covered by representative models or unit tests and documented where its contract is not obvious.

Adapter dispatch selects a warehouse-specific macro implementation behind one shared interface.

• The caller invokes a generic macro name while dbt searches the configured namespaces and current adapter implementation.
• A default implementation can express portable SQL, with BigQuery or Snowflake variants only where syntax or behavior differs.
• Dispatch keeps conditional adapter checks out of business models and allows projects to override package behavior deliberately.
• Portability still requires tests on each supported adapter because equivalent syntax can have different types or null semantics.

A dbt package should be treated as versioned production code with a narrow, reviewed reason for adoption.

• Evaluate whether its macros solve recurring needs, compile efficiently on the target adapter, and expose stable interfaces.
• Pin compatible versions in package dependencies and review release notes before upgrades to avoid unplanned SQL changes.
• Test package upgrades in CI against representative models and inspect compiled SQL for high-impact macros.
• Prefer local project code when the dependency is large, weakly maintained, or used for only one simple transformation.

dbt seeds are appropriate for small, slowly changing reference datasets that belong in version control.

• Good examples include country mappings, controlled category lists, and test fixtures reviewed with the project.
• Column types should be configured explicitly when warehouse inference could change codes, dates, or leading zeros.
• Large, sensitive, frequently updated, or externally owned datasets belong in an ingestion process rather than a CSV commit.
• A seed still needs ownership, tests, and a change process because downstream models may treat it as a contract.

The timestamp strategy detects changes through a reliable updated_at column, while the check strategy compares selected source columns.

• Timestamp is simpler and scales well when every relevant update advances a trustworthy source timestamp.
• Check is useful without such a timestamp but requires deliberate check_cols selection and more comparison work.
• Including volatile or irrelevant columns in check_cols creates unnecessary historical versions.
• Neither strategy is safe if source corrections can arrive without changing the timestamp or compared values.

A dbt snapshot stores version intervals using dbt_valid_from and dbt_valid_to, with configurable handling for deleted source rows.

• A new detected version closes the previous interval and inserts the current state for the same unique key.
• The unique key must identify one logical entity consistently or history will split or overwrite incorrectly.
• Hard deletes can be ignored, invalidated, or represented as new records depending on snapshot configuration and dbt version.
• Downstream joins must use event time within the validity interval when historical attribution is required.

An incremental model is idempotent when rerunning the same input window produces the same target rows without duplicates or drift.

• Its unique_key must match the output grain and the selected strategy must update or replace rows at that grain.
• The incremental predicate should include all records that can change, usually with a bounded overlap for late arrivals.
• Transformations should avoid run-time-dependent values unless those values are stable or intentionally persisted.
• Reprocessing an overlap must be safe, which generally rules out blind append for mutable events.

Incremental strategies differ in the target region they rewrite and the guarantees they require from keys or partitions.

• Append only inserts selected rows, making it cheapest for immutable data and unsafe for updates or duplicate reprocessing.
• Merge updates matched unique keys and inserts new ones, while delete plus insert replaces matching keys with broader adapter compatibility.
• Insert overwrite replaces complete partitions and works well when partitions are reliable units of recomputation.
• Microbatch divides a time range into bounded batches, improving restartability and parallelism for very large event datasets.

An incremental model should reprocess a deliberate overlap or derive changes from a reliable update signal instead of filtering only on the latest event time.

• A lookback window captures delayed events but its duration must reflect measured lateness and accepted cost.
• An updated_at or change-data-capture sequence can target mutations more precisely when the source guarantees completeness.
• Merge or partition replacement must reconcile reprocessed rows at the model grain to prevent duplicates.
• Periodic full reconciliation can bound residual drift that falls outside the chosen change window.

on_schema_change defines how dbt reconciles selected model columns with an existing incremental target, not how it backfills values.

• ignore leaves the target schema unchanged, while fail stops the run when a difference is detected.
• append_new_columns adds newly selected columns without removing old ones.
• sync_all_columns aligns additions, removals, and supported type changes but can be expensive on some warehouses.
• Existing rows may still contain null or old values in new columns, so a full refresh or explicit backfill remains a separate decision.

A full refresh is justified when the target can no longer be made correct by processing only a bounded change set.

• Examples include changed historical business logic, a repaired source history, or a new column requiring values for all prior rows.
• The team should estimate runtime, warehouse cost, locking behavior, and downstream availability before rebuilding a large relation.
• Partition-scoped recomputation is preferable when it can restore correctness without replacing the entire table.
• Full-refresh protection can prevent accidental rebuilds while a documented procedure makes intentional refreshes reproducible.

A dbt model contract enforces the declared output column names and supported data types when the model is built.

• It gives downstream consumers an explicit structural interface and makes incompatible schema changes fail early.
• Constraints can be declared, but actual enforcement varies by warehouse and constraint type.
• Contracts do not prove semantic correctness, freshness, uniqueness, or accepted values without additional tests and SLAs.
• Versioned models are appropriate when an intentional breaking interface must coexist with current consumers during migration.

A useful dbt exposure records a real downstream asset, its dependencies, owner, type, and business criticality.

• depends_on should reference the models or metrics that truly feed the dashboard, notebook, application, or reverse-ETL sync.
• Ownership and maturity make lineage actionable by showing who approves changes and how carefully the asset must be protected.
• Health links or URLs connect generated documentation to the consumer and its monitoring surface.
• Exposure coverage becomes valuable when CI uses lineage to identify impacted consumers rather than when entries are merely decorative.

Source freshness thresholds should derive from when a consumer needs trustworthy data and how often the source is expected to load.

• warn_after can flag early degradation, while error_after represents a missed commitment that should fail the relevant check.
• loaded_at_field must represent ingestion availability rather than an unrelated business event timestamp.
• Thresholds should account for source cadence, timezone, weekends, and known delivery windows instead of using one global value.
• Exposures connect each freshness rule to the dashboards, Hex analyses, or reverse-ETL audiences whose SLA depends on it.

Slim CI compares the current project with a prior manifest, builds changed nodes and selected dependents, and defers unchanged references to an existing environment.

• State selectors such as state:modified identify graph or configuration changes recorded in artifacts.
• The plus operator can include downstream children whose behavior may change because an ancestor changed.
• defer resolves unbuilt ref calls against a stable comparison environment, avoiding a full warehouse rebuild in CI.
• CI must use compatible manifests and account for macros, seeds, variables, and indirect test selection that can broaden impact.

dbt Core supplies the command-line transformation framework, while dbt Cloud adds a managed control plane for development, scheduling, metadata, and governed execution.

• Core offers deployment flexibility but leaves orchestration, credentials, artifact storage, and the development environment to the team.
• Cloud provides managed jobs, an IDE, environment controls, logs, and platform features whose availability depends on the plan.
• Both execute dbt projects against the warehouse, so model design and SQL performance remain the team's responsibility.
• The choice should reflect governance, integration, cost, and operational ownership rather than assuming one changes dbt semantics.