Connect Your Database to Alteryx: Input Data Tool and SQL Queries Explained

Watch the video above, then read on for the full walkthrough and the reasoning behind each step.

How to Query a Database Directly in Alteryx

If you work in treasury, risk reporting, or any finance function that relies on large datasets, you know the friction: someone exports a CSV from the database, you import it into Alteryx, you check the row count three times to make sure nothing was truncated, and then you discover the export happened at 9am so the data is four hours stale.

Connecting Alteryx directly to your database solves this. You pull data on demand, at the frequency you need, with no intermediate file to version or lose. For a finance team managing daily cash positions, regulatory reporting feeds, or counterparty exposure data, this matters. It is the difference between a workflow you run once and one you can schedule to run every morning.

Direct database connection also means you can filter and aggregate at source. If you need only transactions from the last 30 days, or only trades in a specific currency, you tell the database to send you just that data. You do not download everything and then filter in Alteryx. For large tables, this saves seconds or minutes on every run. Over a year of daily workflows, that compounds.

Setting Up Your Database Connection

The first step is telling Alteryx how to find your database and authenticate. This happens once, and then you reuse the connection across multiple workflows.

Choosing the Right Connection Type

Alteryx supports most enterprise databases: SQL Server, Oracle, PostgreSQL, MySQL, and others. The connection method depends on what your organisation runs and what network access you have.

In Alteryx Designer, open the Input Data tool (or double click it if it is already in your canvas). You will see a dropdown menu asking you to select your database type. Choose the one that matches your environment. If you are unsure, check with your database team or look at the connection strings in any existing ETL tools or applications you use.

Some workplaces use a dedicated data warehouse (like Snowflake or a cloud instance). Others run on premises systems. The principle is the same: tell Alteryx the database type, then give it the credentials and network address.

Authentication and Credentials

You need three pieces of information: the server address (hostname or IP), the database name, and your credentials (username and password, or Windows authentication if your organisation uses Active Directory).

In production, always use Alteryx credential storage, not plaintext passwords in the workflow file. When you first connect, Alteryx will offer to save your credentials securely. Accept this. It means your workflow file does not contain your password in plaintext, and if you share the workflow with a colleague, they can run it with their own credentials (if they have database access).

Once you have entered the server address, database name, and credentials, click the test connection button. You will know immediately if the setup works. If it fails, the error message usually tells you why: network unreachable, authentication failed, database name incorrect. Fix the issue and test again. Do not move forward until the test passes.

The Input Data Tool for Databases

Once your connection is live, the Input Data tool offers two paths: select a table or view, or write a SQL query.

Selecting a Table or View

The simplest approach is to browse your database and pick a table. After your connection test passes, click the browse button. Alteryx will list all the tables and views your credentials can access. Select one. Alteryx will show you a preview of the first few rows.

This is useful for ad hoc work. If you need to explore a dataset quickly, browse and preview. But for production workflows, you usually want to be more deliberate about what you import.

A view is a pre written SQL query stored in the database. If your database team has already built a view that handles aggregation, joining, or filtering logic, use it. This avoids duplicating logic and means any changes to the business rules happen in one place (the database) rather than scattered across multiple Alteryx workflows.

Running a SQL Query Instead

For most finance workflows, you will want to write a SQL query. This gives you control over exactly what data you pull and keeps your workflow efficient.

In the Input Data tool, instead of selecting a table, choose the option to enter a custom SQL query. A text box will appear. Write a SELECT statement that retrieves the columns and rows you need.

SELECT
  trade_date,
  counterparty_id,
  currency,
  notional_amount,
  trade_type
FROM trades
WHERE trade_date >= CAST(GETDATE() - 30 AS DATE)
  AND currency IN ('GBP', 'EUR', 'USD')
ORDER BY trade_date DESC

This example pulls trades from the last 30 days in three currencies. The WHERE clause filters at source. The ORDER BY clause sorts by date descending (most recent first). Only these rows and columns come into Alteryx. A 10 million row table becomes a 50,000 row dataset because you filtered upstream.

Writing Effective SQL in Alteryx

Your SQL in Alteryx runs on the database server itself, not in Alteryx. This is important. It means you can use any SQL feature your database supports: WHERE, GROUP BY, HAVING, JOINs, CTEs (Common Table Expressions), and stored procedures where supported by your database.

Basic SELECT, WHERE, and JOIN Queries

A SELECT statement picks columns. A WHERE clause filters rows. A JOIN combines data from two tables.

SELECT
  p.party_id,
  p.party_name,
  COUNT(t.trade_id) AS trade_count,
  SUM(t.notional_amount) AS total_notional
FROM parties p
LEFT JOIN trades t ON p.party_id = t.counterparty_id
WHERE p.status = 'Active'
GROUP BY p.party_id, p.party_name
HAVING COUNT(t.trade_id) > 0
ORDER BY total_notional DESC

This query joins the parties table (your counterparties) to trades, counts how many trades each party has, and sums their notional exposure. It filters to active parties only and excludes any party with zero trades. The database does all this work and sends you only the summary rows.

If you tried to do the same thing in Alteryx (pull all trades, pull all parties, join them in Alteryx, then aggregate), you would move millions of rows through your workflow. By doing it in SQL, a 10 million row join that might take 2 minutes in Alteryx takes 10 seconds at the database. The difference matters for daily or hourly scheduled workflows.

Common Pitfalls and How to Avoid Them

SQL syntax varies between database platforms. In SQL Server, use GETDATE() for the current date; in PostgreSQL, use CURRENT_DATE; in Oracle, use SYSDATE. If your query fails with a syntax error, check your database type and adjust. The error message usually hints at what went wrong.

Column names are case sensitive in some databases, not others. Always check your database documentation. If a query works once and then fails, check case sensitivity.

JOINs without a WHERE clause can explode your dataset. If you join a 1 million row table to a 100 million row table without filtering, you get 100 million rows in Alteryx. Always check your join logic. If you are unsure, write a COUNT(*) query first to see how many rows you get, before importing them.

Date filters are easy to get wrong. Use explicit date formats or database date functions. Avoid string comparisons like WHERE date_column > '2024-01-01' unless you are certain your database stores dates as strings (it should not). Use WHERE trade_date >= CAST('2024-01-01' AS DATE) or WHERE trade_date >= DATEADD(day, -30, CAST(GETDATE() AS DATE)) to be safe. Note: DATEADD and GETDATE are SQL Server syntax; check your database platform for equivalent functions.

Importing Large Datasets Without Slowdown

Filter at Source, Not After Import

This is the single biggest efficiency win for finance teams. If you need the last 90 days of transactions and your table has 10 years of history, write a WHERE clause that filters to the last 90 days. Do not import everything and then filter in Alteryx.

SELECT *
FROM transactions
WHERE transaction_date >= DATEADD(day, -90, CAST(GETDATE() AS DATE))

This is faster, uses less memory, and makes your workflow easier to understand.

Using Views and Stored Procedures

If your database team has already built a view that does the heavy lifting, query the view instead of the base tables.

SELECT *
FROM vw_counterparty_exposure
WHERE valuation_date = CAST(GETDATE() AS DATE)

The view handles the joins, the aggregations, and the business logic. You get a clean, pre calculated dataset. If the view needs updating, the database team updates it in one place. All workflows that use it benefit immediately.

Some databases support stored procedures: pre written scripts that accept parameters and return results. Support and syntax vary by database type. Check your Alteryx documentation for your specific platform to confirm whether you can call stored procedures directly from the Input Data tool.

Testing Your Connection and Troubleshooting

Before you run a full workflow, test your SQL query in the Input Data tool. After you paste your query, click preview. If the query works, you will see the first few rows. If it fails, you will see the error message.

Common errors:

Timeout: Your query is running on the database but taking too long. Add more filters or check if you may have created an unintended Cartesian product. Run your query directly in your database client (SQL Server Management Studio, DBeaver, etc.) to see how long it really takes. If the database is slow, Alteryx will be too.

Authentication failed: Your credentials are wrong, or they do not have permission to access that table or database. Check your username and password. Ask your database team if you need additional permissions.

Table or column not found: You have misspelled the name, or the table is in a different schema. If your table is in a schema called "risk", you might need to write schema.tablename instead of just tablename. Check your database structure.

Port or connection refused: The database server is not reachable from your machine. This is usually a network or firewall issue. Escalate to IT.

If you get stuck, test your query directly in your database client first. If it works there, you know the SQL is correct. If it fails there too, you know the issue is with your query or permissions, not Alteryx. This divide and conquer approach saves hours of frustration.

The Practical Takeaway

Stop exporting CSVs and importing them into Alteryx. Connect directly to your database. Write a SQL query that pulls exactly what you need, no more. Test it. Schedule the workflow to run daily or weekly. You have automated what used to be manual, error prone work. Your data is always current. Your workflow is maintainable because the logic sits in SQL, not scattered across Alteryx tools. And when your team needs to change the business rules, changes made in the database propagate to all dependent workflows.

For more on pulling data into Alteryx, explore our blog posts on importing multiple Excel files and wildcard imports. If you work with APIs, we have also covered how to call them from your workflow.