Datasets & SQL Lab

​

Dataset management

​

Understanding datasets

• Data access - Direct connection to your warehouse tables and views
• Schema information - Column types, relationships, and metadata
• Data preview - Sample data to understand content and structure
• Usage tracking - Monitor which datasets are used in visualizations
• Security controls - Row-level security and access permissions

​

Dataset exploration

• Dataset catalog - Browse all available data sources
• Schema viewer - Examine table structures and column details
• Data sampling - Preview actual data to understand content
• Relationship mapping - See how datasets connect to each other
• Usage analytics - Track which datasets are most popular

​

Dataset configuration

• Name and description - Clear identification and business context
• Tags and categories - Organization for easy discovery
• Ownership - Assign responsibility for dataset management
• Refresh schedules - Automatic data updates for real-time insights

• Custom SQL - Define calculated fields and transformations
• Caching settings - Optimize performance with appropriate caching
• Security policies - Row-level security and access controls
• Data quality rules - Validation and monitoring settings

​

Dataset importance

• Single source of truth - Centralized access to business data
• Consistency - Standardized data definitions across the organization
• Efficiency - Reusable data sources for multiple analyses
• Governance - Controlled access and usage tracking
• Performance - Optimized queries and caching strategies

​

SQL Lab

​

Introduction to SQL Lab

• Full SQL support - Complex queries with joins, subqueries, and advanced functions
• Query history - Track and reuse previous queries
• Query sharing - Collaborate with team members
• Result export - Export query results in various formats
• Query optimization - Built-in performance suggestions

​

Writing effective SQL queries

-- Example: Well-structured query with clear formatting
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    region,
    COUNT(*) AS order_count,
    SUM(order_amount) AS total_revenue,
    AVG(order_amount) AS avg_order_value
FROM orders 
WHERE order_date >= '2024-01-01'
    AND order_status = 'completed'
GROUP BY 1, 2
ORDER BY month DESC, total_revenue DESC
LIMIT 100;

• Use appropriate filters - Limit data with WHERE clauses
• Select only needed columns - Avoid SELECT * for better performance
• Leverage indexes - Structure queries to use database indexes
• Limit result sets - Use LIMIT to control output size
• Use aggregations - Pre-aggregate data when possible

​

Advanced SQL features

-- Example: Using window functions for advanced analytics
SELECT 
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date DESC
    ) AS order_rank
FROM orders;

-- Example: Using CTEs for complex analysis
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY 1
),
sales_growth AS (
    SELECT 
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales,
        (total_sales - LAG(total_sales) OVER (ORDER BY month)) / 
        LAG(total_sales) OVER (ORDER BY month) * 100 AS growth_rate
    FROM monthly_sales
)
SELECT * FROM sales_growth;

​

SQL Lab features

• Save queries - Store frequently used queries for reuse
• Query templates - Create reusable query patterns
• Version control - Track changes to saved queries
• Collaboration - Share queries with team members

• Export options - CSV, Excel, JSON formats
• Result caching - Store query results for faster access
• Large result sets - Handle big data efficiently
• Visualization - Quick chart creation from query results

​

Creating charts from SQL

​

SQL as data source

• Complex calculations - Multi-step data transformations
• Advanced filtering - Sophisticated WHERE clauses
• Data joins - Combining multiple data sources
• Performance optimization - Optimized queries for specific use cases

1. Write your query - Use SQL Lab to create the query
2. Test and validate - Execute and verify results
3. Create chart - Use query results as data source
4. Configure visualization - Choose appropriate chart type
5. Add to dashboard - Include in your dashboard layout

​

Parameterized queries

-- Example: Parameterized query using dashboard filters
SELECT 
    DATE_TRUNC('{{ granularity }}', order_date) AS time_period,
    region,
    SUM(order_amount) AS total_revenue
FROM orders 
WHERE order_date >= '{{ start_date }}'
    AND order_date <= '{{ end_date }}'
    {% if region %}
    AND region = '{{ region }}'
    {% endif %}
GROUP BY 1, 2
ORDER BY time_period DESC;

• Date parameters - Dynamic date ranges
• Text parameters - Categorical filters
• Numeric parameters - Value range filters
• Boolean parameters - True/false conditions

​

Data quality and validation

​

Data profiling

• Completeness - Percentage of non-null values
• Uniqueness - Duplicate record identification
• Consistency - Data format and value consistency
• Accuracy - Data correctness and validity
• Timeliness - Data freshness and update frequency

• Column analysis - Data types, ranges, and distributions
• Pattern recognition - Identify common data patterns
• Outlier detection - Find unusual or suspicious values
• Relationship analysis - Understand data dependencies

​

Data validation queries

-- Example: Data quality validation queries
-- Check for missing values
SELECT 
    COUNT(*) AS total_records,
    COUNT(customer_id) AS non_null_customer_ids,
    COUNT(*) - COUNT(customer_id) AS missing_customer_ids
FROM orders;

-- Check for duplicate records
SELECT 
    customer_id, 
    order_date, 
    COUNT(*) AS duplicate_count
FROM orders
GROUP BY 1, 2
HAVING COUNT(*) > 1;

-- Check for outliers
SELECT 
    customer_id,
    order_amount
FROM orders
WHERE order_amount > (
    SELECT AVG(order_amount) + 3 * STDDEV(order_amount)
    FROM orders
);

​

Best practices

​

Dataset management

​

SQL development

​

Troubleshooting

​

Common issues