Skip to content

Connecting to Data

This guide explains how to connect to the EWE Numera data platform from various tools and programming languages.

Available Numera Stacks

EWE has multiple Numera stacks available, each sourcing data from different Endur systems:

Production and Development Stacks

Stack Database Role Endur Source System Description
Production NUMERA_EWE_PROD_CORE NUMERA_EWE_PROD_REPORTER ENDUR@EWE1HPDB1 Production Endur data
Development NUMERA_EWE_DEV_CORE NUMERA_EWE_DEV_REPORTER ENDUR@EWE1HPDB1 Development environment using production Endur data
UNITTEST NUMERA_EWE_UNITTEST_CORE NUMERA_EWE_UNITTEST_REPORTER ENDUR@EWE1HPDB1 Unit testing environment using production Endur data
TEST NUMERA_EWE_TEST_CORE NUMERA_EWE_TEST_REPORTER ENDUR@EWE1HPDB1 Testing environment using production Endur data

Specialist Stacks

Stack Database Role Endur Source System Description
OAT NUMERA_EWE_OAT_CORE NUMERA_EWE_OAT_REPORTER ENDUR@EWE1HODB1 Endur OAT (Operational Acceptance Testing) system
REG1 NUMERA_EWE_REG1_CORE NUMERA_EWE_REG1_REPORTER URTV23@EWE1HDDB5 Endur v23 regulatory test system
REG2 NUMERA_EWE_REG2_CORE NUMERA_EWE_REG2_REPORTER URTV26@EWE1HDDB7 Endur v26 regulatory test system

All stacks use the same schema (NUMERA_DIM_MODEL) and follow the same dimensional model structure.

Connection Details

Setting Production Development
Account Contact your administrator Contact your administrator
Database NUMERA_EWE_PROD_CORE NUMERA_EWE_DEV_CORE
Schema NUMERA_DIM_MODEL NUMERA_DIM_MODEL
Warehouse NUMERA_PROD_REPORTING NUMERA_DEV_REPORTING
Role NUMERA_EWE_PROD_REPORTER NUMERA_EWE_DEV_REPORTER

Power BI

Direct Query Connection

  1. In Power BI Desktop, select Get Data > Snowflake
  2. Enter connection details:
  3. Server: <account>.snowflakecomputing.com
  4. Warehouse: NUMERA_PROD_REPORTING
  5. Choose DirectQuery for live data
  6. Enter credentials when prompted
  7. Select the NUMERA_DIM_MODEL schema
  8. Choose tables to import

Import Mode

For smaller datasets or when offline access is needed:

  1. Follow steps 1-4 above
  2. Choose Import instead of DirectQuery
  3. Set up scheduled refresh in Power BI Service

Best Practices for Power BI

  • Use DirectQuery for large fact tables (fct_pnl, fct_schedule)
  • Use Import for dimension tables
  • Create relationships using surrogate keys (dim_*_id)
  • Filter metric_date at the report level to limit data

Tableau

Connection Setup

  1. In Tableau, select Connect > Snowflake
  2. Enter connection details:
  3. Server: <account>.snowflakecomputing.com
  4. Role: NUMERA_EWE_PROD_REPORTER
  5. Warehouse: NUMERA_PROD_REPORTING
  6. Database: NUMERA_EWE_PROD_CORE
  7. Sign in with your credentials
  8. Select NUMERA_DIM_MODEL schema
  9. Drag tables to the canvas

Best Practices for Tableau

  • Use Live Connection for real-time data
  • Use Extract for better performance with large datasets
  • Set up incremental extracts on metric_date
  • Create data source filters on transaction status

Python

Using snowflake-connector-python

import snowflake.connector
import pandas as pd

# Connection configuration
conn_params = {
    "user": "your_username",
    "password": "your_password",  # Or use other auth methods
    "account": "your_account.snowflakecomputing.com",
    "database": "NUMERA_EWE_PROD_CORE",
    "schema": "NUMERA_DIM_MODEL",
    "warehouse": "NUMERA_PROD_REPORTING",
    "role": "NUMERA_EWE_PROD_REPORTER"
}

# Create connection
conn = snowflake.connector.connect(**conn_params)

# Execute query and return DataFrame
def query_to_df(sql):
    cur = conn.cursor()
    cur.execute(sql)
    return cur.fetch_pandas_all()

# Example usage
df = query_to_df("""
    SELECT metric_date, portfolio, SUM(pnl_ltd) as total_pnl
    FROM fct_pnl f
    JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
    WHERE metric_date >= DATEADD('day', -7, CURRENT_DATE)
    GROUP BY metric_date, portfolio
""")

print(df)

# Close connection when done
conn.close()

Using Key-Pair Authentication

For automated/service accounts, use key-pair authentication:

import snowflake.connector
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

# Load private key
with open("/path/to/private_key.p8", "rb") as key_file:
    private_key = serialization.load_pem_private_key(
        key_file.read(),
        password=b'your_passphrase',  # Or None if unencrypted
        backend=default_backend()
    )

conn = snowflake.connector.connect(
    user="service_account",
    account="your_account.snowflakecomputing.com",
    private_key=private_key,
    database="NUMERA_EWE_PROD_CORE",
    schema="NUMERA_DIM_MODEL",
    warehouse="NUMERA_PROD_REPORTING",
    role="NUMERA_EWE_PROD_REPORTER"
)

Using MFA Token Caching

For interactive sessions with MFA:

conn = snowflake.connector.connect(
    user="your_username",
    password="your_password",
    account="your_account.snowflakecomputing.com",
    authenticator="username_password_mfa",
    client_store_temporary_credential=True,
    client_request_mfa_token=True,
    database="NUMERA_EWE_PROD_CORE",
    schema="NUMERA_DIM_MODEL",
    warehouse="NUMERA_PROD_REPORTING",
    role="NUMERA_EWE_PROD_REPORTER"
)

Excel

Using Snowflake ODBC Driver

  1. Install the Snowflake ODBC Driver
  2. Configure an ODBC Data Source:
  3. Server: <account>.snowflakecomputing.com
  4. Database: NUMERA_EWE_PROD_CORE
  5. Schema: NUMERA_DIM_MODEL
  6. Warehouse: NUMERA_PROD_REPORTING
  7. Role: NUMERA_EWE_PROD_REPORTER
  8. In Excel, go to Data > Get Data > From Other Sources > From ODBC
  9. Select your Snowflake data source
  10. Write your SQL query or select tables

SQL Clients

DBeaver

  1. Create new connection: Database > New Database Connection
  2. Select Snowflake
  3. Enter connection details:
  4. Host: <account>.snowflakecomputing.com
  5. Database: NUMERA_EWE_PROD_CORE
  6. Schema: NUMERA_DIM_MODEL
  7. Warehouse: NUMERA_PROD_REPORTING
  8. Role: NUMERA_EWE_PROD_REPORTER
  9. Test connection and save

DataGrip

  1. Create new data source: + > Snowflake
  2. Enter connection details in the connection dialog
  3. Download driver if prompted
  4. Test connection

Authentication Methods

Password Authentication

Standard username/password login. May require MFA.

SSO (Single Sign-On)

For organizations with SSO configured:

conn = snowflake.connector.connect(
    user="your_email@company.com",
    account="your_account.snowflakecomputing.com",
    authenticator="externalbrowser",
    # ... other params
)

Key-Pair Authentication

For service accounts and automation. See Python example above.

OAuth

For applications integrating with identity providers:

conn = snowflake.connector.connect(
    user="your_username",
    account="your_account.snowflakecomputing.com",
    authenticator="oauth",
    token="your_oauth_token",
    # ... other params
)

Troubleshooting

Connection Refused

  1. Verify account name format: <account>.<region>.snowflakecomputing.com
  2. Check network/firewall settings
  3. Ensure VPN is connected if required

Insufficient Privileges

  1. Verify role assignment: SHOW GRANTS TO USER your_username;
  2. Request role access from administrator
  3. Ensure you're using the correct role in connection settings

Warehouse Suspended

The warehouse may auto-suspend to save costs:

  • Queries will auto-resume the warehouse
  • First query may take a few seconds longer
  • Contact administrator if warehouse doesn't resume

Query Timeout

For long-running queries:

conn = snowflake.connector.connect(
    # ... connection params
    network_timeout=600  # 10 minutes
)

Required Permissions

The NUMERA_EWE_PROD_REPORTER role grants:

  • USAGE on database NUMERA_EWE_PROD_CORE
  • USAGE on schema NUMERA_DIM_MODEL
  • SELECT on all tables and views in the schema
  • USAGE on warehouse NUMERA_PROD_REPORTING

Getting Help