SQL Insights

Execute custom SQL queries for complete flexibility in data analysis and visualization.

Best for:

• Complex custom queries
• Ad-hoc analysis
• Joining multiple data sources
• Advanced calculations

Example use cases:

• "Calculate customer lifetime value with custom cohort logic"
• "Join product events with CRM data for sales analysis"
• "Build custom retention calculations with specific business rules"

Creating SQL Insights

Write DuckDB SQL to query your lakehouse. SQL insights give you complete flexibility to create custom calculations, join multiple data sources, and implement complex business logic. Use the SQL insight builder to write and test your queries, then save them as reusable insights.

Learn more about querying via API or connecting with SQL clients.

Query, then visualize

SQL insights now follow a two-phase workflow inside the builder:

1. Query: browse catalogs, write SQL, run the statement, and inspect the result set or query history.
2. Visualization: once the query runs successfully, switch to visualization mode to map columns and configure the chart you want to save.

If the insight defines variables, you can update them from the builder and rerun the query without leaving the page. If the SQL stops producing a visualizable result, the builder returns you to the query phase so you can fix the statement first.

SQL Best Practices

1. Use DuckDB dialect: Altertable uses DuckDB SQL syntax
2. Qualify table names: Use catalog.schema.table format
3. Filter early: Apply WHERE clauses before joins and aggregations
4. Limit results: Add LIMIT for large datasets
5. Test with query_lakehouse: Preview results before creating insights

Combining Data Sources

SQL insights can query across multiple catalogs in a single query. Join product analytics events with business data from your Altertable catalogs, external catalogs (Postgres, data warehouses, and more), or any other source in your lakehouse. The federated query engine automatically optimizes cross-catalog joins and handles query execution across all data sources.

Learn more about the SQL engine and DuckDB's capabilities.

Advanced SQL Techniques

SQL insights support all DuckDB SQL features, including:

• Window Functions: Calculate running totals, moving averages, rankings, and percentiles over time windows
• Common Table Expressions (CTEs): Break complex queries into readable, reusable parts
• Recursive Queries: Handle hierarchical data and complex relationships
• Array and JSON Functions: Work with nested data structures
• Time Series Functions: Generate date series and handle time-based calculations

These advanced features enable sophisticated analytics that go beyond simple aggregations.