Find out what’s new in the latest releases of Matillion ETL for Snowflake.

02-07-2018 [1.33] Grid iterator, SQL editor, user management, Open Exchange Rates API and More

 

  • Open Exchange Rates Query component connects to the Open Exchange Rates API
  • Grid Iterator allow iterating the values of a Grid Variable, similarly to iterating through a table of values
  • SQL Editor (in all Query components) now shows available Tables/Columns and Variables to help you author and test SQL queries from source systems
  • A new “Notices” V1 API endpoint allows you to query the current system notifications and post new messages which notify all users
  • A new “User Configuration” V1 API endpoint allows you to do user management via the Matillion API
  • Matillion no longer requires “listAllBuckets” permission (although this is still recommended)
  • Job Variables (scalar and grid) now have a “Visibility” that determines how they are used elsewhere
  • All variables now have a description
  • 100+ bug fixes across all areas of Matillion ETL
  • A new Construct Variant component to create Snowflake variants from data columns
  • Transpose Rows component to aggregate multiple rows of data into a single output row
  • Data staging (from all Query components) may use INTERNAL Staging so you don’t need to care where intermediate files are stored
  • Sequence Support has been added from ‘Environments’ context menu within the client. Users can create their own Snowflake sequences that are accessible by components for easy generation of unique numbers across their datasets (for example, as a way of introducing primary keys).
17-04-2018 [1.32] Grid variable components, run now, internal users, new data load components and more

 

  • Enterprise Only: This version of Matillion ETL for Snowflake introduces a new Permissions system that allows users to:
    • Setup users with fine grained permission sets that can limit the 100+ core functions of the tool
    • Provides default permission groups:
      • Reader – Read only user who can’t modify a project
      • Reader with Comments – Reader with ability to add notes to jobs
      • Runner – A user how and execute but not modify jobs
      • Scheduler – A user who can execute, schedule and change related config
      • Writer – A user who can create ETL jobs but not delete projects
    • Additional permission groups can be added at any time and are organised hierarchically making them easy to set up.
  • A new suite of Grid Variable components are now included to make populating and manipulating them simpler – often without requiring any scripting:
  • A new “SendGrid Query” component connects to the sendgrid email delivery platform
  • A new “ElasticSearch Query” component to connect to the elasticsearch search engine
  • A new “Magento Query” component to connect to the Magento content eCommerce system
  • A new “Zuora Query” component to connect to the Zuora subscription software platform
  • A new “GMail Query” component to connect to Google’s email service
  • A new “Run Now” action has been added when defining a schedule
  • Double-clicking a component on the canvas now opens the components “default” editor, if it has one. For example, double-clicking a Bash Script component will begin editing the script.
  • Internal User: When using the “internal” security option tomcat user passwords are now hashed when stored on disk.
  • External (Domain-based) Login: You can now encrypt your Realm Password with the AWS Key Management Service (KMS)
20-02-2018 [1.31] API profile generator, grid variables, improved matching, new data load components and more

 

  • Zendesk Query orchestration component for loading data from the Zendesk customer relationship system
  • Mixpanel Query orchestration component for loading data from Mixpanel product analytics system
  • Xero Query orchestration component for loading data from the Xero accounting system
  • Dynamics 365 Query orchestration component for loading data from Microsoft Dynamics CRM/ERP
  • API Profile RSD Generator
    • Accelerate the development of API Profiles using a new tool that automatically generates a basic XML “RSD descriptor” for any API endpoint, based on a sample of data returned
  • REST API Version 1 – Matillion ETL now has full API coverage:-
    • You can now read/write more assets (JDBC Drivers, credentials, SQS configuration) as well as allowing finer-control of which resources to include
    • A map of the v1 API is available here
    • The “v0” api is still available and unchanged
  • Grid Variables System
    • In addition to “scalar” (single-valued) variables, you can now define grid variables to hold lists and grids of values; use them wherever a compatible list or grid of values is required
    • Grid variables can be manipulated/modified in Python
    • You can pass values for grid variables when starting a job via SQS and/or the V1 API
  • You can now disable parts of an Orchestration job
  • Improved Matching in column mappings – Many transformation component “Column Mapping” parameters can now be automatically mapped, even when the input and output column names are similar but not identical
  • Viewless Architecture
    • You may remove old views that were generated by previous versions of Matillion ETL for Snowflake, via the right-click menu of each environment
  • You may optionally define a “Default Role” in the Environment. Whenever a connection is required, the given username is first authenticated and then switched to the given role

 

Important (possible breaking change): API Profiles (“RSD’s”) that handle paging may need to be tweaked to disable “auto” paging. Please see here for more details.

Important (possible breaking change): API profile limits are now applied. Where the default of 100 is set it will now be applied. This could affect API Query Components which previously ignored that limit.

23-11-2017 [1.30] Improved Task Info Panel, Redesigned Scheduler, New Data Load Components and more
  • The Google BigQuery Query component now supports standard SQL.
  • New Intersect and Except transformation components
  • New S3 Load Generator can inspect S3 files and generate Create Table / S3 Load components.
  • Redesigned “Scheduler” user interface to simplify the management of scheduled orchestration jobs.
  • New “Task Info” panel and “Task” panel make it much easier to understand complex tasks both at run time and after job execution.
  • Matillion variables can be defined and scoped at job level making jobs much more reusable. Variables can now be passed to and returned from jobs.
  • New Quickbooks Online Query component to connect to the popular online accounting system.
  • New Square Query component to connect to the payment system.
  • New Google Custom Search component allows google search data to be ingested.
  • All data-staging components can append rows to an existing table as well as creating new tables.
05-10-2017 [1.29] New Data Load Components, File Browser Enhancements, Advanced Connection Options and more
  • Matillion ETL for Snowflake Introduces the ability to configure Matillion ETL in a highly available topology with fully active-active cluster. This feature is only available on large and xlarge instance types.
    • Jobs run from SQS, the API or the built-in Scheduler will now fail-over in the event of an instance failure.
    • Scheduled runs missed because a server is offline will be run when it becomes available again
    • Once two or more members are in the cluster, a Cluster Info tab shows membership status and activity.
    • OAuth tokens, Database Drivers and RSD Profiles are replicated via the persistence database (postgres)
    • Logging from each node is sent to Cloudwatch
  • New Jira Query component loads data from Atlassian’s popular Software Development Platform.
  • New PayPal Query component can load payment and other data from Paypal Business accounts.
  • New ServiceNow Query component loads data from Servicenow’s IT Service Management (ITSM) platform.
  • New Stripe Query component loads data from Stripe’s payment platform
  • New Email Query component can query an IMAP based email system.
  • New YouTube Analytics component can query data from the YouTube Analytics API.
  • All query components now allow you to override the output table so you can specify an existing table to load or append to.
  • Excel Query can now load files from Google Cloud Storage, as well as Amazon S3
    • You only see S3 and/or GCS when you have credentials in the environment, otherwise they are hidden.
  • New option to drop a schema from the Environment Tree.
  • Specify a region in S3 Unload (to allow writing to buckets to other regions)
  • S3 / Google Cloud Storage file browser enhancements
  • Set advanced connection options during OAuth flow (e.g. to connect to a Salesforce Sandbox)
  • Warning: Manage Backups and View Audit haven’t been removed, they have been moved to the Admin menu
  • Map Values component
  • Additional support for ORC file types