When I see “Advanced proficiency in SQL, DBT, and Python” on a resume, I am probing for three distinct archetypes in one person: The Data Analyst (SQL), The Software Engineer (Python), and The Analytics Engineer (DBT).

Below is my comprehensive interview playbook. I have organized the questions by Skill, Complexity (Junior vs. Senior), and Behavioral/System-Design crossovers. For each, I provide the Answer Framework and a Model Answer.

Part 1: Advanced SQL (The Foundation)

My goal here is to see if you treat SQL as a scripting language or an engineering language. I care about set-based thinking, not loops.

Q1: (Intermediate) “Explain the exact execution order of a SQL query. Why does this matter for performance?”

The Framework: Don’t just list SELECT first. Start with FROM/JOIN, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Explain that WHERE filters rows before aggregation, while HAVING filters after aggregation.
The Model Answer:
“The logical execution order is FROM (including JOINs) -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT/OFFSET. This matters critically for performance because if I put a filter on a non-aggregated column in HAVING instead of WHERE, the database is forced to scan and aggregate millions of rows before discarding them. Conversely, putting a filter in WHERE reduces the dataset before the expensive GROUP BY operation, which is exponentially faster.”

Q2: (Advanced) “We have a 10-billion-row fact table. Write a query to find the top 5 customers by sales in the last 30 days. Show me three different ways to write this and tell me which performs best.”

The Framework: They need to show ROW_NUMBER(), RANK(), and a LIMIT with GROUP BY. The best performer is usually GROUP BY + ORDER BY + LIMIT because it allows the query planner to use a top-N sort algorithm (which doesn’t sort the whole dataset).
The Model Answer:
“Option 1 (Window Function): SELECT customer_id, SUM(sales), ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) as rn FROM fact WHERE date > CURRENT_DATE - 30 GROUP BY 1 QUALIFY rn <= 5;
Option 2 (Subquery with LIMIT): SELECT customer_id, SUM(sales) FROM fact [...] GROUP BY 1 ORDER BY 2 DESC LIMIT 5;
I would choose Option 2 for a 10-billion-row table. Most modern MPP engines (Snowflake, Redshift, BigQuery) have a ‘Top-N’ optimization. When they see ORDER BY ... LIMIT, they push a LIMIT down into the sort operation, using a heap sort that only keeps 5 rows in memory. Window functions, conversely, have to generate a rank for every single customer before filtering, which is vastly more memory-intensive.”

Q3: (Expert) “Explain the ‘Halloween Problem’ in SQL and how you would avoid it when writing incremental DBT models.”

The Framework: This is a trick question for DBAs. The Halloween Problem is when an UPDATE or MERGE reads a row, updates it, and then reads it again in the same operation, causing infinite loops or double-counting.
The Model Answer:
“The Halloween Problem occurs when a DML operation changes a row’s physical location or attributes in a way that causes it to be revisited during the same scan. In DBT, this usually rears its head during incremental MERGE statements. If my unique_key is a timestamp and I update it, the merge might try to re-insert the same row. I avoid this by: 1) Using a static, immutable unique_key (like a UUID or hash of the natural key). 2) Explicitly materializing the new records into a temporary staging table before the merge, so the source dataset is completely static during the MERGE operation. I never join directly to the target table during the on_conflict clause.”

Part 2: DBT (The Analytics Engineering Core)

I don’t care if you know dbt run. I care if you understand data lineage, incremental strategies, and testing.

Q4: (Intermediate) “Compare and contrast ‘Views’, ‘Tables’, and ‘Incremental’ materializations. Give me a specific business case for each.”

The Framework: Views = light transformation, low cost, high latency. Tables = heavy transformation, high cost, low latency. Incremental = massive datasets where full refreshes are impossible.
The Model Answer:
“- Views: I use these for raw staging models where I am just renaming columns or casting types. Zero storage cost, and they always reflect the source truth. However, they are slow for complex joins.

• Tables: I use these for complex joins or window functions that hit the optimizer hard. I materialize as table for BI tools like Tableau, so queries are sub-second. I schedule a full refresh overnight.
• Incremental: I use these exclusively for event-streaming tables (e.g., 1 billion clicks per day). I run dbt run hourly. The is_incremental() macro filters the source to only the last 2 hours (to catch late-arriving data), and I merge into the target. This saves 99.9% of compute cost compared to a table rebuild.”

Q5: (Advanced) “Walk me through your DBT project structure. How do you handle environment-specific configurations (dev vs. prod) and sensitive secrets?”

The Framework: They should mention targets in profiles.yml, vars in dbt_project.yml, and the importance of sources and freshness tests.
The Model Answer:
“I follow the Staging -> Intermediate -> Marts structure.

1. Staging: One-to-one with source tables. Just clean names and data types.
2. Intermediate: Joins and heavy business logic.
3. Marts: Aggregated, exposed to the business.
   For environments, I rely on target.name in my profiles.yml. In my dbt_project.yml, I use vars like {'schema_prefix': 'dev_'}. When I run dbt run --target dev, all models materialize in my personal development schema.
   For secrets (like API tokens for external sources), I never hardcode them. I use environment variables (e.g., {{ env_var('DBT_SNOWFLAKE_PASSWORD') }}) and pass them via the CLI or our CI/CD pipeline (GitHub Actions). I also leverage dbt source freshness with a warn_after of 1 hour to send Slack alerts if our event streams stop.”

Q6: (Expert) “Your DBT incremental model is producing duplicates. You cannot change the source query. How do you diagnose and fix it?”

The Framework: They must differentiate between intra-run duplicates and cross-run duplicates. They should mention the unique_key and merge_exclude_columns.
The Model Answer:
“Duplicates usually come from three areas:

1. The unique_key is wrong: If I’m merging on user_id but the source sends multiple rows per user per day, the merge will only keep one. I would change the unique_key to user_id || '_' || date_day and adjust the order_by in the incremental strategy to ensure the latest record wins.
2. The is_incremental filter is overlapping: If my filter is WHERE date > (SELECT MAX(date) FROM {{ this }}), and I have records inserted at exactly the same millisecond on the boundary, I need to add a buffer. I use WHERE date > DATEADD(day, -1, (SELECT MAX(date) FROM {{ this }})) to capture overlaps, and then use row_number() within the model to deduplicate before the merge.
3. Backfills: When we backfill historical data, I temporarily set +full_refresh: true to rebuild the entire table, because incremental logic breaks when inserting dates that are older than the current max.”

The Framework: Do not use pd.read_csv() without chunks. Use pandas.read_csv(chunksize=...) or, ideally, Dask or Polars. They must mention lazy evaluation and garbage collection.
The Model Answer:
“Reading a 50GB file into a single DataFrame will cause a MemoryError. I use chunking.

python

import pandas as pd
chunk_iter = pd.read_csv('large_file.csv', chunksize=100000)
for chunk in chunk_iter:
    # Process transformation (e.g., filter columns, rename)
    processed = chunk[['col1', 'col2']].dropna()
    # Append to a partitioned Parquet file using 'mode='a''
    processed.to_parquet('output_folder/', engine='pyarrow', partition_cols=['date'], append=True)
    del chunk # Explicitly clear memory

However, in a production environment, I would advocate for Polars because it uses Apache Arrow and parallelizes across all CPU cores, or I’d convert this to a PySpark job if the cluster supports it. The key is to never store the entire dataset in a Python list or DataFrame.”

Q8: (Advanced) “What is the Global Interpreter Lock (GIL) and how does it affect your data pipelines? How do you bypass it?”

The Framework: The GIL prevents multiple threads from executing Python bytecode simultaneously. For CPU-bound data tasks, threading is useless; you need multiprocessing or vectorized libraries that release the GIL.
The Model Answer:
“The GIL is a mutex that protects access to Python objects, preventing deadlocks but also preventing true parallel execution of threads. In data pipelines, this is deadly because if I spin up 10 threads to process 10 JSON files, only one runs at a time.
To bypass it:

1. I/O Bound: I use asyncio or threading for API calls, because the GIL is released while waiting for network responses.
2. CPU Bound: I use the multiprocessing module (Pool.map) to spawn separate Python processes.
3. Best Practice: I avoid pure Python loops entirely. I use Numpy or Pandas, which are written in C and release the GIL. For example, df['col'].apply(lambda x: x**2) is slow and GIL-locked; df['col']**2 uses vectorized C operations and is 100x faster.”

Q9: (Expert) “Write a Python decorator that retries a data pipeline function if it fails due to a transient Snowflake error, but with exponential backoff.”

The Framework: This tests their grasp of closures, functools.wraps, and time.sleep. I want to see try/except and a variable wait time.
The Model Answer:

python

import time
from functools import wraps
import logging

def retry_snowflake(max_retries=3, base_delay=1):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            retries = 0
            while retries < max_retries:
                try:
                    return func(*args, **kwargs)
                except Exception as e:
                    # Check for transient errors (e.g., 000604, 000606)
                    if "000604" in str(e) or "connection" in str(e).lower():
                        retries += 1
                        wait_time = base_delay * (2 ** retries)  # Exponential: 2s, 4s, 8s
                        logging.warning(f"Transient error. Retry {retries} in {wait_time}s")
                        time.sleep(wait_time)
                    else:
                        raise e  # Critical error, don't retry
            raise Exception(f"Function failed after {max_retries} retries.")
        return wrapper
    return decorator

truncates.”

The Framework: They must propose a Staging Layer (Python for heavy logic) -> Parsing Layer (SQL/DBT for joins). They must handle the “Schrodinger’s data” problem (the source is empty during the load).
The Model Answer:
“We will use a Medallion Architecture:

1. Bronze (Python + S3): A Python script runs via Airflow. It extracts the source via API, processes the heavy business logic using Pandas (handling nested JSONs), and writes the output to S3 as Parquet files, partitioned by ingestion_date. Crucially, we write to a staging prefix (s3://bucket/staging/) first.
2. Silver (DBT + External Tables): In DBT, I create a Source that points to the staging prefix. I run an intermediate DBT model that uses a COPY INTO or CREATE OR REPLACE EXTERNAL TABLE to point to this new data.
3. The ‘Zero Downtime’ Swap (DBT): I do not truncate the production table. Instead, I materialize the final marts as Tables, but I use DBT’s contract feature to ensure the schema matches. I run the model with +full_refresh: true in a separate schema (e.g., analytics_prod_new). Once the DBT run succeeds, I run a single ALTER TABLE analytics_prod SWAP WITH analytics_prod_new; command. The dashboard querying the old table never sees an empty dataset; the swap is atomic and instantaneous.”

Part 5: The “Gotcha” Questions (For Senior/Staff Level)

These are behavioral, but they reveal technical philosophy.

Q11: “Explain to me when you would deliberately write inefficient SQL, Python, or DBT code.”

The Framework: This tests if they understand Developer Efficiency vs. Runtime Efficiency.
The Model Answer:
“I deliberately write inefficient code during the Discovery Phase. If I am exploring a dataset with complex joins, I will write a messy, nested subquery with a SELECT * just to get the data into a CSV for analysis quickly.
I also deliberately choose ‘inefficient’ SQL in DBT when I use materialized='view'. A view is computationally expensive every time it’s queried, but it saves storage costs and allows me to iterate on the logic rapidly during development. I make it efficient (switch to table) only once the business signs off on the logic.”

Q12: “How do you manage schema changes (columns being added/dropped) in your source data across a DBT pipeline that uses Python for preprocessing?”

The Framework: They must mention Defensive Programming and Schema Contracts.
The Model Answer:
“I treat schema changes as breaking changes unless explicitly handled.

1. In Python: I never use df[column] directly without a guard. I use df.get(column, default_value) or write a schema validation function using Pydantic or Great Expectations at the raw ingestion layer. If a column is missing, the pipeline fails gracefully and alerts rather than passing silent NULLs downstream.
2. In DBT: I use the columns configuration in my schema.yml to enforce a Contract. If the source adds a column I don’t know about, DBT will ignore it. If it drops a column I have defined as not null, DBT will throw a compilation error before it even runs. This prevents the ‘garbage in, garbage out’ problem. We enforce semantic versioning on our data contracts.”

Advanced proficiency in SQL, dbt, and Python is a core expectation for senior data engineering, analytics engineering, and data platform roles. Below is a structured collection of high-signal interview questions with detailed, interview-ready answers. Focus is on advanced concepts, real-world trade-offs, performance, testing, collaboration, and production patterns.

1. Advanced SQL

Q1: Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK(). When would you use each?

Answer: All three are window functions that assign rankings within a partition ordered by a specified column.

• ROW_NUMBER() assigns a unique sequential number starting at 1, even for ties.
• RANK() assigns the same rank to ties and leaves gaps (e.g., 1, 2, 2, 4).
• DENSE_RANK() assigns the same rank to ties with no gaps (e.g., 1, 2, 2, 3).

Use ROW_NUMBER() when you need a deterministic unique identifier (e.g., “latest record per key” via QUALIFY ROW_NUMBER() OVER (…) = 1). Use RANK() when gaps matter for competition-style ranking. Use DENSE_RANK() for continuous ranking without gaps. Always combine with a deterministic ORDER BY (include a unique tie-breaker) to avoid non-deterministic results.

Q2: How do you optimize a slow query that joins multiple large tables and uses aggregations?

Answer:

1. Examine the execution plan (EXPLAIN / EXPLAIN ANALYZE).
2. Ensure proper indexing (covering indexes on join and filter columns, composite indexes matching predicate order).
3. Filter early (push predicates as far down as possible; use CTEs or subqueries carefully—modern optimizers handle them well, but materialization can hurt).
4. Prefer hash joins for large equi-joins when memory allows; watch for nested-loop joins on large sets.
5. Use approximate algorithms (APPROX_COUNT_DISTINCT, HyperLogLog) when exactness is not required.
6. Partition large tables by high-cardinality date or key columns and prune partitions.
7. Rewrite correlated subqueries as joins or window functions.
8. Consider materialized views or incremental tables (especially with dbt) for repeated heavy aggregations.
9. Check statistics and vacuum/analyze status.
10. Profile with actual runtime metrics and rewrite the most expensive operators first.

Q3: Explain window functions for running totals, moving averages, and cumulative distributions. Give production examples.

Answer:

• Running total: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING).
• Moving average (e.g., 7-day): AVG(metric) OVER (PARTITION BY … ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
• Cumulative distribution: CUME_DIST() OVER (ORDER BY score) or PERCENT_RANK().

Production examples: customer lifetime value calculation, sessionization (gap detection with LAG), cohort retention curves, anomaly detection baselines, and financial period-to-date metrics. Always specify the frame (ROWS vs RANGE) explicitly; RANGE can produce unexpected results with ties.

Q4: How do you handle slowly changing dimensions (SCD) Type 2 in pure SQL?

Answer: Maintain valid_from, valid_to, and is_current columns. On change:

1. Close the previous version (UPDATE … SET valid_to = CURRENT_TIMESTAMP, is_current = FALSE WHERE key = ? AND is_current).
2. Insert the new version with valid_from = CURRENT_TIMESTAMP, valid_to = ‘9999-12-31’, is_current = TRUE.

Use a merge/upsert pattern or staging + window functions to detect changes (LAG on hash of attributes). In warehouses that support it, use temporal tables or built-in SCD helpers. Always keep a surrogate key for the versioned row.

Q5: What is the difference between EXISTS, IN, and JOIN for semi-joins? Performance implications?

Answer:

• EXISTS short-circuits on the first match and is usually the most efficient for existence checks.
• IN can be rewritten by the optimizer to a semi-join; with large lists or NULLs it can behave differently.
• Explicit JOIN can produce duplicates if not carefully written (use DISTINCT or GROUP BY).

Prefer EXISTS for correlated existence checks. Modern optimizers often turn all three into the same plan, but test with real data volumes.

Q6: Explain anti-joins and how you implement them safely.

Answer: Anti-join returns rows from the left table with no match on the right. Preferred patterns:

• LEFT JOIN … WHERE right.key IS NULL
• WHERE NOT EXISTS (SELECT 1 FROM right WHERE …)

Avoid NOT IN with possible NULLs (it returns empty). Always consider NULL-handling and indexes on the join keys.

Q7: How do you write efficient recursive CTEs and when should you avoid them?

Answer: Recursive CTEs are useful for hierarchies, graphs, and sequential generation (bill-of-materials, org charts, date series). Structure: anchor member + recursive member with a termination condition.

Avoid deep recursion on large graphs (risk of stack depth or performance explosion). Prefer iterative approaches, adjacency-list + closure tables, or graph engines for complex graphs. Always set a maximum recursion depth guard.

Q8: Discuss query performance differences between columnar and row-oriented stores, and how that affects SQL writing.

Answer: Columnar stores (Snowflake, BigQuery, Redshift, DuckDB) excel at aggregations and selective column reads; they benefit from projection push-down and late materialization. Row stores favor OLTP and point lookups.

In columnar environments: avoid SELECT *, prefer predicate push-down, use clustering/partitioning keys that match common filters, and leverage automatic clustering or search optimization services. Write queries that minimize data scanned.

2. Advanced dbt

Q1: Explain the dbt project structure and the purpose of each layer (staging, intermediate, marts).

Answer:

• Staging (stg_): 1:1 with source tables, light cleaning, renaming, type casting, basic tests. Sources are declared in sources.yml.
• Intermediate (int_): Business logic that is reusable but not yet final. Often ephemeral or views.
• Marts (fct_, dim_): Business-facing tables (facts and dimensions) consumed by BI tools or downstream systems.

This layered approach improves readability, testing granularity, and incremental rebuild control. Document materializations and ownership in schema.yml.

Q2: How do you implement incremental models correctly? Discuss strategies and pitfalls.

Answer:

SQL

{{ config(materialized='incremental', unique_key='id', incremental_strategy='merge') }}
SELECT ... FROM source
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Strategies: append, merge (or delete+insert), insert_overwrite (partition-based).

Pitfalls:

• Late-arriving data requires look-back windows or full-refresh capability.
• Changing unique_key or schema requires careful migration.
• Source data quality issues can silently create duplicates.
• Always test incremental logic with both full-refresh and incremental runs. Use dbt run –full-refresh in CI for critical models.

Q3: Explain dbt testing strategy (generic, singular, custom, unit tests).

Answer:

• Generic tests: unique, not_null, accepted_values, relationships declared in YAML.
• Singular tests: custom SQL files that should return zero rows.
• Custom generic tests: macros that accept arguments.
• Unit tests (dbt 1.8+): isolate model logic with given inputs/expected outputs.

Best practice: apply not_null + unique on primary keys, relationships on foreign keys, and business-rule singular tests. Run tests in CI on every PR; fail the pipeline on critical test failures. Use store_failures for debugging.

Q4: How do you manage environments, sources, and secrets in dbt?

Answer: Use profiles.yml (or environment variables / dbt Cloud credentials) for connection details. Target different schemas or databases per environment (dev, ci, prod).

Sources are declared once and referenced with {{ source(‘src’, ‘table’) }}. Use source freshness tests. Secrets never live in the repo; inject via environment variables or secret managers. Use dbt_project.yml variables and vars for environment-specific configuration.

Q5: Discuss macros, packages, and code reuse patterns.

Answer: Macros encapsulate repeated SQL patterns (surrogate keys, date spine, SCD logic, pivot). Packages (dbt-utils, dbt-expectations, audit-helper, etc.) provide battle-tested macros.

Write macros that are pure where possible, document arguments, and keep side-effects minimal. Prefer composition over deep nesting. Version packages carefully and pin versions in packages.yml.

Q6: How do you handle model documentation, lineage, and collaboration at scale?

Answer: Rich schema.yml descriptions, column descriptions, and meta properties. Generate documentation site with dbt docs generate. Use exposures for downstream BI dashboards.

Lineage is automatic via ref() and source(). For large teams: enforce naming conventions, code owners, PR reviews that include model contracts (dbt contracts), and a clear ownership model (domain-oriented data products).

Q7: Explain snapshots and when to use them vs SCD Type 2 models.

Answer: Snapshots capture point-in-time state of a mutable source using timestamp or check strategy. They are ideal for slowly changing source data you do not control.

Custom SCD Type 2 models give more control (custom valid_from/to logic, additional columns). Prefer snapshots for simplicity when the source is external; build explicit SCD models when you own the transformation logic and need complex business rules.

Q8: How do you debug and optimize dbt runs in production?

Answer:

• Use dbt run –select and state-based selection (state:modified+).
• Analyze compiled SQL and warehouse query history.
• Monitor model runtime and bytes scanned.
• Use incremental models, clustering, and materialization choices appropriately.
• Enable query tagging / comments for warehouse cost attribution.
• Fail fast on tests; use Slim CI (only modified + downstream models).

3. Advanced Python (for Data / Analytics Engineering)

Q1: How do you structure a production-grade Python data pipeline?

Answer:

• Clear package structure with src/, tests, configs.
• Dependency management with poetry or uv + lock files.
• Configuration via environment variables or typed config (Pydantic).
• Logging with structured logs (JSON).
• Idempotent, retryable tasks.
• Orchestration via Airflow, Dagster, Prefect, or dbt Cloud + external schedulers.
• Containerization and CI/CD that runs unit + integration tests.
• Observability (metrics, traces, alerts on data freshness/quality).

Q2: Explain efficient data processing with pandas vs Polars vs PySpark. When do you choose each?

Answer:

• pandas: excellent for small-to-medium data, rich ecosystem, but single-threaded and memory-hungry.
• Polars: lazy evaluation, multi-threaded, much lower memory footprint, excellent for medium data on a single machine.
• PySpark / Dask: distributed processing when data exceeds single-node memory.

Prefer Polars for most modern single-node workloads. Use Spark when you already have a cluster or data volume demands it. Always profile memory and CPU.

Q3: How do you write robust, testable data transformation code?

Answer:

• Pure functions where possible.
• Explicit schemas (Pandera, Pydantic, Great Expectations, or Spark schemas).
• Unit tests with small fixtures; property-based testing for edge cases.
• Integration tests against a real (or Testcontainers) database.
• Type hints + mypy/pyright.
• Avoid hidden global state; inject dependencies.

Q4: Discuss concurrency and parallelism in Python data jobs.

Answer:

• concurrent.futures (ThreadPoolExecutor for I/O, ProcessPoolExecutor for CPU).
• asyncio for high-concurrency I/O (API calls, many small DB queries).
• Avoid the GIL for CPU-bound work by using processes, Polars, or native extensions.
• For Spark, rely on the distributed engine.
• Always handle timeouts, retries (tenacity), and graceful cancellation.

Q5: How do you interact with databases and warehouses from Python efficiently?

Answer:

• Use SQLAlchemy (Core) or native connectors with connection pooling.
• Prefer parameterized queries; never string-format SQL.
• For bulk loads: COPY, write_pandas (Snowflake), to_sql with method=’multi’, or cloud storage staging + external tables.
• Use Arrow-based transfer (pandas → PyArrow → warehouse) where supported.
• Manage transactions carefully; prefer idempotent writes.

Q6: Explain error handling, retries, and observability patterns.

Answer:

• Structured exceptions with context.
• Retry transient failures with exponential backoff + jitter.
• Circuit breakers for external systems.
• Emit metrics (rows processed, latency, data quality scores) and traces.
• Dead-letter queues or quarantine tables for bad records.
• Alert on SLA breaches (freshness, volume anomalies).

Q7: How do you manage Python dependencies and environments in a data team?

Answer:

• Lock files, reproducible builds.
• Separate environments for orchestration vs transformation vs BI.
• Use Docker or virtual environments per project.
• Prefer dbt for SQL-heavy transformations; keep Python for complex logic, ML feature pipelines, or custom extractors.
• Version everything; pin transitive dependencies when necessary for stability.

4. Combined / System Design & Behavioral Questions

Q1: Design a reliable, incremental ELT pipeline from multiple SaaS sources into a warehouse, transformed with dbt, orchestrated with Python.

Answer outline: Extract with Python (or Fivetran/Airbyte) → load raw into landing zone → dbt staging → intermediate business logic → marts. Use incremental models + source freshness. Orchestrate with Dagster/Airflow/Prefect. Add data quality tests, alerting, and a backfill strategy. Document contracts and SLAs.

Q2: How do you ensure data quality across SQL, dbt, and Python layers?

Answer: Tests at every layer: source freshness + schema tests, dbt generic/singular tests, Python schema validation (Pandera), and end-to-end reconciliation queries. Monitor with anomaly detection on volume/distribution. Treat data quality as a product with clear ownership.

Q3: A critical dbt model is taking 4 hours and costing too much. Walk through your debugging process.

Answer:

1. Check warehouse query profile / bytes scanned.
2. Examine compiled SQL and joins.
3. Verify incremental logic is actually incremental.
4. Look for data skew, exploding joins, or unnecessary columns.
5. Consider clustering, partitioning, or rewriting with approximate methods.
6. Split into smaller models or materialize intermediate results.
7. Validate with a reduced data set.

Q4: How do you collaborate with analytics engineers, data scientists, and software engineers?

Answer: Clear contracts (dbt model contracts, OpenAPI for services), shared documentation, PR review culture, domain-oriented ownership, and regular alignment on SLAs and data products. Prefer SQL + dbt for transformations that analysts need to understand; use Python when the logic is complex or requires external libraries.

Q5: Describe a time you improved performance or reliability of a data platform (STAR format expected in real interviews).

Prepare a concrete story covering situation, metrics before/after, technical decisions (SQL rewrite, incremental strategy, Python optimization, testing), and business impact.

Preparation Tips for Senior / Managerial Level

• Be ready to whiteboard SQL and dbt model design.
• Discuss trade-offs (cost vs latency vs freshness vs complexity).
• Show ownership of production incidents and post-mortems.
• Demonstrate mentoring: how you raise the SQL/dbt/Python bar for the team.
• Know the latest features (dbt unit tests, contracts, semantic layer; modern Python packaging; warehouse-specific optimizations).

This set covers the majority of advanced technical and system-design questions you will encounter. Practice writing the SQL and dbt code live, and prepare 2–3 detailed war stories that demonstrate depth. Good luck.

For a Senior Technical Manager / Senior Data Engineer / Analytics Engineering interview, “advanced proficiency in SQL, dbt, and Python” usually means much more than syntax. Interviewers will test whether you can design reliable data transformations, optimize workloads, enforce data quality, build reusable frameworks, troubleshoot production failures, and explain trade-offs to engineering/business stakeholders.

Below is a comprehensive interview bank organized from fundamentals through senior/architect-level scenarios.

Advanced SQL + dbt + Python Interview Guide

1. What interviewers are really looking for

At senior level, expect questions around:

PART I — ADVANCED SQL

2. What is the difference between WHERE and HAVING?

Answer:

WHERE filters rows before aggregation, while HAVING filters groups after aggregation.

SELECT
    department_id,
    COUNT(*) AS employee_count
FROM employees
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) > 10;

Execution conceptually:

FROM
 ↓
WHERE
 ↓
GROUP BY
 ↓
HAVING
 ↓
SELECT
 ↓
ORDER BY

A common mistake is trying to use an aggregate in WHERE:

WHERE COUNT(*) > 10

This is invalid because aggregation has not happened yet.

3. Explain INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN

Returns only matching records.

SELECT *
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id;

LEFT JOIN

Returns every record from the left table and matching records from the right.

SELECT *
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

Useful for identifying customers who have never ordered.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

FULL OUTER JOIN

Returns matched and unmatched records from both sides.

Useful for reconciliation.

4. UNION vs UNION ALL

UNION removes duplicates.

SELECT customer_id FROM customers_2025
UNION
SELECT customer_id FROM customers_2026;

UNION ALL preserves duplicates and normally performs better.

SELECT customer_id FROM customers_2025
UNION ALL
SELECT customer_id FROM customers_2026;

Senior-level answer:

I prefer UNION ALL unless deduplication is explicitly required because duplicate elimination introduces additional processing, typically involving sorting or hashing.

5. Explain window functions.

Window functions perform calculations across related rows without collapsing the result set.

Example:

SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) AS department_avg_salary
FROM employees;

Unlike:

GROUP BY department_id

the window function retains the individual employee rows.

6. Difference between ROW_NUMBER, RANK, and DENSE_RANK

Suppose salaries are:

100000
100000
90000
80000

ROW_NUMBER

1
2
3
4

RANK

1
1
3
4

DENSE_RANK

1
1
2
3

Use:

ROW_NUMBER()

when you need exactly one deterministic record.

For example, latest customer record:

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM customer_history t
) x
WHERE rn = 1;

7. How would you find the second-highest salary?

One approach:

SELECT MAX(salary)
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Better for more complex ranking:

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) x
WHERE rnk = 2;

The second solution handles duplicate salaries correctly.

8. Find duplicate records.

SELECT
    customer_id,
    COUNT(*) AS cnt
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

To retrieve the actual duplicate rows:

SELECT *
FROM (
    SELECT
        c.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at
        ) AS rn
    FROM customers c
) x
WHERE rn > 1;

9. How do you remove duplicates while retaining the latest record?

DELETE FROM customer_table
WHERE customer_id IN (
    SELECT customer_id
    FROM (
        SELECT
            customer_id,
            ROW_NUMBER() OVER (
                PARTITION BY customer_id
                ORDER BY updated_at DESC
            ) AS rn
        FROM customer_table
    ) x
    WHERE rn > 1
);

In production, I would first validate the candidate rows with a SELECT, run the operation inside an appropriate transaction where supported, and ensure backups/recovery procedures exist.

10. Explain CTEs.

CTE = Common Table Expression.

WITH active_customers AS (
    SELECT *
    FROM customers
    WHERE status = 'ACTIVE'
)
SELECT *
FROM active_customers;

Advantages:

• readability
• modular SQL
• easier debugging
• recursive queries
• reusable logical steps

But a CTE is not automatically a performance optimization. Depending on the database engine, it may be inlined, materialized, or optimized differently.

11. CTE vs temporary table

CTE

Best for:

• one query
• readability
• logical decomposition

Temporary table

Useful when:

• intermediate results are reused
• you need indexes/statistics
• processing is large
• you need multiple statements against the intermediate dataset

Senior answer:

I choose based on optimizer behavior, data volume, reuse requirements, and whether indexing or materialization provides a measurable benefit.

12. What is a correlated subquery?

A correlated subquery references a column from the outer query.

SELECT
    e.employee_id,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

This finds employees earning above their department average.

For large datasets, I would often rewrite this using a window function:

SELECT *
FROM (
    SELECT
        e.*,
        AVG(salary) OVER (
            PARTITION BY department_id
        ) AS avg_salary
    FROM employees e
) x
WHERE salary > avg_salary;

13. EXISTS vs IN

Example:

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

EXISTS is often useful when you only care whether a matching row exists.

However, don’t claim that EXISTS is always faster than IN. Modern optimizers may transform both into similar execution strategies.

Senior answer:

I inspect the execution plan and data characteristics rather than relying on blanket rules.

14. What is SARGability?

SARG = Search ARGument.

A predicate is SARGable when the database can efficiently use an index/access path.

Bad:

WHERE YEAR(order_date) = 2026

Potentially better:

WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01'

Why?

The first applies a function to the column. The second allows the optimizer to search a range directly.

15. How do you optimize a slow SQL query?

I use a systematic process:

Step 1 — Establish baseline

Measure:

• execution time
• CPU
• logical reads
• physical reads
• rows processed
• concurrency

Step 2 — Inspect execution plan

Look for:

• full table scans
• expensive joins
• incorrect cardinality estimates
• large sorts
• spills
• repeated scans
• poor join strategies

Step 3 — Check predicates

Look for:

• non-SARGable expressions
• unnecessary functions
• implicit conversions
• excessive OR
• unnecessary columns

Step 4 — Check indexes/partitioning

Evaluate:

• join keys
• filter columns
• composite indexes
• clustering/partitioning strategy

Step 5 — Reduce data early

Instead of:

SELECT *

select only required columns and filter as early as appropriate.

Step 6 — Re-test

Never assume the optimization worked.

16. What is an execution plan?

An execution plan describes how the database intends to execute a query.

It may show:

• scans
• seeks
• joins
• sorts
• aggregations
• filters
• estimated rows
• actual rows
• memory grants
• parallelism

A senior engineer should compare estimated vs actual cardinality because major differences can indicate statistics or data-distribution problems.

17. Explain indexes.

An index provides an additional access structure that can make data retrieval faster.

Common types/concepts:

• B-tree
• hash
• clustered
• nonclustered
• composite
• covering
• unique
• filtered/partial indexes depending on database

But indexes have costs:

• storage
• INSERT overhead
• UPDATE overhead
• DELETE overhead
• maintenance

Therefore:

More indexes do not necessarily mean better performance.

18. What is a composite index?

Example:

CREATE INDEX idx_customer_status_date
ON orders(customer_id, status, order_date);

Column order matters.

For queries frequently filtering by:

customer_id

and:

status

this may be useful.

But index design depends heavily on the database engine, query patterns, selectivity, and workload.

19. Explain normalization vs denormalization.

Normalization

Reduces redundancy.

Typical forms:

• 1NF
• 2NF
• 3NF
• BCNF

Good for:

• OLTP
• data integrity
• transactional workloads

Denormalization

Introduces redundancy for performance or analytical usability.

Good for:

• analytics
• reporting
• dimensional models
• read-heavy systems

Senior answer:

I don’t treat normalization and denormalization as ideological choices. I select the model based on workload, integrity requirements, query patterns, latency, and storage/compute economics.

20. Explain ACID.

Atomicity

Transaction succeeds completely or fails completely.

Consistency

Database moves from one valid state to another.

Isolation

Concurrent transactions should not improperly interfere.

Durability

Committed data survives failures according to the system’s durability guarantees.

21. Explain transaction isolation levels.

Common levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Some databases also support:

SNAPSHOT
READ COMMITTED SNAPSHOT

Higher isolation generally provides stronger consistency but can increase locking/contention or resource usage depending on implementation.

Potential anomalies include:

• dirty reads
• non-repeatable reads
• phantom reads

22. What is a deadlock?

A deadlock occurs when transactions wait on each other indefinitely.

Example:

Transaction A locks Row 1
Transaction B locks Row 2

A waits for Row 2
B waits for Row 1

Mitigation:

• consistent lock ordering
• shorter transactions
• appropriate indexes
• smaller batches
• appropriate isolation
• retry logic where appropriate

23. What is a recursive CTE?

Example organizational hierarchy:

WITH employee_hierarchy AS (

    SELECT
        employee_id,
        manager_id,
        employee_name,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name,
        h.level + 1
    FROM employees e
    JOIN employee_hierarchy h
      ON e.manager_id = h.employee_id
)

SELECT *
FROM employee_hierarchy;

Useful for:

• organizational trees
• category hierarchies
• bill of materials
• graph-like relationships

24. How do you calculate a running total?

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
    ) AS running_total
FROM orders;

Notice the explicit ROWS frame. Understanding window frames is important for senior-level interviews.

25. Find gaps in dates.

A common technique uses LAG():

SELECT
    customer_id,
    order_date,
    LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_date
FROM orders;

Then calculate the difference between dates and identify gaps exceeding the expected interval.

26. What is a Slowly Changing Dimension?

SCD manages historical changes in dimensional attributes.

Type 1

Overwrite.

Old address → New address

No history.

Type 2

Maintain history:

Customer | Address | Start Date | End Date | Current

Example:

101 | NY | 2024-01-01 | 2025-05-10 | N
101 | CA | 2025-05-10 | NULL       | Y

Type 3

Store limited previous values in additional columns.

27. How would you implement SCD Type 2 in SQL?

Typical approach:

1. Detect changed records.
2. Expire current records.
3. Insert new versions.
4. Maintain effective dates.
5. Maintain current indicator.

Conceptually:

UPDATE dim_customer
SET
    end_date = CURRENT_DATE,
    is_current = 0
WHERE customer_id = :customer_id
  AND is_current = 1;

Then:

INSERT INTO dim_customer (
    customer_id,
    address,
    start_date,
    end_date,
    is_current
)
VALUES (
    :customer_id,
    :new_address,
    CURRENT_DATE,
    NULL,
    1
);

In production, the implementation should account for concurrency, duplicate events, late-arriving data, and idempotency.

PART II — DBT

28. What is dbt?

dbt is a transformation framework primarily used to transform data inside the data warehouse/lakehouse using SQL and Jinja.

Typical architecture:

Sources
   ↓
Staging
   ↓
Intermediate
   ↓
Marts
   ↓
BI / Analytics / ML

dbt focuses primarily on the T in ELT.

29. Explain dbt’s DAG.

DAG = Directed Acyclic Graph.

Example:

raw_customers
      ↓
stg_customers
      ↓
int_customer_orders
      ↓
dim_customers
      ↓
customer_metrics
      ↓
BI Dashboard

dbt builds dependencies through references such as:

{{ ref('stg_customers') }}

This enables:

• dependency management
• execution ordering
• lineage
• documentation
• selective execution

30. What is ref()?

Example:

SELECT *
FROM {{ ref('stg_orders') }}

ref():

• creates dependency metadata
• resolves the physical relation
• enables environment-aware deployment
• contributes to DAG lineage

Instead of hardcoding:

FROM analytics.stg_orders

you use:

FROM {{ ref('stg_orders') }}

31. What is source()?

Example:

SELECT *
FROM {{ source('sales', 'orders') }}

Sources represent raw/external data entering the dbt project.

They provide:

• lineage
• documentation
• freshness checks
• centralized source definitions

32. What are dbt materializations?

Common materializations:

View

materialized: view

Table

materialized: table

Incremental

materialized: incremental

Ephemeral

materialized: ephemeral

Dynamic/custom approaches

Depending on platform and dbt version, additional materialization patterns may exist.

33. View vs table vs incremental

View

Good for:

• lightweight transformations
• frequently changing logic
• relatively small datasets

Table

Good for:

• expensive transformations
• frequently queried datasets
• stable models

Incremental

Good for:

• very large datasets
• append/change-based processing
• reducing repeated computation

Senior answer:

I select materialization based on data volume, transformation cost, query frequency, freshness requirements, warehouse cost, and update semantics.

34. Explain an incremental dbt model.

Example:

{{ config(
    materialized='incremental',
    unique_key='order_id'
) }}

SELECT
    order_id,
    customer_id,
    amount,
    updated_at
FROM {{ source('sales', 'orders') }}

{% if is_incremental() %}

WHERE updated_at >
(
    SELECT COALESCE(MAX(updated_at), '1900-01-01')
    FROM {{ this }}
)

{% endif %}

The key consideration is how you identify changed records.

Possible strategies:

• timestamp watermark
• CDC
• ingestion sequence
• batch ID
• hash comparison

35. What is is_incremental()?

It allows dbt SQL to behave differently when the model is being incrementally built.

{% if is_incremental() %}
    WHERE updated_at > (
        SELECT MAX(updated_at)
        FROM {{ this }}
    )
{% endif %}

When doing a full refresh, the incremental condition isn’t applied.

36. What is unique_key in an incremental model?

It tells dbt what identifies a logical record.

Example:

unique_key: order_id

Depending on the adapter and strategy, this can support update/merge behavior rather than simply appending records.

A strong candidate should mention that exact behavior depends on the warehouse/adapter and incremental strategy.

37. What is an incremental strategy?

Common strategies include concepts such as:

• append
• merge
• delete+insert
• insert overwrite

The appropriate strategy depends on:

• source change patterns
• warehouse
• partitioning
• update frequency
• late-arriving data
• delete requirements

38. How would you handle late-arriving data in dbt?

Suppose:

Today's data arrives
but yesterday's records can still change.

A naïve:

WHERE updated_at > MAX(updated_at)

may miss certain corrections depending on the watermark logic.

A common approach is a lookback window:

WHERE updated_at >=
    DATEADD(day, -2, CURRENT_DATE)

Then use a MERGE/appropriate incremental strategy to overwrite affected records.

For high-integrity pipelines, I would also consider CDC or a reliable source-side change sequence.

39. How do you handle deletes in dbt?

Options include:

CDC

Consume delete events.

Soft delete

Maintain:

is_deleted = true

Merge logic

Synchronize target state with source state.

Snapshot

Track historical changes where appropriate.

The correct choice depends on whether the business needs:

• current state
• historical state
• deletion history
• auditability

40. What are dbt snapshots?

Snapshots capture changes to records over time.

They are commonly used for SCD Type 2-style historical tracking.

Conceptually:

Customer
   ↓
Address changes
   ↓
Snapshot
   ↓
Historical versions

A snapshot can preserve previous versions instead of simply overwriting them.

41. Snapshot vs incremental model

Incremental

Optimizes processing.

Snapshot

Captures historical changes.

They solve different problems.

An incremental model asks:

How can I process only the data that needs processing?

A snapshot asks:

How can I preserve historical versions of records as they change?

42. What are dbt tests?

Tests validate assumptions about data.

Common built-in generic tests include:

not_null
unique
accepted_values
relationships

Example:

columns:
  - name: customer_id
    tests:
      - not_null
      - unique

43. What is a singular test?

A singular test is a custom SQL query designed to return records that violate a business rule.

Example:

SELECT *
FROM {{ ref('orders') }}
WHERE amount < 0;

If this returns rows, the test fails.

44. Generic test vs singular test

Generic

Reusable.

Example:

- unique

Singular

Specific business rule.

Example:

Order amount cannot be negative.

Senior engineers often create reusable generic tests for common patterns and singular tests for domain-specific rules.

45. How would you design dbt project structure?

A common structure:

models/
│
├── staging/
│   ├── customers/
│   ├── orders/
│
├── intermediate/
│   ├── customer_orders/
│
└── marts/
    ├── finance/
    ├── sales/
    └── customer/

Principle:

Raw
 ↓
Staging
 ↓
Intermediate
 ↓
Business Marts

Avoid putting massive business logic directly into staging models.

46. What should staging models contain?

Typically:

• source renaming
• type casting
• basic cleansing
• standardization
• simple derived fields

Avoid excessive business logic.

Example:

SELECT
    CAST(order_id AS BIGINT) AS order_id,
    CAST(customer_id AS BIGINT) AS customer_id,
    CAST(order_date AS DATE) AS order_date,
    amount
FROM {{ source('sales', 'orders') }}

47. What belongs in marts?

Marts should represent business concepts.

Examples:

dim_customer
dim_product
fct_orders
fct_claims
customer_360
monthly_revenue

They should be understandable to downstream consumers.

48. What are dbt macros?

Macros are reusable Jinja code.

Example:

{% macro cents_to_dollars(column_name) %}

    {{ column_name }} / 100.0

{% endmacro %}

Then:

SELECT
    {{ cents_to_dollars('amount_cents') }} AS amount
FROM orders

Macros reduce repetitive SQL.

49. When should you NOT use a macro?

Don’t create macros simply because you can.

Avoid macros when:

• logic is only used once
• abstraction makes SQL harder to understand
• business logic becomes hidden
• debugging becomes difficult

Senior principle:

Abstract repeated technical patterns, but keep business logic explicit where readability matters.

50. Explain Jinja in dbt.

Jinja allows dynamic SQL generation.

Example:

{% if target.name == 'prod' %}

    SELECT *
    FROM production.orders

{% else %}

    SELECT *
    FROM development.orders

{% endif %}

Common constructs:

{% if %}
{% for %}
{% set %}
{{ variable }}
{{ ref() }}
{{ source() }}

51. What is dbt run?

Runs models.

Example:

dbt run

You can selectively execute:

dbt run --select model_name

Or dependencies:

dbt run --select +model_name

Or downstream:

dbt run --select model_name+

52. What is dbt build?

dbt build is broader than dbt run.

It can execute relevant resources such as:

• models
• tests
• snapshots
• seeds

and respects the DAG/dependencies.

This makes it particularly useful in CI/CD pipelines where you want transformation and validation together.

53. dbt run vs dbt build

Senior answer:

dbt run focuses on executing models, while dbt build orchestrates multiple dbt resource types according to their dependencies, including tests. For CI/CD validation, dbt build is often more appropriate because it can validate the generated data as part of the workflow.

54. How do you optimize a slow dbt project?

I would investigate:

1. Model materialization

Could an expensive view become a table?

2. Incremental processing

Can a huge table be processed incrementally?

3. DAG design

Are models unnecessarily rebuilding?

4. SQL

Inspect warehouse query plans.

5. Warehouse configuration

Evaluate:

• compute size
• concurrency
• clustering/partitioning
• caching

6. Dependency design

Avoid unnecessary fan-out.

7. Incremental predicates

Process only relevant partitions/records.

55. How do you prevent dbt DAG complexity?

I establish conventions:

sources
  ↓
staging
  ↓
intermediate
  ↓
marts

Then enforce:

• naming standards
• ownership
• model documentation
• code review
• dependency review
• test coverage
• domain boundaries

I also avoid circular dependencies and excessive model chaining that provides little business or technical value.

56. How would you implement CI/CD for dbt?

Typical workflow:

Developer
   ↓
Git branch
   ↓
Pull Request
   ↓
Lint
   ↓
Compile
   ↓
dbt build
   ↓
Tests
   ↓
Review
   ↓
Merge
   ↓
Production deployment

Production should include:

• environment-specific credentials
• secrets management
• automated testing
• deployment approvals where required
• artifact/log retention
• rollback strategy

PART III — ADVANCED PYTHON

57. What Python data structures do you use most?

List

Ordered, mutable collection.

items = [1, 2, 3]

Tuple

Ordered, immutable.

items = (1, 2, 3)

Set

Unique elements.

items = {1, 2, 3}

Dictionary

Key-value mapping.

data = {
    "customer_id": 101
}

For data engineering, dictionaries and sets are particularly useful for:

• lookups
• deduplication
• joins in memory
• configuration
• indexing

58. List vs tuple

List:

[1, 2, 3]

Tuple:

(1, 2, 3)

Lists are mutable.

Tuples are immutable.

Tuples can also be used as dictionary keys when their contents are hashable.

59. What is a dictionary comprehension?

squares = {
    x: x * x
    for x in range(10)
}

Equivalent conceptually to:

squares = {}

for x in range(10):
    squares[x] = x * x

Use comprehensions when they improve readability; avoid excessively complicated one-liners.

60. What is a generator?

A generator produces values lazily.

def read_numbers():
    for i in range(1000000):
        yield i

Instead of loading one million values into memory, values are generated as needed.

Useful for:

• large files
• streaming data
• ETL
• memory-efficient processing

61. List vs generator

List:

data = [process(x) for x in records]

Generator:

data = (process(x) for x in records)

The generator is lazy.

For very large datasets, this can substantially reduce memory consumption.

62. What is a decorator?

A decorator modifies or wraps function behavior.

def logger(func):

    def wrapper(*args, **kwargs):
        print("Calling function")
        result = func(*args, **kwargs)
        print("Completed")
        return result

    return wrapper

Usage:

@logger
def process_data():
    pass

Common uses:

• logging
• authorization
• retry
• caching
• timing
• instrumentation

63. What is *args and **kwargs?

def function(*args, **kwargs):
    print(args)
    print(kwargs)

*args captures positional arguments.

**kwargs captures keyword arguments.

Useful for wrappers/decorators and flexible APIs.

64. Explain Python exception handling.

try:
    result = process()
except ValueError as e:
    logger.error("Invalid value: %s", e)
except Exception as e:
    logger.exception("Unexpected failure")
finally:
    cleanup()

Best practices:

• catch specific exceptions
• don’t silently swallow errors
• preserve context
• log appropriately
• use retries only for retryable failures

Avoid:

except:
    pass

65. What is a context manager?

Example:

with open("data.txt") as f:
    data = f.read()

The context manager ensures cleanup.

Useful for:

• files
• database connections
• locks
• transactions
• resource management

66. What is the GIL?

The Global Interpreter Lock in CPython allows only one thread at a time to execute Python bytecode within a process.

This means:

• threading is useful for many I/O-bound workloads
• CPU-bound Python workloads may not scale through threads in the same way

For CPU-heavy work, options include:

• multiprocessing
• native extensions
• distributed processing
• vectorized libraries

The exact behavior depends on Python implementation/version and workload.

67. Threading vs multiprocessing vs asyncio

Threading

Good for:

I/O-bound tasks

Example:

API calls
database calls
file operations

Multiprocessing

Useful for:

CPU-bound workloads

Asyncio

Good for large numbers of asynchronous I/O operations.

Example:

1000 API requests

68. How would you process a 100 GB CSV using Python?

I would not simply do:

pandas.read_csv("100gb.csv")

because memory requirements could be excessive.

Options:

Chunking

for chunk in pd.read_csv(
    "100gb.csv",
    chunksize=100000
):
    process(chunk)

Other options:

• PyArrow
• Polars
• Spark
• distributed processing
• database bulk loading

Senior answer:

First I would determine whether Python is actually the appropriate processing engine. For a 100 GB transformation workload, I would generally prefer pushing computation into a warehouse/lakehouse or using distributed processing rather than forcing the entire workload through a single Python process.

69. How do you optimize Python performance?

I follow:

Measure
 ↓
Profile
 ↓
Identify bottleneck
 ↓
Optimize
 ↓
Benchmark

Techniques:

• avoid unnecessary loops
• use appropriate data structures
• vectorize operations
• generators
• batching
• caching
• parallelism
• efficient I/O
• database-side processing

Do not optimize blindly.

70. Why are sets useful?

Set membership:

if customer_id in customer_ids:
    ...

Average-case membership lookup is typically O(1).

Compared with list membership:

if customer_id in customer_list:

which is typically O(n).

This can matter significantly for large in-memory lookup operations.

71. What is the difference between shallow copy and deep copy?

import copy

a = [[1, 2], [3, 4]]

b = copy.copy(a)
c = copy.deepcopy(a)

Shallow copy copies the outer structure but nested objects may remain shared.

Deep copy recursively copies nested objects.

72. Explain Python memory management.

CPython primarily uses:

• reference counting
• cyclic garbage collection

Objects are allocated on the heap.

When reference counts reach zero, objects can generally be reclaimed immediately; cyclic references require garbage collection mechanisms.

Senior candidates should understand that memory leaks can still occur through:

• retained references
• caches
• global structures
• unbounded collections
• external resources

PART IV — PYTHON + SQL

73. How do you connect Python to a database?

Depending on the platform:

SQLAlchemy
psycopg
pyodbc
database-specific connectors

Example conceptually:

from sqlalchemy import create_engine

engine = create_engine(connection_string)

df = pd.read_sql(
    "SELECT * FROM customers",
    engine
)

In production:

• credentials should come from a secrets manager
• connection pooling should be considered
• parameterized queries should be used
• transactions should be managed explicitly

74. How do you prevent SQL injection in Python?

Never concatenate user input:

query = f"""
SELECT *
FROM users
WHERE id = {user_id}
"""

Instead use parameterized queries:

cursor.execute(
    "SELECT * FROM users WHERE id = ?",
    (user_id,)
)

Exact placeholder syntax depends on the database driver.

75. How would you load a Pandas DataFrame into a database?

Possible approaches:

df.to_sql(...)

or database-native bulk loading.

For large datasets, I would generally prefer:

DataFrame
 ↓
Object storage / staged file
 ↓
Bulk-load mechanism
 ↓
Database

rather than millions of individual INSERT statements.

76. How would you process a large API response?

I would consider:

• pagination
• streaming
• batching
• retries
• exponential backoff
• rate limiting
• checkpointing
• schema validation
• incremental writes

Architecture:

API
 ↓
Pagination
 ↓
Batch
 ↓
Validate
 ↓
Transform
 ↓
Persist
 ↓
Checkpoint

77. How do you implement retry logic?

Conceptually:

for attempt in range(max_retries):
    try:
        response = call_api()
        break
    except RetryableError:
        sleep(backoff(attempt))

A production implementation should distinguish:

Retryable

• timeout
• transient network error
• HTTP 429
• selected 5xx errors

Non-retryable

• authentication failure
• malformed request
• validation failure

Use exponential backoff and jitter where appropriate.

PART V — SENIOR SCENARIO QUESTIONS

78. A dbt pipeline suddenly takes 3 hours instead of 20 minutes. What do you do?

I would not immediately increase warehouse size.

First:

1. Identify what changed

Code?
Data volume?
Warehouse?
Source schema?
Execution concurrency?

2. Inspect dbt artifacts/logs

Identify the slowest models.

3. Inspect query plans

Look for:

• full scans
• changed join strategy
• skew
• spills
• poor statistics
• increased cardinality

4. Check incremental behavior

A model may accidentally be doing a full refresh.

5. Check upstream data growth

Maybe data increased from:

100M → 1B rows

6. Fix root cause

Then benchmark again.

79. Your incremental dbt model is producing duplicates. How do you troubleshoot?

Check:

1. Is unique_key actually unique?
2. Is the incremental filter correct?
3. Are late-arriving records being reprocessed?
4. Are source records duplicated?
5. Is the incremental strategy appropriate?
6. Are multiple source records matching the same target?
7. Is there concurrent execution?
8. Did someone perform a partial/full refresh?

Then create a test:

SELECT
    order_id,
    COUNT(*)
FROM {{ ref('fct_orders') }}
GROUP BY order_id
HAVING COUNT(*) > 1;

80. A business user says yesterday’s revenue changed. How do you investigate?

I would establish lineage:

Dashboard
 ↓
Semantic model
 ↓
dbt mart
 ↓
Intermediate model
 ↓
Staging
 ↓
Source

Then reconcile counts and amounts at each layer.

Check:

• source corrections
• late-arriving records
• duplicate events
• timezone issues
• joins
• currency conversions
• incremental processing
• snapshot logic

This is much stronger than simply rerunning the pipeline.

81. How would you design a production data pipeline using SQL, dbt, and Python?

Example:

External APIs / Applications
          ↓
       Python
          ↓
   Raw Object Storage
          ↓
       Warehouse
          ↓
        dbt
          ↓
   Staging Models
          ↓
 Intermediate Models
          ↓
     Data Marts
          ↓
 BI / Analytics / ML

Python handles:

• ingestion
• API interaction
• file processing
• orchestration integrations

SQL/dbt handles:

• transformation
• modeling
• business logic
• data quality

Warehouse handles:

• scalable computation
• storage
• aggregation

82. Where should business logic live: Python or SQL?

My default preference for analytical transformation is:

SQL/dbt → warehouse transformation
Python → orchestration, ingestion, specialized processing

If a transformation can efficiently run in SQL inside the warehouse, I generally avoid extracting large datasets into Python unnecessarily.

Python becomes appropriate when:

• external API interaction is required
• specialized algorithms are needed
• complex non-SQL processing is required
• orchestration/integration is involved

83. How would you ensure idempotency?

Idempotency means running the same operation multiple times produces the same final state.

Techniques:

• deterministic business keys
• MERGE
• upserts
• checkpoints
• batch IDs
• deduplication
• source event IDs
• transaction boundaries

Example:

Event ID = 12345

First execution:
INSERT

Second execution:
Recognize 12345 already processed
→ no duplicate

This is a critical production-data-engineering concept.

84. How would you design data quality?

I would implement multiple layers:

Schema validation

Expected columns
Expected data types

dbt tests

not_null
unique
relationships
accepted_values

Business rules

Revenue >= 0
Order date <= current date

Reconciliation

Source count ≈ Target count
Source amount ≈ Target amount

Freshness

Latest source timestamp

Operational monitoring

Pipeline duration
Failure rate
Row counts
Anomalies

85. How do you handle schema changes?

For example:

Source adds customer_segment

I would determine:

1. Is it backward compatible?
2. Does downstream logic depend on the schema?
3. Do contracts/tests need updating?
4. Does the dbt model need modification?
5. Is historical backfill required?

For breaking changes, I prefer controlled versioning rather than allowing production pipelines to fail unexpectedly.

86. How do you handle a production dbt failure?

My incident approach:

Detect
 ↓
Assess impact
 ↓
Identify failing model
 ↓
Determine root cause
 ↓
Mitigate
 ↓
Recover
 ↓
Validate
 ↓
Communicate
 ↓
Prevent recurrence

Afterward:

• root cause analysis
• permanent fix
• regression test
• monitoring improvement
• documentation

PART VI — ARCHITECT / SENIOR MANAGER QUESTIONS

87. How do you establish SQL coding standards across a team?

I establish standards for:

• naming
• indentation
• CTE conventions
• aliases
• capitalization
• joins
• null handling
• date handling
• performance
• documentation

Then automate where possible through:

Linting
CI/CD
Code review
Pre-commit hooks
Automated tests

The goal is to make the correct approach the easiest approach.

88. How do you review a junior engineer’s SQL?

I evaluate:

Correctness

Does it produce the right result?

Performance

Does it scale?

Maintainability

Can another engineer understand it?

Data quality

Are edge cases handled?

Security

Could it expose sensitive data?

Testability

Can assumptions be validated?

I avoid rewriting everything myself. I explain the reasoning and coach the engineer.

89. How do you decide between dbt and stored procedures?

I generally prefer dbt for:

• modular transformations
• version-controlled SQL
• DAG management
• testing
• documentation
• lineage
• CI/CD

Stored procedures may still be appropriate for:

• procedural database operations
• complex transactional workflows
• database-native administrative operations
• legacy integrations

The decision should be based on architecture and workload rather than ideology.

90. How would you migrate legacy SQL pipelines to dbt?

I would use:

Inventory
 ↓
Dependency mapping
 ↓
Business logic identification
 ↓
Model classification
 ↓
Staging
 ↓
Intermediate
 ↓
Marts
 ↓
Tests
 ↓
Parallel validation
 ↓
Cutover

Critical requirement:

Do not migrate syntax blindly.

First understand what the legacy pipeline actually does.

91. How do you validate a dbt migration?

Run source-vs-target reconciliation:

Row count
Null count
Distinct keys
Aggregates
Min/max dates
Financial totals
Record-level samples

Example:

SELECT
    COUNT(*) AS row_count,
    SUM(amount) AS total_amount,
    COUNT(DISTINCT customer_id) AS customers
FROM legacy_table;

Compare against:

SELECT
    COUNT(*) AS row_count,
    SUM(amount) AS total_amount,
    COUNT(DISTINCT customer_id) AS customers
FROM {{ ref('new_model') }};

92. How would you explain technical debt in a dbt project?

Examples:

1000-line SQL models
No tests
No documentation
Hardcoded schemas
Duplicate business logic
Poor incremental strategy
Circular/complex dependencies
No CI/CD

I prioritize technical debt based on:

Business impact × Probability × Cost

Rather than attempting to clean everything simultaneously.

93. How do you mentor engineers in SQL/dbt/Python?

I use:

Code reviews

Focus on reasoning, not just syntax.

Pair programming

Work through difficult transformations together.

Standards

Provide templates and examples.

Design reviews

Discuss architecture before implementation.

Production learning

Use incidents as learning opportunities.

Progressive ownership

Give engineers increasingly complex components.

PART VII — VERY ADVANCED QUESTIONS

94. What happens when a dbt incremental model’s watermark is corrupted?

Suppose:

MAX(updated_at)

incorrectly moves forward.

The pipeline may permanently skip records.

Solutions include:

• source CDC sequence
• durable ingestion watermark
• lookback windows
• audit tables
• checkpoint validation
• periodic reconciliation
• controlled backfills

Senior insight:

A watermark is an operational state, not merely a query predicate. It needs observability and recovery mechanisms.

95. How would you handle a billion-row fact table?

I would consider:

Storage

• partitioning
• clustering
• compression

Transformation

• incremental models
• partition pruning
• predicate pushdown
• early filtering

Query design

• avoid unnecessary scans
• pre-aggregate where justified
• avoid Cartesian joins

Data model

Fact
 ↓
Dimensions
 ↓
Aggregates / marts

Operational

• workload isolation
• concurrency management
• monitoring
• cost controls

96. How do you handle data skew?

Suppose:

Customer A → 500 million records
Customer B → 10 records

This can cause uneven processing.

Potential solutions:

• identify skewed keys
• repartition appropriately
• isolate extreme keys
• pre-aggregate
• change join strategy
• use warehouse-specific clustering/partitioning techniques

97. How would you troubleshoot a SQL query that works in development but fails in production?

Check:

Data volume
Schema differences
Permissions
Statistics
Indexes
Warehouse size
Concurrency
Parameter distribution
NULL patterns
Data skew
Execution plan

A query can behave completely differently at production scale.

98. How do you balance performance vs maintainability?

I use this hierarchy:

Correctness
   ↓
Maintainability
   ↓
Performance
   ↓
Cost optimization

But in production, all four matter.

I avoid premature optimization.

When optimization is necessary, I document:

Problem
Baseline
Change
Result
Trade-off

99. What makes someone genuinely “advanced” in SQL?

A senior candidate shouldn’t simply know:

JOIN
GROUP BY
CTE
WINDOW FUNCTIONS

They should understand:

Query optimizer
Execution plans
Cardinality
Indexes
Partition pruning
Transactions
Concurrency
Data modeling
Performance
Cost
Data correctness

And most importantly:

They should know why one implementation is better than another.

100. What makes someone genuinely advanced in dbt?

An advanced dbt engineer understands:

DAG design
Incremental processing
Snapshots
Materializations
Jinja
Macros
Testing
Contracts
Sources
Freshness
Lineage
CI/CD
Environment management
Warehouse optimization
Data governance

But the strongest candidates understand that dbt is not the data warehouse.

dbt orchestrates and manages transformations; the underlying warehouse/lakehouse executes the SQL.

101. What makes someone genuinely advanced in Python for data engineering?

They should understand:

Data structures
Algorithms
OOP
Functional concepts
Generators
Decorators
Context managers
Exceptions
Typing
Testing
Logging
Concurrency
Async I/O
Memory management
Performance
APIs
Database connectivity
Packaging

But they should also know when not to use Python.

For example:

If I need to aggregate 5 TB of warehouse data, I would generally push that computation to the warehouse instead of downloading the data into Python.

That answer demonstrates architectural maturity.

PART VIII — TOP 20 QUESTIONS YOU SHOULD ABSOLUTELY MASTER

If your interview is for a Senior Technical Manager / Senior Data Engineer position, prioritize these:

1. How do you optimize a complex SQL query?
2. How do you read an execution plan?
3. Explain window functions and window frames.
4. How do you implement SCD Type 2?
5. How do you handle duplicate and late-arriving data?
6. Explain transaction isolation and deadlocks.
7. Explain indexing and composite indexes.
8. What is SARGability?
9. Explain dbt DAG architecture.
10. ref() vs source().
11. Incremental models and unique_key.
12. How do you handle late-arriving data in dbt?
13. Snapshot vs incremental model.
14. How do you design dbt tests?
15. How do you optimize a slow dbt project?
16. How do you implement dbt CI/CD?
17. How do you process huge datasets with Python?
18. Threading vs multiprocessing vs asyncio.
19. How do you build idempotent pipelines?
20. Design an end-to-end production SQL + dbt + Python data platform.

PART IX — THE “DESIGN THIS” INTERVIEW QUESTION

A very likely senior-level question is:

“Design a production-grade data platform using SQL, dbt, and Python.”

A strong answer would look like:

                    ┌───────────────────┐
                    │ APIs / Applications│
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │      Python       │
                    │ Ingestion Layer   │
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │   Raw Storage     │
                    │ Immutable Landing  │
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │ Warehouse/Lakehouse│
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │       dbt         │
                    │    Staging        │
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │   Intermediate    │
                    │    Transformations│
                    └─────────┬─────────┘
                              │
                              ▼
                    ┌───────────────────┐
                    │ Business Marts    │
                    │ Facts + Dimensions│
                    └─────────┬─────────┘
                              │
                ┌─────────────┼──────────────┐
                ▼             ▼              ▼
              Power BI      Analytics         ML

Then discuss:

Reliability

• idempotency
• retries
• checkpoints
• data quality
• reconciliation

Security

• secrets management
• RBAC
• least privilege
• encryption
• PII controls

Performance

• incremental models
• partitioning
• clustering
• predicate pushdown
• query optimization

Governance

• lineage
• ownership
• documentation
• contracts
• auditability

DevOps

Git
 ↓
Pull Request
 ↓
CI
 ↓
dbt build
 ↓
Tests
 ↓
Approval
 ↓
Production

Observability

Monitor:

Pipeline success/failure
Runtime
Freshness
Row counts
Duplicates
Null rates
Data drift
Warehouse cost

PART X — HOW I WOULD ANSWER AS A SENIOR TECHNICAL MANAGER

For scenario questions, don’t answer only with syntax.

Use this framework:

1. Clarify the requirement

What is the business requirement and SLA?

2. Understand the data

What is the volume, velocity, source behavior and update pattern?

3. Design

I would separate ingestion, transformation and consumption responsibilities.

4. Choose technology

Python for ingestion/integration, SQL/dbt for warehouse transformations.

5. Address scalability

Incremental processing, partition pruning, appropriate materialization and warehouse optimization.

6. Address reliability

Idempotency, retries, checkpoints and reconciliation.

7. Address security

Least privilege, secrets management and controlled access.

8. Address observability

Logging, metrics, freshness and data-quality monitoring.

9. Explain trade-offs

Explain why your design is better than the alternatives.

10. Explain operational ownership

How will the team deploy, monitor, troubleshoot and maintain it?

That last part is what often separates a senior engineer from a senior technical manager/architect.

Recommended preparation sequence

Given the breadth of this requirement, I would prepare in this order:

Phase 1 — SQL

JOINs
→ CTEs
→ Window Functions
→ Subqueries
→ Query Optimization
→ Execution Plans
→ Indexes
→ Transactions
→ SCD
→ Advanced Data Modeling

Phase 2 — dbt

Models
→ Sources
→ ref/source
→ DAG
→ Materializations
→ Incremental Models
→ Snapshots
→ Tests
→ Macros/Jinja
→ CI/CD
→ Performance
→ Production Troubleshooting

Phase 3 — Python

Data Structures
→ OOP
→ Functions
→ Generators
→ Decorators
→ Exceptions
→ Pandas
→ APIs
→ SQL Integration
→ Concurrency
→ Testing
→ Performance

Phase 4 — Senior/Manager

Architecture
→ System Design
→ Data Quality
→ Security
→ Cost
→ Observability
→ Incident Management
→ Code Reviews
→ Mentoring
→ Technical Leadership

For an actual senior interview, I would not stop at memorizing these answers. The highest-value next step is to practice hands-on SQL coding problems + dbt case studies + Python coding exercises + 10–15 production architecture scenarios, because that is where interviewers usually differentiate candidates.