Medium–Ha

Difficul
50 Questions rd ty 4 Topics

Snowflake · DBT · SQL · Warehouse | Page 1

 Incremental Load Explosion in Snowflake
Q01 Snowflake DBT Performance

■ SCENARIO / CURRENT SITUATION

Your dbt incremental model runs fine for months, but suddenly the daily run takes 4× longer. The source table
receives ~5 million new rows/day but nothing in the pipeline changed. On inspection you see Snowflake is
scanning the full 300-million-row fact table instead of only the new partition.

✔ APPROACH & SOLUTION

1. Check the dbt incremental strategy: if using merge without a cluster key, Snowflake performs a full table scan for
the join.
2. Add a CLUSTER BY on the date/timestamp column that matches your incremental filter (e.g., CLUSTER BY
(event_date)).
3. Switch strategy to delete+insert if upsert semantics aren't required – it respects micro-partitions better.
4. Use COPY_HISTORY / QUERY_HISTORY to confirm partition pruning. Look at 'partitions_scanned' vs
'partitions_total' in query profile.
5. In dbt, add unique_key only on the natural business key, not on a surrogate – large unique_key sets force wider
scans.
6. Consider is_incremental() + a lookback buffer (e.g., last 3 days) to handle late-arriving data without blowing up
scan size.

Slowly Changing Dimension Type 2 with DBT Snapshots

Q02 DBT Snowflake SCD

■ SCENARIO / CURRENT SITUATION

You maintain a customer dimension with SCD Type 2 using dbt snapshots. A business user reports that a
customer's city changed three times in one day, but the snapshot only captured one change. Your snapshot runs
once per day at midnight.

✔ APPROACH & SOLUTION

1. Root cause: dbt snapshot captures the state at run time. If a record changes and reverts within one run window,
intermediate states are lost.
2. Solution 1 – Increase snapshot frequency (e.g., hourly) using Snowflake Tasks + dbt Cloud API or dbt Jobs
triggered by events.
3. Solution 2 – Use a CDC approach: enable Snowflake Streams on the source table to capture all row-level
changes, then process them into the snapshot table manually.
4. Add an updated_at column from the source and use strategy: timestamp in dbt_project.yml so changes within the
same day are detected by timestamp rather than row hash.
5. For audit requirements, store the raw CDC events in a separate staging table before collapsing into the SCD2
dimension.
6. Validate: query the snapshot table with dbt_valid_from / dbt_valid_to and assert no two records for the same
surrogate key overlap in time.

Warehouse Spillage to Remote Storage

Q03 Snowflake Warehouse Performance

■ SCENARIO / CURRENT SITUATION

The finance team complains that their month-end aggregation query runs for 45 minutes and costs $300 per run.
Checking the query profile you see 'Bytes spilled to remote storage: 120 GB'. The warehouse is X-Large.

Snowflake · DBT · SQL · Warehouse | Page 2

✔ APPROACH & SOLUTION
1. Remote spillage = warehouse ran out of local SSD and flushed intermediate data to S3 – this is the #1 cause of
slow, expensive queries.
2. Immediate fix: Resize to 2X-Large or use multi-cluster warehouse with auto-scale – more nodes = more local
memory = less spill.
3. Query-level fix: Break the aggregation into CTEs or temp tables to reduce memory pressure in one pass.
4. Avoid SELECT * – project only required columns before the GROUP BY.
5. Check for data skew: if one GROUP BY key dominates, intermediate results balloon. Use
APPROX_COUNT_DISTINCT or pre-aggregate in a staging layer.
6. Enable result caching: if the report is run multiple times per day with the same filter, Snowflake serves from cache
at zero cost.
7. Long-term: materialize heavy intermediate aggregations as dbt models scheduled before the final report model
runs.

DBT Test Failure Blocking Production Deploy

Q04 DBT Data Quality CI/CD

■ SCENARIO / CURRENT SITUATION

During a CI pipeline run, dbt test fails on a not_null test for order_id in the orders model. The source data has
0.02% null order_ids which has always been there but the test was recently added. Business says this is known
data quality issue from a legacy system and the pipeline must not be blocked.

✔ APPROACH & SOLUTION

1. Short-term: Add a warn severity to the test so it logs a warning without failing the pipeline: config: severity: warn.
2. Better: Use the error_if / warn_if thresholds – fail only if nulls exceed a business-agreed threshold (e.g., warn_if:
'>0', error_if: '>100').
3. Tag the test with a meta block documenting the known issue and link to the Jira ticket for source system fix.
4. Create a separate dbt test job in CI that runs in 'warn' mode and posts results to Slack/PagerDuty without blocking
the deploy job.
5. Add a dbt source freshness test and data contract so future changes to the source schema are caught early.
6. Long-term: work with source system team to enforce NOT NULL at ingestion layer and remove the severity
override once fixed.

Snowflake Time Travel for Accidental Data Deletion

Q05 Snowflake Time Travel Recovery

■ SCENARIO / CURRENT SITUATION

A dbt run with an incorrect WHERE clause deleted 2 million rows from a production fact table at 14:32 UTC. The
error is detected at 16:00 UTC. The table has no backup copy but Snowflake Time Travel is configured to 90
days.

Snowflake · DBT · SQL · Warehouse | Page 3

✔ APPROACH & SOLUTION
1. Immediately suspend any dbt runs that touch the affected table to prevent further overwrites of the Time Travel
data.
2. Restore using: CREATE OR REPLACE TABLE fact_orders AS SELECT * FROM fact_orders AT (TIMESTAMP
=> '2024-01-15 14:31:00'::TIMESTAMP_TZ);
3. If exact timestamp is unknown, use BEFORE (STATEMENT => '') – get the query_id from QUERY_HISTORY
where start_time is near the incident time.
4. Validate row count and checksums before swapping the restored table into production.
5. Use SWAP WITH for zero-downtime table swap: ALTER TABLE fact_orders SWAP WITH fact_orders_restored;
6. Post-incident: add a dbt pre-hook that snapshots row counts to a control table before destructive operations, and
add row_count assertions to dbt tests.
7. Review role-based access: DML on production tables should require a separate elevated role with MFA, not the
dbt service account's default role.

Fan-out Join Causing Row Multiplication

Q06 SQL Snowflake Data Modeling

■ SCENARIO / CURRENT SITUATION

A revenue report shows total revenue 3× higher than expected after a new marketing_campaigns table was
joined to the orders fact table. Both tables are joined on customer_id. The discrepancy appeared only in the last
sprint.

✔ APPROACH & SOLUTION

1. Root cause: marketing_campaigns has multiple rows per customer_id (one per campaign). Joining many-to-one
creates a fan-out – each order row is duplicated for every campaign.
2. Diagnose: run SELECT customer_id, COUNT(*) FROM marketing_campaigns GROUP BY 1 HAVING COUNT(*)
> 1; – confirm duplicates.
3. Fix Option A: Deduplicate campaigns before joining using ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY campaign_start DESC) = 1.
4. Fix Option B: If you need all campaigns, change the grain of the fact model explicitly and use SUM(revenue) /
COUNT(DISTINCT campaign_id) or aggregate metrics at the right level.
5. Fix Option C: Use a bridge/associative table to handle the many-to-many relationship correctly.
6. Add a dbt relationship test and a row_count_ratio test to detect grain changes before they hit production.
7. Document the grain of every fact model in the dbt model description block.

Snowflake Credit Spike Overnight

Q07 Snowflake Cost Warehouse

■ SCENARIO / CURRENT SITUATION

Your Snowflake credit consumption spiked 800% between 2 AM–4 AM on a Sunday when no scheduled jobs
run. The billing dashboard shows one warehouse consumed 400 credits. No one is on-call and no alerts fired.

Snowflake · DBT · SQL · Warehouse | Page 4

✔ APPROACH & SOLUTION
1. Query WAREHOUSE_METERING_HISTORY and QUERY_HISTORY to identify the warehouse, user, and
queries responsible: SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE start_time
BETWEEN '...' AND '...' ORDER BY credits_used_cloud_services DESC;
2. Common culprits: runaway BI tool (Tableau extract, Sigma), a recursive CTE without a termination condition, or an
ETL re-triggered by a failed retry loop.
3. Check TASK_HISTORY for any Snowflake Tasks that fired unexpectedly.
4. Implement resource monitors: CREATE RESOURCE MONITOR with CREDIT_QUOTA = 50 and
NOTIFY_TRIGGERS / SUSPEND_TRIGGERS to auto-suspend on breach.
5. Set STATEMENT_TIMEOUT_IN_SECONDS at the warehouse level to kill runaway queries.
6. Review auto-suspend settings – a warehouse left running idle still accrues credits if auto-suspend is disabled or
set too high.
7. Set up Snowflake budget alerts and Slack webhooks via the NOTIFICATION INTEGRATION so on-call is paged
on anomalies.

DBT Model Dependency Cycle

Q08 DA
DBT G Architecture

■ SCENARIO / CURRENT SITUATION

After merging a large refactor PR, dbt compile throws: 'Error: Found a cycle in the model dependency graph.'
The DAG was clean before the PR. The refactor moved 10 models from the mart layer back to the intermediate
layer.

✔ APPROACH & SOLUTION

1. Run dbt ls --select +model_name+ to visualize upstream/downstream deps, or use dbt docs generate && dbt docs
serve to inspect the lineage graph visually.
2. Use dbt compile --debug to get the full dependency trace showing the circular path.
3. Common cause after refactoring: model A refs model B, and model B (now in a different layer) refs model A via a
ref() that wasn't updated.
4. Fix by breaking the cycle: extract the shared logic into a third base model that both A and B can ref without circular
dependency.
5. Enforce layer conventions in dbt: staging refs only sources, intermediate refs staging or other intermediate, marts
ref intermediate/staging only – use a dbt-project-evaluator or custom macro to enforce this.
6. Add a CI check that runs dbt compile on every PR to catch cycles before merge.

Late-Arriving Facts Breaking Aggregate Tables

Q09 SQL Warehouse Data Modeling

■ SCENARIO / CURRENT SITUATION

Your daily sales aggregate table shows correct totals for recent days but historical months are occasionally
updated retroactively when late transactions arrive (sometimes 30 days late). Downstream BI reports cache
these aggregates and show stale data for closed periods.

Snowflake · DBT · SQL · Warehouse | Page 5

✔ APPROACH & SOLUTION
1. Implement a 'closed period' flag on the aggregate: once a month is reconciled and signed-off, set is_closed =
TRUE and stop re-aggregating it.
2. For open periods, use a watermark-based approach: track the max transaction_date processed and reprocess
only affected date ranges.
3. In dbt: use an incremental model with a lookback window (WHERE transaction_date >= DATEADD('day', -35,
CURRENT_DATE)) to catch late arrivals without full reprocessing.
4. Add a late_arrival_flag column to the fact table: CASE WHEN load_date > transaction_date + INTERVAL '5 days'
THEN TRUE ELSE FALSE END.
5. Invalidate BI caches programmatically: use Tableau REST API or Looker PDT triggers to refresh only when
source data changes.
6. Downstream contracts: publish a data_as_of timestamp with every aggregate so consumers know the freshness
of each row.

Snowflake Dynamic Data Masking for PII

Q10 Snowflake Security Governance

■ SCENARIO / CURRENT SITUATION

GDPR audit reveals that the data science team can query raw email and SSN columns in the customer table.
The security team wants masking in place within 48 hours without breaking existing dbt models or BI
dashboards.

✔ APPROACH & SOLUTION

1. Use Snowflake Dynamic Data Masking: CREATE MASKING POLICY mask_email AS (val STRING) RETURNS
STRING -> CASE WHEN CURRENT_ROLE() IN ('PII_ADMIN') THEN val ELSE '***@[Link]' END;
2. Apply the policy: ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY mask_email;
3. Dynamic masking is transparent – existing queries work but masked roles see obfuscated values, so dbt models
and BI tools are not broken.
4. Create a role hierarchy: PII_ADMIN role granted only to compliance officers and the dbt service account that loads
raw data.
5. Use Snowflake Column-Level Security tags (via TAG-BASED MASKING) to auto-apply policies to any new
column tagged as PII.
6. Audit access: enable ACCESS_HISTORY in ACCOUNT_USAGE to track which users queried PII columns even
before masking was applied.
7. Document in dbt: add meta: {pii: true} to sensitive column descriptions for catalog discoverability.

Handling Schema Evolution in Snowflake + DBT

Q11 DBT Snowflake Schema

■ SCENARIO / CURRENT SITUATION

The upstream application team adds 15 new columns and renames 3 existing columns in the source table
overnight. Your dbt staging models break in production the next morning because they SELECT specific column
names.

Snowflake · DBT · SQL · Warehouse | Page 6

✔ APPROACH & SOLUTION
1. Enable Snowflake Schema Evolution: ALTER TABLE [Link] SET ENABLE_SCHEMA_EVOLUTION = TRUE;
– Snowflake auto-adds new columns from COPY INTO.
2. In dbt, use SELECT * with explicit EXCLUDE or RENAME to be resilient: SELECT * EXCLUDE (old_col) is safer
than listing all columns.
3. Implement dbt source schema tests that alert on column name changes before models run: use dbt-expectations
package's expect_column_to_exist.
4. Add a CI step that compares source schema (INFORMATION_SCHEMA) to the expected schema defined in dbt
[Link] and fails fast on drift.
5. Use Snowflake CHANGES tracking + Stream to detect DDL events and trigger a Slack alert to the data
engineering team.
6. Establish a data contract with upstream teams: any schema change must come with 2-week notice and a
migration script.
7. Version your sources: create a new source version (v2) in dbt when breaking changes occur, keeping v1 running
for grace period.

Multi-Tenant Data Isolation in Snowflake

Q12 Snowflake Security Architecture

■ SCENARIO / CURRENT SITUATION

You are building a SaaS analytics platform where 50 clients share one Snowflake account. Each client must only
see their own data. Some clients have different data residency requirements (EU vs US). You must ensure
isolation without duplicating the entire schema 50 times.

✔ APPROACH & SOLUTION

1. Use Snowflake Row Access Policies: CREATE ROW ACCESS POLICY tenant_policy AS (tenant_id VARCHAR)
RETURNS BOOLEAN -> tenant_id = CURRENT_USER(); – apply to every table once, policy enforces isolation
automatically.
2. For data residency: use Snowflake multi-region replication – primary account in US-East, replicate EU client
databases to EU-West account.
3. Use separate databases per client if isolation requirements are strict (e.g., financial or healthcare), with shared
compute warehouses.
4. Implement a tenant context variable: use SESSION POLICY or SET TENANT_ID = in the connection string, then
reference CURRENT_SESSION() in row policies.
5. dbt: use a var('tenant_id') macro and generate separate dbt target schemas per tenant, or use a single schema
with the row policy approach.
6. Audit: ACCOUNT_USAGE.ACCESS_HISTORY shows per-query which rows were accessed by which user –
critical for compliance reporting.

DBT Macro Complexity and Reusability

Q13 DBT Jinja SQL

■ SCENARIO / CURRENT SITUATION

Across 200 dbt models, the same 40-line window function logic for calculating rolling 30/60/90-day metrics is
copy-pasted. A business rule change requires updating all 200 models. The team wants a better approach.

Snowflake · DBT · SQL · Warehouse | Page 7

✔ APPROACH & SOLUTION
1. Extract the logic into a dbt macro in macros/rolling_metrics.sql: {% macro rolling_metric(column, partition_by,
days) %} ... {% endmacro %}
2. Call it in models: {{ rolling_metric('revenue', 'customer_id', 30) }} – single source of truth, one change propagates
everywhere.
3. For complex multi-column metrics, use a macro that returns a block of SQL with multiple window function columns
in one call.
4. Test the macro: create a macro_test model that applies the macro to a seed file with known expected outputs.
5. Use dbt packages: if the logic is generic enough, consider publishing it as a dbt package (dbt Hub) so other teams
can use it.
6. Version the macro interface: add a default parameter with a sensible default value so existing calls don't break
when you add new parameters.
7. Document the macro in dbt's [Link] or in a docstring inside the macro file using {% docs %} blocks.

Snowflake Query Performance – Cartesian Product

Q14 SQL Snowflake Performance

■ SCENARIO / CURRENT SITUATION

A data scientist runs an exploratory query joining a 500M-row events table to a 1M-row lookup table. The query
has been running for 3 hours and the profile shows 'Join type: Cartesian'. The warehouse is suspended by the
resource monitor after consuming 200 credits.

✔ APPROACH & SOLUTION

1. Cartesian product = no JOIN condition or an always-true condition. Result set = 500M × 1M = 500 trillion rows –
this will never complete.
2. Kill the query immediately: SELECT SYSTEM$CANCEL_QUERY('');
3. Review the SQL: ensure the JOIN has a proper ON clause matching business keys.
4. If a cross join is intentional (date spine × product list), limit one side: use WITH dates AS (SELECT ... LIMIT 365)
before the cross join.
5. Set STATEMENT_TIMEOUT_IN_SECONDS on the data scientist's warehouse to auto-kill runaway queries (e.g.,
600 seconds for ad-hoc work).
6. Enable the query profile alert: Snowflake highlights 'Cartesian Product' in red in the profile – train the team to
check the profile before executing large joins.
7. Governance: ad-hoc warehouse for data scientists should have a resource monitor with a lower credit cap than
the production warehouse.

Idempotency in DBT Full-Refresh Scenarios

Q15 DBT Data Engineering Idempotency

■ SCENARIO / CURRENT SITUATION

Running dbt run --full-refresh on the production environment is a risky operation because it drops and recreates
tables. A junior engineer accidentally runs it during business hours, causing 2 hours of downtime for BI
dashboards.

Snowflake · DBT · SQL · Warehouse | Page 8

✔ APPROACH & SOLUTION
1. Protect against accidental full-refresh: add +full_refresh: false in dbt_project.yml for critical production models.
This makes --full-refresh a no-op for those models.
2. Implement a separate 'full_refresh' dbt target that can only be triggered by senior engineers via a protected CI/CD
job, not the default run.
3. Use blue-green deployment: run full-refresh into a _staging schema, validate, then swap with the production
schema using ALTER TABLE SWAP WITH.
4. Add a confirmation prompt in your CI pipeline (manual approval gate) before any full-refresh job runs in
production.
5. For large tables, use CLONE before full-refresh as a safety net: CREATE OR REPLACE TABLE
fact_orders_backup CLONE fact_orders;
6. Post-incident: set up role-based job permissions – only the 'dbt_prod_admin' role can trigger full-refresh jobs, not
the default developer role.

Window Function vs GROUP BY Performance

Q16 SQL Performance Snowflake

■ SCENARIO / CURRENT SITUATION

A report model uses a self-join to calculate 'revenue rank per region'. The self-join runs for 12 minutes on 100M
rows. A peer suggests rewriting with window functions. You need to validate the rewrite is correct and faster.

✔ APPROACH & SOLUTION

1. Original self-join pattern: SELECT a.*, (SELECT COUNT(*) FROM sales b WHERE [Link] = [Link] AND
[Link] > [Link]) + 1 AS rank FROM sales a – this is O(n²).
2. Rewrite with RANK(): SELECT *, RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS
revenue_rank FROM sales – single scan, O(n log n).
3. Validate equivalence: run both on a 10K-row sample and diff results.
4. Snowflake processes window functions efficiently using columnar micro-partition scanning – no separate lookup
per row.
5. For dense ranking (no gaps): use DENSE_RANK(). For percentile: use PERCENT_RANK() or NTILE(n)
depending on business requirement.
6. Check query profile: window function queries show a 'Window Function' node instead of a 'Join' node –
significantly less data movement.
7. Add the rewrite to a dbt macro if this pattern repeats across models.

DBT Exposures and BI Dependency Tracking

Q17 DBT Governance Documentation

■ SCENARIO / CURRENT SITUATION

The analytics engineer team is about to deprecate 3 intermediate dbt models. But they don't know which Tableau
workbooks or Looker explores depend on them. Two workbooks break in production after the deletion.

Snowflake · DBT · SQL · Warehouse | Page 9

✔ APPROACH & SOLUTION
1. Use dbt Exposures: define Tableau and Looker dependencies in [Link] – this makes BI tools first-class
nodes in the dbt DAG.
2. Example: name: tableau_revenue_dashboard / type: dashboard / url: ... / depends_on: - ref('mart_revenue')
3. With exposures defined, dbt docs shows which BI assets depend on each model – engineers can trace impact
before deprecation.
4. Before deleting a model, run: dbt ls --select +model_to_delete+ --resource-type exposure to list all downstream BI
dependencies.
5. Use dbt's deprecation workflow: add deprecated: true to the model config and add a warning macro that alerts any
ref() caller.
6. Integrate with Tableau REST API or Looker API to auto-generate dbt exposure YAML from existing workbook
metadata – keeps documentation in sync.
7. Establish a deprecation process: 2-week notice, email to BI consumers, redirect query to new model name using a
view alias during transition.

Concurrent Write Conflicts in Snowflake

Q18 Snowflake Concurrency Architecture

■ SCENARIO / CURRENT SITUATION

Two dbt jobs run simultaneously: one merges new data into fact_orders, the other reads fact_orders for a
downstream model. Occasionally the read job sees partial data – half the rows from the current merge and half
from the previous state.

✔ APPROACH & SOLUTION

1. Snowflake uses MVCC (Multi-Version Concurrency Control): readers never block writers and vice versa. However,
readers see a consistent snapshot at the time their transaction started.
2. If the downstream model started before the merge committed, it sees the old snapshot – this is expected MVCC
behavior, not a bug.
3. To ensure the downstream model always reads the fully committed state: enforce sequential job execution via dbt
job dependencies (dbt Cloud) or orchestrator task ordering (Airflow / Prefect).
4. Use Snowflake Streams + Tasks to chain operations: the downstream Task is triggered only after the upstream
merge Task commits.
5. Never run two dbt jobs that write to the same target table simultaneously without partitioning by a non-overlapping
key (e.g., different regions).
6. Add data freshness assertions in dbt: test that the max(updated_at) in the downstream model is >= the
max(updated_at) of the source after the merge.

SQL Anti-Pattern: NOT IN with NULLs

Q19 SQL Data Quality Anti-Pattern

■ SCENARIO / CURRENT SITUATION

A data engineer writes: SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM
blacklist). The query returns 0 rows even though the blacklist has only 10 entries and the orders table has 1
million rows.

Snowflake · DBT · SQL · Warehouse | Page 10

✔ APPROACH & SOLUTION
1. Root cause: if any customer_id in the blacklist subquery is NULL, NOT IN returns NULL (unknown) for all
comparisons – no rows pass the filter.
2. SQL logic: x NOT IN (1, 2, NULL) evaluates as x != 1 AND x != 2 AND x != NULL – the last condition is always
NULL, so the whole expression is NULL/FALSE.
3. Fix Option A: NOT EXISTS – SELECT * FROM orders o WHERE NOT EXISTS (SELECT 1 FROM blacklist b
WHERE b.customer_id = o.customer_id);
4. Fix Option B: LEFT JOIN / IS NULL – SELECT o.* FROM orders o LEFT JOIN blacklist b ON o.customer_id =
b.customer_id WHERE b.customer_id IS NULL;
5. Fix Option C: Filter NULLs in subquery – WHERE customer_id NOT IN (SELECT customer_id FROM blacklist
WHERE customer_id IS NOT NULL);
6. Best practice: always use NOT EXISTS over NOT IN when the subquery can return NULLs.
7. Add a dbt test: not_null on blacklist.customer_id to enforce the invariant at source.

Zero-Copy Cloning for Dev/Test Environments

Q20 Snowflake DevOps Cost

■ SCENARIO / CURRENT SITUATION

The team needs a production-like environment for testing a major dbt refactor. Copying the 5 TB production
database takes 8 hours and costs $500. The refactor needs to be tested within 2 hours.

✔ APPROACH & SOLUTION

1. Use Snowflake Zero-Copy Clone: CREATE DATABASE dev_db CLONE prod_db; – completes in seconds
regardless of data size, zero storage cost until cloned data is modified.
2. Clone is metadata-only initially – it shares micro-partitions with prod. Writes to the clone diverge without affecting
prod.
3. Clone the entire database or specific schemas/tables as needed: CREATE SCHEMA dev_db.raw CLONE
prod_db.raw;
4. Grant the dbt service account access to the cloned database and point the dev dbt target to it.
5. After testing, drop the clone: DROP DATABASE dev_db; – no lingering cost.
6. Automate clone refresh in CI: create a fresh clone at the start of each integration test run and drop it at the end.
7. Caveat: clones share Time Travel costs with the original until the grace period expires – account for this in cost
monitoring.

Handling Duplicates in Streaming Ingestion

Q21 Snowflake Streaming Data Quality

■ SCENARIO / CURRENT SITUATION

Your Kafka → Snowflake pipeline (via Snowflake Kafka Connector) occasionally delivers the same event twice
due to at-least-once delivery semantics. The fact table now has ~2% duplicate rows identified during a monthly
audit.

Snowflake · DBT · SQL · Warehouse | Page 11

✔ APPROACH & SOLUTION
1. Use Snowflake Streams + MERGE for deduplication at load time: MERGE INTO fact_events USING
stream_events ON fact_events.event_id = stream_events.event_id WHEN NOT MATCHED THEN INSERT ...;
2. Add a UNIQUE constraint (informational in Snowflake) and enforce deduplication in the MERGE condition.
3. Alternatively, load raw into a staging table, then deduplicate with ROW_NUMBER() OVER (PARTITION BY
event_id ORDER BY kafka_offset DESC) = 1 before inserting into the fact table.
4. Use Snowpipe with COPY OPTIONS (PURGE = TRUE) and track loaded files in LOAD_HISTORY to prevent
re-processing the same file.
5. Enable COPY INTO with ON_ERROR = CONTINUE and log duplicates to a quarantine table for investigation.
6. Long-term: implement idempotency keys in Kafka producer so retries produce the same event_id, making
deduplication deterministic.
7. Add a dbt uniqueness test on event_id scheduled daily to catch future leakage.

DBT Seeds for Reference Data Management

Q22 DBT Seeds Data Modeling

■ SCENARIO / CURRENT SITUATION

The business team maintains a country_code → region mapping in an Excel sheet. Every quarter it's manually
updated in 3 different dbt models using hard-coded CASE statements. A new region was missed last quarter
causing reporting errors.

✔ APPROACH & SOLUTION

1. Convert the Excel to a dbt seed CSV (seeds/country_region_mapping.csv) and run dbt seed to load it as a table.
2. Reference it in models: FROM {{ ref('country_region_mapping') }} instead of hard-coded CASE statements –
single source of truth.
3. Add column type overrides in dbt_project.yml: seeds > column_types to ensure correct data types (e.g.,
country_code as VARCHAR(3)).
4. Add a dbt accepted_values test on the region column so invalid region names are caught at build time.
5. Automate the quarterly update: give the business team a PR template for the CSV file so changes go through
code review before deployment.
6. For larger reference datasets (>100K rows), consider a dbt source pointing to a managed table in Snowflake
instead of a seed (seeds are not ideal for large files).
7. Version control: the CSV in Git gives full history of every region mapping change with author and date.

Optimizing Snowflake COPY INTO for Large Files

Q23 Snowflake Ingestion Performance

■ SCENARIO / CURRENT SITUATION

A daily S3 → Snowflake COPY INTO job loads a single 200 GB CSV file. It runs for 90 minutes and uses only
10% of the warehouse capacity according to the query profile. The warehouse is X-Large.

Snowflake · DBT · SQL · Warehouse | Page 12

✔ APPROACH & SOLUTION
1. Root cause: a single large file is processed by one thread in Snowflake – parallelism is file-count-based, not
file-size-based.
2. Solution: split the 200 GB file into 100–250 MB compressed chunks before uploading to S3. Use split -b 200m or
equivalent in the upstream pipeline.
3. Use PURGE = TRUE in COPY INTO to auto-delete staged files after successful load.
4. Enable parallel COPY: multiple files in the stage are loaded in parallel across warehouse nodes automatically.
5. Use Parquet or ORC instead of CSV: columnar formats load faster and compress better (typically 3–5× smaller).
6. Use Snowpipe for continuous micro-batch loading if latency is a concern – no need to wait for the full 200 GB to
accumulate.
7. Monitor with COPY_HISTORY: SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(...)) to see
per-file load times and rows loaded.

Testing DBT Models with Complex Business Logic

Q24 DBT Testing Data Quality

■ SCENARIO / CURRENT SITUATION

A revenue recognition model applies 7 different business rules (returns, discounts, FX adjustments, etc.). The
model is 300 lines of SQL. How do you ensure the business logic is correct and will remain correct as rules
change?

✔ APPROACH & SOLUTION

1. Break the 300-line model into smaller intermediate models (one per business rule group) – each is independently
testable.
2. Use dbt-unit-testing package: write unit tests with mocked input data and expected output for each rule – no
production data needed.
3. Create dbt seeds with representative edge cases (zero-revenue orders, full returns, multi-currency orders) and
assert expected outputs.
4. Use dbt-expectations: expect_column_values_to_be_between for revenue bounds,
expect_column_pair_values_A_to_be_greater_than_B for net vs gross revenue.
5. Add a reconciliation model: compare dbt-calculated revenue to the source ERP system total and alert if variance >
0.01%.
6. Peer review SQL with business analyst: have them sign off on the logic using the unit test expected values as the
acceptance criteria.
7. Document each rule as a comment block in the model and link to the business requirements doc URL in the
model's meta block.

Recursive SQL for Org Hierarchy

Q25 SQL Snowflake Recursive CTE

■ SCENARIO / CURRENT SITUATION

HR wants a report showing the full management chain for every employee (up to the CEO). The employee table
has employee_id and manager_id columns. The hierarchy is 8 levels deep and has 50,000 employees.

Snowflake · DBT · SQL · Warehouse | Page 13

✔ APPROACH & SOLUTION
1. Use a Snowflake Recursive CTE: WITH RECURSIVE org_hierarchy AS ( SELECT employee_id, manager_id, 1
AS level, employee_id::VARCHAR AS path FROM employees WHERE manager_id IS NULL UNION ALL SELECT
e.employee_id, e.manager_id, [Link]+1, [Link]||'>'||e.employee_id FROM employees e JOIN org_hierarchy h ON
e.manager_id = h.employee_id) SELECT * FROM org_hierarchy;
2. Add a MAX_RECURSION_DEPTH guard: Snowflake default is 100 iterations – sufficient for 8 levels but verify for
future growth.
3. Include a 'path' column (pipe-delimited ancestor IDs) for easy filtering of subtrees: WHERE path LIKE
'%|manager_id|%'.
4. Materialize as a dbt table (not view) since it's expensive to compute and queried frequently.
5. Add a cycle detection guard: if manager_id = employee_id or a loop exists, the recursion runs forever – add
WHERE level < 20 as a safety stop.
6. Index (cluster) on manager_id to speed up the recursive join at each level.

Multi-Warehouse Strategy for Mixed Workloads

Q26 Snowflake Warehouse Architecture

■ SCENARIO / CURRENT SITUATION

ETL jobs, BI dashboards, and data science notebooks all run on the same X-Large warehouse. ETL spikes
during 6 AM–8 AM causing dashboard queries to queue for 15 minutes. Data science runs full-table scans that
also impact everyone.

✔ APPROACH & SOLUTION

1. Separate concerns with dedicated warehouses: ETL_WH (M, auto-scale 1–3 clusters), BI_WH (S, multi-cluster for
concurrency), DS_WH (L, strict resource monitor).
2. ETL warehouse: scale-out (multi-cluster) is better than scale-up because multiple concurrent dbt models benefit
from more clusters not bigger nodes.
3. BI warehouse: set AUTO_SUSPEND = 60 seconds – BI queries are short-lived, no need to keep warehouse
running between dashboard loads.
4. Data science warehouse: set STATEMENT_TIMEOUT_IN_SECONDS = 3600 and a resource monitor with daily
credit cap of 50 credits.
5. Use Query Tags: ALTER SESSION SET QUERY_TAG = 'etl'; and analyze per-tag spend in QUERY_HISTORY to
right-size each warehouse over time.
6. For BI, enable the result cache: repetitive dashboard queries (same SQL, same data) return from cache at zero
compute cost.
7. Review warehouse utilization weekly using WAREHOUSE_METERING_HISTORY – right-size down if utilization <
30%.

DBT Contract Enforcement on Critical Models

Q27 DBT Data Contracts Governance

■ SCENARIO / CURRENT SITUATION

A mart model used by the finance reporting tool had its revenue column silently change from FLOAT to
VARCHAR after a dbt upgrade. The BI tool crashed but the dbt run showed success. The issue was only caught
3 days later.

Snowflake · DBT · SQL · Warehouse | Page 14

✔ APPROACH & SOLUTION
1. Enable dbt Model Contracts (dbt 1.5+): add contract: {enforced: true} to the model config and define columns with
explicit data_type.
2. dbt will raise a compilation error if the model's output schema doesn't match the contract definition – caught at
build time, not 3 days later.
3. Define the contract in [Link]: columns: - name: revenue / data_type: float / constraints: [{type: not_null}].
4. Add a column_types assertion in a dbt test using dbt-expectations: expect_column_values_to_be_of_type.
5. For the BI tool side: use a type-safe semantic layer (dbt Semantic Layer, Looker LookML) that validates metrics
types independently.
6. Add an integration test in CI that runs the BI tool's main query against the dev environment and validates it returns
correctly typed columns.
7. Document the contract version in model meta – bump the version when the contract changes so downstream
consumers know to update.

Gaps and Islands Problem in Session Analysis

Q28 SQL Window Functions Analytics

■ SCENARIO / CURRENT SITUATION

You need to sessionize user clickstream events: group consecutive events by the same user into sessions where
a new session starts after 30 minutes of inactivity. The events table has 500M rows with user_id and
event_timestamp.

✔ APPROACH & SOLUTION

1. Step 1 – Flag session boundaries: SELECT *, CASE WHEN DATEDIFF('minute', LAG(event_timestamp) OVER
(PARTITION BY user_id ORDER BY event_timestamp), event_timestamp) > 30 OR LAG(event_timestamp) IS
NULL THEN 1 ELSE 0 END AS is_new_session FROM events;
2. Step 2 – Assign session IDs using cumulative sum: SELECT *, SUM(is_new_session) OVER (PARTITION BY
user_id ORDER BY event_timestamp) AS session_id FROM flagged;
3. Step 3 – Aggregate per session: GROUP BY user_id, session_id to get session start, end, duration, and event
count.
4. Performance: cluster the events table by (user_id, event_timestamp) to minimize data movement in the
PARTITION BY clause.
5. For 500M rows, materialize as an incremental dbt model processing only new events + a lookback window for
sessions that span the incremental boundary.
6. Handle edge case: sessions that start at the end of the incremental window and continue in the next batch – use a
2-hour lookback buffer.

Snowflake External Tables vs Internal Tables

Q29 Snowflake External Tables Architecture

■ SCENARIO / CURRENT SITUATION

Your data lake on S3 holds 10 TB of Parquet files partitioned by date. The data science team wants to query it
directly from Snowflake without loading it all in. However, queries on the external table take 10× longer than the
internal table equivalent.

Snowflake · DBT · SQL · Warehouse | Page 15

✔ APPROACH & SOLUTION
1. External tables query S3 directly via the Snowflake stage – no caching, no micro-partition pruning, higher latency
due to network I/O.
2. Add partition metadata: use PARTITION BY (TO_DATE(SPLIT_PART(metadata$filename, '/', 3))) and refresh
partition metadata with ALTER EXTERNAL TABLE ... REFRESH;
3. Enable AUTO_REFRESH on the external table using Snowflake event notification from S3 (S3 Event → SQS →
Snowflake) for automatic partition refresh.
4. For frequently-queried data (last 90 days), materialize into an internal table. Keep cold/archive data as external
table.
5. Use Iceberg tables (Snowflake Open Catalog) for better partition pruning and ACID semantics on S3 data – better
performance than plain external tables.
6. Query pattern: use external table only for initial exploration, then CTAS into internal table for production
workloads.
7. Cost: external table queries don't benefit from result caching – same query scans S3 every time. Internal tables
cache results for 24 hours.

DBT Selector Strategy for Large Projects

Q30 DBT Performance CI/CD

■ SCENARIO / CURRENT SITUATION

Your dbt project has 500 models. A full dbt run takes 4 hours. The CI pipeline runs all 500 models on every PR,
making CI 4 hours long and blocking rapid iteration. Engineers are merging without waiting for CI.

✔ APPROACH & SOLUTION

1. Use dbt slim CI: dbt run --select state:modified+ to run only modified models and their downstream dependents.
2. Requires a [Link] from the last production run (dbt state artifact) – store in S3 and pull in CI before
comparison.
3. Combine with --defer to use production results for unmodified upstream models: dbt run --select state:modified+
--defer --state ./prod_state.
4. Tag slow, non-critical models with +meta {ci_skip: true} and exclude in CI: dbt run --exclude tag:ci_skip.
5. Split CI into 3 stages: (1) dbt compile (fast, catches syntax errors), (2) slim CI run on modified, (3) full run weekly
only.
6. Use dbt Cloud's CI feature which handles slim CI natively with built-in state management – no manual S3 artifact
management.
7. Monitor which models are slowest using dbt artifacts (run_results.json) and optimize or schedule them separately.

Approximate vs Exact Distinct Counts

Q31 SQL Snowflake Performance

■ SCENARIO / CURRENT SITUATION

A dashboard metric 'Unique Active Users' runs COUNT(DISTINCT user_id) over a 365-day window on a
2-billion-row events table. It takes 8 minutes. The metric is displayed on a high-level exec dashboard where 99%
accuracy is acceptable.

Snowflake · DBT · SQL · Warehouse | Page 16

✔ APPROACH & SOLUTION
1. Use APPROX_COUNT_DISTINCT(user_id) – uses HyperLogLog algorithm, typically within 1–2% accuracy, runs
in seconds on billions of rows.
2. Snowflake also offers HLL_ACCUMULATE / HLL_COMBINE for incremental distinct count computation across
partitions.
3. For date-range slicing: pre-compute daily HLL sketches in an incremental dbt model and combine them for any
arbitrary date range – avoids full scans.
4. If exact count is required for specific use cases (e.g., billing), maintain a separate exact-count materialized table
refreshed nightly.
5. Communicate the trade-off to stakeholders: approx_count_distinct for real-time/dashboard use, exact
COUNT(DISTINCT) for monthly reporting.
6. Benchmark: run both and compare – if the difference is <1%, use approximate for all interactive queries.
7. Document the methodology in the dbt model description: 'Uses HyperLogLog approximation; accuracy ±2%.'

Snowflake Fail-Safe vs Time Travel

Q32 Snowflake Recovery Governance

■ SCENARIO / CURRENT SITUATION

A table with 30-day Time Travel is accidentally dropped. The Snowflake admin tries UNDROP TABLE – it works.
But a second table was dropped 35 days ago and UNDROP fails. The compliance team needs the data back.

✔ APPROACH & SOLUTION

1. Time Travel (0–90 days, configurable): user-controlled, accessible via AT/BEFORE clause and UNDROP. First
table was within TT window – UNDROP works.
2. Fail-Safe (7 days AFTER Time Travel expires): Snowflake-managed, NOT user-accessible. Only Snowflake
Support can recover data from Fail-Safe.
3. For the 35-day-old drop: the table may still be in Fail-Safe if it was in the 7-day window after TT expiry. Contact
Snowflake Support immediately – recovery is not guaranteed and takes time.
4. Prevention: set DATA_RETENTION_TIME_IN_DAYS = 90 on critical tables (max for Enterprise edition).
5. Never DROP without a preceding CLONE or backup export for critical tables.
6. Use governance controls: restrict DROP TABLE privilege to a specific 'dba_admin' role and require a second
approver for DDL on production tables.
7. Audit drops: query ACCOUNT_USAGE.TABLE_STORAGE_METRICS and ACCESS_HISTORY to track who
dropped what and when.

DBT Model Materialization Strategy Decision

Q33 DBT Materialization Performance

■ SCENARIO / CURRENT SITUATION

You have a dbt project with: (a) a heavy 2-hour staging model queried by 3 downstream models, (b) a simple
lookup table used by 50 models, (c) a real-time dashboard requiring data < 5 min old, (d) a rarely-changed
seed-like reference table.

Snowflake · DBT · SQL · Warehouse | Page 17

✔ APPROACH & SOLUTION
1. (a) Heavy staging model: materialize as TABLE or INCREMENTAL – avoid view because 3 downstream models
would each trigger the 2-hour computation. One build, three readers.
2. (b) Simple lookup: materialize as VIEW – lightweight, always fresh, Snowflake's optimizer inlines the view SQL
efficiently for small tables.
3. (c) Real-time dashboard: use DYNAMIC TABLE (Snowflake native) with TARGET_LAG = '5 minutes' – Snowflake
auto-refreshes based on the lag target without needing a dbt job.
4. (d) Rarely-changed reference: use dbt seed or an EPHEMERAL model if it's used only as a CTE within other
models – no separate table needed.
5. General rule: views for cheap transforms, tables for expensive ones, incremental for large tables with new data
only, ephemeral for reusable CTEs.
6. Review materialization choices quarterly using dbt's catalog and query frequency from QUERY_HISTORY.

Handling JSON Semi-Structured Data in Snowflake

Q34 Snowflake Semi-Structured SQL

■ SCENARIO / CURRENT SITUATION

A new data source delivers events as nested JSON stored in a VARIANT column. The payload has 3 levels of
nesting and some keys are inconsistent across events. The analytics team needs to query these as flat columns
with reasonable performance.

✔ APPROACH & SOLUTION

1. Use Snowflake dot-notation to extract: SELECT payload:[Link]::VARCHAR AS user_id,
payload:items[0].price::FLOAT AS first_item_price FROM raw_events;
2. Flatten arrays: SELECT [Link]:product_id::VARCHAR FROM raw_events, LATERAL FLATTEN(input =>
payload:items) f;
3. For performance: create a dbt staging model that extracts all required fields into typed columns using :: casting.
Downstream models work on typed columns, not VARIANT – avoids repeated JSON parsing.
4. Handle missing keys gracefully: Snowflake returns NULL for missing paths, not an error – use COALESCE or IS
NULL checks.
5. Use PARSE_JSON() for string-stored JSON: SELECT PARSE_JSON(raw_json):key::VARCHAR;
6. For schema inference: use INFER_SCHEMA or GET_DDL to auto-generate column definitions from a sample of
JSON files.
7. Cluster the staging table on the extracted timestamp field, not the VARIANT column – clustering on VARIANT is
ineffective.

DBT Environment Promotion Strategy

Q35 DBT DevOps Architecture

■ SCENARIO / CURRENT SITUATION

Your team has dev, staging, and prod dbt environments. Developers frequently query each other's dev schemas
by mistake. Staging is often out of sync with prod for weeks. Hotfixes to prod bypass staging and break it.

Snowflake · DBT · SQL · Warehouse | Page 18

✔ APPROACH & SOLUTION
1. Use Snowflake's database-per-environment pattern: DEV_DB, STAGING_DB, PROD_DB – enforced by separate
dbt targets with different database values.
2. Developers work in personal schemas (dev_jsmith) not a shared dev schema: schema: '{{ env_var("DBT_USER")
}}_dev' in [Link].
3. Staging is refreshed via Zero-Copy Clone from prod weekly: CREATE DATABASE STAGING_DB CLONE
PROD_DB; – always production-data-like.
4. Enforce promotion gates in CI/CD: PR to main → CI runs in staging → manual approval → deploy to prod.
Hotfixes still go through staging (fast-track with expedited review, not skip).
5. Use dbt environment variables + targets: dbt run --target prod only works from the CI/CD system's service
account, not developer laptops.
6. Add a dbt model-level config: meta: {env: prod} and a macro that warns if a prod-tagged model is run from a
non-prod target.
7. Document the promotion process in a runbook with clear responsibilities and escalation paths for emergencies.

Pivot and Unpivot Operations in SQL

Q36 SQL Snowflake Transformation

■ SCENARIO / CURRENT SITUATION

A source system delivers a monthly sales table with columns: product, jan_sales, feb_sales, ..., dec_sales (wide
format). The data warehouse requires it in long format: product, month, sales. There are 200 products and the
ETL must handle new months automatically.

✔ APPROACH & SOLUTION

1. Use Snowflake UNPIVOT: SELECT product, month, sales FROM monthly_sales UNPIVOT (sales FOR month IN
(jan_sales, feb_sales, ..., dec_sales));
2. For dynamic column handling (new months added): use a Snowflake stored procedure or JavaScript UDF that
queries INFORMATION_SCHEMA.COLUMNS to build the UNPIVOT list dynamically.
3. In dbt: use a macro to generate the UNPIVOT SQL dynamically based on the actual columns present in the
source table.
4. Alternative without UNPIVOT: UNION ALL one SELECT per month – verbose but more portable across SQL
dialects.
5. Reverse operation (long → wide): use PIVOT: SELECT * FROM long_sales PIVOT (SUM(sales) FOR month IN
('jan', 'feb', ...));
6. Clean the month string during unpivot: REPLACE(month, '_sales', '') and parse to a date: TO_DATE('2024-' ||
month_clean || '-01', 'YYYY-MON-DD').
7. Test: assert that SUM(sales) in wide format = SUM(sales) in long format after transformation.

Snowflake Stored Procedures for Complex ETL Logic

Q37 Snowflake Stored Procedures ETL

■ SCENARIO / CURRENT SITUATION

A legacy Oracle ETL procedure has 500 lines of PL/SQL with loops, cursors, and conditional branching. The
team is migrating to Snowflake and dbt but the logic is too complex to express in pure SQL models.

Snowflake · DBT · SQL · Warehouse | Page 19

✔ APPROACH & SOLUTION
1. Use Snowflake JavaScript or Python Stored Procedures for procedural logic: CREATE OR REPLACE
PROCEDURE etl_transform() RETURNS STRING LANGUAGE JAVASCRIPT AS $$ ... $$;
2. For Python: Snowflake Snowpark Python procedures allow pandas-like operations inside Snowflake without data
leaving the platform.
3. Break the 500-line procedure into smaller, testable procedures each handling one logical step – call them in
sequence from a parent orchestrating procedure.
4. Integrate with dbt using a dbt macro that calls the stored procedure: {{ run_query("CALL etl_transform()") }}
5. Or use dbt's hooks: pre-hook or post-hook to call procedures before/after model runs.
6. Migrate incrementally: replace cursor-based loops with set-based SQL first (window functions, MERGE, CTEs) –
only use stored procedures for logic that truly cannot be expressed in SQL.
7. Add error handling in the procedure: use TRY/CATCH (JavaScript) or exception blocks (Python) and log errors to
a dedicated error_log table.

Detecting Data Drift Between Environments

Q38 DBT Data Quality Testing

■ SCENARIO / CURRENT SITUATION

After deploying a dbt model change from staging to prod, the KPI dashboard shows a 5% revenue drop. The
model runs successfully in prod. The staging data was 3 weeks old. You need a process to catch this before
future deploys.

✔ APPROACH & SOLUTION

1. Refresh staging from a recent prod clone before every deploy: CREATE DATABASE STAGING_DB CLONE
PROD_DB; – ensures staging uses current data.
2. Add pre-deploy data validation: run dbt test in staging with a freshly cloned prod dataset and compare key metric
totals to prod baseline.
3. Use dbt-audit-helper package: compare a model's output between staging and prod environments to detect
differences.
4. Implement a 'data diff' step in CI: run audit_helper.compare_relations() macro on critical mart models between
environments.
5. Set tolerance thresholds: fail deployment if any KPI metric differs by >1% between staging and prod after the
model change.
6. Add business metric assertions to dbt: test that total_revenue_current_month is within X% of last_month using a
custom generic test.
7. Post-deploy monitoring: add a data observability alert (Monte Carlo, Great Expectations) that fires if metric shifts
>3σ from historical baseline.

Surrogate Key Generation Strategy

Q39 SQL DBT Data Modeling

■ SCENARIO / CURRENT SITUATION

Your dimensional model uses auto-increment integers as surrogate keys. After migrating from SQL Server to
Snowflake, auto-increment sequences cause concurrency issues during parallel loads, and surrogate keys
collide between dev and prod environments.

Snowflake · DBT · SQL · Warehouse | Page 20

✔ APPROACH & SOLUTION
1. Switch to hash-based surrogate keys: MD5 or SHA256 of the natural business key. Deterministic – same input
always gives same key, no sequence dependency.
2. In dbt: use the dbt_utils.generate_surrogate_key() macro: {{ dbt_utils.generate_surrogate_key(['customer_id',
'source_system']) }}
3. Hash-based keys are environment-safe: dev and prod generate identical surrogate keys for the same business
key – no collisions.
4. For SCD Type 2: include the effective date in the hash to generate unique keys per version:
generate_surrogate_key(['customer_id', 'dbt_valid_from']).
5. Performance: MD5 returns a 32-char VARCHAR – use a BIGINT if storage is critical, but VARCHAR is simpler for
joins.
6. If you must use sequences: use AUTOINCREMENT + Snowflake sequence objects, but disable caching on the
sequence to avoid gaps in parallel inserts (NOORDER keyword).
7. Document the surrogate key strategy in dbt model descriptions so future engineers understand why MD5 is used
instead of IDENTITY.

Query Optimization with Clustering Keys

Q40 Snowflake Clustering Performance

■ SCENARIO / CURRENT SITUATION

A 2 TB fact table has no clustering key. Analytical queries always filter on event_date and country_code. The
query profile shows 95% of micro-partitions are scanned for every query. You need to reduce scan ratio to <10%.

✔ APPROACH & SOLUTION

1. Add a clustering key: ALTER TABLE fact_events CLUSTER BY (event_date, country_code). Snowflake
re-organizes micro-partitions so rows with the same date+country co-locate, enabling pruning.
2. Check clustering effectiveness: SELECT SYSTEM$CLUSTERING_INFORMATION('fact_events', '(event_date,
country_code)') – look for average_depth close to 1 and overlap_depth close to 0.
3. Order matters: put the column with highest cardinality filter first (event_date with ~365 values) vs country_code
(~200 values).
4. Monitor clustering cost: Snowflake charges for automatic re-clustering – enable only on tables queried frequently
and with > 1 TB of data.
5. For smaller tables: manual clustering via periodic CTAS is cheaper than automatic clustering.
6. Validate improvement: compare PARTITIONS_SCANNED / PARTITIONS_TOTAL before and after clustering in
QUERY_HISTORY.
7. Do NOT cluster on a surrogate key or UUID – high cardinality with random distribution defeats clustering entirely.

dbt Source Freshness and SLA Alerting

Q41 DBT Monitoring Data Quality

■ SCENARIO / CURRENT SITUATION

A source table that feeds the daily P&L; model stopped receiving data at 3 AM. The dbt run completed at 6 AM
with all tests passing because the tests check data correctness, not freshness. The P&L; report shows
yesterday's numbers, which finance notices only at 9 AM.

Snowflake · DBT · SQL · Warehouse | Page 21

✔ APPROACH & SOLUTION
1. Add dbt source freshness configuration in [Link]: freshness: {warn_after: {count: 6, period: hour},
error_after: {count: 12, period: hour}} loaded_at_field: created_at
2. Run dbt source freshness before dbt run in the pipeline – if freshness fails, abort the run rather than producing
stale results.
3. Configure alerting: dbt Cloud sends freshness failures to Slack/PagerDuty. For dbt Core, parse run_results.json in
a post-run script.
4. Add a sentinel test: assert that MAX(event_date) in the source is >= CURRENT_DATE - 1 – catches the 'no new
data' scenario explicitly.
5. Implement a row-count delta test: if today's row count is < 10% of yesterday's row count, raise an alert before
models run.
6. For the P&L; model specifically: add a pre-hook that checks source freshness and raises an exception if data is
stale – surfacing the issue at model run time with a descriptive error message.
7. Downstream SLA: add a data_as_of column to the P&L; output so dashboard consumers can see data currency
without a support ticket.

Optimizing MERGE Statements in Snowflake

Q42 Snowflake SQL Performance

■ SCENARIO / CURRENT SITUATION

A nightly MERGE statement upserts 5 million rows into a 200-million-row target table. It runs for 2 hours. The
query profile shows 'Full Outer Join' and 'Table Scan: 100% of partitions'. You need it under 20 minutes.

✔ APPROACH & SOLUTION

1. Root cause: MERGE without a clustering key causes a full table scan to find matching rows. The join is O(n × m).
2. Fix 1: Add a cluster key on the MERGE join column (e.g., customer_id or date) to enable micro-partition pruning
for the target table scan.
3. Fix 2: Pre-filter the target using a staging approach – instead of MERGE directly, delete matching rows first then
insert: DELETE FROM target WHERE id IN (SELECT id FROM source); INSERT INTO target SELECT * FROM
source;
4. Fix 3: Use a date-bounded MERGE – add WHERE target.event_date BETWEEN source.min_date AND
source.max_date to limit target scan scope.
5. Fix 4: For insert-only workloads (no true upsert), use INSERT INTO ... SELECT ... WHERE NOT EXISTS –
cheaper than MERGE.
6. Check for data skew: if the merge key is skewed (one key = 50% of rows), the join takes much longer. Investigate
distribution with GROUP BY + COUNT.
7. Tune warehouse size: MERGE benefits from more nodes (multi-cluster) for parallel join processing – scale-out for
concurrency.

DBT Versioned Models for Breaking Changes

Q43 DBT Versioning Governance

■ SCENARIO / CURRENT SITUATION

You need to change the grain of a widely-used dbt mart model (from daily to hourly). This is a breaking change
for 20 downstream consumers. Changing the model directly will immediately break all consumers.

Snowflake · DBT · SQL · Warehouse | Page 22

✔ APPROACH & SOLUTION
1. Use dbt Model Versioning (dbt 1.5+): define versions in [Link] – v1 (current daily grain) and v2 (new hourly
grain) coexist.
2. Downstream consumers ref the version explicitly: {{ ref('mart_orders', v=1) }} – they continue working without code
changes.
3. New consumers use v2: {{ ref('mart_orders', v=2) }}
4. Set a deprecation timeline for v1: add deprecated_by: 2024-06-01 in the version config. dbt warns any model still
referencing v1 after that date.
5. Communicate the deprecation via dbt's documentation site (dbt docs) – all consumers can see the version status
and migration guide.
6. Migrate consumers over 4–6 weeks: update each downstream model to reference v2, validate outputs, then move
to the next.
7. Once all consumers are on v2, delete v1 definition and the underlying table. Run with --full-refresh to clean up.

Cross-Database Query Performance in Snowflake

Q44 Snowflake SQL Architecture

■ SCENARIO / CURRENT SITUATION

A dbt model joins tables from 3 different Snowflake databases (SALES_DB, MARKETING_DB, FINANCE_DB).
The query takes 45 minutes. All databases are in the same Snowflake account and region.

✔ APPROACH & SOLUTION

1. Cross-database joins within the same Snowflake account incur no network cost – data stays within the platform.
The issue is likely query structure.
2. Check for implicit Cartesian products or missing join keys between the three databases using the query profile.
3. Reduce the cross-database surface: materialize the needed columns from MARKETING_DB and FINANCE_DB
into a staging schema in SALES_DB before the final join – reduces complexity and allows clustering on join keys.
4. Use database shares (Snowflake Data Sharing) if the databases are on separate accounts – eliminates
cross-account data movement.
5. In dbt: use sources from multiple databases and materialize them into a unified intermediate layer in one target
database.
6. Profiling: check BYTES_PROCESSED and PARTITIONS_SCANNED per table in the query profile – the largest
scan is the optimization target.
7. Consider using a federated query approach for truly cross-domain queries with Snowflake's database link
equivalent via shares.

Handling NULL Semantics in Aggregations

Q45 SQL Data Quality Anti-Pattern

■ SCENARIO / CURRENT SITUATION

A revenue report shows different totals depending on whether analysts use SUM(revenue),
SUM(COALESCE(revenue, 0)), or AVG(revenue). The source table has 15% NULL revenue values. Business
wants NULLs treated as zero for totals but excluded for average calculations.

Snowflake · DBT · SQL · Warehouse | Page 23

✔ APPROACH & SOLUTION
1. SUM ignores NULLs natively in SQL – SUM(revenue) gives the sum of non-null values. COALESCE(revenue, 0)
makes NULLs count as zero in SUM – identical result in this case.
2. AVG also ignores NULLs – AVG(revenue) = SUM(revenue) / COUNT(non-null rows). AVG(COALESCE(revenue,
0)) = SUM / COUNT(ALL rows including NULLs) – lower result.
3. Business rule: document the agreed semantics explicitly in the dbt model. Create separate metrics: revenue_total
(sum, nulls as zero) and avg_revenue_per_sale (avg, nulls excluded).
4. Use dbt metrics layer to define these calculations once with clear semantics, preventing analysts from
re-implementing inconsistently.
5. Add a dbt test: assert that COUNT(*) - COUNT(revenue) = NULL_COUNT and alert if NULL rate exceeds 15%
threshold (source data quality issue).
6. Investigate why 15% of revenue values are NULL: data entry issue? Partial returns? Different transaction types?
NULLs may need a dedicated 'no_revenue_reason' code, not a NULL.
7. In SQL: make the NULL behavior explicit everywhere with clear column aliases: SUM(COALESCE(revenue, 0))
AS total_revenue_including_null_as_zero.

Snowflake Task Orchestration vs External Orchestrators

Q46 Snowflake Orchestration Architecture

■ SCENARIO / CURRENT SITUATION

The team is debating whether to use Snowflake Tasks (native) or Apache Airflow to orchestrate a 50-step ETL
pipeline. Some steps involve external API calls and file processing on S3 before data reaches Snowflake.

✔ APPROACH & SOLUTION

1. Snowflake Tasks are best for: Snowflake-native operations (SQL, stored procedures, dbt via API), simple DAGs,
low-latency triggers using Streams.
2. Airflow (or Prefect/Dagster) is better when: pipeline includes external API calls, file processing outside Snowflake,
cross-system dependencies, or complex retry/branching logic.
3. Recommendation for this scenario: use Airflow for the full 50-step pipeline since some steps are external. Use
Snowflake Tasks only for the Snowflake-specific sub-DAG if latency requires it.
4. Hybrid: Airflow triggers dbt Cloud jobs via API for the dbt portion, while Snowflake Streams + Tasks handle
micro-batch processing within Snowflake.
5. Avoid Snowflake Tasks for: long-running ML training jobs, external HTTP calls (possible via stored procedures but
awkward), or complex error handling with human-in-the-loop steps.
6. Cost: Snowflake Tasks consume credits even when idle if a warehouse is dedicated to them – use serverless
tasks (SERVERLESS compute) for short-running, frequent tasks.
7. Observability: Airflow has a native UI for DAG visualization and task history. Snowflake Tasks use
TASK_HISTORY view – less visual, more effort to monitor.

Rolling Window Calculations Across Sparse Data

Q47 SQL Window Functions Data Engineering

■ SCENARIO / CURRENT SITUATION

You need a 7-day rolling average of daily active users. The data has gaps (some days have no events). A simple
window function gives NULL for days with no data, and days near gaps have fewer than 7 days of input,
producing misleading averages.

Snowflake · DBT · SQL · Warehouse | Page 24

✔ APPROACH & SOLUTION
1. Step 1 – Generate a date spine: use dbt_utils.date_spine() or a recursive CTE to create one row per date in the
required range.
2. Step 2 – Left join the DAU data to the date spine: missing days become 0 (if truly 0 users) or NULL (if data was not
collected) – clarify with business.
3. Step 3 – Apply the rolling window: AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW). Now all 7 days exist in the frame.
4. Handle insufficient history at the start: use CASE WHEN ROW_NUMBER() OVER (ORDER BY date) >= 7 THEN
AVG(...) ELSE NULL END to show rolling avg only when 7 full days of data are available.
5. For business preference (use available days even if < 7): AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6
PRECEDING AND CURRENT ROW) naturally uses available rows without special handling.
6. Materialize the date spine + DAU join as an intermediate dbt model to avoid re-computing it in multiple rolling
window models.

Snowflake Data Sharing with External Organizations

Q48 Snowflake Data Sharing Architecture

■ SCENARIO / CURRENT SITUATION

You need to share a subset of your customer analytics data with a partner company that also uses Snowflake.
The data must be real-time (not a nightly export), the partner cannot modify the data, and PII must be masked.

✔ APPROACH & SOLUTION

1. Use Snowflake Secure Data Sharing: CREATE SHARE partner_share; GRANT USAGE ON DATABASE
analytics_db TO SHARE partner_share; GRANT SELECT ON VIEW masked_customer_view TO SHARE
partner_share; ALTER SHARE partner_share ADD ACCOUNTS = ;
2. Use a SECURE VIEW for masking PII: the partner queries the view but cannot see the view's SQL definition or the
underlying table.
3. The partner creates a database from the share: CREATE DATABASE from_partner FROM SHARE .; – they see
live data, zero latency.
4. Reader account option: if the partner doesn't have Snowflake, create a Snowflake Reader Account (managed by
you) – partner queries via SQL without their own Snowflake subscription.
5. Secure views enforce masking: PII columns show masked values by default. Use the same dynamic masking
policies from your internal governance setup.
6. Revoke access immediately when the partnership ends: DROP SHARE partner_share;
7. Audit: ACCOUNT_USAGE.DATA_SHARING_USAGE tracks partner query activity for compliance logging.

DBT Project Restructuring for Team Scalability

Q49 DBT Architecture Team

■ SCENARIO / CURRENT SITUATION

Your monolithic dbt project has 500 models managed by 8 engineers across 4 business domains (Sales,
Marketing, Finance, Product). Merge conflicts are frequent, build times are 4 hours, and one domain's breaking
change blocks all other domains' deployments.

Snowflake · DBT · SQL · Warehouse | Page 25

✔ APPROACH & SOLUTION
1. Split into dbt mesh (multi-project architecture): one dbt project per domain (sales_dbt, marketing_dbt, finance_dbt,
product_dbt) plus a shared foundation project for staging/raw models.
2. Use dbt cross-project references: define public models in each project with access: public config, reference them
from other projects using {{ ref('project_name', 'model_name') }}.
3. Independent deployments: each domain project has its own CI/CD pipeline. Finance breaking change only blocks
finance deploy, not Marketing.
4. Shared foundation project: raw/staging models shared by all domains are published as public models – single
source of truth for common entities.
5. Reduce build times: each domain project has ~100–150 models vs 500 – builds take ~45 min per domain
independently.
6. Governance: define public model contracts (data types, not-null constraints) in the foundation project –
downstream domains get guaranteed interfaces.
7. Tooling: dbt Cloud handles multi-project orchestration and lineage across projects via the unified DAG view.

End-to-End Lineage for Regulatory Compliance

Q50 DBT Snowflake Governance SQL

■ SCENARIO / CURRENT SITUATION

The regulatory team requires proof that revenue numbers in the quarterly report can be traced back to individual
source transactions in the ERP. You must document the full lineage – from raw S3 files through Snowflake
stages, through dbt transformations, to the final BI dashboard number – within 2 weeks.

✔ APPROACH & SOLUTION

1. Use dbt's built-in lineage: dbt docs generate produces a full DAG from sources (ERP files) to mart models. Export
as JSON for regulatory documentation.
2. Augment with Snowflake ACCESS_HISTORY: it records which tables/columns were read/written by each query –
provides query-level provenance for every transformation step.
3. Add column-level lineage: dbt's column-level lineage (dbt 1.6+ with lineage inference) traces individual columns
from source to mart.
4. Document the COPY INTO step: LOAD_HISTORY in Snowflake tracks exactly which S3 files were loaded into
which table, with timestamps.
5. Create a lineage map document: Source file → Stage → Raw table → Staging model → Intermediate model →
Mart model → BI measure. Link each hop to a Git commit and a dbt run ID.
6. Use a data catalog (Alation, Atlan, or dbt Catalog) to expose lineage in a format auditors can navigate without
SQL knowledge.
7. For the BI layer: document the metric definition in the semantic layer (dbt Metrics / Looker LookML) – shows the
exact aggregation logic applied to the mart model.

Snowflake · DBT · SQL · Warehouse | Page 26