Raw Query
The only component absolutely essential. We live & breathe our query editors, but its power is unlocked when combined using its neighbors: references, steps, scratchpads. If used alone, it's no different than text edit. But here's a taste of what it can do.
- Use aliases for long table names.
- Remove duplicated calculation expressions.
- Make table names easily parametrizable.
- Prefix columns to select star after joins.
- Join tables to a CSV or live spreadsheet.
Your needs will shift which components most appeal to you. An analyst might never need to export a schema to TypeScript, but it might become your favorite feature. Everything should connect to raw query in the same way. Data Cynical has only one method of hooking into your editor, alias syntax.
Alias syntax doesn't require any database plugins. It's simply part of your raw query editor experience. Under the hood is a pre-processor which converts aliases into regular SQL. This is fully opt-out so you can use your own pre-processor.
Capabilities
Diving deeper into Data Cynical's capabilities, here's a breakdown of what tools are used in the above feature list. Use this shortlist as a starting point to determine which components you want to research more.
Table shorthand
This is the most common way to refer to another database table or an earlier step. You use the a dropdown to select what table to point to, then provide an alias. It's up to you how short or explanatory it is. It's a great way to take tables having long IDs or hashes appended to their name, to make more readable in your query.
| Description | Use aliases for long table names. |
|---|---|
| Primary Tool | References |
| Difficulty | Easy 🟢 |
| Explanation | When referencing a table or step, you set a shorthand to be used for that query. |
| Alias Syntax | Supported ✅ Optional ✅ |
Extract calculations
As your queries become more complex, this becomes more crucial. Many times, calculated columns cannot be used in where, group by, or order by clauses - unless duplicated in each place you need them. By moving a calculation to a previous step, future steps can refer to the calculation by name rather than by expression. Keep your complicated logic in one place!
| Description | Remove duplicated calculation expressions. |
|---|---|
| Primary Tool | Steps |
| Difficulty | Medium 🟠 |
| Explanation | Add calculated columns to earlier steps so the calculation can be referred to by name in wheres, groups, or orders. |
| Alias Syntax | Supported ✅ Optional ✅ |
Table parameters
Most often, you'll want a parameterized from or join clause when visualizing data or developing an API. When you use references, the generated SQL makes it easy to parameterize table names. You can also change the table pointed via the dropdown as often as you like, allowing for easy graphing comparisons.
| Description | Make table names easily parametrizable. |
|---|---|
| Primary Tool | References |
| Difficulty | Easy 🟢 |
| Explanation | Table references can be changed easily while generating clean SQL. |
| Alias Syntax | Supported ✅ Optional 🟡 |
Column prefixes
When joining tables the ambiguous column error is the most frequent dread - any columns named the same break a select star. Column prefixing is a feature which gathers the original column names, then inserts a CTE that renames them in between your raw query.
| Description | Prefix columns to select star after joins. |
|---|---|
| Primary Tool | References |
| Difficulty | Easy 🟢 |
| Explanation | Table columns can be optionally prefixed just before your query runs to keep all columns unique. |
| Alias Syntax | Supported ✅ Optional 🟡 |
CSV support
Your real super powers are inserts, exports, read-only joins, or live editing of CSVs. These let you ad-hoc analyze a dataset by handwriting any data you want into the database temporarily. Define your own columns or values from Data Cynical or drag-n-drop a CSV to edit as you need.
| Description | Join tables to a CSV or live spreadsheet. |
|---|---|
| Primary Tool | Steps |
| Difficulty | Medium 🟠 |
| Explanation | Download as CSV, insert from CSV, join a table to CSV, or edit a CSV. |
| Alias Syntax | Supported ✅ Optional 🟡 |
Alias Syntax
Aliases are the simplest, but also only, way to use every component that talks to raw query editor. An alias is a dataset in SQL. You can use an alias anywhere you would use a table name - many times aliases are simply shorthand for a table name. The syntax is as simple as possible, every alias looks like this, $Any Alias$. No exceptions.
Aliases have two rules:
- It starts
$, it ends$. - It represents a table name or CTE only. Not column name, values, function, etc.
Examples
Use these examples to feel more comfortable using or reading aliases. Whether a developer or not, you've got equal capacity to recognize where aliases are, but also where you can use them yourself.
| Example | Valid | Reason |
|---|---|---|
> SELECT * FROM $web_visitors$ | ✅ | |
> SELECT $visitors_count$ FROM all_web_visitors | ❌ | Aliases cannot be column names. |
> SELECT * FROM %web_visitors% | ❌ | Incorrect format for an alias. |
> SELECT * FROM $Web Visitors$ | ✅ |
The Alias Pre-Processor
The alias shorthand only exists in raw query editor, all known aliases are replaced by their long forms before being sent to the SQL engine. By using the "Query" tab of the output section, you can see exactly what is sent. A small comparison is all that's needed to see how aliases work. First, your step's references each have an alias shorthand, to power this, a reference has a SQL-safe name. Some example are, Step1Alias3 or Step13Table2 depending on the step, whether alias or table, then index.
Using alias shorthand aides in concise readability. The pre-processor adds in SQL comments to each alias to help you in debugging, for example, each SQL-safe name has your shorthand commented next to it. Using the shorthand additionally protects you from adding, moving, or deleting steps breaking your query. If you were to write SELECT * FROM Step1Alias3, your query is vulnerable to index shifting. Raw query editor handles this automatically via alias shorthand in the pre-processor.
SQL-safe specifies two indexes, first: step, second: alias. Two different steps can have the same or different references, but they do not share references. Use scratchpads to easily clone references to a new step.
Cynical Engine
This is our feather-weight engine that runs raw query editor. It concatenates the query of all the steps you reference into the query's CTEs. Instead of converting queries into nested subqueries, SQL's CTE pattern keeps queries flat. The CTEs are ordered just like your steps. Relying on cross-platform SQL features as the backbone, Cynical Engine is less than 50 lines of readable code.
Here's a developer overview of Cynical Engine to help you know how your query is parsed.
| Reference Type | Column Prefixing | Call Method | Query Generation |
|---|---|---|---|
| Table | ➖ | swap | direct |
| Table | ✔️ | cache | indirect |
| Step | ➖ | swap | indirect |
| Step | ✔️ | cache | indirect |
| CSV | - | swap | indirect |
| CSV | ✔️ | swap | indirect |
Since your steps are converted into CTEs, then output as a single query, you cannot use the WITH keyword to initialize a CTE. Instead, WITH is inserted automatically, but adding your own CTE steps is completely viable. You can view the generated query in the "Query" tab of the output section.