Analytics engineer coding interview questions

As asked

You have a table of daily sales with columns date and amount. Write a SQL query that returns, for each date, the cumulative total amount since the beginning of the dataset and the 7-day moving average of amount.

Sample answer outline

The cumulative total uses SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The 7-day moving average uses AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). A strong answer notes that ROWS BETWEEN is preferred over RANGE BETWEEN to avoid unexpected behavior with duplicate dates, and that the first 6 rows will have a moving average over fewer than 7 days.

Reference implementation (sql)

-- daily_sales(date DATE, amount NUMERIC)
SELECT
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales
ORDER BY date;

Expect these follow-ups

• How would you handle gaps in the date series where no sales were recorded?
• What changes if you need the 7-day moving average to always average exactly 7 days, filling missing days with zero?

As asked

A source table has multiple rows per customer because each update appends a new row with an updated_at timestamp. Write a SQL query to return only the most recent row for each customer.

Sample answer outline

The canonical approach uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and filters for rn = 1 in an outer query. An equally valid approach uses a subquery with MAX(updated_at) grouped by customer_id and joins back to the original table. A strong answer also considers ties on updated_at and adds a tiebreaker like a sequence ID.

Reference implementation (sql)

-- customers(customer_id, name, plan, updated_at)
SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customers
)
WHERE rn = 1;

Expect these follow-ups

• How does this pattern change if you want the latest record per customer per month?
• What is the performance implication of ROW_NUMBER() versus a MAX subquery on a large table in Snowflake?

As asked

You have a table of user events with user_id and event_timestamp. Define a session as a sequence of events where no two consecutive events for the same user are more than 30 minutes apart. Write a SQL query to assign a session_id to each event.

Sample answer outline

First compute the time gap to the previous event per user using LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp). Then flag rows where the gap exceeds 30 minutes or where there is no prior event as a session start. Finally use SUM(is_session_start) OVER (PARTITION BY user_id ORDER BY event_timestamp) as a running count to assign the session number, and concatenate with user_id to form a unique session_id. A strong answer also handles NULL lag values for the first event per user.

Reference implementation (sql)

-- user_events(user_id, event_timestamp)
WITH gaps AS (
  SELECT
    user_id,
    event_timestamp,
    LAG(event_timestamp) OVER (
      PARTITION BY user_id ORDER BY event_timestamp
    ) AS prev_ts
  FROM user_events
),
session_starts AS (
  SELECT
    *,
    CASE
      WHEN prev_ts IS NULL
        OR DATEDIFF('minute', prev_ts, event_timestamp) > 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM gaps
)
SELECT
  user_id,
  event_timestamp,
  user_id || '-' || SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_timestamp
  ) AS session_id
FROM session_starts;

Expect these follow-ups

• How would you extend this to compute session duration and event count per session?
• What happens to your sessionization logic if events can arrive out of order in the source table?

As asked

You have a table with columns product, metric_name, and metric_value where each product has multiple rows for metrics like revenue, units_sold, and margin. Write a SQL query that returns one row per product with each metric as a separate column.

Sample answer outline

Use conditional aggregation with SUM(CASE WHEN metric_name = 'revenue' THEN metric_value END) AS revenue for each metric. In Snowflake you can also use the native PIVOT syntax. A strong answer notes that conditional aggregation is more portable across warehouses, that PIVOT requires knowing the column names at query-write time, and how to handle NULLs when a product is missing a metric.

Reference implementation (sql)

-- product_metrics(product, metric_name, metric_value)
SELECT
  product,
  SUM(CASE WHEN metric_name = 'revenue'
      THEN metric_value END)     AS revenue,
  SUM(CASE WHEN metric_name = 'units_sold'
      THEN metric_value END)     AS units_sold,
  SUM(CASE WHEN metric_name = 'margin'
      THEN metric_value END)     AS margin
FROM product_metrics
GROUP BY product;

Expect these follow-ups

• How would you generate this pivot dynamically if the list of metric names is not known at query-write time?
• What is the Snowflake PIVOT syntax and what does it require?

As asked

Given a table of user logins with user_id and login_date, write a SQL query that produces a cohort retention table showing what percentage of users from each weekly signup cohort were still active in weeks 1, 2, 3, and 4 after signup.

Sample answer outline

First identify each user's cohort week from their first login. Then join activity events to the cohort table and compute the week number offset as DATEDIFF('week', cohort_week, login_date). Group by cohort_week and week_offset to count active users, then divide by the cohort size for retention rate. Pivot week offsets into columns with conditional aggregation. A strong answer handles users who were active in week 2 but not week 1, clarifies whether retention is defined as any activity or a specific event, and chooses the right week date truncation.

Reference implementation (sql)

-- logins(user_id, login_date)
WITH cohorts AS (
  SELECT user_id,
    DATE_TRUNC('week', MIN(login_date)) AS cohort_week
  FROM logins
  GROUP BY user_id
),
activity AS (
  SELECT l.user_id, c.cohort_week,
    DATEDIFF('week', c.cohort_week, l.login_date) AS week_num
  FROM logins l
  JOIN cohorts c USING (user_id)
  WHERE DATEDIFF('week', c.cohort_week, l.login_date) BETWEEN 0 AND 4
)
SELECT
  cohort_week,
  COUNT(DISTINCT user_id) AS cohort_size,
  COUNT(DISTINCT CASE WHEN week_num = 1 THEN user_id END) * 100.0
    / COUNT(DISTINCT user_id) AS week_1_retention
FROM activity
GROUP BY cohort_week
ORDER BY cohort_week;

Expect these follow-ups

• How would you modify this query to show day-over-day retention for a 30-day window?
• What does a retention curve that flattens after week 4 tell you about your product?

As asked

When would you write a dbt Python model instead of a SQL model, and what are the limitations you need to be aware of?

Sample answer outline

Python models are useful when the logic requires ML inference, string parsing with complex regex, calling external APIs, or operations that are awkward in SQL like string similarity matching or nested JSON manipulation. In a dbt Python model you write a function that receives a DataFrame via the dbt.ref() helper and returns a transformed DataFrame. The warehouse executes this using Snowpark (Snowflake) or Dataproc (BigQuery), so the Python runs server-side. Limitations include higher latency than SQL models, dependency management for Python packages, limited interactive debugging, and no support for all incremental strategies.

Reference implementation (python)

# dbt Python model example
import pandas as pd
from sklearn.preprocessing import StandardScaler

def model(dbt, session):
    dbt.config(materialized='table')
    df = dbt.ref('fct_customer_features').to_pandas()
    scaler = StandardScaler()
    df[['scaled_spend']] = scaler.fit_transform(df[['total_spend']])
    return df

Expect these follow-ups

• How do you manage Python package dependencies in a dbt Python model on Snowflake?
• What is the execution environment for a dbt Python model on BigQuery, and how does it differ from Snowflake Snowpark?

As asked

A BigQuery table has a column called items of type ARRAY<STRUCT<product_id STRING, quantity INT64, price FLOAT64>>. Write a query that returns one row per item across all orders.

Sample answer outline

Use CROSS JOIN UNNEST(items) AS item to explode the array, then reference item.product_id, item.quantity, and item.price in the SELECT list alongside the order-level columns. A strong answer notes that UNNEST drops orders with empty arrays (like an INNER JOIN behavior) and that LEFT JOIN UNNEST handles empty arrays with NULLs. Also covers how to use OFFSET in UNNEST to get the array position.

Reference implementation (sql)

-- orders(order_id, customer_id,
--   items ARRAY<STRUCT<product_id STRING, quantity INT64, price FLOAT64>>)
SELECT
  o.order_id,
  o.customer_id,
  item.product_id,
  item.quantity,
  item.price
FROM orders o
CROSS JOIN UNNEST(o.items) AS item;

Expect these follow-ups

• How does this pattern differ in Snowflake where the equivalent is LATERAL FLATTEN?
• What happens to an order row if its items array is empty or NULL?

As asked

A subscriptions table records each subscription period with a user_id, start_date, and end_date. A user can have multiple consecutive or overlapping periods. Write a SQL query that merges overlapping or adjacent periods into single continuous periods.

Sample answer outline

Use a window function to find the maximum end_date seen so far before the current row (MAX(end_date) OVER with ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), then mark a row as a new island if its start_date is greater than the running maximum end_date. Assign island numbers with a cumulative sum, then GROUP BY user_id and island to get MIN(start_date) and MAX(end_date) per merged period. A strong answer handles same-day gaps between periods correctly and discusses the difference between strictly overlapping versus adjacent periods.

Reference implementation (sql)

-- subscriptions(user_id, start_date, end_date)
WITH ordered AS (
  SELECT
    user_id, start_date, end_date,
    MAX(end_date) OVER (
      PARTITION BY user_id
      ORDER BY start_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_max_end
  FROM subscriptions
),
islands AS (
  SELECT *,
    SUM(CASE WHEN start_date > prev_max_end OR prev_max_end IS NULL
             THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY start_date) AS island
  FROM ordered
)
SELECT user_id, island,
  MIN(start_date) AS period_start,
  MAX(end_date) AS period_end
FROM islands
GROUP BY user_id, island;

Expect these follow-ups

• How would you handle the case where two subscription periods share exactly the same end date and start date?
• What is the time complexity of this approach versus a procedural solution?

As asked

Some older warehouse versions do not have MEDIAN() or PERCENTILE_CONT(). Write a SQL query to compute the median order value from an orders table using window functions.

Sample answer outline

Assign a row number and count total rows. The median is the middle value for odd counts, or the average of the two middle values for even counts. Using ROW_NUMBER() OVER (ORDER BY order_value) and COUNT(*) OVER () you can select rows where row_number IN (floor((count+1)/2.0), ceil((count+1)/2.0)) and average them. For example, with 4 rows this selects row numbers 2 and 3, which are the two middle values. Using floor(count/2) and ceil(count/2) is a common mistake that returns only a single row for even-count inputs. Alternatively, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) is the standard SQL way. A strong answer knows both approaches and explains when the manual approach is needed.

Reference implementation (sql)

-- orders(order_id, order_value)
SELECT
  AVG(order_value) AS median_order_value
FROM (
  SELECT
    order_value,
    ROW_NUMBER() OVER (ORDER BY order_value) AS rn,
    COUNT(*) OVER () AS total
  FROM orders
)
WHERE rn IN (FLOOR((total + 1) / 2.0),
             CEIL((total + 1) / 2.0));

Expect these follow-ups

• How does PERCENTILE_CONT differ from PERCENTILE_DISC?
• What is the most efficient way to compute multiple percentiles (p50, p90, p99) in a single query pass?

As asked

Write a dbt macro called assert_completeness that takes a model name and a list of columns, and generates SQL that returns any row where any of those columns is NULL. The macro should be callable from a singular test file.

Sample answer outline

The macro uses Jinja to iterate over the columns list and build a WHERE clause with OR conditions for each column being NULL. The SELECT returns the primary key plus the offending column values so the user can identify which rows fail. A strong answer uses dbt's ref() inside the macro to compile the model name correctly, returns zero rows on pass, and adds a meaningful alias to indicate which column failed.

Reference implementation (sql)

{% macro assert_completeness(model, columns) %}
SELECT *
FROM {{ ref(model) }}
WHERE
  {% for col in columns %}
    {{ col }} IS NULL
    {% if not loop.last %} OR {% endif %}
  {% endfor %}
{% endmacro %}

-- Usage in a singular test file:
{{ assert_completeness(
     model='dim_customers',
     columns=['customer_id', 'email', 'created_at']
   ) }}

Expect these follow-ups

• How would you modify this macro to accept a severity parameter and set it as a dbt test severity?
• What is the difference between a singular test and using this macro as a generic test?

As asked

You have a table of daily sales with columns date and amount. Write a SQL query that returns, for each date, the cumulative total amount since the beginning of the dataset and the 7-day moving average of amount.

Sample answer outline

The cumulative total uses SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The 7-day moving average uses AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). A strong answer notes that ROWS BETWEEN is preferred over RANGE BETWEEN to avoid unexpected behavior with duplicate dates, and that the first 6 rows will have a moving average over fewer than 7 days.

Reference implementation (sql)

-- daily_sales(date DATE, amount NUMERIC)
SELECT
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales
ORDER BY date;

Expect these follow-ups

• How would you handle gaps in the date series where no sales were recorded?
• What changes if you need the 7-day moving average to always average exactly 7 days, filling missing days with zero?

As asked

A source table has multiple rows per customer because each update appends a new row with an updated_at timestamp. Write a SQL query to return only the most recent row for each customer.

Sample answer outline

The canonical approach uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and filters for rn = 1 in an outer query. An equally valid approach uses a subquery with MAX(updated_at) grouped by customer_id and joins back to the original table. A strong answer also considers ties on updated_at and adds a tiebreaker like a sequence ID.

Reference implementation (sql)

-- customers(customer_id, name, plan, updated_at)
SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customers
)
WHERE rn = 1;

Expect these follow-ups

• How does this pattern change if you want the latest record per customer per month?
• What is the performance implication of ROW_NUMBER() versus a MAX subquery on a large table in Snowflake?

As asked

You have a table of user events with user_id and event_timestamp. Define a session as a sequence of events where no two consecutive events for the same user are more than 30 minutes apart. Write a SQL query to assign a session_id to each event.

Sample answer outline

First compute the time gap to the previous event per user using LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp). Then flag rows where the gap exceeds 30 minutes or where there is no prior event as a session start. Finally use SUM(is_session_start) OVER (PARTITION BY user_id ORDER BY event_timestamp) as a running count to assign the session number, and concatenate with user_id to form a unique session_id. A strong answer also handles NULL lag values for the first event per user.

Reference implementation (sql)

-- user_events(user_id, event_timestamp)
WITH gaps AS (
  SELECT
    user_id,
    event_timestamp,
    LAG(event_timestamp) OVER (
      PARTITION BY user_id ORDER BY event_timestamp
    ) AS prev_ts
  FROM user_events
),
session_starts AS (
  SELECT
    *,
    CASE
      WHEN prev_ts IS NULL
        OR DATEDIFF('minute', prev_ts, event_timestamp) > 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM gaps
)
SELECT
  user_id,
  event_timestamp,
  user_id || '-' || SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_timestamp
  ) AS session_id
FROM session_starts;

Expect these follow-ups

• How would you extend this to compute session duration and event count per session?
• What happens to your sessionization logic if events can arrive out of order in the source table?

As asked

You have a table with columns product, metric_name, and metric_value where each product has multiple rows for metrics like revenue, units_sold, and margin. Write a SQL query that returns one row per product with each metric as a separate column.

Sample answer outline

Use conditional aggregation with SUM(CASE WHEN metric_name = 'revenue' THEN metric_value END) AS revenue for each metric. In Snowflake you can also use the native PIVOT syntax. A strong answer notes that conditional aggregation is more portable across warehouses, that PIVOT requires knowing the column names at query-write time, and how to handle NULLs when a product is missing a metric.

Reference implementation (sql)

-- product_metrics(product, metric_name, metric_value)
SELECT
  product,
  SUM(CASE WHEN metric_name = 'revenue'
      THEN metric_value END)     AS revenue,
  SUM(CASE WHEN metric_name = 'units_sold'
      THEN metric_value END)     AS units_sold,
  SUM(CASE WHEN metric_name = 'margin'
      THEN metric_value END)     AS margin
FROM product_metrics
GROUP BY product;

Expect these follow-ups

• How would you generate this pivot dynamically if the list of metric names is not known at query-write time?
• What is the Snowflake PIVOT syntax and what does it require?

As asked

Given a table of user logins with user_id and login_date, write a SQL query that produces a cohort retention table showing what percentage of users from each weekly signup cohort were still active in weeks 1, 2, 3, and 4 after signup.

Sample answer outline

First identify each user's cohort week from their first login. Then join activity events to the cohort table and compute the week number offset as DATEDIFF('week', cohort_week, login_date). Group by cohort_week and week_offset to count active users, then divide by the cohort size for retention rate. Pivot week offsets into columns with conditional aggregation. A strong answer handles users who were active in week 2 but not week 1, clarifies whether retention is defined as any activity or a specific event, and chooses the right week date truncation.

Reference implementation (sql)

-- logins(user_id, login_date)
WITH cohorts AS (
  SELECT user_id,
    DATE_TRUNC('week', MIN(login_date)) AS cohort_week
  FROM logins
  GROUP BY user_id
),
activity AS (
  SELECT l.user_id, c.cohort_week,
    DATEDIFF('week', c.cohort_week, l.login_date) AS week_num
  FROM logins l
  JOIN cohorts c USING (user_id)
  WHERE DATEDIFF('week', c.cohort_week, l.login_date) BETWEEN 0 AND 4
)
SELECT
  cohort_week,
  COUNT(DISTINCT user_id) AS cohort_size,
  COUNT(DISTINCT CASE WHEN week_num = 1 THEN user_id END) * 100.0
    / COUNT(DISTINCT user_id) AS week_1_retention
FROM activity
GROUP BY cohort_week
ORDER BY cohort_week;

Expect these follow-ups

• How would you modify this query to show day-over-day retention for a 30-day window?
• What does a retention curve that flattens after week 4 tell you about your product?

As asked

When would you write a dbt Python model instead of a SQL model, and what are the limitations you need to be aware of?

Sample answer outline

Python models are useful when the logic requires ML inference, string parsing with complex regex, calling external APIs, or operations that are awkward in SQL like string similarity matching or nested JSON manipulation. In a dbt Python model you write a function that receives a DataFrame via the dbt.ref() helper and returns a transformed DataFrame. The warehouse executes this using Snowpark (Snowflake) or Dataproc (BigQuery), so the Python runs server-side. Limitations include higher latency than SQL models, dependency management for Python packages, limited interactive debugging, and no support for all incremental strategies.

Reference implementation (python)

# dbt Python model example
import pandas as pd
from sklearn.preprocessing import StandardScaler

def model(dbt, session):
    dbt.config(materialized='table')
    df = dbt.ref('fct_customer_features').to_pandas()
    scaler = StandardScaler()
    df[['scaled_spend']] = scaler.fit_transform(df[['total_spend']])
    return df

Expect these follow-ups

• How do you manage Python package dependencies in a dbt Python model on Snowflake?
• What is the execution environment for a dbt Python model on BigQuery, and how does it differ from Snowflake Snowpark?

As asked

A BigQuery table has a column called items of type ARRAY<STRUCT<product_id STRING, quantity INT64, price FLOAT64>>. Write a query that returns one row per item across all orders.

Sample answer outline

Use CROSS JOIN UNNEST(items) AS item to explode the array, then reference item.product_id, item.quantity, and item.price in the SELECT list alongside the order-level columns. A strong answer notes that UNNEST drops orders with empty arrays (like an INNER JOIN behavior) and that LEFT JOIN UNNEST handles empty arrays with NULLs. Also covers how to use OFFSET in UNNEST to get the array position.

Reference implementation (sql)

-- orders(order_id, customer_id,
--   items ARRAY<STRUCT<product_id STRING, quantity INT64, price FLOAT64>>)
SELECT
  o.order_id,
  o.customer_id,
  item.product_id,
  item.quantity,
  item.price
FROM orders o
CROSS JOIN UNNEST(o.items) AS item;

Expect these follow-ups

• How does this pattern differ in Snowflake where the equivalent is LATERAL FLATTEN?
• What happens to an order row if its items array is empty or NULL?

As asked

A subscriptions table records each subscription period with a user_id, start_date, and end_date. A user can have multiple consecutive or overlapping periods. Write a SQL query that merges overlapping or adjacent periods into single continuous periods.

Sample answer outline

Use a window function to find the maximum end_date seen so far before the current row (MAX(end_date) OVER with ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), then mark a row as a new island if its start_date is greater than the running maximum end_date. Assign island numbers with a cumulative sum, then GROUP BY user_id and island to get MIN(start_date) and MAX(end_date) per merged period. A strong answer handles same-day gaps between periods correctly and discusses the difference between strictly overlapping versus adjacent periods.

Reference implementation (sql)

-- subscriptions(user_id, start_date, end_date)
WITH ordered AS (
  SELECT
    user_id, start_date, end_date,
    MAX(end_date) OVER (
      PARTITION BY user_id
      ORDER BY start_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_max_end
  FROM subscriptions
),
islands AS (
  SELECT *,
    SUM(CASE WHEN start_date > prev_max_end OR prev_max_end IS NULL
             THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY start_date) AS island
  FROM ordered
)
SELECT user_id, island,
  MIN(start_date) AS period_start,
  MAX(end_date) AS period_end
FROM islands
GROUP BY user_id, island;

Expect these follow-ups

• How would you handle the case where two subscription periods share exactly the same end date and start date?
• What is the time complexity of this approach versus a procedural solution?

As asked

Some older warehouse versions do not have MEDIAN() or PERCENTILE_CONT(). Write a SQL query to compute the median order value from an orders table using window functions.

Sample answer outline

Assign a row number and count total rows. The median is the middle value for odd counts, or the average of the two middle values for even counts. Using ROW_NUMBER() OVER (ORDER BY order_value) and COUNT(*) OVER () you can select rows where row_number IN (floor((count+1)/2.0), ceil((count+1)/2.0)) and average them. For example, with 4 rows this selects row numbers 2 and 3, which are the two middle values. Using floor(count/2) and ceil(count/2) is a common mistake that returns only a single row for even-count inputs. Alternatively, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) is the standard SQL way. A strong answer knows both approaches and explains when the manual approach is needed.

Reference implementation (sql)

-- orders(order_id, order_value)
SELECT
  AVG(order_value) AS median_order_value
FROM (
  SELECT
    order_value,
    ROW_NUMBER() OVER (ORDER BY order_value) AS rn,
    COUNT(*) OVER () AS total
  FROM orders
)
WHERE rn IN (FLOOR((total + 1) / 2.0),
             CEIL((total + 1) / 2.0));

Expect these follow-ups

• How does PERCENTILE_CONT differ from PERCENTILE_DISC?
• What is the most efficient way to compute multiple percentiles (p50, p90, p99) in a single query pass?

As asked

Write a dbt macro called assert_completeness that takes a model name and a list of columns, and generates SQL that returns any row where any of those columns is NULL. The macro should be callable from a singular test file.

Sample answer outline

The macro uses Jinja to iterate over the columns list and build a WHERE clause with OR conditions for each column being NULL. The SELECT returns the primary key plus the offending column values so the user can identify which rows fail. A strong answer uses dbt's ref() inside the macro to compile the model name correctly, returns zero rows on pass, and adds a meaningful alias to indicate which column failed.

Reference implementation (sql)

{% macro assert_completeness(model, columns) %}
SELECT *
FROM {{ ref(model) }}
WHERE
  {% for col in columns %}
    {{ col }} IS NULL
    {% if not loop.last %} OR {% endif %}
  {% endfor %}
{% endmacro %}

-- Usage in a singular test file:
{{ assert_completeness(
     model='dim_customers',
     columns=['customer_id', 'email', 'created_at']
   ) }}

Expect these follow-ups

• How would you modify this macro to accept a severity parameter and set it as a dbt test severity?
• What is the difference between a singular test and using this macro as a generic test?