This looks correct to me, and yes if you're looking to use this as a module you would import main. Though, it would probably be better to name it in a more descriptive way.

To clarify how __main__ and the function main() works. When you execute a module it will have a name which is stored in __name__. If you execute the module stand alone as a script it will have the name __main__. If you execute it as part of a module ie import it into another module it will have the name of the module.

The function main() can be named anything you would like, and that wouldn't affect your program. It's commonly named main in small scripts but it's not a particularly good name if it's part of a larger body of code.

In terms letting a user to input arguments when running as a script I would look into either using argparse or click

An example of how argparse would work.

if __name__ == '__main__':
    import argparse

    parser = argparse.ArgumentParser(description='Create a ArcHydro schema')
    parser.add_argument('--workspace', metavar='path', required=True,
                        help='the path to workspace')
    parser.add_argument('--schema', metavar='path', required=True,
                        help='path to schema')
    parser.add_argument('--dem', metavar='path', required=True,
                        help='path to dem')
    args = parser.parse_args()
    main(workspace=args.workspace, schema=args.schema, dem=args.dem)

Your doing it in the past way is probably fine for quick& dirty scripts or for mini tools used by other developers. There’s often no need to be fancy. On the contrary, Python’s default traceback output can be an appropriate or even desired form of error reporting. A script falling off its end exits with code 0, an unhandled exception exits the script with a non-zero code, so you’re covered there, too:

def main() -> None:
    # here would be code raising exceptions on error
    # note the `None` return type

if __name__ == '__main__':
    main()

When you need something more fancy working with sys.exit() explicitly is a good idea. That’s basically your future approach. But don’t only think of scripts called directly, also think of installed packages. Setuptools has a cross-platform mechanism to define functions as entry points for scripts. If you have this in your setup.py:

setuptools.setup(
        ...
        entry_points={
            'console_scripts': ['your_script=your_package.your_module:main'],
    },
)

and install that package you can run your_script from the command line.

A console_scripts function must be callable without any arguments. So, as long as all parameters have default values you’re fine. For longer running scripts consider explicitly handling KeyboardInterrupt to print a nice message when the user aborts with Ctrl+C. In the end the relevant part of your your_package/your_module.py might look something like this:

def main(cli_args: List[str] = None) -> int:
    """
    `cli_args` makes it possible to call this function command-line-style
    from other Python code without touching sys.argv.
    """
    try:
        # Parsing with `argparse` and additional processing 
        # is usually lenghty enough to extract into separate functions.
        raw_config = _parse_cli(
            sys.argv[1:] if cli_args is None else cli_args)
        config = _validate_and_sanitize(raw_config)

        # Same exception raising idea as the simple approach
        do_real_work(config.foo, config.bar)

    except KeyboardInterrupt:
        print('Aborted manually.', file=sys.stderr)
        return 1

    except Exception as err:
        # (in real code the `except` would probably be less broad)
        # Turn exceptions into appropriate logs and/or console output.

        # non-zero return code to signal error
        # Can of course be more fine grained than this general
        # "something went wrong" code.
        return 1

    return 0  # success

# __main__ support is still here to make this file executable without
# installing the package first.
if __name__ == '__main__':
    sys.exit(main())