Getting Started: Integrating Xero with Snowflake

Welcome to Precog! This guide will walk you through creating your first data integration by connecting Xero (your accounting software) to Snowflake (your data warehouse) in just minutes.

What You'll Accomplish

By the end of this guide, you'll have:

  • Connected Xero as a data source
  • Set up Snowflake as your destination
  • Created a schedule to automatically sync your financial data
  • Your Xero accounting data flowing into Snowflake for analysis

Prerequisites

Before you start, make sure you have:

For Xero Integration

  • A Xero account with accounting data
  • Admin access to create a Xero app (free)
  • The following Xero data permissions you want to sync:
    • Accounting transactions
    • Contacts (customers/vendors)
    • Invoices and payments
    • Chart of accounts

For Snowflake Integration

  • A Snowflake account
  • Database credentials or private key authentication
  • A warehouse, database, and schema created
  • Necessary permissions to create tables

Step 1: Create Your Workspace

  1. Log into Precog using your email
  2. Create your first workspace - this is where all your integrations will live
  3. Name it something meaningful like "Finance Analytics" or "Company Data Hub"

Step 2: Set Up Xero as Your Data Source

Create a Xero App

First, you'll need to create an app in Xero to get API credentials:

  1. Visit Xero My Apps
  2. Click "New app"
  3. Choose "Web app"
  4. Enter these details:
    • App name: "Precog Data Integration"
    • Company URL: https://precog.cloud
    • Redirect URI: https://precog.cloud
  5. Click "Create app"
  6. Go to the Configuration section
  7. Copy your Client ID - you'll need this next

Configure Your Xero Source in Precog

  1. Navigate to Sources in your workspace

  2. Click "Add Source"

  3. Select "Xero" from the available connectors

  4. Fill in the source details:

    • Name: "Company Xero Account"
    • Description: "Primary accounting data from Xero"

  5. Configure the connection:

    • Client ID: Paste the Client ID from your Xero app
    • Client Secret: Generate and copy the client secret from Xero, then paste it here
    • Scopes: accounting.transactions.read accounting.journals.read accounting.settings.read accounting.contacts.read

  6. Click "Save & Authorize"

  7. Complete OAuth authorization - you'll be redirected to Xero to grant permissions

  8. Return to Precog - your source is now connected!

Your Xero integration will automatically sync these datasets:

  • Accounts - Chart of accounts
  • Contacts - Customers and vendors
  • Invoices - Sales and purchase invoices
  • Payments - Payment transactions
  • Journals - General ledger entries

Step 3: Set Up Snowflake as Your Destination

  1. Navigate to Destinations in your workspace

  2. Click "Add Destination"

  3. Select "Snowflake" from the available options

  4. Fill in destination details:

    • Name: "Data Warehouse"
    • Description: "Primary Snowflake data warehouse"

  5. Configure your Snowflake connection:

    • Account: yourcompany.snowflakecomputing.com
    • Username: Your Snowflake username
    • Warehouse: COMPUTE_WH (or your warehouse name)
    • Database: ANALYTICS_DB (or your database name)
    • Schema: XERO_DATA (or your preferred schema)
    • Role: ACCOUNTADMIN (or appropriate role)

  6. Choose your authentication method:

    Option A: Password Authentication

    • Password: Enter your Snowflake password

    Option B: Private Key Authentication (Recommended for production)

    • Private Key: Upload your .pem private key file
    • Passphrase: Enter passphrase if your key is encrypted

  7. Click "Create Destination"

Step 4: Create Your Data Pipeline

Now you'll create a schedule that moves data from Xero to Snowflake:

  1. Navigate to Schedules in your workspace

  2. Click "Create Schedule"

  3. Configure your pipeline:

    • Name: "Xero to Snowflake Daily Sync"
    • Description: "Daily sync of financial data from Xero to Snowflake"
    • Source: Select your "Company Xero Account" source
    • Destination: Select your "Data Warehouse" destination

  4. Set your sync frequency:

    • Frequency: Daily
    • Time: 6:00 AM (or when convenient)
    • Time Zone: Your local timezone

  5. Choose datasets to sync:

    • ✅ Accounts - Chart of accounts
    • ✅ Contacts - Customer and vendor data
    • ✅ Invoices - Sales and purchase invoices
    • ✅ Payments - Payment transactions
    • ✅ Journals - General ledger entries

  6. Click "Create Schedule"

Step 5: Monitor Your Integration

View Your First Data Sync

  1. Your schedule will start automatically - the first sync begins immediately
  2. Navigate to the Schedules page to monitor progress
  3. Click on your schedule to see run details
  4. Watch the data flow from Xero into Snowflake

Verify Data in Snowflake

Connect to your Snowflake instance and check your new tables:

-- Switch to your database and schema
USE DATABASE ANALYTICS_DB;
USE SCHEMA XERO_DATA;

-- View available tables
SHOW TABLES;

-- Check your accounts data
SELECT * FROM ACCOUNTS LIMIT 10;

-- Check recent invoices
SELECT * FROM INVOICES
ORDER BY UPDATEDDATEUTC DESC
LIMIT 10;

-- Check contacts
SELECT * FROM CONTACTS LIMIT 10;

You should see your Xero data organized in clean, queryable tables!

Next Steps

Expand Your Data Integration

Now that you have Xero data flowing into Snowflake, consider adding:

  • Other financial data sources like Stripe, PayPal, or bank data
  • CRM data from Salesforce or HubSpot
  • Marketing data from Google Analytics or Facebook Ads
  • Operational data from your business applications

Build Analytics and Reports

With your financial data in Snowflake, you can:

  • Connect BI tools like Tableau, Looker, or Power BI
  • Create financial dashboards showing revenue, expenses, and cash flow
  • Build customer analytics combining Xero invoices with CRM data
  • Set up data alerts for important business metrics

Optimize Your Pipeline

As your data needs grow:

  • Adjust sync frequency - run more or less often based on needs
  • Add data transformations to clean and enrich your data
  • Set up monitoring alerts to know if syncs fail
  • Scale your Snowflake warehouse for larger data volumes

Troubleshooting

Common Issues

Xero Authorization Fails

  • Verify your Client ID and Secret are correct
  • Check that redirect URI matches exactly: https://precog.cloud
  • Ensure you have admin permissions in Xero

Snowflake Connection Fails

  • Double-check account URL format: company.snowflakecomputing.com
  • Verify username, warehouse, database, and schema exist
  • For private key auth, ensure the key file is in correct PEM format

No Data Syncing

  • Check that your schedule is enabled and running
  • Verify you have data in the selected Xero datasets
  • Look at the schedule run logs for specific error messages

Getting Help

  • Documentation: Explore more guides in this docs site
  • Support: Contact support through the app if you need assistance
  • Community: Join our community for tips and best practices

Congratulations! 🎉

You've successfully set up your first data integration! Your Xero financial data is now automatically flowing into Snowflake, ready for analysis and reporting.

This is just the beginning - you can now add more sources, create complex data transformations, and build the comprehensive data platform your business needs.