Troubleshooting

This guide helps resolve common issues when working with the EWE Numera data platform.

Connection Issues

Failed to Connect to Snowflake

Symptoms: Connection refused, timeout, or authentication error.

Solutions:

1. Verify account format

   # Correct format
   <account>.<region>.snowflakecomputing.com
   
   # Example
   xy12345.eu-west-1.snowflakecomputing.com
   

2. Check role grants

   SHOW GRANTS TO USER your_username;
   

3. Verify network access

4. Check if VPN is required

5. Verify firewall allows Snowflake connections (port 443)

6. SSO/MFA issues

7. Clear browser cache for SSO
8. Reset MFA token if expired

Insufficient Privileges

Symptoms: "Insufficient privileges to operate on..." error.

Solutions:

1. Verify role

   SELECT CURRENT_ROLE();
   -- Should return NUMERA_EWE_PROD_REPORTER or similar
   

2. Check required grants

   -- Required grants for reporter role
   GRANT USAGE ON DATABASE NUMERA_EWE_PROD_CORE TO ROLE your_role;
   GRANT USAGE ON SCHEMA NUMERA_DIM_MODEL TO ROLE your_role;
   GRANT SELECT ON ALL TABLES IN SCHEMA NUMERA_DIM_MODEL TO ROLE your_role;
   GRANT USAGE ON WAREHOUSE NUMERA_PROD_REPORTING TO ROLE your_role;
   

3. Request access: Contact your Snowflake administrator

---

Query Issues

No Data Returned

Symptoms: Query returns empty results when data is expected.

Solutions:

1. Check date filters

   -- Are you filtering to a date with data?
   SELECT MAX(metric_date) FROM fct_pnl;
   
   -- Use yesterday's date for latest data
   WHERE metric_date = CURRENT_DATE - 1
   

2. Check placeholder exclusion

   -- ID = 0 is the placeholder row
   WHERE dim_deal_attribute_id != 0
   

3. Check status filters

   -- Include active statuses
   WHERE tran_status IN ('Validated', 'Matured', 'Amended')
   

4. Check is_best_available flag

   -- For schedule data
   WHERE is_best_available = TRUE
   

Duplicate Records

Symptoms: Query returns more rows than expected, apparent duplicates.

Solutions:

1. Check for fanout joins

   -- Verify join cardinality
   SELECT COUNT(*) FROM fact_table;
   SELECT COUNT(*) FROM fact_table f
   JOIN dim_table d ON f.dim_id = d.dim_id;
   -- Counts should match
   

2. Use DISTINCT

   SELECT DISTINCT deal_id, portfolio
   FROM fct_deal f
   JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
   

3. Check for multiple versions

   -- Use MAX(tran_version) if needed
   SELECT deal_id, MAX(tran_version) AS latest_version
   FROM dim_tran_info
   GROUP BY deal_id
   

Query Timeout

Symptoms: Query runs for extended period then times out.

Solutions:

1. Add date filters early

   -- Good: Filter first
   SELECT ...
   FROM fct_pnl
   WHERE metric_date = CURRENT_DATE - 1
   
   -- Bad: No early filter
   SELECT ...
   FROM fct_pnl
   

2. Reduce data scope

   -- Limit to specific portfolio
   WHERE d.portfolio = 'GAS_Trading'
   
   -- Limit date range
   WHERE metric_date >= DATEADD('day', -30, CURRENT_DATE)
   

3. Use warehouse sizing

4. Contact administrator to resize warehouse if needed

5. Larger warehouses process faster but cost more

6. Check query plan

   EXPLAIN SELECT ...
   

---

Data Quality Issues

Volumes Don't Balance

Symptoms: Buy and sell volumes don't net to expected values.

Solutions:

1. Check status filters

   WHERE s.tran_status IN ('Validated', 'Matured', 'Amended')
   

2. Use is_best_available

   WHERE f.is_best_available = TRUE
   

3. Check volume types

   -- Different volume types have different meanings
   SELECT DISTINCT vt.volume_type
   FROM fct_volume f
   JOIN dim_volume_type vt ON f.dim_volume_type_id = vt.dim_volume_type_id
   

P&L Doesn't Match Source

Symptoms: P&L totals differ from source system reports.

Solutions:

1. Verify metric date

   -- Are you using the same as-of date?
   SELECT MAX(metric_date) FROM fct_pnl;
   

2. Check reval type

   -- MTM vs Settlement valuation
   WHERE reval_type = 'MTM'
   

3. Verify deal population

   -- Check which deals are included
   WHERE s.tran_status IN ('Validated', 'Matured', 'Amended')
   AND d.portfolio = 'Your_Portfolio'
   

4. Check timing

5. Source systems may update at different times
6. Verify when Numera data was last refreshed

Missing Counterparty Information

Symptoms: External party shows as NULL or joins return empty.

Solutions:

1. Check for placeholder

   -- ID = 0 means unknown
   SELECT *
   FROM fct_credit_exposure f
   LEFT JOIN dim_party_external pe
       ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
   WHERE pe.dim_party_external_bu_le_attribute_id IS NULL
      OR pe.dim_party_external_bu_le_attribute_id = 0
   

2. Verify foreign key exists

   -- Check if the party exists
   SELECT DISTINCT dim_party_external_bu_le_attribute_id
   FROM fct_credit_exposure
   WHERE dim_party_external_bu_le_attribute_id NOT IN (
       SELECT dim_party_external_bu_le_attribute_id
       FROM dim_party_external
   )
   

---

Credit Exposure Issues

Exposure Values Seem Wrong

Symptoms: Credit exposure numbers don't match expectations.

Solutions:

1. Verify metric date

   -- Credit exposures are calculated daily
   SELECT DISTINCT metric_date
   FROM fct_credit_exposure
   ORDER BY metric_date DESC
   LIMIT 5
   

2. Check simulation run

   -- PIT tables select latest simulation
   SELECT metric_date, sim_run_id, sim_version
   FROM pit_endur_credit_exposure
   WHERE metric_date = CURRENT_DATE - 1
   

3. Understand aggregation

4. Exposures may be by exposure line, not just counterparty
5. Check exp_line_id grouping

Missing PEX Factors

Symptoms: PEX factor query returns no data for commodity.

Solutions:

1. Check commodity spelling

   SELECT DISTINCT commodity
   FROM fct_credit_pex_factor
   WHERE metric_date = CURRENT_DATE - 1
   

2. Verify date

   SELECT MAX(metric_date)
   FROM fct_credit_pex_factor
   

---

Clearing Statement Issues

Missing Clearing Data

Symptoms: No data from ABN or BNP.

Solutions:

1. Check data arrival

   SELECT *
   FROM fct_data_arrival
   WHERE metric_date >= CURRENT_DATE - 7
   ORDER BY metric_date DESC
   

2. Verify feature flag

3. Clearing statements require feed.clearing_statements feature flag

4. Contact administrator if data is missing

5. Check specific bank

   SELECT pe.bunit_name, COUNT(*)
   FROM fct_clearing_statement_open_position f
   JOIN dim_party_external pe
       ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
   WHERE f.metric_date = CURRENT_DATE - 1
   GROUP BY pe.bunit_name
   

---

Performance Issues

Slow Dashboard Load

Symptoms: Power BI or Tableau dashboards take long to load.

Solutions:

1. Add filters at report level
2. Filter metric_date to recent dates

3. Filter to specific portfolios/counterparties

4. Use aggregated tables

5. Use fct_volume_daily instead of fct_schedule for daily totals

6. Use summary tables where available

7. Consider import mode

8. For dimension tables, use import instead of direct query
9. Set up incremental refresh

Warehouse Auto-Suspend

Symptoms: First query is slow, subsequent queries are fast.

Explanation: Warehouses auto-suspend to save costs. First query resumes the warehouse.

Solutions: - This is normal behavior - First query typically takes 5-15 seconds longer - Contact administrator if warehouse doesn't resume

---

Developer Issues

dbt Run Fails

Symptoms: dbt run command fails with error.

Solutions:

1. Check connection

   uv run dbt debug
   

2. View error details

   cat logs/dbt.log | tail -100
   

3. Check compiled SQL

   uv run dbt compile --select model_name
   cat target/compiled/.../model_name.sql
   

4. Run dependencies first

   uv run dbt run --select +model_name
   

Package Not Found

Symptoms: "Package not found" or "Macro not found" error.

Solutions:

# Clean and reinstall
rm -rf dbt_packages/
uv run dbt deps

Model Not Processing New Data

Symptoms: Incremental model doesn't pick up new records.

Solutions:

1. Check incremental predicate
2. Verify timestamp column in incremental logic

3. Check ingestion_delay_seconds variable

4. Full refresh

   uv run dbt run --select model_name --full-refresh
   

---

Common Error Messages

Error	Likely Cause	Solution
Compilation Error	SQL or Jinja syntax error	Check model syntax
Database Error	Snowflake query issue	Review error details
Relation does not exist	Table not created	Run dependencies first
Insufficient privileges	Missing grants	Request role access
Warehouse does not exist	Typo or missing warehouse	Check warehouse name

---

Getting Help

1. Check logs: cat logs/dbt.log
2. Review compiled SQL: target/compiled/
3. Search documentation: Use search on this site
4. Contact support: Reach out to your data team

Information to Provide

When requesting help, include: - Error message (full text) - Model or table name - Query being executed - Steps to reproduce - Expected vs actual behavior