References

---

The easiest tool in your toolkit. Powerful from the start, limitless when honed. Not just a table dropdown; it'll be a closer friend to your joins than join editors ever could. Refer to tables, custom SQL calculations, or live spreadsheets. Rely on references. This tool is designed to attack any situation where you'd reach for a programming language. Yet, no programming is required.

---

You'll use references differently at whatever level of SQL familiarity you have. Let's see the full potential of what references are capable of.

1. Temporarily rename tables to provide a readable shorthand.
2. Simplify complex calculated columns.
3. Automate many-table joins.
4. Update graphs or reports in real-time.

Are references in fact so powerful? Their simple yet restrictive design means that they can only be used in place of tables. Since references can't refer to columns, values, or functions - only datasets - how is it possible? CTEs. Built into SQL is a feature making it possible to turn calculated columns into datasets, Data Cynical makes it straightforward.

Uses

Let's walk through each capability mentioned above. See how all use cases are achieved through datasets.

Step or table shorthand

How to: Reference a step or table, then in the edit window, change the shorthand alias to something concise but readable to you.

Benefits: Adding steps or tables will add them to your query behind the scenes. You can use multiple steps to pull complexity away from your query, place it elsewhere, then label its functionality appropriately.

Calculated column extraction

How to: Create a step which adds the calculated column to the dataset, then reference that step in a later one.

Scenario: Determine the percent each salary is of all salary expenses.

1. Create a "Total" step to sum all salaries.
2. Add a "Combine" step to join the total to each row.
3. Add a "Final" step to calculate each salary divided by the total.

Benefits: Placing unrelated concerns in separate references adds reusability, readability, better cohesive understanding of your goal. The example above could've used SQL's window functions to solve the task. Any SQL you decide is too intricate or unclear can be unwound into a useful reference.

Multi-table joins

How to: For each table or step referenced, in the edit reference window, enable "Prefix Columns," then set a unique prefix. Joins don't need to be in table.column style. Select star now works out of the box. Automated column prefixes always relieve an ambiguous column error.

Benefits: Column prefixing is a powerful option built into references. Prefixing is automatically handled by requesting the original columns, then generating correct SQL to alias them. Column names never fall out of sync since the query is recreated when the underlying data potentially changed.

Real-time graph slicers

How to: Add a live spreadsheet step, reference this step, join this step onto another dataset, finally, graph the result. Changing the live spreadsheet will impact the graph as changes are made.

Scenario: Quickly change metrics you're comparing products by.

Option 1:

1. Create steps for each metric, each should have columns for X axis, Y axis, labels.
2. Create a graphing step which references the first metric.
3. Use the reference editor to change the metric on the fly.

Option 2:

1. Create an "EAV" step to give each metric, product combo its own row.
2. Add a single row to a live spreadsheet that has the key metric.
3. Create steps to join, then filter your EAV to only show the key metric.
4. Use the "Graph" tab in the output section to visualize results.
5. Edit your live spreadsheet, the graph reflects the metric changes.

Kinds Of References

Data Cynical built all reference types to be interchangeable. Your raw query will never know whether an alias is a table, step, or spreadsheet. All references become standard SQL CTEs behind the scenes. Yet, each type excels at different goals.

All references are datasets, you select from them, join onto, etc. Here's how each differs.

Type	Unique Feature
Table	Data can be modified. Only table references can have their data persistently updated. Steps have no persistent data. Spreadsheet data is held in memory or file.
Step	Columns can be calculated. Steps allow for adding any kind of SQL transformation. Steps are the only method to operate on spreadsheets or tables.
Spreadsheet	Values can update even in read-only databases. Because spreadsheets are external to the database, you always have full access to edit them.

The specific tradeoffs a reference type has will only constrain the data, not how you use it. You're free to switch between any of these types at a moment's notice. The following covers the common instances when one reference type works better than another.

Why Switch To Tables?

It's most common to switch from table to step. Even difficult to switch the other way. This leaves table to table, spreadsheet to table.

Scenario 1: Some tables share column names or are partitioned tables.

1. Select one of the tables as a reference.
2. Add a step to graph the columns which are common.
3. Swap the table for any similar table to compare results.

Scenario 2: You're a developer working on creating a table.

1. Add a spreadsheet as a reference.
2. Edit it until it's the correct shape.
3. Use the step's raw query editor to assert the correct shape.
4. Swap the spreadsheet for a table to test its schema.

Use A Step Instead

For nearly everyone, switching out a reference will be to a step. Since steps add columns to any kind of reference, switching from spreadsheet or table to step is easy. Follow this plan to switch to a step.

1. Create an earlier step (anywhere above the current step).
2. Reference the table, spreadsheet, or step you're switching away from.
3. Back at the current step, change out the old reference for your new one.

Mock Data Using Spreadsheets

Any step or table can be downloaded as a CSV. Then, easily uploaded as a live spreadsheet. You can mock any kind of data this way. In read-only databases, this allows you to visualize how changes would impact results. Swapping a step or table for a live spreadsheet hands you complete control to experiment on the data or table schema. Powerful, yet safe. Here's how.

1. In the output section of a step, open the "Export" tab, then download the CSV.
2. Next switch the reference to spreadsheet, then upload your CSV.
3. Open the live spreadsheet editor, then begin making changes.