Debug SQL in Ascend

Keep your Ascend pipelines running smoothly with these essential SQL debugging techniques.

Explain errors with Otto

Stuck on an error that's blocking your progress? Otto can help.

When you encounter an issue:

1. Click the error message in the build info panel
2. View the error details in their dedicated tab
3. Click the sparkle icon in the top right to start an Otto chat in the sidebar
4. Get Otto's assistance to resolve the issue

Testing

Validate your SQL queries before they run in production pipelines to identify issues early and ensure reliable data transformation.

Test queries on sample data

Run your queries against representative sample data to verify results:

-- Test a transformation on a smaller dataset
SELECT * FROM your_table
WHERE date_column > '2023-01-01'
LIMIT 100;

Validate expected outputs

Check that your queries produce the expected output structure and values:

-- Verify column types and data patterns
SELECT
  TYPEOF(column_a) as type_a,
  MIN(column_b) as min_b,
  MAX(column_b) as max_b,
  COUNT(*) as row_count
FROM your_transformed_table;

Common debugging strategies

Incremental query building

Build complex queries step by step, validating each part:

-- Start with base tables
SELECT * FROM table_a LIMIT 10;

-- Add joins one at a time
SELECT a.*, b.column_1
FROM table_a a
JOIN table_b b ON a.id = b.id
LIMIT 10;

-- Finally add transformations
SELECT
  a.id,
  b.column_1,
  CASE WHEN b.column_1 > 100 THEN 'High' ELSE 'Low' END as category
FROM table_a a
JOIN table_b b ON a.id = b.id
LIMIT 10;

Data validation

Include validation checks in your queries to identify potential issues:

-- Check for unexpected NULL values
SELECT
  COUNT(*) as total_rows,
  SUM(CASE WHEN critical_column IS NULL THEN 1 ELSE 0 END) as null_count,
  SUM(CASE WHEN critical_column IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as null_percentage
FROM your_table;

-- Verify data ranges
SELECT
  MIN(date_column) as earliest_date,
  MAX(date_column) as latest_date
FROM your_table;

By applying these strategies, you can efficiently identify and resolve SQL issues in your Ascend data pipelines.