Cataloging the data output from an Amazon AppFlow flow

When you use Amazon AppFlow to transfer data with a flow that meets certain requirements, you get the option to register the data with a data catalog. A data catalog is a metadata repository. The metadata represents aspects of your data, such as the schema, format, and data types. The metadata also includes business metadata, which consists of labels and descriptions that data users write to provide helpful context for themselves and other data users. A data catalog provides a unified view of your data, even if the data belongs to multiple datasets that reside in multiple locations. When you query the consolidated metadata in a data catalog, you can more quickly search and discover your data assets.

To catalog your data, you create flows that transfer to Amazon S3, and you configure these flows with the required settings. When the flows run, Amazon AppFlow creates metadata tables in the AWS Glue Data Catalog.

The AWS Glue Data Catalog is a component of the AWS Glue service. You can use the Data Catalog to discover and search your data assets across various locations, including S3 buckets. When you register your data with the Data Catalog, you can more quickly access it with many AWS analytics, AI, and ML services. These services include AWS Glue, Amazon Athena, Amazon SageMaker Data Wrangler, and more.

It's convenient to catalog your data with Amazon AppFlow for the following reasons:

When you run a flow that catalogs your data, Amazon AppFlow does the following in your AWS account:

The Data Catalog table also includes any partition keys that organize your data in Amazon S3. For any flow that transfers data to Amazon S3, you can activate several types of partition keys in the flow settings. For more information, see Partitioning and aggregating data output from an Amazon AppFlow flow.

For more information about the Data Catalog, see AWS Glue Data Catalog in the AWS Glue Developer Guide.

Before you begin

Before you can catalog the data that you transfer with Amazon AppFlow, you must have a user role that you create with the AWS Identity and Access Management (IAM) service. This role grants Amazon AppFlow the permissions that it needs to create Data Catalog tables, databases, and partitions.

For an example IAM policy that has the required permissions, see Identity-based policy examples for Amazon AppFlow.

Cataloging flow output (Amazon AppFlow console)

To create a flow that catalogs data, complete the following steps in the Amazon AppFlow console.

Data Catalog table names

Amazon AppFlow creates metadata tables in the Data Catalog in several different naming formats.

To query the latest data output from your flow, use the table with the name that appears in the following format:

To query historical versions of your data output, use the tables with names that appear in the following formats:

The variable elements in these names are as follows:

Example Data Catalog output from a flow run

The following examples show how Amazon AppFlow creates metadata tables in the Data Catalog to catalog a dataset.

Example dataset

The following table represents an example dataset of account records from a Salesforce database. The dataset is the source data for a flow that transfers from Salesforce to Amazon S3.

Example flow configuration

The flow that transfers the example dataset has the following configuration under AWS Glue Data Catalog settings in the console:

Example Data Catalog table

When the flow runs, Amazon AppFlow creates tables in the database named example-database. One of these tables is named example-prefix_appflow_exampleflow_1668036146_latest.Amazon AppFlow updates this table every time the flow runs. You can view the tables that Amazon AppFlow creates for your flows in the Data Catalog console.

The Data Catalog console provides details pages for each table. Each page shows the metadata that a table stores, such as the columns and data types in the data schema. For more information, see Working with tables on the AWS Glue console in the AWS Glue Developer Guide.

While you're on a table details page, you can also view any business metadata that Amazon AppFlow discovered in your source data. To view this data, open the table properties by choosing Actions and then View properties. The table properties JSON gives the business metadata as the values of the AppFlowLabel and AppFlowDescription attributes.

. . .
  "StorageDescriptor": {
    "Columns": [
      {
        "Name": "id",
        "Type": "string",
        "Parameters": {
          "AppFlowLabel": "Account ID",
          "AppFlowDescription": "A unique identifier for the customer account."
        }
      },
. . .

You can search the cataloged dataset with data query tools and many AWS services. One way that you can query the data is to choose the Table data link on the database page in the Data Catalog console. That link opens Amazon Athena. This is an AWS service that runs SQL queries to help you analyze data in Amazon S3.

In Amazon Athena, the following SQL query retrieves the data that Amazon AppFlow catalogs in the example table:

SELECT * FROM "AwsDataCatalog"."example-database"
    ."example-prefix_appflow_exampleflow_1668036146_latest" limit 10;

The Amazon Athena console shows the data that the query retrieves.