# Live Query

Query databases directly without importing data

Live Query enables you to analyze data directly from your database or data warehouse without importing it into Scoop. This is ideal for large datasets, real-time analysis, or when data must remain in your infrastructure.

## When to Use Live Query

| Scenario | Import Mode | Live Query |
|----------|-------------|------------|
| Data size | Less than 10M rows | Any size |
| Update frequency | Daily or scheduled | Real-time |
| Data transformation | Needed before analysis | Query-time only |
| Data location requirements | Can be copied | Must stay in source |
| Query complexity | Any | Star schema optimal |

## Key Concepts

### Star Schema
Live Query works best with a star schema architecture:

- **Fact Table**: Central table containing your transactional data (sales, events, transactions)
- **Dimension Tables**: Reference tables providing context (customers, products, dates, locations)

This structure allows Scoop's AI to understand relationships and generate accurate analytical queries.

### How It Works

1. **Connect**: Establish a secure connection to your database
2. **Define Schema**: Select your fact table and dimension tables
3. **Configure Joins**: Specify how tables relate to each other
4. **Query**: Ask questions in natural language; Scoop generates optimized SQL

## Getting Started

### Step 1: Create a New Live Query

1. Navigate to **Live Query** in the sidebar
2. Click **New Live Query**
3. The setup wizard will guide you through configuration

### Step 2: Configure Your Connection

Choose your database type and enter connection details:

- **Server Host**: Your database server address
- **Port**: Database port (auto-filled based on database type)
- **Database**: The database or schema name
- **Username**: Read-only database user
- **Password**: User password

Click **Test Connection** to verify your settings before proceeding.

### Step 3: Add Dimension Tables

Dimension tables contain the attributes you want to analyze by:

1. Select a schema from the dropdown
2. Click **Add Dimension Table**
3. Select a table from the list
4. Choose which columns to include
5. Repeat for additional dimensions

**Examples of dimension tables:**
- Customer dimension (customer_id, name, region, segment)
- Product dimension (product_id, name, category, brand)
- Date dimension (date_id, date, month, quarter, year)
- Location dimension (location_id, city, state, country)

### Step 4: Select Your Fact Table

The fact table is your central transactional table:

1. Select the fact table from your schema
2. Choose columns to include (typically measures and foreign keys)
3. Configure joins to your dimension tables

### Step 5: Configure Join Relationships

For each dimension table, specify:

- **FK Column (Fact)**: The foreign key column in your fact table
- **PK Column (Dimension)**: The primary key column in the dimension table
- **Join Type**: LEFT (include all fact rows) or INNER (only matching rows)

### Step 6: Name and Save

1. Give your Live Query a descriptive name
2. Add an optional description
3. Click **Create Live Query**

## Querying Your Data

Once configured, you can analyze your data using natural language:

**Example queries:**
- "Show me total sales by region for last quarter"
- "What are the top 10 customers by revenue?"
- "Compare monthly sales growth by product category"
- "Which stores have declining performance?"

Scoop's AI understands your schema and generates optimized SQL queries automatically.

## Best Practices

### Database User Permissions
- **Always use read-only credentials**
- Grant SELECT only on necessary tables
- Avoid granting access to system tables

### Performance Optimization
- **Index foreign key columns** in your fact table
- **Index columns** frequently used in WHERE clauses
- Consider **materialized views** for complex aggregations
- Use **partitioning** on large fact tables

### Schema Design Tips
- Keep dimension tables denormalized where practical
- Use consistent naming for join columns
- Include a date dimension for time-based analysis
- Add descriptive columns (not just IDs) in dimensions

## Supported Databases

Live Query supports all Scoop-connected databases:

| Database | Notes |
|----------|-------|
| [Snowflake](databases/snowflake) | Recommended for large-scale analytics |
| [PostgreSQL](databases/postgresql) | Great for general purpose |
| [MySQL](databases/mysql) | Widely deployed |
| [Amazon Redshift](databases/redshift) | AWS data warehouse |
| [Google BigQuery](databases/bigquery) | Google Cloud analytics |
| [Oracle](databases/oracle) | Enterprise databases |
| [SQL Server](databases/sqlserver) | Microsoft ecosystem |
| [MariaDB](databases/mariadb) | MySQL-compatible |
| [ClickHouse](databases/clickhouse) | Real-time analytics |
| [Greenplum](databases/greenplum) | MPP data warehouse |
| [IBM DB2](databases/ibm-db2) | Enterprise systems |
| [Teradata](databases/teradata) | Enterprise data warehouse |
| [Vertica](databases/vertica) | High-performance analytics |

## Troubleshooting

### Connection Issues
- Verify Scoop's IP addresses are whitelisted in your firewall
- Check that the database is accessible from the internet
- Confirm credentials have SELECT permissions on required tables

### Slow Queries
- Add indexes on join columns and frequently filtered columns
- Review query plans in your database
- Consider reducing the data scope with partitioning

### Incorrect Results
- Verify join relationships are correctly configured
- Check that foreign keys match primary keys exactly
- Ensure dimension tables have unique primary keys

## Security Considerations

- **Credentials are encrypted** at rest and in transit
- **No data is stored** in Scoop; queries run directly on your database
- **Read-only access** ensures your data cannot be modified
- **Query logging** available for compliance requirements

For additional help, contact [Scoop Support](mailto:support@scoopanalytics.com).