Database Best Practices for Analytics

When you connect a database to Querri, a little preparation goes a long way. This guide covers how administrators can set up a database so analytics users get clean, fast, secure data, without wading through raw normalized tables.

The views on this page are database views you create in your own database. They’re separate from Querri’s own views, which Querri builds from the tables you load.

Raw tables vs analytics views

Most production databases are normalized: data is split across many tables to reduce redundancy and keep it consistent. That’s great for applications and awkward for analytics.

The problem with raw tables

Consider a typical e-commerce database:

orders          → order_items      → products      → categories
    ↓                  ↓
customers       → addresses        → regions
    ↓
customer_segments

Answering “What’s our revenue by product category and customer segment?” means joining six tables. In a natural language tool, that causes three problems:

1. Complexity: the AI has to understand every relationship.
2. Performance: multi-table joins on large tables are slow.
3. Confusion: people see dozens of tables and don’t know where to start.

The solution: analytics views

Create pre-joined views that present data the way analysts think about it:

-- PostgreSQL and MySQL
CREATE VIEW analytics.order_summary AS
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    c.customer_name,
    c.email,
    cs.segment_name as customer_segment,
    p.product_name,
    cat.category_name,
    oi.quantity,
    oi.unit_price,
    oi.line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN customer_segments cs ON c.segment_id = cs.segment_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id;

Now people see one order_summary view instead of six tables, and can ask straight away:

• “Show revenue by customer segment”
• “What are the top products by category?”
• “Revenue trend by month”

If you can’t create views

You can still narrow what Querri loads without changing your database. In the connector’s Select Data section:

• Table Selection: load only the tables and database views people need.
• Custom SQL Queries: load the result of your own query, such as the join above, as its own source.
• Limit rows per table (under Advanced Settings): cap how many rows load from large tables.

Once data is loaded, the Librarian can also build Querri views that join and clean it.

When to create views

Create views for:

Commonly joined data. If analysts always need customer details with orders, pre-join them.

Aggregated summaries. Daily, weekly or monthly rollups that are expensive to compute every time.

Filtered subsets. “Active customers only” or “last two years of orders”.

Calculated fields. Profit margin, customer lifetime value, days since last order: compute them once in the view.

Denormalized dimensions. Flatten hierarchies (product → subcategory → category → department) into single columns.

Keep raw tables for:

Ad-hoc deep dives. Sometimes analysts need the raw detail.

Data whose structure changes often. Views on volatile schemas break easily.

Very large fact tables. A view over billions of rows may not load any faster than the table itself.

Creating effective analytics views

Naming conventions

Use clear, descriptive names:

-- Good: clear what it contains
CREATE VIEW analytics.daily_sales_summary ...
CREATE VIEW analytics.customer_lifetime_value ...
CREATE VIEW analytics.product_performance ...

-- Avoid: cryptic abbreviations
CREATE VIEW v_dss_01 ...
CREATE VIEW rpt_cust_ltv ...

Include useful calculated fields

-- PostgreSQL
CREATE VIEW analytics.customer_metrics AS
SELECT
    c.customer_id,
    c.customer_name,
    c.email,
    c.created_at as signup_date,

    -- Calculated fields analysts always need
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as lifetime_value,
    AVG(o.total_amount) as avg_order_value,
    MIN(o.order_date) as first_order_date,
    MAX(o.order_date) as last_order_date,
    CURRENT_DATE - MAX(o.order_date)::date as days_since_last_order,

    -- Derived segments
    CASE
        WHEN COUNT(o.order_id) >= 10 THEN 'VIP'
        WHEN COUNT(o.order_id) >= 5 THEN 'Regular'
        WHEN COUNT(o.order_id) >= 2 THEN 'Repeat'
        ELSE 'New'
    END as customer_tier

FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email, c.created_at;

On SQL Server, use DATEDIFF(day, MAX(o.order_date), GETDATE()) for the days since the last order. On MySQL, use DATEDIFF(CURRENT_DATE, MAX(o.order_date)).

Pre-aggregate time series

For data that’s always shown by month or week:

-- PostgreSQL and Redshift
CREATE VIEW analytics.monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total_amount) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY DATE_TRUNC('month', order_date);

Consider materialized views for large data

For expensive aggregations, use materialized views, which are computed ahead of time and stored:

PostgreSQL:

CREATE MATERIALIZED VIEW analytics.product_performance AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    SUM(oi.quantity) as total_units_sold,
    SUM(oi.line_total) as total_revenue,
    COUNT(DISTINCT o.customer_id) as unique_buyers
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY p.product_id, p.product_name, p.category;

-- Refresh periodically (e.g., nightly), before Querri's scheduled sync
REFRESH MATERIALIZED VIEW analytics.product_performance;

SQL Server:

-- Indexed views stay up to date automatically
CREATE VIEW analytics.product_performance WITH SCHEMABINDING AS
SELECT
    p.product_id,
    p.product_name,
    SUM(oi.line_total) as total_revenue,
    COUNT_BIG(*) as row_count
FROM dbo.products p
JOIN dbo.order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

CREATE UNIQUE CLUSTERED INDEX IX_product_performance
ON analytics.product_performance(product_id);

Restricting access to specific tables and views

Don’t give the Querri user access to everything. A focused set of tables is easier to work with and safer.

Create an analytics schema

Keep analytics views separate from raw tables:

PostgreSQL:

-- Create analytics schema
CREATE SCHEMA analytics;

-- Create analytics user
CREATE USER querri_analytics WITH PASSWORD 'secure_password';

-- Grant access ONLY to analytics schema
GRANT USAGE ON SCHEMA analytics TO querri_analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO querri_analytics;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
    GRANT SELECT ON TABLES TO querri_analytics;

-- Remove access to other schemas (optional)
REVOKE ALL ON SCHEMA public FROM querri_analytics;

MySQL:

-- MySQL has no schemas like PostgreSQL, so use a separate database
CREATE DATABASE analytics;

CREATE USER 'querri_analytics'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON analytics.* TO 'querri_analytics'@'%';
FLUSH PRIVILEGES;

SQL Server:

-- Create analytics schema
CREATE SCHEMA analytics;

-- Create login and user
CREATE LOGIN querri_analytics WITH PASSWORD = 'secure_password';
USE your_database;
CREATE USER querri_analytics FOR LOGIN querri_analytics;

-- Grant access only to analytics schema
GRANT SELECT ON SCHEMA::analytics TO querri_analytics;

-- Deny access to other schemas
DENY SELECT ON SCHEMA::dbo TO querri_analytics;

Grant access to specific tables only

If you don’t want a separate schema, grant access table by table:

PostgreSQL:

CREATE USER querri_analytics WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_database TO querri_analytics;
GRANT USAGE ON SCHEMA public TO querri_analytics;

-- Grant access to specific tables only
GRANT SELECT ON orders TO querri_analytics;
GRANT SELECT ON customers TO querri_analytics;
GRANT SELECT ON products TO querri_analytics;

-- The user can't see invoices, payments, user_credentials, etc.

MySQL:

CREATE USER 'querri_analytics'@'%' IDENTIFIED BY 'secure_password';

GRANT SELECT ON your_database.orders TO 'querri_analytics'@'%';
GRANT SELECT ON your_database.customers TO 'querri_analytics'@'%';
GRANT SELECT ON your_database.products TO 'querri_analytics'@'%';
FLUSH PRIVILEGES;

SQL Server:

CREATE USER querri_analytics FOR LOGIN querri_analytics;

GRANT SELECT ON dbo.orders TO querri_analytics;
GRANT SELECT ON dbo.customers TO querri_analytics;
GRANT SELECT ON dbo.products TO querri_analytics;

Reducing data clutter

The fewer tables people see, the faster they find what they need.

Hide internal and system tables

Don’t expose:

• migration tracking tables (schema_migrations, __drizzle_migrations)
• audit logs, unless someone needs them
• session and cache tables
• internal configuration tables

Exclude sensitive data

Leave out or mask columns analysts don’t need:

-- PostgreSQL and MySQL
CREATE VIEW analytics.customers_safe AS
SELECT
    customer_id,
    customer_name,
    -- Mask email
    CONCAT(LEFT(email, 2), '***@***', RIGHT(email, 4)) as email_masked,
    city,
    state,
    country,
    segment,
    created_at
    -- Excluded: password_hash, ssn, credit_card_last4, etc.
FROM customers;

Keep recent data separate

-- PostgreSQL and Redshift
-- Recent data only
CREATE VIEW analytics.orders_recent AS
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '2 years';

-- Older data, separately, if needed
CREATE VIEW analytics.orders_archive AS
SELECT * FROM orders_archive;

Sample analytics schema

A complete example of an analytics-ready setup:

-- PostgreSQL
CREATE SCHEMA analytics;

-- Core views
CREATE VIEW analytics.orders AS
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    c.customer_id,
    c.customer_name,
    c.segment as customer_segment,
    c.city,
    c.state,
    c.country
FROM raw.orders o
JOIN raw.customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '3 years';

CREATE VIEW analytics.order_items AS
SELECT
    oi.order_id,
    oi.line_number,
    p.product_id,
    p.product_name,
    p.category,
    p.subcategory,
    oi.quantity,
    oi.unit_price,
    oi.line_total
FROM raw.order_items oi
JOIN raw.products p ON oi.product_id = p.product_id;

CREATE VIEW analytics.customer_summary AS
SELECT
    c.customer_id,
    c.customer_name,
    c.segment,
    c.city,
    c.state,
    c.country,
    c.created_at as signup_date,
    COUNT(o.order_id) as total_orders,
    COALESCE(SUM(o.total_amount), 0) as lifetime_value,
    MAX(o.order_date) as last_order_date
FROM raw.customers c
LEFT JOIN raw.orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.segment,
         c.city, c.state, c.country, c.created_at;

CREATE VIEW analytics.monthly_summary AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as orders,
    SUM(total_amount) as revenue,
    COUNT(DISTINCT customer_id) as customers
FROM raw.orders
WHERE order_date >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY DATE_TRUNC('month', order_date);

-- Create analytics user
CREATE USER querri_analytics WITH PASSWORD 'secure_password';
GRANT USAGE ON SCHEMA analytics TO querri_analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO querri_analytics;

Now the Querri user sees four clean views instead of dozens of normalized tables.

Checklist for database admins

Before connecting a database to Querri:

• Identify key analytics tables. What do analysts actually need?
• Create pre-joined views for data that’s always combined.
• Add calculated fields analysts always compute.
• Create an analytics schema, separate from raw tables.
• Set up a dedicated user with minimal permissions.
• Grant access to views only, not raw tables.
• Exclude sensitive columns, or mask them.
• Consider materialized views for expensive aggregations.
• Document the views: what each contains and when to use it.
• Test in Querri: check that the tables appear under Select Data and load with Save and Load Data.

Next Steps

• PostgreSQL Connector: PostgreSQL setup
• MySQL Connector: MySQL setup
• SQL Server Connector: SQL Server setup
• Amazon Redshift Connector: Redshift setup
• Managing Connections: change and refresh your connectors